MySQL Bugs: #64197: "Impossible WHERE noticed after reading const tables" message

Bug #64197	"Impossible WHERE noticed after reading const tables" message
Submitted:	1 Feb 2012 20:34	Modified:	4 Jun 2012 14:40
Reporter:	Daniel Katz	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S4 (Feature request)
Version:	5.0.85-community-log	OS:	Linux
Assigned to:		CPU Architecture:	Any
Tags:	error message, impossible where

Description:
I feel like the error output 
"Impossible WHERE noticed after reading const tables" is unnecessary when doing an explain.  In fact, I feel like it's counter productive.

Sometimes I do query profiling and see how I can improve upon what other developers write.

When using live data, it's not uncommon that an empty set will occur.  

1) The error output as it is written is both alarmist AND ambiguous

How to repeat:
CREATE TABLE `test_dk` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
;
CREATE TABLE `test_dk_favorite_foods` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `favorite_food` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `user_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
;
insert into test_dk (name) values ('Tom');
insert into test_dk (name) values ('Dick');
insert into test_dk (name) values ('Harry');

insert into test_dk_favorite_foods (user_id, favorite_food) values (1, 'Spaghetti');
insert into test_dk_favorite_foods (user_id, favorite_food) values (1, 'Meatballs');
insert into test_dk_favorite_foods (user_id, favorite_food) values (2, 'Oatmeal');

explain select * from test_dk join test_dk_favorite_foods on (test_dk_favorite_foods.user_id = test_dk.id) where test_dk.id = -12;  (or any integer/id that's not found in the dataset).

Suggested fix:
Rather than returning 
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
| 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 |
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+

I think it'd be better to still show the joins how they WOULD join 

+----+-------------+------------------------+-------+---------------+---------+---------+-------+------+-------------+
| id | select_type | table                  | type  | possible_keys | key     | key_len | ref   | rows | Extra       |
+----+-------------+------------------------+-------+---------------+---------+---------+-------+------+-------------+
|  1 | SIMPLE      | test_dk                | const | PRIMARY       | PRIMARY | 4       | const |    1 |             |
|  1 | SIMPLE      | test_dk_favorite_foods | ALL   | NULL          | NULL    | NULL    | NULL  |    3 | Using where |
+----+-------------+------------------------+-------+---------------+---------+---------+-------+------+-------------+

And then a flag a warning for the user.

Also, requested is to reword: "Impossible WHERE noticed after reading const tables" to something more friendly like 
- WHERE changed to 'FALSE' due to empty set when evaluating WHERE conditions
or
- EMPTY SET found when evaluating WHERE conditions, substituted to FALSE.

The real argument is that you're telling the user what's going on in the background but you're not telling them what they want to know.
Similar how you don't tell me how mysql is going to manage the BTREE or whatnot.

Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

This message means no row which satisfied condition can be found in the table.

> This message means no row which satisfied condition can be found in the table.
I realize this.  In fact, I pretty much stated as much above.

Aren't feature requests as well as UI behavior and even back end behavior reported through this same vehicle?  AFAIK, the only difference is the Severity.  

I'm making an argument for the fact that it's alarmist and ambiguous being error output rather than a warning and returning the expected dataset to the user.

I reopened the status and changed the Severity back to "feature request" however I thought it was more appropriate as S3 since the server is not effectively saying what it's doing.

IMHO it makes some sense to show 0 (zero) in "rows" column and put proper explanation in "Extra" column in this case... But if greedy optimizer, internally, does NOT continue to process the query in this case, it does not make much sense to show next (fictional) steps for other tables joined. 

Let's delegate final decision on this FR to product management.

Well i think this is a bug because I have a select which consists of 9 joins...

(main query)
Select a.foo1,b.foo2,c.foo3,d.foo4 FROM mainView as a
LEFT JOIN view1 as b ON a.uuid=b.uuid 
LEFT JOIN view2 as c ON a.uuid=c.uuid
LEFT JOIN view3 as d ON a.uuid=d.uuid

Now view3 looks like this:

Select a.col1,a.col2,a.col3 FROM status as a
INNER JOIN status_snapshot as b ON a.ID=b.ID 
WHERE b.deleted = 0;       Im trying to select after replications the current state of something. snapshot is basicly just pointing on the last deltas of status.

Now If I have no data in status_snapshot and status  I get 
"Impossible WHERE noticed after reading const tables" and the main query takes about an hour

however If I add a dummy record into both status and status_snapshot and thus the view becomes not empty,  my main query 
Select a.foo1,b.foo2,c.foo3,d.foo4 FROM mainView as a
LEFT JOIN view1 as b ON a.uuid=b.uuid 
LEFT JOIN view2 as c ON a.uuid=c.uuid
LEFT JOIN view3 as d ON a.uuid=d.uuid

takes 5 seconds to complete, it's not a win but still better. 
I suspect that that fail gets propagated into the main query and the optimizer collapses or something.

Please send feedback on this situation. Thanks.

This also occurs with MySQL 5.5.32 Enterprise. I am trying to find out why a query took 4 minutes to run. My specific query had one table. Data moves in/out so quickly the criteria changed so that the Explain is useless. The Explain should show what assumptions the Optimizer will make based on what stats it knows and the structure of the tables, join or no join.