| Bug #67517 | There is bug when i use CONV function in predicates of sql query , | ||
|---|---|---|---|
| Submitted: | 8 Nov 2012 11:24 | Modified: | 29 Jan 2013 10:40 |
| Reporter: | anu baby | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server: Data Types | Severity: | S2 (Serious) |
| Version: | 5.5.30,5.6.10 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | CONV function, datatype conversion | ||
[11 Nov 2012 18:04]
Valeriy Kravchuk
Please, send the output of:
explain extended SELECT _FFID FROM CsFFPCBValid WHERE CONV(_FFID,16,10) > 12;
show warnings\G
explain extended SELECT _FFID FROM CsFFPCBValid WHERE CONV(_FFID,16,10) > CONV('c',16,10);
show warnings\G
show create table CsFFPCBValid\G
[12 Nov 2012 2:54]
anu baby
The results obtained by executing the given queries are given below..
Query
*****
explain extended SELECT _FFID FROM CsFFPCBValid WHERE CONV(_FFID,16,10)
> 12;
show warnings\G
OUTPUT IS..........
id select_type table type possible_keys key key_len ref
--------------------------------------------------------------------------------
1 SIMPLE CsFFPCBValid ALL (NULL) (NULL) (NULL) (NULL)
rows filtered Extra
-----------------------
5 100 Using where
Query
*****
explain extended SELECT _FFID FROM CsFFPCBValid WHERE CONV(_FFID,16,10)
> CONV('c',16,10);
show warnings\G
OUTPUT is...........
id select_type table type possible_keys key key_len ref
--------------------------------------------------------------------------------
1 SIMPLE CsFFPCBValid ALL (NULL) (NULL) (NULL) (NULL)
rows filtered Extra
-----------------------
5 100 Using where
Query
******
show create table CsFFPCBValid;
show WARNINGS\G
OUTPUT is....
Table Create Table
--------------------------------------------------------------------------------
CsFFPCBValid CREATE TABLE `csffpcbvalid` (`_Count` text,`_VLD`text,`_FFID`
text) ENGINE=InnoDB DEFAULT CHARSET=latin1
[12 Nov 2012 15:37]
MySQL Verification Team
Please provide the sql script with create table, insert data, queries statements and the real result and expected result. Thanks.
[13 Nov 2012 16:06]
anu baby
table created using the query.......
create table CsFFPCBValid(_Count text,_FFID text,_VLD text)
Inserted the values using following queries
insert into CsFFPCBValid values('00000001','0','1')
insert into CsFFPCBValid values('00000002','3','1')
insert into CsFFPCBValid values('00000003','1','1')
insert into CsFFPCBValid values('00000004','2','1')
insert into CsFFPCBValid values('00000005','e','1')
Table created is........
_Count _FFID _VLD
-------------------------
1 0 1
2 3 1
3 1 1
4 2 1
5 e 1
QUERY
*****
SELECT _FFID FROM CsFFPCBValid WHERE CONV(_FFID,16,10) > 12
OUTPUT is
--------
_FFID
------
e
QUERY
*****
SELECT _FFID FROM CsFFPCBValid WHERE CONV(_FFID,16,10) > CONV('c',16,10)
OUTPUT is
-------=
_FFID
------
3
2
e
Both queries have same meaning.But second query gives wrong result.Expecting same result for both.
[19 Nov 2012 10:59]
anu baby
plz give the reply
[27 Jan 2013 11:21]
Valeriy Kravchuk
This is clearly repeatable with 5.5.29 on Windows also:
mysql> select version();
+-----------+
| version() |
+-----------+
| 5.5.29 |
+-----------+
1 row in set (0.00 sec)
mysql> create table CsFFPCBValid(_Count text,_FFID text,_VLD text);
Query OK, 0 rows affected (2.01 sec)
mysql> insert into CsFFPCBValid values('00000001','0','1');
Query OK, 1 row affected (0.23 sec)
mysql> insert into CsFFPCBValid values('00000002','3','1');
Query OK, 1 row affected (0.03 sec)
mysql> insert into CsFFPCBValid values('00000003','1','1');
Query OK, 1 row affected (0.08 sec)
mysql> insert into CsFFPCBValid values('00000004','2','1');
Query OK, 1 row affected (0.08 sec)
mysql> insert into CsFFPCBValid values('00000005','e','1');
Query OK, 1 row affected (0.03 sec)
mysql> SELECT _FFID FROM CsFFPCBValid WHERE CONV(_FFID,16,10) > 12;
+-------+
| _FFID |
+-------+
| e |
+-------+
1 row in set (0.27 sec)
mysql> SELECT _FFID FROM CsFFPCBValid WHERE CONV(_FFID,16,10) > CONV('c',16,10);
+-------+
| _FFID |
+-------+
| 3 |
| 2 |
| e |
+-------+
3 rows in set (0.00 sec)
mysql> explain extended SELECT _FFID FROM CsFFPCBValid WHERE CONV(_FFID,16,10) >
CONV('c',16,10);
+----+-------------+--------------+------+---------------+------+---------+-----
-+------+----------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref
| rows | filtered | Extra |
+----+-------------+--------------+------+---------------+------+---------+-----
-+------+----------+-------------+
| 1 | SIMPLE | CsFFPCBValid | ALL | NULL | NULL | NULL | NULL
| 5 | 100.00 | Using where |
+----+-------------+--------------+------+---------------+------+---------+-----
-+------+----------+-------------+
1 row in set, 1 warning (0.08 sec)
mysql> show warnings\G
*************************** 1. row ***************************
Level: Note
Code: 1003
Message: select `test`.`csffpcbvalid`.`_FFID` AS `_FFID` from `test`.`csffpcbval
id` where (conv(`test`.`csffpcbvalid`.`_FFID`,16,10) > <cache>(conv('c',16,10)))
1 row in set (0.00 sec)
mysql> explain extended SELECT _FFID FROM CsFFPCBValid WHERE CONV(_FFID,16,10) >
12;
+----+-------------+--------------+------+---------------+------+---------+-----
-+------+----------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref
| rows | filtered | Extra |
+----+-------------+--------------+------+---------------+------+---------+-----
-+------+----------+-------------+
| 1 | SIMPLE | CsFFPCBValid | ALL | NULL | NULL | NULL | NULL
| 5 | 100.00 | Using where |
+----+-------------+--------------+------+---------------+------+---------+-----
-+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql> show warnings\G
*************************** 1. row ***************************
Level: Note
Code: 1003
Message: select `test`.`csffpcbvalid`.`_FFID` AS `_FFID` from `test`.`csffpcbval
id` where (conv(`test`.`csffpcbvalid`.`_FFID`,16,10) > 12)
1 row in set (0.00 sec)
[27 Jan 2013 16:36]
Matthew Lord
mysql> select version();
+-------------------------------------------+
| version() |
+-------------------------------------------+
| 5.5.27-enterprise-commercial-advanced-log |
+-------------------------------------------+
1 row in set (0.00 sec)
mysql> use test;
Database changed
mysql> create table CsFFPCBValid(_Count text,_FFID text,_VLD text);
Query OK, 0 rows affected (0.12 sec)
mysql> insert into CsFFPCBValid values('00000001','0','1');
Query OK, 1 row affected (0.03 sec)
mysql> insert into CsFFPCBValid values('00000002','3','1');
Query OK, 1 row affected (0.01 sec)
mysql> insert into CsFFPCBValid values('00000003','1','1')
-> ;
Query OK, 1 row affected (0.01 sec)
mysql> insert into CsFFPCBValid values('00000004','2','1');
Query OK, 1 row affected (0.00 sec)
mysql> insert into CsFFPCBValid values('00000005','e','1');
Query OK, 1 row affected (0.00 sec)
mysql> SELECT _FFID, CONV(_FFID,16,10) as conv FROM CsFFPCBValid;
+-------+------+
| _FFID | conv |
+-------+------+
| 0 | 0 |
| 3 | 3 |
| 1 | 1 |
| 2 | 2 |
| e | 14 |
+-------+------+
5 rows in set (0.00 sec)
mysql> select CONV('c',16,10);
+-----------------+
| CONV('c',16,10) |
+-----------------+
| 12 |
+-----------------+
1 row in set (0.00 sec)
mysql> SELECT _FFID, CONV(_FFID,16,10) as conv FROM CsFFPCBValid having conv > 12;
+-------+------+
| _FFID | conv |
+-------+------+
| e | 14 |
+-------+------+
1 row in set (0.00 sec)
mysql> SELECT _FFID FROM CsFFPCBValid WHERE CONV(_FFID,16,10) > CONV('c',16,10);
+-------+
| _FFID |
+-------+
| 3 |
| 2 |
| e |
+-------+
3 rows in set (0.02 sec)
I will test this with 5.5.31 and 5.6.10 tomorrow. Assuming that I can still repeat it, then I will mark this as verified and copy it to the development team.
Thank you for the great bug report, Anu!
[28 Jan 2013 17:32]
Matthew Lord
Verified in 5.5.30 and 5.6.10 with:
use test;
create table CsFFPCBValid(_Count text,_FFID text,_VLD text);
insert into CsFFPCBValid values('00000001','0','1');
insert into CsFFPCBValid values('00000002','3','1');
insert into CsFFPCBValid values('00000003','1','1');
insert into CsFFPCBValid values('00000004','2','1');
insert into CsFFPCBValid values('00000005','e','1');
SELECT _FFID, CONV(_FFID,16,10) as conv FROM CsFFPCBValid;
SELECT CONV('c',16,10);
SELECT _FFID, CONV(_FFID,16,10) as conv FROM CsFFPCBValid having conv > 12;
SELECT _FFID FROM CsFFPCBValid WHERE CONV(_FFID,16,10) > CONV('c',16,10);
[29 Jan 2013 10:40]
Øystein Grøvlen
This is behavior is according to the documentation. The CONV function returns a string. Hence, when comparing the results of two CONV calls, string comparison will be use. When comparing the result of CONV with a number, numeric comparison will be used. (See http://dev.mysql.com/doc/refman/5.5/en/type-conversion.html.) Further examples that illustrate this behavior: mysql> SELECT _FFID FROM CsFFPCBValid WHERE CONV(_FFID,16,10) > '12'; +-------+ | _FFID | +-------+ | 3 | | 2 | | e | +-------+ 3 rows in set (0.00 sec) mysql> SELECT _FFID FROM CsFFPCBValid WHERE cast(CONV(_FFID,16,10) as signed) > cast(CONV('c',16,10) as signed); +-------+ | _FFID | +-------+ | e | +-------+ 1 row in set (0.01 sec)
Description: table i used to execute the query is _Count _FFID _VLD ------------------------- 1 0 1 2 3 1 3 1 1 4 2 1 5 e 1 *********SELECT _FFID FROM CsFFPCBValid WHERE CONV(_FFID,16,10) > 12 When i executed the query given above i got the result as _FFID ------ e But when i executed the query below , i got different result even though these have same meaning *************SELECT _FFID FROM CsFFPCBValid WHERE CONV(_FFID,16,10) > CONV('c',16,10) _FFID ------ 3 2 e Why it is so? How to repeat: i am getting different results when executing queries having same meaning.Queries are.... SELECT _FFID FROM CsFFPCBValid WHERE CONV(_FFID,16,10) > 12 SELECT _FFID FROM CsFFPCBValid WHERE CONV(_FFID,16,10) > CONV('c',16,10). This bugs are repeating when i use these comparison operators other than = and<>