[#MDEV-18968] Both (WHERE 0.1) and (WHERE NOT 0.1) return empty set

[MDEV-18968] Both (WHERE 0.1) and (WHERE NOT 0.1) return empty set Created: 2019-03-19 Updated: 2019-03-26 Resolved: 2019-03-26
Status:	Closed
Project:	MariaDB Server
Component/s:	Optimizer
Affects Version/s:	10.1, 10.2, 10.3, 10.4
Fix Version/s:	10.2.24, 10.1.39, 10.3.14, 10.4.4

Type:

Bug

Priority:

Major

Reporter:

Alexander Barkov

Assignee:

Alexander Barkov

Resolution:

Fixed

Votes:

Labels:

None

Issue Links:

Blocks
blocks	MDEV-18898	SELECT using wrong index when using o...	Open
blocks	MDEV-18964	Non-optimal execution of WHERE num_fi...	Open

Description

DROP TABLE IF EXISTS t1;

CREATE TABLE t1 (a INT);

INSERT INTO t1 VALUES (10);

SELECT CASE WHEN 0.1 THEN 'TRUE' ELSE 'FALSE' END FROM t1;

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

| CASE WHEN 0.1 THEN 'TRUE' ELSE 'FALSE' END |

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

| TRUE                                       |

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

The above script demonstrates:

Now I run these two queries:

SELECT * FROM t1 WHERE 0.1;

Empty set (0.00 sec)

SELECT * FROM t1 WHERE NOT 0.1;

Empty set (0.00 sec)

Notice, both return empty set. Looks wrong. The query with WHERE 0.1 should return one record.

Generated at Thu Feb 08 08:48:05 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.