| Bug #33833 | No rows are returned, even though query matches | ||
|---|---|---|---|
| Submitted: | 12 Jan 2008 15:31 | Modified: | 2 Apr 2008 17:22 |
| Reporter: | River Tarnell | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S2 (Serious) |
| Version: | 5.0-bk, 5.1-bk | OS: | Any |
| Assigned to: | Igor Babaev | CPU Architecture: | Any |
[12 Jan 2008 15:32]
River Tarnell
Some further information: removing the first part of the 'or' query returns the correct result: river@sql-s1:enwiki_p> select page_id from page where (page_title='Al_gore' and page_namespace=0 and page_is_redirect=0) or (page_title='Al_Gore' and page_namespace=0 and page_is_redirect=0)\G Empty set (0.00 sec) river@sql-s1:enwiki_p> select page_id from page where page_title='Al_Gore' and page_namespace=0 and page_is_redirect=0\G *************************** 1. row *************************** page_id: 5042706 1 row in set (0.00 sec)
[12 Jan 2008 15:36]
River Tarnell
This is the base table 'enwiki.page':
Table: page
Create Table: CREATE TABLE `page` (
`page_id` int(8) unsigned NOT NULL auto_increment,
`page_namespace` int(11) NOT NULL default '0',
`page_title` varchar(255) character set latin1 collate latin1_bin NOT NULL default '',
`page_restrictions` tinyblob NOT NULL,
`page_counter` bigint(20) unsigned NOT NULL default '0',
`page_is_redirect` tinyint(1) unsigned NOT NULL default '0',
`page_is_new` tinyint(1) unsigned NOT NULL default '0',
`page_random` double unsigned NOT NULL default '0',
`page_touched` varchar(14) character set latin1 collate latin1_bin NOT NULL default '',
`page_latest` int(8) unsigned NOT NULL default '0',
`page_len` int(8) unsigned NOT NULL default '0',
PRIMARY KEY (`page_id`),
UNIQUE KEY `name_title` (`page_namespace`,`page_title`),
KEY `page_random` (`page_random`),
KEY `page_len` (`page_len`)
) ENGINE=InnoDB AUTO_INCREMENT=15177384 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
This is the view 'enwiki_p.page':
root@localhost:enwiki> show create view enwiki_p.page\G
*************************** 1. row ***************************
View: page
Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `enwiki_p`.`page` AS select `enwiki`.`page`.`page_id` AS `page_id`,`enwiki`.`page`.`page_namespace` AS `page_namespace`,`enwiki`.`page`.`page_title` AS `page_title`,`enwiki`.`page`.`page_restrictions` AS `page_restrictions`,`enwiki`.`page`.`page_counter` AS `page_counter`,`enwiki`.`page`.`page_is_redirect` AS `page_is_redirect`,`enwiki`.`page`.`page_is_new` AS `page_is_new`,`enwiki`.`page`.`page_random` AS `page_random`,`enwiki`.`page`.`page_touched` AS `page_touched`,`enwiki`.`page`.`page_latest` AS `page_latest`,`enwiki`.`page`.`page_len` AS `page_len` from `enwiki`.`page`
1 row in set (0.00 sec)
EXPLAIN data from the broken query:
river@sql-s1:enwiki_p> explain select page_id from page where (page_title='Al_gore' and page_namespace=0 and page_is_redirect=0) or (page_title='Al_Gore' and page_namespace=0 and page_is_redirect=0)\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: NULL
type: NULL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: NULL
Extra: Impossible WHERE noticed after reading const tables
EXPLAIN data from the working query (without --default-character-set):
river@sql-s1:enwiki_p> explain select page_id from page where page_title='Al_Gore' and page_namespace=0 and page_is_redirect=0\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: page
type: const
possible_keys: name_title
key: name_title
key_len: 261
ref: const,const
rows: 1
Extra:
1 row in set (0.00 sec)
Explain from the original query using --default-character-set:
river@yarrow:enwiki_p> explain select page_id from page where (page_title='Al_gore' and page_namespace=0 and page_is_redirect=0) or (page_title='Al_Gore' and page_namespace=0 and page_is_redirect=0)\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: page
type: range
possible_keys: name_title
key: name_title
key_len: 261
ref: NULL
rows: 2
Extra: Using where
1 row in set (0.00 sec)
[14 Jan 2008 12:50]
Susanne Ebrecht
Many thanks for writing a bug report. We don't support packages from distributors. Please install the packages/source from our download page and let us know, if the error still occurs. Also we need a little test from you for reproducing this.
[14 Jan 2008 13:01]
River Tarnell
Only the Debian system is using a distribution package. I installed the binaries from mysql.com for Windows, and compiled from source for Solaris. (I cannot install 5.0.51 binaries for Solaris because there are none on the distribution page.) Here is a simple test to recreate the problem (5.0.51 client and server on Solaris): root@localhost:u_river> create table 33833_test (page_id int(8) unsigned not null auto_increment, page_namespace int(11) not null default '0', page_title varchar(255) character set latin1 collate latin1_bin not null default '', primary key(page_id), unique key name_title (page_namespace, page_title)) engine=innodb; Query OK, 0 rows affected (0.02 sec) root@localhost:u_river> insert into 33833_test (page_namespace, page_title) values (0, 'Al_Gore'); Query OK, 1 row affected (0.01 sec) root@localhost:u_river> select page_id from 33833_test where (page_title='Al_gore' and page_namespace=0) or (page_title='Al_Gore' and page_namespace=0); Empty set (0.00 sec) root@localhost:u_river> select page_id from 33833_test where page_title='Al_Gore' and page_namespace=0; +---------+ | page_id | +---------+ | 1 | +---------+ 1 row in set (0.00 sec)
[14 Jan 2008 17:11]
Domas Mituzas
Verified on 5.0-bk and 5.1-bk with:
DROP TABLE IF EXISTS t6;
CREATE TABLE t6 ( a int , b varchar(255), key ab (a,b))
ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_bin;
INSERT INTO t6 VALUES (0,'A');
select a from t6 where (b='a' and a=0) or (b='A' and a=0);
5.0:
mysql> select a from t6 where (b='a' and a=0) or (b='A' and a=0);
Empty set (0.00 sec)
mysql> explain select a from t6 where (b='a' and a=0) or (b='A' and a=0);
+----+-------------+-------+------+---------------+------+---------+-------------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+-------------+------+--------------------------+
| 1 | SIMPLE | t6 | ref | ab | ab | 263 | const,const | 2 | Using where; Using index |
+----+-------------+-------+------+---------------+------+---------+-------------+------+--------------------------+
1 row in set (0.02 sec)
6.0:
mysql> select a from t6 where (b='a' and a=0) or (b='A' and a=0);
Empty set (0.00 sec)
mysql> explain select a from t6 where (b='a' and a=0) or (b='A' and a=0);
+----+-------------+-------+------+---------------+------+---------+-------------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+-------------+------+--------------------------+
| 1 | SIMPLE | t6 | ref | ab | ab | 263 | const,const | 2 | Using where; Using index |
+----+-------------+-------+------+---------------+------+---------+-------------+------+--------------------------+
1 row in set (0.01 sec)
This seems to require latin1_bin and InnoDB - but is poor optimizer execution, so changing category to Optimizer - though it might be InnoDB statistics problem.
[14 Jan 2008 17:12]
Domas Mituzas
^^ above not 6.0, but 5.1
[27 Jan 2008 2:58]
Igor Babaev
This problem can be demonstrated with one MyISAM table as well:
mysql> SELECT VERSION();
+--------------+
| VERSION() |
+--------------+
| 5.0.54-debug |
+--------------+
1 row in set (0.00 sec)
mysql> CREATE TABLE t1 (
-> a varchar(32), index (a)
-> ) DEFAULT CHARSET=latin1 COLLATE=latin1_bin;
Query OK, 0 rows affected (0.49 sec)
mysql> INSERT INTO t1 VALUES
-> ('B'), ('A'), ('A'), ('C'), ('B'), ('A'), ('A');
Query OK, 7 rows affected (0.00 sec)
Records: 7 Duplicates: 0 Warnings: 0
mysql> SELECT a FROM t1 WHERE a='b' OR a='B';
Empty set (0.00 sec)
mysql> EXPLAIN SELECT a FROM t1 WHERE a='b' OR a='B';
+----+-------------+-------+------+---------------+------+---------+-------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+-------+------+--------------------------+
| 1 | SIMPLE | t1 | ref | a | a | 35 | const | 3 | Using where; Using index |
+----+-------------+-------+------+---------------+------+---------+-------+------+--------------------------+
1 row in set (0.00 sec)
The same behaviour can be observed for 4.1:
mysql> SELECT VERSION();
+--------------+
| VERSION() |
+--------------+
| 4.1.24-debug |
+--------------+
1 row in set (0.00 sec)
mysql> CREATE TABLE t1 (
-> a varchar(32), index (a)
-> ) DEFAULT CHARSET=latin1 COLLATE=latin1_bin;
Query OK, 0 rows affected (0.45 sec)
mysql> INSERT INTO t1 VALUES
-> ('B'), ('A'), ('A'), ('C'), ('B'), ('A'), ('A');
Query OK, 7 rows affected (0.00 sec)
Records: 7 Duplicates: 0 Warnings: 0
mysql> SELECT a FROM t1 WHERE a='b' OR a='B';
Empty set (0.06 sec)
mysql> EXPLAIN SELECT a FROM t1 WHERE a='b' OR a='B';
+----+-------------+-------+------+---------------+------+---------+-------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+-------+------+--------------------------+
| 1 | SIMPLE | t1 | ref | a | a | 33 | const | 3 | Using where; Using index |
+----+-------------+-------+------+---------------+------+---------+-------+------+--------------------------+
1 row in set (0.00 sec)
[27 Jan 2008 5:41]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/41286 ChangeSet@1.2593, 2008-01-26 21:45:35-08:00, igor@olga.mysql.com +5 -0 Fixed bug #33833. Two disjuncts containing equalities of the form key=const1 and key=const2 can be merged into one if const1 is equal to const2. To check it the common collation of the constants were used rather than the collation of the field key. For example when the default collation of the constants was cases insensitive while the collation of the field was case sensitive, then two or-ed equality predicates key='b' and key='B' incorrectly were merged into one f='b'. As a result ref access was used instead of range access and wrong result sets were returned in many cases. Fixed the problem by comparing constant in the or-ed predicate with collation of the key field.
[13 Mar 2008 19:27]
Bugs System
Pushed into 6.0.5-alpha
[13 Mar 2008 19:35]
Bugs System
Pushed into 5.1.24-rc
[13 Mar 2008 19:42]
Bugs System
Pushed into 5.0.60
[2 Apr 2008 17:22]
Jon Stephens
Documented bugfix in the 5.0.60, 5.1.23-ndb-6.3.11, 5.1.24, and 6.0.5 changelogs as follows:
A query using WHERE (column1='string1' AND
column2=constant1) OR (column1='string2' AND
column2=constant2), where col1 used a binary
collation and string1 matched
string2 except for case, failed to match any
records even when matches were found by a query using the equivalent
clause WHERE column2=constant1 OR
column2=constant2.
Description: I have tested this bug with MySQL 5.0.45 client on Debian Linux and Microsoft Windows XP SP2. The behaviour is identical. The server (5.0.51) is running Sun Solaris 10 U4. The following row exists in the table enwiki_p.page: river@sql-s1:enwiki_p> select page_namespace, page_title, page_is_redirect from page where page_id=5042706\G *************************** 1. row *************************** page_namespace: 0 page_title: Al_Gore page_is_redirect: 0 1 row in set (0.00 sec) However, the following query cannot find it: river@sql-s1:enwiki_p> \s -------------- mysql Ver 14.12 Distrib 5.0.45, for pc-linux-gnu (x86_64) using readline 5.2 Connection id: 299048 Current database: enwiki_p Current user: river@hemlock.ts.wikimedia.org SSL: Not in use Current pager: stdout Using outfile: '' Using delimiter: ; Server version: 5.0.51 Source distribution Protocol version: 10 Connection: sql-s1 via TCP/IP Server characterset: latin1 Db characterset: latin1 Client characterset: latin1 Conn. characterset: latin1 TCP port: 3306 Uptime: 23 hours 7 min 34 sec Threads: 60 Questions: 44943828 Slow queries: 9866 Opens: 4104204 Flush tables: 1 Open tables: 2048 Queries per second avg: 539.840 -------------- river@sql-s1:enwiki_p> select page_id from page where (page_title='Al_gore' and page_namespace=0 and page_is_redirect=0) or (page_title='Al_Gore' and page_namespace=0 and page_is_redirect=0); Empty set (0.00 sec) If the client is started with --default-character-set=utf8, the query does return the row: river@yarrow:enwiki_p> \s -------------- mysql Ver 14.12 Distrib 5.0.45, for pc-linux-gnu (x86_64) using readline 5.2 Connection id: 298847 Current database: enwiki_p Current user: river@hemlock.ts.wikimedia.org SSL: Not in use Current pager: stdout Using outfile: '' Using delimiter: ; Server version: 5.0.51 Source distribution Protocol version: 10 Connection: yarrow via TCP/IP Server characterset: latin1 Db characterset: latin1 Client characterset: utf8 Conn. characterset: utf8 TCP port: 3306 Uptime: 23 hours 6 min 46 sec Threads: 51 Questions: 44917812 Slow queries: 9862 Opens: 4093766 Flush tables: 1 Open tables: 2048 Queries per second avg: 539.839 -------------- river@yarrow:enwiki_p> select page_id from page where (page_title='Al_gore' and page_namespace=0 and page_is_redirect=0) or (page_title='Al_Gore' and page_namespace=0 and page_is_redirect=0)\G *************************** 1. row *************************** page_id: 5042706 1 row in set (0.01 sec) How to repeat: Explained in description. Suggested fix: Unknown