Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

order of tables in FROM-clause has negative impact on generated plan (using crossproducts instead of joins) #3819

Closed
monetdb-team opened this issue Nov 30, 2020 · 0 comments
Closed

order of tables in FROM-clause has negative impact on generated plan (using crossproducts instead of joins) #3819

monetdb-team opened this issue Nov 30, 2020 · 0 comments
Labels
bug Something isn't working major SQL
Milestone
Ancient Release

Comments

@monetdb-team
Copy link

Date: 2015-10-08 16:35:57 +0200
From: Martin van Dinther <<martin.van.dinther>>
To: SQL devs <>
Version: 11.21.5 (Jul2015)
CC: @njnes

Last updated: 2015-11-03 10:18:04 +0100

Comment 21322

Date: 2015-10-08 16:35:57 +0200
From: Martin van Dinther <<martin.van.dinther>>

User-Agent: Mozilla/5.0 (X11; Fedora; Linux x86_64; rv:38.0) Gecko/20100101 Firefox/38.0
Build Identifier:

When changing the order of the table names in the FROM-clause, the generated plan introduces crossproduct(s) instead of (inner)join(s).
The plan generator (or optimiser) should generate the most optimal plan, so with the least number of crossproduct operations as possible.

This is an extension to the issue found in bug #3809.
I have added 5 plan queries to sql/test/BugTracker-2015/Tests/large_join.Bug-3809.sql

Reproducible: Always

Steps to Reproduce:

  1. cd sql/test/BugTracker-2015/Tests/
  2. Mtest.py large_join.Bug-3809
  3. Inspect the results of large_join.Bug-3809.stable.out
    It shows that 3 of the 5 plan queries produce crossproducts instead of only all joins.

Actual Results:

plan
SELECT x20,x47,x38,x18,x10,x22,x37,x3,x63,x8,x30,x43,x54,x9,x21,x25,x2,x61,x55,x32,x52,x29,x50,x12
FROM t2,t9,t20,t63,t54,t32,t22,t52,t25,t47,t29,t12,t18,t10,t21,t43,t37,t61,t55,t3,t38,t50,t30,t8
WHERE b3=a18
AND a25=b38
AND a54=b8
AND a61=b47
AND a43=b29
AND b18=a12
AND b32=a30
AND a22=b43
AND a29=b21
AND a10=b25
AND a12=4
AND b22=a32
AND a20=b55
AND b30=a9
AND a2=b61
AND a38=b52
AND a55=b9
AND a21=b50
AND a37=b54
AND b10=a3
AND a52=b2
AND a50=b63
% .plan table_name
% rel name
% clob type
% 375 length
project (
| join (
| | join (
| | | crossproduct (
| | | | crossproduct (
| | | | | crossproduct (
| | | | | | crossproduct (
| | | | | | | crossproduct (
| | | | | | | | crossproduct (
| | | | | | | | | crossproduct (
| | | | | | | | | | crossproduct (
| | | | | | | | | | | crossproduct (
| | | | | | | | | | | | crossproduct (
| | | | | | | | | | | | | join (
| | | | | | | | | | | | | | join (
| | | | | | | | | | | | | | | join (
| | | | | | | | | | | | | | | | join (
| | | | | | | | | | | | | | | | | join (
| | | | | | | | | | | | | | | | | | join (
| | | | | | | | | | | | | | | | | | | join (
| | | | | | | | | | | | | | | | | | | | join (
| | | | | | | | | | | | | | | | | | | | | join (
| | | | | | | | | | | | | | | | | | | | | | join (
| | | | | | | | | | | | | | | | | | | | | | | join (
| | | | | | | | | | | | | | | | | | | | | | | | table(sys.t18) [ t18.a18 NOT NULL HASHCOL , t18.b18, t18.x18 ] COUNT ,
| | | | | | | | | | | | | | | | | | | | | | | | select (
| | | | | | | | | | | | | | | | | | | | | | | | | table(sys.t12) [ t12.a12 NOT NULL HASHCOL , t12.x12 ] COUNT
| | | | | | | | | | | | | | | | | | | | | | | | ) [ t12.a12 NOT NULL HASHCOL = int "4" ]
| | | | | | | | | | | | | | | | | | | | | | | ) [ t18.b18 = t12.a12 NOT NULL HASHCOL ],
| | | | | | | | | | | | | | | | | | | | | | | table(sys.t3) [ t3.a3 NOT NULL HASHCOL , t3.b3, t3.x3 ] COUNT
| | | | | | | | | | | | | | | | | | | | | | ) [ t3.b3 = t18.a18 NOT NULL HASHCOL ],
| | | | | | | | | | | | | | | | | | | | | | table(sys.t10) [ t10.a10 NOT NULL HASHCOL , t10.b10, t10.x10 ] COUNT
| | | | | | | | | | | | | | | | | | | | | ) [ t10.b10 = t3.a3 NOT NULL HASHCOL ],
| | | | | | | | | | | | | | | | | | | | | table(sys.t25) [ t25.a25 NOT NULL HASHCOL , t25.b25, t25.x25 ] COUNT
| | | | | | | | | | | | | | | | | | | | ) [ t10.a10 NOT NULL HASHCOL = t25.b25 ],
| | | | | | | | | | | | | | | | | | | | table(sys.t38) [ t38.a38 NOT NULL HASHCOL , t38.b38, t38.x38 ] COUNT
| | | | | | | | | | | | | | | | | | | ) [ t25.a25 NOT NULL HASHCOL = t38.b38 ],
| | | | | | | | | | | | | | | | | | | table(sys.t52) [ t52.a52 NOT NULL HASHCOL , t52.b52, t52.x52 ] COUNT
| | | | | | | | | | | | | | | | | | ) [ t38.a38 NOT NULL HASHCOL = t52.b52 ],
| | | | | | | | | | | | | | | | | | table(sys.t2) [ t2.a2 NOT NULL HASHCOL , t2.b2, t2.x2 ] COUNT
| | | | | | | | | | | | | | | | | ) [ t52.a52 NOT NULL HASHCOL = t2.b2 ],
| | | | | | | | | | | | | | | | | table(sys.t61) [ t61.a61 NOT NULL HASHCOL , t61.b61, t61.x61 ] COUNT
| | | | | | | | | | | | | | | | ) [ t2.a2 NOT NULL HASHCOL = t61.b61 ],
| | | | | | | | | | | | | | | | table(sys.t47) [ t47.a47 NOT NULL HASHCOL , t47.b47, t47.x47 ] COUNT
| | | | | | | | | | | | | | | ) [ t61.a61 NOT NULL HASHCOL = t47.b47 ],
| | | | | | | | | | | | | | | table(sys.t37) [ t37.a37 NOT NULL HASHCOL , t37.b37, t37.x37 ] COUNT
| | | | | | | | | | | | | | ) [ t47.a47 NOT NULL HASHCOL = t37.b37 ],
| | | | | | | | | | | | | | table(sys.t54) [ t54.a54 NOT NULL HASHCOL , t54.b54, t54.x54 ] COUNT
| | | | | | | | | | | | | ) [ t37.a37 NOT NULL HASHCOL = t54.b54 ],
| | | | | | | | | | | | | table(sys.t50) [ t50.a50 NOT NULL HASHCOL , t50.b50, t50.x50 ] COUNT
| | | | | | | | | | | | ) [ ],
| | | | | | | | | | | | table(sys.t30) [ t30.a30 NOT NULL HASHCOL , t30.b30, t30.x30 ] COUNT
| | | | | | | | | | | ) [ ],
| | | | | | | | | | | table(sys.t55) [ t55.a55 NOT NULL HASHCOL , t55.b55, t55.x55 ] COUNT
| | | | | | | | | | ) [ ],
| | | | | | | | | | table(sys.t21) [ t21.a21 NOT NULL HASHCOL , t21.b21, t21.x21 ] COUNT
| | | | | | | | | ) [ ],
| | | | | | | | | table(sys.t32) [ t32.a32 NOT NULL HASHCOL , t32.b32, t32.x32 ] COUNT
| | | | | | | | ) [ ],
| | | | | | | | table(sys.t63) [ t63.a63 NOT NULL HASHCOL , t63.b63, t63.x63 ] COUNT
| | | | | | | ) [ ],
| | | | | | | table(sys.t9) [ t9.a9 NOT NULL HASHCOL , t9.b9, t9.x9 ] COUNT
| | | | | | ) [ ],
| | | | | | table(sys.t20) [ t20.a20 NOT NULL HASHCOL , t20.b20, t20.x20 ] COUNT
| | | | | ) [ ],
| | | | | table(sys.t29) [ t29.a29 NOT NULL HASHCOL , t29.b29, t29.x29 ] COUNT
| | | | ) [ ],
| | | | table(sys.t22) [ t22.a22 NOT NULL HASHCOL , t22.b22, t22.x22 ] COUNT
| | | ) [ ],
| | | table(sys.t43) [ t43.a43 NOT NULL HASHCOL , t43.b43, t43.x43 ] COUNT
| | ) [ t32.b32 = t30.a30 NOT NULL HASHCOL , t30.b30 = t9.a9 NOT NULL HASHCOL , t21.a21 NOT NULL HASHCOL = t50.b50, t50.a50 NOT NULL HASHCOL = t63.b63, t20.a20 NOT NULL HASHCOL = t55.b55, t55.a55 NOT NULL HASHCOL = t9.b9, t43.a43 NOT NULL HASHCOL = t29.b29, t22.a22 NOT NULL HASHCOL = t43.b43, t29.a29 NOT NULL HASHCOL = t21.b21, t22.b22 = t32.a32 NOT NULL HASHCOL ],
| | table(sys.t8) [ t8.a8 NOT NULL HASHCOL , t8.b8, t8.x8 ] COUNT
| ) [ t54.a54 NOT NULL HASHCOL = t8.b8, t8.a8 NOT NULL HASHCOL = t20.b20 ]
) [ t20.x20, t47.x47, t38.x38, t18.x18, t10.x10, t22.x22, t37.x37, t3.x3, t63.x63, t8.x8, t30.x30, t43.x43, t54.x54, t9.x9, t21.x21, t25.x25, t2.x2, t61.x61, t55.x55, t32.x32, t52.x52, t29.x29, t50.x50, t12.x12 ]
plan
SELECT x20,x47,x38,x18,x10,x22,x37,x3,x63,x8,x30,x43,x54,x9,x21,x25,x2,x61,x55,x32,x52,x29,x50,x12
FROM t61,t9,t20,t63,t54,t32,t22,t52,t25,t47,t29,t12,t18,t10,t21,t43,t37,t2,t55,t3,t38,t50,t30,t8
WHERE b3=a18
AND a25=b38
AND a54=b8
AND a61=b47
AND a43=b29
AND b18=a12
AND b32=a30
AND a22=b43
AND a29=b21
AND a10=b25
AND a12=4
AND b22=a32
AND a20=b55
AND b30=a9
AND a2=b61
AND a38=b52
AND a55=b9
AND a21=b50
AND a37=b54
AND b10=a3
AND a52=b2
AND a50=b63
% .plan table_name
% rel name
% clob type
% 375 length
project (
| join (
| | join (
| | | crossproduct (
| | | | crossproduct (
| | | | | crossproduct (
| | | | | | crossproduct (
| | | | | | | crossproduct (
| | | | | | | | crossproduct (
| | | | | | | | | crossproduct (
| | | | | | | | | | crossproduct (
| | | | | | | | | | | crossproduct (
| | | | | | | | | | | | crossproduct (
| | | | | | | | | | | | | join (
| | | | | | | | | | | | | | join (
| | | | | | | | | | | | | | | join (
| | | | | | | | | | | | | | | | join (
| | | | | | | | | | | | | | | | | join (
| | | | | | | | | | | | | | | | | | join (
| | | | | | | | | | | | | | | | | | | join (
| | | | | | | | | | | | | | | | | | | | join (
| | | | | | | | | | | | | | | | | | | | | join (
| | | | | | | | | | | | | | | | | | | | | | join (
| | | | | | | | | | | | | | | | | | | | | | | join (
| | | | | | | | | | | | | | | | | | | | | | | | table(sys.t18) [ t18.a18 NOT NULL HASHCOL , t18.b18, t18.x18 ] COUNT ,
| | | | | | | | | | | | | | | | | | | | | | | | select (
| | | | | | | | | | | | | | | | | | | | | | | | | table(sys.t12) [ t12.a12 NOT NULL HASHCOL , t12.x12 ] COUNT
| | | | | | | | | | | | | | | | | | | | | | | | ) [ t12.a12 NOT NULL HASHCOL = int "4" ]
| | | | | | | | | | | | | | | | | | | | | | | ) [ t18.b18 = t12.a12 NOT NULL HASHCOL ],
| | | | | | | | | | | | | | | | | | | | | | | table(sys.t3) [ t3.a3 NOT NULL HASHCOL , t3.b3, t3.x3 ] COUNT
| | | | | | | | | | | | | | | | | | | | | | ) [ t3.b3 = t18.a18 NOT NULL HASHCOL ],
| | | | | | | | | | | | | | | | | | | | | | table(sys.t10) [ t10.a10 NOT NULL HASHCOL , t10.b10, t10.x10 ] COUNT
| | | | | | | | | | | | | | | | | | | | | ) [ t10.b10 = t3.a3 NOT NULL HASHCOL ],
| | | | | | | | | | | | | | | | | | | | | table(sys.t25) [ t25.a25 NOT NULL HASHCOL , t25.b25, t25.x25 ] COUNT
| | | | | | | | | | | | | | | | | | | | ) [ t10.a10 NOT NULL HASHCOL = t25.b25 ],
| | | | | | | | | | | | | | | | | | | | table(sys.t38) [ t38.a38 NOT NULL HASHCOL , t38.b38, t38.x38 ] COUNT
| | | | | | | | | | | | | | | | | | | ) [ t25.a25 NOT NULL HASHCOL = t38.b38 ],
| | | | | | | | | | | | | | | | | | | table(sys.t52) [ t52.a52 NOT NULL HASHCOL , t52.b52, t52.x52 ] COUNT
| | | | | | | | | | | | | | | | | | ) [ t38.a38 NOT NULL HASHCOL = t52.b52 ],
| | | | | | | | | | | | | | | | | | table(sys.t2) [ t2.a2 NOT NULL HASHCOL , t2.b2, t2.x2 ] COUNT
| | | | | | | | | | | | | | | | | ) [ t52.a52 NOT NULL HASHCOL = t2.b2 ],
| | | | | | | | | | | | | | | | | table(sys.t61) [ t61.a61 NOT NULL HASHCOL , t61.b61, t61.x61 ] COUNT
| | | | | | | | | | | | | | | | ) [ t2.a2 NOT NULL HASHCOL = t61.b61 ],
| | | | | | | | | | | | | | | | table(sys.t47) [ t47.a47 NOT NULL HASHCOL , t47.b47, t47.x47 ] COUNT
| | | | | | | | | | | | | | | ) [ t61.a61 NOT NULL HASHCOL = t47.b47 ],
| | | | | | | | | | | | | | | table(sys.t37) [ t37.a37 NOT NULL HASHCOL , t37.b37, t37.x37 ] COUNT
| | | | | | | | | | | | | | ) [ t47.a47 NOT NULL HASHCOL = t37.b37 ],
| | | | | | | | | | | | | | table(sys.t54) [ t54.a54 NOT NULL HASHCOL , t54.b54, t54.x54 ] COUNT
| | | | | | | | | | | | | ) [ t37.a37 NOT NULL HASHCOL = t54.b54 ],
| | | | | | | | | | | | | table(sys.t50) [ t50.a50 NOT NULL HASHCOL , t50.b50, t50.x50 ] COUNT
| | | | | | | | | | | | ) [ ],
| | | | | | | | | | | | table(sys.t30) [ t30.a30 NOT NULL HASHCOL , t30.b30, t30.x30 ] COUNT
| | | | | | | | | | | ) [ ],
| | | | | | | | | | | table(sys.t55) [ t55.a55 NOT NULL HASHCOL , t55.b55, t55.x55 ] COUNT
| | | | | | | | | | ) [ ],
| | | | | | | | | | table(sys.t21) [ t21.a21 NOT NULL HASHCOL , t21.b21, t21.x21 ] COUNT
| | | | | | | | | ) [ ],
| | | | | | | | | table(sys.t32) [ t32.a32 NOT NULL HASHCOL , t32.b32, t32.x32 ] COUNT
| | | | | | | | ) [ ],
| | | | | | | | table(sys.t63) [ t63.a63 NOT NULL HASHCOL , t63.b63, t63.x63 ] COUNT
| | | | | | | ) [ ],
| | | | | | | table(sys.t9) [ t9.a9 NOT NULL HASHCOL , t9.b9, t9.x9 ] COUNT
| | | | | | ) [ ],
| | | | | | table(sys.t20) [ t20.a20 NOT NULL HASHCOL , t20.b20, t20.x20 ] COUNT
| | | | | ) [ ],
| | | | | table(sys.t29) [ t29.a29 NOT NULL HASHCOL , t29.b29, t29.x29 ] COUNT
| | | | ) [ ],
| | | | table(sys.t22) [ t22.a22 NOT NULL HASHCOL , t22.b22, t22.x22 ] COUNT
| | | ) [ ],
| | | table(sys.t43) [ t43.a43 NOT NULL HASHCOL , t43.b43, t43.x43 ] COUNT
| | ) [ t32.b32 = t30.a30 NOT NULL HASHCOL , t30.b30 = t9.a9 NOT NULL HASHCOL , t21.a21 NOT NULL HASHCOL = t50.b50, t50.a50 NOT NULL HASHCOL = t63.b63, t20.a20 NOT NULL HASHCOL = t55.b55, t55.a55 NOT NULL HASHCOL = t9.b9, t43.a43 NOT NULL HASHCOL = t29.b29, t22.a22 NOT NULL HASHCOL = t43.b43, t29.a29 NOT NULL HASHCOL = t21.b21, t22.b22 = t32.a32 NOT NULL HASHCOL ],
| | table(sys.t8) [ t8.a8 NOT NULL HASHCOL , t8.b8, t8.x8 ] COUNT
| ) [ t54.a54 NOT NULL HASHCOL = t8.b8, t8.a8 NOT NULL HASHCOL = t20.b20 ]
) [ t20.x20, t47.x47, t38.x38, t18.x18, t10.x10, t22.x22, t37.x37, t3.x3, t63.x63, t8.x8, t30.x30, t43.x43, t54.x54, t9.x9, t21.x21, t25.x25, t2.x2, t61.x61, t55.x55, t32.x32, t52.x52, t29.x29, t50.x50, t12.x12 ]
plan
SELECT x20,x47,x38,x18,x10,x22,x37,x3,x63,x8,x30,x43,x54,x9,x21,x25,x2,x61,x55,x32,x52,x29,x50,x12
FROM t2,t3,t8,t9,t10,t12,t18,t20,t21,t22,t25,t29,t30,t32,t37,t38,t43,t47,t50,t52,t54,t55,t61,t63
WHERE b3=a18
AND a25=b38
AND a54=b8
AND a61=b47
AND a43=b29
AND b18=a12
AND b32=a30
AND a22=b43
AND a29=b21
AND a10=b25
AND a12=4
AND b22=a32
AND a20=b55
AND b30=a9
AND a2=b61
AND a38=b52
AND a55=b9
AND a21=b50
AND a37=b54
AND b10=a3
AND a52=b2
AND a50=b63
% .plan table_name
% rel name
% clob type
% 212 length
project (
| join (
| | join (
| | | join (
| | | | join (
| | | | | join (
| | | | | | join (
| | | | | | | join (
| | | | | | | | join (
| | | | | | | | | join (
| | | | | | | | | | join (
| | | | | | | | | | | join (
| | | | | | | | | | | | join (
| | | | | | | | | | | | | join (
| | | | | | | | | | | | | | join (
| | | | | | | | | | | | | | | join (
| | | | | | | | | | | | | | | | join (
| | | | | | | | | | | | | | | | | join (
| | | | | | | | | | | | | | | | | | join (
| | | | | | | | | | | | | | | | | | | join (
| | | | | | | | | | | | | | | | | | | | join (
| | | | | | | | | | | | | | | | | | | | | join (
| | | | | | | | | | | | | | | | | | | | | | join (
| | | | | | | | | | | | | | | | | | | | | | | join (
| | | | | | | | | | | | | | | | | | | | | | | | table(sys.t18) [ t18.a18 NOT NULL HASHCOL , t18.b18, t18.x18 ] COUNT ,
| | | | | | | | | | | | | | | | | | | | | | | | select (
| | | | | | | | | | | | | | | | | | | | | | | | | table(sys.t12) [ t12.a12 NOT NULL HASHCOL , t12.x12 ] COUNT
| | | | | | | | | | | | | | | | | | | | | | | | ) [ t12.a12 NOT NULL HASHCOL = int "4" ]
| | | | | | | | | | | | | | | | | | | | | | | ) [ t18.b18 = t12.a12 NOT NULL HASHCOL ],
| | | | | | | | | | | | | | | | | | | | | | | table(sys.t3) [ t3.a3 NOT NULL HASHCOL , t3.b3, t3.x3 ] COUNT
| | | | | | | | | | | | | | | | | | | | | | ) [ t3.b3 = t18.a18 NOT NULL HASHCOL ],
| | | | | | | | | | | | | | | | | | | | | | table(sys.t10) [ t10.a10 NOT NULL HASHCOL , t10.b10, t10.x10 ] COUNT
| | | | | | | | | | | | | | | | | | | | | ) [ t10.b10 = t3.a3 NOT NULL HASHCOL ],
| | | | | | | | | | | | | | | | | | | | | table(sys.t25) [ t25.a25 NOT NULL HASHCOL , t25.b25, t25.x25 ] COUNT
| | | | | | | | | | | | | | | | | | | | ) [ t10.a10 NOT NULL HASHCOL = t25.b25 ],
| | | | | | | | | | | | | | | | | | | | table(sys.t38) [ t38.a38 NOT NULL HASHCOL , t38.b38, t38.x38 ] COUNT
| | | | | | | | | | | | | | | | | | | ) [ t25.a25 NOT NULL HASHCOL = t38.b38 ],
| | | | | | | | | | | | | | | | | | | table(sys.t52) [ t52.a52 NOT NULL HASHCOL , t52.b52, t52.x52 ] COUNT
| | | | | | | | | | | | | | | | | | ) [ t38.a38 NOT NULL HASHCOL = t52.b52 ],
| | | | | | | | | | | | | | | | | | table(sys.t2) [ t2.a2 NOT NULL HASHCOL , t2.b2, t2.x2 ] COUNT
| | | | | | | | | | | | | | | | | ) [ t52.a52 NOT NULL HASHCOL = t2.b2 ],
| | | | | | | | | | | | | | | | | table(sys.t61) [ t61.a61 NOT NULL HASHCOL , t61.b61, t61.x61 ] COUNT
| | | | | | | | | | | | | | | | ) [ t2.a2 NOT NULL HASHCOL = t61.b61 ],
| | | | | | | | | | | | | | | | table(sys.t47) [ t47.a47 NOT NULL HASHCOL , t47.b47, t47.x47 ] COUNT
| | | | | | | | | | | | | | | ) [ t61.a61 NOT NULL HASHCOL = t47.b47 ],
| | | | | | | | | | | | | | | table(sys.t37) [ t37.a37 NOT NULL HASHCOL , t37.b37, t37.x37 ] COUNT
| | | | | | | | | | | | | | ) [ t47.a47 NOT NULL HASHCOL = t37.b37 ],
| | | | | | | | | | | | | | table(sys.t54) [ t54.a54 NOT NULL HASHCOL , t54.b54, t54.x54 ] COUNT
| | | | | | | | | | | | | ) [ t37.a37 NOT NULL HASHCOL = t54.b54 ],
| | | | | | | | | | | | | table(sys.t8) [ t8.a8 NOT NULL HASHCOL , t8.b8, t8.x8 ] COUNT
| | | | | | | | | | | | ) [ t54.a54 NOT NULL HASHCOL = t8.b8 ],
| | | | | | | | | | | | table(sys.t20) [ t20.a20 NOT NULL HASHCOL , t20.b20, t20.x20 ] COUNT
| | | | | | | | | | | ) [ t8.a8 NOT NULL HASHCOL = t20.b20 ],
| | | | | | | | | | | table(sys.t55) [ t55.a55 NOT NULL HASHCOL , t55.b55, t55.x55 ] COUNT
| | | | | | | | | | ) [ t20.a20 NOT NULL HASHCOL = t55.b55 ],
| | | | | | | | | | table(sys.t9) [ t9.a9 NOT NULL HASHCOL , t9.b9, t9.x9 ] COUNT
| | | | | | | | | ) [ t55.a55 NOT NULL HASHCOL = t9.b9 ],
| | | | | | | | | table(sys.t30) [ t30.a30 NOT NULL HASHCOL , t30.b30, t30.x30 ] COUNT
| | | | | | | | ) [ t30.b30 = t9.a9 NOT NULL HASHCOL ],
| | | | | | | | table(sys.t32) [ t32.a32 NOT NULL HASHCOL , t32.b32, t32.x32 ] COUNT
| | | | | | | ) [ t32.b32 = t30.a30 NOT NULL HASHCOL ],
| | | | | | | table(sys.t22) [ t22.a22 NOT NULL HASHCOL , t22.b22, t22.x22 ] COUNT
| | | | | | ) [ t22.b22 = t32.a32 NOT NULL HASHCOL ],
| | | | | | table(sys.t43) [ t43.a43 NOT NULL HASHCOL , t43.b43, t43.x43 ] COUNT
| | | | | ) [ t22.a22 NOT NULL HASHCOL = t43.b43 ],
| | | | | table(sys.t29) [ t29.a29 NOT NULL HASHCOL , t29.b29, t29.x29 ] COUNT
| | | | ) [ t43.a43 NOT NULL HASHCOL = t29.b29 ],
| | | | table(sys.t21) [ t21.a21 NOT NULL HASHCOL , t21.b21, t21.x21 ] COUNT
| | | ) [ t29.a29 NOT NULL HASHCOL = t21.b21 ],
| | | table(sys.t50) [ t50.a50 NOT NULL HASHCOL , t50.b50, t50.x50 ] COUNT
| | ) [ t21.a21 NOT NULL HASHCOL = t50.b50 ],
| | table(sys.t63) [ t63.b63, t63.x63 ] COUNT
| ) [ t50.a50 NOT NULL HASHCOL = t63.b63 ]
) [ t20.x20, t47.x47, t38.x38, t18.x18, t10.x10, t22.x22, t37.x37, t3.x3, t63.x63, t8.x8, t30.x30, t43.x43, t54.x54, t9.x9, t21.x21, t25.x25, t2.x2, t61.x61, t55.x55, t32.x32, t52.x52, t29.x29, t50.x50, t12.x12 ]
plan
SELECT x20,x47,x38,x18,x10,x22,x37,x3,x63,x8,x30,x43,x54,x9,x21,x25,x2,x61,x55,x32,x52,x29,x50,x12
FROM t63,t61,t55,t54,t52,t50,t47,t43,t38,t37,t32,t30,t29,t25,t22,t21,t20,t18,t12,t10,t9,t8,t3,t2
WHERE b3=a18
AND a25=b38
AND a54=b8
AND a61=b47
AND a43=b29
AND b18=a12
AND b32=a30
AND a22=b43
AND a29=b21
AND a10=b25
AND a12=4
AND b22=a32
AND a20=b55
AND b30=a9
AND a2=b61
AND a38=b52
AND a55=b9
AND a21=b50
AND a37=b54
AND b10=a3
AND a52=b2
AND a50=b63
% .plan table_name
% rel name
% clob type
% 555 length
project (
| join (
| | table(sys.t2) [ t2.a2 NOT NULL HASHCOL , t2.b2, t2.x2 ] COUNT ,
| | join (
| | | crossproduct (
| | | | crossproduct (
| | | | | crossproduct (
| | | | | | crossproduct (
| | | | | | | crossproduct (
| | | | | | | | crossproduct (
| | | | | | | | | crossproduct (
| | | | | | | | | | crossproduct (
| | | | | | | | | | | crossproduct (
| | | | | | | | | | | | crossproduct (
| | | | | | | | | | | | | crossproduct (
| | | | | | | | | | | | | | crossproduct (
| | | | | | | | | | | | | | | crossproduct (
| | | | | | | | | | | | | | | | crossproduct (
| | | | | | | | | | | | | | | | | crossproduct (
| | | | | | | | | | | | | | | | | | join (
| | | | | | | | | | | | | | | | | | | join (
| | | | | | | | | | | | | | | | | | | | join (
| | | | | | | | | | | | | | | | | | | | | join (
| | | | | | | | | | | | | | | | | | | | | | join (
| | | | | | | | | | | | | | | | | | | | | | | join (
| | | | | | | | | | | | | | | | | | | | | | | | table(sys.t18) [ t18.a18 NOT NULL HASHCOL , t18.b18, t18.x18 ] COUNT ,
| | | | | | | | | | | | | | | | | | | | | | | | select (
| | | | | | | | | | | | | | | | | | | | | | | | | table(sys.t12) [ t12.a12 NOT NULL HASHCOL , t12.x12 ] COUNT
| | | | | | | | | | | | | | | | | | | | | | | | ) [ t12.a12 NOT NULL HASHCOL = int "4" ]
| | | | | | | | | | | | | | | | | | | | | | | ) [ t18.b18 = t12.a12 NOT NULL HASHCOL ],
| | | | | | | | | | | | | | | | | | | | | | | table(sys.t3) [ t3.a3 NOT NULL HASHCOL , t3.b3, t3.x3 ] COUNT
| | | | | | | | | | | | | | | | | | | | | | ) [ t3.b3 = t18.a18 NOT NULL HASHCOL ],
| | | | | | | | | | | | | | | | | | | | | | table(sys.t10) [ t10.a10 NOT NULL HASHCOL , t10.b10, t10.x10 ] COUNT
| | | | | | | | | | | | | | | | | | | | | ) [ t10.b10 = t3.a3 NOT NULL HASHCOL ],
| | | | | | | | | | | | | | | | | | | | | table(sys.t25) [ t25.a25 NOT NULL HASHCOL , t25.b25, t25.x25 ] COUNT
| | | | | | | | | | | | | | | | | | | | ) [ t10.a10 NOT NULL HASHCOL = t25.b25 ],
| | | | | | | | | | | | | | | | | | | | table(sys.t38) [ t38.a38 NOT NULL HASHCOL , t38.b38, t38.x38 ] COUNT
| | | | | | | | | | | | | | | | | | | ) [ t25.a25 NOT NULL HASHCOL = t38.b38 ],
| | | | | | | | | | | | | | | | | | | table(sys.t52) [ t52.a52 NOT NULL HASHCOL , t52.b52, t52.x52 ] COUNT
| | | | | | | | | | | | | | | | | | ) [ t38.a38 NOT NULL HASHCOL = t52.b52 ],
| | | | | | | | | | | | | | | | | | table(sys.t8) [ t8.a8 NOT NULL HASHCOL , t8.b8, t8.x8 ] COUNT
| | | | | | | | | | | | | | | | | ) [ ],
| | | | | | | | | | | | | | | | | table(sys.t9) [ t9.a9 NOT NULL HASHCOL , t9.b9, t9.x9 ] COUNT
| | | | | | | | | | | | | | | | ) [ ],
| | | | | | | | | | | | | | | | table(sys.t50) [ t50.a50 NOT NULL HASHCOL , t50.b50, t50.x50 ] COUNT
| | | | | | | | | | | | | | | ) [ ],
| | | | | | | | | | | | | | | table(sys.t63) [ t63.a63 NOT NULL HASHCOL , t63.b63, t63.x63 ] COUNT
| | | | | | | | | | | | | | ) [ ],
| | | | | | | | | | | | | | table(sys.t21) [ t21.a21 NOT NULL HASHCOL , t21.b21, t21.x21 ] COUNT
| | | | | | | | | | | | | ) [ ],
| | | | | | | | | | | | | table(sys.t29) [ t29.a29 NOT NULL HASHCOL , t29.b29, t29.x29 ] COUNT
| | | | | | | | | | | | ) [ ],
| | | | | | | | | | | | table(sys.t43) [ t43.a43 NOT NULL HASHCOL , t43.b43, t43.x43 ] COUNT
| | | | | | | | | | | ) [ ],
| | | | | | | | | | | table(sys.t22) [ t22.a22 NOT NULL HASHCOL , t22.b22, t22.x22 ] COUNT
| | | | | | | | | | ) [ ],
| | | | | | | | | | table(sys.t32) [ t32.a32 NOT NULL HASHCOL , t32.b32, t32.x32 ] COUNT
| | | | | | | | | ) [ ],
| | | | | | | | | table(sys.t30) [ t30.a30 NOT NULL HASHCOL , t30.b30, t30.x30 ] COUNT
| | | | | | | | ) [ ],
| | | | | | | | table(sys.t54) [ t54.a54 NOT NULL HASHCOL , t54.b54, t54.x54 ] COUNT
| | | | | | | ) [ ],
| | | | | | | table(sys.t55) [ t55.a55 NOT NULL HASHCOL , t55.b55, t55.x55 ] COUNT
| | | | | | ) [ ],
| | | | | | table(sys.t61) [ t61.a61 NOT NULL HASHCOL , t61.b61, t61.x61 ] COUNT
| | | | | ) [ ],
| | | | | table(sys.t47) [ t47.a47 NOT NULL HASHCOL , t47.b47, t47.x47 ] COUNT
| | | | ) [ ],
| | | | table(sys.t37) [ t37.a37 NOT NULL HASHCOL , t37.b37, t37.x37 ] COUNT
| | | ) [ ],
| | | table(sys.t20) [ t20.a20 NOT NULL HASHCOL , t20.b20, t20.x20 ] COUNT
| | ) [ t54.a54 NOT NULL HASHCOL = t8.b8, t8.a8 NOT NULL HASHCOL = t20.b20, t30.b30 = t9.a9 NOT NULL HASHCOL , t55.a55 NOT NULL HASHCOL = t9.b9, t20.a20 NOT NULL HASHCOL = t55.b55, t37.a37 NOT NULL HASHCOL = t54.b54, t47.a47 NOT NULL HASHCOL = t37.b37, t61.a61 NOT NULL HASHCOL = t47.b47, t32.b32 = t30.a30 NOT NULL HASHCOL , t22.b22 = t32.a32 NOT NULL HASHCOL , t22.a22 NOT NULL HASHCOL = t43.b43, t43.a43 NOT NULL HASHCOL = t29.b29, t29.a29 NOT NULL HASHCOL = t21.b21, t21.a21 NOT NULL HASHCOL = t50.b50, t50.a50 NOT NULL HASHCOL = t63.b63 ]
| ) [ t2.a2 NOT NULL HASHCOL = t61.b61, t52.a52 NOT NULL HASHCOL = t2.b2 ]
) [ t20.x20, t47.x47, t38.x38, t18.x18, t10.x10, t22.x22, t37.x37, t3.x3, t63.x63, t8.x8, t30.x30, t43.x43, t54.x54, t9.x9, t21.x21, t25.x25, t2.x2, t61.x61, t55.x55, t32.x32, t52.x52, t29.x29, t50.x50, t12.x12 ]

Expected Results:

No crossproducts in the plans when only the order of the table names in the FROM-clause differ.

Once the issue has been fixed, please approve the new output of large_join.Bug-3809.stable.out

Comment 21332

Date: 2015-10-14 13:01:13 +0200
From: @njnes

before reordering a join tree clean out the empty selects.

Comment 21335

Date: 2015-10-14 13:16:53 +0200
From: MonetDB Mercurial Repository <>

Changeset 0ef3d9bdad0c made by Niels Nes niels@cwi.nl in the MonetDB repo, refers to this bug.

For complete details, see http//devmonetdborg/hg/MonetDB?cmd=changeset;node=0ef3d9bdad0c

Changeset description:

fix bug #3819, ie properly reorder joins (first cleanup the introduced
empty selects)

Comment 21434

Date: 2015-11-03 10:18:04 +0100
From: @sjoerdmullender

Jul2015 SP1 has been released.
@monetdb-team monetdb-team added bug Something isn't working major SQL labels Nov 30, 2020
@sjoerdmullender sjoerdmullender added this to the Ancient Release milestone Feb 7, 2022
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
bug Something isn't working major SQL
Projects
None yet
Milestone
Ancient Release
Development

No branches or pull requests
2 participants
@sjoerdmullender @monetdb-team