|
create or replace table t (a timestamp);
|
insert into t values ('2021-12-12 01:02:12'),('0000-00-00 00:00:00');
|
|
select MIN(a) FROM t WHERE a = '0';
|
|
alter table t add index ind(a);
|
select MIN(a) FROM t WHERE a = '0';
|
|
alter table t drop index ind, add index ind_desc(a desc);
|
select MIN(a) FROM t WHERE a = '0';
|
|
# Cleanup
|
drop table t;
|
The first two SELECTs (without an index and with the usual ASC one) return zero, although warnings are different – two for the first SELECT and one for the second:
select MIN(a) FROM t WHERE a = '0';
|
MIN(a)
|
0000-00-00 00:00:00
|
Warnings:
|
Warning 1292 Incorrect datetime value: '0'
|
Warning 1292 Truncated incorrect datetime value: '0'
|
|
select MIN(a) FROM t WHERE a = '0';
|
MIN(a)
|
0000-00-00 00:00:00
|
Warnings:
|
Warning 1292 Incorrect datetime value: '0'
|
The third one (with the DESC index) returns NULL with one warning:
select MIN(a) FROM t WHERE a = '0';
|
MIN(a)
|
NULL
|
Warnings:
|
Warning 1292 Incorrect datetime value: '0'
|
Plans are different in all three cases:
id select_type table type possible_keys key key_len ref rows filtered Extra
|
1 SIMPLE t ALL NULL NULL NULL NULL 2 100.00 Using where
|
Warnings:
|
Warning 1292 Incorrect datetime value: '0'
|
Note 1003 select min(`test`.`t`.`a`) AS `MIN(a)` from `test`.`t` where `test`.`t`.`a` = '0'
|
id select_type table type possible_keys key key_len ref rows filtered Extra
|
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Select tables optimized away
|
Warnings:
|
Warning 1292 Incorrect datetime value: '0'
|
Note 1003 select min(`test`.`t`.`a`) AS `MIN(a)` from `test`.`t` where `test`.`t`.`a` = '0'
|
id select_type table type possible_keys key key_len ref rows filtered Extra
|
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
|
Warnings:
|
Warning 1292 Incorrect datetime value: '0'
|
Note 1003 select min('0000-00-00 00:00:00') AS `MIN(a)` from `test`.`t` where 0
|
|