| Bug #59650 | SELECT Does Not Return Result For "equal / Null safe equal" Operator On BIT | ||
|---|---|---|---|
| Submitted: | 21 Jan 2011 9:32 | Modified: | 21 Jan 2011 16:11 |
| Reporter: | Vinay Fisrekar | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S3 (Non-critical) |
| Version: | mysql-trunk | OS: | Any |
| Assigned to: | Assigned Account | CPU Architecture: | Any |
| Tags: | pb2 | ||
[21 Jan 2011 9:32]
Vinay Fisrekar
[21 Jan 2011 10:23]
Vinay Fisrekar
"type_bit_iuds" tests is failing from engine suite. Failure: http://pb2.norway.sun.com/web.py?template=mysql_show_test_failure&search=yes&push_id=19463...
[21 Jan 2011 16:11]
Valeriy Kravchuk
Thank you for the bug report. Verified with current mysql-trunk:
macbook-pro:trunk openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.2-m5-debug Source distribution
Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> DROP TABLE IF EXISTS t5;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> CREATE TABLE t5(c1 BIT(1) NOT NULL PRIMARY KEY, c2 BIT(1)) ENGINE = InnoDB;
Query OK, 0 rows affected (0.06 sec)
mysql> INSERT IGNORE INTO t5 VALUES (95, 46), (31, 438), (61, 152), (78, 123), (88, 411), (122,
-> 118), (0, 177),(75, 42), (108, 67), (79, 349), (59, 188), (69, 206), (49, 345), (118,
-> 380),(111, 368), (94, 468), (56, 379), (77, 133), (29, 399), (9, 363), (23, 36),(116,
-> 390), (119, 368), (87, 351), (123, 411), (24, 398), (34, 202), (28, 499),(30, 83), (5,
-> 178), (60, 343), (4, 245), (104, 280), (106, 446), (127, 403),(44, 307), (68, 454), (57,
-> 135);
Query OK, 2 rows affected, 75 warnings (0.00 sec)
Records: 38 Duplicates: 36 Warnings: 75
mysql> INSERT IGNORE INTO t5 VALUES(96,null);
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> SELECT HEX(c1),HEX(c2) FROM t5;
+---------+---------+
| HEX(c1) | HEX(c2) |
+---------+---------+
| 0 | 1 |
| 1 | 1 |
+---------+---------+
2 rows in set (0.00 sec)
mysql> SELECT hex(c1),hex(c2) FROM t5 WHERE c1 = '1' ORDER BY c1;
Empty set (0.00 sec)
mysql> SELECT hex(c1),hex(c2) FROM t5 WHERE c1 <=> '1' ORDER BY c1;
Empty set (0.00 sec)
mysql> SELECT hex(c1),hex(c2) FROM t5 WHERE c1 >= '1' ORDER BY c1;
+---------+---------+
| hex(c1) | hex(c2) |
+---------+---------+
| 1 | 1 |
+---------+---------+
1 row in set (0.00 sec)
mysql> explain SELECT hex(c1),hex(c2) FROM t5 WHERE c1 >= '1' ORDER BY c1;
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
| 1 | SIMPLE | t5 | index | PRIMARY | PRIMARY | 1 | NULL | 2 | Using where |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
1 row in set (0.00 sec)
mysql> explain SELECT hex(c1),hex(c2) FROM t5 WHERE c1 = '1' ORDER BY c1;
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE noticed after reading const tables |
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
1 row in set (0.00 sec)
[21 Jan 2011 16:12]
Valeriy Kravchuk
mysql-5.5 works as expected:
macbook-pro:5.5 openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.9-debug Source distribution
Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> DROP TABLE IF EXISTS t5;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> CREATE TABLE t5(c1 BIT(1) NOT NULL PRIMARY KEY, c2 BIT(1)) ENGINE = InnoDB;
Query OK, 0 rows affected (0.07 sec)
mysql> INSERT IGNORE INTO t5 VALUES (95, 46), (31, 438), (61, 152), (78, 123), (88, 411), (122,
-> 118), (0, 177),(75, 42), (108, 67), (79, 349), (59, 188), (69, 206), (49, 345), (118,
-> 380),(111, 368), (94, 468), (56, 379), (77, 133), (29, 399), (9, 363), (23, 36),(116,
-> 390), (119, 368), (87, 351), (123, 411), (24, 398), (34, 202), (28, 499),(30, 83), (5,
-> 178), (60, 343), (4, 245), (104, 280), (106, 446), (127, 403),(44, 307), (68, 454), (57,
-> 135);
Query OK, 2 rows affected, 75 warnings (0.00 sec)
Records: 38 Duplicates: 36 Warnings: 75
mysql> INSERT IGNORE INTO t5 VALUES(96,null);
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> SELECT HEX(c1),HEX(c2) FROM t5;
+---------+---------+
| HEX(c1) | HEX(c2) |
+---------+---------+
| 0 | 1 |
| 1 | 1 |
+---------+---------+
2 rows in set (0.03 sec)
mysql> SELECT hex(c1),hex(c2) FROM t5 WHERE c1 = '1' ORDER BY c1;
+---------+---------+
| hex(c1) | hex(c2) |
+---------+---------+
| 1 | 1 |
+---------+---------+
1 row in set (0.01 sec)
mysql> SELECT hex(c1),hex(c2) FROM t5 WHERE c1 <=> '1' ORDER BY c1;
+---------+---------+
| hex(c1) | hex(c2) |
+---------+---------+
| 1 | 1 |
+---------+---------+
1 row in set (0.00 sec)
So, this is a regression bug in mysql-trunk.
[9 Feb 2011 14:21]
Ole John Aske
After having debugged the attached testcase I am tempted to close this as 'not a bug' - or more correctly change 'Category' to 'Test' bug. The failing testcase compares the 'bit(1)' column c1 with the *character* literal '1' (With ASCII value 49). Lets look at what was happening inside the optimizer when executing this query *before* the fix for bug#58628: When create_ref_for_key() builds the REF key for the predicate 'c1 = '1'' it will end up in Field_bit_as_char::store() which will attempt to store the character value '1' in the bit(1) field. As the ASCII value 0x31 for '1' is outside the range for what can be stored in a bit(1) field, Field_bit_as_char::store() will 'return 1;' to indicate an 'out of range' error. Prior to fix for bug#58628, any errors from '::store()' within create_ref_for_key() was ignored - We therefore happily continued using whatever (undefined) keys we had produced... Furthermore, we find (still wo/ fix): SELECT hex(c1),hex(c2) FROM t5 ; +---------+---------+ | hex(c1) | hex(c2) | +---------+---------+ | 0 | 1 | | 1 | 1 | +---------+---------+ 2 rows in set (0.00 sec) We might then expect ' WHERE c1 = '0'' to return the row (0,1), however: SELECT hex(c1),hex(c2) FROM t5 WHERE c1 = '0'; Empty set (0.00 sec) Doing an 'EXPLAIN' on the 'failing' query we find: mysql> explain SELECT hex(c1),hex(c2) FROM t5 WHERE c1 = '1'; +----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+ | 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE noticed after reading const tables | +----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+ 1 row in set (0.00 sec) --> 'Impossible where' even if the query itself returns 1 row. !!! :-o After fix for bug#58628 is applied, the 'range error' from converting '1' to a bit(1) will be catched, and no rows are (correctly) returned. Changing the testcase to use a bit(1) literal specified as b'1' will correctly return a single row as expected - The same is true when using the bit(1) literal b'0'. My suggestion is to change category for this report to 'Tests'. The fix will then be to change the testsuite to use BIT literals instead of character literals. BTW: Lots of trouble would have been avoided if MySQL never had provided the 'usability feature' of doing automatic type conversion between nonrelated datatypes.
[9 Feb 2011 14:24]
Ole John Aske
Changed category pr. previous comment
[9 Feb 2011 14:30]
Roy Lyseng
Test problem, and no regression.
[10 Feb 2011 10:52]
Vinay Fisrekar
If we have to typecast literal to bit then behavior is inconsistent w.r.t to other operators. We expect to use b'1' to get result with operator = and <=>. But we are getting result for operators like >= , <= with out using b'1'. SELECT hex(c1),hex(c2) FROM t5 WHERE c1 = b'1' ORDER BY c1; hex(c1) hex(c2) 1 1 SELECT hex(c1),hex(c2) FROM t5 WHERE c1 <=> b'1' ORDER BY c1; hex(c1) hex(c2) 1 1 SELECT hex(c1),hex(c2) FROM t5 WHERE c1 >= '1' ORDER BY c1; hex(c1) hex(c2) 1 1 SELECT hex(c1),hex(c2) FROM t5 WHERE c1 <= '1' ORDER BY c1; hex(c1) hex(c2) 0 1 1 1
[10 Feb 2011 20:59]
Roy Lyseng
Taking it back. Apparently some basic store function is broken. But why are basic data type tests not in the main test suite?
[6 Feb 2018 20:01]
Sveta Smirnova
Seems to be fixed in 5.7: mysql> SELECT hex(c1),hex(c2) FROM t5 WHERE c1 = '1' ORDER BY c1; +---------+---------+ | hex(c1) | hex(c2) | +---------+---------+ | 1 | 1 | +---------+---------+ 1 row in set (0.01 sec) mysql> SELECT hex(c1),hex(c2) FROM t5 WHERE c1 <=> '1' ORDER BY c1; +---------+---------+ | hex(c1) | hex(c2) | +---------+---------+ | 1 | 1 | +---------+---------+ 1 row in set (0.00 sec)
