MySQL Bugs: #48214: no error returned

Bug #48214	no error returned
Submitted:	21 Oct 2009 19:38	Modified:	22 Oct 2009 9:13
Reporter:	Peter Laursen (Basic Quality Contributor)	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.1.36	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	qc

Description:
The incorrect statement
SELECT * FROM order_details WHERE email_used_to_buy ORDER BY order_id DESC LIMIT 20;

.. does no return an error. It returns 4 rows out of +20000 rows in the table.

Probably this is some "C-idiocrazy". I assume the server executes something like

SELECT * FROM webyog_portal.order_details WHERE email_used_to_buy = 1 ORDER BY order_id DESC LIMIT 20;
.. or similar. Obviously "WHERE email_used_to_buy" is evaluated TRUE in 4 cases (only).

.. but the 4 rows returned seem totally random to me. I cannot see how 4 rows are TRUE and the rest FALSE with that WHERE.  'email_used_to_buy' column always contains valid email adresses (or in come cases more than one valid email seperated by " ; ")

How to repeat:
See above - of course I intended to execute

SELECT * FROM order_details WHERE email_used_to_buy LIKE 'some%.mail@any' ORDER BY order_id DESC LIMIT 20;

Test case is private. No way! 

Anyway a CREATE TABLE statement will be added as a private comment.  Private means P-R-I-V-A-T-E !! (how do I increase font size here? :-) )

Suggested fix:
This is very counter-intuitive! Statement is invalid as there is no operator for WHERE and an error should be returned.

EXPLAIN EXTENDED SELECT * FROM order_details WHERE email_used_to_buy ORDER BY order_id DESC LIMIT 20;
/*
    id  select_type  table          type    possible_keys  key      key_len  ref       rows  filtered  Extra      
------  -----------  -------------  ------  -------------  -------  -------  ------  ------  --------  -----------
     1  SIMPLE       order_details  index   (NULL)         PRIMARY  4        (NULL)      20  80695.00  Using where

.. no hint in SHOW WARNINGS  .. still only the WHERE reading like

.. where <database>.`order_details`.`email_used_to_buy` ...

Thank you for the problem report. No need for private data, but tell me if that emails in the rows returned contain any number? Something like 2openxs@a.com or openxs4all@a.com?

Yes. I should have noticed They all start with long numbers: '6050809@..', '83493andersson@..', '2019353@...' AND '6130445@..'.  There are lots of other addresses that contain short numbers like 

SELECT COUNT(*) FROM order_details WHERE email_used_to_buy LIKE '%0%'; -- 169
SELECT COUNT(*) FROM order_details WHERE email_used_to_buy LIKE '%1%'; -- 299
etc.

.. but only the 4 listed start with a number.

However I am not discussing if the result should be 0 rows or 4 rows - rather that parser should not accept an empty where_condition at all.  I do not think implicitly adding " = true "(or whatever) if nothing specified should apply to WHERE in SQL (unlike IF-conditions etc. in most programming languages). I may be wrong but what does SQL standards say about it?  Mysql documentation says 
http://dev.mysql.com/doc/refman/5.0/en/select.html
[WHERE where_condition]
.. and I am surprised that a statement without "where_condition" explicitly given is valid.

It is surprising, "C-idiotism", whatever, but NOT a bug. It was always like that in MySQL, by design. Check yourself:

C:\Program Files\MySQL\MySQL Server 5.0\bin>mysql -uroot -proot -P3308 test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.0.86-community-nt-log MySQL Community Edition (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select 1 is true;
+-----------+
| 1 is true |
+-----------+
|         1 |
+-----------+
1 row in set (0.03 sec)

mysql> select 2 is true;
+-----------+
| 2 is true |
+-----------+
|         1 |
+-----------+
1 row in set (0.00 sec)

mysql> select 0 is true;
+-----------+
| 0 is true |
+-----------+
|         0 |
+-----------+
1 row in set (0.00 sec)

mysql> select 1a is true;
ERROR 1054 (42S22): Unknown column '1a' in 'field list'
mysql> select '1a' is true;
+--------------+
| '1a' is true |
+--------------+
|            1 |
+--------------+
1 row in set, 1 warning (0.00 sec)

mysql> show warnings\G
*************************** 1. row ***************************
  Level: Warning
   Code: 1292
Message: Truncated incorrect DOUBLE value: '1a'
1 row in set (0.00 sec)

Actually I expected a reply like this but it is still not the point. 

What is evaluated true is one discussion. What is a valid SQL-statement another. I would expect that execution would be denied with syntax error 1064 here. I think the parser should reject the statement as invalid due to missing where-condition. I do not complain about the result (in the first place - even though this is also somewhat weird).

I do not claim to be an expert here but it would be interesting to compare with other SQL-database systems (and not at least SQL-standards): is an empty where-condition valid? But I do not have the option so I guess that you will handle this as you like.

WHERE condition is NOT empty, it is WHERE <column>:

WHERE email_used_to_buy

<column> is a valid expression, it is just treated as BOOLEAN. Now, what is BOOLEAN in MySQL? Check http://dev.mysql.com/doc/refman/5.0/en/numeric-type-overview.html. It is just (small?) INTEGER. 

All is documented, NOT a bug.