Incorrect translation "where" clause with data&time types and stored routines

[mshurutov]

Hi! We found incorrect work queries with a where clause which have conditions with using the date/time and associated with these data types stored routines: PostgreSQL query:

postgres@test=# select * from test where t >=('yesterday'::timestamptz - interval '1 day') and t <= date_trunc('day', now());

is assembling the following query mysql:

SELECT `id`, `t` FROM `test`.`test` WHERE  ((`t` >= ('2016-08-09 00:00:00+03' - '1 day')))

But the query is expected to be following (keyword INTERVAL and notation with this keyword is very important for this request!):

SELECT `id`, `t` FROM `test`.`test` WHERE  ((`t` >= ('2016-08-09 00:00:00+03' - INTERVAL 1 DAY) and `t` <= '2016-08-10 00:00:00+3'

i.e. date_trunc('day', now()) is expected be translated into current date with nulls in time part of datetime string. Or the second part of the query is processed directly to PostgreSQL? PostgreSQL:

postgres@test=# show server_version;
 server_version 
----------------
 9.5.3
(1 строка)

postgres@test=#

Mysql (percona-server):

mysql> show global variables like 'version';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| version       | 5.6.31-77.0-log |
+---------------+-----------------+
1 row in set (0,00 sec)

mysql>

Mysql (MariaDB):

mysql> show global variables like 'version';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| version       | 10.1.16-MariaDB |
+---------------+-----------------+
1 row in set (0,01 sec)

mysql>

[mshurutov]

We did the experiments on other version of PostgreSQLwith same data in MySQL:

postgres@test=# show server_version;
 server_version 
----------------
 9.4.8
(1 строка)

Время: 0,404 мс
postgres@test=# insert into test (t) values ('2015-08-10 11:03:10');
INSERT 0 1
Время: 59,667 мс
postgres@test=# insert into test (t) values ('2015-08-11 11:03:10');
INSERT 0 1
Время: 36,548 мс
postgres@test=# insert into test (t) values ('2015-08-12 11:03:10');
INSERT 0 1
Время: 471,201 мс
postgres@test=# select * from test where t >=('yesterday'::timestamptz - interval '1 day') and t <= date_trunc('hour', now());
 id |          t          
----+---------------------
  1 | 2016-08-10 12:30:40
  2 | 2016-08-10 12:30:49
  3 | 2016-08-10 12:30:55
  4 | 2015-08-10 11:03:10
  5 | 2015-08-11 11:03:10
  6 | 2015-08-12 11:03:10
(6 строк)

This output is incorrect. We expected

postgres@test=# select * from test where t >=('yesterday'::timestamptz - interval '1 day') and t <= date_trunc('hour', now());
 id |          t          
----+---------------------
  1 | 2016-08-10 12:30:40
  2 | 2016-08-10 12:30:49
  3 | 2016-08-10 12:30:55
...

[ahsanhadi]

This might be happening because mysql doesn't have an Interval datatype like PG does...but we need to look into it to see why the queries are getting translated incorrectly...We will look at this as soon as time permits...