| Bug #50757 | SELECT 0 in ('a','b') returns TRUE | ||
|---|---|---|---|
| Submitted: | 30 Jan 2010 16:59 | Modified: | 2 Feb 2010 10:53 |
| Reporter: | Peufeu Peufeu | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server: Data Types | Severity: | S2 (Serious) |
| Version: | 5.0.51a-3ubuntu5.4 | OS: | Linux |
| Assigned to: | CPU Architecture: | Any | |
[1 Feb 2010 8:18]
Sveta Smirnova
Thank you for the report. According to http://dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html: "Strict mode controls how MySQL handles input values that are invalid or missing." Values for which you are not getting errors are not input, so behavior is expected.
[2 Feb 2010 10:53]
Peufeu Peufeu
in the following query, 0, 'a', and 'b' are input values, and 'a' and 'b' are invalid input values, since they are not convertable to integer type. Same for all examples above.
mysql> SELECT 0 in ('a','b');
+----------------+
| 0 in ('a','b') |
+----------------+
| 1 |
+----------------+
1 row in set, 2 warnings (0.00 sec)
Description: Enable all strictest options : mysql>SET @@SESSION.sql_mode = 'TRADITIONAL,STRICT_ALL_TABLES,NO_AUTO_VALUE_ON_ZERO,NO_ENGINE_SUBSTITUTION,NO_UNSIGNED_SUBTRACTION,NO_ZERO_DATE,NO_ZERO_IN_DATE'; Perform this query : mysql> SELECT 0 in ('a','b'); +----------------+ | 0 in ('a','b') | +----------------+ | 1 | +----------------+ 1 row in set, 2 warnings (0.00 sec) mysql> show warnings; +---------+------+---------------------------------------+ | Level | Code | Message | +---------+------+---------------------------------------+ | Warning | 1292 | Truncated incorrect DOUBLE value: 'a' | | Warning | 1292 | Truncated incorrect DOUBLE value: 'b' | +---------+------+---------------------------------------+ In STRICT mode, it is not possible to insert an invalid value (like 'a') into an INT column. However, the strictness does not apply to anything else. Here, the type of 0 (INT) is applied to strings 'a' and 'b', which both evaluate to 0. So, the condition evaluates to TRUE (1), even if it makes no sense. STRICT mode should treat these as errors. CREATE TABLE test1 ( x INT ); INSERT INTO test1 VALUES (0),(1); SELECT * FROM test1 WHERE x='a'; +------+ | x | +------+ | 0 | +------+ 1 row in set, 2 warnings (0.00 sec) Same here : even though the condition is erroneous and should raise an error, the query is performed, and results returned. SELECT * FROM test1 WHERE x=''; +------+ | x | +------+ | 0 | +------+ 1 row in set Even worse, here we have NO WARNINGS AT ALL ! This makes it harder to track application bugs. How to repeat: See above. Suggested fix: Make STRICT mode be STRICT. In STRICT mode, incorrect values (which are impossible to convert to the specified type) should be rejected, not generate warnings. Postgres handles all the above cases correctly (ie, it rejects the incorrect data instead of coercing it into a pseudo-default value).