[MDEV-20633] Optimizer unexpectedly changes equi-JOIN predicates Created: 2019-09-19  Updated: 2019-09-19  Resolved: 2019-09-19

Status: Closed
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 10.4.8
Fix Version/s: 10.4.8

Type: Bug Priority: Minor
Reporter: Roman Assignee: Igor Babaev
Resolution: Not a Bug Votes: 0
Labels: None

Issue Links:
Relates
relates to MCOL-2178 Run CS with vanilla 10.4 Closed

 Description   

Consider the following example and how optimizer rewrites equi-join predicates.

DROP TABLE IF EXISTS t1;
 
DROP TABLE IF EXISTS t2;
 
DROP TABLE IF EXISTS t3;
 
create table t1 (c1 bigint, c2 bigint);
 
create table t2 (c1 bigint, c2 bigint);
 
create table t3 (c1 bigint, c2 bigint);
 
select * from t1,t2,t3 where t1.c1=t2.c1 AND t3.c1=t2.c1 AND t2.c2=t3.c2;

Here is the dbug_print_item(conds) for the COND item in optimize_inner() before optimize_cond(), right after it and after optimize_stage2().

before optimize_conds()
 
dbug_print_item(conds) = 0x5600736ee9a0 <dbug_item_print_buf> "t1.c1 = t2.c1 and t3.c1 = t2.c1 and t2.c2 = t3.c2"
 
 
 
after optimize_conds()
 
dbug_print_item(conds) = 0x5600736ee9a0 <dbug_item_print_buf> "multiple equal(t1.c1, t2.c1, t3.c1) and multiple equal(t2.c2, t3.c2)"
 
 
 
after optimize_stage2()
 
dbug_print_item(conds) = 0x5600736ee9a0 <dbug_item_print_buf> "t2.c1 = t1.c1 and t3.c1 = t1.c1 and t3.c2 = t2.c2"

However if one swaps t1 and t2 in FROM we got expected join predicates:

before optimize_conds()
 
1: dbug_print_item(conds) = 0x5600736ee9a0 <dbug_item_print_buf> "t1.c1 = t2.c1 and t3.c1 = t2.c1 and t2.c2 = t3.c2"
 
 
 
after optimize_conds()
 
$2 = 0x5582958f59a0 <dbug_item_print_buf> "multiple equal(t1.c1, t2.c1, t3.c1) and multiple equal(t2.c2, t3.c2)"
 
 
 
after optimize_stage2()
 
1: dbug_print_item(conds) = 0x5600736ee9a0 <dbug_item_print_buf> "t1.c1 = t2.c1 and t3.c1 = t2.c1 and t2.c2 = t3.c2"

This unstable behavior breaks a number of regression tests in CS. How to avoid such replacements?

 Comments   
Comment by Roman [ 2019-09-19 ]

This is an expected behavior of JOIN order pick mechanism.

Generated at Thu Feb 08 09:00:59 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.