MySQL Bugs: #73031: Wrong result in SELECT query

Bug #73031	Wrong result in SELECT query
Submitted:	18 Jun 2014 9:31	Modified:	18 Jun 2014 14:21
Reporter:	giovanni palozzi	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Windows	Severity:	S2 (Serious)
Version:	5.0.92-enterprise-log and 5.6.15-log	OS:	Windows (Only tested on Windows)
Assigned to:		CPU Architecture:	Any
Tags:	Alphanumeric, INT, SELECT, where

Description:
The query:
SELECT * FROM `timecalendar` WHERE lingua = "ne" AND anno =2014 AND mese >5
AND mese <10 AND numerotour = "t1" ORDER BY mese, giorno

return data but the "lingua" column is defined as int(11). The returned data doesn't macth obviously the "ne" value.

Follow the table definition:

CREATE TABLE IF NOT EXISTS `timecalendar` (
  `giorno` int(11) NOT NULL,
  `mese` int(11) NOT NULL,
  `anno` int(11) NOT NULL,
  `calt` int(11) NOT NULL,
  `lingua` int(11) NOT NULL COMMENT 'o=en-1=it-2=de-3=ne',
  `numerotour` varchar(4) NOT NULL,
  `orariopartenza` varchar(30) NOT NULL,
  UNIQUE KEY `giorno` (`giorno`,`mese`,`anno`,`lingua`,`numerotour`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

The problem is that there is not an syntax error report when you execute the query.

How to repeat:
there is not any difficulty to repet the error.

Suggested fix:
Use the correct syntax for where using the correct data type.

Thank you for the report.
I cannot reproduce this with latest GA versions and with dummy data. Could you please provide repeatable test case(subset of data, exact query etc), conf file used etc.

Thanks,
Umesh

This is an example of data file

Attachment: timecalendar.backup.sql (application/octet-stream, text), 162.87 KiB.

I had alredy included an example of query

Imho - this is an expected behavior, when an operator is used with operands of different types, type conversion occurs to make the operands compatible
In numeric context string literal is interpreted as number, char by char, if not a number it ends up as 0.

Please see http://dev.mysql.com/doc/refman/5.6/en/type-conversion.html

mysql> SELECT 0 = 'x6';
        -> 1

or

mysql> select 0='Hello';
+-----------+
| 0='Hello' |
+-----------+
|         1 |
+-----------+
1 row in set, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+-------------------------------------------+
| Level   | Code | Message                                   |
+---------+------+-------------------------------------------+
| Warning | 1292 | Truncated incorrect DOUBLE value: 'Hello' |
+---------+------+-------------------------------------------+
1 row in set (0.00 sec)

mysql> use test
Database changed
mysql> source /tmp/timecalendar.backup.sql;
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 1473 rows affected (0.03 sec)
Records: 1473  Duplicates: 0  Warnings: 0

Query OK, 1472 rows affected (0.02 sec)
Records: 1472  Duplicates: 0  Warnings: 0

Query OK, 1473 rows affected (0.02 sec)
Records: 1473  Duplicates: 0  Warnings: 0

Query OK, 170 rows affected (0.00 sec)
Records: 170  Duplicates: 0  Warnings: 0

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM `timecalendar` WHERE lingua = "ne" AND anno =2014 AND mese >5
    -> AND mese <10 AND numerotour = "t1" ORDER BY mese, giorno;
+--------+------+------+------+--------+------------+----------------+
| giorno | mese | anno | calt | lingua | numerotour | orariopartenza |
+--------+------+------+------+--------+------------+----------------+
|      1 |    6 | 2014 |    2 |      0 | t1         | 09:00          |
|      2 |    6 | 2014 |    2 |      0 | t1         | 09:00          |
|      3 |    6 | 2014 |    2 |      0 | t1         | 09:00          |
..
..
..
.
|     25 |    9 | 2014 |    2 |      0 | t1         | 09:00          |
|     26 |    9 | 2014 |    2 |      0 | t1         | 09:30          |
|     27 |    9 | 2014 |    2 |      0 | t1         | 09:30          |
|     28 |    9 | 2014 |    2 |      0 | t1         | 09:30          |
|     29 |    9 | 2014 |    2 |      0 | t1         | 09:30          |
|     30 |    9 | 2014 |    2 |      0 | t1         | 09:30          |
|     31 |    9 | 2014 |    2 |      0 | t1         | 09:30          |
+--------+------+------+------+--------+------------+----------------+
123 rows in set, 1 warning (0.01 sec)

mysql> show warnings;
+---------+------+----------------------------------------+
| Level   | Code | Message                                |
+---------+------+----------------------------------------+
| Warning | 1292 | Truncated incorrect DOUBLE value: 'ne' |
+---------+------+----------------------------------------+
1 row in set (0.00 sec)

^^ If you notice, this returns where lingua is 0, which is correct in this case. Had this returned non-zero lingua values then could have been a bug.