Bug #15881 expression = constant -> invalid results when they are of different types
Submitted: 20 Dec 2005 9:24 Modified: 16 Jan 2007 7:05
Reporter: jo soares Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:5.0.20-BK, 4.0.24 OS:Linux (Linux)
Assigned to: Georgi Kodinov CPU Architecture:Any

[20 Dec 2005 9:24] jo soares
Description:
As you can see in the example, mysql insert a 0 into  orecchio_ristampa char(1) but then mysql cannot find it.
-------------------------------------------------------------------------------------------------------------

create table test (marca char(15), orecchio_ristampa char(1),azienda char(8));

mysql> insert into test values('TS001928','D','006TS130');
Query OK, 1 row affected (0.00 sec)

mysql> insert into test values('TS001929','S','006TS130');
Query OK, 1 row affected (0.00 sec)

mysql> insert into test values('TS001930',0,'006ts130');
Query OK, 1 row affected (0.00 sec)

mysql> select * from test;
+----------------+-------------------+----------+
| marca          | orecchio_ristampa | azienda  |
+----------------+-------------------+----------+
| TS001928 | D                 | 006TS130 |
| TS001929 | S                 | 006TS130 |
| TS001930 | 0                 | 006ts130 |
+----------------+-------------------+----------+
3 rows in set (0.00 sec)

mysql> select  orecchio_ristampa, azienda  from test where azienda='006ts130' and orecchio_ristampa=0;
+-------------------+----------+
| orecchio_ristampa | azienda  |
+-------------------+----------+
| D                  | 006TS130 |
| S                  | 006TS130 |
| 0                  | 006ts130 |
+-------------------+----------+
3 rows in set (0.00 sec)

mysql> select DISTINCT orecchio_ristampa, azienda  from test where azienda='006ts130' and orecchio_ristampa=0;
+-------------------+----------+
| orecchio_ristampa | azienda  |
+-------------------+----------+
| D                 | 006TS130 |
+-------------------+----------+
1 row in set (0.01 sec)

How to repeat:

create table test (marca char(15), orecchio_ristampa char(1),azienda char(8));

mysql> insert into test values('TS001928','D','006TS130');
Query OK, 1 row affected (0.00 sec)

mysql> insert into test values('TS001929','S','006TS130');
Query OK, 1 row affected (0.00 sec)

mysql> insert into test values('TS001930',0,'006ts130');
Query OK, 1 row affected (0.00 sec)
[20 Dec 2005 11:08] jo soares
This is the behavior of postgresql:

create table test (marca char(15),
 orecchio_ristampa char(1),
 azienda char(8));

insert into test values('TS001928','D','006TS130');
insert into test values('TS001929','S','006TS130');
insert into test values('TS001930', 0 ,'006ts130');

select * from test;
       marca      | orecchio_ristampa | azienda
 -----------------+-------------------+----------
  TS001928        | D                 | 006TS130
  TS001929        | S                 | 006TS130
  TS001930        | 0                 | 006ts130
 (3 rows)

select  orecchio_ristampa, azienda  from test
 where azienda='006ts130' and orecchio_ristampa=0;

  orecchio_ristampa | azienda
 -------------------+----------
  0                 | 006ts130
 (1 row)

select DISTINCT orecchio_ristampa, azienda  from test
 where azienda='006ts130' and orecchio_ristampa=0;
  orecchio_ristampa | azienda
 -------------------+----------
  0                 | 006ts130
 (1 row)
[20 Dec 2005 12:57] Valeriy Kravchuk
Thank you for a bug report. Verified just as described on 5.0.18-BK (ChangeSet@1.1981, 2005-12-15 02:08:52-03:00) on Linux:

mysql> create table test (marca char(15),
    ->  orecchio_ristampa char(1),
    ->  azienda char(8));

Query OK, 0 rows affected (0.01 sec)

mysql> insert into test values('TS001928','D','006TS130');
Query OK, 1 row affected (0.00 sec)

mysql> insert into test values('TS001929','S','006TS130');
Query OK, 1 row affected (0.00 sec)

mysql> insert into test values('TS001930', 0 ,'006ts130');
Query OK, 1 row affected (0.00 sec)

mysql> select * from test;
+----------+-------------------+----------+
| marca    | orecchio_ristampa | azienda  |
+----------+-------------------+----------+
| TS001928 | D                 | 006TS130 |
| TS001929 | S                 | 006TS130 |
| TS001930 | 0                 | 006ts130 |
+----------+-------------------+----------+
3 rows in set (0.00 sec)

mysql> select  orecchio_ristampa, azienda  from test
    ->  where azienda='006ts130' and orecchio_ristampa=0;
+-------------------+----------+
| orecchio_ristampa | azienda  |
+-------------------+----------+
| D                 | 006TS130 |
| S                 | 006TS130 |
| 0                 | 006ts130 |
+-------------------+----------+
3 rows in set (0.00 sec)

mysql> select  orecchio_ristampa, azienda  from test  where azienda='006ts130' and orecchio_ristampa='0';
+-------------------+----------+
| orecchio_ristampa | azienda  |
+-------------------+----------+
| 0                 | 006ts130 |
+-------------------+----------+
1 row in set (0.00 sec)

mysql> select distinct  orecchio_ristampa, azienda  from test  where azienda='006ts130' and orecchio_ristampa=0;
+-------------------+----------+
| orecchio_ristampa | azienda  |
+-------------------+----------+
| D                 | 006TS130 |
+-------------------+----------+
1 row in set (0.00 sec)

mysql> select distinct  orecchio_ristampa, azienda  from test  where azienda='006ts130' and orecchio_ristampa='0';
+-------------------+----------+
| orecchio_ristampa | azienda  |
+-------------------+----------+
| 0                 | 006ts130 |
+-------------------+----------+
1 row in set (0.01 sec)

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.0.18    |
+-----------+
1 row in set (0.00 sec)

mysql> show create table test;
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                     |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| test  | CREATE TABLE `test` (
  `marca` char(15) default NULL,
  `orecchio_ristampa` char(1) default NULL,
  `azienda` char(8) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Engine=InnoDB and DEFAULT CHARSET=utf8 give the same result. Looks like it is a bug.
[13 Jan 2006 19:27] Gunnar von Boehn
This is not a bug.

The user does not compare a char column with a string
WHERE orecchio_ristampa='0'

But the user compares a char column with an integer.
WHERE orecchio_ristampa=0

For the comparison the string/char column is converted to an integer.

The comparison conversion rules are here: http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html

When converting strings to number, any string which does
not look like a number will be converted to a 0,
so the returned result is correct.

Kind regards
Gunnar von Boehn
[14 Jan 2006 8:23] Valeriy Kravchuk
Sorry, my fault. 3 rows that are returned when comparing char(1) column to 0 is formally not a bug, as that manual page explains:

"-      In all other cases, the arguments are compared as floating-point (real) numbers."

One can easily check:

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

mysql> select 'D' = 0;
+---------+
| 'D' = 0 |
+---------+
|       1 |
+---------+

But is it OK to get the following:

mysql> select  orecchio_ristampa, azienda  from test
    ->  where azienda='006ts130' and orecchio_ristampa=0;
+-------------------+----------+
| orecchio_ristampa | azienda  |
+-------------------+----------+
| D                 | 006TS130 |
| S                 | 006TS130 |
| 0                 | 006ts130 |
+-------------------+----------+
3 rows in set (0.00 sec)

and, at the same time:

mysql> select distinct  orecchio_ristampa, azienda  from test  where
azienda='006ts130' and orecchio_ristampa=0;
+-------------------+----------+
| orecchio_ristampa | azienda  |
+-------------------+----------+
| D                 | 006TS130 |
+-------------------+----------+
1 row in set (0.00 sec)

When looking for distinct values, why don't we compare char(1) column values as strings? 'D', 'S' and '0' are different, aren't they?
[14 Jan 2006 16:06] MySQL Verification Team
Valeriy,

Mysql returns results correctly as it contains DISTINCT for those two columns, which get converted to 0 both for WHERE clause, so everything is ok.
[24 Mar 2006 17:39] Valeriy Kravchuk
Test case, to copy and paste:

create table test (marca char(15), orecchio_ristampa char(1), azienda char(8));
insert into test values('TS001928','D','006TS130');
insert into test values('TS001929','S','006TS130');
insert into test values('TS001930', 0 ,'006ts130');

select  orecchio_ristampa, azienda  from test
where azienda='006ts130' and orecchio_ristampa=0;

This will produce CORRECT results:

+-------------------+----------+
| orecchio_ristampa | azienda  |
+-------------------+----------+
| D                 | 006TS130 |
| S                 | 006TS130 |
| 0                 | 006ts130 |
+-------------------+----------+
3 rows in set (0.00 sec)

But for this:

select distinct  orecchio_ristampa, azienda  from test  where
azienda='006ts130' and orecchio_ristampa=0;

you'll get:

+-------------------+----------+
| orecchio_ristampa | azienda  |
+-------------------+----------+
| D                 | 006TS130 |
+-------------------+----------+
1 row in set (0.00 sec)

while correct results are same as above:

+-------------------+----------+
| orecchio_ristampa | azienda  |
+-------------------+----------+
| D                 | 006TS130 |
| S                 | 006TS130 |
| 0                 | 006ts130 |
+-------------------+----------+
3 rows in set (0.00 sec)

See private comment from PeterG for the details.
[24 Mar 2006 17:43] Valeriy Kravchuk
Verified on 5.0.20-BK (ChangeSet@1.2108, 2006-03-23 22:29:53+01:00)
[13 Dec 2006 12:35] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/16879

ChangeSet@1.2583, 2006-12-13 14:34:19+02:00, gkodinov@macbook.gmz +3 -0
  Bug #15881: cast problems
   The optimizer removes expressions from GROUP BY/DISTINCT
   if they happen to participate in a <expression> = <const>
   predicates of the WHERE clause (the idea being that if
   it's always equal to a constant it can't have multiple 
   values).
   However for predicates where the expression and the 
   constant item are of different result type this is not
   valid (e.g. a string column compared to 0).
   Fixed by disabling the usage of predicates that compare 
   expressions and constants of different result type when 
   simplifying GROUP BY/DISTINCT.
[19 Dec 2006 15:31] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/17169

ChangeSet@1.2583, 2006-12-19 17:31:11+02:00, gkodinov@macbook.gmz +3 -0
  Bug #15881: cast problems
   The optimizer removes expressions from GROUP BY/DISTINCT
   if they happen to participate in a <expression> = <const>
   predicates of the WHERE clause (the idea being that if
   it's always equal to a constant it can't have multiple 
   values).
   However for predicates where the expression and the 
   constant item are of different result type this is not
   valid (e.g. a string column compared to 0).
   Fixed by additional check of the result types of the 
   expression and the constant and if they differ the 
   expression don't get removed from the group by list.
[22 Dec 2006 8:44] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/17310

ChangeSet@1.2583, 2006-12-22 10:44:24+02:00, gkodinov@macbook.gmz +3 -0
  Bug #15881: cast problems
   The optimizer removes expressions from GROUP BY/DISTINCT
   if they happen to participate in a <expression> = <const>
   predicates of the WHERE clause (the idea being that if
   it's always equal to a constant it can't have multiple 
   values).
   However for predicates where the expression and the 
   constant item are of different result type this is not
   valid (e.g. a string column compared to 0).
   Fixed by additional check of the result types of the 
   expression and the constant and if they differ the 
   expression don't get removed from the group by list.
[5 Jan 2007 11:01] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/17656

ChangeSet@1.2366, 2007-01-05 13:00:32+02:00, gkodinov@macbook.gmz +3 -0
  Bug #15881: cast problems
    The optimizer removes expressions from GROUP BY/DISTINCT
    if they happen to participate in a <expression> = <const>
    predicates of the WHERE clause (the idea being that if
    it's always equal to a constant it can't have multiple 
    values).
    However for predicates where the expression and the 
    constant item are of different result type this is not
    valid (e.g. a string column compared to 0).
    Fixed by additional check of the result types of the 
    expression and the constant and if they differ the 
    expression don't get removed from the group by list.
[5 Jan 2007 12:03] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/17660

ChangeSet@1.2366, 2007-01-05 14:02:50+02:00, gkodinov@macbook.gmz +3 -0
  Bug #15881: cast problems
    The optimizer removes expressions from GROUP BY/DISTINCT
    if they happen to participate in a <expression> = <const>
    predicates of the WHERE clause (the idea being that if
    it's always equal to a constant it can't have multiple 
    values).
    However for predicates where the expression and the 
    constant item are of different result type this is not
    valid (e.g. a string column compared to 0).
    Fixed by additional check of the result types of the 
    expression and the constant and if they differ the 
    expression don't get removed from the group by list.
[15 Jan 2007 8:10] Sergei Glukhov
Fixed in 5.0.34, 5.1.15-beta
[16 Jan 2007 7:05] Jon Stephens
Thank you for your bug report. This issue has been committed to our source repository of that product and will be incorporated into the next release.

If necessary, you can access the source repository and build the latest available version, including the bug fix. More information about accessing the source trees is available at

    http://dev.mysql.com/doc/en/installing-source.html

Updated synopsis.

Documented bugfix in 5.0.34 and 5.1.15 changelogs.