MySQL Bugs: #47296: Impossible WHERE does not give a proper warning

Bug #47296	Impossible WHERE does not give a proper warning
Submitted:	13 Sep 2009 23:25	Modified:	16 Sep 2009 1:35
Reporter:	Roel Van de Paar	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Errors	Severity:	S4 (Feature request)
Version:	5.1.37	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Current:
mysql> SELECT * FROM t WHERE t.row IS NULL;
Empty set (0.00 sec)

Should be:
mysql> SELECT * FROM t WHERE t.row IS NULL;
Empty set, 1 warning (0.00 sec)

Given that it's an impossible WHERE:

mysql> EXPLAIN SELECT * FROM t WHERE t.row IS NULL;
+----+-------------+-------+------+---------------+------+---------+------+------+------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra            |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------+
|  1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | Impossible WHERE |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------+
1 row in set (0.00 sec)

How to repeat:
DROP TABLE IF EXISTS t;
CREATE TABLE t (row INT NOT NULL) ENGINE=MyISAM;
INSERT INTO t VALUES (1);
SELECT * FROM t WHERE t.row IS NULL;
EXPLAIN SELECT * FROM t WHERE t.row IS NULL;

Suggested fix:
Fix warning report

Verifying as D4

Idea stemmed from bug #47005

To clarify, notice that the table is declared as 'NOT NULL' while the statement is asking for 'IS NULL'. Another example:

mysql> select * from t where 1=2;
Empty set (0.00 sec)

mysql> explain select * from t where 1=2;
+----+-------------+-------+------+---------------+------+---------+------+------+------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra            |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------+
|  1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | Impossible WHERE |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------+
1 row in set (0.00 sec)

Interesting to note:

mysql> SELECT * FROM t WHERE t.row ='h';
Empty set, 1 warning (0.00 sec)

mysql> SHOW WARNINGS;
+---------+------+---------------------------------------+
| Level   | Code | Message                               |
+---------+------+---------------------------------------+
| Warning | 1292 | Truncated incorrect DOUBLE value: 'h' |
+---------+------+---------------------------------------+
1 row in set (0.00 sec)

mysql> EXPLAIN SELECT * FROM t WHERE t.row ='h';
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                                               |
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
|  1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | Impossible WHERE noticed after reading const tables |
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
1 row in set, 1 warning (0.00 sec)

Where did you get an idea that "impossible WHERE" should produce a warning ?

See my [14 Sep 6:29] comment

The one with "Idea stemmed" ? I don't see where it follows from it that an "impossible WHERE" should produce a warning.

Please clarify, why do you think that no warning for "impossible WHERE" is a bug.

Why should an 'impossible where' not produce a warning?
Are there any rules or guidelines for when a warning should be produced or not?

Also, 'IS NULL' not matching any rows *if the table allows NULL* should not be a warning, but, if the table def. is NOT NULL, then the query can never match - hence a warning should be produced.

Also, if EXPLAIN can see an impossible where, why can't the same be returned for any given instruction that is impossible (as EXPLAIN already does)?

Note that EXPLAIN will tell you two different things:

1. Impossible WHERE 
<or>
2. Impossible WHERE noticed after reading const tables

1. Is seen when something is literally impossible in the instruction, as in the examples above i.e. IS NULL vs NULL, 2=3 etc.

2. Is seen when the SELECT statement itself is a possible instruction, but given the variable input to it, becomes impossible, for instance a > value_greater_than_int where a is an int, or a = 1 where a is 2.

The very least that could be done for 1 is to provide a warning. One could argue that 2 should produce a warning and 1 should produce an error, though this may (or may not) be standard SQL.

s/may (or may not)/may or may not/

Okay, understand.
You want "impossible WHERE" to be generate a warning.
But the manual does not say that it should do that.

It's a feature request.