Inconsistent Results Due to Engine Difference and BINARY/LEAST Handling

This example illustrates a query optimization bug where two identical queries produce different results depending on whether the table is created with the ENGINE=MyISAM option. The first query, using a table with ENGINE=MyISAM, returns an empty set, while the second query, after dropping and recreating the table without specifying the engine, returns a row. This discrepancy is likely caused by how MySQL handles the BINARY data type and the LEAST() function differently depending on the storage engine. The optimizer seems to treat the binary comparison in conjunction with the LEAST() function inconsistently, causing different results based on the table engine used.

DROP DATABASE IF EXISTS test1;

CREATE DATABASE test1;

USE test1;

--  query1

CREATE TABLE t0(c0 BOOLEAN  , c1 REAL  ) engine=MyISAM;

INSERT INTO t0 VALUES (false, -1);

 SELECT 1 AS c0 FROM  t0 AS tom4   WHERE LEAST( X'68656C6C6F' , BINARY( '2025' ) ) << tom4.c0  limit 9999 ;

--  query2

 DROP table t0;

CREATE TABLE t0(c0 BOOLEAN,c1 REAL);

INSERT INTO t0 VALUES (false, -1);

 SELECT 1 AS c0 FROM  t0 AS tom4   WHERE LEAST( X'68656C6C6F' , BINARY( '2025' ) ) << tom4.c0  limit 9999 ;

--  output1

Empty set, 1 warning (0.00 sec)

--  output2

+----+

| c0 |

+----+

|  1 |

+----+

1 row in set (0.00 sec)