Group_Concat used in mysql cursor returning wrong result

Question

The following code should produce 3 entries for csv2 ... however 6 entries are produced.

Why is that? The code is in progress.

Creation Code:

Create table testing(
  idString varchar(20),
  region varchar(20)
);

insert into testing values("abcdefg","123456");
insert into testing values("bcdefgh","123456");
insert into testing values("cdefghi","123456");
insert into testing values("defghij","456789");
insert into testing values("efghijk","456789");
insert into testing values("fghijkl","456789");

Code to Run:

DROP PROCEDURE IF EXISTS `sp_split`;
DROP PROCEDURE IF EXISTS `getCombinations`;

CREATE TABLE IF NOT EXISTS `temp` (
  `col` VARCHAR(100) NOT NULL
) ENGINE=MEMORY;

CREATE TABLE IF NOT EXISTS `result` (
  `col1` VARCHAR(100) NOT NULL
) ENGINE=MEMORY;


CREATE PROCEDURE getCombinations()
  BEGIN

    DECLARE countInt INT DEFAULT 0;
    DECLARE csv varchar(100);
    DECLARE region varchar(100);
    DECLARE v_last_row_fetched INT;
    declare v_counter int unsigned default 1;

    DECLARE counter cursor for
      select  * from ( select region as region,
                              count(distinct idString) as countInt,
                              group_concat(idString) as csv
                          from testing
                          group by region ) temp;

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET  v_last_row_fetched=1;
    SET SESSION sql_mode = CONCAT(@@sql_mode, ',PIPES_AS_CONCAT');

    delete from result;
    open counter;
    c1_loop: loop
      fetch counter into region , countInt, csv ;


      IF  v_last_row_fetched=1 THEN LEAVE c1_loop; END IF;

      #DEBUG STATEMENT HERE - 6 values instead of two displayed for csv3
      select csv as csv3;
      LEAVE c1_loop;

    end loop c1_loop;
    close counter;

  END;

DELIMITER ;

CALL getCombinations();
SET SESSION sql_mode = CONCAT(@@sql_mode, ',PIPES_AS_CONCAT');

Answer 1

The problem is that you have a local variable named region, and this is being used in GROUP BY region rather than the column. So it's as if you'd written GROUP BY null, so everything is grouped together.

Change the name of the region variable and it will work. In general, avoid using variables that have the same names as your table columns, it often causes problems.