Bug #20928 | SELECT x - 2 = 0, where x='1' andx is SET('0','1') | ||
---|---|---|---|
Submitted: | 9 Jul 2006 13:02 | Modified: | 12 Jul 2006 17:23 |
Reporter: | Andrius Jankevicius | Email Updates: | |
Status: | Not a Bug | Impact on me: | |
Category: | MySQL Server | Severity: | S3 (Non-critical) |
Version: | 5.0.16-nt | OS: | Windows (windows server 2003 R2 x64) |
Assigned to: | CPU Architecture: | Any | |
Tags: | arithmetic, SET, type |
[9 Jul 2006 13:02]
Andrius Jankevicius
[9 Jul 2006 13:04]
Andrius Jankevicius
p.s. i have made a mistake in this line: a query SELECT `active` + 1 FROM `banners` WHERE id = 3 returns 1 when `active` is 1. The correction should be: a query SELECT `active` + 1 FROM `banners` WHERE id = 3 returns 3 when `active` is 1.
[9 Jul 2006 14:39]
Peter Laursen
A simplified example: ===================== /* MySQL 5.0.23 */ DROP TABLE if EXISTS onemore; CREATE TABLE `onemore` ( `id` int(11) NOT NULL auto_increment, `active` set('0','1') NOT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8; INSERT INTO `onemore` values (3,1); SELECT `active` + 1 FROM `banners` WHERE id = 3; /* returns 3 */ SELECT `active` FROM onemore WHERE `id` = 3; /* returns 0 */ TRUNCATE onemore; INSERT INTO `onemore` values (3,'1'); SELECT `active` + 1 FROM `banners` WHERE id = 3; /* returns 3 */ SELECT `active` FROM onemore WHERE `id` = 3; /* returns 1 */ DROP TABLE if EXISTS onemore; CREATE TABLE `onemore` ( `id` int(11) NOT NULL auto_increment, `active` enum('0','1') NOT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; INSERT INTO `onemore` values (3,1); SELECT `active` + 1 FROM `banners` WHERE id = 3; /* returns 3 */ SELECT `active` FROM onemore WHERE `id` = 3; /* returns 0 */ TRUNCATE onemore; INSERT INTO `onemore` values (3,'1'); SELECT `active` + 1 FROM `banners` WHERE id = 3; /* returns 3 */ SELECT `active` FROM onemore WHERE `id` = 3; /* returns 1 */ I do not understand this behaviour either!
[9 Jul 2006 14:57]
Peter Laursen
And even more strange this: select * from onemore; id active ------ ------ 3 1 alter table `test`.`banners` change `active` `active` bigint NOT NULL COLLATE utf8_general_ci select * from onemore; id active ------ ------ 3 2 Looks like Andrius uses SQLyog like I do! But I just verified everything the same way with command-line clinet
[9 Jul 2006 15:11]
Peter Laursen
I tested on an 'ordinary' WinXP SP2 ... not a 64 bit issue!
[9 Jul 2006 20:42]
Peter Laursen
Maybe more illustrative with some other data/values: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 12 to server version: 5.0.23-community-max-lo Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> USE test; Database changed mysql> mysql> DROP TABLE if EXISTS onemore; Query OK, 0 rows affected (0.02 sec) mysql> mysql> CREATE TABLE `onemore` ( -> `id` int(11) NOT NULL auto_increment, -> `active` set('10','100') NOT NULL, -> PRIMARY KEY (`id`) -> ) ENGINE=MyISAM DEFAULT CHARSET=utf8; Query OK, 0 rows affected (0.09 sec) mysql> mysql> INSERT INTO `onemore` values (3,10); Query OK, 1 row affected, 1 warning (0.01 sec) mysql> SELECT `active` + 1 FROM `banners` WHERE id = 3; +--------------+ | `active` + 1 | +--------------+ | 3 | +--------------+ 1 row in set (0.00 sec) mysql> SELECT `active` FROM onemore WHERE `id` = 3; +--------+ | active | +--------+ | 100 | +--------+ 1 row in set (0.00 sec) mysql> mysql> TRUNCATE onemore; Query OK, 0 rows affected (0.06 sec) mysql> mysql> INSERT INTO `onemore` values (3,'10'); Query OK, 1 row affected (0.00 sec) mysql> SELECT `active` + 1 FROM `banners` WHERE id = 3; +--------------+ | `active` + 1 | +--------------+ | 3 | +--------------+ 1 row in set (0.00 sec) mysql> SELECT `active` FROM onemore WHERE `id` = 3; +--------+ | active | +--------+ | 10 | +--------+ 1 row in set (0.00 sec) mysql> mysql> TRUNCATE onemore; Query OK, 0 rows affected (0.02 sec) mysql> mysql> INSERT INTO `onemore` values (3,100); Query OK, 1 row affected, 1 warning (0.01 sec) mysql> SELECT `active` + 1 FROM `banners` WHERE id = 3; +--------------+ | `active` + 1 | +--------------+ | 3 | +--------------+ 1 row in set (0.01 sec) mysql> SELECT `active` FROM onemore WHERE `id` = 3; +--------+ | active | +--------+ | | +--------+ 1 row in set (0.04 sec) mysql> mysql> TRUNCATE onemore; Query OK, 0 rows affected (0.03 sec) mysql> mysql> INSERT INTO `onemore` values (3,'100'); Query OK, 1 row affected (0.01 sec) mysql> SELECT `active` + 1 FROM `banners` WHERE id = 3; +--------------+ | `active` + 1 | +--------------+ | 3 | +--------------+ 1 row in set (0.00 sec) mysql> SELECT `active` FROM onemore WHERE `id` = 3; +--------+ | active | +--------+ | 100 | +--------+ 1 row in set (0.00 sec) mysql> mysql> DROP TABLE if EXISTS onemore; Query OK, 0 rows affected (0.03 sec) mysql> mysql> CREATE TABLE `onemore` ( -> `id` int(11) NOT NULL auto_increment, -> `active` enum('10','100') NOT NULL, -> PRIMARY KEY (`id`) -> ) ENGINE=MyISAM DEFAULT CHARSET=utf8; Query OK, 0 rows affected (0.08 sec) mysql> mysql> INSERT INTO `onemore` values (3,10); Query OK, 1 row affected, 1 warning (0.01 sec) mysql> SELECT `active` + 1 FROM `banners` WHERE id = 3; +--------------+ | `active` + 1 | +--------------+ | 3 | +--------------+ 1 row in set (0.00 sec) mysql> SELECT `active` FROM onemore WHERE `id` = 3; +--------+ | active | +--------+ | | +--------+ 1 row in set (0.01 sec) mysql> mysql> TRUNCATE onemore; Query OK, 0 rows affected (0.02 sec) mysql> mysql> INSERT INTO `onemore` values (3,'10'); Query OK, 1 row affected (0.00 sec) mysql> SELECT `active` + 1 FROM `banners` WHERE id = 3; +--------------+ | `active` + 1 | +--------------+ | 3 | +--------------+ 1 row in set (0.00 sec) mysql> SELECT `active` FROM onemore WHERE `id` = 3; +--------+ | active | +--------+ | 10 | +--------+ 1 row in set (0.01 sec) mysql> mysql> TRUNCATE onemore; Query OK, 0 rows affected (0.03 sec) mysql> mysql> INSERT INTO `onemore` values (3,100); Query OK, 1 row affected, 1 warning (0.01 sec) mysql> SELECT `active` + 1 FROM `banners` WHERE id = 3; +--------------+ | `active` + 1 | +--------------+ | 3 | +--------------+ 1 row in set (0.00 sec) mysql> SELECT `active` FROM onemore WHERE `id` = 3; +--------+ | active | +--------+ | | +--------+ 1 row in set (0.01 sec) mysql> mysql> TRUNCATE onemore; Query OK, 0 rows affected (0.05 sec) mysql> mysql> INSERT INTO `onemore` values (3,'100'); Query OK, 1 row affected (0.00 sec) mysql> SELECT `active` + 1 FROM `banners` WHERE id = 3; +--------------+ | `active` + 1 | +--------------+ | 3 | +--------------+ 1 row in set (0.00 sec) mysql> SELECT `active` FROM onemore WHERE `id` = 3; +--------+ | active | +--------+ | 100 | +--------+ 1 row in set (0.00 sec) mysql> ALTER TABLE `test`.`onemore` change `active` `active` bigint; Query OK, 1 row affected (0.19 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> select * from onemore; +----+--------+ | id | active | +----+--------+ | 3 | 2 | +----+--------+ 1 row in set (0.00 sec)
[9 Jul 2006 21:35]
Peter Laursen
Please forget my last post. Things got messed up :-( This should be better: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 14 to server version: 5.0.23-community-max-l Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> USE test; Database changed mysql> DROP TABLE if EXISTS onemore; Query OK, 0 rows affected (0.02 sec) mysql> CREATE TABLE `onemore` ( -> `id` int(11) NOT NULL auto_increment, -> `active` set('10','100') NOT NULL, -> PRIMARY KEY (`id`)) -> ENGINE=MyISAM DEFAULT CHARSET=utf8; Query OK, 0 rows affected (0.09 sec) mysql> INSERT INTO `onemore` values (3,10); Query OK, 1 row affected, 1 warning (0.01 sec) mysql> SELECT `active` + 1 FROM `onemore` WHERE id = 3; +--------------+ | `active` + 1 | +--------------+ | 3 | +--------------+ 1 row in set (0.02 sec) mysql> SELECT `active` FROM onemore WHERE `id` = 3; +--------+ | active | +--------+ | 100 | +--------+ 1 row in set (0.00 sec) mysql> TRUNCATE onemore; Query OK, 0 rows affected (0.04 sec) mysql> INSERT INTO `onemore` values (3,'10'); Query OK, 1 row affected (0.00 sec) mysql> SELECT `active` + 1 FROM `onemore` WHERE id = 3; +--------------+ | `active` + 1 | +--------------+ | 2 | +--------------+ 1 row in set (0.00 sec) mysql> SELECT `active` FROM onemore WHERE `id` = 3; +--------+ | active | +--------+ | 10 | +--------+ 1 row in set (0.00 sec) mysql> TRUNCATE onemore; Query OK, 0 rows affected (0.03 sec) mysql> INSERT INTO `onemore` values (3,100); Query OK, 1 row affected, 1 warning (0.01 sec) mysql> SELECT `active` + 1 FROM `onemore` WHERE id = 3; +--------------+ | `active` + 1 | +--------------+ | 1 | +--------------+ 1 row in set (0.00 sec) mysql> SELECT `active` FROM onemore WHERE `id` = 3; +--------+ | active | +--------+ | | +--------+ 1 row in set (0.00 sec) mysql> TRUNCATE onemore; Query OK, 0 rows affected (0.04 sec) mysql> INSERT INTO `onemore` values (3,'100'); Query OK, 1 row affected (0.01 sec) mysql> SELECT `active` + 1 FROM `onemore` WHERE id = 3; +--------------+ | `active` + 1 | +--------------+ | 3 | +--------------+ 1 row in set (0.00 sec) mysql> SELECT `active` FROM onemore WHERE `id` = 3; +--------+ | active | +--------+ | 100 | +--------+ 1 row in set (0.00 sec) mysql> DROP TABLE if EXISTS onemore; Query OK, 0 rows affected (0.03 sec) mysql> CREATE TABLE `onemore` ( -> `id` int(11) NOT NULL auto_increment, -> `active` enum('10','100') NOT NULL, -> PRIMARY KEY (`id`)) -> ENGINE=MyISAM DEFAULT CHARSET=utf8; Query OK, 0 rows affected (0.07 sec) mysql> INSERT INTO `onemore` values (3,10); Query OK, 1 row affected, 1 warning (0.01 sec) mysql> SELECT `active` + 1 FROM `onemore` WHERE id = 3; +--------------+ | `active` + 1 | +--------------+ | 1 | +--------------+ 1 row in set (0.00 sec) mysql> SELECT `active` FROM onemore WHERE `id` = 3; +--------+ | active | +--------+ | | +--------+ 1 row in set (0.00 sec) mysql> TRUNCATE onemore; Query OK, 0 rows affected (0.03 sec) mysql> INSERT INTO `onemore` values (3,'10'); Query OK, 1 row affected (0.01 sec) mysql> SELECT `active` + 1 FROM `onemore` WHERE id = 3; +--------------+ | `active` + 1 | +--------------+ | 2 | +--------------+ 1 row in set (0.00 sec) mysql> SELECT `active` FROM onemore WHERE `id` = 3; +--------+ | active | +--------+ | 10 | +--------+ 1 row in set (0.01 sec) mysql> TRUNCATE onemore; Query OK, 0 rows affected (0.03 sec) mysql> INSERT INTO `onemore` values (3,100); Query OK, 1 row affected, 1 warning (0.01 sec) mysql> SELECT `active` + 1 FROM `onemore` WHERE id = 3; +--------------+ | `active` + 1 | +--------------+ | 1 | +--------------+ 1 row in set (0.00 sec) mysql> SELECT `active` FROM onemore WHERE `id` = 3; +--------+ | active | +--------+ | | +--------+ 1 row in set (0.00 sec) mysql> TRUNCATE onemore; Query OK, 0 rows affected (0.04 sec) mysql> INSERT INTO `onemore` values (3,'100'); Query OK, 1 row affected (0.01 sec) mysql> SELECT `active` + 1 FROM `onemore` WHERE id = 3; +--------------+ | `active` + 1 | +--------------+ | 3 | +--------------+ 1 row in set (0.00 sec) mysql> SELECT `active` FROM onemore WHERE `id` = 3; +--------+ | active | +--------+ | 100 | +--------+ 1 row in set (0.00 sec) mysql> ALTER TABLE `test`.`onemore` change `active` `active` bigint; Query OK, 1 row affected (0.14 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> SELECT * from onemore; +----+--------+ | id | active | +----+--------+ | 3 | 2 | +----+--------+ 1 row in set (0.00 sec) mysql>
[11 Jul 2006 20:22]
Valeriy Kravchuk
Sorry, but doesn't the manual explain the results you got with SET (http://dev.mysql.com/doc/refman/5.0/en/set.html): "MySQL stores SET values numerically, with the low-order bit of the stored value corresponding to the first set member. If you retrieve a SET value in a numeric context, the value retrieved has bits set corresponding to the set members that make up the column value. For example, you can retrieve numeric values from a SET column like this: mysql> SELECT set_col+0 FROM tbl_name; If a number is stored into a SET column, the bits that are set in the binary representation of the number determine the set members in the column value. For a column specified as SET('a','b','c','d'), the members have the following decimal and binary values: SET Member Decimal Value Binary Value 'a' 1 0001 'b' 2 0010 'c' 4 0100 'd' 8 1000 If you assign a value of 9 to this column, that is 1001 in binary, so the first and fourth SET value members 'a' and 'd' are selected and the resulting value is 'a,d'. " Please, check.
[11 Jul 2006 20:40]
Andrius Jankevicius
Hello, Thank you very much for the explanation! That means that values in SET fields are stored differently than in TEXT or INT fields... sorry that i did not understood earlier. Regards, Andrius J.