| 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: | |
| 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 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.
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)