| 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: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.
Description: I have found a bug. The following is my table: CREATE TABLE `banners` ( `id` int(11) NOT NULL auto_increment, `link` text NOT NULL, `file` mediumblob NOT NULL, `type` text NOT NULL, `w` text NOT NULL, `h` text NOT NULL, `show` text NOT NULL, `max_show` text NOT NULL, `hits` text NOT NULL, `max_hits` text NOT NULL, `expires` datetime NOT NULL, `place` text NOT NULL, `active` set('0','1') NOT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=4 ; Some data in it: INSERT INTO `banners` VALUES (3, 'http://skaitliukas.lt',[BLOB - 11.3 KB], 'gif', '120', '120', '0', '5000', '0', '5000', '2006-06-09 14:26:45', '0', '1'); a query SELECT `active` + 1 FROM `banners` WHERE id = 3 returns 1 when `active` is 1. Or SELECT `active` - 2 FROM `banners` WHERE id=3 returns 0 when `active` = '1'.. so what i think here is that somehow a number stored as SET is 1 unit more than for example integer. so SET('1') = INT(2) or something like this. How to repeat: do the queries from the description field.