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:
None 
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
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.
[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.