[MDEV-5160] Wrong result (missing row) with outer_join_with_cache=on, join_cache_level > 0, RIGHT JOIN, HAVING, LIMIT Created: 2013-10-19  Updated: 2013-11-15  Resolved: 2013-11-15

Status: Closed
Project: MariaDB Server
Component/s: None
Affects Version/s: 10.0.4
Fix Version/s: 10.0.6

Type: Bug Priority: Major
Reporter: Elena Stepanova Assignee: Igor Babaev
Resolution: Fixed Votes: 0
Labels: None

Issue Links:
Relates

 Description    

SET optimizer_switch = 'outer_join_with_cache=on';
 
 
 
CREATE TABLE t1 (c1 VARCHAR(6)) ENGINE=MyISAM;
 
INSERT INTO t1 VALUES ('s'),('t');
 
 
 
CREATE TABLE t2 (c2 VARCHAR(1)) ENGINE=MyISAM;
 
INSERT INTO t2 VALUES ('a'),('x');
 
 
 
SET join_cache_level = 1;
 
SELECT t2.* FROM t1 RIGHT JOIN t2 ON c1 = c2 HAVING c2 > 'a' ORDER BY c2 LIMIT 1;
 
 
 
SET join_cache_level = 0;
 
SELECT t2.* FROM t1 RIGHT JOIN t2 ON c1 = c2 HAVING c2 > 'a' ORDER BY c2 LIMIT 1;

Result:

SET join_cache_level = 1;
 
SELECT t2.* FROM t1 RIGHT JOIN t2 ON c1 = c2 HAVING c2 > 'a' ORDER BY c2 LIMIT 1;
 
c2
 
SET join_cache_level = 0;
 
SELECT t2.* FROM t1 RIGHT JOIN t2 ON c1 = c2 HAVING c2 > 'a' ORDER BY c2 LIMIT 1;
 
c2
 
x

The 2nd result is the correct one.

The failure happens on current 10.0-base (revno 3733) and 10.0 (revno 3856). I found the revision on 10.0-base when it started happening, it was a merge from 5.5:

revno: 3645 [merge]
 
revision-id: sergii@pisem.net-20130606155128-5mytep9v42626tfs
 
committer: Sergei Golubchik <sergii@pisem.net>
 
branch nick: 10.0-base
 
timestamp: Thu 2013-06-06 17:51:28 +0200
 
message:
 
  5.5 merge

But on some reason I could not reproduce it on 5.5, even after rolling back to the revision which was merged into 10.0-base.



 Comments   
Comment by Igor Babaev [ 2013-10-22 ]

The bug is reproducible with a regular join as well:

MariaDB [test]> INSERT INTO t1 values ('a'), ('x');
Query OK, 2 rows affected (0.00 sec)
MariaDB [test]> SELECT * FROM t1 JOIN t2 ON c1 = c2 HAVING c2 > 'a' ORDER BY c2 LIMIT 1;
Empty set (0.00 sec)
MariaDB [test]> SET join_cache_level = 0;
Query OK, 0 rows affected (0.00 sec)
MariaDB [test]> SELECT * FROM t1 JOIN t2 ON c1 = c2 HAVING c2 > 'a' ORDER BY c2 LIMIT 1;
----------+

c1 c2

----------+

x x

----------+
1 row in set (0.00 sec)

Comment by Igor Babaev [ 2013-10-22 ]

The bug happens because sort_table_cond is extracted incorrectly due to the fact that curr_join->tmp_having needs an update of used tables.
The bug can be fixed with the following patch:

=== modified file 'sql/sql_select.cc'
— sql/sql_select.cc 2013-10-17 05:45:31 +0000
+++ sql/sql_select.cc 2013-10-21 22:23:16 +0000
@@ -2903,6 +2903,7 @@ void JOIN::exec_inner()
JOIN_TAB *curr_table= &curr_join->join_tab[curr_join->const_tables];
table_map used_tables= (curr_join->const_table_map |
curr_table->table->map);
+ curr_join->tmp_having->update_used_tables();

Item* sort_table_cond= make_cond_for_table(thd, curr_join->tmp_having,
used_tables,

It looks like the bug can manifest itself only starting with10.0-base.

Comment by Igor Babaev [ 2013-11-15 ]

The fix for this bug was pushed into 10.0-base

Generated at Thu Feb 08 07:02:08 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.