MySQL Bugs: #68372: Function ifnull has ignored when used with select with some others selects

Bug #68372	Function ifnull has ignored when used with select with some others selects
Submitted:	13 Feb 2013 18:59	Modified:	4 Mar 2013 19:08
Reporter:	Luis A S Junior Camargo	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S1 (Critical)
Version:	5.6.10	OS:	Windows
Assigned to:		CPU Architecture:	Any
Tags:	regression

Description:
Same command has returning diferents results of the same data.

If the ifnull is executed in normal SQL, then return normal:
select ifnull(count(*),0) from sametable

If the ifnull is executed in more complex SQL, with more selects, the result is NULL:
select (select ifnull(count(*),0) from sametable) as tot,(select (......)) as other

How to repeat:
CREATE TABLE `comprapedlote` (
  `cod` int(11) NOT NULL AUTO_INCREMENT,
  `ped` int(11) DEFAULT '0',
  `tipo` smallint(4) DEFAULT '0',
  `tecido` smallint(4) DEFAULT '0',
  `cor` smallint(4) DEFAULT '0',
  `tam` tinyint(2) DEFAULT '0',
  `descricao` char(255) DEFAULT NULL,
  `qtd` smallint(4) DEFAULT '0',
  `preco` decimal(10,2) DEFAULT '0.00',
  `itensvenda` tinyint(1) DEFAULT '0',
  `codprodsite` int(11) DEFAULT '0',
  `corsite` smallint(6) DEFAULT '0',
  `agrupamento` int(11) DEFAULT '0',
  PRIMARY KEY (`cod`),
  KEY `ped` (`ped`),
  KEY `agrupamento` (`agrupamento`)
) ENGINE=MyISAM AUTO_INCREMENT=11732 DEFAULT CHARSET=latin1 ROW_FORMAT=FIXED;

INSERT INTO `comprapedlote` VALUES (11724,1779,200,6,5,2,'Montaria preto',55,13,1,0,0,1904);
INSERT INTO `comprapedlote` VALUES (11725,1779,200,6,5,4,'Montaria preto',57,13,1,0,0,1904);
INSERT INTO `comprapedlote` VALUES (11726,1779,200,6,5,3,'Montaria preto',56,13,1,0,0,1904);
INSERT INTO `comprapedlote` VALUES (11727,1779,200,6,5,5,'Montaria preto',52,13,1,0,0,1904);
INSERT INTO `comprapedlote` VALUES (11728,1779,200,6,9,2,'Montaria preto',43,13,1,0,0,1904);
INSERT INTO `comprapedlote` VALUES (11729,1779,200,6,9,4,'Montaria preto',42,13,1,0,0,1904);
INSERT INTO `comprapedlote` VALUES (11730,1779,200,6,9,3,'Montaria preto',42,13,1,0,0,1904);
INSERT INTO `comprapedlote` VALUES (11731,1779,200,6,9,5,'Montaria preto',46,13,1,0,0,1904);

CREATE TABLE `comprapedlotecodbarras` (
  `pedcompralote` int(11) NOT NULL,
  `pedcompraseq` smallint(5) DEFAULT '0',
  `pedvenda` int(11) DEFAULT '0',
  `pedvendasite` tinyint(1) DEFAULT '0',
  `local` tinyint(2) DEFAULT '0',
  `cancelado` tinyint(1) DEFAULT '0'
) ENGINE=MyISAM DEFAULT CHARSET=latin1 ROW_FORMAT=FIXED;

INSERT INTO `comprapedlotecodbarras` VALUES (12,12,12,12,12,12);

======================================================================

Test with result OK:
select ifnull(count(*),3) from comprapedlotecodbarras where pedcompralote=11725
Returns:
0

Test with incorrect results:
select (select ifnull(count(*),3) from comprapedlotecodbarras where pedcompralote=11725) as tot,ifnull(max(pedcompraseq),0)+1 as newcode from comprapedlotecodbarras where pedcompralote in(select cod from comprapedlote where ped=1779) 
Returns:
tot: NULL
newcode: 1

table pedcompralote

Attachment: comprapedlote.sql (application/octet-stream, text), 1.65 KiB.

table pedcompralotecodbarras

Attachment: comprapedlotecodbarras.sql (application/octet-stream, text), 658 bytes.

This is what I get on 5.5.29 with your test:

mysql> select ifnull(count(*),3) from comprapedlotecodbarras where pedcompralote
=11725;
+--------------------+
| ifnull(count(*),3) |
+--------------------+
|                  0 |
+--------------------+
1 row in set (0.16 sec)

mysql> select (select ifnull(count(*),3) from comprapedlotecodbarras where pedco
mpralote=11725) as tot,ifnull(max(pedcompraseq),0)+1 as newcode from comprapedlo
tecodbarras where pedcompralote in(select cod from comprapedlote where ped=1779)
 ;
+------+---------+
| tot  | newcode |
+------+---------+
|    0 |       1 |
+------+---------+
1 row in set (0.38 sec)

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.5.29    |
+-----------+
1 row in set (0.00 sec)

The results on 5.6.10:

mysql> select (select ifnull(count(*),3) from comprapedlotecodbarras where pedco
mpralote=11725) as tot,ifnull(max(pedcompraseq),0)+1 as newcode from comprapedlo
tecodbarras where pedcompralote in(select cod from comprapedlote where ped=1779)
 ;
+------+---------+
| tot  | newcode |
+------+---------+
| NULL |       1 |
+------+---------+
1 row in set (0.05 sec)

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.6.10    |
+-----------+
1 row in set (0.00 sec)

So, there are reasons to claim we have a regression bug here...

Thank you for the bug report. Verified as described.

Noted in 5.6.11, 5.7.1 changelogs.

A statement with an aggregated, nongrouped outer query and an
aggregated, nongrouped subquery in the SELECT list could return
incorrect results.