| 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: | |
| 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: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).
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)