Details
-
Bug
-
Resolution: Fixed
-
Major
-
6.6.2, 7.1.3
-
Untriaged
-
0
-
Unknown
Description
CREATE PRIMARY INDEX on default; |
insert into default (KEY k, VALUE v) SELECT "k"||TO_STR(d) AS k , {"c1":d, "c2":"doc"} AS v FROM ARRAY_RANGE(0,3) AS d; |
SELECT t3.*
|
FROM ( SELECT t2.*
|
FROM ( SELECT d.*
|
FROM default d) AS t1
|
JOIN ( SELECT d0.*
|
FROM default d0) AS t2
|
ON t1.c1 = t2.c1) AS t3
|
JOIN ( SELECT t4.*
|
FROM ( SELECT d2.*
|
FROM default d2) AS t4
|
JOIN ( SELECT d3.*
|
FROM default d3) AS t5
|
ON t4.c1 = t5.c1) AS t6
|
ON t3.c1 = t6.c1 and t3.c2 = t6.c2;
|
6.6.2 gives 1 row which is wrong
7.1.3 hangs
In 7.1.3 t3<==>t6 using NESTED LOOP even forced HASH that may contributing hang
SELECT t3.*
|
FROM ( SELECT t2.*
|
FROM ( SELECT d.*
|
FROM default d) AS t1
|
JOIN ( SELECT d0.*
|
FROM default d0) AS t2 USE HASH (BUILD)
|
ON t1.c1 = t2.c1) AS t3
|
JOIN ( SELECT t4.*
|
FROM ( SELECT d2.*
|
FROM default d2) AS t4
|
JOIN ( SELECT d3.*
|
FROM default d3) AS t5 USE HASH (BUILD)
|
ON t4.c1 = t5.c1) AS t6 USE HASH (BUILD)
|
ON t3.c1 = t6.c1 and t3.c2 = t6.c2;
|
Workaround: If right side of JOIN is subquery use that subquery as CTE if possible
Attachments
Issue Links
- is a backport of
-
MB-54045 [BP to 7.2.0 MB-53565] - CTE with sub select and hash join returns wrong result
- Closed
For Gerrit Dashboard: MB-55059 | ||||||
---|---|---|---|---|---|---|
# | Subject | Branch | Project | Status | CR | V |
184779,2 | MB-55059 Proper formalization for ExpressionTerm and SubqueryTerm under ANSI join | 7.1.4 | query | Status: MERGED | +2 | +1 |