[#MDEV-27101] Subquery using the ALL keyword on TIMESTAMP columns produces a wrong result

This bug is related to ~~MDEV-27098~~.

To reproduce this bug, make sure the time zone information is loaded, e.g.:

mysql_tzinfo_to_sql  /usr/share/zoneinfo/|mysql -uroot mysql

I create a table with a TIMESTAMP column and insert to values near DST change:

SET time_zone='Europe/Moscow';

CREATE OR REPLACE TABLE t1 (a TIMESTAMP NULL);

SET timestamp=1288477526;  -- this is summer time

INSERT INTO t1 VALUES (NOW());

SET timestamp=1288477526+3599; -- this is winter time

INSERT INTO t1 VALUES (NOW());

SELECT a, UNIX_TIMESTAMP(a) FROM t1;

It returns the following result:

+---------------------+-------------------+

| a                   | UNIX_TIMESTAMP(a) |

+---------------------+-------------------+

| 2010-10-31 02:25:26 |        1288477526 | <-- this value is smaller

| 2010-10-31 02:25:25 |        1288481125 | <-- this value is greater

+---------------------+-------------------+

Now I run a query with an ALL subquery:

SELECT a, UNIX_TIMESTAMP(a) FROM t1 WHERE a <= ALL (SELECT * FROM t1);

+---------------------+-------------------+

| a                   | UNIX_TIMESTAMP(a) |

+---------------------+-------------------+

| 2010-10-31 02:25:26 |        1288477526 |

| 2010-10-31 02:25:25 |        1288481125 |

+---------------------+-------------------+

The result is wrong. The query is expected to return only the smallest value - the first record.

Now I run a different query with an ALL subquery:

SELECT a, UNIX_TIMESTAMP(a) FROM t1 WHERE a >= ALL (SELECT * FROM t1);

+---------------------+-------------------+

| a                   | UNIX_TIMESTAMP(a) |

+---------------------+-------------------+

| 2010-10-31 02:25:26 |        1288477526 |

| 2010-10-31 02:25:25 |        1288481125 |

+---------------------+-------------------+

The result is wrong. The query is expected to return only the greatest value - the second record.

[MDEV-27101] Subquery using the ALL keyword on TIMESTAMP columns produces a wrong result Created: 2021-11-20 Updated: 2022-11-01 Resolved: 2022-08-22
Status:	Closed
Project:	MariaDB Server
Component/s:	Data types
Affects Version/s:	10.2, 10.3, 10.4, 10.5, 10.6, 10.7, 10.8
Fix Version/s:	10.9.2, 10.10.1, 10.4.27, 10.5.18, 10.6.10, 10.7.6, 10.8.5