Details
-
Type:
Bug
-
Status: Closed
-
Priority:
Major
-
Resolution: Fixed
-
Affects Version/s: None
-
Fix Version/s: None
-
Component/s: None
-
Labels:
Description
The following query:
SELECT *
FROM t1 STRAIGHT_JOIN t2 ON t2.f2 = t1.f1
WHERE t1.f10 OR t1.f1 OR t1.f3 AND t1.f10 AND t1.f1 = t2.f3 ;
returns no rows when executed with jkl = 0 and "Range checked for each record (index map: 0x1)", even though there is 1 row that matches the WHERE expression and which is returned by other query plans and jkl levels.
Test case:
SET SESSION join_cache_level=0
CREATE TABLE t1 ( f1 int NOT NULL , f3 int NOT NULL , f10 int NOT NULL ) ;
INSERT IGNORE INTO t1 VALUES ('4','0','g'),('5','1','v');
CREATE TABLE t2 ( f2 int NOT NULL , f3 int NOT NULL , KEY (f3)) ;
INSERT IGNORE INTO t2 VALUES ('5','7');
SELECT * FROM t1 STRAIGHT_JOIN t2 ON t2.f2 = t1.f1 WHERE t1.f10 OR t1.f1 OR t1.f3 AND t1.f10 AND t1.f1 = t2.f3;
explain:
------------------------------------------------------------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
------------------------------------------------------------------------------------------------------+
| 1 | SIMPLE | t1 | ALL | NULL | NULL | NULL | NULL | 2 | Using where |
| 1 | SIMPLE | t2 | ALL | f3 | NULL | NULL | NULL | 1 | Range checked for each record (index map: 0x1) |
------------------------------------------------------------------------------------------------------+
bzr version-info:
revision-id: <email address hidden>
date: 2011-03-04 18:54:30 +0300
build-date: 2011-03-07 09:19:00 +0200
revno: 2933
branch-nick: maria-5.3
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
Re: Wrong result with OR + Range checked for each record + NOT NULL
This is a duplicate of LP bug #729067.
Launchpad bug id: 730466
Re: Wrong result with OR + Range checked for each record + NOT NULL
Igor, assigning this bug to you because it could be related to
https://bugs.launchpad.net/maria/+bug/729067
which you already work on.
Both this, and BUG#729067 require STRAIGHT_JOIN
to force "range checked for each record".