You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-dev@db.apache.org by "Dag H. Wanvik (JIRA)" <ji...@apache.org> on 2010/07/07 19:02:51 UTC
[jira] Created: (DERBY-4736) ASSERT FAIL when code generating a
column reference in a join predicate in presence of other outer join
reordering
ASSERT FAIL when code generating a column reference in a join predicate in presence of other outer join reordering
------------------------------------------------------------------------------------------------------------------
Key: DERBY-4736
URL: https://issues.apache.org/jira/browse/DERBY-4736
Project: Derby
Issue Type: Sub-task
Components: SQL
Affects Versions: 10.7.0.0
Reporter: Dag H. Wanvik
>From schema given in DERBY-4712, this query gives an ASSERT with sane Derby:
rs = s.executeQuery("SELECT 1 FROM (T0 LEFT JOIN (T1 LEFT JOIN (T2 LEFT JOIN " +
" (T3 LEFT JOIN T4 ON 1=1) ON T2.X = T3.X) ON 1=1) ON 1=1) " +
" LEFT JOIN " +
" (T5 INNER JOIN T6 ON 1=1) " +
" ON T2.X = 1 ");
Cf the attachments in DERBY-4712 assert-bind-opt-trees.*.
>From preliminary analysis, this error seems to be unrelated to the NPEs reported in DERBY-4712, so filing this as a sub-issue.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Closed: (DERBY-4736) ASSERT FAIL when code generating a
column reference in a join predicate in presence of other outer join
reordering
Posted by "Dag H. Wanvik (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4736?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Dag H. Wanvik closed DERBY-4736.
--------------------------------
Resolution: Fixed
> ASSERT FAIL when code generating a column reference in a join predicate in presence of other outer join reordering
> ------------------------------------------------------------------------------------------------------------------
>
> Key: DERBY-4736
> URL: https://issues.apache.org/jira/browse/DERBY-4736
> Project: Derby
> Issue Type: Sub-task
> Components: SQL
> Affects Versions: 10.7.0.0
> Reporter: Dag H. Wanvik
> Assignee: Dag H. Wanvik
> Fix For: 10.7.0.0
>
> Attachments: derby-4736-1a.diff, derby-4736-1a.stat, derby-4736-1b.diff, derby-4736-1b.stat, derby-4736-1c.diff, derby-4736-1c.stat, derby-4736-1d.diff, derby-4736-1d.stat, loj-analysis.txt, query_plan_derby_4736.pdf
>
>
> From schema given in DERBY-4712, this query gives an ASSERT with sane Derby:
> rs = s.executeQuery("SELECT 1 FROM (T0 LEFT JOIN (T1 LEFT JOIN (T2 LEFT JOIN " +
> " (T3 LEFT JOIN T4 ON 1=1) ON T2.X = T3.X) ON 1=1) ON 1=1) " +
> " LEFT JOIN " +
> " (T5 INNER JOIN T6 ON 1=1) " +
> " ON T2.X = 1 ");
> Cf the attachments in DERBY-4712 assert-bind-opt-trees.*.
> From preliminary analysis, this error seems to be unrelated to the NPEs reported in DERBY-4712, so filing this as a sub-issue.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Updated: (DERBY-4736) ASSERT FAIL when code generating a
column reference in a join predicate in presence of other outer join
reordering
Posted by "Dag H. Wanvik (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4736?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Dag H. Wanvik updated DERBY-4736:
---------------------------------
Attachment: derby-4736-1c.diff
derby-4736-1c.stat
Actually, it was Knut's simplified query that got an erroneous reordering.
If I use the original query from DERBY-4712, we get a correct reordering *and* the ASSERT
error seen. So, we will address the wrong reordering in DERBY-4471 and use the original query
in our test for this issue.
Uploading DERBY-4736-1c using the original query, checking that we get the correct result and also that the expected reordering takes place. Running regressions, ready for review.
> ASSERT FAIL when code generating a column reference in a join predicate in presence of other outer join reordering
> ------------------------------------------------------------------------------------------------------------------
>
> Key: DERBY-4736
> URL: https://issues.apache.org/jira/browse/DERBY-4736
> Project: Derby
> Issue Type: Sub-task
> Components: SQL
> Affects Versions: 10.7.0.0
> Reporter: Dag H. Wanvik
> Assignee: Dag H. Wanvik
> Attachments: derby-4736-1a.diff, derby-4736-1a.stat, derby-4736-1b.diff, derby-4736-1b.stat, derby-4736-1c.diff, derby-4736-1c.stat, loj-analysis.txt, query_plan_derby_4736.pdf
>
>
> From schema given in DERBY-4712, this query gives an ASSERT with sane Derby:
> rs = s.executeQuery("SELECT 1 FROM (T0 LEFT JOIN (T1 LEFT JOIN (T2 LEFT JOIN " +
> " (T3 LEFT JOIN T4 ON 1=1) ON T2.X = T3.X) ON 1=1) ON 1=1) " +
> " LEFT JOIN " +
> " (T5 INNER JOIN T6 ON 1=1) " +
> " ON T2.X = 1 ");
> Cf the attachments in DERBY-4712 assert-bind-opt-trees.*.
> From preliminary analysis, this error seems to be unrelated to the NPEs reported in DERBY-4712, so filing this as a sub-issue.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Updated: (DERBY-4736) ASSERT FAIL when code generating a
column reference in a join predicate in presence of other outer join
reordering
Posted by "Dag H. Wanvik (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4736?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Dag H. Wanvik updated DERBY-4736:
---------------------------------
Attachment: derby-4736-1b.diff
derby-4736-1b.stat
Uploading a new version of the preliminary patch, derby-4736-1b, which makes the test
correctly expect a row count of 1280 (the same as postgresql). By commenting out the LOJ reordering code in SelectNode, Derby produces the same number of rows also, so there is another bug in the reordering logic somewhere. The test currently fails, although the assert is gone.
> ASSERT FAIL when code generating a column reference in a join predicate in presence of other outer join reordering
> ------------------------------------------------------------------------------------------------------------------
>
> Key: DERBY-4736
> URL: https://issues.apache.org/jira/browse/DERBY-4736
> Project: Derby
> Issue Type: Sub-task
> Components: SQL
> Affects Versions: 10.7.0.0
> Reporter: Dag H. Wanvik
> Assignee: Dag H. Wanvik
> Attachments: derby-4736-1a.diff, derby-4736-1a.stat, derby-4736-1b.diff, derby-4736-1b.stat
>
>
> From schema given in DERBY-4712, this query gives an ASSERT with sane Derby:
> rs = s.executeQuery("SELECT 1 FROM (T0 LEFT JOIN (T1 LEFT JOIN (T2 LEFT JOIN " +
> " (T3 LEFT JOIN T4 ON 1=1) ON T2.X = T3.X) ON 1=1) ON 1=1) " +
> " LEFT JOIN " +
> " (T5 INNER JOIN T6 ON 1=1) " +
> " ON T2.X = 1 ");
> Cf the attachments in DERBY-4712 assert-bind-opt-trees.*.
> From preliminary analysis, this error seems to be unrelated to the NPEs reported in DERBY-4712, so filing this as a sub-issue.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Updated: (DERBY-4736) ASSERT FAIL when code generating a
column reference in a join predicate in presence of other outer join
reordering
Posted by "Dag H. Wanvik (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4736?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Dag H. Wanvik updated DERBY-4736:
---------------------------------
Fix Version/s: 10.6.2.1
> ASSERT FAIL when code generating a column reference in a join predicate in presence of other outer join reordering
> ------------------------------------------------------------------------------------------------------------------
>
> Key: DERBY-4736
> URL: https://issues.apache.org/jira/browse/DERBY-4736
> Project: Derby
> Issue Type: Sub-task
> Components: SQL
> Affects Versions: 10.0.2.0, 10.0.2.1, 10.1.1.0, 10.1.2.1, 10.1.3.1, 10.2.1.6, 10.2.2.0, 10.3.1.4, 10.3.2.1, 10.3.3.0, 10.4.1.3, 10.4.2.0, 10.5.1.1, 10.5.2.0, 10.5.3.0, 10.6.1.0
> Reporter: Dag H. Wanvik
> Assignee: Dag H. Wanvik
> Fix For: 10.6.2.1, 10.7.0.0
>
> Attachments: derby-4736-1a.diff, derby-4736-1a.stat, derby-4736-1b.diff, derby-4736-1b.stat, derby-4736-1c.diff, derby-4736-1c.stat, derby-4736-1d.diff, derby-4736-1d.stat, derby-4736-followup-a.diff, derby-4736-followup-a.stat, loj-analysis.txt, query_plan_derby_4736.pdf
>
>
> From schema given in DERBY-4712, this query gives an ASSERT with sane Derby:
> rs = s.executeQuery("SELECT 1 FROM (T0 LEFT JOIN (T1 LEFT JOIN (T2 LEFT JOIN " +
> " (T3 LEFT JOIN T4 ON 1=1) ON T2.X = T3.X) ON 1=1) ON 1=1) " +
> " LEFT JOIN " +
> " (T5 INNER JOIN T6 ON 1=1) " +
> " ON T2.X = 1 ");
> Cf the attachments in DERBY-4712 assert-bind-opt-trees.*.
> From preliminary analysis, this error seems to be unrelated to the NPEs reported in DERBY-4712, so filing this as a sub-issue.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Updated: (DERBY-4736) ASSERT FAIL when code generating a
column reference in a join predicate in presence of other outer join
reordering
Posted by "Dag H. Wanvik (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4736?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Dag H. Wanvik updated DERBY-4736:
---------------------------------
Attachment: derby-4736-1d.diff
derby-4736-1d.stat
Uploading version 1d of this patch, which just improves legibility a bit in the new RuntimeStatisticsParser#assertSequence argument strings, by interpreting "_" as TAB and removing the explicit newline in favor if it being implicit.
I think this patch is pretty safe, so if there are no objections, I'll commit it tomorrow.
> ASSERT FAIL when code generating a column reference in a join predicate in presence of other outer join reordering
> ------------------------------------------------------------------------------------------------------------------
>
> Key: DERBY-4736
> URL: https://issues.apache.org/jira/browse/DERBY-4736
> Project: Derby
> Issue Type: Sub-task
> Components: SQL
> Affects Versions: 10.7.0.0
> Reporter: Dag H. Wanvik
> Assignee: Dag H. Wanvik
> Attachments: derby-4736-1a.diff, derby-4736-1a.stat, derby-4736-1b.diff, derby-4736-1b.stat, derby-4736-1c.diff, derby-4736-1c.stat, derby-4736-1d.diff, derby-4736-1d.stat, loj-analysis.txt, query_plan_derby_4736.pdf
>
>
> From schema given in DERBY-4712, this query gives an ASSERT with sane Derby:
> rs = s.executeQuery("SELECT 1 FROM (T0 LEFT JOIN (T1 LEFT JOIN (T2 LEFT JOIN " +
> " (T3 LEFT JOIN T4 ON 1=1) ON T2.X = T3.X) ON 1=1) ON 1=1) " +
> " LEFT JOIN " +
> " (T5 INNER JOIN T6 ON 1=1) " +
> " ON T2.X = 1 ");
> Cf the attachments in DERBY-4712 assert-bind-opt-trees.*.
> From preliminary analysis, this error seems to be unrelated to the NPEs reported in DERBY-4712, so filing this as a sub-issue.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-4736) ASSERT FAIL when code generating a
column reference in a join predicate in presence of other outer join
reordering
Posted by "Rick Hillegas (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4736?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12889900#action_12889900 ]
Rick Hillegas commented on DERBY-4736:
--------------------------------------
Thanks for the patch, Dag. It's a hopeful sign that this only involves a 1 line change to the engine code. If I understand this change correctly, it seems that you have replaced a full rebinding of all expressions with a more limited rebinding of just the result columns. I would think that a full rebinding would include the more limited rebinding of the result columns. Since you have just been through this pinball machine, can you explain why this fixes the problem? Thanks.
> ASSERT FAIL when code generating a column reference in a join predicate in presence of other outer join reordering
> ------------------------------------------------------------------------------------------------------------------
>
> Key: DERBY-4736
> URL: https://issues.apache.org/jira/browse/DERBY-4736
> Project: Derby
> Issue Type: Sub-task
> Components: SQL
> Affects Versions: 10.7.0.0
> Reporter: Dag H. Wanvik
> Assignee: Dag H. Wanvik
> Attachments: derby-4736-1a.diff, derby-4736-1a.stat, derby-4736-1b.diff, derby-4736-1b.stat, derby-4736-1c.diff, derby-4736-1c.stat, loj-analysis.txt, query_plan_derby_4736.pdf
>
>
> From schema given in DERBY-4712, this query gives an ASSERT with sane Derby:
> rs = s.executeQuery("SELECT 1 FROM (T0 LEFT JOIN (T1 LEFT JOIN (T2 LEFT JOIN " +
> " (T3 LEFT JOIN T4 ON 1=1) ON T2.X = T3.X) ON 1=1) ON 1=1) " +
> " LEFT JOIN " +
> " (T5 INNER JOIN T6 ON 1=1) " +
> " ON T2.X = 1 ");
> Cf the attachments in DERBY-4712 assert-bind-opt-trees.*.
> From preliminary analysis, this error seems to be unrelated to the NPEs reported in DERBY-4712, so filing this as a sub-issue.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Updated: (DERBY-4736) ASSERT FAIL when code generating a
column reference in a join predicate in presence of other outer join
reordering
Posted by "Dag H. Wanvik (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4736?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Dag H. Wanvik updated DERBY-4736:
---------------------------------
Fix Version/s: 10.7.0.0
Issue & fix info: [Repro attached] (was: [Patch Available, Repro attached])
> ASSERT FAIL when code generating a column reference in a join predicate in presence of other outer join reordering
> ------------------------------------------------------------------------------------------------------------------
>
> Key: DERBY-4736
> URL: https://issues.apache.org/jira/browse/DERBY-4736
> Project: Derby
> Issue Type: Sub-task
> Components: SQL
> Affects Versions: 10.7.0.0
> Reporter: Dag H. Wanvik
> Assignee: Dag H. Wanvik
> Fix For: 10.7.0.0
>
> Attachments: derby-4736-1a.diff, derby-4736-1a.stat, derby-4736-1b.diff, derby-4736-1b.stat, derby-4736-1c.diff, derby-4736-1c.stat, derby-4736-1d.diff, derby-4736-1d.stat, loj-analysis.txt, query_plan_derby_4736.pdf
>
>
> From schema given in DERBY-4712, this query gives an ASSERT with sane Derby:
> rs = s.executeQuery("SELECT 1 FROM (T0 LEFT JOIN (T1 LEFT JOIN (T2 LEFT JOIN " +
> " (T3 LEFT JOIN T4 ON 1=1) ON T2.X = T3.X) ON 1=1) ON 1=1) " +
> " LEFT JOIN " +
> " (T5 INNER JOIN T6 ON 1=1) " +
> " ON T2.X = 1 ");
> Cf the attachments in DERBY-4712 assert-bind-opt-trees.*.
> From preliminary analysis, this error seems to be unrelated to the NPEs reported in DERBY-4712, so filing this as a sub-issue.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-4736) ASSERT FAIL when code generating a
column reference in a join predicate in presence of other outer join
reordering
Posted by "Dag H. Wanvik (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4736?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12910187#action_12910187 ]
Dag H. Wanvik commented on DERBY-4736:
--------------------------------------
Backported to 10.6 branch as svn 997790.
> ASSERT FAIL when code generating a column reference in a join predicate in presence of other outer join reordering
> ------------------------------------------------------------------------------------------------------------------
>
> Key: DERBY-4736
> URL: https://issues.apache.org/jira/browse/DERBY-4736
> Project: Derby
> Issue Type: Sub-task
> Components: SQL
> Affects Versions: 10.0.2.0, 10.0.2.1, 10.1.1.0, 10.1.2.1, 10.1.3.1, 10.2.1.6, 10.2.2.0, 10.3.1.4, 10.3.2.1, 10.3.3.0, 10.4.1.3, 10.4.2.0, 10.5.1.1, 10.5.2.0, 10.5.3.0, 10.6.1.0
> Reporter: Dag H. Wanvik
> Assignee: Dag H. Wanvik
> Fix For: 10.6.2.1, 10.7.0.0
>
> Attachments: derby-4736-1a.diff, derby-4736-1a.stat, derby-4736-1b.diff, derby-4736-1b.stat, derby-4736-1c.diff, derby-4736-1c.stat, derby-4736-1d.diff, derby-4736-1d.stat, derby-4736-followup-a.diff, derby-4736-followup-a.stat, loj-analysis.txt, query_plan_derby_4736.pdf
>
>
> From schema given in DERBY-4712, this query gives an ASSERT with sane Derby:
> rs = s.executeQuery("SELECT 1 FROM (T0 LEFT JOIN (T1 LEFT JOIN (T2 LEFT JOIN " +
> " (T3 LEFT JOIN T4 ON 1=1) ON T2.X = T3.X) ON 1=1) ON 1=1) " +
> " LEFT JOIN " +
> " (T5 INNER JOIN T6 ON 1=1) " +
> " ON T2.X = 1 ");
> Cf the attachments in DERBY-4712 assert-bind-opt-trees.*.
> From preliminary analysis, this error seems to be unrelated to the NPEs reported in DERBY-4712, so filing this as a sub-issue.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-4736) ASSERT FAIL when code generating a
column reference in a join predicate in presence of other outer join
reordering
Posted by "Dag H. Wanvik (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4736?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12886049#action_12886049 ]
Dag H. Wanvik commented on DERBY-4736:
--------------------------------------
Attaching a patch which makes the ASSERT go away and the query complete, as well as a new fixture to OuterJoinTest: testDerby4736.
The problem is that a LOJ reordering has happened (cf. assert-bind-opt-trees.txt: positions of T2, T3 and T4),
the result column lists are regenerated for the affected join nodes.
The change is detected in SelectNode#preprocess, cf. this if-statement, ca line 997:
if (anyChange)
{
FromList afromList = (FromList) getNodeFactory().getNode(C_NodeTypes.FROM_LIST,
getNodeFactory().doJoinOrderOptimization(),
getContextManager());
bindExpressions(afromList);
}
However, the call to bindExpressions does *not* lead to rebinding of the join clause ON T2.X = 1, since the join clauses are
bound as part of JoinNode#BindResultColumns, cf cdeferredBindExpressionsall to deferredBindExpressions. Adding the call
bindResultColumns(aFromList)
to the above if-block makes the ASSERT go away.
I am not convinced that the execution results are correct, though, since it returns a different number of rows that does PostgreSQL.
I will investigate.
> ASSERT FAIL when code generating a column reference in a join predicate in presence of other outer join reordering
> ------------------------------------------------------------------------------------------------------------------
>
> Key: DERBY-4736
> URL: https://issues.apache.org/jira/browse/DERBY-4736
> Project: Derby
> Issue Type: Sub-task
> Components: SQL
> Affects Versions: 10.7.0.0
> Reporter: Dag H. Wanvik
> Attachments: derby-4736-1a.diff, derby-4736-1a.stat
>
>
> From schema given in DERBY-4712, this query gives an ASSERT with sane Derby:
> rs = s.executeQuery("SELECT 1 FROM (T0 LEFT JOIN (T1 LEFT JOIN (T2 LEFT JOIN " +
> " (T3 LEFT JOIN T4 ON 1=1) ON T2.X = T3.X) ON 1=1) ON 1=1) " +
> " LEFT JOIN " +
> " (T5 INNER JOIN T6 ON 1=1) " +
> " ON T2.X = 1 ");
> Cf the attachments in DERBY-4712 assert-bind-opt-trees.*.
> From preliminary analysis, this error seems to be unrelated to the NPEs reported in DERBY-4712, so filing this as a sub-issue.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-4736) ASSERT FAIL when code generating a
column reference in a join predicate in presence of other outer join
reordering
Posted by "Dag H. Wanvik (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4736?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12886105#action_12886105 ]
Dag H. Wanvik commented on DERBY-4736:
--------------------------------------
This smaller part of the query gives a wrong result, too, unless I comment out the reorder logic:
SELECT count(*) FROM (T2 LEFT JOIN (T3 left outer JOIN T4 ON 1=1) ON T2.X = T3.X)
returns 32 rows, but should return 20, and does if I comment out the reorder logic. So does PostgreSQL. Manually rewriting this query to be left-deep gives 32 rows with both Derby and PostgreSQL. DERBY-4471?
> ASSERT FAIL when code generating a column reference in a join predicate in presence of other outer join reordering
> ------------------------------------------------------------------------------------------------------------------
>
> Key: DERBY-4736
> URL: https://issues.apache.org/jira/browse/DERBY-4736
> Project: Derby
> Issue Type: Sub-task
> Components: SQL
> Affects Versions: 10.7.0.0
> Reporter: Dag H. Wanvik
> Assignee: Dag H. Wanvik
> Attachments: derby-4736-1a.diff, derby-4736-1a.stat, derby-4736-1b.diff, derby-4736-1b.stat
>
>
> From schema given in DERBY-4712, this query gives an ASSERT with sane Derby:
> rs = s.executeQuery("SELECT 1 FROM (T0 LEFT JOIN (T1 LEFT JOIN (T2 LEFT JOIN " +
> " (T3 LEFT JOIN T4 ON 1=1) ON T2.X = T3.X) ON 1=1) ON 1=1) " +
> " LEFT JOIN " +
> " (T5 INNER JOIN T6 ON 1=1) " +
> " ON T2.X = 1 ");
> Cf the attachments in DERBY-4712 assert-bind-opt-trees.*.
> From preliminary analysis, this error seems to be unrelated to the NPEs reported in DERBY-4712, so filing this as a sub-issue.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Updated: (DERBY-4736) ASSERT FAIL when code generating a
column reference in a join predicate in presence of other outer join
reordering
Posted by "Dag H. Wanvik (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4736?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Dag H. Wanvik updated DERBY-4736:
---------------------------------
Affects Version/s: 10.6.1.0
10.5.3.0
10.5.2.0
10.5.1.1
(was: 10.7.0.0)
> ASSERT FAIL when code generating a column reference in a join predicate in presence of other outer join reordering
> ------------------------------------------------------------------------------------------------------------------
>
> Key: DERBY-4736
> URL: https://issues.apache.org/jira/browse/DERBY-4736
> Project: Derby
> Issue Type: Sub-task
> Components: SQL
> Affects Versions: 10.5.1.1, 10.5.2.0, 10.5.3.0, 10.6.1.0
> Reporter: Dag H. Wanvik
> Assignee: Dag H. Wanvik
> Fix For: 10.7.0.0
>
> Attachments: derby-4736-1a.diff, derby-4736-1a.stat, derby-4736-1b.diff, derby-4736-1b.stat, derby-4736-1c.diff, derby-4736-1c.stat, derby-4736-1d.diff, derby-4736-1d.stat, loj-analysis.txt, query_plan_derby_4736.pdf
>
>
> From schema given in DERBY-4712, this query gives an ASSERT with sane Derby:
> rs = s.executeQuery("SELECT 1 FROM (T0 LEFT JOIN (T1 LEFT JOIN (T2 LEFT JOIN " +
> " (T3 LEFT JOIN T4 ON 1=1) ON T2.X = T3.X) ON 1=1) ON 1=1) " +
> " LEFT JOIN " +
> " (T5 INNER JOIN T6 ON 1=1) " +
> " ON T2.X = 1 ");
> Cf the attachments in DERBY-4712 assert-bind-opt-trees.*.
> From preliminary analysis, this error seems to be unrelated to the NPEs reported in DERBY-4712, so filing this as a sub-issue.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Updated: (DERBY-4736) ASSERT FAIL when code generating a
column reference in a join predicate in presence of other outer join
reordering
Posted by "Dag H. Wanvik (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4736?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Dag H. Wanvik updated DERBY-4736:
---------------------------------
Attachment: derby-4736-followup-a.diff
derby-4736-followup-a.stat
Attaching a patch which fixes the nullability issue and adds a new test case.
The problem is that the added call to SelectNode#bindResultColumns, in addition to calling
fromList.bindResultColumns, which what we need in for this issue, also calls super.bindResultColumns,
which sets up the datatypes over again, erroneously (i.e. without taking into consideration the nature of outer join which can make values stemming from otherwise NOT NULL columns be null in the final result set).
Replacing the call to SelectNode#bindResultColumns with fromTable.bindResultColumns avoids this problem. Running regressions.
> ASSERT FAIL when code generating a column reference in a join predicate in presence of other outer join reordering
> ------------------------------------------------------------------------------------------------------------------
>
> Key: DERBY-4736
> URL: https://issues.apache.org/jira/browse/DERBY-4736
> Project: Derby
> Issue Type: Sub-task
> Components: SQL
> Affects Versions: 10.0.2.0, 10.0.2.1, 10.1.1.0, 10.1.2.1, 10.1.3.1, 10.2.1.6, 10.2.2.0, 10.3.1.4, 10.3.2.1, 10.3.3.0, 10.4.1.3, 10.4.2.0, 10.5.1.1, 10.5.2.0, 10.5.3.0, 10.6.1.0
> Reporter: Dag H. Wanvik
> Assignee: Dag H. Wanvik
> Fix For: 10.7.0.0
>
> Attachments: derby-4736-1a.diff, derby-4736-1a.stat, derby-4736-1b.diff, derby-4736-1b.stat, derby-4736-1c.diff, derby-4736-1c.stat, derby-4736-1d.diff, derby-4736-1d.stat, derby-4736-followup-a.diff, derby-4736-followup-a.stat, loj-analysis.txt, query_plan_derby_4736.pdf
>
>
> From schema given in DERBY-4712, this query gives an ASSERT with sane Derby:
> rs = s.executeQuery("SELECT 1 FROM (T0 LEFT JOIN (T1 LEFT JOIN (T2 LEFT JOIN " +
> " (T3 LEFT JOIN T4 ON 1=1) ON T2.X = T3.X) ON 1=1) ON 1=1) " +
> " LEFT JOIN " +
> " (T5 INNER JOIN T6 ON 1=1) " +
> " ON T2.X = 1 ");
> Cf the attachments in DERBY-4712 assert-bind-opt-trees.*.
> From preliminary analysis, this error seems to be unrelated to the NPEs reported in DERBY-4712, so filing this as a sub-issue.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-4736) ASSERT FAIL when code generating a
column reference in a join predicate in presence of other outer join
reordering
Posted by "Dag H. Wanvik (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4736?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12900727#action_12900727 ]
Dag H. Wanvik commented on DERBY-4736:
--------------------------------------
Committed derby-4736-1d as svn 987539, closing.
> ASSERT FAIL when code generating a column reference in a join predicate in presence of other outer join reordering
> ------------------------------------------------------------------------------------------------------------------
>
> Key: DERBY-4736
> URL: https://issues.apache.org/jira/browse/DERBY-4736
> Project: Derby
> Issue Type: Sub-task
> Components: SQL
> Affects Versions: 10.7.0.0
> Reporter: Dag H. Wanvik
> Assignee: Dag H. Wanvik
> Fix For: 10.7.0.0
>
> Attachments: derby-4736-1a.diff, derby-4736-1a.stat, derby-4736-1b.diff, derby-4736-1b.stat, derby-4736-1c.diff, derby-4736-1c.stat, derby-4736-1d.diff, derby-4736-1d.stat, loj-analysis.txt, query_plan_derby_4736.pdf
>
>
> From schema given in DERBY-4712, this query gives an ASSERT with sane Derby:
> rs = s.executeQuery("SELECT 1 FROM (T0 LEFT JOIN (T1 LEFT JOIN (T2 LEFT JOIN " +
> " (T3 LEFT JOIN T4 ON 1=1) ON T2.X = T3.X) ON 1=1) ON 1=1) " +
> " LEFT JOIN " +
> " (T5 INNER JOIN T6 ON 1=1) " +
> " ON T2.X = 1 ");
> Cf the attachments in DERBY-4712 assert-bind-opt-trees.*.
> From preliminary analysis, this error seems to be unrelated to the NPEs reported in DERBY-4712, so filing this as a sub-issue.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-4736) ASSERT FAIL when code generating a
column reference in a join predicate in presence of other outer join
reordering
Posted by "Dag H. Wanvik (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4736?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12885990#action_12885990 ]
Dag H. Wanvik commented on DERBY-4736:
--------------------------------------
for repliminary analysis, see https://issues.apache.org/jira/browse/DERBY-4712?focusedCommentId=12885701&page=com.atlassian.jira.plugin.system.issuetabpanels%3Acomment-tabpanel#action_12885701
> ASSERT FAIL when code generating a column reference in a join predicate in presence of other outer join reordering
> ------------------------------------------------------------------------------------------------------------------
>
> Key: DERBY-4736
> URL: https://issues.apache.org/jira/browse/DERBY-4736
> Project: Derby
> Issue Type: Sub-task
> Components: SQL
> Affects Versions: 10.7.0.0
> Reporter: Dag H. Wanvik
>
> From schema given in DERBY-4712, this query gives an ASSERT with sane Derby:
> rs = s.executeQuery("SELECT 1 FROM (T0 LEFT JOIN (T1 LEFT JOIN (T2 LEFT JOIN " +
> " (T3 LEFT JOIN T4 ON 1=1) ON T2.X = T3.X) ON 1=1) ON 1=1) " +
> " LEFT JOIN " +
> " (T5 INNER JOIN T6 ON 1=1) " +
> " ON T2.X = 1 ");
> Cf the attachments in DERBY-4712 assert-bind-opt-trees.*.
> From preliminary analysis, this error seems to be unrelated to the NPEs reported in DERBY-4712, so filing this as a sub-issue.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Updated: (DERBY-4736) ASSERT FAIL when code generating a
column reference in a join predicate in presence of other outer join
reordering
Posted by "Dag H. Wanvik (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4736?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Dag H. Wanvik updated DERBY-4736:
---------------------------------
Affects Version/s: 10.4.2.0
10.4.1.3
10.3.3.0
10.3.2.1
10.3.1.4
10.2.2.0
10.2.1.6
10.1.3.1
10.1.2.1
10.1.1.0
10.0.2.1
10.0.2.0
> ASSERT FAIL when code generating a column reference in a join predicate in presence of other outer join reordering
> ------------------------------------------------------------------------------------------------------------------
>
> Key: DERBY-4736
> URL: https://issues.apache.org/jira/browse/DERBY-4736
> Project: Derby
> Issue Type: Sub-task
> Components: SQL
> Affects Versions: 10.0.2.0, 10.0.2.1, 10.1.1.0, 10.1.2.1, 10.1.3.1, 10.2.1.6, 10.2.2.0, 10.3.1.4, 10.3.2.1, 10.3.3.0, 10.4.1.3, 10.4.2.0, 10.5.1.1, 10.5.2.0, 10.5.3.0, 10.6.1.0
> Reporter: Dag H. Wanvik
> Assignee: Dag H. Wanvik
> Fix For: 10.7.0.0
>
> Attachments: derby-4736-1a.diff, derby-4736-1a.stat, derby-4736-1b.diff, derby-4736-1b.stat, derby-4736-1c.diff, derby-4736-1c.stat, derby-4736-1d.diff, derby-4736-1d.stat, loj-analysis.txt, query_plan_derby_4736.pdf
>
>
> From schema given in DERBY-4712, this query gives an ASSERT with sane Derby:
> rs = s.executeQuery("SELECT 1 FROM (T0 LEFT JOIN (T1 LEFT JOIN (T2 LEFT JOIN " +
> " (T3 LEFT JOIN T4 ON 1=1) ON T2.X = T3.X) ON 1=1) ON 1=1) " +
> " LEFT JOIN " +
> " (T5 INNER JOIN T6 ON 1=1) " +
> " ON T2.X = 1 ");
> Cf the attachments in DERBY-4712 assert-bind-opt-trees.*.
> From preliminary analysis, this error seems to be unrelated to the NPEs reported in DERBY-4712, so filing this as a sub-issue.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Reopened: (DERBY-4736) ASSERT FAIL when code generating a
column reference in a join predicate in presence of other outer join
reordering
Posted by "Dag H. Wanvik (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4736?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Dag H. Wanvik reopened DERBY-4736:
----------------------------------
In some cases, with this fix, the nullability of columns from the null-producing (right) side of the LOJ
gets set to NOT NULL after reassociation.
> ASSERT FAIL when code generating a column reference in a join predicate in presence of other outer join reordering
> ------------------------------------------------------------------------------------------------------------------
>
> Key: DERBY-4736
> URL: https://issues.apache.org/jira/browse/DERBY-4736
> Project: Derby
> Issue Type: Sub-task
> Components: SQL
> Affects Versions: 10.0.2.0, 10.0.2.1, 10.1.1.0, 10.1.2.1, 10.1.3.1, 10.2.1.6, 10.2.2.0, 10.3.1.4, 10.3.2.1, 10.3.3.0, 10.4.1.3, 10.4.2.0, 10.5.1.1, 10.5.2.0, 10.5.3.0, 10.6.1.0
> Reporter: Dag H. Wanvik
> Assignee: Dag H. Wanvik
> Fix For: 10.7.0.0
>
> Attachments: derby-4736-1a.diff, derby-4736-1a.stat, derby-4736-1b.diff, derby-4736-1b.stat, derby-4736-1c.diff, derby-4736-1c.stat, derby-4736-1d.diff, derby-4736-1d.stat, loj-analysis.txt, query_plan_derby_4736.pdf
>
>
> From schema given in DERBY-4712, this query gives an ASSERT with sane Derby:
> rs = s.executeQuery("SELECT 1 FROM (T0 LEFT JOIN (T1 LEFT JOIN (T2 LEFT JOIN " +
> " (T3 LEFT JOIN T4 ON 1=1) ON T2.X = T3.X) ON 1=1) ON 1=1) " +
> " LEFT JOIN " +
> " (T5 INNER JOIN T6 ON 1=1) " +
> " ON T2.X = 1 ");
> Cf the attachments in DERBY-4712 assert-bind-opt-trees.*.
> From preliminary analysis, this error seems to be unrelated to the NPEs reported in DERBY-4712, so filing this as a sub-issue.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Updated: (DERBY-4736) ASSERT FAIL when code generating a
column reference in a join predicate in presence of other outer join
reordering
Posted by "Dag H. Wanvik (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4736?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Dag H. Wanvik updated DERBY-4736:
---------------------------------
Attachment: loj-analysis.txt
The analysis in "loj-analysis.txt" which I upload, shows why the result gets different (and wrong) with the reordering, by spelling out the intermediate results of the two orderings variants.
> ASSERT FAIL when code generating a column reference in a join predicate in presence of other outer join reordering
> ------------------------------------------------------------------------------------------------------------------
>
> Key: DERBY-4736
> URL: https://issues.apache.org/jira/browse/DERBY-4736
> Project: Derby
> Issue Type: Sub-task
> Components: SQL
> Affects Versions: 10.7.0.0
> Reporter: Dag H. Wanvik
> Assignee: Dag H. Wanvik
> Attachments: derby-4736-1a.diff, derby-4736-1a.stat, derby-4736-1b.diff, derby-4736-1b.stat, loj-analysis.txt
>
>
> From schema given in DERBY-4712, this query gives an ASSERT with sane Derby:
> rs = s.executeQuery("SELECT 1 FROM (T0 LEFT JOIN (T1 LEFT JOIN (T2 LEFT JOIN " +
> " (T3 LEFT JOIN T4 ON 1=1) ON T2.X = T3.X) ON 1=1) ON 1=1) " +
> " LEFT JOIN " +
> " (T5 INNER JOIN T6 ON 1=1) " +
> " ON T2.X = 1 ");
> Cf the attachments in DERBY-4712 assert-bind-opt-trees.*.
> From preliminary analysis, this error seems to be unrelated to the NPEs reported in DERBY-4712, so filing this as a sub-issue.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-4736) ASSERT FAIL when code generating a
column reference in a join predicate in presence of other outer join
reordering
Posted by "Dag H. Wanvik (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4736?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12899842#action_12899842 ]
Dag H. Wanvik commented on DERBY-4736:
--------------------------------------
Thanks for looking at this, Rick.
Actually, I don't *replace* the binding of expressions, I just *added* a call to bind the result columns which was missing and necessary, cf the explanation here: https://issues.apache.org/jira/browse/DERBY-4736?focusedCommentId=12886049&page=com.atlassian.jira.plugin.system.issuetabpanels%3Acomment-tabpanel#action_12886049
(JoinNode#bindResultColumns is not called as part of JoinNode#bindExpressions.)
> ASSERT FAIL when code generating a column reference in a join predicate in presence of other outer join reordering
> ------------------------------------------------------------------------------------------------------------------
>
> Key: DERBY-4736
> URL: https://issues.apache.org/jira/browse/DERBY-4736
> Project: Derby
> Issue Type: Sub-task
> Components: SQL
> Affects Versions: 10.7.0.0
> Reporter: Dag H. Wanvik
> Assignee: Dag H. Wanvik
> Attachments: derby-4736-1a.diff, derby-4736-1a.stat, derby-4736-1b.diff, derby-4736-1b.stat, derby-4736-1c.diff, derby-4736-1c.stat, loj-analysis.txt, query_plan_derby_4736.pdf
>
>
> From schema given in DERBY-4712, this query gives an ASSERT with sane Derby:
> rs = s.executeQuery("SELECT 1 FROM (T0 LEFT JOIN (T1 LEFT JOIN (T2 LEFT JOIN " +
> " (T3 LEFT JOIN T4 ON 1=1) ON T2.X = T3.X) ON 1=1) ON 1=1) " +
> " LEFT JOIN " +
> " (T5 INNER JOIN T6 ON 1=1) " +
> " ON T2.X = 1 ");
> Cf the attachments in DERBY-4712 assert-bind-opt-trees.*.
> From preliminary analysis, this error seems to be unrelated to the NPEs reported in DERBY-4712, so filing this as a sub-issue.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Issue Comment Edited: (DERBY-4736) ASSERT FAIL when code
generating a column reference in a join predicate in presence of other
outer join reordering
Posted by "Dag H. Wanvik (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4736?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12885990#action_12885990 ]
Dag H. Wanvik edited comment on DERBY-4736 at 7/7/10 1:07 PM:
--------------------------------------------------------------
for preliminary analysis, see https://issues.apache.org/jira/browse/DERBY-4712?focusedCommentId=12885701&page=com.atlassian.jira.plugin.system.issuetabpanels%3Acomment-tabpanel#action_12885701
was (Author: dagw):
for repliminary analysis, see https://issues.apache.org/jira/browse/DERBY-4712?focusedCommentId=12885701&page=com.atlassian.jira.plugin.system.issuetabpanels%3Acomment-tabpanel#action_12885701
> ASSERT FAIL when code generating a column reference in a join predicate in presence of other outer join reordering
> ------------------------------------------------------------------------------------------------------------------
>
> Key: DERBY-4736
> URL: https://issues.apache.org/jira/browse/DERBY-4736
> Project: Derby
> Issue Type: Sub-task
> Components: SQL
> Affects Versions: 10.7.0.0
> Reporter: Dag H. Wanvik
>
> From schema given in DERBY-4712, this query gives an ASSERT with sane Derby:
> rs = s.executeQuery("SELECT 1 FROM (T0 LEFT JOIN (T1 LEFT JOIN (T2 LEFT JOIN " +
> " (T3 LEFT JOIN T4 ON 1=1) ON T2.X = T3.X) ON 1=1) ON 1=1) " +
> " LEFT JOIN " +
> " (T5 INNER JOIN T6 ON 1=1) " +
> " ON T2.X = 1 ");
> Cf the attachments in DERBY-4712 assert-bind-opt-trees.*.
> From preliminary analysis, this error seems to be unrelated to the NPEs reported in DERBY-4712, so filing this as a sub-issue.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Issue Comment Edited: (DERBY-4736) ASSERT FAIL when code
generating a column reference in a join predicate in presence of other
outer join reordering
Posted by "Dag H. Wanvik (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4736?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12886049#action_12886049 ]
Dag H. Wanvik edited comment on DERBY-4736 at 7/7/10 3:50 PM:
--------------------------------------------------------------
Attaching a patch which makes the ASSERT go away and the query complete, as well as a new fixture to OuterJoinTest: testDerby4736.
The problem is that a LOJ reordering has happened (cf. assert-bind-opt-trees.txt: positions of T2, T3 and T4),
the result column lists are regenerated for the affected join nodes.
The change is detected in SelectNode#preprocess, cf. this if-statement, ca line 997:
if (anyChange)
{
FromList afromList = (FromList) getNodeFactory().getNode(C_NodeTypes.FROM_LIST,
getNodeFactory().doJoinOrderOptimization(),
getContextManager());
bindExpressions(afromList);
}
However, the call to bindExpressions does *not* lead to rebinding of the join clause ON T2.X = 1, since the join clauses are
bound as part of JoinNode#BindResultColumns, cf call to deferredBindExpressions. Adding the call
bindResultColumns(aFromList)
to the above if-block makes the ASSERT go away.
I am not convinced that the execution results are correct, though, since it returns a different number of rows that does PostgreSQL.
I will investigate.
was (Author: dagw):
Attaching a patch which makes the ASSERT go away and the query complete, as well as a new fixture to OuterJoinTest: testDerby4736.
The problem is that a LOJ reordering has happened (cf. assert-bind-opt-trees.txt: positions of T2, T3 and T4),
the result column lists are regenerated for the affected join nodes.
The change is detected in SelectNode#preprocess, cf. this if-statement, ca line 997:
if (anyChange)
{
FromList afromList = (FromList) getNodeFactory().getNode(C_NodeTypes.FROM_LIST,
getNodeFactory().doJoinOrderOptimization(),
getContextManager());
bindExpressions(afromList);
}
However, the call to bindExpressions does *not* lead to rebinding of the join clause ON T2.X = 1, since the join clauses are
bound as part of JoinNode#BindResultColumns, cf cdeferredBindExpressionsall to deferredBindExpressions. Adding the call
bindResultColumns(aFromList)
to the above if-block makes the ASSERT go away.
I am not convinced that the execution results are correct, though, since it returns a different number of rows that does PostgreSQL.
I will investigate.
> ASSERT FAIL when code generating a column reference in a join predicate in presence of other outer join reordering
> ------------------------------------------------------------------------------------------------------------------
>
> Key: DERBY-4736
> URL: https://issues.apache.org/jira/browse/DERBY-4736
> Project: Derby
> Issue Type: Sub-task
> Components: SQL
> Affects Versions: 10.7.0.0
> Reporter: Dag H. Wanvik
> Attachments: derby-4736-1a.diff, derby-4736-1a.stat
>
>
> From schema given in DERBY-4712, this query gives an ASSERT with sane Derby:
> rs = s.executeQuery("SELECT 1 FROM (T0 LEFT JOIN (T1 LEFT JOIN (T2 LEFT JOIN " +
> " (T3 LEFT JOIN T4 ON 1=1) ON T2.X = T3.X) ON 1=1) ON 1=1) " +
> " LEFT JOIN " +
> " (T5 INNER JOIN T6 ON 1=1) " +
> " ON T2.X = 1 ");
> Cf the attachments in DERBY-4712 assert-bind-opt-trees.*.
> From preliminary analysis, this error seems to be unrelated to the NPEs reported in DERBY-4712, so filing this as a sub-issue.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Updated: (DERBY-4736) ASSERT FAIL when code generating a
column reference in a join predicate in presence of other outer join
reordering
Posted by "Dag H. Wanvik (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4736?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Dag H. Wanvik updated DERBY-4736:
---------------------------------
Attachment: derby-4736-1a.diff
derby-4736-1a.stat
> ASSERT FAIL when code generating a column reference in a join predicate in presence of other outer join reordering
> ------------------------------------------------------------------------------------------------------------------
>
> Key: DERBY-4736
> URL: https://issues.apache.org/jira/browse/DERBY-4736
> Project: Derby
> Issue Type: Sub-task
> Components: SQL
> Affects Versions: 10.7.0.0
> Reporter: Dag H. Wanvik
> Attachments: derby-4736-1a.diff, derby-4736-1a.stat
>
>
> From schema given in DERBY-4712, this query gives an ASSERT with sane Derby:
> rs = s.executeQuery("SELECT 1 FROM (T0 LEFT JOIN (T1 LEFT JOIN (T2 LEFT JOIN " +
> " (T3 LEFT JOIN T4 ON 1=1) ON T2.X = T3.X) ON 1=1) ON 1=1) " +
> " LEFT JOIN " +
> " (T5 INNER JOIN T6 ON 1=1) " +
> " ON T2.X = 1 ");
> Cf the attachments in DERBY-4712 assert-bind-opt-trees.*.
> From preliminary analysis, this error seems to be unrelated to the NPEs reported in DERBY-4712, so filing this as a sub-issue.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Updated: (DERBY-4736) ASSERT FAIL when code generating a
column reference in a join predicate in presence of other outer join
reordering
Posted by "C.S. Nirmal J. Fernando (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4736?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
C.S. Nirmal J. Fernando updated DERBY-4736:
-------------------------------------------
Attachment: query_plan_derby_4736.pdf
Hi,
I'm attaching the query plan created using PlanExporter tool (DERBY-4587)
(I created a PDF, using screen shots of the generated HTML), for the query
SELECT count(*) FROM (T2 LEFT JOIN (T3 left outer JOIN T4 ON 1=1) ON T2.X = T3.X) ,
since I saw Dag's comment saying incorrect results returned for that query.
Hope this helps!
Thanks.
> ASSERT FAIL when code generating a column reference in a join predicate in presence of other outer join reordering
> ------------------------------------------------------------------------------------------------------------------
>
> Key: DERBY-4736
> URL: https://issues.apache.org/jira/browse/DERBY-4736
> Project: Derby
> Issue Type: Sub-task
> Components: SQL
> Affects Versions: 10.7.0.0
> Reporter: Dag H. Wanvik
> Assignee: Dag H. Wanvik
> Attachments: derby-4736-1a.diff, derby-4736-1a.stat, derby-4736-1b.diff, derby-4736-1b.stat, loj-analysis.txt, query_plan_derby_4736.pdf
>
>
> From schema given in DERBY-4712, this query gives an ASSERT with sane Derby:
> rs = s.executeQuery("SELECT 1 FROM (T0 LEFT JOIN (T1 LEFT JOIN (T2 LEFT JOIN " +
> " (T3 LEFT JOIN T4 ON 1=1) ON T2.X = T3.X) ON 1=1) ON 1=1) " +
> " LEFT JOIN " +
> " (T5 INNER JOIN T6 ON 1=1) " +
> " ON T2.X = 1 ");
> Cf the attachments in DERBY-4712 assert-bind-opt-trees.*.
> From preliminary analysis, this error seems to be unrelated to the NPEs reported in DERBY-4712, so filing this as a sub-issue.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Assigned: (DERBY-4736) ASSERT FAIL when code generating a
column reference in a join predicate in presence of other outer join
reordering
Posted by "Dag H. Wanvik (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4736?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Dag H. Wanvik reassigned DERBY-4736:
------------------------------------
Assignee: Dag H. Wanvik
> ASSERT FAIL when code generating a column reference in a join predicate in presence of other outer join reordering
> ------------------------------------------------------------------------------------------------------------------
>
> Key: DERBY-4736
> URL: https://issues.apache.org/jira/browse/DERBY-4736
> Project: Derby
> Issue Type: Sub-task
> Components: SQL
> Affects Versions: 10.7.0.0
> Reporter: Dag H. Wanvik
> Assignee: Dag H. Wanvik
> Attachments: derby-4736-1a.diff, derby-4736-1a.stat
>
>
> From schema given in DERBY-4712, this query gives an ASSERT with sane Derby:
> rs = s.executeQuery("SELECT 1 FROM (T0 LEFT JOIN (T1 LEFT JOIN (T2 LEFT JOIN " +
> " (T3 LEFT JOIN T4 ON 1=1) ON T2.X = T3.X) ON 1=1) ON 1=1) " +
> " LEFT JOIN " +
> " (T5 INNER JOIN T6 ON 1=1) " +
> " ON T2.X = 1 ");
> Cf the attachments in DERBY-4712 assert-bind-opt-trees.*.
> From preliminary analysis, this error seems to be unrelated to the NPEs reported in DERBY-4712, so filing this as a sub-issue.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Issue Comment Edited: (DERBY-4736) ASSERT FAIL when code
generating a column reference in a join predicate in presence of other
outer join reordering
Posted by "Dag H. Wanvik (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4736?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12886105#action_12886105 ]
Dag H. Wanvik edited comment on DERBY-4736 at 8/20/10 11:17 AM:
----------------------------------------------------------------
This smaller part of the query gives a wrong result, too, unless I comment out the reorder logic:
SELECT count(*) FROM (T2 LEFT JOIN (T3 left outer JOIN T4 ON 1=1) ON T2.X = T3.X)
returns 32 rows, but should return 20, and does if I comment out the reorder logic. So does PostgreSQL. Manually rewriting this query to be left-deep gives 32 rows with both Derby and PostgreSQL. DERBY-4471?
(Answer: yes. Edited dhw 2010-08-20)
was (Author: dagw):
This smaller part of the query gives a wrong result, too, unless I comment out the reorder logic:
SELECT count(*) FROM (T2 LEFT JOIN (T3 left outer JOIN T4 ON 1=1) ON T2.X = T3.X)
returns 32 rows, but should return 20, and does if I comment out the reorder logic. So does PostgreSQL. Manually rewriting this query to be left-deep gives 32 rows with both Derby and PostgreSQL. DERBY-4471?
> ASSERT FAIL when code generating a column reference in a join predicate in presence of other outer join reordering
> ------------------------------------------------------------------------------------------------------------------
>
> Key: DERBY-4736
> URL: https://issues.apache.org/jira/browse/DERBY-4736
> Project: Derby
> Issue Type: Sub-task
> Components: SQL
> Affects Versions: 10.7.0.0
> Reporter: Dag H. Wanvik
> Assignee: Dag H. Wanvik
> Fix For: 10.7.0.0
>
> Attachments: derby-4736-1a.diff, derby-4736-1a.stat, derby-4736-1b.diff, derby-4736-1b.stat, derby-4736-1c.diff, derby-4736-1c.stat, derby-4736-1d.diff, derby-4736-1d.stat, loj-analysis.txt, query_plan_derby_4736.pdf
>
>
> From schema given in DERBY-4712, this query gives an ASSERT with sane Derby:
> rs = s.executeQuery("SELECT 1 FROM (T0 LEFT JOIN (T1 LEFT JOIN (T2 LEFT JOIN " +
> " (T3 LEFT JOIN T4 ON 1=1) ON T2.X = T3.X) ON 1=1) ON 1=1) " +
> " LEFT JOIN " +
> " (T5 INNER JOIN T6 ON 1=1) " +
> " ON T2.X = 1 ");
> Cf the attachments in DERBY-4712 assert-bind-opt-trees.*.
> From preliminary analysis, this error seems to be unrelated to the NPEs reported in DERBY-4712, so filing this as a sub-issue.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Updated: (DERBY-4736) ASSERT FAIL when code generating a
column reference in a join predicate in presence of other outer join
reordering
Posted by "Dag H. Wanvik (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4736?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Dag H. Wanvik updated DERBY-4736:
---------------------------------
Issue & fix info: [Patch Available, Repro attached] (was: [Repro attached])
> ASSERT FAIL when code generating a column reference in a join predicate in presence of other outer join reordering
> ------------------------------------------------------------------------------------------------------------------
>
> Key: DERBY-4736
> URL: https://issues.apache.org/jira/browse/DERBY-4736
> Project: Derby
> Issue Type: Sub-task
> Components: SQL
> Affects Versions: 10.7.0.0
> Reporter: Dag H. Wanvik
> Assignee: Dag H. Wanvik
> Attachments: derby-4736-1a.diff, derby-4736-1a.stat
>
>
> From schema given in DERBY-4712, this query gives an ASSERT with sane Derby:
> rs = s.executeQuery("SELECT 1 FROM (T0 LEFT JOIN (T1 LEFT JOIN (T2 LEFT JOIN " +
> " (T3 LEFT JOIN T4 ON 1=1) ON T2.X = T3.X) ON 1=1) ON 1=1) " +
> " LEFT JOIN " +
> " (T5 INNER JOIN T6 ON 1=1) " +
> " ON T2.X = 1 ");
> Cf the attachments in DERBY-4712 assert-bind-opt-trees.*.
> From preliminary analysis, this error seems to be unrelated to the NPEs reported in DERBY-4712, so filing this as a sub-issue.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Closed: (DERBY-4736) ASSERT FAIL when code generating a
column reference in a join predicate in presence of other outer join
reordering
Posted by "Dag H. Wanvik (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4736?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Dag H. Wanvik closed DERBY-4736.
--------------------------------
Resolution: Fixed
Committed follow-up patch as snv 989099, closing again.
> ASSERT FAIL when code generating a column reference in a join predicate in presence of other outer join reordering
> ------------------------------------------------------------------------------------------------------------------
>
> Key: DERBY-4736
> URL: https://issues.apache.org/jira/browse/DERBY-4736
> Project: Derby
> Issue Type: Sub-task
> Components: SQL
> Affects Versions: 10.0.2.0, 10.0.2.1, 10.1.1.0, 10.1.2.1, 10.1.3.1, 10.2.1.6, 10.2.2.0, 10.3.1.4, 10.3.2.1, 10.3.3.0, 10.4.1.3, 10.4.2.0, 10.5.1.1, 10.5.2.0, 10.5.3.0, 10.6.1.0
> Reporter: Dag H. Wanvik
> Assignee: Dag H. Wanvik
> Fix For: 10.7.0.0
>
> Attachments: derby-4736-1a.diff, derby-4736-1a.stat, derby-4736-1b.diff, derby-4736-1b.stat, derby-4736-1c.diff, derby-4736-1c.stat, derby-4736-1d.diff, derby-4736-1d.stat, derby-4736-followup-a.diff, derby-4736-followup-a.stat, loj-analysis.txt, query_plan_derby_4736.pdf
>
>
> From schema given in DERBY-4712, this query gives an ASSERT with sane Derby:
> rs = s.executeQuery("SELECT 1 FROM (T0 LEFT JOIN (T1 LEFT JOIN (T2 LEFT JOIN " +
> " (T3 LEFT JOIN T4 ON 1=1) ON T2.X = T3.X) ON 1=1) ON 1=1) " +
> " LEFT JOIN " +
> " (T5 INNER JOIN T6 ON 1=1) " +
> " ON T2.X = 1 ");
> Cf the attachments in DERBY-4712 assert-bind-opt-trees.*.
> From preliminary analysis, this error seems to be unrelated to the NPEs reported in DERBY-4712, so filing this as a sub-issue.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.