0

Aggregate functions are working for my on command line and in my SQL client, but when I run those very same aggregate functions for use in setting a variable inside a stored procedure, I get duplicate values - the same number of values for multiple rows, but all values are the same.

Say I have a table `table_name`:
|test_field       | target_field|
|----------------:|:------------|
|"test_value"     |1            |
|"test_value"     |2            |
|"not_test_value" |3            |
|"test_value"     |4            |

The aggregate function works as expected in regular SQL query:

SET @array_value := "";
SELECT GROUP_CONCAT(target_field) INTO @array_value
  FROM `table_name`
  WHERE `test_field` = 'test_value';

Where I get the result: "1,2,4"

However, when I use same syntax inside a stored procedure, I get very different results: "4,4,4"

Note that this stored procedure is triggered on an update of the same table:

CREATE TRIGGER `cacheAggregate` 
AFTER INSERT ON `table_name` 
FOR EACH ROW 
BEGIN
    CALL storedProcedureName (
      NEW.target_field
    );
END

...which calls the following stored procedure:

CREATE PROCEDURE `storedProcedureName `(
    IN `target_field` VARCHAR
)
BEGIN
 SET @answer_array := '';
    
  SELECT GROUP_CONCAT(target_field) INTO @answer_array
        FROM `table_name`
        WHERE `test_field` = "test_value";
        
   INSERT INTO CACHE_TABLE (`answers_array`, `fk_target_field`)
       VALUES(@answer_array, target_field);

END

When I insert a value into the table, the trigger fires, calls the stored procedure, but the issue presents itself - @answer_array is wrong, consisting of a an array of the correct length, where each value is always the last value entered into the table.

IE, If I run this query:

INSERT INTO `table_name` (`test_field`,`target_field`) VALUES ("test_value", 5);

I would expect it to be stored as: "1,2,4,5"

However, what is actually being returned is: "5,5,5,5"

My guess is that the stored procedure is using some kind of cursor/pointer which breaks the aggregate function, but I had trouble finding anyone who had the same issue.

UPDATE/RESOLVED

While trying to reproduce this with the modified example code, I found my issue. I'm leaving this here in case it helps anyone out in the future.

The issue was a naming collision. The parameter name for my stored procedure was the same as the target_field of my table, so the SELECT statement was using the stored procedure parameter as opposed to the table field. Since the value passed to the stored procedure the last updated value sent by the trigger, it was replicated the number of the resultant rows.

It would be the same if I used a literal in a select statement that returned multiple rows, EG:

 SELECT GROUP_CONCAT("String Literal") INTO @answer_array
        FROM `table_name`
        WHERE `test_field` = "test_value";

...would produce the result: "String Literal,String Literal,String Literal,String Literal"

Joshua Albert
  • 1
  • 1

1 Answers1

0

The solution was to either explicitly set the table name in the GROUP_CONCAT argument:

SELECT GROUP_CONCAT(tn.target_field) INTO @answer_array
        FROM `table_name` tn
        WHERE `test_field` = "test_value";

-or- ...you could just change the name of the passed IN parameter in the stored procedure:

CREATE PROCEDURE `storedProcedureName `(
    IN `sp_target_field` VARCHAR
)
...
Joshua Albert
  • 1
  • 1