| Bug #720 | Select Problem | ||
|---|---|---|---|
| Submitted: | 25 Jun 2003 8:11 | Modified: | 27 Jun 2003 10:25 |
| Reporter: | Giovanni Monferdini | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server: InnoDB storage engine | Severity: | S2 (Serious) |
| Version: | 4.1 alpha | OS: | Linux (linux RH 7.1) |
| Assigned to: | Ramil Kalimullin | CPU Architecture: | Any |
[27 Jun 2003 2:29]
Heikki Tuuri
Hi!
Looks like you have to use the CAST(hexnumber AS CHAR) function to force MySQL to treat the hex string as a character string. Then it works. But the documentation at http://www.mysql.com/doc/en/Hexadecimal_values.html claims that you would not need to use the CAST() function.
The behavior is the same for MyISAM and InnoDB tables.
Regards,
Heikki
mysql> show create table s10;
+-------+-----------------------------------------------------------------------
-------------------------------------------------------------------------+
| Table | Create Table
|
+-------+-----------------------------------------------------------------------
-------------------------------------------------------------------------+
| s10 | CREATE TABLE `s10` (
`a` char(9) binary NOT NULL default '',
`b` char(9) default NULL,
PRIMARY KEY (`a`),
KEY `b` (`b`)
) TYPE=InnoDB |
+-------+-----------------------------------------------------------------------
-------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> insert into s10 values (0x111213, 0x111213);
Query OK, 1 row affected (0.00 sec)
mysql> select * from s10 where a > CAST(0x11 AS CHAR);
Empty set (0.00 sec)
mysql> select * from s10 where a > CAST(0x110000 AS CHAR);
+-----+------+
| a | b |
+-----+------+
| | |
+-----+------+
1 row in set (0.00 sec)
mysql> select * from s10 where a = CAST(0x110000 AS CHAR);
Empty set (0.00 sec)
mysql> select * from s10 where a < CAST(0x110000 AS CHAR);
Empty set (0.00 sec)
mysql> select * from s10 where b > CAST(0x110000 AS CHAR);
+-----+------+
| a | b |
+-----+------+
| | |
+-----+------+
1 row in set (0.00 sec)
mysql> select * from s10 where b = CAST(0x111213 AS CHAR);
+-----+------+
| a | b |
+-----+------+
| | |
+-----+------+
1 row in set (0.00 sec)
mysql> select * from s10 where b > CAST(0x111213 AS CHAR);
Empty set (0.00 sec)
[27 Jun 2003 3:00]
Ramil Kalimullin
Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://www.mysql.com/documentation/ and the instructions on how to report a bug at http://bugs.mysql.com/how-to-report.php First of all, you should correct a typo in your query: should be '... PE_TAB='SAL.01'...' instead of '...PE_TAB='SAL.1'...'. Second: expression '' > 0x19 is FALSE. '' is empty string, 0x19 is one-char string, so it's greater than '', so it's expected behavior.
[27 Jun 2003 10:25]
Heikki Tuuri
Ramil,
I tested the behavior on 4.0.14 and looks like there MySQL treats the hex number as an integer in a '<' comparison and converts the COLUMN to an integer, too. The conversion produces value 0 for the column. But a pure SELECT 0x7865 treats the hex string as a string!
In 4.1.1 MySQL treats the hex number as a string in both contexts. The docs claim that
"In numeric context these act like an integer (64-bit precision). In string context these act like a binary string where each pair of hex digits is converted to a character:"
Conclusion: you have use an explicit typecast in 4.0 to be sure what data type the hex string gets internally. The docs should warn users that the 'context' of a hex string is determined by complicated rules.
Regards,
Heikki
heikki@hundin:~/mysql-4.0/client> mysql test
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 4.0.14-debug-log
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> show create table s10;
+-------+-----------------------------------------------------------------------
-------------------------------------------------------------------------+
| Table | Create Table
|
+-------+-----------------------------------------------------------------------
-------------------------------------------------------------------------+
| s10 | CREATE TABLE `s10` (
`a` char(9) binary NOT NULL default '',
`b` char(9) default NULL,
PRIMARY KEY (`a`),
KEY `b` (`b`)
) TYPE=MyISAM |
+-------+-----------------------------------------------------------------------
-------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> insert into s10 values ('abc', 'abc');
Query OK, 1 row affected (0.01 sec)
mysql> select * from s10 where a > 0x10;
Empty set (0.00 sec)
mysql> select * from s10 where a > CAST(0x10 AS CHAR);
+-----+------+
| a | b |
+-----+------+
| abc | abc |
+-----+------+
1 row in set (0.00 sec)
mysql>
BUT:
mysql> select 0x4344;
+--------+
| 0x4344 |
+--------+
| CD |
+--------+
1 row in set (0.00 sec)
mysql>
"
MySQL supports hexadecimal values. In numeric context these act like an integer (64-bit precision). In string context these act like a binary string where each pair of hex digits is converted to a character:
mysql> SELECT x'4D7953514C';
-> MySQL
mysql> SELECT 0xa+0;
-> 10
mysql> SELECT 0x5061756c;
-> Paul
In MySQL 4.1 (and in MySQL 4.0 when using the --new option) the default type of of a hexadecimal value is a string. If you want to be sure that the string is threated as a number, you can use CAST( ... AS UNSIGNED) on the hexadecimal value.
The x'hexstring' syntax (new in 4.0) is based on standard SQL and the 0x syntax is based on ODBC. Hexadecimal strings are often used by ODBC to supply values for BLOB columns. You can convert a string or a number to string in hexadecimal format with the HEX() function.
"
Description: We would make a SELECT with a Value low that space (for example with 0x19 ). This is our SELECT that return nothing : select PE_SIGLA,PE_STRUCED from TPPACED where PE_SIGLA='PT' and PE_TAB='SAL.1' and PE_STRUCED='3' and PE_ELCED1='0001' and PE_ELCED2='*' and PE_ELCED3='*' and PE_ELCED4='*' AND PE_ELCED6> 0x19; Thanks a LOT!! How to repeat: This is our TABLE : CREATE TABLE TPPACED ( PE_SOUB char(2) NOT NULL default '', PE_VERS char(2) NOT NULL default '', PE_SIGLA char(2) NOT NULL default '', PE_TAB char(7) NOT NULL default '', PE_STRUCED char(1) NOT NULL default '', PE_ELCED1 char(9) NOT NULL default '', PE_ELCED2 char(9) NOT NULL default '', PE_ELCED3 char(9) NOT NULL default '', PE_ELCED4 char(9) NOT NULL default '', PE_ELCED5 char(9) NOT NULL default '', PE_ELCED6 char(9) NOT NULL default '', PE_ELCED7 char(9) NOT NULL default '', PE_ELCED8 char(9) NOT NULL default '', PE_USO_RIFER decimal(5,0) default NULL, PE_TIPO_RIB char(1) NOT NULL default '', PE_TIPO_ACC char(1) NOT NULL default '', PE_STRURIF char(1) NOT NULL default '', PE_ELRIF1 char(9) default NULL, PE_ELRIF2 char(9) default NULL, PE_ELRIF3 char(9) default NULL, PE_ELRIF4 char(9) default NULL, PE_ELRIF5 char(9) default NULL, PE_ELRIF6 char(9) default NULL, PE_ELRIF7 char(9) default NULL, PE_ELRIF8 char(9) default NULL, PE_STRUCHIU char(1) NOT NULL default '', PE_ELCHIU1 char(9) default NULL, PE_ELCHIU2 char(9) default NULL, PE_ELCHIU3 char(9) default NULL, PE_ELCHIU4 char(9) default NULL, PE_ELCHIU5 char(9) default NULL, PE_ELCHIU6 char(9) default NULL, PE_ELCHIU7 char(9) default NULL, PE_ELCHIU8 char(9) default NULL, PE_OGCHIUS char(3) default NULL, PE_ELINCLDA1 char(3) default NULL, PE_ELINCLDA2 char(3) default NULL, PE_ELINCLDA3 char(3) default NULL, PE_ELINCLDA4 char(3) default NULL, PE_ELINCLDA5 char(3) default NULL, PE_ELINCLDA6 char(3) default NULL, PE_ELINCLDA7 char(3) default NULL, PE_ELINCLDA8 char(3) default NULL, PE_ELINCLDA9 char(3) default NULL, PE_ELINCLDA10 char(3) default NULL, PE_ELINCLA1 char(3) default NULL, PE_ELINCLA2 char(3) default NULL, PE_ELINCLA3 char(3) default NULL, PE_ELINCLA4 char(3) default NULL, PE_ELINCLA5 char(3) default NULL, PE_ELINCLA6 char(3) default NULL, PE_ELINCLA7 char(3) default NULL, PE_ELINCLA8 char(3) default NULL, PE_ELINCLA9 char(3) default NULL, PE_ELINCLA10 char(3) default NULL, PE_ELESCLDA1 char(3) default NULL, PE_ELESCLDA2 char(3) default NULL, PE_ELESCLDA3 char(3) default NULL, PE_ELESCLDA4 char(3) default NULL, PE_ELESCLDA5 char(3) default NULL, PE_ELESCLDA6 char(3) default NULL, PE_ELESCLDA7 char(3) default NULL, PE_ELESCLDA8 char(3) default NULL, PE_ELESCLDA9 char(3) default NULL, PE_ELESCLDA10 char(3) default NULL, PE_ELESCLA1 char(3) default NULL, PE_ELESCLA2 char(3) default NULL, PE_ELESCLA3 char(3) default NULL, PE_ELESCLA4 char(3) default NULL, PE_ELESCLA5 char(3) default NULL, PE_ELESCLA6 char(3) default NULL, PE_ELESCLA7 char(3) default NULL, PE_ELESCLA8 char(3) default NULL, PE_ELESCLA9 char(3) default NULL, PE_ELESCLA10 char(3) default NULL, PE_TIPO_STATO char(1) NOT NULL default '', PE_ELSTA1 char(1) NOT NULL default '', PE_ELSTA2 char(1) NOT NULL default '', PE_ELSTA3 char(1) NOT NULL default '', PE_ELSTA4 char(1) NOT NULL default '', PE_ELSTA5 char(1) NOT NULL default '', PE_ELSTA6 char(1) NOT NULL default '', PE_DATA_AGG char(8) default NULL, PE_PROG_RICEV decimal(5,0) default NULL, PE_CODALG_I char(3) default NULL, PE_CODALG_Q char(3) default NULL, PE_CODALG_D char(3) default NULL, PE_CODALG_C char(3) default NULL, PE_DIVISA char(3) default NULL, PE_TIPO char(1) NOT NULL default '', UNIQUE KEY TPPACED_KEY1 (PE_SIGLA,PE_TAB,PE_STRUCED,PE_ELCED1,PE_ELCED2,PE_ELCED3,PE_ELCED4,PE_ELCED5,PE_ELCED6,PE_ELCED7,PE_ELCED8), UNIQUE KEY TPPACED_KEY2 (PE_SIGLA,PE_TAB,PE_STRUCED,PE_ELCED1,PE_ELCED2,PE_ELCED3,PE_ELCED4,PE_ELCED5,PE_ELCED6,PE_ELCED7,PE_ELCED8,PE_TIPO_RIB) ) TYPE=InnoDB CHARSET=latin1; -- -- Dumping data for table 'TPPACED' -- INSERT INTO TPPACED VALUES ('','00','PR','B=C','A','*','*','','','','','','',NULL,'','C','',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'','','','','','','','','20001117',1,'BCC',NULL,NULL,NULL,NULL,'N'); INSERT INTO TPPACED VALUES ('','00','PR','B=C','B','*','*','','','','','','',NULL,'','C','',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'A','010','*',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'','','','','','','','','20000218',1,'BCC',NULL,NULL,NULL,NULL,'C'); INSERT INTO TPPACED VALUES ('','00','PR','TEL','A','010','*','','','','','','',NULL,'','C','',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'','','','','','','','','20001002',NULL,'001','QTA',NULL,NULL,NULL,'N'); INSERT INTO TPPACED VALUES ('','00','PR','VTS','A','*','*','*','*','PRODNT','5108','','',NULL,'','C','',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'C','050','*','*','*','5108',NULL,NULL,NULL,'COS',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'','','','','','','','','20011218',1,'VTS',NULL,NULL,NULL,NULL,'D'); INSERT INTO TPPACED VALUES ('','00','PT','SAL.01','3','0001','*','*','*','*','','','',NULL,'','C','',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'4','0001','*','*','*','0401010',NULL,NULL,NULL,'CON',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'','','','','','','','','20030618',1,'SA1',NULL,NULL,NULL,NULL,'D'); INSERT INTO TPPACED VALUES ('','00','PT','SAL.02','3','0001','*','*','*','*','','','',NULL,'','C','',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'','','','','','','','','20030621',1,'SA2',NULL,NULL,NULL,NULL,'N'); INSERT INTO TPPACED VALUES ('','00','PT','SAL.03','3','0001','*','*','*','9090005','','','',NULL,'','C','',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'','','','','','','','','20030621',1,NULL,'SA3',NULL,NULL,NULL,'N'); INSERT INTO TPPACED VALUES ('','00','PT','SAL.04','3','0001','*','*','*','9090010','','','',NULL,'','C','',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'','','','','','','','','20030621',1,NULL,'SA4',NULL,NULL,NULL,'N'); INSERT INTO TPPACED VALUES ('','00','PT','SAL.05','3','0001','*','*','*','9090015','','','',NULL,'','C','',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'','','','','','','','','20030621',1,NULL,'SA5',NULL,NULL,NULL,'N'); INSERT INTO TPPACED VALUES ('','00','PT','SAL.06','3','0001','*','*','*','9090030','','','',NULL,'','C','',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'4','9001','*','*','*','0401010',NULL,NULL,NULL,'CON',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'','','','','','','','','20030621',1,'SA6',NULL,NULL,NULL,NULL,'D');