-- Reproduction of group_concat_max_len limit of 2^16 - 2.
--
-- First select produces correct results; the second select, identical
-- to the first but with a slightly larger group_concat_max_len
-- parameter, produces incorrect results.

DROP TABLE IF EXISTS test_hierarchy;
CREATE TABLE test_hierarchy (
  startID int(11) NOT NULL,
  stopID int(11) NOT NULL,
  pathLen int(11) NOT NULL,
  PRIMARY KEY  (startID,stopID,pathLen)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO test_hierarchy VALUES (20634,21038,2),(20634,21039,1),(20634,22521,3),(21038,22521,1),(21039,21038,1),(21039,22521,2);

DROP TABLE IF EXISTS test_table;
CREATE TABLE test_table (
  id int(11) NOT NULL auto_increment,
  t varchar(255) NOT NULL,
  a varchar(255) NOT NULL,
  s varchar(25) NOT NULL,
  q varchar(15) NOT NULL,
  parentID int(11) NOT NULL default '0',
  PRIMARY KEY  (id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 PACK_KEYS=1;

INSERT INTO test_table VALUES (20634,'D','p','C','Q',0),(21038,'D','p','T','T',21039),(21039,'P','r','C','T',20634),(22521,'R','p','','O',21038);

set session group_concat_max_len = 1024 * 64 - 2;

select	test_table.id, 
		group_concat(concat(parentRecs.id, 
							'<field>', 
							parentRecs.t, 
							'<field>', 
							parentRecs.s, 
							'<field>', 
							parentRecs.a, 
							'<field>', 
							parentRecs.q) 
		order by parentHierarchy.pathLen 
		separator '<rec>') as parents
from	test_table 
		join test_hierarchy as parentHierarchy on
			parentHierarchy.stopID = test_table.id
		left join test_table as parentRecs on
			parentRecs.id = parentHierarchy.startID
where	test_table.id in (0, 22521)
group by test_table.id;

set session group_concat_max_len = 1024 * 64 - 1;

select	test_table.id, 
		group_concat(concat(parentRecs.id, 
							'<field>', 
							parentRecs.t, 
							'<field>', 
							parentRecs.s, 
							'<field>', 
							parentRecs.a, 
							'<field>', 
							parentRecs.q) 
		order by parentHierarchy.pathLen 
		separator '<rec>') as parents
from	test_table 
		join test_hierarchy as parentHierarchy on
			parentHierarchy.stopID = test_table.id
		left join test_table as parentRecs on
			parentRecs.id = parentHierarchy.startID
where	test_table.id in (0, 22521)
group by test_table.id;