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 "Rick Hillegas (JIRA)" <ji...@apache.org> on 2007/10/19 20:25:50 UTC
[jira] Created: (DERBY-3138) Optimizer error when vtis are invoked
with correlated references to outer query blocks
Optimizer error when vtis are invoked with correlated references to outer query blocks
--------------------------------------------------------------------------------------
Key: DERBY-3138
URL: https://issues.apache.org/jira/browse/DERBY-3138
Project: Derby
Issue Type: Bug
Components: SQL
Affects Versions: 10.4.0.0
Reporter: Rick Hillegas
The following query works fine in 10.3, but raises an optimizer error in the mainline:
select s.schemaname, t.tableName
from sys.sysschemas s, sys.systables t
where t.schemaid=s.schemaid
and exists
(
select vti.*
from table( syscs_diag.space_table( s.schemaname, t.tableName ) ) as vti
where vti.numfreepages > -1
)
;
In the mainline, it raises the following error:
ERROR 42Y69: No valid execution plan was found for this statement. This may have one of two causes: either you specified a hash join strategy when hash join is not allowed (no optimizable equijoin) or you are attempting to join two external virtual tables, each of which references the other, and so the statement cannot be evaluated.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-3138) Optimizer error when vtis are
invoked with correlated references to outer query blocks
Posted by "Rick Hillegas (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3138?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12536820 ]
Rick Hillegas commented on DERBY-3138:
--------------------------------------
Committed derby-3138-fix-aa.diff at subversion revision 587230.
> Optimizer error when vtis are invoked with correlated references to outer query blocks
> --------------------------------------------------------------------------------------
>
> Key: DERBY-3138
> URL: https://issues.apache.org/jira/browse/DERBY-3138
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.4.0.0
> Reporter: Rick Hillegas
> Assignee: Rick Hillegas
> Attachments: derby-3138-fix-aa.diff
>
>
> The following query works fine in 10.3, but raises an optimizer error in the mainline:
> select s.schemaname, t.tableName
> from sys.sysschemas s, sys.systables t
> where t.schemaid=s.schemaid
> and exists
> (
> select vti.*
> from table( syscs_diag.space_table( s.schemaname, t.tableName ) ) as vti
> where vti.numfreepages > -1
> )
> ;
> In the mainline, it raises the following error:
> ERROR 42Y69: No valid execution plan was found for this statement. This may have one of two causes: either you specified a hash join strategy when hash join is not allowed (no optimizable equijoin) or you are attempting to join two external virtual tables, each of which references the other, and so the statement cannot be evaluated.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Updated: (DERBY-3138) Optimizer error when vtis are invoked
with correlated references to outer query blocks
Posted by "Rick Hillegas (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3138?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Rick Hillegas updated DERBY-3138:
---------------------------------
Derby Info: [Patch Available, Regression] (was: [Regression])
> Optimizer error when vtis are invoked with correlated references to outer query blocks
> --------------------------------------------------------------------------------------
>
> Key: DERBY-3138
> URL: https://issues.apache.org/jira/browse/DERBY-3138
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.4.0.0
> Reporter: Rick Hillegas
> Assignee: Rick Hillegas
> Attachments: derby-3138-fix-aa.diff
>
>
> The following query works fine in 10.3, but raises an optimizer error in the mainline:
> select s.schemaname, t.tableName
> from sys.sysschemas s, sys.systables t
> where t.schemaid=s.schemaid
> and exists
> (
> select vti.*
> from table( syscs_diag.space_table( s.schemaname, t.tableName ) ) as vti
> where vti.numfreepages > -1
> )
> ;
> In the mainline, it raises the following error:
> ERROR 42Y69: No valid execution plan was found for this statement. This may have one of two causes: either you specified a hash join strategy when hash join is not allowed (no optimizable equijoin) or you are attempting to join two external virtual tables, each of which references the other, and so the statement cannot be evaluated.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Resolved: (DERBY-3138) Optimizer error when vtis are invoked
with correlated references to outer query blocks
Posted by "Rick Hillegas (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3138?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Rick Hillegas resolved DERBY-3138.
----------------------------------
Resolution: Fixed
> Optimizer error when vtis are invoked with correlated references to outer query blocks
> --------------------------------------------------------------------------------------
>
> Key: DERBY-3138
> URL: https://issues.apache.org/jira/browse/DERBY-3138
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.4.0.0
> Reporter: Rick Hillegas
> Assignee: Rick Hillegas
> Attachments: derby-3138-fix-aa.diff
>
>
> The following query works fine in 10.3, but raises an optimizer error in the mainline:
> select s.schemaname, t.tableName
> from sys.sysschemas s, sys.systables t
> where t.schemaid=s.schemaid
> and exists
> (
> select vti.*
> from table( syscs_diag.space_table( s.schemaname, t.tableName ) ) as vti
> where vti.numfreepages > -1
> )
> ;
> In the mainline, it raises the following error:
> ERROR 42Y69: No valid execution plan was found for this statement. This may have one of two causes: either you specified a hash join strategy when hash join is not allowed (no optimizable equijoin) or you are attempting to join two external virtual tables, each of which references the other, and so the statement cannot be evaluated.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Closed: (DERBY-3138) Optimizer error when vtis are invoked
with correlated references to outer query blocks
Posted by "Dyre Tjeldvoll (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3138?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Dyre Tjeldvoll closed DERBY-3138.
---------------------------------
Committed & resolved in October
> Optimizer error when vtis are invoked with correlated references to outer query blocks
> --------------------------------------------------------------------------------------
>
> Key: DERBY-3138
> URL: https://issues.apache.org/jira/browse/DERBY-3138
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.4.0.0
> Reporter: Rick Hillegas
> Assignee: Rick Hillegas
> Attachments: derby-3138-fix-aa.diff
>
>
> The following query works fine in 10.3, but raises an optimizer error in the mainline:
> select s.schemaname, t.tableName
> from sys.sysschemas s, sys.systables t
> where t.schemaid=s.schemaid
> and exists
> (
> select vti.*
> from table( syscs_diag.space_table( s.schemaname, t.tableName ) ) as vti
> where vti.numfreepages > -1
> )
> ;
> In the mainline, it raises the following error:
> ERROR 42Y69: No valid execution plan was found for this statement. This may have one of two causes: either you specified a hash join strategy when hash join is not allowed (no optimizable equijoin) or you are attempting to join two external virtual tables, each of which references the other, and so the statement cannot be evaluated.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Assigned: (DERBY-3138) Optimizer error when vtis are invoked
with correlated references to outer query blocks
Posted by "Rick Hillegas (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3138?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Rick Hillegas reassigned DERBY-3138:
------------------------------------
Assignee: Rick Hillegas
> Optimizer error when vtis are invoked with correlated references to outer query blocks
> --------------------------------------------------------------------------------------
>
> Key: DERBY-3138
> URL: https://issues.apache.org/jira/browse/DERBY-3138
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.4.0.0
> Reporter: Rick Hillegas
> Assignee: Rick Hillegas
>
> The following query works fine in 10.3, but raises an optimizer error in the mainline:
> select s.schemaname, t.tableName
> from sys.sysschemas s, sys.systables t
> where t.schemaid=s.schemaid
> and exists
> (
> select vti.*
> from table( syscs_diag.space_table( s.schemaname, t.tableName ) ) as vti
> where vti.numfreepages > -1
> )
> ;
> In the mainline, it raises the following error:
> ERROR 42Y69: No valid execution plan was found for this statement. This may have one of two causes: either you specified a hash join strategy when hash join is not allowed (no optimizable equijoin) or you are attempting to join two external virtual tables, each of which references the other, and so the statement cannot be evaluated.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Updated: (DERBY-3138) Optimizer error when vtis are invoked
with correlated references to outer query blocks
Posted by "Rick Hillegas (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3138?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Rick Hillegas updated DERBY-3138:
---------------------------------
Attachment: derby-3138-fix-aa.diff
Attaching a fix for this bug, derby-31380-fix-aa.diff. Touches the following files:
M java/engine/org/apache/derby/impl/sql/compile/MethodCallNode.java
This is the fix. The wrong overload of accept() was being called during preprocessing. When submission 555032 moved getCorrelationTables() out of NewInvocationNode into its superclass, MethodCallNode, the call to accept() should have been adjusted from super.accept() to just accept().
M java/testing/org/apache/derbyTesting/functionTests/tests/lang/SysDiagVTIMappingTest.java
Regression test case for this bug.
> Optimizer error when vtis are invoked with correlated references to outer query blocks
> --------------------------------------------------------------------------------------
>
> Key: DERBY-3138
> URL: https://issues.apache.org/jira/browse/DERBY-3138
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.4.0.0
> Reporter: Rick Hillegas
> Assignee: Rick Hillegas
> Attachments: derby-3138-fix-aa.diff
>
>
> The following query works fine in 10.3, but raises an optimizer error in the mainline:
> select s.schemaname, t.tableName
> from sys.sysschemas s, sys.systables t
> where t.schemaid=s.schemaid
> and exists
> (
> select vti.*
> from table( syscs_diag.space_table( s.schemaname, t.tableName ) ) as vti
> where vti.numfreepages > -1
> )
> ;
> In the mainline, it raises the following error:
> ERROR 42Y69: No valid execution plan was found for this statement. This may have one of two causes: either you specified a hash join strategy when hash join is not allowed (no optimizable equijoin) or you are attempting to join two external virtual tables, each of which references the other, and so the statement cannot be evaluated.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Updated: (DERBY-3138) Optimizer error when vtis are invoked
with correlated references to outer query blocks
Posted by "Rick Hillegas (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3138?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Rick Hillegas updated DERBY-3138:
---------------------------------
Derby Info: [Regression] (was: [Regression, Patch Available])
> Optimizer error when vtis are invoked with correlated references to outer query blocks
> --------------------------------------------------------------------------------------
>
> Key: DERBY-3138
> URL: https://issues.apache.org/jira/browse/DERBY-3138
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.4.0.0
> Reporter: Rick Hillegas
> Assignee: Rick Hillegas
> Attachments: derby-3138-fix-aa.diff
>
>
> The following query works fine in 10.3, but raises an optimizer error in the mainline:
> select s.schemaname, t.tableName
> from sys.sysschemas s, sys.systables t
> where t.schemaid=s.schemaid
> and exists
> (
> select vti.*
> from table( syscs_diag.space_table( s.schemaname, t.tableName ) ) as vti
> where vti.numfreepages > -1
> )
> ;
> In the mainline, it raises the following error:
> ERROR 42Y69: No valid execution plan was found for this statement. This may have one of two causes: either you specified a hash join strategy when hash join is not allowed (no optimizable equijoin) or you are attempting to join two external virtual tables, each of which references the other, and so the statement cannot be evaluated.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Updated: (DERBY-3138) Optimizer error when vtis are invoked
with correlated references to outer query blocks
Posted by "Dyre Tjeldvoll (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3138?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Dyre Tjeldvoll updated DERBY-3138:
----------------------------------
Fix Version/s: 10.4.0.0
This issue has no fix version, but has been checked in to trunk after the 10.3 branch was cut, and has not been merged to the 10.3 branch (before the last release).
(Some have been checked into trunk after 10.4 was cut and then merged to 10.4).
> Optimizer error when vtis are invoked with correlated references to outer query blocks
> --------------------------------------------------------------------------------------
>
> Key: DERBY-3138
> URL: https://issues.apache.org/jira/browse/DERBY-3138
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.4.0.0
> Reporter: Rick Hillegas
> Assignee: Rick Hillegas
> Fix For: 10.4.0.0
>
> Attachments: derby-3138-fix-aa.diff
>
>
> The following query works fine in 10.3, but raises an optimizer error in the mainline:
> select s.schemaname, t.tableName
> from sys.sysschemas s, sys.systables t
> where t.schemaid=s.schemaid
> and exists
> (
> select vti.*
> from table( syscs_diag.space_table( s.schemaname, t.tableName ) ) as vti
> where vti.numfreepages > -1
> )
> ;
> In the mainline, it raises the following error:
> ERROR 42Y69: No valid execution plan was found for this statement. This may have one of two causes: either you specified a hash join strategy when hash join is not allowed (no optimizable equijoin) or you are attempting to join two external virtual tables, each of which references the other, and so the statement cannot be evaluated.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.