Re: Error " #1241 - Operand should contain 1 column(s)" when calling a function

[Jay Alverson]

That's one complex function.

You might try copying it into a PROCEDURE and then put SELECT statements
every so often so that you can debug it from top to bottom.

Then call the procedure and see if you can isolate the problem. Once you
get a chunk of code secure, just comment out the SELECT and move to the
next block.

That's usually what I do with complex functions; that way I can see how far
a variable or piece of code gets. I also often create temp tables so that
I can monitor the progress of the executing code:

mysql> 
mysql> use test;
Database changed
mysql> 
mysql> DELIMITER $$
mysql> 
mysql> DROP PROCEDURE IF EXISTS `test`.`GS` $$
Query OK, 0 rows affected (0.02 sec)

mysql> CREATE PROCEDURE `test`.`GS` (str VARCHAR(500), delim VARCHAR(10))
    -> BEGIN
    ->   DECLARE pos SMALLINT;
    ->   DECLARE dastr VARCHAR(100);
    ->   DECLARE daCount SMALLINT;
    -> 
    ->   SET daCount = 0;
    -> 
    -> 
    -> 
    ->   drop table if exists test_getstring;
    ->   create table test_getstring (pos smallint, dastr varchar(500), str varchar(500), dapos smallint);
    -> 
    ->   label1: LOOP
    ->     SET pos = instr(str, delim);
    ->     SET dastr = SUBSTRING_INDEX(str, delim, 1);
    ->     IF pos > 0 THEN
    ->       SET daCount = daCount + 1;
    ->       SET str = mid(str, pos+1, CHAR_LENGTH(str));
    ->       ELSE LEAVE label1;
    ->     END IF;
    -> 
    ->     insert into test_getstring values (pos, dastr, str, dapos);
    -> 
    -> 
    ->     if daCount > 10000 then LEAVE label1; end if;
    ->   END LOOP label1;
    ->   SET daCount = daCount + 1;
    -> 
    -> END $$
Query OK, 0 rows affected (0.00 sec)

mysql> 
mysql> DELIMITER ;
mysql> 
mysql> call GS("One;Two;Three;Four;Five;Six;Seven", ";");
Query OK, 1 row affected (0.08 sec)

mysql> 
mysql> select * from test_getstring;
+------+-------+-------------------------------+-------+
| pos  | dastr | str                           | dapos |
+------+-------+-------------------------------+-------+
|    4 | One   | Two;Three;Four;Five;Six;Seven |  NULL | 
|    4 | Two   | Three;Four;Five;Six;Seven     |  NULL | 
|    6 | Three | Four;Five;Six;Seven           |  NULL | 
|    5 | Four  | Five;Six;Seven                |  NULL | 
|    5 | Five  | Six;Seven                     |  NULL | 
|    4 | Six   | Seven                         |  NULL | 
+------+-------+-------------------------------+-------+
6 rows in set (0.00 sec)

mysql> 
mysql> notee

>

Thanks, Jay