MySQL Bugs: #46356: Certain queries aren't recognizing GROUP BY w/ join_cache

Bug #46356	Certain queries aren't recognizing GROUP BY w/ join_cache_level = 1
Submitted:	23 Jul 2009 14:06	Modified:	21 Oct 2009 12:28
Reporter:	Patrick Crews	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	5.4, 6.0	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	extra rows, join_cache_level, NULL rows, optimizer_switch, regression

Description:
An extra NULL row is being returned for certain query patterns when join_cache_level = 1:
NOTE:  Table AA is empty (see test case in How to repeat section)
SELECT table1 .`datetime_key` field1  
FROM AA table1  RIGHT  JOIN B table3  JOIN B table4  ON table4 .`int_key`  ON table3 .`int_nokey`  
GROUP  BY field1   ;

Returns:
NULL
when join_cache_level = 0

and returns:
NULL
NULL
when join_cache_level = 1

How to repeat:
Test case:

# The value of optimizer_switch is common between the two servers:
/*!50400 SET SESSION optimizer_switch = firstmatch=off,index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,loosescan=off,materialization=off,semijoin=off */;

# The value of optimizer_use_mrr is common between the two servers:
/*!50400 SET SESSION optimizer_use_mrr = disable */;

# The value of engine_condition_pushdown is common between the two servers:
/*!50400 SET SESSION engine_condition_pushdown = 0 */;

# The value of join_cache_level is distinct between the two servers:
# Server 0 : SET SESSION join_cache_level = 0;
# Server 1 : SET SESSION join_cache_level = 1;

# Begin test case for query 0

--disable_warnings
DROP TABLE IF EXISTS AA, B;
--enable_warnings

CREATE TABLE `AA` (
  `int_nokey` int(11) NOT NULL,
  `int_key` int(11) NOT NULL,
  `datetime_key` datetime NOT NULL,
  KEY `int_key` (`int_key`),
  KEY `datetime_key` (`datetime_key`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
CREATE TABLE `B` (
  `int_nokey` int(11) NOT NULL,
  `int_key` int(11) NOT NULL,
  `datetime_key` datetime NOT NULL,
  KEY `int_key` (`int_key`),
  KEY `datetime_key` (`datetime_key`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
INSERT INTO `B` VALUES (5,9,'0000-00-00 00:00:00'),(0,0,'2004-07-02 14:34:13');

 
SELECT table1 .`datetime_key` field1  
FROM AA table1  RIGHT  JOIN B table3  JOIN B table4  ON table4 .`int_key`  ON table3 .`int_nokey`  
GROUP  BY field1   ;

# Diff:

# --- /var/folders/Pt/PtJd7NDTGgyOk3+iDGXrQk+++TI/-Tmp-///randgen707-1248355884-server0.dump	2009-07-23 09:31:24.000000000 -0400
# +++ /var/folders/Pt/PtJd7NDTGgyOk3+iDGXrQk+++TI/-Tmp-///randgen707-1248355884-server1.dump	2009-07-23 09:31:24.000000000 -0400
# @@ -1 +1,2 @@
#  NULL
# +NULL

DROP TABLE AA, B;

# End of test case for query 0

# Begin test case for query 1

--disable_warnings
DROP TABLE IF EXISTS B;
--enable_warnings

CREATE TABLE `B` (
  `int_nokey` int(11) NOT NULL,
  `int_key` int(11) NOT NULL,
  `date_key` date NOT NULL,
  `datetime_key` datetime NOT NULL,
  `datetime_nokey` datetime NOT NULL,
  `varchar_nokey` varchar(1) NOT NULL,
  KEY `int_key` (`int_key`),
  KEY `date_key` (`date_key`),
  KEY `datetime_key` (`datetime_key`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
INSERT INTO `B` VALUES (5,9,'2003-07-28','0000-00-00 00:00:00','0000-00-00 00:00:00','f'),(0,0,'0000-00-00','2004-07-02 14:34:13','2004-07-02 14:34:13','x');

 
SELECT table1 . `datetime_key` AS field1 
FROM (AA AS table1 RIGHT  JOIN (BB AS table2 CROSS JOIN (B AS table3 RIGHT  JOIN B AS table4 ON ( table4 . `int_key` = table3 . `datetime_nokey` )) ON ( table4 . `varchar_nokey` = table3 . `datetime_key` )) ON ( table4 . `int_key` = table3 . `int_nokey` )) 
WHERE  (table3 . `date_key` <> '2001-08-14 18:18:44' IS NOT NULL OR table3 . `int_nokey` <> 'i')  
GROUP BY field1  ;

# Diff:

# --- /var/folders/Pt/PtJd7NDTGgyOk3+iDGXrQk+++TI/-Tmp-///randgen707-1248355887-server0.dump	2009-07-23 09:31:27.000000000 -0400
# +++ /var/folders/Pt/PtJd7NDTGgyOk3+iDGXrQk+++TI/-Tmp-///randgen707-1248355887-server1.dump	2009-07-23 09:31:27.000000000 -0400
# @@ -1 +1,2 @@
#  NULL
# +NULL

DROP TABLE B;

# End of test case for query 1

Suggested fix:
Ensure consistent result sets regardless of variable settings.

This isn't *just* an extra NULL row, but rather that the GROUP BY clause isn't being recognized.

This behavior was observed with other, similar queries and different values.

Here is a test case that does not involve NULLs. The query is as follows:

SELECT table1 .`varchar_key` field1
FROM B table1  JOIN C table2  ON table2 .`int_key`
WHERE table1 .`varchar_key`  = 'b'
GROUP  BY field1;

In other words, we both GROUP and filter on the same field and we filter on a single value.

# Server0: version = 5.4.4-alpha-debug-log
# Server1: version = 5.1.37-debug-log

# The value of optimizer_switch is distinct between the two servers:
# Server 0 : SET SESSION optimizer_switch = 'firstmatch=off,index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,loosescan=off,materialization=off,semijoin=off';
# Server 1 : SET SESSION optimizer_switch = 'index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on';

# The value of optimizer_use_mrr is distinct between the two servers:
# Server 0 : SET SESSION optimizer_use_mrr = 'disable';
# Server 1 : SET SESSION optimizer_use_mrr = '';

# The value of engine_condition_pushdown is distinct between the two servers:
# Server 0 : SET SESSION engine_condition_pushdown = 0;
# Server 1 : SET SESSION engine_condition_pushdown = 'ON';

# The value of join_cache_level is distinct between the two servers:
# Server 0 : SET SESSION join_cache_level = 1;
# Server 1 : SET SESSION join_cache_level = '';

# Begin test case for query 0

--disable_warnings
DROP TABLE IF EXISTS C, B;
--enable_warnings

CREATE TABLE `C` (
  `int_key` int(11) DEFAULT NULL,
  `varchar_key` varchar(1) DEFAULT NULL,
  KEY `int_key` (`int_key`),
  KEY `varchar_key` (`varchar_key`,`int_key`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
INSERT INTO `C` VALUES (1,NULL),(2,NULL),(7,NULL),(122,'e'),(7,'f'),(198,'f'),(4,'g'),(1,'i'),(6,'j'),(127,'j'),(0,'k'),(7,'l'),(6,'m'),(8,'p'),(NULL,'u'),(2,'u'),(9,'u'),(213,'u'),(NULL,'v'),(217,'v');
CREATE TABLE `B` (
  `int_key` int(11) DEFAULT NULL,
  `varchar_key` varchar(1) DEFAULT NULL,
  KEY `int_key` (`int_key`),
  KEY `varchar_key` (`varchar_key`,`int_key`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
INSERT INTO `B` VALUES (5,'b'),(6,'c');

SELECT table1 .`varchar_key` field1
FROM B table1  JOIN C table2  ON table2 .`int_key`
WHERE table1 .`varchar_key`  = 'b'
GROUP  BY field1;

DROP TABLE C, B;

# End of test case for query 0

# Server0: version = 5.4.4-alpha-debug-log
# Server1: version = 5.1.37-debug-log

# The value of optimizer_switch is distinct between the two servers:
# Server 0 : SET SESSION optimizer_switch = 'firstmatch=off,index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,loosescan=off,materialization=off,semijoin=off';
# Server 1 : SET SESSION optimizer_switch = 'index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on';

# The value of optimizer_use_mrr is distinct between the two servers:
# Server 0 : SET SESSION optimizer_use_mrr = 'disable';
# Server 1 : SET SESSION optimizer_use_mrr = '';

# The value of engine_condition_pushdown is distinct between the two servers:
# Server 0 : SET SESSION engine_condition_pushdown = 0;
# Server 1 : SET SESSION engine_condition_pushdown = 'ON';

# The value of join_cache_level is distinct between the two servers:
# Server 0 : SET SESSION join_cache_level = 1;
# Server 1 : SET SESSION join_cache_level = '';

# Begin test case for query 0

--disable_warnings
DROP TABLE IF EXISTS C, B;
--enable_warnings

CREATE TABLE `C` (
  `int_key` int(11) DEFAULT NULL,
  `varchar_key` varchar(1) DEFAULT NULL,
  KEY `int_key` (`int_key`),
  KEY `varchar_key` (`varchar_key`,`int_key`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
INSERT INTO `C` VALUES (1,NULL),(2,NULL),(7,NULL),(122,'e'),(7,'f'),(198,'f'),(4,'g'),(1,'i'),(6,'j'),(127,'j'),(0,'k'),(7,'l'),(6,'m'),(8,'p'),(NULL,'u'),(2,'u'),(9,'u'),(213,'u'),(NULL,'v'),(217,'v');
CREATE TABLE `B` (
  `int_key` int(11) DEFAULT NULL,
  `varchar_key` varchar(1) DEFAULT NULL,
  KEY `int_key` (`int_key`),
  KEY `varchar_key` (`varchar_key`,`int_key`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
INSERT INTO `B` VALUES (5,'b'),(6,'c');

SELECT table1 .`varchar_key` field1
FROM B table1  JOIN C table2  ON table2 .`int_key`
WHERE table1 .`varchar_key`  = 'b'
GROUP  BY field1;

# Diff:

# --- /tmp//randgen3978-1249366917-server0.dump 2009-08-04 09:21:57.000000000 +0300
# +++ /tmp//randgen3978-1249366917-server1.dump 2009-08-04 09:21:57.000000000 +0300
# @@ -1,17 +1 @@
#  b
# -b
# -b
# -b
# -b
# -b
# -b
# -b
# -b
# -b
# -b
# -b
# -b
# -b
# -b
# -b
# -b

DROP TABLE C, B;

# End of test case for query 0

This is a duplicate of bug # 45267. bzrfind was applied to 6.0 branches between 2009-07-23 and 2009-10-21, and the bugfix for this was found to also solve this problem.