[MDEV-8706] Wrong result for SELECT..WHERE time_column=TIMESTAMP'2015-08-30 00:00:00' AND time_column='00:00:00' - Jira

XML

Word

Printable

In this script:

SET timestamp=UNIX_TIMESTAMP('2015-08-30 10:20:30');

DROP TABLE IF EXISTS t1;

CREATE TABLE t1 (a TIME);

INSERT INTO t1 VALUES ('00:00:00');

SELECT * FROM t1 WHERE a=TIMESTAMP'2015-08-30 00:00:00';

SELECT * FROM t1 WHERE a='00:00:00';

SELECT * FROM t1 WHERE a=TIMESTAMP'2015-08-30 00:00:00' AND a='00:00:00';

SELECT * FROM t1 WHERE a>=TIMESTAMP'2015-08-30 00:00:00' AND a='00:00:00';

the first and the second SELECT queries correctly return one row.
The third and the fourth SELECT query erroneously return empty set.

If I rewrite the third query to use TIME literal:

SELECT * FROM t1 WHERE a=TIMESTAMP'2015-08-30 00:00:00' AND a=TIME'00:00:00';

it correctly returns one row.

Another example:

SET timestamp=UNIX_TIMESTAMP('2015-08-30 10:20:30');

DROP TABLE IF EXISTS t1;

CREATE TABLE t1 (a DATE);

INSERT INTO t1 VALUES ('2015-08-30'),('2015-08-31');

SELECT * FROM t1 WHERE a=TIME'00:00:00';

SELECT * FROM t1 WHERE LENGTH(a)=10;

SELECT * FROM t1 WHERE LENGTH(a)=10 AND a=TIME'00:00:00';

correctly returns one row in the first SELECT and both rows in the second SELECT, but erroneously returns empty set in the third SELECT.

The reason is that Item_equal remembers a wrong equal constant. In case if time_column=<const> equality it should remember a constant of TIME type.

blocks

MDEV-8728 Fix a number of problems in equal field and equal expression propagation

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.