MySQL Bugs: #98942: Inconsistent results for the queries which should return same result

Bug #98942	Inconsistent results for the queries which should return same result
Submitted:	13 Mar 2020 11:53	Modified:	13 Mar 2020 23:20
Reporter:	Andrews B A	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	8.0.19	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
In some cases we see some select queries returning wrong results. We are seeing these cases repeat when there are the combination of these two are present,

1.Some function on the datetime column
2.cast on some varchar column in the table.

How to repeat:
CREATE TABLE `Sales` ( `SalesDT` datetime DEFAULT NULL, `ProductType` varchar(100) DEFAULT NULL,`Prize` double DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8

After loading the data in this link
https://drive.google.com/open?id=1oyK8pHc0hZFnJGjCKA2-r8OSbfXVtU1F

The below select quries

--------------------------------------------------------------------
1

select sum("Prize") from "Sales" ---> 68500.0

--------------------------------------------------------------------
2

SELECT sum(t.s)
FROM (	SELECT
			 month("SalesDT"),
			 sum("Prize") s,
			 cast("ProductType" as Binary)
	FROM  "Sales" 
	GROUP BY 1,
		  3
) t                             ---> 79500.0

-------------------------------------------------------------------

The result of the second query should have been same as the first one. One more example,

-------------------------------------------------------------------
3

SELECT sum(t.s)
FROM (	SELECT
			 week("SalesDT",6),
			 sum("Prize") s,
			 cast("ProductType" as Binary)
	FROM  "Sales" 
	GROUP BY 1,
		  3
) t                             ---> 71000.0
-------------------------------------------------------------------

The result of the 2nd and 3rd queries should've been same as the first query for the given data. But they produce wrong results.

Hi Mr. A,

Thank you for your bug report.

However, we do not have sufficient info in order to repeat the behaviour that you are complaining about.

First of all, your file with data is in HTML format. Please, mysqldump that table in the common, SQL format, so that we can try to repeat the behaviour. You can use the above "Files" tab to upload your data.

Next, we do not see why are you using a nested query in the FROM clause, when this entire data can be retrieved with a single query.

Next, you are actually asking for the sum of the sums. This does not necessary has to bring the same results. Next, if you do not CAST() that column, do you get the same result as with a single query ???

We are eagerly awaiting for your feedback, because this truly could be a bug, although not a severe one, since the workaround is so simple.

Hi Mr. A,

My colleague noticed that your DATETIME column values are incorrect. You have not entered these in the correct, standard format. So, please, also correct this problem and try again.

This could truly cause problems with results, since month() and week() can not be calculated properly !!!

If you continue to have problems with correct and standard format, please let us know !!!!

The Dump is taken in 8.0.16, But this is the data that fails to work in 8.0.19

Attachment: MySQLBug.sql (application/sql, text), 2.78 KiB.

Use the aboe dump file to reproduce the issue

Attachment: T_5542000022057953.sql (application/sql, text), 2.09 MiB.

Perform the below queries over the dumpfile I have attached here. Expected result for both the queries should be 4027600. In mysql8.0.16, it returns correct result for both the queries. In mysql8.0.19 it returns wrong result. Seems a bug with Mysql8.0.19 version. Please check and let us know what went wrong. We have used subquery or derived table to demonstrate the issue. Its clear that when we apply group by it is giving some inconsistent result. Please check the issue in mysql8.0.19 environment and suggest a fix or workaround. 

select SUM(TC_1.C_5542000022057967) COL_2 from T_5542000022057953 TC_1;

+---------+
| COL_2   |
+---------+
| 4027600 |
+---------+

select sum(COL_2) from (select week(TC_1.C_5542000022057958,6) COL_1,SUM(TC_1.C_5542000022057967) COL_2,cast(TC_1.C_5542000022057963 as binary) from T_5542000022057953 TC_1 group by 1,3 order by 1,3 limit 1000001 offset 0) a;

+------------+
| sum(COL_2) |
+------------+
|    5285600 |
+------------+

Thank you
Rajesh Kota

Thank you for the feedback. Repeatable with 8.0.19 but not anymore with most recent source server built:
mysql: [Warning] D:\MySQL\MySQL Server 8.0\bin\mysql.exe: ignoring option '--no-beep' due to invalid value ''.
Enter password: **********
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.19 MySQL Community Server - GPL

Copyright (c) 2000, 2020, 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> CREATE DATABASE dummy;
Query OK, 1 row affected (0.01 sec)

mysql> USE dummy;
Database changed
mysql> SOURCE D:/tmp/T_5542000022057953.sql
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.04 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 477 rows affected (0.16 sec)
Records: 477  Duplicates: 0  Warnings: 0

Query OK, 477 rows affected (0.06 sec)
Records: 477  Duplicates: 0  Warnings: 0

Query OK, 44 rows affected (0.01 sec)
Records: 44  Duplicates: 0  Warnings: 0

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> select SUM(TC_1.C_5542000022057967) COL_2 from T_5542000022057953 TC_1;
+---------+
| COL_2   |
+---------+
| 4027600 |
+---------+
1 row in set (0.00 sec)

mysql> select sum(COL_2) from (select week(TC_1.C_5542000022057958,6) COL_1,SUM(TC_1.C_5542000022057967) COL_2,cast(TC_1.C_5542000022057963 as binary) from T_5542000022057953 TC_1 group by 1,3 order by 1,3 limit 1000001 offset 0) a;
+------------+
| sum(COL_2) |
+------------+
|    5285600 |
+------------+
1 row in set (0.00 sec)

mysql>

----------------------------------------------------------------------
d:\dbs>d:\dbs\8.0\bin\mysql -uroot --port=3580 -p --local-infile=1 --prompt="mysql 8.0 > " --default-character-set=utf8mb4
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.0.20 Source distribution BUILT: 2020-FEB-16

Copyright (c) 2000, 2020, 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 8.0 > CREATE DATABASE dummy;
Query OK, 1 row affected (0.01 sec)

mysql 8.0 > USE dummy
Database changed
mysql 8.0 > SOURCE D:/tmp/T_5542000022057953.sql
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.03 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 477 rows affected (0.18 sec)
Records: 477  Duplicates: 0  Warnings: 0

Query OK, 477 rows affected (0.06 sec)
Records: 477  Duplicates: 0  Warnings: 0

Query OK, 44 rows affected (0.01 sec)
Records: 44  Duplicates: 0  Warnings: 0

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql 8.0 > select SUM(TC_1.C_5542000022057967) COL_2 from T_5542000022057953 TC_1;
+---------+
| COL_2   |
+---------+
| 4027600 |
+---------+
1 row in set (0.00 sec)

mysql 8.0 > select sum(COL_2) from (select week(TC_1.C_5542000022057958,6) COL_1,SUM(TC_1.C_5542000022057967) COL_2,cast(TC_1.C_5542000022057963 as binary) from T_5542000022057953 TC_1 group by 1,3 order by 1,3 limit 1000001 offset 0) a;
+------------+
| sum(COL_2) |
+------------+
|    4027600 |
+------------+
1 row in set (0.00 sec)

mysql 8.0 >

Hi,

I have tested this with latest daily build of 8.0.20, and I can't repeat it, just like my colleague.

Thank you and Good to know that this has been fixed in the next immediate version. But currently verion 8.0.20 is not yet for GA. Can I know when is the next GA release from Mysql side? The current GA that is 8.0.19 version is not stable. Expecting the next GA at the earliest possible.

Hi Mr. Kota,

This bug is fixed in 8.0.19, so you can upgrade to it immediately.

Hi,

I have download the mysql8.0.19 version from the below link and tested the issue.

https://dev.mysql.com/get/Downloads/MySQL-8.0/mysql-8.0.19-linux-glibc2.12-x86_64.tar.xz

Issue still not fixed. 

OR can you please share the link to download mysql8.0.19 version with the fix.

Sorry, my mistake.

You will have to wait on 8.0.20 to be published.

At this moment, nobody knows when will that exactly happen. It is yet to receive bug fixes, plus it has to pass a lengthy procedure of testing and building.

You should watch download pages on dev.mysql.com.