Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.0(EOL), 10.1(EOL)
Description
SET timestamp=UNIX_TIMESTAMP('2015-09-11 20:20:20');
|
DROP TABLE IF EXISTS t1;
|
CREATE TABLE t1 (a TIME);
|
INSERT INTO t1 VALUES('10:20:30'),('00:00:00');
|
SELECT * FROM t1 WHERE COALESCE(a)=TIME('00:00:00');
|
SELECT * FROM t1 WHERE COALESCE(a)=DATE('2015-09-11');
|
SELECT * FROM t1 WHERE COALESCE(a)=TIME('00:00:00') AND COALESCE(a)=DATE('2015-09-11');
|
The first and the second SELECT query correctly return one row:
+----------+
|
| a |
|
+----------+
|
| 00:00:00 |
|
+----------+
|
The third query erroneously returns empty set.
EXPLAIN SELECT * FROM t1 WHERE COALESCE(a)=TIME('00:00:00') AND COALESCE(a)=DATE('2015-09-11');
|
returns:
+------+-------------+-------+------+---------------+------+---------+------+------+------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+------+---------------+------+---------+------+------+------------------+
|
| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE |
|
+------+-------------+-------+------+---------------+------+---------+------+------+------------------+
|
The problem happens in Arg_comparator::datetime() called from propagate_cond_constants.
Arg_comparator tries to compare the TIME and the DATE literals and fails.
If I rewrite the query like this:
SELECT * FROM t1 WHERE COALESCE(a)=TIME'00:00:00' AND COALESCE(a)=DATE'2015-09-11';
|
it returns the correct row:
+----------+
|
| a |
|
+----------+
|
| 00:00:00 |
|
+----------+
|
But equal expression propagation is not performed:
EXPLAIN EXTENDED SELECT * FROM t1 WHERE COALESCE(a)=TIME'00:00:00' AND COALESCE(a)=DATE'2015-09-11';
|
SHOW WARNINGS;
|
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| Level | Code | Message |
|
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| Note | 1003 | select `test`.`t1`.`a` AS `a` from `test`.`t1` where ((coalesce(`test`.`t1`.`a`) = TIME'00:00:00') and (coalesce(`test`.`t1`.`a`) = DATE'2015-09-11')) |
|
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------+
|
which also looks like a bug.
Summary: both queries:
SELECT * FROM t1 WHERE COALESCE(a)=TIME('00:00:00') AND COALESCE(a)=DATE('2015-09-11');
|
SELECT * FROM t1 WHERE COALESCE(a)=TIME'00:00:00' AND COALESCE(a)=DATE'2015-09-11';
|
are expected to return one row, and perform equal expression propagation.
- the first query performs propagation but returns a wrong result
- the second query returns a correct result but does not perform propagation
Attachments
Issue Links
- blocks
-
MDEV-8728 Fix a number of problems in equal field and equal expression propagation
-
- Closed
-
Activity
Description |
{code} SET timestamp=UNIX_TIMESTAMP('2015-09-11 20:20:20'); DROP TABLE IF EXISTS t1; CREATE TABLE t1 (a TIME); INSERT INTO t1 VALUES('10:20:30'),('00:00:00'); SELECT * FROM t1 WHERE COALESCE(a)=TIME('00:00:00'); SELECT * FROM t1 WHERE COALESCE(a)=DATE('2015-09-11'); SELECT * FROM t1 WHERE COALESCE(a)=TIME('00:00:00') AND COALESCE(a)=DATE('2015-09-11'); {code} The first and the second SELECT query correctly return one row: {code} +----------+ | a | +----------+ | 00:00:00 | +----------+ {code} The third query erroneously returns empty set. |
{code} SET timestamp=UNIX_TIMESTAMP('2015-09-11 20:20:20'); DROP TABLE IF EXISTS t1; CREATE TABLE t1 (a TIME); INSERT INTO t1 VALUES('10:20:30'),('00:00:00'); SELECT * FROM t1 WHERE COALESCE(a)=TIME('00:00:00'); SELECT * FROM t1 WHERE COALESCE(a)=DATE('2015-09-11'); SELECT * FROM t1 WHERE COALESCE(a)=TIME('00:00:00') AND COALESCE(a)=DATE('2015-09-11'); {code} The first and the second SELECT query correctly return one row: {code} +----------+ | a | +----------+ | 00:00:00 | +----------+ {code} The third query erroneously returns empty set. {code} EXPLAIN SELECT * FROM t1 WHERE COALESCE(a)=TIME('00:00:00') AND COALESCE(a)=DATE('2015-09-11'); {code} returns: {noformat} +------+-------------+-------+------+---------------+------+---------+------+------+------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+-------+------+---------------+------+---------+------+------+------------------+ | 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE | +------+-------------+-------+------+---------------+------+---------+------+------+------------------+ {noformat} |
Description |
{code} SET timestamp=UNIX_TIMESTAMP('2015-09-11 20:20:20'); DROP TABLE IF EXISTS t1; CREATE TABLE t1 (a TIME); INSERT INTO t1 VALUES('10:20:30'),('00:00:00'); SELECT * FROM t1 WHERE COALESCE(a)=TIME('00:00:00'); SELECT * FROM t1 WHERE COALESCE(a)=DATE('2015-09-11'); SELECT * FROM t1 WHERE COALESCE(a)=TIME('00:00:00') AND COALESCE(a)=DATE('2015-09-11'); {code} The first and the second SELECT query correctly return one row: {code} +----------+ | a | +----------+ | 00:00:00 | +----------+ {code} The third query erroneously returns empty set. {code} EXPLAIN SELECT * FROM t1 WHERE COALESCE(a)=TIME('00:00:00') AND COALESCE(a)=DATE('2015-09-11'); {code} returns: {noformat} +------+-------------+-------+------+---------------+------+---------+------+------+------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+-------+------+---------------+------+---------+------+------+------------------+ | 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE | +------+-------------+-------+------+---------------+------+---------+------+------+------------------+ {noformat} |
{code} SET timestamp=UNIX_TIMESTAMP('2015-09-11 20:20:20'); DROP TABLE IF EXISTS t1; CREATE TABLE t1 (a TIME); INSERT INTO t1 VALUES('10:20:30'),('00:00:00'); SELECT * FROM t1 WHERE COALESCE(a)=TIME('00:00:00'); SELECT * FROM t1 WHERE COALESCE(a)=DATE('2015-09-11'); SELECT * FROM t1 WHERE COALESCE(a)=TIME('00:00:00') AND COALESCE(a)=DATE('2015-09-11'); {code} The first and the second SELECT query correctly return one row: {code} +----------+ | a | +----------+ | 00:00:00 | +----------+ {code} The third query erroneously returns empty set. {code} EXPLAIN SELECT * FROM t1 WHERE COALESCE(a)=TIME('00:00:00') AND COALESCE(a)=DATE('2015-09-11'); {code} returns: {noformat} +------+-------------+-------+------+---------------+------+---------+------+------+------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+-------+------+---------------+------+---------+------+------+------------------+ | 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE | +------+-------------+-------+------+---------------+------+---------+------+------+------------------+ {noformat} The problem happens in Arg_comparator::datetime() called from propagate_cond_constants. Arg_comparator tries to compare the TIME and the DATE literals and fails. |
Description |
{code} SET timestamp=UNIX_TIMESTAMP('2015-09-11 20:20:20'); DROP TABLE IF EXISTS t1; CREATE TABLE t1 (a TIME); INSERT INTO t1 VALUES('10:20:30'),('00:00:00'); SELECT * FROM t1 WHERE COALESCE(a)=TIME('00:00:00'); SELECT * FROM t1 WHERE COALESCE(a)=DATE('2015-09-11'); SELECT * FROM t1 WHERE COALESCE(a)=TIME('00:00:00') AND COALESCE(a)=DATE('2015-09-11'); {code} The first and the second SELECT query correctly return one row: {code} +----------+ | a | +----------+ | 00:00:00 | +----------+ {code} The third query erroneously returns empty set. {code} EXPLAIN SELECT * FROM t1 WHERE COALESCE(a)=TIME('00:00:00') AND COALESCE(a)=DATE('2015-09-11'); {code} returns: {noformat} +------+-------------+-------+------+---------------+------+---------+------+------+------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+-------+------+---------------+------+---------+------+------+------------------+ | 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE | +------+-------------+-------+------+---------------+------+---------+------+------+------------------+ {noformat} The problem happens in Arg_comparator::datetime() called from propagate_cond_constants. Arg_comparator tries to compare the TIME and the DATE literals and fails. |
{code} SET timestamp=UNIX_TIMESTAMP('2015-09-11 20:20:20'); DROP TABLE IF EXISTS t1; CREATE TABLE t1 (a TIME); INSERT INTO t1 VALUES('10:20:30'),('00:00:00'); SELECT * FROM t1 WHERE COALESCE(a)=TIME('00:00:00'); SELECT * FROM t1 WHERE COALESCE(a)=DATE('2015-09-11'); SELECT * FROM t1 WHERE COALESCE(a)=TIME('00:00:00') AND COALESCE(a)=DATE('2015-09-11'); {code} The first and the second SELECT query correctly return one row: {code} +----------+ | a | +----------+ | 00:00:00 | +----------+ {code} The third query erroneously returns empty set. {code} EXPLAIN SELECT * FROM t1 WHERE COALESCE(a)=TIME('00:00:00') AND COALESCE(a)=DATE('2015-09-11'); {code} returns: {noformat} +------+-------------+-------+------+---------------+------+---------+------+------+------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+-------+------+---------------+------+---------+------+------+------------------+ | 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE | +------+-------------+-------+------+---------------+------+---------+------+------+------------------+ {noformat} The problem happens in Arg_comparator::datetime() called from propagate_cond_constants. Arg_comparator tries to compare the TIME and the DATE literals and fails. If I rewrite the query like this: {code} SELECT * FROM t1 WHERE COALESCE(a)=TIME'00:00:00' AND COALESCE(a)=DATE'2015-09-11'; {code} it returns the correct row: {noformat} +----------+ | a | +----------+ | 00:00:00 | +----------+ {noformat} But equal expression propagation is not performed: {code} EXPLAIN EXTENDED SELECT * FROM t1 WHERE COALESCE(a)=TIME'00:00:00' AND COALESCE(a)=DATE'2015-09-11'; SHOW WARNINGS; {code} {noformat} +-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------+ | Level | Code | Message | +-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------+ | Note | 1003 | select `test`.`t1`.`a` AS `a` from `test`.`t1` where ((coalesce(`test`.`t1`.`a`) = TIME'00:00:00') and (coalesce(`test`.`t1`.`a`) = DATE'2015-09-11')) | +-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------+ {noformat} which also looks like a bug. Summary: both queries: {code} SELECT * FROM t1 WHERE COALESCE(a)=TIME('00:00:00') AND COALESCE(a)=DATE('2015-09-11'); SELECT * FROM t1 WHERE COALESCE(a)=TIME'00:00:00' AND COALESCE(a)=DATE'2015-09-11'; {code} are expected to return one row, and perform equal expression propagation. - the first query performs propagation but returns a wrong result - the second query returns a correct result but does not perform propagation |
Status | Open [ 1 ] | In Progress [ 3 ] |
Labels | propagation |
Component/s | Temporal Types [ 11000 ] | |
Fix Version/s | 10.1.8 [ 19605 ] | |
Fix Version/s | 10.1 [ 16100 ] | |
Resolution | Fixed [ 1 ] | |
Status | In Progress [ 3 ] | Closed [ 6 ] |
Workflow | MariaDB v3 [ 71530 ] | MariaDB v4 [ 149607 ] |
MySQL-5.7.8 does not support equal expression propagation in this example:
EXPLAIN EXTENDED SELECT * FROM t1 WHERE COALESCE(a)=TIME('00:00:00') AND COALESCE(a)=DATE('2015-09-11');
SHOW WARNINGS;
returns
+---------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Warning | 1681 | 'EXTENDED' is deprecated and will be removed in a future release. |
| Note | 1003 | /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where ((coalesce(`test`.`t1`.`a`) = TIME'00:00:00') and (coalesce(`test`.`t1`.`a`) = DATE'2015-09-11')) |
+---------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------+