|
THanks! I repeated on 10.4-11.4 versions, with InnoDB, the reason - types conversion
MariaDB [test]> explain extended SELECT f1 FROM (SELECT (t0.c0 - ADDDATE('2024-01-01', 1)) AS f1, (t0.c0 - ADDDATE('2024-01-01', 1)) IS TRUE AS flag FROM t0) as t WHERE flag=1;
|
+------+-------------+-------+------+---------------+------+---------+------+------+----------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|
+------+-------------+-------+------+---------------+------+---------+------+------+----------+-------------+
|
| 1 | SIMPLE | t0 | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | Using where |
|
+------+-------------+-------+------+---------------+------+---------+------+------+----------+-------------+
|
1 row in set, 1 warning (0,001 sec)
|
|
Note (Code 1003): select `test`.`t0`.`c0` - ('2024-01-01' + interval 1 day) AS `f1` from `test`.`t0` where `test`.`t0`.`c0` - <cache>('2024-01-01' + interval 1 day) is true = 1
|
MariaDB [test]> SELECT f1 FROM (SELECT (t0.c0 - ADDDATE('2024-01-01', 1)) AS f1, (t0.c0 - ADDDATE('2024-01-01', 1)) IS TRUE AS flag FROM t0) as t WHERE flag=1;
|
+-----------+
|
| f1 |
|
+-----------+
|
| -20240103 |
|
+-----------+
|
1 row in set, 1 warning (0,001 sec)
|
|
Warning (Code 1292): Truncated incorrect DOUBLE value: '2024-01-02'
|
MariaDB [test]> explain extended SELECT f1 FROM (SELECT (t0.c0 - ADDDATE('2024-01-01', 1)) AS f1 FROM t0) as t WHERE f1;
|
+------+-------------+-------+------+---------------+------+---------+------+------+----------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|
+------+-------------+-------+------+---------------+------+---------+------+------+----------+-------------+
|
| 1 | SIMPLE | t0 | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | Using where |
|
+------+-------------+-------+------+---------------+------+---------+------+------+----------+-------------+
|
1 row in set, 1 warning (0,001 sec)
|
|
Note (Code 1003): select `test`.`t0`.`c0` - <cache>('2024-01-01' + interval 1 day) AS `f1` from `test`.`t0` where `test`.`t0`.`c0` - <cache>('2024-01-01' + interval 1 day) <> 0
|
MariaDB [test]> SELECT f1 FROM (SELECT (t0.c0 - ADDDATE('2024-01-01', 1)) AS f1 FROM t0) as t WHERE f1;
|
+-------+
|
| f1 |
|
+-------+
|
| -2025 |
|
+-------+
|
1 row in set, 2 warnings (0,001 sec)
|
|
Warning (Code 1292): Truncated incorrect DOUBLE value: '2024-01-02'
|
Warning (Code 1292): Truncated incorrect DOUBLE value: '2024-01-02'
|
|