| Bug #69233 | inconsistant results when odbc date format is used | ||
|---|---|---|---|
| Submitted: | 14 May 2013 17:08 | Modified: | 31 Oct 2014 16:07 |
| Reporter: | Jason Rider | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S1 (Critical) |
| Version: | 5.6.11 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | regression | ||
[14 May 2013 17:09]
Jason Rider
table definitions and data
Attachment: dump_5_6.sql (application/octet-stream, text), 1.56 MiB.
[14 May 2013 17:13]
Jason Rider
I have also tested this on versions 5.1.56, 5.1.69, 5.5.21, 5.6.10. Only 5.6.10 has the issue. Lower versions all return the correct results.
[15 May 2013 11:09]
MySQL Verification Team
Hello Jason, Thank you for the report. Verified as described on reported and later versions. Thanks, Umesh
[15 May 2013 11:09]
MySQL Verification Team
## 5.6.3 - not affected
mysql> select version();
+--------------+
| version() |
+--------------+
| 5.6.3-m6-log |
+--------------+
1 row in set (0.00 sec)
mysql> SELECT COUNT(*) FROM policy_entity pe LEFT JOIN customer a ON a.customerId = pe.id LEFT JOIN customer c ON c.customerId = pe.currentVersionNum WHERE pe.credate BETWEEN {d'2012-01-01'} AND {d'2012-01-31'};
+----------+
| COUNT(*) |
+----------+
| 13062 |
+----------+
1 row in set (0.05 sec)
mysql> SELECT COUNT(*) FROM policy_entity pe LEFT JOIN customer a ON a.customerId = pe.id LEFT JOIN customer c ON c.customerId = pe.currentVersionNum WHERE pe.credate BETWEEN '2012-01-01' AND '2012-01-31';
+----------+
| COUNT(*) |
+----------+
| 13062 |
+----------+
1 row in set (0.06 sec)
[15 May 2013 11:11]
MySQL Verification Team
### 5.6.11 - affected
mysql> select version();
+------------------+
| version() |
+------------------+
| 5.6.11-debug-log |
+------------------+
1 row in set (0.00 sec)
mysql> source /home/ushastry/mybuilds/mysql-5.6.11-release/load.txt;
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.47 sec)
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.17 sec)
Query OK, 14355 rows affected (5.32 sec)
Records: 14355 Duplicates: 0 Warnings: 0
Query OK, 0 rows affected (0.02 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 COUNT(*) FROM policy_entity pe LEFT JOIN customer a ON a.customerId = pe.id LEFT JOIN customer c ON c.customerId = pe.currentVersionNum WHERE pe.credate BETWEEN {d'2012-01-01'} AND {d'2012-01-31'};
+----------+
| COUNT(*) |
+----------+
| 0 |
+----------+
1 row in set (0.02 sec)
mysql> explain SELECT COUNT(*) FROM policy_entity pe LEFT JOIN customer a ON a.customerId = pe.id LEFT JOIN customer c ON c.customerId = pe.currentVersionNum WHERE pe.credate BETWEEN
+----+-------------+-------+--------+---------------------------------+------------+---------+-------------------+------+----------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+---------------------------------+------------+---------+-------------------+------+----------------------------------------------------+
| 1 | SIMPLE | a | index | PRIMARY | CUSTOMER37 | 5 | NULL | 2 | Using where; Using index |
| 1 | SIMPLE | c | index | PRIMARY | CUSTOMER37 | 5 | NULL | 2 | Using index; Using join buffer (Block Nested Loop) |
| 1 | SIMPLE | pe | eq_ref | PRIMARY,POLICY_ENTITY40,credate | PRIMARY | 4 | test.a.customerId | 1 | Using where |
+----+-------------+-------+--------+---------------------------------+------------+---------+-------------------+------+----------------------------------------------------+
3 rows in set (0.00 sec)
mysql> SELECT COUNT(*) FROM policy_entity pe LEFT JOIN customer a ON a.customerId = pe.id LEFT JOIN customer c ON c.customerId = pe.currentVersionNum WHERE pe.credate BETWEEN '2012-01-01' AND '2012-01-31';
+----------+
| COUNT(*) |
+----------+
| 13062 |
+----------+
1 row in set (1.24 sec)
mysql> explain SELECT COUNT(*) FROM policy_entity pe LEFT JOIN customer a ON a.customerId = pe.id LEFT JOIN customer c ON c.customerId = pe.currentVersionNum WHERE pe.credate BETWEEN
+----+-------------+-------+--------+---------------+---------+---------+---------------------------+-------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+---------------+---------+---------+---------------------------+-------+-------------+
| 1 | SIMPLE | pe | ALL | credate | NULL | NULL | NULL | 14408 | Using where |
| 1 | SIMPLE | a | eq_ref | PRIMARY | PRIMARY | 4 | test.pe.id | 1 | Using index |
| 1 | SIMPLE | c | eq_ref | PRIMARY | PRIMARY | 4 | test.pe.currentVersionNum | 1 | Using index |
+----+-------------+-------+--------+---------------+---------+---------+---------------------------+-------+-------------+
3 rows in set (0.00 sec)
mysql> show global variables like 'sql_mode';
+---------------+--------------------------------------------+
| Variable_name | Value |
+---------------+--------------------------------------------+
| sql_mode | STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION |
+---------------+--------------------------------------------+
1 row in set (0.01 sec)
[15 May 2013 11:12]
MySQL Verification Team
### 5.5.31 - not affected
mysql> select version();
+------------------+
| version() |
+------------------+
| 5.5.31-debug-log |
+------------------+
1 row in set (0.00 sec)
mysql> source /home/ushastry/mybuilds/mysql-5.5.31/load.txt;
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.08 sec)
Query OK, 0 rows affected (0.05 sec)
Query OK, 2 rows affected (0.02 sec)
Records: 2 Duplicates: 0 Warnings: 0
Query OK, 0 rows affected (0.02 sec)
Query OK, 0 rows affected (0.04 sec)
Query OK, 14355 rows affected (3.56 sec)
Records: 14355 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)
mysql> SELECT COUNT(*) FROM policy_entity pe LEFT JOIN customer a ON a.customerId = pe.id LEFT JOIN customer c ON c.customerId = pe.currentVersionNum WHERE pe.credate BETWEEN {d'2012-01-01'} AND {d'2012-01-31'};
+----------+
| COUNT(*) |
+----------+
| 13062 |
+----------+
1 row in set (0.48 sec)
mysql> SELECT COUNT(*) FROM policy_entity pe LEFT JOIN customer a ON a.customerId = pe.id LEFT JOIN customer c ON c.customerId = pe.currentVersionNum WHERE pe.credate BETWEEN '2012-01-01' AND '2012-01-31';
+----------+
| COUNT(*) |
+----------+
| 13062 |
+----------+
1 row in set (0.46 sec)
mysql> show global variables like 'sql_mode';
+---------------+--------------------------------------------+
| Variable_name | Value |
+---------------+--------------------------------------------+
| sql_mode | STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION |
+---------------+--------------------------------------------+
1 row in set (0.00 sec)
mysql>
### 5.7.1-m11 - affected
mysql> select version();
+-----------------+
| version() |
+-----------------+
| 5.7.1-m11-debug |
+-----------------+
1 row in set (0.00 sec)
mysql> source /home/ushastry/mybuilds/mysql-5.7.1/load.txt;
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.64 sec)
Query OK, 2 rows affected (0.04 sec)
Records: 2 Duplicates: 0 Warnings: 0
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.59 sec)
Query OK, 14355 rows affected (7.19 sec)
Records: 14355 Duplicates: 0 Warnings: 0
Query OK, 0 rows affected (0.02 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 COUNT(*) FROM policy_entity pe LEFT JOIN customer a ON a.customerId = pe.id LEFT JOIN customer c ON c.customerId = pe.currentVersionNum WHERE pe.credate BETWEEN {d'2012-01-01'} AND {d'2012-01-31'};
+----------+
| COUNT(*) |
+----------+
| 0 |
+----------+
1 row in set (0.03 sec)
mysql> SELECT COUNT(*) FROM policy_entity pe LEFT JOIN customer a ON a.customerId = pe.id LEFT JOIN customer c ON c.customerId = pe.currentVersionNum WHERE pe.credate BETWEEN '2012-01-01' AND '2012-01-31';
+----------+
| COUNT(*) |
+----------+
| 13062 |
+----------+
1 row in set (1.26 sec)
mysql> show global variables like 'sql_mode';
+---------------+--------------------------------------------+
| Variable_name | Value |
+---------------+--------------------------------------------+
| sql_mode | STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION |
+---------------+--------------------------------------------+
1 row in set (0.01 sec)
[15 May 2013 11:12]
MySQL Verification Team
### 5.6.12 - affected
mysql> select version();
+------------------+
| version() |
+------------------+
| 5.6.12-debug-log |
+------------------+
1 row in set (0.00 sec)
mysql> use test
Database changed
mysql> source /home/ushastry/mybuilds/mysql-5.6.12-release/load.txt;
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 (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.06 sec)
Query OK, 0 rows affected (0.38 sec)
Query OK, 2 rows affected (0.04 sec)
Records: 2 Duplicates: 0 Warnings: 0
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.14 sec)
Query OK, 14355 rows affected (4.45 sec)
Records: 14355 Duplicates: 0 Warnings: 0
Query OK, 0 rows affected (0.02 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 COUNT(*) FROM policy_entity pe LEFT JOIN customer a ON a.customerId = pe.id LEFT JOIN customer c ON c.customerId = pe.currentVersionNum WHERE pe.credate BETWEEN {d'2012-01-01'} AND {d'2012-01-31'};
+----------+
| COUNT(*) |
+----------+
| 0 |
+----------+
1 row in set (0.02 sec)
mysql> SELECT COUNT(*) FROM policy_entity pe LEFT JOIN customer a ON a.customerId = pe.id LEFT JOIN customer c ON c.customerId = pe.currentVersionNum WHERE pe.credate BETWEEN '2012-01-01' AND '2012-01-31';
+----------+
| COUNT(*) |
+----------+
| 13062 |
+----------+
1 row in set (0.73 sec)
mysql> show global variables like 'sql_mode';
+---------------+--------------------------------------------+
| Variable_name | Value |
+---------------+--------------------------------------------+
| sql_mode | STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION |
+---------------+--------------------------------------------+
1 row in set (0.00 sec)
mysql>
[30 Oct 2013 6:21]
MySQL Verification Team
Bug #70761 marked as duplicate of this one.
[6 May 2014 19:16]
Jerry Fowler
This appears to happen also on the Windows platform. Any estimates on a correction?
[23 Sep 2014 9:54]
Lukas Eder
This seems like a rather severe issue, which was just now reported also on the jOOQ user group: https://groups.google.com/forum/#!topic/jooq-user/eXkHOFTbUZs While we could probably work around this issue from within jOOQ, I wonder if this could be prioritised a bit higher? not sure what's higher than "Serious" and "Critical", though :-)
[31 Oct 2014 16:07]
Paul DuBois
Noted in 5.6.22, 5.7.6 changelogs. Use of ODBC-format date literals could produce incorrect query results.
[10 Dec 2014 14:19]
Laurynas Biveinis
$ bzr log -r 6227
------------------------------------------------------------
revno: 6227
committer: mithun <mithun.c.y@oracle.com>
branch nick: mysql-5.6
timestamp: Wed 2014-10-29 14:08:10 +0530
message:
BUG #16812821: INCONSISTANT RESULTS WHEN ODBC DATE FORMAT IS
USED
Issue :
-------
Temporal literals which are in ODBC STYLE are considered as
data/time constants. For such items we have used_tables as 0
but did not initialize not_null_tables. not_null_tables is
now pointing to some junk table_map values. While in
simplify_joins the conditions with temporal literals were
wrongly assumed to filter nulls in inner table of outer
joins. This is because not_null_tables is not initialized.
So some of the outer joins are converted to inner joins.
This results in invalid result row set.
Solution:
---------
For class Item_date_literal, Item_time_literal and
Item_datetime_literal set not_null_tables as used_tables().
Description: We have found that the results of a query that uses odbc date format ({d 'yyyy-mm-dd} or DATE 'yyyy-mm-dd') will produce inconsistant results. There may be other cases but we have reproduced the issue with a query that uses multiple left joins. When the odbc format is used one of the left joins will be treated as an inner join. The appears to be related to the factional time change introduced in version 5.6.4 where the temporal key word / format is no longer ignored. http://dev.mysql.com/doc/refman/5.6/en/date-and-time-literals.html It is data dependent though, and is not always repeatable. I believe that it may be due to how the optimizer calculates the cost and execution plan of the query. How to repeat: Using a 2 left joins to a single table and use the Standard SQL or odbc format for date selection. For example: SELECT COUNT(*) FROM policy_entity pe LEFT JOIN customer a ON a.customerId = pe.id LEFT JOIN customer c ON c.customerId = pe.currentVersionNum WHERE pe.credate BETWEEN {d'2012-01-01'} AND {d'2012-01-31'}; The above query most of the time will return a zero count as there are no matching rows in the customer table. The query below will return the correct count from the policy_entity table. SELECT COUNT(*) FROM policy_entity pe LEFT JOIN customer a ON a.customerId = pe.id LEFT JOIN customer c ON c.customerId = pe.currentVersionNum WHERE pe.credate BETWEEN '2012-01-01' AND '2012-01-31'; I will attach a dump with table definitions and data. Here are the exact steps to repeat using the dump: root@debian:~/mysql# make_sandbox mysql-5.6.11-linux-glibc2.5-i686.tar.gz root@debian:~/mysql# cd $HOME/sandboxes/msb_5_6_11 root@debian:~/sandboxes/msb_5_6_11# vi my.sandbox.cnf -- add max_allowed_packet=128M root@debian:~/sandboxes/msb_5_6_11# ./restart root@debian:~/sandboxes/msb_5_6_11# ./use mysql [localhost] {msandbox} ((none)) > use test; mysql [localhost] {msandbox} (test) > source /root/dump_5_6.sql; mysql [localhost] {msandbox} (test) > SELECT COUNT(*) FROM policy_entity pe LEFT JOIN customer a ON a.customerId = pe.id LEFT JOIN customer c ON c.customerId = pe.currentVersionNum WHERE pe.credate BETWEEN {d'2012-01-01'} AND {d'2012-01-31'}; +----------+ | COUNT(*) | +----------+ | 0 | +----------+ 1 row in set (0.00 sec) mysql [localhost] {msandbox} (test) > SELECT COUNT(*) FROM policy_entity pe LEFT JOIN customer a ON a.customerId = pe.id LEFT JOIN customer c ON c.customerId = pe.currentVersionNum WHERE pe.credate BETWEEN '2012-01-01' AND '2012-01-31'; +----------+ | COUNT(*) | +----------+ | 13062 | +----------+ 1 row in set (0.10 sec) -- test in 5.6.3 root@debian:~/mysql# make_sandbox mysql-5.6.3-m6-linux2.6-i686.tar.gz root@debian:~/mysql# cd $HOME/sandboxes/msb_5_6_3 root@debian:~/sandboxes/msb_5_6_3# vi my.sandbox.cnf -- add max_allowed_packet=128M root@debian:~/sandboxes/msb_5_6_3# ./restart root@debian:~/sandboxes/msb_5_6_3# ./use mysql [localhost] {msandbox} ((none)) > use test; mysql [localhost] {msandbox} (test) > source /root/dump_5_6.sql; mysql [localhost] {msandbox} (test) > SELECT COUNT(*) FROM policy_entity pe LEFT JOIN customer a ON a.customerId = pe.id LEFT JOIN customer c ON c.customerId = pe.currentVersionNum WHERE pe.credate BETWEEN {d'2012-01-01'} AND {d'2012-01-31'}; +----------+ | COUNT(*) | +----------+ | 13062 | +----------+ 1 row in set (0.09 sec) mysql [localhost] {msandbox} (test) > SELECT COUNT(*) FROM policy_entity pe LEFT JOIN customer a ON a.customerId = pe.id LEFT JOIN customer c ON c.customerId = pe.currentVersionNum WHERE pe.credate BETWEEN '2012-01-01' AND '2012-01-31'; +----------+ | COUNT(*) | +----------+ | 13062 | +----------+ 1 row in set (0.08 sec)