MySQL Bugs: #80244: "Using index for group-by" queries can return no data with LE/LEQ and rounding

Bug #80244	"Using index for group-by" queries can return no data with LE/LEQ and rounding
Submitted:	2 Feb 2016 23:30	Modified:	10 May 2016 17:03
Reporter:	Andrew Bloomgarden (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S2 (Serious)
Version:	5.7.10, but at least as far back as 5.6	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	Contribution

Description:
Given a table like:

CREATE TABLE t1 (a INT, b INT, KEY(a,b));

If you create a group by query that uses max(b) and can be optimized to only hit the index, it's possible for the optimizer to look for the wrong data in the index and then end up returning no data. Specifically, with a query like:

SELECT a, max(b) FROM t1 WHERE a = 1 and b < 1999.5 GROUP BY a;

and a row (1, 2000) in the table, the max(b) found internally will be 2000, but since that doesn't actually satisfy the WHERE clause it's stripped out from the final result. The same is true for <=.

As far as I can tell the code that the patch changes dates back to at least 2000, but I'm not clear if the bug dates back that long since I can't easily tell how it was used then.

How to repeat:
See attached test suite.

Suggested fix:
I think the attached patch will do the trick, and as far as I can tell it doesn't break the main suite.

Patch including now-passing test

Attachment: 0001-Fix-group-by-max-when-using-or.patch (application/octet-stream, text), 15.06 KiB.

I've signed the OCA, but I suppose I'm waiting for it to go through before I can upload the contribution officially.

Hello Andrew,

Thank you for the bug report and contribution.
Verified as described.

As a side note(pls ignore if already done) - in order to submit contributions you must first sign the Oracle Contribution Agreement (OCA). For additional information please check http://www.oracle.com/technetwork/community/oca-486395.html.
If you have any questions, please contact the MySQL community team.

Thanks,
Umesh

-- 5.7.10

bin/mysql_install_db --insecure --basedir=/export/umesh/server/binaries/mysql-advanced-5.7.10 --datadir=/export/umesh/server/binaries/mysql-advanced-5.7.10/80244 -v
bin/mysqld --basedir=/export/umesh/server/binaries/mysql-advanced-5.7.10 --datadir=/export/umesh/server/binaries/mysql-advanced-5.7.10/80244 --core-file --socket=/tmp/mysql_ushastry.sock  --port=15000 --log-error=/export/umesh/server/binaries/mysql-advanced-5.7.10/80244/log.err 2>&1 &

DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a INT, b INT, KEY(a,b));
INSERT INTO t1 VALUES (1,1000);
INSERT INTO t1 VALUES (1,1001);
INSERT INTO t1 VALUES (1,2000);
INSERT INTO t1 VALUES (1,3000);
INSERT INTO t1 VALUES (1,3002);
SELECT a, max(b) FROM t1 WHERE a = 1 and b < 1999.5 GROUP BY a;

[umshastr@hod03]/export/umesh/server/binaries/mysql-advanced-5.7.10: bin/mysql -uroot -S /tmp/mysql_ushastry.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.10-enterprise-commercial-advanced 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> create database test;
Query OK, 1 row affected (0.00 sec)

mysql> use test
Database changed
mysql> DROP TABLE IF EXISTS t1;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE TABLE t1 (a INT, b INT, KEY(a,b));
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO t1 VALUES (1,1000);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t1 VALUES (1,1001);
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO t1 VALUES (1,2000);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t1 VALUES (1,3000);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t1 VALUES (1,3002);
Query OK, 1 row affected (0.00 sec)

mysql> \q
Bye
[umshastr@hod03]/export/umesh/server/binaries/mysql-advanced-5.7.10:
[umshastr@hod03]/export/umesh/server/binaries/mysql-advanced-5.7.10: bin/mysql -uroot -S /tmp/mysql_ushastry.sock test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.10-enterprise-commercial-advanced 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> set sql_mode=DEFAULT;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'sql_mode';
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+
| Variable_name | Value                                                                                                                                     |
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+
| sql_mode      | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM t1;
+------+------+
| a    | b    |
+------+------+
|    1 | 1000 |
|    1 | 1001 |
|    1 | 2000 |
|    1 | 3000 |
|    1 | 3002 |
+------+------+
5 rows in set (0.00 sec)

-- imho there are 2 rows with b < 1999.5, so it should list rows but nothing returned

mysql> SELECT a, max(b) FROM t1 WHERE a = 1 and b < 1999.5 GROUP BY a;
Empty set (0.02 sec)

mysql> explain SELECT a, max(b) FROM t1 WHERE a = 1 and b < 1999.5 GROUP BY a;
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+---------------------------------------+
| id | select_type | table | partitions | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                 |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+---------------------------------------+
|  1 | SIMPLE      | t1    | NULL       | range | a             | a    | 10      | NULL |    1 |   100.00 | Using where; Using index for group-by |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+---------------------------------------+
1 row in set, 1 warning (0.01 sec)

-- This works

mysql> SELECT a, max(b) FROM t1 ignore index(a) WHERE a = 1 and b < 1999.5 GROUP BY a;
+------+--------+
| a    | max(b) |
+------+--------+
|    1 |   1001 |
+------+--------+
1 row in set (0.00 sec)

mysql>

Patch including now-passing test (*) I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.

Contribution: 0001-Fix-group-by-max-when-using-or.patch (application/octet-stream, text), 15.06 KiB.

Adding Contribution tag to try to follow the process better, from https://community.oracle.com/docs/DOC-914911

Thank you for the patch. Patch looks good. However it needs a bit more analysis and testing from our side.

Hi Chaithra, any update on this? Is the analysis looking good?

Thanks,
Andrew

Hello Andrew,

I have the patch in review. Your analysis looks correct. We will update you once the patch gets approved.

Thanks,
Chaithra

Posted by developer:
 
Noted in 5.8.0 changelog.

If rounding occurred while storing a predicate value, the range
optimizer might not return correct results for the < and <=
operators.

BUG#98826 is a regression of this bug. No change in this bug's status.