Bug #60254 SQL Query returning wrong records / invalid match
Submitted: 25 Feb 2011 16:48 Modified: 22 Mar 2011 17:48
Reporter: Mike Reid Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.1.53 OS:Linux
Assigned to: CPU Architecture:Any
Tags: editor, IN, like, match, query, SQL, workbench

[25 Feb 2011 16:48] Mike Reid
Description:
In Workbench for Mac, I've noticed a few occasions where the result set seems invalid based on the intended SQL. Under a few basic scenarios I can repeat this behavior of getting Workbench to display records that shouldn't be there based on the SQL.

How to repeat:
CREATE DATABASE `demo`;

CREATE  TABLE `demo`.`test` (
  `varchar` VARCHAR(10) NOT NULL ,
  `char` CHAR(10) NOT NULL ,
  `text` TEXT NOT NULL );

INSERT INTO `demo`.`test` (`varchar`,`char`,`text`) VALUES ('var_data','char_data','text_data');

-- Expected 0 results, but 1 record was returned. No records contain `varchar` with a value of "0"
SELECT * FROM `demo`.`test` WHERE `varchar` = 0;

-- When ensuring string type comparison, result works as expected.
SELECT * FROM `demo`.`test` WHERE `varchar` = '0';

-- Expected 1 result per query, result works as expected.
SELECT * FROM `demo`.`test` WHERE `varchar` IN ('var_data');
SELECT * FROM `demo`.`test` WHERE `char` IN ('char_data');
SELECT * FROM `demo`.`test` WHERE `text` IN ('text_data');

-- Expected 0 results per query, but 1 record is returned per query. No records contain "*_data                    " (NOTE the 20 extra trailing spaces after '_data <--------- '....)
SELECT * FROM `demo`.`test` WHERE `varchar` IN ('var_data                    ');
SELECT * FROM `demo`.`test` WHERE `char` IN ('char_data                    ');
SELECT * FROM `demo`.`test` WHERE `text` IN ('text_data                    ');

Also, I realize my naming convention for this example uses escaped, reserved words (varchar,char,text), however this is unrelated to this particular problem, I can reproduce this regardless.

Suggested fix:
Review SQL query parsing logic and intended match conditions. Ensure valid result set under these conditions:

- Cross-datatype comparisons? (String vs. Integer, etc)
- IN('...') statements with trailing whitespace
[4 Mar 2011 16:28] Mike Reid
Originally thought this bug was limited to Mac, but looks like it's happening on Windows, too. :(
[15 Mar 2011 20:22] Johannes Taxacher
all these cases return the same resultsets when executed via the mysql commandline client - so if at all, these are server bugs rather than problems in WB directly.
Can you confirm that?
[15 Mar 2011 20:36] Mike Reid
Sure enough, you are correct! MySQL v5.1.53 - I have confirmed this issue is directly with MySQL Server and not WB. My apologies...
[15 Mar 2011 20:37] Mike Reid
Not a bug with Workbench
[15 Mar 2011 20:39] Mike Reid
Originally I thought this to be an issue with Workbench, but after some attempts on mysql command line, it seems to be related to mysql server directly.

I cannot understand why this behavior would be by design, so I am presuming it is still a bug.
[22 Mar 2011 0:54] Sveta Smirnova
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

Please read about type conversion in expression evaluation at http://dev.mysql.com/doc/refman/5.1/en/type-conversion.html
[22 Mar 2011 1:27] Mike Reid
Thank you for the information on Type Conversion. However, does this also cover the IN ('value     ') example I also referenced?

E.g.
 -- Expected 0 results per query, but 1 record is returned per query. No records contain "*_data " (NOTE the 20 extra trailing spaces after '_data <---------'....) SELECT * FROM `demo`.`test` WHERE `varchar` IN ('var_data '); SELECT * FROM `demo`.`test` WHERE `char` IN ('char_data '); SELECT * FROM `demo`.`test` WHERE `text` IN ('text_data ');

A
[22 Mar 2011 17:48] Sveta Smirnova
Thank you for the feedback.

Please read http://dev.mysql.com/doc/refman/5.1/en/char.html also.