Bug #78951 select * from abc where xCol = 'dd', return row with xCol = 0, xCol is Double ty

• Submitted: 24 Oct 2015 4:12
• Modified: 27 Oct 2015 1:24
• Reporter: deepansh aggarwal
• Status: Verified
• Category: MySQL Server: Data Types
• Severity: S4 (Feature request)
• Version: 5.5.24
• OS: Windows
• CPU Architecture: Any
• Tags: Double column string in where clause

[24 Oct 2015 4:12] deepansh aggarwal

Description:
In my table, there is a column notn of type DOUBLE. When I am running following query select * from drcrrecord where notn = 'dd';
The result is showing as:

notn   datefield              <blah>  <blah>
'0', '2015-10-03 01:00:00', '1562-G', 'B-12529', 'S', 'REFUNDED', '2500', 'C', '2015-10-03 01:00:00', NULL, NULL, NULL, NULL

This is coming for any string given in where clause. 

How to repeat:
Create table with column type double. Fill up some data.
Make sure that there is some data with value '0' in the column on which you will query
Run query, in where clause use String after =

It will show up result with row having value 0.

[24 Oct 2015 7:11] MySQL Verification Team

As far as I can see,  this is a request to have some kind of STRICT mode apply to selects?   Here's the example I'm using.

mysql> set sql_mode='STRICT_ALL_TABLES';
Query OK, 0 rows affected (0.00 sec)

mysql> drop table if exists t;
Query OK, 0 rows affected (0.00 sec)

mysql> create table t(a double)engine=innodb;
Query OK, 0 rows affected (0.02 sec)

mysql> insert into t values(0),(1),(2);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from t where a='d';
+------+
| a    |
+------+
|    0 |
+------+
1 row in set, 1 warning (0.00 sec)

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

[24 Oct 2015 7:19] MySQL Verification Team

personally I'd love this,  because it would let application developers catch bad queries sooner.  we all know nobody checks warnings ;)

[27 Oct 2015 1:24] deepansh aggarwal

Yeah I agree. But with increasing developing frameworks, nobody can see warnings in java applications, since they get the result, incorrect though :P