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).
Portions of this website are copyright © 2001, 2002 The PHP Group
Page generated in 0.046 sec. using MySQL 8.4.7-u6-cloud
Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the
respective copyright holders.
It is not reviewed in advance
by Oracle and does not necessarily represent the opinion of
Oracle or any other party.
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)