MySQL Bugs: #60254: SQL Query returning wrong records / invalid match

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

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

Originally thought this bug was limited to Mac, but looks like it's happening on Windows, too. :(

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?

Sure enough, you are correct! MySQL v5.1.53 - I have confirmed this issue is directly with MySQL Server and not WB. My apologies...

Not a bug with Workbench

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.

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

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

Thank you for the feedback.

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