| Bug #65013 | Illegal (?) syntax returns different results depending on index in WHERE clause | ||
|---|---|---|---|
| Submitted: | 17 Apr 2012 18:32 | Modified: | 18 Apr 2012 18:45 |
| Reporter: | Peter Laursen (Basic Quality Contributor) | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: DML | Severity: | S3 (Non-critical) |
| Version: | 5.1.36, 5.5.22 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[17 Apr 2012 18:59]
Sveta Smirnova
Thank you for the report. This is not a bug. Result depends from existence of 0 or NULL values in the field: create table t1( f1 int not null primary key, f2 int, f3 int, unique(f2)); insert into t1 values(1,1,1), (2,2,0), (3,NULL,NULL); select * from t1 where f1; f1 f2 f3 1 1 1 2 2 0 3 NULL NULL select * from t1 where f2; f1 f2 f3 1 1 1 2 2 0 select * from t1 where f3; f1 f2 f3 1 1 1
[17 Apr 2012 18:59]
Peter Laursen
The table is InnoDB if it matters.
[17 Apr 2012 19:03]
Peter Laursen
I am sorry, but there are absolutely no NULLs on columns used in the WHERE clause with either of the 3 examples.
[17 Apr 2012 19:04]
Peter Laursen
.. and no "0"s either.
[17 Apr 2012 19:09]
Peter Laursen
SELECT COUNT(*) FROM order_details WHERE email_used_to_buy; -- returns "16" SELECT COUNT(*) FROM order_details WHERE email_used_to_buy IS NULL OR email_used_to_buy = '0'; -- returns "0"
[17 Apr 2012 19:18]
Sveta Smirnova
Thank you for the feedback. If your data is not similar to my example, please, try to create partial dump which you can share. I think only 3 columns matters, not name of columns and strings/numbers can be replaced somehow.
[17 Apr 2012 19:37]
Peter Laursen
I hoped I would be able to avoid this! But it seems that I will have to. It could take a few days though. Hopefully it is reproducible with one row that gets and one that does not get returned with the WHERE on `email_used_to_buy` column. The table is involved in FK-constraints as both 'parent' and 'child' so maybe simplification of the schema removes the issue. Just one more comment now. The database is live on MySQL 5.1.36. I dumped the table and imported to a local 5.5.22 for verification. So I think we can rule out anything like corruption of table statistics. BTW .. completely unpredictable it seems. After dropping Foreign Keys: DELETE FROM order_details WHERE customer_id <11 OR NOT order_id; -- 13 rows affected .. so "NOT order_id" matched 3 rows!! ???
[17 Apr 2012 19:44]
Sveta Smirnova
Thank you for the feedback. I am afraid you need to create repeatable test case. All cases which can lead to such behavior which I can imagine are similar to code I provided: expression 'WHERE column' evaluates to FALSE. This can happen if column contains NULL, 0, empty string.
[17 Apr 2012 20:12]
Peter Laursen
-- I have an anonymized 2-row test case now with PK's removed; -- I will attach dump shortly -- I can import this dump to an empty database on local 5.5.22 and issue is still reproducible. SELECT COUNT(*) FROM order_details; -- returns 2 SELECT COUNT(*) FROM order_details WHERE order_id; -- returns 2 SELECT COUNT(*) FROM order_details WHERE email_used_to_buy; -- returns 1
[17 Apr 2012 21:48]
Peter Laursen
I meant with *FK*s removed. The dump has been attached privately.
[18 Apr 2012 4:10]
Valeriy Kravchuk
In your last test case uploaded you have one email address that starts with numeric character, '6', and thus it is converted to number > 1 and is considered as TRUE in WHERE. Other email address starts with non-numeric character and thus is converted to number 0, FALSE. Check warnings that statement produces. order_id is, not surprisingly, number > 0 in every row, so it is considered as TRUE. This has nothing to do with indexes defined on columns. What is the problem?
[18 Apr 2012 18:45]
Peter Laursen
OK .. thanks for the explanation.
Description: A statement of the type SELECT * FROM table WHERE column; -- note: <empty expression> after WHERE .. returns a result. Further the result depends on whether `column` has an index defined on it and what kind of index. If `column` is the PK all rows are returned. If not only a subset of rows are returned. EXPLAIN gives no indication why. How to repeat: SELECT COUNT(*) FROM order_details; -- returns 68200 SELECT COUNT(*) FROM order_details WHERE order_id; -- returns 68200; there is a single column primary key on order_id -column EXPLAIN SELECT * FROM order_details WHERE order_id; /* id select_type table type possible_keys key key_len ref rows Extra ------ ----------- ------------- ------ ------------- ------ ------- ------ ------ ------------- 1 SIMPLE order_details ALL (NULL) (NULL) (NULL) (NULL) 28038 Using where */ SELECT COUNT(*) FROM order_details WHERE email_used_to_buy; -- returns 16; no index on email_used_to_buy -column EXPLAIN SELECT * FROM order_details WHERE email_used_to_buy; /* id select_type table type possible_keys key key_len ref rows Extra ------ ----------- ------------- ------ ------------- ------ ------- ------ ------ ------------- 1 SIMPLE order_details ALL (NULL) (NULL) (NULL) (NULL) 26583 Using where */ SELECT COUNT(*) FROM order_details WHERE order_no; -- returns 66126; there is UNIQUE KEY `order_no` (`order_no`,`product_id`), EXPLAIN SELECT * FROM order_details WHERE order_no; /* id select_type table type possible_keys key key_len ref rows Extra ------ ----------- ------------- ------ ------------- ------ ------- ------ ------ ------------- 1 SIMPLE order_details ALL (NULL) (NULL) (NULL) (NULL) 26292 Using where */ Suggested fix: 1) I expected a 1064 syntax error. But it seems that the server accepts the <empty expression> as syntactcally valid. 2) But why then different results? I cannot share more data or schema. The data belongs to a company that competes with Oracle. Even the numbers have been faked - with the exception of the number "16" what is actually genuine. All other numbers are 5-digit numbers in 'real world'. Observed on both MySQL 5.1.36 and 5.5.22.