[
https://issues.apache.org/jira/browse/DERBY-6267?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Rick Hillegas updated DERBY-6267:
---------------------------------
Attachment: derby-6267-02-aa-moreTests.diff
Attaching derby-6267-02-aa-moreTests.diff. This patch adds more test cases for
these overrides. Some changes to override processing are made as well. I will
run full regression tests.
This patch makes the following changes to the product:
1) Moves the check for the number of overriding row sources from bind() time to
optimize() time. This is necessary because the first phase of optimization (the
preprocessing phase) may flatten a subquery into an outer query block. The
overriding plan needs to have the same number of row sources as the single,
flattened query block. By moving the check into the optimizer, we handle both
the flattened and unflattened cases. I see no value in building something fancy
to try to merge separate overriding plans bound to each original query block.
Let's keep this simple, as Bryan advises.
2) Makes the debug toXML() method of execution ResultSets also look for arrays
of child ResultSets. Originally, this method only looked for fields of type
ResultSet. Now this method looks for fields of type ResultSet[] and recurses
into them too. This makes the toXML() method do something more useful for
statements with multiple query blocks. That is because at execution time, the
inner query blocks are represented as an array of ResultSets.
3) Adds a toXML() override to GenericAggregateResultSet. This override skips
over a redundant child ResultSet. The redundant ResultSet makes the toXML()
output confusing.
Touches the following files:
----------------
M java/engine/org/apache/derby/iapi/sql/compile/OptimizerPlan.java
M java/engine/org/apache/derby/impl/sql/compile/SelectNode.java
M java/engine/org/apache/derby/impl/sql/compile/OptimizerImpl.java
Changes for (1).
----------------
M
java/engine/org/apache/derby/impl/sql/execute/BasicNoPutResultSetImpl.java
Changes for (2).
----------------
M
java/engine/org/apache/derby/impl/sql/execute/GenericAggregateResultSet.java
Changes for (3).
----------------
M
java/testing/org/apache/derbyTesting/functionTests/tests/lang/NewOptimizerOverridesTest.java
New test cases.
> Add ability to compactly specify a complete query plan in an optimizer
> override.
> --------------------------------------------------------------------------------
>
> Key: DERBY-6267
> URL: https://issues.apache.org/jira/browse/DERBY-6267
> Project: Derby
> Issue Type: Improvement
> Components: SQL
> Affects Versions: 10.11.0.0
> Reporter: Rick Hillegas
> Labels: derby_triage10_11
> Attachments: derby-6267-01-ac-compactSyntax.diff,
> derby-6267-01-ad-compactSyntax.diff, derby-6267-01-ae-compactSyntax.diff,
> derby-6267-02-aa-moreTests.diff
>
>
> It would be nice to be able to override the optimizer's choice and specify a
> complete query plan using the compact summary syntax output by XMLOptTrace.
> Given how the optimizer handles a statement, this would require binding a
> query plan at the query block level. Two obvious candidates for such a
> feature are:
> 1) Extend the use of DERBY-PROPERTIES in the comments of a query.
> 2) Add an extra clause to query blocks. The clause would have to be a clearly
> marked Derby extension.
> (1) might look like this (here we add a new "fullQueryPlan" property):
> select tablename from sys.systables t, sys.syscolumns c, sys.sysaliases a
> where t.tablename = c.columnname and c.columnname = a.alias
> -- DERBY-PROPERTIES fullQueryPlan = (SYSCOLUMNS_HEAP # SYSALIASES_INDEX1) #
> SYSTABLES_INDEX1
> union all
> select tablename from sys.systables t, sys.syscolumns c, sys.sysaliases a,
> sys.syssequences s
> where t.tablename = c.columnname and c.columnname = a.alias and a.alias =
> s.sequencename
> -- DERBY-PROPERTIES fullQueryPlan = ((SYSCOLUMNS_HEAP # SYSTABLES_INDEX1) #
> SYSALIASES_INDEX1) # SYSSEQUENCES_INDEX2
> ;
> (2) might look like this (here we add a new "using derby join order" clause):
> select tablename from sys.systables t, sys.syscolumns c, sys.sysaliases a
> where t.tablename = c.columnname and c.columnname = a.alias
> using derby join order (SYSCOLUMNS_HEAP # SYSALIASES_INDEX1) #
> SYSTABLES_INDEX1
> union all
> select tablename from sys.systables t, sys.syscolumns c, sys.sysaliases a,
> sys.syssequences s
> where t.tablename = c.columnname and c.columnname = a.alias and a.alias =
> s.sequencename
> using derby join order ((SYSCOLUMNS_HEAP # SYSTABLES_INDEX1) #
> SYSALIASES_INDEX1) # SYSSEQUENCES_INDEX2
> ;
> Here's a comparison of these approaches:
> (1)
> + Portability: the same query text can be used against different RDBMSes.
> - Parsing of DERBY-PROPERTIES happens outside the grammer.
> (2)
> + Parsing happens in the parser.
> - Not portable.
> I slightly prefer approach (1). If I pursue that approach, I would like to
> see if I can move the parsing into the parser.
> I am interested in other opinions about how to address this feature. Thanks.
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators
For more information on JIRA, see: http://www.atlassian.com/software/jira
