4:16 p.m.
Author: shawkins
Date: 2010-06-23 17:16:17 -0400 (Wed, 23 Jun 2010)
New Revision: 2291
Added:
trunk/engine/src/test/java/org/teiid/query/processor/TestOrderByProcessing.java
Modified:
trunk/api/src/main/java/org/teiid/language/SQLConstants.java
trunk/api/src/main/java/org/teiid/language/SortSpecification.java
trunk/api/src/main/java/org/teiid/language/visitor/SQLStringVisitor.java
trunk/api/src/main/java/org/teiid/translator/ExecutionFactory.java
trunk/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/JDBCExecutionFactory.java
trunk/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/SQLConversionVisitor.java
trunk/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/derby/DerbyExecutionFactory.java
trunk/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/mm/MetaMatrixExecutionFactory.java
trunk/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/oracle/OracleExecutionFactory.java
trunk/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/postgresql/PostgreSQLExecutionFactory.java
trunk/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/teiid/TeiidExecutionFactory.java
trunk/connectors/translator-jdbc/src/test/java/org/teiid/translator/jdbc/oracle/TestOracleTranslator.java
trunk/documentation/developer-guide/src/main/docbook/en-US/content/translator-api.xml
trunk/documentation/reference/src/main/docbook/en-US/content/grammar.xml
trunk/documentation/reference/src/main/docbook/en-US/content/sql_support.xml
trunk/engine/src/main/java/org/teiid/dqp/internal/datamgr/impl/CapabilitiesConverter.java
trunk/engine/src/main/java/org/teiid/dqp/internal/datamgr/language/LanguageBridgeFactory.java
trunk/engine/src/main/java/org/teiid/query/optimizer/capabilities/SourceCapabilities.java
trunk/engine/src/main/java/org/teiid/query/optimizer/relational/PlanToProcessConverter.java
trunk/engine/src/main/java/org/teiid/query/optimizer/relational/rules/CapabilitiesUtil.java
trunk/engine/src/main/java/org/teiid/query/optimizer/relational/rules/RuleRaiseAccess.java
trunk/engine/src/main/java/org/teiid/query/processor/relational/GroupingNode.java
trunk/engine/src/main/java/org/teiid/query/processor/relational/ListNestedSortComparator.java
trunk/engine/src/main/java/org/teiid/query/processor/relational/SortNode.java
trunk/engine/src/main/java/org/teiid/query/processor/relational/SortUtility.java
trunk/engine/src/main/java/org/teiid/query/processor/relational/SortingFilter.java
trunk/engine/src/main/java/org/teiid/query/sql/lang/OrderBy.java
trunk/engine/src/main/java/org/teiid/query/sql/lang/OrderByItem.java
trunk/engine/src/main/java/org/teiid/query/sql/visitor/SQLStringVisitor.java
trunk/engine/src/main/javacc/org/teiid/query/parser/SQLParser.jj
trunk/engine/src/test/java/org/teiid/query/optimizer/TestRuleMergeVirtual.java
trunk/engine/src/test/java/org/teiid/query/parser/TestParser.java
trunk/engine/src/test/java/org/teiid/query/processor/TestProcessor.java
trunk/engine/src/test/java/org/teiid/query/processor/relational/TestDuplicateFilter.java
trunk/engine/src/test/java/org/teiid/query/processor/relational/TestSortNode.java
trunk/test-integration/common/src/test/java/org/teiid/connector/visitor/util/TestSQLStringVisitor.java
trunk/test-integration/common/src/test/java/org/teiid/dqp/internal/process/TestTPCR.java
Log:
TEIID-715 adding support for null ordering.
Modified: trunk/api/src/main/java/org/teiid/language/SQLConstants.java
===================================================================
--- trunk/api/src/main/java/org/teiid/language/SQLConstants.java 2010-06-23 20:58:24 UTC
(rev 2290)
+++ trunk/api/src/main/java/org/teiid/language/SQLConstants.java 2010-06-23 21:16:17 UTC
(rev 2291)
@@ -92,6 +92,10 @@
public static final String STDDEV_SAMP = "STDDEV_SAMP"; //$NON-NLS-1$
public static final String VAR_SAMP = "VAR_SAMP"; //$NON-NLS-1$
public static final String VAR_POP = "VAR_POP"; //$NON-NLS-1$
+
+ public static final String NULLS = "NULLS"; //$NON-NLS-1$
+ public static final String FIRST = "FIRST"; //$NON-NLS-1$
+ public static final String LAST = "LAST"; //$NON-NLS-1$
}
public interface Reserved {
Modified: trunk/api/src/main/java/org/teiid/language/SortSpecification.java
===================================================================
--- trunk/api/src/main/java/org/teiid/language/SortSpecification.java 2010-06-23 20:58:24
UTC (rev 2290)
+++ trunk/api/src/main/java/org/teiid/language/SortSpecification.java 2010-06-23 21:16:17
UTC (rev 2291)
@@ -31,8 +31,14 @@
DESC
}
+ public enum NullOrdering {
+ FIRST,
+ LAST
+ }
+
private Ordering ordering;
- private Expression expression;
+ private Expression expression;
+ private NullOrdering nullOrdering;
public SortSpecification(Ordering direction, Expression expression) {
this.ordering = direction;
@@ -58,5 +64,13 @@
public void setExpression(Expression expression) {
this.expression = expression;
}
+
+ public void setNullOrdering(NullOrdering nullOrdering) {
+ this.nullOrdering = nullOrdering;
+ }
+
+ public NullOrdering getNullOrdering() {
+ return nullOrdering;
+ }
}
Modified: trunk/api/src/main/java/org/teiid/language/visitor/SQLStringVisitor.java
===================================================================
--- trunk/api/src/main/java/org/teiid/language/visitor/SQLStringVisitor.java 2010-06-23
20:58:24 UTC (rev 2290)
+++ trunk/api/src/main/java/org/teiid/language/visitor/SQLStringVisitor.java 2010-06-23
21:16:17 UTC (rev 2291)
@@ -650,6 +650,12 @@
buffer.append(Tokens.SPACE)
.append(DESC);
} // Don't print default "ASC"
+ if (obj.getNullOrdering() != null) {
+ buffer.append(Tokens.SPACE)
+ .append(NonReserved.NULLS)
+ .append(Tokens.SPACE)
+ .append(obj.getNullOrdering().name());
+ }
}
public void visit(Argument obj) {
Modified: trunk/api/src/main/java/org/teiid/translator/ExecutionFactory.java
===================================================================
--- trunk/api/src/main/java/org/teiid/translator/ExecutionFactory.java 2010-06-23 20:58:24
UTC (rev 2290)
+++ trunk/api/src/main/java/org/teiid/translator/ExecutionFactory.java 2010-06-23 21:16:17
UTC (rev 2291)
@@ -71,6 +71,14 @@
*/
KEY
}
+
+ public enum NullOrder {
+ HIGH,
+ LOW,
+ FIRST,
+ LAST,
+ UNKNOWN
+ }
public static final int DEFAULT_MAX_FROM_GROUPS = -1;
public static final int DEFAULT_MAX_IN_CRITERIA_SIZE = -1;
@@ -463,6 +471,24 @@
}
/**
+ * Returns the default null ordering
+ * @since 7.1
+ * @return the {@link NullOrder}
+ */
+ public NullOrder getDefaultNullOrder() {
+ return NullOrder.UNKNOWN;
+ }
+
+ /**
+ * Returns whether the database supports explicit join ordering.
+ * @since 7.1
+ * @return true if nulls high|low can be specified
+ */
+ public boolean supportsOrderByNullOrdering() {
+ return false;
+ }
+
+ /**
* Whether the source supports an explicit GROUP BY clause
* @since 6.1
*/
Modified:
trunk/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/JDBCExecutionFactory.java
===================================================================
---
trunk/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/JDBCExecutionFactory.java 2010-06-23
20:58:24 UTC (rev 2290)
+++
trunk/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/JDBCExecutionFactory.java 2010-06-23
21:16:17 UTC (rev 2291)
@@ -81,13 +81,6 @@
public static final int DEFAULT_MAX_IN_CRITERIA = 1000;
- public enum NullOrder {
- HIGH,
- LOW,
- FIRST,
- LAST
- }
-
// Because the retrieveValue() method will be hit for every value of
// every JDBC result set returned, we do lots of weird special stuff here
// to improve the performance (most importantly to remove big if/else checks
@@ -1056,23 +1049,12 @@
return false;
}
- /**
- * Returns the default null ordering
- * @return the {@link NullOrder}
- */
+ @Override
public NullOrder getDefaultNullOrder() {
return NullOrder.LOW;
}
/**
- * Returns whether the database supports explicit join ordering.
- * @return true if nulls high|low can be specified
- */
- public boolean supportsExplicitNullOrdering() {
- return false;
- }
-
- /**
* Returns whether the limit clause is applied to the select clause.
* @return true if the limit clause is part of the select
*/
Modified:
trunk/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/SQLConversionVisitor.java
===================================================================
---
trunk/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/SQLConversionVisitor.java 2010-06-23
20:58:24 UTC (rev 2290)
+++
trunk/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/SQLConversionVisitor.java 2010-06-23
21:16:17 UTC (rev 2291)
@@ -47,16 +47,13 @@
import org.teiid.language.Literal;
import org.teiid.language.SearchedCase;
import org.teiid.language.SetClause;
-import org.teiid.language.SortSpecification;
import org.teiid.language.Argument.Direction;
import org.teiid.language.SQLConstants.Reserved;
import org.teiid.language.SQLConstants.Tokens;
import org.teiid.language.SetQuery.Operation;
-import org.teiid.language.SortSpecification.Ordering;
import org.teiid.language.visitor.SQLStringVisitor;
import org.teiid.translator.ExecutionContext;
import org.teiid.translator.TypeFacility;
-import org.teiid.translator.jdbc.JDBCExecutionFactory.NullOrder;
/**
@@ -131,22 +128,6 @@
this.replaceWithBinding = replacementMode;
}
- @Override
- public void visit(SortSpecification obj) {
- super.visit(obj);
- NullOrder nullOrder = this.executionFactory.getDefaultNullOrder();
- if (!this.executionFactory.supportsExplicitNullOrdering() || nullOrder ==
NullOrder.LOW) {
- return;
- }
- if (obj.getOrdering() == Ordering.ASC) {
- if (nullOrder != NullOrder.FIRST) {
- buffer.append(" NULLS FIRST"); //$NON-NLS-1$
- }
- } else if (nullOrder == NullOrder.FIRST) {
- buffer.append(" NULLS LAST"); //$NON-NLS-1$
- }
- }
-
/**
* @param type
* @param object
Modified:
trunk/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/derby/DerbyExecutionFactory.java
===================================================================
---
trunk/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/derby/DerbyExecutionFactory.java 2010-06-23
20:58:24 UTC (rev 2290)
+++
trunk/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/derby/DerbyExecutionFactory.java 2010-06-23
21:16:17 UTC (rev 2291)
@@ -66,7 +66,7 @@
}
@Override
- public boolean supportsExplicitNullOrdering() {
+ public boolean supportsOrderByNullOrdering() {
return getDatabaseVersion().compareTo(TEN_4) >= 0;
}
Modified:
trunk/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/mm/MetaMatrixExecutionFactory.java
===================================================================
---
trunk/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/mm/MetaMatrixExecutionFactory.java 2010-06-23
20:58:24 UTC (rev 2290)
+++
trunk/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/mm/MetaMatrixExecutionFactory.java 2010-06-23
21:16:17 UTC (rev 2291)
@@ -129,4 +129,9 @@
public boolean supportsRowOffset() {
return true;
}
+
+ @Override
+ public NullOrder getDefaultNullOrder() {
+ return NullOrder.UNKNOWN;
+ }
}
\ No newline at end of file
Modified:
trunk/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/oracle/OracleExecutionFactory.java
===================================================================
---
trunk/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/oracle/OracleExecutionFactory.java 2010-06-23
20:58:24 UTC (rev 2290)
+++
trunk/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/oracle/OracleExecutionFactory.java 2010-06-23
21:16:17 UTC (rev 2291)
@@ -435,7 +435,7 @@
}
@Override
- public boolean supportsExplicitNullOrdering() {
+ public boolean supportsOrderByNullOrdering() {
return true;
}
Modified:
trunk/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/postgresql/PostgreSQLExecutionFactory.java
===================================================================
---
trunk/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/postgresql/PostgreSQLExecutionFactory.java 2010-06-23
20:58:24 UTC (rev 2290)
+++
trunk/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/postgresql/PostgreSQLExecutionFactory.java 2010-06-23
21:16:17 UTC (rev 2291)
@@ -222,7 +222,7 @@
}
@Override
- public boolean supportsExplicitNullOrdering() {
+ public boolean supportsOrderByNullOrdering() {
return getDatabaseVersion().compareTo(EIGHT_4) >= 0;
}
Modified:
trunk/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/teiid/TeiidExecutionFactory.java
===================================================================
---
trunk/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/teiid/TeiidExecutionFactory.java 2010-06-23
20:58:24 UTC (rev 2290)
+++
trunk/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/teiid/TeiidExecutionFactory.java 2010-06-23
21:16:17 UTC (rev 2291)
@@ -28,6 +28,7 @@
import java.util.List;
import org.teiid.translator.Translator;
+import org.teiid.translator.ExecutionFactory.NullOrder;
import org.teiid.translator.jdbc.JDBCExecutionFactory;
@@ -172,4 +173,9 @@
public boolean supportsAggregatesEnhancedNumeric() {
return getDatabaseVersion().compareTo(SEVEN_1) >= 0;
}
+
+ @Override
+ public NullOrder getDefaultNullOrder() {
+ return NullOrder.UNKNOWN;
+ }
}
Modified:
trunk/connectors/translator-jdbc/src/test/java/org/teiid/translator/jdbc/oracle/TestOracleTranslator.java
===================================================================
---
trunk/connectors/translator-jdbc/src/test/java/org/teiid/translator/jdbc/oracle/TestOracleTranslator.java 2010-06-23
20:58:24 UTC (rev 2290)
+++
trunk/connectors/translator-jdbc/src/test/java/org/teiid/translator/jdbc/oracle/TestOracleTranslator.java 2010-06-23
21:16:17 UTC (rev 2291)
@@ -301,7 +301,7 @@
}
@Test public void testUnionWithOrderBy() throws Exception {
String input = "SELECT IntKey FROM BQT1.SMALLA UNION SELECT IntKey FROM
BQT1.SMALLB ORDER BY IntKey"; //$NON-NLS-1$
- String output = "SELECT SmallA.IntKey FROM SmallA UNION SELECT SmallB.IntKey
FROM SmallB ORDER BY IntKey NULLS FIRST"; //$NON-NLS-1$
+ String output = "SELECT SmallA.IntKey FROM SmallA UNION SELECT SmallB.IntKey
FROM SmallB ORDER BY IntKey"; //$NON-NLS-1$
TranslationHelper.helpTestVisitor(TranslationHelper.BQT_VDB, UDF,
input, output,
@@ -580,7 +580,7 @@
helpTestVisitor(getTestVDB(),
"select part_id id FROM parts UNION ALL select part_name
FROM parts UNION ALL select part_id FROM parts ORDER BY id", //$NON-NLS-1$
null,
- "SELECT g_2.PART_ID AS c_0 FROM PARTS g_2 UNION ALL SELECT
g_1.PART_NAME AS c_0 FROM PARTS g_1 UNION ALL SELECT g_0.PART_ID AS c_0 FROM PARTS g_0
ORDER BY c_0 NULLS FIRST", //$NON-NLS-1$
+ "SELECT g_2.PART_ID AS c_0 FROM PARTS g_2 UNION ALL SELECT
g_1.PART_NAME AS c_0 FROM PARTS g_1 UNION ALL SELECT g_0.PART_ID AS c_0 FROM PARTS g_0
ORDER BY c_0", //$NON-NLS-1$
true);
}
@@ -588,7 +588,7 @@
helpTestVisitor(getTestVDB(),
"select part_id FROM parts UNION ALL select part_name FROM
parts ORDER BY part_id", //$NON-NLS-1$
null,
- "SELECT g_1.PART_ID AS c_0 FROM PARTS g_1 UNION ALL SELECT
g_0.PART_NAME AS c_0 FROM PARTS g_0 ORDER BY c_0 NULLS FIRST", //$NON-NLS-1$
+ "SELECT g_1.PART_ID AS c_0 FROM PARTS g_1 UNION ALL SELECT
g_0.PART_NAME AS c_0 FROM PARTS g_0 ORDER BY c_0", //$NON-NLS-1$
true);
}
@@ -596,7 +596,7 @@
helpTestVisitor(getTestVDB(),
"select part_id as p FROM parts UNION ALL select part_name
FROM parts ORDER BY p", //$NON-NLS-1$
null,
- "SELECT PARTS.PART_ID AS p FROM PARTS UNION ALL SELECT
PARTS.PART_NAME FROM PARTS ORDER BY p NULLS FIRST"); //$NON-NLS-1$
+ "SELECT PARTS.PART_ID AS p FROM PARTS UNION ALL SELECT
PARTS.PART_NAME FROM PARTS ORDER BY p"); //$NON-NLS-1$
}
@Test public void testUpdateWithFunction() throws Exception {
@@ -731,7 +731,7 @@
@Test public void testLimitWithNestedInlineView() throws Exception {
String input = "select max(intkey), stringkey from (select intkey, stringkey
from bqt1.smalla order by intkey limit 100) x group by stringkey"; //$NON-NLS-1$
- String output = "SELECT MAX(x.intkey), x.stringkey FROM (SELECT * FROM
(SELECT SmallA.IntKey, SmallA.StringKey FROM SmallA ORDER BY intkey NULLS FIRST) WHERE
ROWNUM <= 100) x GROUP BY x.stringkey"; //$NON-NLS-1$
+ String output = "SELECT MAX(x.intkey), x.stringkey FROM (SELECT * FROM
(SELECT SmallA.IntKey, SmallA.StringKey FROM SmallA ORDER BY intkey) WHERE ROWNUM <=
100) x GROUP BY x.stringkey"; //$NON-NLS-1$
helpTestVisitor(FakeMetadataFactory.exampleBQTCached(),
input,
@@ -761,7 +761,7 @@
@Test public void testRowLimitWithUnionOrderBy() throws Exception {
String input = "(select intkey from bqt1.smalla limit 50, 100) union select
intnum from bqt1.smalla order by intkey"; //$NON-NLS-1$
- String output = "SELECT c_0 FROM (SELECT VIEW_FOR_LIMIT.*, ROWNUM ROWNUM_
FROM (SELECT g_1.IntKey AS c_0 FROM SmallA g_1) VIEW_FOR_LIMIT WHERE ROWNUM <= 150)
WHERE ROWNUM_ > 50 UNION SELECT g_0.IntNum AS c_0 FROM SmallA g_0 ORDER BY c_0 NULLS
FIRST"; //$NON-NLS-1$
+ String output = "SELECT c_0 FROM (SELECT VIEW_FOR_LIMIT.*, ROWNUM ROWNUM_
FROM (SELECT g_1.IntKey AS c_0 FROM SmallA g_1) VIEW_FOR_LIMIT WHERE ROWNUM <= 150)
WHERE ROWNUM_ > 50 UNION SELECT g_0.IntNum AS c_0 FROM SmallA g_0 ORDER BY c_0";
//$NON-NLS-1$
CommandBuilder commandBuilder = new
CommandBuilder(FakeMetadataFactory.exampleBQTCached());
Command obj = commandBuilder.getCommand(input, true, true);
Modified:
trunk/documentation/developer-guide/src/main/docbook/en-US/content/translator-api.xml
===================================================================
---
trunk/documentation/developer-guide/src/main/docbook/en-US/content/translator-api.xml 2010-06-23
20:58:24 UTC (rev 2290)
+++
trunk/documentation/developer-guide/src/main/docbook/en-US/content/translator-api.xml 2010-06-23
21:16:17 UTC (rev 2291)
@@ -972,11 +972,22 @@
<para>OrderBy</para>
</entry>
<entry>
- <para>Translator can support the ORDER BY items that are not
directly specified in the select clause.</para>
+ <para>Translator can support ORDER BY items that are not directly
specified in the select clause.</para>
</entry>
</row>
<row>
<entry>
+ <para>OrderByNullOrdering</para>
+ </entry>
+ <entry>
+ <para>OrderBy</para>
+ </entry>
+ <entry>
+ <para>Translator can support ORDER BY items with NULLS
FIRST/LAST.</para>
+ </entry>
+ </row>
+ <row>
+ <entry>
<para>GroupBy</para>
</entry>
<entry>
@@ -1301,6 +1312,11 @@
update modes, the query engine will compensate by issuing the updates
individually.</para>
</sect3>
+ <sect3>
+ <title>Default Behavior</title>
+ <para>The method
<code>ExecutionFactory.getDefaultNullOrder()</code> specifies the default null
order. Can be one of UNKNOWN, LOW, HIGH, FIRST, LAST. This is only used if ORDER BY is
supported, but null ordering is not.</para>
+ </sect3>
+
</sect2>
</sect1>
Modified: trunk/documentation/reference/src/main/docbook/en-US/content/grammar.xml
===================================================================
--- trunk/documentation/reference/src/main/docbook/en-US/content/grammar.xml 2010-06-23
20:58:24 UTC (rev 2290)
+++ trunk/documentation/reference/src/main/docbook/en-US/content/grammar.xml 2010-06-23
21:16:17 UTC (rev 2291)
@@ -347,7 +347,6 @@
| <TIMETYPE: "{" "t">
| <TIMESTAMPTYPE: "{" "ts">
| <BOOLEANTYPE: "{" "b">
-| <XMLTYPE: "{" "x">
| <INTEGERVAL: (<MINUS>)? (<DIGIT>)+>
| <FLOATVAL: (<MINUS>)? (<DIGIT>)*
<PERIOD> (<DIGIT>)+ (["e","E"]
(["+","-"])? (<DIGIT>)+)?>
| <STRINGVAL: ("N")? "\'" ("\'\'" |
~["\'"])* "\'">
@@ -580,7 +579,7 @@
<row>
<entry align="right" valign="top"><para><anchor
id="prod57"
xreflabel="aggregateSymbol"/>aggregateSymbol</para></entry>
<entry align="left" valign="top"><para>::=
-( ( <link linkend="prod58">nonReserved</link>
<LPAREN> <STAR> <RPAREN> ) | ( <link
linkend="prod58">nonReserved</link> <LPAREN> (
<DISTINCT> )? <link linkend="prod16">expression</link>
<RPAREN> ) )</para></entry></row>
+( ( <link linkend="prod58">nonReserved</link>
<LPAREN> <STAR> <RPAREN> ) | ( ( <link
linkend="prod58">nonReserved</link> | <ANY> |
<SOME> ) <LPAREN> ( <DISTINCT> |
<ALL> )? <link linkend="prod16">expression</link>
<RPAREN> ) )</para></entry></row>
<row>
<entry align="right" valign="top"><para><anchor
id="prod49" xreflabel="from"/>from</para></entry>
<entry align="left" valign="top"><para>::=
@@ -588,65 +587,61 @@
<row>
<entry align="right" valign="top"><para><anchor
id="prod59"
xreflabel="tableReference"/>tableReference</para></entry>
<entry align="left" valign="top"><para>::=
-( ( <LBRACE> <link
linkend="prod58">nonReserved</link> <link
linkend="prod60">tableReferenceUnescaped</link> <RBRACE>
) | <link linkend="prod60">tableReferenceUnescaped</link>
)</para></entry></row>
+( ( <LBRACE> <link
linkend="prod58">nonReserved</link> <link
linkend="prod60">joinedTable</link> <RBRACE> ) | <link
linkend="prod60">joinedTable</link>
)</para></entry></row>
<row>
-<entry align="right" valign="top"><para><anchor
id="prod60"
xreflabel="tableReferenceUnescaped"/>tableReferenceUnescaped</para></entry>
+<entry align="right" valign="top"><para><anchor
id="prod60"
xreflabel="joinedTable"/>joinedTable</para></entry>
<entry align="left" valign="top"><para>::=
-( <link linkend="prod61">joinedTable</link> | <link
linkend="prod62">tablePrimary</link>
)</para></entry></row>
+<link linkend="prod61">tablePrimary</link> ( ( <link
linkend="prod62">crossJoin</link> | <link
linkend="prod63">qualifiedJoin</link> )
)*</para></entry></row>
<row>
-<entry align="right" valign="top"><para><anchor
id="prod61"
xreflabel="joinedTable"/>joinedTable</para></entry>
+<entry align="right" valign="top"><para><anchor
id="prod62"
xreflabel="crossJoin"/>crossJoin</para></entry>
<entry align="left" valign="top"><para>::=
-<link linkend="prod62">tablePrimary</link> ( ( <link
linkend="prod63">crossJoin</link> | <link
linkend="prod64">qualifiedJoin</link> )
)+</para></entry></row>
+( ( <CROSS> | <UNION> ) <JOIN> <link
linkend="prod61">tablePrimary</link>
)</para></entry></row>
<row>
-<entry align="right" valign="top"><para><anchor
id="prod63"
xreflabel="crossJoin"/>crossJoin</para></entry>
+<entry align="right" valign="top"><para><anchor
id="prod63"
xreflabel="qualifiedJoin"/>qualifiedJoin</para></entry>
<entry align="left" valign="top"><para>::=
-( ( <CROSS> | <UNION> ) <JOIN> <link
linkend="prod62">tablePrimary</link>
)</para></entry></row>
-<row>
-<entry align="right" valign="top"><para><anchor
id="prod64"
xreflabel="qualifiedJoin"/>qualifiedJoin</para></entry>
-<entry align="left" valign="top"><para>::=
( ( ( <RIGHT> ( <OUTER> )? ) | ( <LEFT> (
<OUTER> )? ) | ( <FULL> ( <OUTER> )? ) |
<INNER> )? <JOIN> <link
linkend="prod59">tableReference</link> <ON> <link
linkend="prod28">criteria</link>
)</para></entry></row>
<row>
-<entry align="right" valign="top"><para><anchor
id="prod62"
xreflabel="tablePrimary"/>tablePrimary</para></entry>
+<entry align="right" valign="top"><para><anchor
id="prod61"
xreflabel="tablePrimary"/>tablePrimary</para></entry>
<entry align="left" valign="top"><para>::=
-( <link linkend="prod65">textTable</link> | <link
linkend="prod66">xmlTable</link> | <link
linkend="prod67">unaryFromClause</link> | <link
linkend="prod68">subqueryFromClause</link> | ( <LPAREN>
<link linkend="prod61">joinedTable</link> <RPAREN> ) )
( ( <MAKEDEP> ) | ( <MAKENOTDEP> )
)?</para></entry></row>
+( <link linkend="prod64">textTable</link> | <link
linkend="prod65">xmlTable</link> | <link
linkend="prod66">unaryFromClause</link> | <link
linkend="prod67">subqueryFromClause</link> | ( <LPAREN>
<link linkend="prod60">joinedTable</link> <RPAREN> ) )
( ( <MAKEDEP> ) | ( <MAKENOTDEP> )
)?</para></entry></row>
<row>
-<entry align="right" valign="top"><para><anchor
id="prod69"
xreflabel="xmlSerialize"/>xmlSerialize</para></entry>
+<entry align="right" valign="top"><para><anchor
id="prod68"
xreflabel="xmlSerialize"/>xmlSerialize</para></entry>
<entry align="left" valign="top"><para>::=
-<XMLSERIALIZE> <LPAREN> <link
linkend="prod58">nonReserved</link> <link
linkend="prod16">expression</link> ( <AS> (
<STRING> | <VARCHAR> | <CLOB> ) )?
<RPAREN></para></entry></row>
+<XMLSERIALIZE> <LPAREN> ( <link
linkend="prod58">nonReserved</link> )? <link
linkend="prod16">expression</link> ( <AS> (
<STRING> | <VARCHAR> | <CLOB> ) )?
<RPAREN></para></entry></row>
<row>
<entry align="right" valign="top"><para><anchor
id="prod58"
xreflabel="nonReserved"/>nonReserved</para></entry>
<entry align="left" valign="top"><para>::=
<ID></para></entry></row>
<row>
-<entry align="right" valign="top"><para><anchor
id="prod65"
xreflabel="textTable"/>textTable</para></entry>
+<entry align="right" valign="top"><para><anchor
id="prod64"
xreflabel="textTable"/>textTable</para></entry>
<entry align="left" valign="top"><para>::=
-<ID> <LPAREN> <link
linkend="prod16">expression</link> <link
linkend="prod58">nonReserved</link> <link
linkend="prod70">textColumn</link> ( <COMMA> <link
linkend="prod70">textColumn</link> )* ( <ID> <link
linkend="prod71">charVal</link> )? ( ( <ESCAPE> <link
linkend="prod71">charVal</link> ) | ( <ID> <link
linkend="prod71">charVal</link> ) )? ( <ID> ( <link
linkend="prod72">intVal</link> )? )? ( <ID> <link
linkend="prod72">intVal</link> )? <RPAREN> (
<AS> )? <link
linkend="prod2">id</link></para></entry></row>
+<ID> <LPAREN> <link
linkend="prod16">expression</link> <link
linkend="prod58">nonReserved</link> <link
linkend="prod69">textColumn</link> ( <COMMA> <link
linkend="prod69">textColumn</link> )* ( <ID> <link
linkend="prod70">charVal</link> )? ( ( <ESCAPE> <link
linkend="prod70">charVal</link> ) | ( <ID> <link
linkend="prod70">charVal</link> ) )? ( <ID> ( <link
linkend="prod71">intVal</link> )? )? ( <ID> <link
linkend="prod71">intVal</link> )? <RPAREN> (
<AS> )? <link
linkend="prod2">id</link></para></entry></row>
<row>
-<entry align="right" valign="top"><para><anchor
id="prod70"
xreflabel="textColumn"/>textColumn</para></entry>
+<entry align="right" valign="top"><para><anchor
id="prod69"
xreflabel="textColumn"/>textColumn</para></entry>
<entry align="left" valign="top"><para>::=
-<link linkend="prod2">id</link> <link
linkend="prod31">dataType</link> ( <ID> <link
linkend="prod72">intVal</link>
)?</para></entry></row>
+<link linkend="prod2">id</link> <link
linkend="prod31">dataType</link> ( <ID> <link
linkend="prod71">intVal</link>
)?</para></entry></row>
<row>
-<entry align="right" valign="top"><para><anchor
id="prod73"
xreflabel="xmlQuery"/>xmlQuery</para></entry>
+<entry align="right" valign="top"><para><anchor
id="prod72"
xreflabel="xmlQuery"/>xmlQuery</para></entry>
<entry align="left" valign="top"><para>::=
-<XMLQUERY> <LPAREN> ( <link
linkend="prod74">xmlNamespaces</link> <COMMA> )? <link
linkend="prod1">stringVal</link> ( <ID> <link
linkend="prod55">derivedColumn</link> ( <COMMA> <link
linkend="prod55">derivedColumn</link> )* )? ( ( <NULL> |
<link linkend="prod58">nonReserved</link> ) <ON>
<link linkend="prod58">nonReserved</link> )?
<RPAREN></para></entry></row>
+<XMLQUERY> <LPAREN> ( <link
linkend="prod73">xmlNamespaces</link> <COMMA> )? <link
linkend="prod1">stringVal</link> ( <ID> <link
linkend="prod55">derivedColumn</link> ( <COMMA> <link
linkend="prod55">derivedColumn</link> )* )? ( ( <NULL> |
<link linkend="prod58">nonReserved</link> ) <ON>
<link linkend="prod58">nonReserved</link> )?
<RPAREN></para></entry></row>
<row>
-<entry align="right" valign="top"><para><anchor
id="prod66"
xreflabel="xmlTable"/>xmlTable</para></entry>
+<entry align="right" valign="top"><para><anchor
id="prod65"
xreflabel="xmlTable"/>xmlTable</para></entry>
<entry align="left" valign="top"><para>::=
-<XMLTABLE> <LPAREN> ( <link
linkend="prod74">xmlNamespaces</link> <COMMA> )? <link
linkend="prod1">stringVal</link> ( <ID> <link
linkend="prod55">derivedColumn</link> ( <COMMA> <link
linkend="prod55">derivedColumn</link> )* )? ( <ID>
<link linkend="prod75">xmlColumn</link> ( <COMMA>
<link linkend="prod75">xmlColumn</link> )* )? <RPAREN>
( <AS> )? <link
linkend="prod2">id</link></para></entry></row>
+<XMLTABLE> <LPAREN> ( <link
linkend="prod73">xmlNamespaces</link> <COMMA> )? <link
linkend="prod1">stringVal</link> ( <ID> <link
linkend="prod55">derivedColumn</link> ( <COMMA> <link
linkend="prod55">derivedColumn</link> )* )? ( <ID>
<link linkend="prod74">xmlColumn</link> ( <COMMA>
<link linkend="prod74">xmlColumn</link> )* )? <RPAREN>
( <AS> )? <link
linkend="prod2">id</link></para></entry></row>
<row>
-<entry align="right" valign="top"><para><anchor
id="prod75"
xreflabel="xmlColumn"/>xmlColumn</para></entry>
+<entry align="right" valign="top"><para><anchor
id="prod74"
xreflabel="xmlColumn"/>xmlColumn</para></entry>
<entry align="left" valign="top"><para>::=
<link linkend="prod2">id</link> ( ( <FOR> <link
linkend="prod58">nonReserved</link> ) | ( <link
linkend="prod31">dataType</link> ( <DEFAULT_KEYWORD>
<link linkend="prod16">expression</link> )? ( <link
linkend="prod58">nonReserved</link> <link
linkend="prod1">stringVal</link> )? )
)</para></entry></row>
<row>
-<entry align="right" valign="top"><para><anchor
id="prod72" xreflabel="intVal"/>intVal</para></entry>
+<entry align="right" valign="top"><para><anchor
id="prod71" xreflabel="intVal"/>intVal</para></entry>
<entry align="left" valign="top"><para>::=
<INTEGERVAL></para></entry></row>
<row>
-<entry align="right" valign="top"><para><anchor
id="prod68"
xreflabel="subqueryFromClause"/>subqueryFromClause</para></entry>
+<entry align="right" valign="top"><para><anchor
id="prod67"
xreflabel="subqueryFromClause"/>subqueryFromClause</para></entry>
<entry align="left" valign="top"><para>::=
( <TABLE> )? <LPAREN> ( <link
linkend="prod7">queryExpression</link> | <link
linkend="prod8">storedProcedure</link> ) <RPAREN> (
<AS> )? <link
linkend="prod2">id</link></para></entry></row>
<row>
-<entry align="right" valign="top"><para><anchor
id="prod67"
xreflabel="unaryFromClause"/>unaryFromClause</para></entry>
+<entry align="right" valign="top"><para><anchor
id="prod66"
xreflabel="unaryFromClause"/>unaryFromClause</para></entry>
<entry align="left" valign="top"><para>::=
( <ID> ( ( <AS> )? <link
linkend="prod2">id</link> )? )</para></entry></row>
<row>
@@ -656,69 +651,73 @@
<row>
<entry align="right" valign="top"><para><anchor
id="prod28"
xreflabel="criteria"/>criteria</para></entry>
<entry align="left" valign="top"><para>::=
-<link
linkend="prod76">compoundCritOr</link></para></entry></row>
+<link
linkend="prod75">compoundCritOr</link></para></entry></row>
<row>
-<entry align="right" valign="top"><para><anchor
id="prod76"
xreflabel="compoundCritOr"/>compoundCritOr</para></entry>
+<entry align="right" valign="top"><para><anchor
id="prod75"
xreflabel="compoundCritOr"/>compoundCritOr</para></entry>
<entry align="left" valign="top"><para>::=
-<link linkend="prod77">compoundCritAnd</link> ( <OR>
<link linkend="prod77">compoundCritAnd</link>
)*</para></entry></row>
+<link linkend="prod76">compoundCritAnd</link> ( <OR>
<link linkend="prod76">compoundCritAnd</link>
)*</para></entry></row>
<row>
-<entry align="right" valign="top"><para><anchor
id="prod77"
xreflabel="compoundCritAnd"/>compoundCritAnd</para></entry>
+<entry align="right" valign="top"><para><anchor
id="prod76"
xreflabel="compoundCritAnd"/>compoundCritAnd</para></entry>
<entry align="left" valign="top"><para>::=
-<link linkend="prod78">notCrit</link> ( <AND>
<link linkend="prod78">notCrit</link>
)*</para></entry></row>
+<link linkend="prod77">notCrit</link> ( <AND>
<link linkend="prod77">notCrit</link>
)*</para></entry></row>
<row>
-<entry align="right" valign="top"><para><anchor
id="prod78"
xreflabel="notCrit"/>notCrit</para></entry>
+<entry align="right" valign="top"><para><anchor
id="prod77"
xreflabel="notCrit"/>notCrit</para></entry>
<entry align="left" valign="top"><para>::=
-( <NOT> )? <link
linkend="prod79">primary</link></para></entry></row>
+( <NOT> )? <link
linkend="prod78">booleanPrimary</link></para></entry></row>
<row>
-<entry align="right" valign="top"><para><anchor
id="prod79"
xreflabel="primary"/>primary</para></entry>
+<entry align="right" valign="top"><para><anchor
id="prod78"
xreflabel="booleanPrimary"/>booleanPrimary</para></entry>
<entry align="left" valign="top"><para>::=
-( <link linkend="prod80">predicate</link> | (
<LPAREN> <link linkend="prod28">criteria</link>
<RPAREN> ) )</para></entry></row>
+( <link linkend="prod79">predicate</link> | (
<LPAREN> <link linkend="prod28">criteria</link>
<RPAREN> ) )</para></entry></row>
<row>
-<entry align="right" valign="top"><para><anchor
id="prod80"
xreflabel="predicate"/>predicate</para></entry>
+<entry align="right" valign="top"><para><anchor
id="prod79"
xreflabel="predicate"/>predicate</para></entry>
<entry align="left" valign="top"><para>::=
-( <link linkend="prod81">subqueryCompareCriteria</link> | <link
linkend="prod82">compareCrit</link> | <link
linkend="prod83">matchCrit</link> | <link
linkend="prod84">betweenCrit</link> | <link
linkend="prod85">setCrit</link> | <link
linkend="prod86">existsCriteria</link> | <link
linkend="prod30">hasCriteria</link> | <link
linkend="prod34">translateCriteria</link> | <link
linkend="prod87">isNullCrit</link>
)</para></entry></row>
+( <link linkend="prod34">translateCriteria</link> | ( <link
linkend="prod80">commonValueExpression</link> ( <link
linkend="prod81">betweenCrit</link> | <link
linkend="prod82">matchCrit</link> | <link
linkend="prod83">setCrit</link> | <link
linkend="prod84">isNullCrit</link> | <link
linkend="prod85">compareCrit</link> | <link
linkend="prod86">subqueryCompareCriteria</link> ) ) | <link
linkend="prod87">existsCriteria</link> | <link
linkend="prod30">hasCriteria</link>
)</para></entry></row>
<row>
-<entry align="right" valign="top"><para><anchor
id="prod82"
xreflabel="compareCrit"/>compareCrit</para></entry>
+<entry align="right" valign="top"><para><anchor
id="prod88"
xreflabel="operator"/>operator</para></entry>
<entry align="left" valign="top"><para>::=
-<link linkend="prod16">expression</link> ( <EQ> |
<NE> | <NE2> | <LT> | <LE> |
<GT> | <GE> ) <link
linkend="prod16">expression</link></para></entry></row>
+( <EQ> | <NE> | <NE2> | <LT> |
<LE> | <GT> | <GE>
)</para></entry></row>
<row>
-<entry align="right" valign="top"><para><anchor
id="prod88"
xreflabel="subquery"/>subquery</para></entry>
+<entry align="right" valign="top"><para><anchor
id="prod85"
xreflabel="compareCrit"/>compareCrit</para></entry>
<entry align="left" valign="top"><para>::=
+<link linkend="prod88">operator</link> <link
linkend="prod80">commonValueExpression</link></para></entry></row>
+<row>
+<entry align="right" valign="top"><para><anchor
id="prod89"
xreflabel="subquery"/>subquery</para></entry>
+<entry align="left" valign="top"><para>::=
<LPAREN> ( <link
linkend="prod7">queryExpression</link> | <link
linkend="prod8">storedProcedure</link> )
<RPAREN></para></entry></row>
<row>
-<entry align="right" valign="top"><para><anchor
id="prod81"
xreflabel="subqueryCompareCriteria"/>subqueryCompareCriteria</para></entry>
+<entry align="right" valign="top"><para><anchor
id="prod86"
xreflabel="subqueryCompareCriteria"/>subqueryCompareCriteria</para></entry>
<entry align="left" valign="top"><para>::=
-<link linkend="prod16">expression</link> ( <EQ> |
<NE> | <NE2> | <LT> | <LE> |
<GT> | <GE> ) ( <ANY> | <SOME> |
<ALL> ) <link
linkend="prod88">subquery</link></para></entry></row>
+<link linkend="prod88">operator</link> ( <ANY> |
<SOME> | <ALL> ) <link
linkend="prod89">subquery</link></para></entry></row>
<row>
-<entry align="right" valign="top"><para><anchor
id="prod83"
xreflabel="matchCrit"/>matchCrit</para></entry>
+<entry align="right" valign="top"><para><anchor
id="prod82"
xreflabel="matchCrit"/>matchCrit</para></entry>
<entry align="left" valign="top"><para>::=
-( <link linkend="prod16">expression</link> ( <NOT> )?
<LIKE> <link linkend="prod16">expression</link> (
<ESCAPE> <link linkend="prod71">charVal</link> | (
<LBRACE> <ESCAPE> <link
linkend="prod71">charVal</link> <RBRACE> ) )?
)</para></entry></row>
+( <NOT> )? <LIKE> <link
linkend="prod80">commonValueExpression</link> ( <ESCAPE>
<link linkend="prod70">charVal</link> | ( <LBRACE>
<ESCAPE> <link linkend="prod70">charVal</link>
<RBRACE> ) )?</para></entry></row>
<row>
-<entry align="right" valign="top"><para><anchor
id="prod71"
xreflabel="charVal"/>charVal</para></entry>
+<entry align="right" valign="top"><para><anchor
id="prod70"
xreflabel="charVal"/>charVal</para></entry>
<entry align="left" valign="top"><para>::=
<link
linkend="prod1">stringVal</link></para></entry></row>
<row>
-<entry align="right" valign="top"><para><anchor
id="prod84"
xreflabel="betweenCrit"/>betweenCrit</para></entry>
+<entry align="right" valign="top"><para><anchor
id="prod81"
xreflabel="betweenCrit"/>betweenCrit</para></entry>
<entry align="left" valign="top"><para>::=
-<link linkend="prod16">expression</link> ( <NOT> )?
<BETWEEN> <link linkend="prod16">expression</link>
<AND> <link
linkend="prod16">expression</link></para></entry></row>
+( <NOT> )? <BETWEEN> <link
linkend="prod80">commonValueExpression</link> <AND>
<link
linkend="prod80">commonValueExpression</link></para></entry></row>
<row>
-<entry align="right" valign="top"><para><anchor
id="prod87"
xreflabel="isNullCrit"/>isNullCrit</para></entry>
+<entry align="right" valign="top"><para><anchor
id="prod84"
xreflabel="isNullCrit"/>isNullCrit</para></entry>
<entry align="left" valign="top"><para>::=
-<link linkend="prod16">expression</link> <IS> (
<NOT> )? <NULL></para></entry></row>
+<IS> ( <NOT> )?
<NULL></para></entry></row>
<row>
-<entry align="right" valign="top"><para><anchor
id="prod85"
xreflabel="setCrit"/>setCrit</para></entry>
+<entry align="right" valign="top"><para><anchor
id="prod83"
xreflabel="setCrit"/>setCrit</para></entry>
<entry align="left" valign="top"><para>::=
-<link linkend="prod16">expression</link> ( <NOT> )?
<IN> ( ( <link linkend="prod88">subquery</link> ) | (
<LPAREN> <link linkend="prod16">expression</link> (
<COMMA> <link linkend="prod16">expression</link> )*
<RPAREN> ) )</para></entry></row>
+( <NOT> )? <IN> ( ( <link
linkend="prod89">subquery</link> ) | ( <LPAREN> <link
linkend="prod80">commonValueExpression</link> ( <COMMA>
<link linkend="prod80">commonValueExpression</link> )*
<RPAREN> ) )</para></entry></row>
<row>
-<entry align="right" valign="top"><para><anchor
id="prod86"
xreflabel="existsCriteria"/>existsCriteria</para></entry>
+<entry align="right" valign="top"><para><anchor
id="prod87"
xreflabel="existsCriteria"/>existsCriteria</para></entry>
<entry align="left" valign="top"><para>::=
-<EXISTS> <link
linkend="prod88">subquery</link></para></entry></row>
+<EXISTS> <link
linkend="prod89">subquery</link></para></entry></row>
<row>
<entry align="right" valign="top"><para><anchor
id="prod50"
xreflabel="groupBy"/>groupBy</para></entry>
<entry align="left" valign="top"><para>::=
-<GROUP> <BY> ( <link
linkend="prod89">groupByItem</link> ( <COMMA> <link
linkend="prod89">groupByItem</link> )*
)</para></entry></row>
+<GROUP> <BY> ( <link
linkend="prod90">groupByItem</link> ( <COMMA> <link
linkend="prod90">groupByItem</link> )*
)</para></entry></row>
<row>
-<entry align="right" valign="top"><para><anchor
id="prod89"
xreflabel="groupByItem"/>groupByItem</para></entry>
+<entry align="right" valign="top"><para><anchor
id="prod90"
xreflabel="groupByItem"/>groupByItem</para></entry>
<entry align="left" valign="top"><para>::=
<link
linkend="prod16">expression</link></para></entry></row>
<row>
@@ -728,10 +727,14 @@
<row>
<entry align="right" valign="top"><para><anchor
id="prod43"
xreflabel="orderby"/>orderby</para></entry>
<entry align="left" valign="top"><para>::=
-<ORDER> <BY> <link
linkend="prod90">sortKey</link> ( <ASC> |
<DESC> )? ( <COMMA> <link
linkend="prod90">sortKey</link> ( <ASC> |
<DESC> )? )*</para></entry></row>
+<ORDER> <BY> <link
linkend="prod91">sortSpecification</link> ( <COMMA>
<link linkend="prod91">sortSpecification</link>
)*</para></entry></row>
<row>
-<entry align="right" valign="top"><para><anchor
id="prod90"
xreflabel="sortKey"/>sortKey</para></entry>
+<entry align="right" valign="top"><para><anchor
id="prod91"
xreflabel="sortSpecification"/>sortSpecification</para></entry>
<entry align="left" valign="top"><para>::=
+<link linkend="prod92">sortKey</link> ( <ASC> |
<DESC> )? ( <link linkend="prod58">nonReserved</link>
<link linkend="prod58">nonReserved</link>
)?</para></entry></row>
+<row>
+<entry align="right" valign="top"><para><anchor
id="prod92"
xreflabel="sortKey"/>sortKey</para></entry>
+<entry align="left" valign="top"><para>::=
<link
linkend="prod16">expression</link></para></entry></row>
<row>
<entry align="right" valign="top"><para><anchor
id="prod44" xreflabel="limit"/>limit</para></entry>
@@ -744,33 +747,33 @@
<row>
<entry align="right" valign="top"><para><anchor
id="prod16"
xreflabel="expression"/>expression</para></entry>
<entry align="left" valign="top"><para>::=
-<link
linkend="prod91">concatExpression</link></para></entry></row>
+<link
linkend="prod80">commonValueExpression</link></para></entry></row>
<row>
-<entry align="right" valign="top"><para><anchor
id="prod91"
xreflabel="concatExpression"/>concatExpression</para></entry>
+<entry align="right" valign="top"><para><anchor
id="prod80"
xreflabel="commonValueExpression"/>commonValueExpression</para></entry>
<entry align="left" valign="top"><para>::=
-( <link linkend="prod92">plusExpression</link> (
<CONCAT_OP> <link
linkend="prod92">plusExpression</link> )*
)</para></entry></row>
+( <link linkend="prod93">plusExpression</link> (
<CONCAT_OP> <link
linkend="prod93">plusExpression</link> )*
)</para></entry></row>
<row>
-<entry align="right" valign="top"><para><anchor
id="prod92"
xreflabel="plusExpression"/>plusExpression</para></entry>
+<entry align="right" valign="top"><para><anchor
id="prod93"
xreflabel="plusExpression"/>plusExpression</para></entry>
<entry align="left" valign="top"><para>::=
-( <link linkend="prod93">timesExpression</link> ( <link
linkend="prod94">plusOperator</link> <link
linkend="prod93">timesExpression</link> )*
)</para></entry></row>
+( <link linkend="prod94">timesExpression</link> ( <link
linkend="prod95">plusOperator</link> <link
linkend="prod94">timesExpression</link> )*
)</para></entry></row>
<row>
-<entry align="right" valign="top"><para><anchor
id="prod94"
xreflabel="plusOperator"/>plusOperator</para></entry>
+<entry align="right" valign="top"><para><anchor
id="prod95"
xreflabel="plusOperator"/>plusOperator</para></entry>
<entry align="left" valign="top"><para>::=
( <PLUS> | <MINUS> )</para></entry></row>
<row>
-<entry align="right" valign="top"><para><anchor
id="prod93"
xreflabel="timesExpression"/>timesExpression</para></entry>
+<entry align="right" valign="top"><para><anchor
id="prod94"
xreflabel="timesExpression"/>timesExpression</para></entry>
<entry align="left" valign="top"><para>::=
-( <link linkend="prod95">basicExpression</link> ( <link
linkend="prod96">timesOperator</link> <link
linkend="prod95">basicExpression</link> )*
)</para></entry></row>
+( <link linkend="prod96">valueExpressionPrimary</link> ( <link
linkend="prod97">timesOperator</link> <link
linkend="prod96">valueExpressionPrimary</link> )*
)</para></entry></row>
<row>
-<entry align="right" valign="top"><para><anchor
id="prod96"
xreflabel="timesOperator"/>timesOperator</para></entry>
+<entry align="right" valign="top"><para><anchor
id="prod97"
xreflabel="timesOperator"/>timesOperator</para></entry>
<entry align="left" valign="top"><para>::=
( <STAR> | <SLASH> )</para></entry></row>
<row>
-<entry align="right" valign="top"><para><anchor
id="prod95"
xreflabel="basicExpression"/>basicExpression</para></entry>
+<entry align="right" valign="top"><para><anchor
id="prod96"
xreflabel="valueExpressionPrimary"/>valueExpressionPrimary</para></entry>
<entry align="left" valign="top"><para>::=
-( <QMARK> | <link linkend="prod97">literal</link> | (
<LBRACE> <link linkend="prod58">nonReserved</link>
<link linkend="prod98">function</link> <RBRACE> ) | (
<link linkend="prod57">aggregateSymbol</link> ) | ( <link
linkend="prod56">xmlAgg</link> ) | ( <link
linkend="prod98">function</link> ) | ( <ID> ) | (
<LPAREN> <link linkend="prod16">expression</link>
<RPAREN> ) | <link linkend="prod88">subquery</link> |
<link linkend="prod99">caseExpression</link> | <link
linkend="prod100">searchedCaseExpression</link>
)</para></entry></row>
+( <QMARK> | <link linkend="prod98">literal</link> | (
<LBRACE> <link linkend="prod58">nonReserved</link>
<link linkend="prod99">function</link> <RBRACE> ) | (
<link linkend="prod57">aggregateSymbol</link> ) | ( <link
linkend="prod57">aggregateSymbol</link> ) | ( <link
linkend="prod57">aggregateSymbol</link> ) | ( <link
linkend="prod56">xmlAgg</link> ) | ( <link
linkend="prod99">function</link> ) | ( <ID> ) | (
<LPAREN> <link linkend="prod16">expression</link>
<RPAREN> ) | <link linkend="prod89">subquery</link> |
<link linkend="prod100">searchedCaseExpression</link> | <link
linkend="prod101">caseExpression</link>
)</para></entry></row>
<row>
-<entry align="right" valign="top"><para><anchor
id="prod99"
xreflabel="caseExpression"/>caseExpression</para></entry>
+<entry align="right" valign="top"><para><anchor
id="prod101"
xreflabel="caseExpression"/>caseExpression</para></entry>
<entry align="left" valign="top"><para>::=
<CASE> <link linkend="prod16">expression</link> (
<WHEN> <link linkend="prod16">expression</link>
<THEN> <link linkend="prod16">expression</link> )+ (
<ELSE> <link linkend="prod16">expression</link> )?
<END></para></entry></row>
<row>
@@ -778,35 +781,39 @@
<entry align="left" valign="top"><para>::=
<CASE> ( <WHEN> <link
linkend="prod28">criteria</link> <THEN> <link
linkend="prod16">expression</link> )+ ( <ELSE> <link
linkend="prod16">expression</link> )?
<END></para></entry></row>
<row>
-<entry align="right" valign="top"><para><anchor
id="prod98"
xreflabel="function"/>function</para></entry>
+<entry align="right" valign="top"><para><anchor
id="prod99"
xreflabel="function"/>function</para></entry>
<entry align="left" valign="top"><para>::=
-( ( <CONVERT> <LPAREN> <link
linkend="prod16">expression</link> <COMMA> <link
linkend="prod31">dataType</link> <RPAREN> ) | (
<CAST> <LPAREN> <link
linkend="prod16">expression</link> <AS> <link
linkend="prod31">dataType</link> <RPAREN> ) | ( <link
linkend="prod58">nonReserved</link> <LPAREN> <link
linkend="prod101">intervalType</link> <COMMA> <link
linkend="prod16">expression</link> <COMMA> <link
linkend="prod16">expression</link> <RPAREN> ) | <link
linkend="prod102">queryString</link> | ( ( <LEFT> |
<RIGHT> | <CHAR> | <USER> | <YEAR>
| <MONTH> | <HOUR> | <MINUTE> |
<SECOND> | <XMLCONCAT> | <XMLCOMMENT> )
<LPAREN> ( <link linkend="prod16">expression</link> (
<COMMA> <link linkend="prod16">expression</link> )* )?
<RPAREN> ) | ( ( <INSERT> ) <LPAREN> ( <link
linkend="prod16">expression</link> ( <COMMA> <l!
ink linkend="prod16">expression</link> )* )? <RPAREN> )
| ( ( <TRANSLATE> ) <LPAREN> ( <link
linkend="prod16">expression</link> ( <COMMA> <link
linkend="prod16">expression</link> )* )? <RPAREN> ) |
<link linkend="prod103">xmlParse</link> | <link
linkend="prod104">xmlElement</link> | ( <XMLPI>
<LPAREN> ( <ID> <link
linkend="prod105">idExpression</link> | <link
linkend="prod105">idExpression</link> ) ( <COMMA>
<link linkend="prod16">expression</link> )? <RPAREN> )
| <link linkend="prod106">xmlForest</link> | <link
linkend="prod69">xmlSerialize</link> | <link
linkend="prod73">xmlQuery</link> | ( <link
linkend="prod2">id</link> <LPAREN> ( <link
linkend="prod16">expression</link> ( <COMMA> <link
linkend="prod16">expression</link> )* )? <RPAREN> )
)</para></entry></row>
+( ( <CONVERT> <LPAREN> <link
linkend="prod16">expression</link> <COMMA> <link
linkend="prod31">dataType</link> <RPAREN> ) | (
<CAST> <LPAREN> <link
linkend="prod16">expression</link> <AS> <link
linkend="prod31">dataType</link> <RPAREN> ) | ( <link
linkend="prod58">nonReserved</link> <LPAREN> <link
linkend="prod16">expression</link> <COMMA> <link
linkend="prod102">stringConstant</link> <RPAREN> ) | (
<link linkend="prod58">nonReserved</link> <LPAREN>
<link linkend="prod103">intervalType</link> <COMMA>
<link linkend="prod16">expression</link> <COMMA>
<link linkend="prod16">expression</link> <RPAREN> ) |
<link linkend="prod104">queryString</link> | ( (
<LEFT> | <RIGHT> | <CHAR> | <USER>
| <YEAR> | <MONTH> | <HOUR> |
<MINUTE> | <SECOND> | <XMLCONCAT> |
<XMLCOMMENT> ) <LPAREN> ( <link
linkend="prod16">expression</link> !
( <COMMA> <link linkend="prod16">expression</link> )*
)? <RPAREN> ) | ( ( <INSERT> ) <LPAREN> (
<link linkend="prod16">expression</link> ( <COMMA>
<link linkend="prod16">expression</link> )* )?
<RPAREN> ) | ( ( <TRANSLATE> ) <LPAREN> (
<link linkend="prod16">expression</link> ( <COMMA>
<link linkend="prod16">expression</link> )* )?
<RPAREN> ) | <link linkend="prod105">xmlParse</link> |
<link linkend="prod106">xmlElement</link> | ( <XMLPI>
<LPAREN> ( <ID> <link
linkend="prod107">idExpression</link> | <link
linkend="prod107">idExpression</link> ) ( <COMMA>
<link linkend="prod16">expression</link> )? <RPAREN> )
| <link linkend="prod108">xmlForest</link> | <link
linkend="prod68">xmlSerialize</link> | <link
linkend="prod72">xmlQuery</link> | ( <link
linkend="prod2">id</link> <LPAREN> ( <link
linkend="prod16">expression</link> ( <COMMA> <link
linkend="prod16">expression</link> )*!
)? <RPAREN> ) )</para></entry></row>
<row>
-<entry align="right" valign="top"><para><anchor
id="prod103"
xreflabel="xmlParse"/>xmlParse</para></entry>
+<entry align="right" valign="top"><para><anchor
id="prod102"
xreflabel="stringConstant"/>stringConstant</para></entry>
<entry align="left" valign="top"><para>::=
+<link
linkend="prod1">stringVal</link></para></entry></row>
+<row>
+<entry align="right" valign="top"><para><anchor
id="prod105"
xreflabel="xmlParse"/>xmlParse</para></entry>
+<entry align="left" valign="top"><para>::=
<XMLPARSE> <LPAREN> <link
linkend="prod58">nonReserved</link> <link
linkend="prod16">expression</link> ( <link
linkend="prod58">nonReserved</link> )?
<RPAREN></para></entry></row>
<row>
-<entry align="right" valign="top"><para><anchor
id="prod102"
xreflabel="queryString"/>queryString</para></entry>
+<entry align="right" valign="top"><para><anchor
id="prod104"
xreflabel="queryString"/>queryString</para></entry>
<entry align="left" valign="top"><para>::=
<link linkend="prod58">nonReserved</link> <LPAREN>
<link linkend="prod16">expression</link> ( <COMMA>
<link linkend="prod55">derivedColumn</link> )*
<RPAREN></para></entry></row>
<row>
-<entry align="right" valign="top"><para><anchor
id="prod104"
xreflabel="xmlElement"/>xmlElement</para></entry>
+<entry align="right" valign="top"><para><anchor
id="prod106"
xreflabel="xmlElement"/>xmlElement</para></entry>
<entry align="left" valign="top"><para>::=
-<XMLELEMENT> <LPAREN> ( <ID> <link
linkend="prod2">id</link> | <link
linkend="prod2">id</link> ) ( <COMMA> <link
linkend="prod74">xmlNamespaces</link> )? ( <COMMA>
<link linkend="prod107">xmlAttributes</link> )? (
<COMMA> <link linkend="prod16">expression</link> )*
<RPAREN></para></entry></row>
+<XMLELEMENT> <LPAREN> ( <ID> <link
linkend="prod2">id</link> | <link
linkend="prod2">id</link> ) ( <COMMA> <link
linkend="prod73">xmlNamespaces</link> )? ( <COMMA>
<link linkend="prod109">xmlAttributes</link> )? (
<COMMA> <link linkend="prod16">expression</link> )*
<RPAREN></para></entry></row>
<row>
-<entry align="right" valign="top"><para><anchor
id="prod107"
xreflabel="xmlAttributes"/>xmlAttributes</para></entry>
+<entry align="right" valign="top"><para><anchor
id="prod109"
xreflabel="xmlAttributes"/>xmlAttributes</para></entry>
<entry align="left" valign="top"><para>::=
<XMLATTRIBUTES> <LPAREN> <link
linkend="prod55">derivedColumn</link> ( <COMMA> <link
linkend="prod55">derivedColumn</link> )*
<RPAREN></para></entry></row>
<row>
-<entry align="right" valign="top"><para><anchor
id="prod106"
xreflabel="xmlForest"/>xmlForest</para></entry>
+<entry align="right" valign="top"><para><anchor
id="prod108"
xreflabel="xmlForest"/>xmlForest</para></entry>
<entry align="left" valign="top"><para>::=
-<XMLFOREST> <LPAREN> ( <link
linkend="prod74">xmlNamespaces</link> <COMMA> )? <link
linkend="prod55">derivedColumn</link> ( <COMMA> <link
linkend="prod55">derivedColumn</link> )*
<RPAREN></para></entry></row>
+<XMLFOREST> <LPAREN> ( <link
linkend="prod73">xmlNamespaces</link> <COMMA> )? <link
linkend="prod55">derivedColumn</link> ( <COMMA> <link
linkend="prod55">derivedColumn</link> )*
<RPAREN></para></entry></row>
<row>
-<entry align="right" valign="top"><para><anchor
id="prod74"
xreflabel="xmlNamespaces"/>xmlNamespaces</para></entry>
+<entry align="right" valign="top"><para><anchor
id="prod73"
xreflabel="xmlNamespaces"/>xmlNamespaces</para></entry>
<entry align="left" valign="top"><para>::=
-<XMLNAMESPACES> <LPAREN> <link
linkend="prod108">namespaceItem</link> ( <COMMA> <link
linkend="prod108">namespaceItem</link> )*
<RPAREN></para></entry></row>
+<XMLNAMESPACES> <LPAREN> <link
linkend="prod110">namespaceItem</link> ( <COMMA> <link
linkend="prod110">namespaceItem</link> )*
<RPAREN></para></entry></row>
<row>
-<entry align="right" valign="top"><para><anchor
id="prod108"
xreflabel="namespaceItem"/>namespaceItem</para></entry>
+<entry align="right" valign="top"><para><anchor
id="prod110"
xreflabel="namespaceItem"/>namespaceItem</para></entry>
<entry align="left" valign="top"><para>::=
( <link linkend="prod1">stringVal</link> <AS>
<link linkend="prod2">id</link>
)</para></entry></row>
<row>
@@ -818,7 +825,7 @@
<entry align="left" valign="top"><para>::=
( <DEFAULT_KEYWORD> <link
linkend="prod1">stringVal</link>
)</para></entry></row>
<row>
-<entry align="right" valign="top"><para><anchor
id="prod105"
xreflabel="idExpression"/>idExpression</para></entry>
+<entry align="right" valign="top"><para><anchor
id="prod107"
xreflabel="idExpression"/>idExpression</para></entry>
<entry align="left" valign="top"><para>::=
<link
linkend="prod2">id</link></para></entry></row>
<row>
@@ -826,13 +833,13 @@
<entry align="left" valign="top"><para>::=
( <STRING> | <VARCHAR> | <BOOLEAN> |
<BYTE> | <TINYINT> | <SHORT> |
<SMALLINT> | <CHAR> | <INTEGER> |
<LONG> | <BIGINT> | <BIGINTEGER> |
<FLOAT> | <REAL> | <DOUBLE> |
<BIGDECIMAL> | <DECIMAL> | <DATE> |
<TIME> | <TIMESTAMP> | <OBJECT> |
<BLOB> | <CLOB> | <XML>
)</para></entry></row>
<row>
-<entry align="right" valign="top"><para><anchor
id="prod101"
xreflabel="intervalType"/>intervalType</para></entry>
+<entry align="right" valign="top"><para><anchor
id="prod103"
xreflabel="intervalType"/>intervalType</para></entry>
<entry align="left" valign="top"><para>::=
( <link linkend="prod58">nonReserved</link>
)</para></entry></row>
<row>
-<entry align="right" valign="top"><para><anchor
id="prod97"
xreflabel="literal"/>literal</para></entry>
+<entry align="right" valign="top"><para><anchor
id="prod98"
xreflabel="literal"/>literal</para></entry>
<entry align="left" valign="top"><para>::=
-( <link linkend="prod1">stringVal</link> |
<INTEGERVAL> | <FLOATVAL> | <FALSE> |
<TRUE> | <UNKNOWN> | <NULL> | ( (
<BOOLEANTYPE> | <TIMESTAMPTYPE> | <DATETYPE> |
<TIMETYPE> | <XMLTYPE> ) <link
linkend="prod1">stringVal</link> <RBRACE> )
)</para></entry></row>
+( <link linkend="prod1">stringVal</link> |
<INTEGERVAL> | <FLOATVAL> | <FALSE> |
<TRUE> | <UNKNOWN> | <NULL> | ( (
<BOOLEANTYPE> | <TIMESTAMPTYPE> | <DATETYPE> |
<TIMETYPE> ) <link linkend="prod1">stringVal</link>
<RBRACE> ) )</para></entry></row>
</tbody>
</tgroup>
</informaltable>
Modified: trunk/documentation/reference/src/main/docbook/en-US/content/sql_support.xml
===================================================================
---
trunk/documentation/reference/src/main/docbook/en-US/content/sql_support.xml 2010-06-23
20:58:24 UTC (rev 2290)
+++
trunk/documentation/reference/src/main/docbook/en-US/content/sql_support.xml 2010-06-23
21:16:17 UTC (rev 2291)
@@ -113,8 +113,7 @@
<title>Expressions</title>
<para> Identifiers, literals, and functions can be combined into
expressions. Expressions can be used almost anywhere in a query --
- SELECT, FROM (if specifying join criteria, WHERE, GROUP BY, HAVING.
- However you currently cannot use expressions in an ORDER BY clause.</para>
+ SELECT, FROM (if specifying join criteria), WHERE, GROUP BY, HAVING, or ORDER
BY.</para>
<itemizedlist>
<para>Teiid supports the following types of expressions:
</para>
@@ -973,7 +972,7 @@
</para>
<para>
Usage:
- <synopsis label="Usage">ORDER BY expression [ASC|DESC],
...</synopsis>
+ <synopsis label="Usage">ORDER BY expression [ASC|DESC] [NULLS
(FIRST|LAST)], ...</synopsis>
</para>
<itemizedlist>
<para>Syntax Rules:
@@ -1004,6 +1003,12 @@
definition without a limit clause, it will be removed by the Teiid
optimizer.</para>
</listitem>
+ <listitem>
+ <para>If NULLS FIRST/LAST is specified, then nulls are guaranteed to be
sorted either first or last. If the null ordering is not specified, then results will
+ typically be sorted with nulls as low values, which is Teiid's internal
default sorting behavior.
+ However not all sources return results with nulss sorted as low values by
default, and Teiid may return results with different null orderings.
+ </para>
+ </listitem>
</itemizedlist>
<warning>
<para>The use of positional ordering is no longer supported by the
Modified:
trunk/engine/src/main/java/org/teiid/dqp/internal/datamgr/impl/CapabilitiesConverter.java
===================================================================
---
trunk/engine/src/main/java/org/teiid/dqp/internal/datamgr/impl/CapabilitiesConverter.java 2010-06-23
20:58:24 UTC (rev 2290)
+++
trunk/engine/src/main/java/org/teiid/dqp/internal/datamgr/impl/CapabilitiesConverter.java 2010-06-23
21:16:17 UTC (rev 2291)
@@ -95,6 +95,7 @@
tgtCaps.setCapabilitySupport(Capability.INSERT_WITH_QUERYEXPRESSION,
srcCaps.supportsInsertWithQueryExpression());
tgtCaps.setCapabilitySupport(Capability.QUERY_ORDERBY_UNRELATED,
srcCaps.supportsOrderByUnrelated());
tgtCaps.setCapabilitySupport(Capability.QUERY_AGGREGATES_ENHANCED_NUMERIC,
srcCaps.supportsAggregatesEnhancedNumeric());
+ tgtCaps.setCapabilitySupport(Capability.QUERY_ORDERBY_NULL_ORDERING,
srcCaps.supportsOrderByNullOrdering());
List functions = srcCaps.getSupportedFunctions();
if(functions != null && functions.size() > 0) {
@@ -109,6 +110,7 @@
tgtCaps.setSourceProperty(Capability.CONNECTOR_ID, connectorID);
tgtCaps.setSourceProperty(Capability.MAX_QUERY_FROM_GROUPS, new
Integer(srcCaps.getMaxFromGroups()));
tgtCaps.setSourceProperty(Capability.JOIN_CRITERIA_ALLOWED,
srcCaps.getSupportedJoinCriteria());
+ tgtCaps.setSourceProperty(Capability.QUERY_ORDERBY_DEFAULT_NULL_ORDER,
srcCaps.getDefaultNullOrder());
return tgtCaps;
}
Modified:
trunk/engine/src/main/java/org/teiid/dqp/internal/datamgr/language/LanguageBridgeFactory.java
===================================================================
---
trunk/engine/src/main/java/org/teiid/dqp/internal/datamgr/language/LanguageBridgeFactory.java 2010-06-23
20:58:24 UTC (rev 2290)
+++
trunk/engine/src/main/java/org/teiid/dqp/internal/datamgr/language/LanguageBridgeFactory.java 2010-06-23
21:16:17 UTC (rev 2291)
@@ -429,6 +429,7 @@
} else {
orderByItem = new SortSpecification(direction, translate(symbol));
}
+ orderByItem.setNullOrdering(items.get(i).getNullOrdering());
translatedItems.add(orderByItem);
}
return new org.teiid.language.OrderBy(translatedItems);
Modified:
trunk/engine/src/main/java/org/teiid/query/optimizer/capabilities/SourceCapabilities.java
===================================================================
---
trunk/engine/src/main/java/org/teiid/query/optimizer/capabilities/SourceCapabilities.java 2010-06-23
20:58:24 UTC (rev 2290)
+++
trunk/engine/src/main/java/org/teiid/query/optimizer/capabilities/SourceCapabilities.java 2010-06-23
21:16:17 UTC (rev 2291)
@@ -164,6 +164,8 @@
QUERY_ORDERBY,
QUERY_ORDERBY_UNRELATED,
+ QUERY_ORDERBY_NULL_ORDERING,
+ QUERY_ORDERBY_DEFAULT_NULL_ORDER,
/**
* Composite support for group by and having - not
* used by the connector layer
Modified:
trunk/engine/src/main/java/org/teiid/query/optimizer/relational/PlanToProcessConverter.java
===================================================================
---
trunk/engine/src/main/java/org/teiid/query/optimizer/relational/PlanToProcessConverter.java 2010-06-23
20:58:24 UTC (rev 2290)
+++
trunk/engine/src/main/java/org/teiid/query/optimizer/relational/PlanToProcessConverter.java 2010-06-23
21:16:17 UTC (rev 2291)
@@ -28,8 +28,6 @@
import java.util.List;
import java.util.Map;
-import net.sf.saxon.expr.PathMap.PathMapRoot;
-
import org.teiid.api.exception.query.QueryMetadataException;
import org.teiid.api.exception.query.QueryPlannerException;
import org.teiid.core.CoreConstants;
@@ -343,7 +341,7 @@
SortNode sortNode = new SortNode(getID());
OrderBy orderBy = (OrderBy)
node.getProperty(NodeConstants.Info.SORT_ORDER);
if (orderBy != null) {
- sortNode.setSortElements(orderBy.getSortKeys(), orderBy.getTypes());
+ sortNode.setSortElements(orderBy.getOrderByItems());
}
if (node.getType() == NodeConstants.Types.DUP_REMOVE) {
sortNode.setMode(Mode.DUP_REMOVE);
Modified:
trunk/engine/src/main/java/org/teiid/query/optimizer/relational/rules/CapabilitiesUtil.java
===================================================================
---
trunk/engine/src/main/java/org/teiid/query/optimizer/relational/rules/CapabilitiesUtil.java 2010-06-23
20:58:24 UTC (rev 2290)
+++
trunk/engine/src/main/java/org/teiid/query/optimizer/relational/rules/CapabilitiesUtil.java 2010-06-23
21:16:17 UTC (rev 2291)
@@ -45,6 +45,7 @@
import org.teiid.query.sql.symbol.Function;
import org.teiid.query.sql.symbol.AggregateSymbol.Type;
import org.teiid.query.sql.visitor.ElementCollectorVisitor;
+import org.teiid.translator.ExecutionFactory.NullOrder;
import org.teiid.translator.ExecutionFactory.SupportedJoinCriteria;
@@ -340,6 +341,18 @@
return crits;
}
+ public static NullOrder getDefaultNullOrder(Object modelID, QueryMetadataInterface
metadata, CapabilitiesFinder capFinder) throws QueryMetadataException,
TeiidComponentException {
+ if (metadata.isVirtualModel(modelID)){
+ return NullOrder.UNKNOWN;
+ }
+ SourceCapabilities caps = getCapabilities(modelID, metadata, capFinder);
+ NullOrder order =
(NullOrder)caps.getSourceProperty(Capability.QUERY_ORDERBY_DEFAULT_NULL_ORDER);
+ if (order == null) {
+ return NullOrder.UNKNOWN;
+ }
+ return order;
+ }
+
public static boolean supportsRowLimit(Object modelID, QueryMetadataInterface
metadata, CapabilitiesFinder capFinder)
throws QueryMetadataException, TeiidComponentException {
return supports(Capability.ROW_LIMIT, modelID, metadata, capFinder);
Modified:
trunk/engine/src/main/java/org/teiid/query/optimizer/relational/rules/RuleRaiseAccess.java
===================================================================
---
trunk/engine/src/main/java/org/teiid/query/optimizer/relational/rules/RuleRaiseAccess.java 2010-06-23
20:58:24 UTC (rev 2290)
+++
trunk/engine/src/main/java/org/teiid/query/optimizer/relational/rules/RuleRaiseAccess.java 2010-06-23
21:16:17 UTC (rev 2291)
@@ -31,6 +31,7 @@
import org.teiid.api.exception.query.QueryMetadataException;
import org.teiid.api.exception.query.QueryPlannerException;
import org.teiid.core.TeiidComponentException;
+import org.teiid.language.SortSpecification.NullOrdering;
import org.teiid.query.analysis.AnalysisRecord;
import org.teiid.query.metadata.QueryMetadataInterface;
import org.teiid.query.metadata.SupportConstants;
@@ -46,6 +47,7 @@
import org.teiid.query.sql.lang.Criteria;
import org.teiid.query.sql.lang.JoinType;
import org.teiid.query.sql.lang.OrderBy;
+import org.teiid.query.sql.lang.OrderByItem;
import org.teiid.query.sql.lang.SetQuery.Operation;
import org.teiid.query.sql.symbol.AggregateSymbol;
import org.teiid.query.sql.symbol.ElementSymbol;
@@ -54,6 +56,7 @@
import org.teiid.query.sql.symbol.SingleElementSymbol;
import org.teiid.query.sql.util.SymbolMap;
import org.teiid.query.util.CommandContext;
+import org.teiid.translator.ExecutionFactory.NullOrder;
import org.teiid.translator.ExecutionFactory.SupportedJoinCriteria;
@@ -317,12 +320,37 @@
return false;
}
- //TODO: this check shouldn't be necessary, since the order by is not
introducing new expressions
- List<SingleElementSymbol> sortCols =
((OrderBy)parentNode.getProperty(NodeConstants.Info.SORT_ORDER)).getSortKeys();
- for (SingleElementSymbol symbol : sortCols) {
- if(! canPushSymbol(symbol, true, modelID, metadata, capFinder)) {
+ List<OrderByItem> sortCols =
((OrderBy)parentNode.getProperty(NodeConstants.Info.SORT_ORDER)).getOrderByItems();
+ for (OrderByItem symbol : sortCols) {
+ //TODO: this check shouldn't be necessary, since the order by is not
introducing new expressions
+ if(! canPushSymbol(symbol.getSymbol(), true, modelID, metadata, capFinder))
{
return false;
}
+ boolean supportsNullOrdering =
CapabilitiesUtil.supports(Capability.QUERY_ORDERBY_NULL_ORDERING, modelID, metadata,
capFinder);
+ NullOrder defaultNullOrder = CapabilitiesUtil.getDefaultNullOrder(modelID,
metadata, capFinder);
+ if (symbol.getNullOrdering() != null) {
+ if (!supportsNullOrdering) {
+ if (symbol.getNullOrdering() == NullOrdering.FIRST) {
+ if (defaultNullOrder != NullOrder.FIRST && !(symbol.isAscending()
&& defaultNullOrder == NullOrder.LOW)
+ && !(!symbol.isAscending() && defaultNullOrder ==
NullOrder.HIGH)) {
+ return false;
+ }
+ } else if (defaultNullOrder != NullOrder.LAST &&
!(symbol.isAscending() && defaultNullOrder == NullOrder.HIGH)
+ && !(!symbol.isAscending() && defaultNullOrder ==
NullOrder.LOW)) {
+ return false;
+ }
+ symbol.setNullOrdering(null);
+ }
+ } else if (supportsNullOrdering && defaultNullOrder != NullOrder.LOW)
{
+ //try to match the expected default of low
+ if (symbol.isAscending()) {
+ if (defaultNullOrder != NullOrder.FIRST) {
+ symbol.setNullOrdering(NullOrdering.FIRST);
+ }
+ } else if (defaultNullOrder != NullOrder.LAST) {
+ symbol.setNullOrdering(NullOrdering.LAST);
+ }
+ }
}
if (accessNode.getLastChild() != null) {
Modified:
trunk/engine/src/main/java/org/teiid/query/processor/relational/GroupingNode.java
===================================================================
---
trunk/engine/src/main/java/org/teiid/query/processor/relational/GroupingNode.java 2010-06-23
20:58:24 UTC (rev 2290)
+++
trunk/engine/src/main/java/org/teiid/query/processor/relational/GroupingNode.java 2010-06-23
21:16:17 UTC (rev 2291)
@@ -194,11 +194,10 @@
ElementSymbol element = new ElementSymbol("val");
//$NON-NLS-1$
element.setType(inputType);
filter.setElements(Arrays.asList(element));
- filter.setSortElements(filter.getElements());
functions[i] = filter;
} else if (aggSymbol.getOrderBy() != null) { //handle the xmlagg
case
int[] orderIndecies = new
int[aggSymbol.getOrderBy().getOrderByItems().size()];
- List<Boolean> aggSortTypes = new
ArrayList<Boolean>(orderIndecies.length);
+ List<OrderByItem> orderByItems = new
ArrayList<OrderByItem>(orderIndecies.length);
List<ElementSymbol> schema = new
ArrayList<ElementSymbol>(orderIndecies.length + 1);
ElementSymbol element = new ElementSymbol("val");
//$NON-NLS-1$
element.setType(inputType);
@@ -206,16 +205,17 @@
for (ListIterator<OrderByItem> iterator =
aggSymbol.getOrderBy().getOrderByItems().listIterator(); iterator.hasNext();) {
OrderByItem item = iterator.next();
orderIndecies[iterator.previousIndex()] =
collectExpression(item.getSymbol());
- aggSortTypes.add(item.isAscending());
element = new
ElementSymbol(String.valueOf(iterator.previousIndex()));
element.setType(inputType);
schema.add(element);
+ OrderByItem newItem = item.clone();
+ newItem.setSymbol(element);
+ orderByItems.add(newItem);
}
SortingFilter filter = new SortingFilter(functions[i],
getBufferManager(), getConnectionID(), false);
- filter.setSortTypes(aggSortTypes);
filter.setIndecies(orderIndecies);
filter.setElements(schema);
- filter.setSortElements(schema.subList(1, schema.size()));
+ filter.setSortItems(orderByItems);
functions[i] = filter;
}
functions[i].setExpressionIndex(index);
Modified:
trunk/engine/src/main/java/org/teiid/query/processor/relational/ListNestedSortComparator.java
===================================================================
---
trunk/engine/src/main/java/org/teiid/query/processor/relational/ListNestedSortComparator.java 2010-06-23
20:58:24 UTC (rev 2290)
+++
trunk/engine/src/main/java/org/teiid/query/processor/relational/ListNestedSortComparator.java 2010-06-23
21:16:17 UTC (rev 2291)
@@ -24,9 +24,8 @@
import java.util.List;
-import org.teiid.core.util.Assertion;
+import org.teiid.language.SortSpecification.NullOrdering;
-
/**
* This class can be used for comparing lists of elements, when the fields to
* be sorted on and the comparison mechanism are dynamically specified. <p>
@@ -56,7 +55,7 @@
* { "a2", "b2", "c2" }
* </pre>
*/
-public class ListNestedSortComparator implements java.util.Comparator,
java.io.Serializable {
+public class ListNestedSortComparator<T extends Comparable<? super T>>
implements java.util.Comparator<List<T>> {
/**
* Specifies which fields to sort on.
@@ -72,10 +71,12 @@
/**
* List of booleans indicating the order in which each column should be sorted
*/
- private List orderTypes = null;
+ private List<Boolean> orderTypes = null;
private boolean isDistinct = true;
private int distinctIndex;
+
+ private List<NullOrdering> nullOrdering;
/**
* Constructs an instance of this class given the indicies of the parameters
@@ -101,7 +102,7 @@
* to sort on, and orderList used to determine the order in which each column
* is sorted.
*/
- public ListNestedSortComparator( int[] sortParameters, List orderTypes ) {
+ public ListNestedSortComparator( int[] sortParameters, List<Boolean> orderTypes
) {
this.sortParameters = sortParameters;
this.orderTypes = orderTypes;
}
@@ -113,6 +114,10 @@
public void setDistinctIndex(int distinctIndex) {
this.distinctIndex = distinctIndex;
}
+
+ public void setNullOrdering(List<NullOrdering> nullOrdering) {
+ this.nullOrdering = nullOrdering;
+ }
/**
* Compares its two arguments for order. Returns a negative integer,
@@ -129,14 +134,12 @@
* @param o1 The first object being compared
* @param o2 The second object being compared
*/
- public int compare( Object o1, Object o2 ) {
- List list1 = (List)o1;
- List list2 = (List)o2;
-
+
+ public int compare(java.util.List<T> list1, java.util.List<T> list2) {
int compare = 0;
for (int k = 0; k < sortParameters.length; k++) {
- Object param1 = list1.get(sortParameters[k]);
- Object param2 = list2.get(sortParameters[k]);
+ T param1 = list1.get(sortParameters[k]);
+ T param2 = list2.get(sortParameters[k]);
if( param1 == null ) {
if(param2 == null ) {
@@ -145,17 +148,29 @@
} else {
// param1 = null, so is less than a non-null
compare = -1;
+ NullOrdering no = getNullOrdering(k);
+ if (no != null) {
+ if (nullOrdering.get(k) == NullOrdering.FIRST) {
+ return -1;
+ }
+ return 1;
+ }
}
- } else if( param2 == null ) {
+ } else if( param2 == null ) {
// param1 != null, param2 == null
compare = 1;
- } else if ( param1 instanceof Comparable ) {
- compare = ((Comparable)param1).compareTo(param2);
- } else {
- Assertion.failed("Expected comparable types"); //$NON-NLS-1$
- }
+ NullOrdering no = getNullOrdering(k);
+ if (no != null) {
+ if (nullOrdering.get(k) == NullOrdering.FIRST) {
+ return 1;
+ }
+ return -1;
+ }
+ } else {
+ compare = param1.compareTo(param2);
+ }
if (compare != 0) {
- boolean asc = orderTypes !=
null?((Boolean)orderTypes.get(k)).booleanValue():this.ascendingOrder;
+ boolean asc = orderTypes != null?orderTypes.get(k):this.ascendingOrder;
return asc ? compare : -compare;
} else if (k == distinctIndex) {
isDistinct = false;
@@ -164,5 +179,12 @@
return 0;
}
+ private NullOrdering getNullOrdering(int index) {
+ if (nullOrdering != null) {
+ return nullOrdering.get(index);
+ }
+ return null;
+ }
+
} // END CLASS
Modified: trunk/engine/src/main/java/org/teiid/query/processor/relational/SortNode.java
===================================================================
---
trunk/engine/src/main/java/org/teiid/query/processor/relational/SortNode.java 2010-06-23
20:58:24 UTC (rev 2290)
+++
trunk/engine/src/main/java/org/teiid/query/processor/relational/SortNode.java 2010-06-23
21:16:17 UTC (rev 2291)
@@ -24,7 +24,6 @@
import static org.teiid.query.analysis.AnalysisRecord.*;
-import java.util.ArrayList;
import java.util.List;
import org.teiid.client.plan.PlanNode;
@@ -36,13 +35,12 @@
import org.teiid.core.TeiidProcessingException;
import org.teiid.query.processor.BatchIterator;
import org.teiid.query.processor.relational.SortUtility.Mode;
-import org.teiid.query.sql.lang.OrderBy;
+import org.teiid.query.sql.lang.OrderByItem;
public class SortNode extends RelationalNode {
- private List sortElements;
- private List<Boolean> sortTypes;
+ private List<OrderByItem> items;
private Mode mode = Mode.SORT;
private SortUtility sortUtility;
@@ -65,13 +63,12 @@
outputTs = null;
}
- public void setSortElements(List sortElements, List<Boolean> sortTypes) {
- this.sortElements = sortElements;
- this.sortTypes = sortTypes;
+ public void setSortElements(List<OrderByItem> items) {
+ this.items = items;
}
- public List getSortElements() {
- return this.sortElements;
+ public List<OrderByItem> getSortElements() {
+ return this.items;
}
public Mode getMode() {
@@ -93,8 +90,7 @@
private void sortPhase() throws BlockedException, TeiidComponentException,
TeiidProcessingException {
if (this.sortUtility == null) {
- this.sortUtility = new SortUtility(new BatchIterator(getChildren()[0]),
sortElements,
- sortTypes, this.mode, getBufferManager(),
+ this.sortUtility = new SortUtility(new BatchIterator(getChildren()[0]), items,
this.mode, getBufferManager(),
getConnectionID());
}
this.output = this.sortUtility.sort();
@@ -141,14 +137,13 @@
super.getNodeString(str);
str.append("[").append(mode).append("] "); //$NON-NLS-1$
//$NON-NLS-2$
if (this.mode != Mode.DUP_REMOVE) {
- str.append(sortElements);
+ str.append(this.items);
}
}
protected void copy(SortNode source, SortNode target){
super.copy(source, target);
- target.sortElements = source.sortElements;
- target.sortTypes = source.sortTypes;
+ target.items = source.items;
target.mode = source.mode;
}
@@ -162,18 +157,8 @@
public PlanNode getDescriptionProperties() {
PlanNode props = super.getDescriptionProperties();
- if(this.mode != Mode.DUP_REMOVE && this.sortElements != null) {
- Boolean ASC_B = Boolean.valueOf(OrderBy.ASC);
- List<String> cols = new
ArrayList<String>(this.sortElements.size());
- for(int i=0; i<this.sortElements.size(); i++) {
- String elemName = this.sortElements.get(i).toString();
- if(this.sortTypes.get(i).equals(ASC_B)) {
- cols.add(elemName + " ASC"); //$NON-NLS-1$
- } else {
- cols.add(elemName + " DESC"); //$NON-NLS-1$
- }
- }
- props.addProperty(PROP_SORT_COLS, cols);
+ if(this.mode != Mode.DUP_REMOVE && this.items != null) {
+ props.addProperty(PROP_SORT_COLS, this.items.toString());
}
props.addProperty(PROP_SORT_MODE, this.mode.toString());
Modified:
trunk/engine/src/main/java/org/teiid/query/processor/relational/SortUtility.java
===================================================================
---
trunk/engine/src/main/java/org/teiid/query/processor/relational/SortUtility.java 2010-06-23
20:58:24 UTC (rev 2290)
+++
trunk/engine/src/main/java/org/teiid/query/processor/relational/SortUtility.java 2010-06-23
21:16:17 UTC (rev 2291)
@@ -39,11 +39,12 @@
import org.teiid.core.TeiidComponentException;
import org.teiid.core.TeiidProcessingException;
import org.teiid.core.util.Assertion;
+import org.teiid.language.SortSpecification.NullOrdering;
import org.teiid.logging.LogManager;
import org.teiid.logging.MessageLevel;
import org.teiid.query.sql.lang.OrderBy;
+import org.teiid.query.sql.lang.OrderByItem;
import org.teiid.query.sql.symbol.Expression;
-import org.teiid.query.sql.symbol.SingleElementSymbol;
/**
@@ -106,7 +107,7 @@
private static final int DONE = 3;
private Collection<List<?>> workingTuples;
- public SortUtility(TupleSource sourceID, List sortElements, List<Boolean>
sortTypes, Mode mode, BufferManager bufferMgr,
+ public SortUtility(TupleSource sourceID, List<OrderByItem> items, Mode mode,
BufferManager bufferMgr,
String groupName) {
this.source = sourceID;
this.mode = mode;
@@ -114,34 +115,49 @@
this.groupName = groupName;
this.schema = this.source.getSchema();
this.schemaSize = bufferManager.getSchemaSize(this.schema);
- int distinctIndex = sortElements != null? sortElements.size() - 1:0;
- if (mode != Mode.SORT) {
- if (sortElements == null) {
- sortElements = this.schema;
- sortTypes = Collections.nCopies(sortElements.size(), OrderBy.ASC);
- } else if (sortElements.size() < schema.size()) {
- sortElements = new ArrayList(sortElements);
+ int distinctIndex = items != null? items.size() - 1:0;
+ List<Expression> sortElements = null;
+ List<Boolean> sortTypes = null;
+ List<NullOrdering> nullOrderings = null;
+ if (items == null) {
+ sortElements = (List<Expression>) this.schema;
+ sortTypes = Collections.nCopies(sortElements.size(), OrderBy.ASC);
+ } else {
+ sortElements = new ArrayList(items.size());
+ sortTypes = new ArrayList<Boolean>(items.size());
+ nullOrderings = new ArrayList<NullOrdering>(items.size());
+ for (OrderByItem orderByItem : items) {
+ sortElements.add(orderByItem.getSymbol());
+ sortTypes.add(orderByItem.isAscending());
+ nullOrderings.add(orderByItem.getNullOrdering());
+ }
+ if (items.size() < schema.size() && mode != Mode.SORT) {
List<Expression> toAdd = new ArrayList<Expression>(schema);
toAdd.removeAll(sortElements);
sortElements.addAll(toAdd);
- sortTypes = new ArrayList<Boolean>(sortTypes);
sortTypes.addAll(Collections.nCopies(sortElements.size() - sortTypes.size(),
OrderBy.ASC));
- }
+ nullOrderings.addAll(Collections.nCopies(sortElements.size() -
nullOrderings.size(), (NullOrdering)null));
+ }
}
int[] cols = new int[sortElements.size()];
-
- for (ListIterator<SingleElementSymbol> iter = sortElements.listIterator();
iter.hasNext();) {
- SingleElementSymbol elem = iter.next();
+ for (ListIterator<Expression> iter = sortElements.listIterator();
iter.hasNext();) {
+ Expression elem = iter.next();
cols[iter.previousIndex()] = schema.indexOf(elem);
Assertion.assertTrue(cols[iter.previousIndex()] != -1);
}
this.comparator = new ListNestedSortComparator(cols, sortTypes);
this.comparator.setDistinctIndex(distinctIndex);
+ this.comparator.setNullOrdering(nullOrderings);
}
- public boolean isDone() {
+ public SortUtility(TupleSource ts, List expressions, List<Boolean> types,
+ Mode mode, BufferManager bufferManager, String connectionID) {
+ this(ts, new OrderBy(expressions, types).getOrderByItems(), mode, bufferManager,
connectionID);
+ }
+
+ public boolean isDone() {
return this.doneReading && this.phase == DONE;
}
Modified:
trunk/engine/src/main/java/org/teiid/query/processor/relational/SortingFilter.java
===================================================================
---
trunk/engine/src/main/java/org/teiid/query/processor/relational/SortingFilter.java 2010-06-23
20:58:24 UTC (rev 2290)
+++
trunk/engine/src/main/java/org/teiid/query/processor/relational/SortingFilter.java 2010-06-23
21:16:17 UTC (rev 2291)
@@ -35,6 +35,7 @@
import org.teiid.core.TeiidProcessingException;
import org.teiid.query.function.aggregate.AggregateFunction;
import org.teiid.query.processor.relational.SortUtility.Mode;
+import org.teiid.query.sql.lang.OrderByItem;
/**
*/
@@ -49,8 +50,7 @@
// Derived and static - can be reused
private List elements;
- private List sortTypes;
- private List sortElements;
+ private List<OrderByItem> sortItems;
private int[] indecies = NO_INDECIES;
@@ -78,16 +78,12 @@
this.elements = elements;
}
- public void setSortTypes(List sortTypes) {
- this.sortTypes = sortTypes;
- }
-
public void setIndecies(int[] indecies) {
this.indecies = indecies;
}
- public void setSortElements(List sortElements) {
- this.sortElements = sortElements;
+ public void setSortItems(List<OrderByItem> sortItems) {
+ this.sortItems = sortItems;
}
/**
@@ -138,7 +134,7 @@
// Sort
if (sortUtility == null) {
- sortUtility = new SortUtility(collectionBuffer.createIndexedTupleSource(),
sortElements, sortTypes, removeDuplicates?Mode.DUP_REMOVE_SORT:Mode.SORT, mgr,
groupName);
+ sortUtility = new SortUtility(collectionBuffer.createIndexedTupleSource(),
sortItems, removeDuplicates?Mode.DUP_REMOVE_SORT:Mode.SORT, mgr, groupName);
}
TupleBuffer sorted = sortUtility.sort();
sorted.setForwardOnly(true);
Modified: trunk/engine/src/main/java/org/teiid/query/sql/lang/OrderBy.java
===================================================================
--- trunk/engine/src/main/java/org/teiid/query/sql/lang/OrderBy.java 2010-06-23 20:58:24
UTC (rev 2290)
+++ trunk/engine/src/main/java/org/teiid/query/sql/lang/OrderBy.java 2010-06-23 21:16:17
UTC (rev 2291)
@@ -136,10 +136,6 @@
visitor.visit(this);
}
- // =========================================================================
- // O V E R R I D D E N O B J E C T M E T H O D S
- // =========================================================================
-
/**
* Return deep copy of this ORDER BY clause.
*/
Modified: trunk/engine/src/main/java/org/teiid/query/sql/lang/OrderByItem.java
===================================================================
--- trunk/engine/src/main/java/org/teiid/query/sql/lang/OrderByItem.java 2010-06-23
20:58:24 UTC (rev 2290)
+++ trunk/engine/src/main/java/org/teiid/query/sql/lang/OrderByItem.java 2010-06-23
21:16:17 UTC (rev 2291)
@@ -22,6 +22,7 @@
package org.teiid.query.sql.lang;
+import org.teiid.language.SortSpecification.NullOrdering;
import org.teiid.query.sql.LanguageObject;
import org.teiid.query.sql.LanguageVisitor;
import org.teiid.query.sql.symbol.SingleElementSymbol;
@@ -34,6 +35,7 @@
private Integer expressionPosition; //set during resolving to the select clause
position
private boolean ascending = true;
private SingleElementSymbol symbol;
+ private NullOrdering nullOrdering;
public OrderByItem(SingleElementSymbol symbol, boolean ascending) {
this.symbol = symbol;
@@ -63,6 +65,14 @@
public void setSymbol(SingleElementSymbol symbol) {
this.symbol = symbol;
}
+
+ public NullOrdering getNullOrdering() {
+ return nullOrdering;
+ }
+
+ public void setNullOrdering(NullOrdering nullOrdering) {
+ this.nullOrdering = nullOrdering;
+ }
/**
*
@@ -81,6 +91,7 @@
public OrderByItem clone() {
OrderByItem clone = new OrderByItem((SingleElementSymbol)this.symbol.clone(),
ascending);
clone.expressionPosition = this.expressionPosition;
+ clone.nullOrdering = this.nullOrdering;
return clone;
}
@@ -93,7 +104,7 @@
return false;
}
OrderByItem o = (OrderByItem)obj;
- return o.symbol.equals(symbol) && o.ascending == this.ascending;
+ return o.symbol.equals(symbol) && o.ascending == this.ascending &&
o.nullOrdering == this.nullOrdering;
}
@Override
Modified: trunk/engine/src/main/java/org/teiid/query/sql/visitor/SQLStringVisitor.java
===================================================================
---
trunk/engine/src/main/java/org/teiid/query/sql/visitor/SQLStringVisitor.java 2010-06-23
20:58:24 UTC (rev 2290)
+++
trunk/engine/src/main/java/org/teiid/query/sql/visitor/SQLStringVisitor.java 2010-06-23
21:16:17 UTC (rev 2291)
@@ -36,7 +36,6 @@
import org.teiid.language.SQLConstants;
import org.teiid.language.SQLConstants.NonReserved;
import org.teiid.language.SQLConstants.Tokens;
-import org.teiid.query.function.FunctionLibrary;
import org.teiid.query.sql.LanguageObject;
import org.teiid.query.sql.LanguageVisitor;
import org.teiid.query.sql.lang.AtomicCriteria;
@@ -127,7 +126,6 @@
import org.teiid.query.sql.symbol.XMLNamespaces.NamespaceItem;
import org.teiid.translator.SourceSystemFunctions;
-
/**
* <p>The SQLStringVisitor will visit a set of language objects and return the
* corresponding SQL string representation. </p>
@@ -726,6 +724,12 @@
parts.add(SPACE);
parts.add(DESC);
} // Don't print default "ASC"
+ if (obj.getNullOrdering() != null) {
+ parts.add(SPACE);
+ parts.add(NonReserved.NULLS);
+ parts.add(SPACE);
+ parts.add(obj.getNullOrdering().name());
+ }
}
public void visit(DynamicCommand obj) {
Modified: trunk/engine/src/main/javacc/org/teiid/query/parser/SQLParser.jj
===================================================================
--- trunk/engine/src/main/javacc/org/teiid/query/parser/SQLParser.jj 2010-06-23 20:58:24
UTC (rev 2290)
+++ trunk/engine/src/main/javacc/org/teiid/query/parser/SQLParser.jj 2010-06-23 21:16:17
UTC (rev 2291)
@@ -2741,38 +2741,45 @@
*/
OrderBy orderby(ParseInfo info) :
{
- SingleElementSymbol ex = null;
- Token type = null;
OrderBy orderby = new OrderBy();
- boolean ascending = true;
+ OrderByItem item = null;
}
{
<ORDER> <BY>
- ex=sortKey(info) [<ASC> | type=<DESC>]
+ item = sortSpecification(info)
{
- ascending = true;
- if(type != null) {
- ascending = false;
- type=null;
- }
- orderby.addVariable(ex, ascending);
+ orderby.getOrderByItems().add(item);
}
(<COMMA>
- ex=sortKey(info) [<ASC> | type=<DESC>]
+ item = sortSpecification(info)
{
- ascending = true;
- if(type != null) {
- ascending = false;
- type=null;
- }
- orderby.addVariable(ex, ascending);
- }
+ orderby.getOrderByItems().add(item);
+ }
)*
{
return orderby;
}
}
+OrderByItem sortSpecification(ParseInfo info) :
+{
+ SingleElementSymbol ex = null;
+ boolean ascending = true;
+ String nullOrdering = null;
+}
+{
+ ex=sortKey(info)
+ [<ASC> | <DESC> {ascending=false;}]
+ [nonReserved("NULLS") nullOrdering = nonReserved("FIRST",
"LAST")]
+ {
+ OrderByItem item = new OrderByItem(ex, ascending);
+ if (nullOrdering != null) {
+
item.setNullOrdering(org.teiid.language.SortSpecification.NullOrdering.valueOf(nullOrdering.toUpperCase()));
+ }
+ return item;
+ }
+}
+
SingleElementSymbol sortKey(ParseInfo info) :
{
Expression ex = null;
Modified: trunk/engine/src/test/java/org/teiid/query/optimizer/TestRuleMergeVirtual.java
===================================================================
---
trunk/engine/src/test/java/org/teiid/query/optimizer/TestRuleMergeVirtual.java 2010-06-23
20:58:24 UTC (rev 2290)
+++
trunk/engine/src/test/java/org/teiid/query/optimizer/TestRuleMergeVirtual.java 2010-06-23
21:16:17 UTC (rev 2291)
@@ -304,7 +304,7 @@
new String[] {"SELECT g_0.e1 FROM pm1.g1 AS g_0"}, capFinder,
TestOptimizer.ComparisonMode.EXACT_COMMAND_STRING); //$NON-NLS-1$
SortNode node = (SortNode)plan.getRootNode();
- assertTrue("Alias was not accounted for in sort node",
node.getElements().containsAll(node.getSortElements())); //$NON-NLS-1$
+ assertTrue("Alias was not accounted for in sort node",
node.getElements().get(0).equals(node.getSortElements().get(0).getSymbol()));
//$NON-NLS-1$
}
@Test public void testMergeImplicitGroupBy() throws Exception {
Modified: trunk/engine/src/test/java/org/teiid/query/parser/TestParser.java
===================================================================
--- trunk/engine/src/test/java/org/teiid/query/parser/TestParser.java 2010-06-23 20:58:24
UTC (rev 2290)
+++ trunk/engine/src/test/java/org/teiid/query/parser/TestParser.java 2010-06-23 21:16:17
UTC (rev 2291)
@@ -39,6 +39,7 @@
import org.teiid.core.TeiidException;
import org.teiid.core.types.DataTypeManager;
import org.teiid.language.SQLConstants.Reserved;
+import org.teiid.language.SortSpecification.NullOrdering;
import org.teiid.query.sql.lang.BetweenCriteria;
import org.teiid.query.sql.lang.Command;
import org.teiid.query.sql.lang.CompareCriteria;
@@ -61,6 +62,7 @@
import org.teiid.query.sql.lang.MatchCriteria;
import org.teiid.query.sql.lang.NotCriteria;
import org.teiid.query.sql.lang.OrderBy;
+import org.teiid.query.sql.lang.OrderByItem;
import org.teiid.query.sql.lang.PredicateCriteria;
import org.teiid.query.sql.lang.Query;
import org.teiid.query.sql.lang.SPParameter;
@@ -2643,30 +2645,18 @@
/** SELECT a FROM db.g WHERE b = aString order by c desc*/
@Test public void testOrderByDesc(){
- GroupSymbol g = new GroupSymbol("db.g"); //$NON-NLS-1$
- From from = new From();
- from.addGroup(g);
-
- Select select = new Select();
- ElementSymbol a = new ElementSymbol("a"); //$NON-NLS-1$
- select.addSymbol(a);
-
- Criteria crit = new CompareCriteria(new ElementSymbol("b"),
CompareCriteria.EQ, new ElementSymbol("aString")); //$NON-NLS-1$ //$NON-NLS-2$
-
ArrayList elements = new ArrayList();
elements.add(new ElementSymbol("c")); //$NON-NLS-1$
ArrayList orderTypes = new ArrayList();
orderTypes.add(Boolean.FALSE);
OrderBy orderBy = new OrderBy(elements, orderTypes);
-
- Query query = new Query(select, from, crit, orderBy, null);
+
+ Query query = getOrderByQuery(orderBy);
helpTest("SELECT a FROM db.g WHERE b = aString ORDER BY c desc",
//$NON-NLS-1$
"SELECT a FROM db.g WHERE b = aString ORDER BY c DESC", //$NON-NLS-1$
query);
- }
-
- /** SELECT a FROM db.g WHERE b = aString order by c,d*/
- @Test public void testOrderBys(){
+ }
+ private Query getOrderByQuery(OrderBy orderBy) {
GroupSymbol g = new GroupSymbol("db.g"); //$NON-NLS-1$
From from = new From();
from.addGroup(g);
@@ -2677,12 +2667,18 @@
Criteria crit = new CompareCriteria(new ElementSymbol("b"),
CompareCriteria.EQ, new ElementSymbol("aString")); //$NON-NLS-1$ //$NON-NLS-2$
+ Query query = new Query(select, from, crit, orderBy, null);
+ return query;
+ }
+
+ /** SELECT a FROM db.g WHERE b = aString order by c,d*/
+ @Test public void testOrderBys(){
ArrayList elements = new ArrayList();
elements.add(new ElementSymbol("c")); //$NON-NLS-1$
elements.add(new ElementSymbol("d")); //$NON-NLS-1$
OrderBy orderBy = new OrderBy(elements);
- Query query = new Query(select, from, crit, orderBy, null);
+ Query query = getOrderByQuery(orderBy);
helpTest("SELECT a FROM db.g WHERE b = aString ORDER BY c,d", //$NON-NLS-1$
"SELECT a FROM db.g WHERE b = aString ORDER BY c, d", //$NON-NLS-1$
query);
@@ -2690,16 +2686,6 @@
/** SELECT a FROM db.g WHERE b = aString order by c desc,d desc*/
@Test public void testOrderBysDesc(){
- GroupSymbol g = new GroupSymbol("db.g"); //$NON-NLS-1$
- From from = new From();
- from.addGroup(g);
-
- Select select = new Select();
- ElementSymbol a = new ElementSymbol("a"); //$NON-NLS-1$
- select.addSymbol(a);
-
- Criteria crit = new CompareCriteria(new ElementSymbol("b"),
CompareCriteria.EQ, new ElementSymbol("aString")); //$NON-NLS-1$ //$NON-NLS-2$
-
ArrayList elements = new ArrayList();
elements.add(new ElementSymbol("c")); //$NON-NLS-1$
elements.add(new ElementSymbol("d")); //$NON-NLS-1$
@@ -2708,7 +2694,7 @@
orderTypes.add(Boolean.FALSE);
OrderBy orderBy = new OrderBy(elements, orderTypes);
- Query query = new Query(select, from, crit, orderBy, null);
+ Query query = getOrderByQuery(orderBy);
helpTest("SELECT a FROM db.g WHERE b = aString ORDER BY c desc,d desc",
//$NON-NLS-1$
"SELECT a FROM db.g WHERE b = aString ORDER BY c DESC, d DESC",
//$NON-NLS-1$
query);
@@ -2716,16 +2702,6 @@
/** SELECT a FROM db.g WHERE b = aString order by c desc,d*/
@Test public void testMixedOrderBys(){
- GroupSymbol g = new GroupSymbol("db.g"); //$NON-NLS-1$
- From from = new From();
- from.addGroup(g);
-
- Select select = new Select();
- ElementSymbol a = new ElementSymbol("a"); //$NON-NLS-1$
- select.addSymbol(a);
-
- Criteria crit = new CompareCriteria(new ElementSymbol("b"),
CompareCriteria.EQ, new ElementSymbol("aString")); //$NON-NLS-1$ //$NON-NLS-2$
-
ArrayList elements = new ArrayList();
elements.add(new ElementSymbol("c")); //$NON-NLS-1$
elements.add(new ElementSymbol("d")); //$NON-NLS-1$
@@ -2734,12 +2710,27 @@
orderTypes.add(Boolean.TRUE);
OrderBy orderBy = new OrderBy(elements, orderTypes);
- Query query = new Query(select, from, crit, orderBy, null);
+ Query query = getOrderByQuery(orderBy);
helpTest("SELECT a FROM db.g WHERE b = aString ORDER BY c desc,d",
//$NON-NLS-1$
"SELECT a FROM db.g WHERE b = aString ORDER BY c DESC, d", //$NON-NLS-1$
query);
}
+
+ @Test public void testOrderByNullOrdering(){
+ OrderBy orderBy = new OrderBy();
+ OrderByItem item = new OrderByItem(new ElementSymbol("c"), true);
+ item.setNullOrdering(NullOrdering.FIRST);
+ orderBy.getOrderByItems().add(item);
+ item = new OrderByItem(new ElementSymbol("d"), false);
+ item.setNullOrdering(NullOrdering.LAST);
+ orderBy.getOrderByItems().add(item);
+ Query query = getOrderByQuery(orderBy);
+ helpTest("SELECT a FROM db.g WHERE b = aString ORDER BY c NULLS FIRST,d desc nulls
last", //$NON-NLS-1$
+ "SELECT a FROM db.g WHERE b = aString ORDER BY c NULLS FIRST, d DESC NULLS
LAST", //$NON-NLS-1$
+ query);
+ }
+
// ================================== match ====================================
/** SELECT a FROM db.g WHERE b LIKE 'aString'*/
Added: trunk/engine/src/test/java/org/teiid/query/processor/TestOrderByProcessing.java
===================================================================
--- trunk/engine/src/test/java/org/teiid/query/processor/TestOrderByProcessing.java
(rev 0)
+++
trunk/engine/src/test/java/org/teiid/query/processor/TestOrderByProcessing.java 2010-06-23
21:16:17 UTC (rev 2291)
@@ -0,0 +1,202 @@
+/*
+ * JBoss, Home of Professional Open Source.
+ * See the COPYRIGHT.txt file distributed with this work for information
+ * regarding copyright ownership. Some portions may be licensed
+ * to Red Hat, Inc. under one or more contributor license agreements.
+ *
+ * This library is free software; you can redistribute it and/or
+ * modify it under the terms of the GNU Lesser General Public
+ * License as published by the Free Software Foundation; either
+ * version 2.1 of the License, or (at your option) any later version.
+ *
+ * This library is distributed in the hope that it will be useful,
+ * but WITHOUT ANY WARRANTY; without even the implied warranty of
+ * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
+ * Lesser General Public License for more details.
+ *
+ * You should have received a copy of the GNU Lesser General Public
+ * License along with this library; if not, write to the Free Software
+ * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA
+ * 02110-1301 USA.
+ */
+
+package org.teiid.query.processor;
+
+import static org.teiid.query.processor.TestProcessor.*;
+
+import java.util.Arrays;
+import java.util.List;
+
+import org.junit.Test;
+import org.teiid.query.optimizer.TestOptimizer;
+import org.teiid.query.optimizer.capabilities.BasicSourceCapabilities;
+import org.teiid.query.optimizer.capabilities.FakeCapabilitiesFinder;
+import org.teiid.query.optimizer.capabilities.SourceCapabilities.Capability;
+import org.teiid.query.unittest.FakeMetadataFacade;
+import org.teiid.query.unittest.FakeMetadataFactory;
+import org.teiid.translator.ExecutionFactory.NullOrder;
+
+@SuppressWarnings({"nls", "unchecked"})
+public class TestOrderByProcessing {
+
+ @Test public void testOrderByDescAll() {
+ String sql = "SELECT distinct e1 from pm1.g2 order by e1 desc limit 1";
//$NON-NLS-1$
+
+ List[] expected = new List[] {
+ Arrays.asList("c"),
+ };
+
+ FakeDataManager dataManager = new FakeDataManager();
+ sampleData1(dataManager);
+
+ ProcessorPlan plan = helpGetPlan(helpParse(sql),
FakeMetadataFactory.example1Cached());
+
+ helpProcess(plan, dataManager, expected);
+ }
+
+ @Test public void testOrderByOutsideOfSelect() {
+ // Create query
+ String sql = "SELECT e1 FROM (select e1, e2 || e3 as e2 from pm1.g2) x order by
e2"; //$NON-NLS-1$
+
+ //a, a, null, c, b, a
+ // Create expected results
+ List[] expected = new List[] {
+ Arrays.asList("a"),
+ Arrays.asList("a"),
+ Arrays.asList((String)null),
+ Arrays.asList("c"),
+ Arrays.asList("b"),
+ Arrays.asList("a"),
+ };
+
+ // Construct data manager with data
+ FakeDataManager dataManager = new FakeDataManager();
+ sampleData1(dataManager);
+
+ // Plan query
+ ProcessorPlan plan = helpGetPlan(helpParse(sql),
FakeMetadataFactory.example1Cached(), TestOptimizer.getGenericFinder());
+
+ // Run query
+ helpProcess(plan, dataManager, expected);
+ }
+
+ @Test public void testOrderByUnrelatedExpression() {
+ String sql = "SELECT e1, e2 + 1 from pm1.g2 order by e3 || e2 limit 1";
//$NON-NLS-1$
+
+ List[] expected = new List[] {
+ Arrays.asList("a", 1),
+ };
+
+ FakeDataManager dataManager = new FakeDataManager();
+ sampleData1(dataManager);
+
+ ProcessorPlan plan = helpGetPlan(helpParse(sql),
FakeMetadataFactory.example1Cached(), TestOptimizer.getGenericFinder());
+
+ helpProcess(plan, dataManager, expected);
+ }
+
+ /**
+ * A control test to ensure that y will still exist for sorting
+ */
+ @Test public void testOrderByWithDuplicateExpressions() throws Exception {
+ String sql = "select e1 as x, e1 as y from pm1.g1 order by y ASC";
//$NON-NLS-1$
+
+ FakeMetadataFacade metadata = FakeMetadataFactory.example1Cached();
+
+ ProcessorPlan plan = helpGetPlan(helpParse(sql), metadata);
+
+ List[] expected = new List[] {
+ Arrays.asList(null, null),
+ Arrays.asList("a", "a"), //$NON-NLS-1$ //$NON-NLS-2$
+ Arrays.asList("a", "a"), //$NON-NLS-1$ //$NON-NLS-2$
+ Arrays.asList("a", "a"), //$NON-NLS-1$ //$NON-NLS-2$
+ Arrays.asList("b", "b"), //$NON-NLS-1$ //$NON-NLS-2$
+ Arrays.asList("c", "c"), //$NON-NLS-1$ //$NON-NLS-2$
+ };
+
+ FakeDataManager manager = new FakeDataManager();
+ sampleData1(manager);
+ helpProcess(plan, manager, expected);
+ }
+
+ @Test public void testExplicitNullOrdering() throws Exception {
+ String sql = "select e1, case when e4 = 2.0 then null else e4 end as x from pm1.g1
order by e1 ASC NULLS LAST, x DESC NULLS FIRST"; //$NON-NLS-1$
+
+ FakeMetadataFacade metadata = FakeMetadataFactory.example1Cached();
+ ProcessorPlan plan = helpGetPlan(helpParse(sql), metadata);
+
+ List[] expected = new List[] { Arrays.asList("a", null),
+ Arrays.asList("a", null), //$NON-NLS-1$
+ Arrays.asList("a", 7.0), //$NON-NLS-1$
+ Arrays.asList("b", 0.0), //$NON-NLS-1$
+ Arrays.asList("c", null), //$NON-NLS-1$
+ Arrays.asList(null, 1.0),
+ };
+
+ FakeDataManager manager = new FakeDataManager();
+ sampleData1(manager);
+ helpProcess(plan, manager, expected);
+ }
+
+ @Test public void testNullOrdering() throws Exception {
+ FakeCapabilitiesFinder capFinder = new FakeCapabilitiesFinder();
+ BasicSourceCapabilities caps = TestOptimizer.getTypicalCapabilities();
+ caps.setCapabilitySupport(Capability.QUERY_ORDERBY_NULL_ORDERING, true);
+ capFinder.addCapabilities("pm1", caps); //$NON-NLS-1$
+
+ ProcessorPlan plan = TestOptimizer.helpPlan("select e1 from pm1.g1 order by
e1 desc, e2 asc", //$NON-NLS-1$
+ FakeMetadataFactory.example1Cached(), null, capFinder,
+ new String[] { "SELECT g_0.e1 AS c_0 FROM pm1.g1 AS g_0 ORDER BY c_0 DESC
NULLS LAST, g_0.e2 NULLS FIRST"}, //$NON-NLS-1$
+ TestOptimizer.ComparisonMode.EXACT_COMMAND_STRING);
+
+ TestOptimizer.checkNodeTypes(plan, TestOptimizer.FULL_PUSHDOWN);
+ }
+
+ @Test public void testNullOrdering1() throws Exception {
+ FakeCapabilitiesFinder capFinder = new FakeCapabilitiesFinder();
+ BasicSourceCapabilities caps = TestOptimizer.getTypicalCapabilities();
+ caps.setCapabilitySupport(Capability.QUERY_ORDERBY_NULL_ORDERING, true);
+ caps.setSourceProperty(Capability.QUERY_ORDERBY_DEFAULT_NULL_ORDER,
NullOrder.LAST);
+ capFinder.addCapabilities("pm1", caps); //$NON-NLS-1$
+
+ ProcessorPlan plan = TestOptimizer.helpPlan("select e1 from pm1.g1 order by
e1 desc, e2 asc", //$NON-NLS-1$
+ FakeMetadataFactory.example1Cached(), null, capFinder,
+ new String[] { "SELECT g_0.e1 AS c_0 FROM pm1.g1 AS g_0 ORDER BY c_0 DESC,
g_0.e2 NULLS FIRST"}, //$NON-NLS-1$
+ TestOptimizer.ComparisonMode.EXACT_COMMAND_STRING);
+
+ TestOptimizer.checkNodeTypes(plan, TestOptimizer.FULL_PUSHDOWN);
+ }
+
+ @Test public void testNullOrdering2() throws Exception {
+ FakeCapabilitiesFinder capFinder = new FakeCapabilitiesFinder();
+ BasicSourceCapabilities caps = TestOptimizer.getTypicalCapabilities();
+ caps.setCapabilitySupport(Capability.QUERY_ORDERBY_NULL_ORDERING, true);
+ capFinder.addCapabilities("pm1", caps); //$NON-NLS-1$
+
+ ProcessorPlan plan = TestOptimizer.helpPlan("select e1 from pm1.g1 order by
e1 desc NULLS FIRST, e2 asc NULLS LAST", //$NON-NLS-1$
+ FakeMetadataFactory.example1Cached(), null, capFinder,
+ new String[] { "SELECT g_0.e1 AS c_0 FROM pm1.g1 AS g_0 ORDER BY c_0 DESC
NULLS FIRST, g_0.e2 NULLS LAST"}, //$NON-NLS-1$
+ TestOptimizer.ComparisonMode.EXACT_COMMAND_STRING);
+
+ TestOptimizer.checkNodeTypes(plan, TestOptimizer.FULL_PUSHDOWN);
+ }
+
+ /**
+ * The engine will remove the null ordering if it's not needed
+ * @throws Exception
+ */
+ @Test public void testNullOrdering3() throws Exception {
+ FakeCapabilitiesFinder capFinder = new FakeCapabilitiesFinder();
+ BasicSourceCapabilities caps = TestOptimizer.getTypicalCapabilities();
+ caps.setSourceProperty(Capability.QUERY_ORDERBY_DEFAULT_NULL_ORDER,
NullOrder.HIGH);
+ capFinder.addCapabilities("pm1", caps); //$NON-NLS-1$
+
+ ProcessorPlan plan = TestOptimizer.helpPlan("select e1 from pm1.g1 order by
e1 desc, e2 asc NULLS LAST", //$NON-NLS-1$
+ FakeMetadataFactory.example1Cached(), null, capFinder,
+ new String[] { "SELECT g_0.e1 AS c_0 FROM pm1.g1 AS g_0 ORDER BY c_0 DESC,
g_0.e2"}, //$NON-NLS-1$
+ TestOptimizer.ComparisonMode.EXACT_COMMAND_STRING);
+
+ TestOptimizer.checkNodeTypes(plan, TestOptimizer.FULL_PUSHDOWN);
+ }
+
+}
Property changes on:
trunk/engine/src/test/java/org/teiid/query/processor/TestOrderByProcessing.java
___________________________________________________________________
Name: svn:mime-type
+ text/plain
Modified: trunk/engine/src/test/java/org/teiid/query/processor/TestProcessor.java
===================================================================
--- trunk/engine/src/test/java/org/teiid/query/processor/TestProcessor.java 2010-06-23
20:58:24 UTC (rev 2290)
+++ trunk/engine/src/test/java/org/teiid/query/processor/TestProcessor.java 2010-06-23
21:16:17 UTC (rev 2291)
@@ -7254,30 +7254,6 @@
}
/**
- * A control test to ensure that y will still exist for sorting
- */
- @Test public void testOrderByWithDuplicateExpressions() throws Exception {
- String sql = "select e1 as x, e1 as y from pm1.g1 order by y ASC";
//$NON-NLS-1$
-
- FakeMetadataFacade metadata = FakeMetadataFactory.example1Cached();
-
- ProcessorPlan plan = helpGetPlan(helpParse(sql), metadata);
-
- List[] expected = new List[] {
- Arrays.asList(null, null),
- Arrays.asList("a", "a"), //$NON-NLS-1$ //$NON-NLS-2$
- Arrays.asList("a", "a"), //$NON-NLS-1$ //$NON-NLS-2$
- Arrays.asList("a", "a"), //$NON-NLS-1$ //$NON-NLS-2$
- Arrays.asList("b", "b"), //$NON-NLS-1$ //$NON-NLS-2$
- Arrays.asList("c", "c"), //$NON-NLS-1$ //$NON-NLS-2$
- };
-
- FakeDataManager manager = new FakeDataManager();
- sampleData1(manager);
- helpProcess(plan, manager, expected);
- }
-
- /**
* This is a control test. It should work regardless of whether the reference is
aliased
* since accessnodes are now fully positional
*/
@@ -7368,47 +7344,6 @@
assertEquals(2, dataManager.getCommandHistory().size());
}
- @Test public void testOrderByOutsideOfSelect() {
- // Create query
- String sql = "SELECT e1 FROM (select e1, e2 || e3 as e2 from pm1.g2) x order
by e2"; //$NON-NLS-1$
-
- //a, a, null, c, b, a
- // Create expected results
- List[] expected = new List[] {
- Arrays.asList("a"),
- Arrays.asList("a"),
- Arrays.asList(new String[] {null}),
- Arrays.asList("c"),
- Arrays.asList("b"),
- Arrays.asList("a"),
- };
-
- // Construct data manager with data
- FakeDataManager dataManager = new FakeDataManager();
- sampleData1(dataManager);
-
- // Plan query
- ProcessorPlan plan = helpGetPlan(helpParse(sql),
FakeMetadataFactory.example1Cached(), TestOptimizer.getGenericFinder());
-
- // Run query
- helpProcess(plan, dataManager, expected);
- }
-
- @Test public void testOrderByUnrelatedExpression() {
- String sql = "SELECT e1, e2 + 1 from pm1.g2 order by e3 || e2 limit 1";
//$NON-NLS-1$
-
- List[] expected = new List[] {
- Arrays.asList("a", 1),
- };
-
- FakeDataManager dataManager = new FakeDataManager();
- sampleData1(dataManager);
-
- ProcessorPlan plan = helpGetPlan(helpParse(sql),
FakeMetadataFactory.example1Cached(), TestOptimizer.getGenericFinder());
-
- helpProcess(plan, dataManager, expected);
- }
-
/**
* Test a query that uses ambiguous alias names in the top level query and
* its sub-query and uses columns belonging to the alias as a parameter to a
@@ -7443,21 +7378,6 @@
helpProcess(plan, manager, expected);
}
- @Test public void testOrderByDescAll() {
- String sql = "SELECT distinct e1 from pm1.g2 order by e1 desc limit 1";
//$NON-NLS-1$
-
- List[] expected = new List[] {
- Arrays.asList("c"),
- };
-
- FakeDataManager dataManager = new FakeDataManager();
- sampleData1(dataManager);
-
- ProcessorPlan plan = helpGetPlan(helpParse(sql),
FakeMetadataFactory.example1Cached());
-
- helpProcess(plan, dataManager, expected);
- }
-
@Test public void testImplicitAggregateWithInlineView() {
String sql = "SELECT * FROM (SELECT b.count, enterprise_id FROM (SELECT
COUNT(*), 2 AS enterprise_id FROM (SELECT 'A Name' AS Name, 1 AS enterprise_id) c
) b ) a WHERE enterprise_id = 1"; //$NON-NLS-1$
Modified:
trunk/engine/src/test/java/org/teiid/query/processor/relational/TestDuplicateFilter.java
===================================================================
---
trunk/engine/src/test/java/org/teiid/query/processor/relational/TestDuplicateFilter.java 2010-06-23
20:58:24 UTC (rev 2290)
+++
trunk/engine/src/test/java/org/teiid/query/processor/relational/TestDuplicateFilter.java 2010-06-23
21:16:17 UTC (rev 2291)
@@ -56,7 +56,6 @@
ElementSymbol element = new ElementSymbol("val"); //$NON-NLS-1$
element.setType(dataType);
filter.setElements(Arrays.asList(element));
- filter.setSortElements(filter.getElements());
filter.reset();
// Add inputs
Modified:
trunk/engine/src/test/java/org/teiid/query/processor/relational/TestSortNode.java
===================================================================
---
trunk/engine/src/test/java/org/teiid/query/processor/relational/TestSortNode.java 2010-06-23
20:58:24 UTC (rev 2290)
+++
trunk/engine/src/test/java/org/teiid/query/processor/relational/TestSortNode.java 2010-06-23
21:16:17 UTC (rev 2291)
@@ -40,9 +40,7 @@
import org.teiid.core.TeiidComponentException;
import org.teiid.core.TeiidProcessingException;
import org.teiid.core.types.DataTypeManager;
-import org.teiid.query.processor.relational.ListNestedSortComparator;
-import org.teiid.query.processor.relational.SortNode;
-import org.teiid.query.processor.relational.SortUtility;
+import org.teiid.language.SortSpecification.NullOrdering;
import org.teiid.query.processor.relational.SortUtility.Mode;
import org.teiid.query.sql.lang.OrderBy;
import org.teiid.query.sql.symbol.ElementSymbol;
@@ -63,7 +61,7 @@
dataNode.initialize(context, mgr, null);
SortNode sortNode = new SortNode(1);
- sortNode.setSortElements(sortElements, sortTypes);
+ sortNode.setSortElements(new OrderBy(sortElements, sortTypes).getOrderByItems());
sortNode.setMode(mode);
sortNode.setElements(elements);
sortNode.addChild(dataNode);
@@ -133,11 +131,11 @@
int rows = batches * BATCH_SIZE;
- ListNestedSortComparator comparator = new ListNestedSortComparator(new int[] {0},
OrderBy.DESC);
+ ListNestedSortComparator<Integer> comparator = new
ListNestedSortComparator<Integer>(new int[] {0}, OrderBy.DESC);
- List[] expected = new List[rows];
- List[] data = new List[rows];
- TreeSet<List> distinct = new TreeSet<List>(comparator);
+ List<Integer>[] expected = new List[rows];
+ List<Integer>[] data = new List[rows];
+ TreeSet<List<Integer>> distinct = new
TreeSet<List<Integer>>(comparator);
for(int i=0; i<rows; i++) {
Integer value = new Integer((i*51) % 11);
data[i] = Arrays.asList(value);
@@ -158,6 +156,26 @@
helpTestSort(elements, data, sortElements, sortTypes,
mode==Mode.SORT?expected:expectedDistinct, mode);
}
}
+
+ @Test public void testComparatorNullOrdering() {
+ ListNestedSortComparator<Integer> comparator = new
ListNestedSortComparator<Integer>(new int[] {0}, OrderBy.DESC);
+ comparator.setNullOrdering(Arrays.asList(NullOrdering.FIRST));
+ List<Integer>[] data = new List[3];
+ data[0] = Arrays.asList(1);
+ data[1] = Arrays.asList((Integer)null);
+ data[2] = Arrays.asList(2);
+ Arrays.sort(data, comparator);
+ assertNull(data[0].get(0));
+ comparator.setNullOrdering(Arrays.asList(NullOrdering.LAST));
+ Arrays.sort(data, comparator);
+ assertNull(data[2].get(0));
+ comparator = new ListNestedSortComparator<Integer>(new int[] {0},
OrderBy.ASC);
+ Arrays.sort(data, comparator);
+ assertNull(data[0].get(0));
+ comparator.setNullOrdering(Arrays.asList(NullOrdering.LAST));
+ Arrays.sort(data, comparator);
+ assertNull(data[2].get(0));
+ }
@Test public void testNoSort() throws Exception {
helpTestAllSorts(0);
Modified:
trunk/test-integration/common/src/test/java/org/teiid/connector/visitor/util/TestSQLStringVisitor.java
===================================================================
---
trunk/test-integration/common/src/test/java/org/teiid/connector/visitor/util/TestSQLStringVisitor.java 2010-06-23
20:58:24 UTC (rev 2290)
+++
trunk/test-integration/common/src/test/java/org/teiid/connector/visitor/util/TestSQLStringVisitor.java 2010-06-23
21:16:17 UTC (rev 2291)
@@ -418,5 +418,12 @@
Command command =
FakeTranslationFactory.getInstance().getBQTTranslationUtility().parseCommand(sql, true,
true);
assertEquals("SELECT g_0.IntKey AS c_0 FROM SmallA AS g_0 ORDER BY c_0",
command.toString()); //$NON-NLS-1$
}
+
+ @Test public void testOrderByNullOrdering() throws Exception {
+ String sql = "select intkey as x from bqt1.smalla order by x nulls first";
//$NON-NLS-1$
+
+ Command command =
FakeTranslationFactory.getInstance().getBQTTranslationUtility().parseCommand(sql, true,
true);
+ assertEquals("SELECT g_0.IntKey AS c_0 FROM SmallA AS g_0 ORDER BY c_0 NULLS
FIRST", command.toString()); //$NON-NLS-1$
+ }
}
Modified:
trunk/test-integration/common/src/test/java/org/teiid/dqp/internal/process/TestTPCR.java
===================================================================
---
trunk/test-integration/common/src/test/java/org/teiid/dqp/internal/process/TestTPCR.java 2010-06-23
20:58:24 UTC (rev 2290)
+++
trunk/test-integration/common/src/test/java/org/teiid/dqp/internal/process/TestTPCR.java 2010-06-23
21:16:17 UTC (rev 2291)
@@ -63,7 +63,7 @@
Arrays.asList(new Object[] { new Double(3459808.0), new
BigDecimal("405838.6989"), TimestampUtil.createDate(95, 2, 4), new Double(0.0)
}), //$NON-NLS-1$
Arrays.asList(new Object[] { new Double(492164.0), new
BigDecimal("390324.0610"), TimestampUtil.createDate(95, 1, 19), new Double(0.0)
}) }; //$NON-NLS-1$
- dataMgr.addData("SELECT g_2.l_orderkey AS c_0, SUM((g_2.l_extendedprice * (1
- g_2.l_discount))) AS c_1, g_1.o_orderdate AS c_2, g_1.o_shippriority AS c_3 FROM
TPCR_Oracle_9i.CUSTOMER AS g_0, TPCR_Oracle_9i.ORDERS AS g_1, TPCR_Oracle_9i.LINEITEM AS
g_2 WHERE (g_0.c_custkey = g_1.o_custkey) AND (g_2.l_orderkey = g_1.o_orderkey) AND
(g_0.c_mktsegment = 'BUILDING') AND (g_1.o_orderdate < {d'1995-03-15'})
AND (g_2.l_shipdate > {ts'1995-03-15 00:00:00.0'}) GROUP BY g_2.l_orderkey,
g_1.o_orderdate, g_1.o_shippriority ORDER BY c_1 DESC, c_2", //$NON-NLS-1$
+ dataMgr.addData("SELECT g_2.l_orderkey AS c_0, SUM((g_2.l_extendedprice * (1
- g_2.l_discount))) AS c_1, g_1.o_orderdate AS c_2, g_1.o_shippriority AS c_3 FROM
TPCR_Oracle_9i.CUSTOMER AS g_0, TPCR_Oracle_9i.ORDERS AS g_1, TPCR_Oracle_9i.LINEITEM AS
g_2 WHERE (g_0.c_custkey = g_1.o_custkey) AND (g_2.l_orderkey = g_1.o_orderkey) AND
(g_0.c_mktsegment = 'BUILDING') AND (g_1.o_orderdate < {d'1995-03-15'})
AND (g_2.l_shipdate > {ts'1995-03-15 00:00:00.0'}) GROUP BY g_2.l_orderkey,
g_1.o_orderdate, g_1.o_shippriority ORDER BY c_1 DESC NULLS LAST, c_2 NULLS FIRST",
//$NON-NLS-1$
expected);
doProcess(METADATA,
@@ -121,7 +121,7 @@
new List[] { Arrays.asList(new Object[] { new Long(5), "Bill",
"101 Fake St.", "392839283", "21.12" } ), //$NON-NLS-1$
//$NON-NLS-2$ //$NON-NLS-3$ //$NON-NLS-4$
Arrays.asList(new Object[] { new Long(6), "Stu",
"102 Fake St.", "385729385", "51.50" } )}; //$NON-NLS-1$
//$NON-NLS-2$ //$NON-NLS-3$ //$NON-NLS-4$
- dataMgr.addData("SELECT g_0.C_CUSTKEY AS c_0, g_0.C_NAME AS c_1,
g_0.C_ADDRESS AS c_2, g_0.C_PHONE AS c_3, g_0.C_ACCTBAL AS c_4 FROM TPCR_Ora.CUSTOMER AS
g_0 WHERE g_0.C_ACCTBAL > 50 ORDER BY c_0", //$NON-NLS-1$
+ dataMgr.addData("SELECT g_0.C_CUSTKEY AS c_0, g_0.C_NAME AS c_1,
g_0.C_ADDRESS AS c_2, g_0.C_PHONE AS c_3, g_0.C_ACCTBAL AS c_4 FROM TPCR_Ora.CUSTOMER AS
g_0 WHERE g_0.C_ACCTBAL > 50 ORDER BY c_0 NULLS FIRST", //$NON-NLS-1$
oracleExpected);
List[] sqlServerExpected =
@@ -161,7 +161,7 @@
new List[] { Arrays.asList(new Object[] { new Long(5), "Bill",
"101 Fake St.", "392839283", "51.12" } ), //$NON-NLS-1$
//$NON-NLS-2$ //$NON-NLS-3$ //$NON-NLS-4$
Arrays.asList(new Object[] { new Long(6), "Stu",
"102 Fake St.", "385729385", "51.50" } )}; //$NON-NLS-1$
//$NON-NLS-2$ //$NON-NLS-3$ //$NON-NLS-4$
- dataMgr.addData("SELECT g_0.C_CUSTKEY AS c_0, g_0.C_NAME AS c_1,
g_0.C_ADDRESS AS c_2, g_0.C_PHONE AS c_3, g_0.C_ACCTBAL AS c_4 FROM TPCR_Ora.CUSTOMER AS
g_0 WHERE g_0.C_ACCTBAL > 50 ORDER BY c_0", //$NON-NLS-1$
+ dataMgr.addData("SELECT g_0.C_CUSTKEY AS c_0, g_0.C_NAME AS c_1,
g_0.C_ADDRESS AS c_2, g_0.C_PHONE AS c_3, g_0.C_ACCTBAL AS c_4 FROM TPCR_Ora.CUSTOMER AS
g_0 WHERE g_0.C_ACCTBAL > 50 ORDER BY c_0 NULLS FIRST", //$NON-NLS-1$
oracleExpected);
List[] sqlServerExpected =
@@ -191,7 +191,7 @@
ProcessorPlan plan = TestOptimizer.helpPlan("SELECT custsale.cntrycode,
COUNT(*) AS numcust, SUM(c_acctbal) AS totacctbal FROM (SELECT left(C_PHONE, 2) AS
cntrycode, CUSTOMER.C_ACCTBAL FROM CUSTOMER WHERE (left(C_PHONE, 2) IN
('13','31','23','29','30','18','17'))
AND (CUSTOMER.C_ACCTBAL > (SELECT AVG(CUSTOMER.C_ACCTBAL) FROM CUSTOMER WHERE
(CUSTOMER.C_ACCTBAL > 0.0) AND (left(C_PHONE, 2) IN
('13','31','23','29','30','18','17'))))
AND (NOT (EXISTS (SELECT * FROM ORDERS WHERE O_CUSTKEY = C_CUSTKEY)))) AS custsale GROUP
BY custsale.cntrycode ORDER BY custsale.cntrycode", //$NON-NLS-1$
METADATA, null, finder,
- new String[] {"SELECT v_0.c_0, COUNT(*) AS c_1, SUM(v_0.c_1) AS c_2 FROM
(SELECT left(g_0.C_PHONE, 2) AS c_0, g_0.C_ACCTBAL AS c_1 FROM TPCR_Oracle_9i.CUSTOMER AS
g_0 WHERE (left(g_0.C_PHONE, 2) IN ('13', '31', '23',
'29', '30', '18', '17')) AND (g_0.C_ACCTBAL > (SELECT
AVG(g_1.C_ACCTBAL) FROM TPCR_Oracle_9i.CUSTOMER AS g_1 WHERE (g_1.C_ACCTBAL > 0.0) AND
(left(g_1.C_PHONE, 2) IN ('13', '31', '23', '29',
'30', '18', '17')))) AND (NOT (EXISTS (SELECT g_2.O_ORDERKEY,
g_2.O_CUSTKEY, g_2.O_ORDERSTATUS, g_2.O_TOTALPRICE, g_2.O_ORDERDATE, g_2.O_ORDERPRIORITY,
g_2.O_CLERK, g_2.O_SHIPPRIORITY, g_2.O_COMMENT FROM TPCR_Oracle_9i.ORDERS AS g_2 WHERE
g_2.O_CUSTKEY = g_0.C_CUSTKEY)))) AS v_0 GROUP BY v_0.c_0 ORDER BY c_0"}, true);
//$NON-NLS-1$
+ new String[] {"SELECT v_0.c_0, COUNT(*) AS c_1, SUM(v_0.c_1) AS c_2 FROM
(SELECT left(g_0.C_PHONE, 2) AS c_0, g_0.C_ACCTBAL AS c_1 FROM TPCR_Oracle_9i.CUSTOMER AS
g_0 WHERE (left(g_0.C_PHONE, 2) IN ('13', '31', '23',
'29', '30', '18', '17')) AND (g_0.C_ACCTBAL > (SELECT
AVG(g_1.C_ACCTBAL) FROM TPCR_Oracle_9i.CUSTOMER AS g_1 WHERE (g_1.C_ACCTBAL > 0.0) AND
(left(g_1.C_PHONE, 2) IN ('13', '31', '23', '29',
'30', '18', '17')))) AND (NOT (EXISTS (SELECT g_2.O_ORDERKEY,
g_2.O_CUSTKEY, g_2.O_ORDERSTATUS, g_2.O_TOTALPRICE, g_2.O_ORDERDATE, g_2.O_ORDERPRIORITY,
g_2.O_CLERK, g_2.O_SHIPPRIORITY, g_2.O_COMMENT FROM TPCR_Oracle_9i.ORDERS AS g_2 WHERE
g_2.O_CUSTKEY = g_0.C_CUSTKEY)))) AS v_0 GROUP BY v_0.c_0 ORDER BY c_0 NULLS FIRST"},
true); //$NON-NLS-1$
TestOptimizer.checkNodeTypes(plan, TestOptimizer.FULL_PUSHDOWN);
}