[MDEV-20633] Optimizer unexpectedly changes equi-JOIN predicates - Jira

XML

Word

Printable

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?

relates to

MCOL-2178 Run CS with vanilla 10.4

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.