MySQL Bugs: #77742: join query returns in-correct result

Bug #77742	join query returns in-correct result
Submitted:	15 Jul 2015 22:02	Modified:	10 Sep 2015 9:16
Reporter:	Son Nguyen	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.7.7	OS:	Any
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
Hi,

I'm not sure if this issue is similar the issue #77632.  I have two separate queries, when joining them with or without WHERE clause, the result returned is not correct.

1) query 1 with data as below
select 
`PRODUCT`.`CATEGORY` as `C0` , 
count(distinct `PRODUCT`.`CATEGORY`) as `C1` , 
sum(`ORDER_FACTS`.`QUANTITY_ORDERED`) as `C2`
from `PRODUCT` `PRODUCT` 
INNER JOIN `ORDER_FACTS` `ORDER_FACTS` 
on `PRODUCT`.`PRODUCT_KEY` = `ORDER_FACTS`.`PRODUCT_KEY`
group by `PRODUCT`.`CATEGORY`

Get Data All:
"C0", "C1", "C2"
"Home Appliances", 1, 1040100
"Kitchen Appliances", 1, 266180
2 rows fetched from 3 columns.

2) query 2 with data as below
select 
`PRODUCT`.`CATEGORY` as `C0` , 
`PRODUCT`.`PRODUCT` as `C1` , 
sum(`ORDER_FACTS`.`QUANTITY_ORDERED`) as `C2`
from `PRODUCT` `PRODUCT` 
INNER JOIN `ORDER_FACTS` `ORDER_FACTS` 
on `PRODUCT`.`PRODUCT_KEY` = `ORDER_FACTS`.`PRODUCT_KEY`
group by `PRODUCT`.`CATEGORY`, `PRODUCT`.`PRODUCT`
Get Data All:
"C0", "C1", "C2"
"Home Appliances", "Dryer", 1000000
"Home Appliances", "Refrigerator", 40000
"Home Appliances", "Washer", 100
"Kitchen Appliances", "Blender", 60
"Kitchen Appliances", "Can Opener", 25000
"Kitchen Appliances", "Deluxe Coffee Maker", 20000
"Kitchen Appliances", "Standard Coffee Maker", 1000
"Kitchen Appliances", "Thermal Coffee Maker", 220000
"Kitchen Appliances", "Toaster", 40
"Kitchen Appliances", "Toaster Oven", 80
10 rows fetched from 3 columns.

3) now join two queries, The null values are incorrectly returned for T1.C2
select 
`T1`.`C0`,
`T1`.`C1`,
`T1`.`C2`,
`T2`.`C0`,
`T2`.`C1`,
`T2`.`C2`
from
(
select 
`PRODUCT`.`CATEGORY` as `C0` , 
count(distinct `PRODUCT`.`CATEGORY`) as `C1` , 
sum(`ORDER_FACTS`.`QUANTITY_ORDERED`) as `C2`
from `PRODUCT` `PRODUCT` 
INNER JOIN `ORDER_FACTS` `ORDER_FACTS` 
on `PRODUCT`.`PRODUCT_KEY` = `ORDER_FACTS`.`PRODUCT_KEY`
group by `PRODUCT`.`CATEGORY`
) `T1`,

(
select 
`PRODUCT`.`CATEGORY` as `C0` , 
`PRODUCT`.`PRODUCT` as `C1` , 
sum(`ORDER_FACTS`.`QUANTITY_ORDERED`) as `C2`
from `PRODUCT` `PRODUCT` 
INNER JOIN `ORDER_FACTS` `ORDER_FACTS` 
on `PRODUCT`.`PRODUCT_KEY` = `ORDER_FACTS`.`PRODUCT_KEY`
group by `PRODUCT`.`CATEGORY`, `PRODUCT`.`PRODUCT`
) `T2`
where `T2`.`C0` = `T1`.`C0`

Get Data All:
"C0", "C1", "C2", "C0", "C1", "C2"
"Home Appliances", 1, <Null>, "Home Appliances", "Refrigerator", 40000
"Home Appliances", 1, <Null>, "Home Appliances", "Washer", 100
"Home Appliances", 1, <Null>, "Home Appliances", "Dryer", 1000000
"Kitchen Appliances", 1, <Null>, "Kitchen Appliances", "Deluxe Coffee Maker", 20000
"Kitchen Appliances", 1, <Null>, "Kitchen Appliances", "Standard Coffee Maker", 1000
"Kitchen Appliances", 1, <Null>, "Kitchen Appliances", "Thermal Coffee Maker", 220000
"Kitchen Appliances", 1, <Null>, "Kitchen Appliances", "Blender", 60
"Kitchen Appliances", 1, <Null>, "Kitchen Appliances", "Toaster", 40
"Kitchen Appliances", 1, <Null>, "Kitchen Appliances", "Can Opener", 25000
"Kitchen Appliances", 1, <Null>, "Kitchen Appliances", "Toaster Oven", 80
10 rows fetched from 6 columns.

I have no issue with MySQL 5.6.  The behavior is introduced in MySQL 5.7.7.

Here are database and driver versions I'm using
SQL_DBMS_NAME=17, 10, "MySQL"
SQL_DBMS_VER=18, 24, "5.7.7-rc-log"
SQL_DRIVER_NAME=6, 24, "myodbc5a.dll"
SQL_DRIVER_VER=7, 20, "05.02.0004"

Thanks

Son Nguyen

How to repeat:

Run the queries above using MS ODBCTest tool or other ODBC program.

Hello Son Nguyen,

Thank you for the bug report.
"ORDER_FACTS" table missing in provided dump file. Could you please provide table script and data with us to confirm this issue at our end?

Thanks,
Chiranjeevi.

Hi Chiranjeevi,

Actually the query runs against our QESTAR database, not GOSALES1.

I will provide dumpfile of QESTAR database soon.

Thanks

Son Nguyen

DDL for QESTAR database

Attachment: mysql_5.7.7_qestar.sql (application/octet-stream, text), 30.43 KiB.

Hello Son Nguyen,

Thank you for your feedback and dmp file.
I'm not seeing this issue with MySQL 5.7.8 builds and it is most likely fixed, please try with 5.7.8 and let us know if you are still having the problem.

Thanks,
Chiranjeevi.

Results:

Enter password: ******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.7.8-rc-enterprise-commercial-advanced-log MySQL Enterprise Server - Advanced Edition (Commercial)

Copyright (c) 2000, 2015, 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> use test
Database changed
mysql> select `PRODUCT`.`CATEGORY` as `C0` , count(distinct `PRODUCT`.`CATEGORY`) as `C1` , sum(`ORDER_FACTS`.`QUANTITY_ORDERED`) as `C2`fro
m `PRODUCT` `PRODUCT` INNER JOIN `ORDER_FACTS` `ORDER_FACTS` on `PRODUCT`.`PRODUCT_KEY` = `ORDER_FACTS`.`PRODUCT_KEY`group by `PRODUCT`.`CAT
EGORY`;
+--------------------+----+---------+
| C0                 | C1 | C2      |
+--------------------+----+---------+
| Home Appliances    |  1 | 1040100 |
| Kitchen Appliances |  1 |  266180 |
+--------------------+----+---------+
2 rows in set (0.01 sec)

mysql> select `PRODUCT`.`CATEGORY` as `C0` , `PRODUCT`.`PRODUCT` as `C1` , sum(`ORDER_FACTS`.`QUANTITY_ORDERED`) as `C2`from `PRODUCT` `PROD
UCT` INNER JOIN `ORDER_FACTS` `ORDER_FACTS` on `PRODUCT`.`PRODUCT_KEY` = `ORDER_FACTS`.`PRODUCT_KEY`group by `PRODUCT`.`CATEGORY`, `PRODUCT`
.`PRODUCT`;
+--------------------+-----------------------+---------+
| C0                 | C1                    | C2      |
+--------------------+-----------------------+---------+
| Home Appliances    | Dryer                 | 1000000 |
| Home Appliances    | Refrigerator          |   40000 |
| Home Appliances    | Washer                |     100 |
| Kitchen Appliances | Blender               |      60 |
| Kitchen Appliances | Can Opener            |   25000 |
| Kitchen Appliances | Deluxe Coffee Maker   |   20000 |
| Kitchen Appliances | Standard Coffee Maker |    1000 |
| Kitchen Appliances | Thermal Coffee Maker  |  220000 |
| Kitchen Appliances | Toaster               |      40 |
| Kitchen Appliances | Toaster Oven          |      80 |
+--------------------+-----------------------+---------+
10 rows in set (0.00 sec)

mysql> select `T1`.`C0`,`T1`.`C1`,`T1`.`C2`,`T2`.`C0`,`T2`.`C1`,`T2`.`C2` from(select `PRODUCT`.`CATEGORY` as `C0` , count(distinct `PRODUCT
`.`CATEGORY`) as `C1` , sum(`ORDER_FACTS`.`QUANTITY_ORDERED`) as `C2`from `PRODUCT` `PRODUCT` INNER JOIN `ORDER_FACTS` `ORDER_FACTS` on `PRO
DUCT`.`PRODUCT_KEY` = `ORDER_FACTS`.`PRODUCT_KEY`group by `PRODUCT`.`CATEGORY`) `T1`,(select `PRODUCT`.`CATEGORY` as `C0` , `PRODUCT`.`PRODU
CT` as `C1` , sum(`ORDER_FACTS`.`QUANTITY_ORDERED`) as `C2`from `PRODUCT` `PRODUCT` INNER JOIN `ORDER_FACTS` `ORDER_FACTS` on `PRODUCT`.`PRO
DUCT_KEY` = `ORDER_FACTS`.`PRODUCT_KEY`group by `PRODUCT`.`CATEGORY`, `PRODUCT`.`PRODUCT`) `T2` where `T2`.`C0` = `T1`.`C0`;
+--------------------+----+---------+--------------------+-----------------------+---------+
| C0                 | C1 | C2      | C0                 | C1                    | C2      |
+--------------------+----+---------+--------------------+-----------------------+---------+
| Home Appliances    |  1 | 1040100 | Home Appliances    | Dryer                 | 1000000 |
| Home Appliances    |  1 | 1040100 | Home Appliances    | Refrigerator          |   40000 |
| Home Appliances    |  1 | 1040100 | Home Appliances    | Washer                |     100 |
| Kitchen Appliances |  1 |  266180 | Kitchen Appliances | Blender               |      60 |
| Kitchen Appliances |  1 |  266180 | Kitchen Appliances | Toaster               |      40 |
| Kitchen Appliances |  1 |  266180 | Kitchen Appliances | Can Opener            |   25000 |
| Kitchen Appliances |  1 |  266180 | Kitchen Appliances | Toaster Oven          |      80 |
| Kitchen Appliances |  1 |  266180 | Kitchen Appliances | Deluxe Coffee Maker   |   20000 |
| Kitchen Appliances |  1 |  266180 | Kitchen Appliances | Standard Coffee Maker |    1000 |
| Kitchen Appliances |  1 |  266180 | Kitchen Appliances | Thermal Coffee Maker  |  220000 |
+--------------------+----+---------+--------------------+-----------------------+---------+
10 rows in set (0.01 sec)

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".