| Bug #110940 | Querying VarChar column using 0 in WHERE clause incorrectly returns ALL ROWS | ||
|---|---|---|---|
| Submitted: | 5 May 2023 21:04 | Modified: | 8 May 2023 15:12 |
| Reporter: | Robert Rothkopf | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server | Severity: | S3 (Non-critical) |
| Version: | 8.0.32 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[8 May 2023 13:38]
MySQL Verification Team
Hi Mr. Roghkopf, Thank you for your bug report. However, this is not a bug. All type-safe relational database systems return the error on the query like the one that you reported. MySQL tries to be user-friendly. Hence, in the comparison between the string and integer, both sides of the expression are converted to the floating point numbers. Hence, the result is correct. It is also basic SQL knowledge that 0 (zero) IS NOT and unknown entity, while NULL is also known as UNKNOWN !!!!!! With the best intentions in our mind, we would like to recommend to you to read some textbooks on the SQL language ....... This is all described in our Reference Manual. Definitely not a bug.
[8 May 2023 15:12]
Robert Rothkopf
> All type-safe relational database systems return > the error on the query like the one that you reported. If MySQL returned an error, I'd agree. If you look at my query & result set though, no error was raised. You note that "all relational databases" would behave the same as MySQL -- I ran this query in MS SQL Server and confirmed -- they properly report this as an error, whereas MySQL does not. Here's what SQL Server returns: Msg 245, Level 16, State 1, Line 22 Conversion failed when converting the varchar value 'bubba' to data type int. > MySQL tries to be user-friendly. Hence, in the comparison between the string > and integer, both sides of the expression are converted to the floating point > numbers. Hence, the result is correct. I understand. In that example, the username char data is converted to floating point, so every value converts to 0; since 0=0 all results are returned. > It is also basic SQL knowledge that 0 (zero) IS NOT and > unknown entity, while NULL is also known as UNKNOWN !!!!!! > With the best intentions in our mind, we would like to > recommend to you to read some textbooks on the SQL language . > Definitely not a bug. Why are you discussing zero and NULL here? My bug report was with all results being returned instead of an error being raised or no results returned. Maybe you're just trying to explain why my 2nd query comparing with NULL properly returned no results? I have no questions there.. it worked properly. That last comment of yours was a pretty rude reply -- I worked extensively with MS SQL Server for 16 years optimizing SPs and queries for large banks who were needed help getting better performance from their systems .. although I'm currently a tad rusty, and definitely new to MySQL specifics, I'm beyond the basics with general SQL language, data modeling, etc.. THAT all said -- thank you for your reply. With the auto-type-conversion happening and the strings converting to 0, I can understand why MySQL returned these results and you say it's not a bug. How can I configure MySQL to return an error instead of doing an auto-type-conversion like this? I believe strong typing would avoid this, and I'd rather be deliberate with my conversions. (we discovered this because of a bug in our code where the wrong type parameter was being passed in)
[8 May 2023 15:30]
MySQL Verification Team
Hi, MySQL was never designed with a strict typing in mind. One of the basic principles of MySQL design, from the very beginning until today, is that MySQL will do all in its power to return any result for each query that programmer / designer decided to throw at it. Hence, MySQL never was, is not and (probably) will never be a strict-typing RDBMS. This is also true for all of its numerous clones as well. Thanks to this behaviour, MySQL has become the most widely used and popular RDBMS in the world. Hence, there is no option to run MySQL into strict-typing RDBMS.
Description: Running a query against a table comparing a VarChar value to the value 0 in the WHERE clause incorrectly causes ALL results to be returned (as if there *were* no WHERE clause) For example, the following: SCENARIO: Table: bugtest Has a column defined: username varchar(255) (indexed unique) Has rows with data, none of them NULL ERROR: Running the following query returns ALL rows in the table SELECT * FROM bugtest WHERE username = 0; EXPECTED RESULT: No rows returned How to repeat: -- --------------------- -- create the test table -- --------------------- CREATE TABLE bugtest ( `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, `username` VARCHAR(255) NULL, PRIMARY KEY (`id`), UNIQUE INDEX `username_UNIQUE` (`username` ASC) VISIBLE); -- --------------------- -- Insert some test data -- --------------------- insert into bugtest (username) values ("rob"), ("mary"), ("sue"), ("bubba"), ("casper"), ("dart vader"); -- --------------------- -- Run test queries -- --------------------- select * from bugtest where username="bubba"; -- correctly returns 1 row select * from bugtest where username is NULL; -- correctly returns 0 rows select * from bugtest where username = ""; -- correctly returns no rows select * from bugtest where username = 0; -- !!! BUG: returns ALL ROWS?!!! Suggested fix: Fix so that if an integer value 0 is used in a WHERE clause comparing to a VarChar column, it correctly returns NO rows.