Inconsistent Results with IFNULL and Time Functions Based on ENGINE=MyISAM

This example illustrates a query optimization bug where two identical queries produce different results depending on whether the table uses the ENGINE=MyISAM. In the first query, the table t0 does not specify the engine, and the result is an empty set. In the second query, with ENGINE=MyISAM, the query returns 1. The difference in results is likely related to how MySQL handles the IFNULL function when combined with time functions such as TIMESTAMP() and SYSDATE(). The optimizer appears to process the IFNULL and time-related expressions differently depending on the storage engine, leading to inconsistent results.

DROP DATABASE IF EXISTS test2;

CREATE DATABASE test2;

USE test2;

--  query1

CREATE TABLE t0(c0 char(100) ,c1 SMALLINT);

INSERT INTO t0 VALUES ('', false);

SELECT 1 FROM t0 AS tom0 WHERE ( IFNULL( TIMESTAMP( 'A' ) , SYSDATE( 0 ) ) ) ;

--  query2

CREATE OR REPLACE TABLE t0(c0 CHAR(100) , c1 SMALLINT  ) engine=MyISAM;

INSERT INTO t0 VALUES ('', false);

SELECT 1 FROM t0 AS tom0 WHERE ( IFNULL( TIMESTAMP( 'A' ) , SYSDATE( 0 ) ) ) ;

--  output1

Empty set, 1 warning (0.00 sec)

--  output2

+---+

| 1 |

+---+

| 1 |

+---+

1 row in set, 1 warning (0.01 sec)