Bug #73946 Wrong result with agregate with join with no resultset
Submitted: 17 Sep 2014 9:17 Modified: 17 Sep 2014 11:20
Reporter: T. M. Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: DML Severity:S1 (Critical)
Version:5.5.39/5.6/5.7 OS:Windows (x64)
Assigned to: CPU Architecture:Any
Tags: AGREGATE MIN NULL, regression

[17 Sep 2014 9:17] T. M.
Description:
Hi,

When you join 2 tables with no resultset and put a min/max on child table, in some case Parent's fields is not null.

In the test-case bellow, PARENT_ID must be null, not "1"

no workaround, working on 5.6.20

How to repeat:
mysql> use test;
Database changed
mysql>
mysql> drop table if exists _Parent;
Query OK, 0 rows affected (0.25 sec)

mysql> create table _Parent
    -> (
    ->                 PARENT_ID int(10) unsigned NOT NULL AUTO_INCREMENT,
    ->                 PARENT_FIELD VARCHAR(10),
    ->                 PRIMARY KEY (PARENT_ID)
    -> );
Query OK, 0 rows affected (0.19 sec)

mysql>
mysql> drop table if exists _Child;
Query OK, 0 rows affected (0.10 sec)

mysql> create table _Child
    -> (
    ->                 CHILD_ID INT NOT NULL AUTO_INCREMENT,
    ->                 PARENT_ID INT NOT NULL,
    ->                 CHILD_FIELD varchar(10),
    ->                 PRIMARY KEY (CHILD_ID)
    -> );
Query OK, 0 rows affected (0.14 sec)

mysql>
mysql> INSERT INTO _Parent (PARENT_FIELD)
    -> SELECT 'AAAA';
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql>
mysql> INSERT INTO _Child (PARENT_ID, CHILD_FIELD)
    -> SELECT 1, 'BBBB';
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql>
mysql> select
    -> _Parent.PARENT_ID,
    -> min(CHILD_FIELD)
    -> from _Parent straight_join _Child
    -> where _Parent.PARENT_ID = 1
    -> and _Parent.PARENT_ID = _Child.PARENT_ID
    -> and _Child.CHILD_FIELD = "ZZZZ";
+-----------+------------------+
| PARENT_ID | min(CHILD_FIELD) |
+-----------+------------------+
|         1 | NULL             |
+-----------+------------------+
1 row in set (0.06 sec)
[17 Sep 2014 9:32] T. M.
Test case in a more usable way :

use test;

drop table if exists _Parent;
create table _Parent
(
	PARENT_ID int(10) unsigned NOT NULL AUTO_INCREMENT,
	PARENT_FIELD VARCHAR(10),
	PRIMARY KEY (PARENT_ID)
);

drop table if exists _Child;
create table _Child
(
	CHILD_ID INT NOT NULL AUTO_INCREMENT,
	PARENT_ID INT NOT NULL,
	CHILD_FIELD varchar(10),
	PRIMARY KEY (CHILD_ID)
);

INSERT INTO _Parent (PARENT_FIELD)
SELECT 'AAAA';

INSERT INTO _Child (PARENT_ID, CHILD_FIELD)
SELECT 1, 'BBBB';

select 
 _Parent.PARENT_ID,
 min(CHILD_FIELD)
 from _Parent straight_join _Child
 where _Parent.PARENT_ID = 1
 and _Parent.PARENT_ID = _Child.PARENT_ID
 and _Child.CHILD_FIELD = "ZZZZ";
[17 Sep 2014 11:17] MySQL Verification Team
C:\dbs>c:\dbs\5.1\bin\mysql -uroot --port=3510 --debug-info --prompt="mysql 5.1 > "
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.74-Win X64 Source distribution

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql 5.1 > use test
Database changed
mysql 5.1 > drop table if exists _Parent;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql 5.1 > create table _Parent
    -> (
    ->  PARENT_ID int(10) unsigned NOT NULL AUTO_INCREMENT,
    ->  PARENT_FIELD VARCHAR(10),
    ->  PRIMARY KEY (PARENT_ID)
    -> );
Query OK, 0 rows affected (0.08 sec)

mysql 5.1 >
mysql 5.1 > drop table if exists _Child;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql 5.1 > create table _Child
    -> (
    ->  CHILD_ID INT NOT NULL AUTO_INCREMENT,
    ->  PARENT_ID INT NOT NULL,
    ->  CHILD_FIELD varchar(10),
    ->  PRIMARY KEY (CHILD_ID)
    -> );
Query OK, 0 rows affected (0.06 sec)

mysql 5.1 >
mysql 5.1 > INSERT INTO _Parent (PARENT_FIELD)
    -> SELECT 'AAAA';
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql 5.1 >
mysql 5.1 > INSERT INTO _Child (PARENT_ID, CHILD_FIELD)
    -> SELECT 1, 'BBBB';
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql 5.1 >
mysql 5.1 > select
    ->  _Parent.PARENT_ID,
    ->  min(CHILD_FIELD)
    ->  from _Parent straight_join _Child
    ->  where _Parent.PARENT_ID = 1
    ->  and _Parent.PARENT_ID = _Child.PARENT_ID
    ->  and _Child.CHILD_FIELD = "ZZZZ";
+-----------+------------------+
| PARENT_ID | min(CHILD_FIELD) |
+-----------+------------------+
|      NULL | NULL             |
+-----------+------------------+
1 row in set (0.00 sec)
[17 Sep 2014 11:20] MySQL Verification Team
Thank you for the bug report.

C:\dbs>c:\dbs\5.5\bin\mysql -uroot --port=3550 --prompt="mysql 5.5 > "
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.40 Source distribution

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql 5.5 > use test;
Database changed
mysql 5.5 >
mysql 5.5 > drop table if exists _Parent;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql 5.5 > create table _Parent
    -> (
    ->  PARENT_ID int(10) unsigned NOT NULL AUTO_INCREMENT,
    ->  PARENT_FIELD VARCHAR(10),
    ->  PRIMARY KEY (PARENT_ID)
    -> );
Query OK, 0 rows affected (0.14 sec)

mysql 5.5 >
mysql 5.5 > drop table if exists _Child;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql 5.5 > create table _Child
    -> (
    ->  CHILD_ID INT NOT NULL AUTO_INCREMENT,
    ->  PARENT_ID INT NOT NULL,
    ->  CHILD_FIELD varchar(10),
    ->  PRIMARY KEY (CHILD_ID)
    -> );
Query OK, 0 rows affected (0.11 sec)

mysql 5.5 >
mysql 5.5 > INSERT INTO _Parent (PARENT_FIELD)
    -> SELECT 'AAAA';
Query OK, 1 row affected (0.03 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql 5.5 >
mysql 5.5 > INSERT INTO _Child (PARENT_ID, CHILD_FIELD)
    -> SELECT 1, 'BBBB';
Query OK, 1 row affected (0.03 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql 5.5 >
mysql 5.5 > select
    ->  _Parent.PARENT_ID,
    ->  min(CHILD_FIELD)
    ->  from _Parent straight_join _Child
    ->  where _Parent.PARENT_ID = 1
    ->  and _Parent.PARENT_ID = _Child.PARENT_ID
    ->  and _Child.CHILD_FIELD = "ZZZZ";
+-----------+------------------+
| PARENT_ID | min(CHILD_FIELD) |
+-----------+------------------+
|         1 | NULL             |
+-----------+------------------+
1 row in set (0.00 sec)
[17 Sep 2014 11:27] MySQL Verification Team
5.6 affected too:

C:\dbs>c:\dbs\5.6\bin\mysql -uroot --port=3560 --debug-info --prompt="mysql 5.6 > "
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.6.22 Source distribution

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql 5.6 > use test;
Database changed
mysql 5.6 >
mysql 5.6 > drop table if exists _Parent;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql 5.6 > create table _Parent
    -> (
    ->  PARENT_ID int(10) unsigned NOT NULL AUTO_INCREMENT,
    ->  PARENT_FIELD VARCHAR(10),
    ->  PRIMARY KEY (PARENT_ID)
    -> );
Query OK, 0 rows affected (1.20 sec)

mysql 5.6 >
mysql 5.6 > drop table if exists _Child;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql 5.6 > create table _Child
    -> (
    ->  CHILD_ID INT NOT NULL AUTO_INCREMENT,
    ->  PARENT_ID INT NOT NULL,
    ->  CHILD_FIELD varchar(10),
    ->  PRIMARY KEY (CHILD_ID)
    -> );
Query OK, 0 rows affected (0.66 sec)

mysql 5.6 >
mysql 5.6 > INSERT INTO _Parent (PARENT_FIELD)
    -> SELECT 'AAAA';
Query OK, 1 row affected (0.14 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql 5.6 >
mysql 5.6 > INSERT INTO _Child (PARENT_ID, CHILD_FIELD)
    -> SELECT 1, 'BBBB';
Query OK, 1 row affected (0.19 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql 5.6 >
mysql 5.6 > select
    ->  _Parent.PARENT_ID,
    ->  min(CHILD_FIELD)
    ->  from _Parent straight_join _Child
    ->  where _Parent.PARENT_ID = 1
    ->  and _Parent.PARENT_ID = _Child.PARENT_ID
    ->  and _Child.CHILD_FIELD = "ZZZZ";
+-----------+------------------+
| PARENT_ID | min(CHILD_FIELD) |
+-----------+------------------+
|         1 | NULL             |
+-----------+------------------+
1 row in set (0.16 sec)
[17 Sep 2014 11:30] MySQL Verification Team
C:\dbs>c:\dbs\5.7\bin\mysql -uroot --port=3570 --prompt="mysql 5.7 > "
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.6-m16 Source distribution

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql 5.7 > create database test;
Query OK, 1 row affected (0.19 sec)

mysql 5.7 > use test;
Database changed
mysql 5.7 >
mysql 5.7 > drop table if exists _Parent;
Query OK, 0 rows affected, 1 warning (0.02 sec)

mysql 5.7 > create table _Parent
    -> (
    ->  PARENT_ID int(10) unsigned NOT NULL AUTO_INCREMENT,
    ->  PARENT_FIELD VARCHAR(10),
    ->  PRIMARY KEY (PARENT_ID)
    -> );
Query OK, 0 rows affected (0.67 sec)

mysql 5.7 >
mysql 5.7 > drop table if exists _Child;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql 5.7 > create table _Child
    -> (
    ->  CHILD_ID INT NOT NULL AUTO_INCREMENT,
    ->  PARENT_ID INT NOT NULL,
    ->  CHILD_FIELD varchar(10),
    ->  PRIMARY KEY (CHILD_ID)
    -> );
Query OK, 0 rows affected (0.48 sec)

mysql 5.7 >
mysql 5.7 > INSERT INTO _Parent (PARENT_FIELD)
    -> SELECT 'AAAA';
Query OK, 1 row affected (0.06 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql 5.7 >
mysql 5.7 > INSERT INTO _Child (PARENT_ID, CHILD_FIELD)
    -> SELECT 1, 'BBBB';
Query OK, 1 row affected (0.02 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql 5.7 >
mysql 5.7 > select
    ->  _Parent.PARENT_ID,
    ->  min(CHILD_FIELD)
    ->  from _Parent straight_join _Child
    ->  where _Parent.PARENT_ID = 1
    ->  and _Parent.PARENT_ID = _Child.PARENT_ID
    ->  and _Child.CHILD_FIELD = "ZZZZ";
+-----------+------------------+
| PARENT_ID | min(CHILD_FIELD) |
+-----------+------------------+
|         1 | NULL             |
+-----------+------------------+
1 row in set (0.66 sec)
[17 Sep 2014 11:34] MySQL Verification Team
C:\dbs>c:\dbs\5.0\bin\mysql -uroot --port=3500 --prompt="mysql 5.0 > "
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.97-Win X64 Source distribution

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql 5.0 > use test;
Database changed
mysql 5.0 >
mysql 5.0 > drop table if exists _Parent;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql 5.0 > create table _Parent
    -> (
    ->  PARENT_ID int(10) unsigned NOT NULL AUTO_INCREMENT,
    ->  PARENT_FIELD VARCHAR(10),
    ->  PRIMARY KEY (PARENT_ID)
    -> );
Query OK, 0 rows affected (0.10 sec)

mysql 5.0 >
mysql 5.0 > drop table if exists _Child;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql 5.0 > create table _Child
    -> (
    ->  CHILD_ID INT NOT NULL AUTO_INCREMENT,
    ->  PARENT_ID INT NOT NULL,
    ->  CHILD_FIELD varchar(10),
    ->  PRIMARY KEY (CHILD_ID)
    -> );
Query OK, 0 rows affected (0.05 sec)

mysql 5.0 >
mysql 5.0 > INSERT INTO _Parent (PARENT_FIELD)
    -> SELECT 'AAAA';
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql 5.0 >
mysql 5.0 > INSERT INTO _Child (PARENT_ID, CHILD_FIELD)
    -> SELECT 1, 'BBBB';
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql 5.0 >
mysql 5.0 > select
    ->  _Parent.PARENT_ID,
    ->  min(CHILD_FIELD)
    ->  from _Parent straight_join _Child
    ->  where _Parent.PARENT_ID = 1
    ->  and _Parent.PARENT_ID = _Child.PARENT_ID
    ->  and _Child.CHILD_FIELD = "ZZZZ";
+-----------+------------------+
| PARENT_ID | min(CHILD_FIELD) |
+-----------+------------------+
|      NULL | NULL             |
+-----------+------------------+
1 row in set (0.00 sec)

mysql 5.0 >
[23 Sep 2014 21:38] Elena Stepanova
Hi Miguel, 

5.1 is affected too (at least 5.1.73 which I have). The problem is only reproducible with InnoDB, not with MyISAM; 5.1 has default storage engine MyISAM, so you don't get the wrong result with the provided test case. Starting from 5.5, default engine is InnoDB, hence wrong result.
[25 Sep 2014 8:46] Guilhem Bichot
interesting: if in Miguel's query I swap both tables:
"from  _Child straight_join _Parent"
then I get (NULL,NULL).