Details
-
Bug
-
Status: Closed (View Workflow)
-
Minor
-
Resolution: Not a Bug
-
10.4.8
-
None
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?
Attachments
Issue Links
- relates to
-
MCOL-2178 Run CS with vanilla 10.4
- Closed