Author: shawkins Date: 2011-08-05 14:33:39 -0400 (Fri, 05 Aug 2011) New Revision: 3364 Modified: trunk/api/src/main/java/org/teiid/language/Like.java trunk/api/src/main/java/org/teiid/language/SQLConstants.java trunk/api/src/main/java/org/teiid/language/visitor/SQLStringVisitor.java trunk/api/src/main/java/org/teiid/translator/ExecutionFactory.java trunk/build/kits/jboss-container/teiid-releasenotes.html 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/h2/H2ExecutionFactory.java trunk/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/mysql/MySQL5ExecutionFactory.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/connectors/translator-jdbc/src/test/java/org/teiid/translator/jdbc/postgresql/TestPostgreSQLTranslator.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/scalar_functions.xml trunk/documentation/reference/src/main/docbook/en-US/content/sql_support.xml trunk/engine/src/main/java/org/teiid/dqp/internal/datamgr/CapabilitiesConverter.java trunk/engine/src/main/java/org/teiid/dqp/internal/datamgr/LanguageBridgeFactory.java trunk/engine/src/main/java/org/teiid/query/eval/Evaluator.java trunk/engine/src/main/java/org/teiid/query/metadata/TempCapabilitiesFinder.java trunk/engine/src/main/java/org/teiid/query/optimizer/capabilities/SourceCapabilities.java trunk/engine/src/main/java/org/teiid/query/optimizer/relational/rules/CriteriaCapabilityValidatorVisitor.java trunk/engine/src/main/java/org/teiid/query/optimizer/relational/rules/NewCalculateCostUtil.java trunk/engine/src/main/java/org/teiid/query/rewriter/QueryRewriter.java trunk/engine/src/main/java/org/teiid/query/sql/lang/MatchCriteria.java trunk/engine/src/main/java/org/teiid/query/sql/visitor/SQLStringVisitor.java trunk/engine/src/main/java/org/teiid/query/tempdata/IndexInfo.java trunk/engine/src/main/javacc/org/teiid/query/parser/SQLParser.jj trunk/engine/src/test/java/org/teiid/query/parser/TestParser.java trunk/engine/src/test/java/org/teiid/query/processor/TestTempTables.java trunk/engine/src/test/java/org/teiid/query/processor/eval/TestExpressionEvaluator.java trunk/engine/src/test/java/org/teiid/query/sql/visitor/TestSQLStringVisitor.java Log: TEIID-1694 TEIID-1602 adding support for like_regex and extract function parsing. Modified: trunk/api/src/main/java/org/teiid/language/Like.java =================================================================== --- trunk/api/src/main/java/org/teiid/language/Like.java 2011-08-04 17:51:11 UTC (rev 3363) +++ trunk/api/src/main/java/org/teiid/language/Like.java 2011-08-05 18:33:39 UTC (rev 3364) @@ -25,11 +25,21 @@ import org.teiid.language.visitor.LanguageObjectVisitor; public class Like extends Condition implements Predicate { + + public enum MatchMode { + LIKE, + SIMILAR, + /** + * The escape char is typically not used in regex mode. + */ + REGEX + } private Expression leftExpression; private Expression rightExpression; private Character escapeCharacter; private boolean isNegated; + private MatchMode mode = MatchMode.LIKE; public Like(Expression left, Expression right, Character escapeCharacter, boolean negated) { leftExpression = left; @@ -74,5 +84,13 @@ public void setNegated(boolean negated) { this.isNegated = negated; } + + public MatchMode getMode() { + return mode; + } + + public void setMode(MatchMode mode) { + this.mode = mode; + } } Modified: trunk/api/src/main/java/org/teiid/language/SQLConstants.java =================================================================== --- trunk/api/src/main/java/org/teiid/language/SQLConstants.java 2011-08-04 17:51:11 UTC (rev 3363) +++ trunk/api/src/main/java/org/teiid/language/SQLConstants.java 2011-08-05 18:33:39 UTC (rev 3364) @@ -251,6 +251,7 @@ public static final String LEADING = "LEADING"; //$NON-NLS-1$ public static final String LEFT = "LEFT"; //$NON-NLS-1$ public static final String LIKE = "LIKE"; //$NON-NLS-1$ + public static final String LIKE_REGEX = "LIKE_REGEX"; //$NON-NLS-1$ public static final String LOCAL = "LOCAL"; //$NON-NLS-1$ public static final String LOCALTIME = "LOCALTIME"; //$NON-NLS-1$ public static final String LOCALTIMESTAMP = "LOCALTIMESTAMP"; //$NON-NLS-1$ @@ -318,7 +319,7 @@ public static final String SESSION_USER = "SESSION_USER"; //$NON-NLS-1$ public static final String SET = "SET"; //$NON-NLS-1$ public static final String SHORT = "SHORT"; //$NON-NLS-1$ - public static final String SIILAR = "SIMILAR"; //$NON-NLS-1$ + public static final String SIMILAR = "SIMILAR"; //$NON-NLS-1$ public static final String SMALLINT = "SMALLINT"; //$NON-NLS-1$ public static final String SOME = "SOME"; //$NON-NLS-1$ public static final String SPECIFIC = "SPECIFIC"; //$NON-NLS-1$ Modified: trunk/api/src/main/java/org/teiid/language/visitor/SQLStringVisitor.java =================================================================== --- trunk/api/src/main/java/org/teiid/language/visitor/SQLStringVisitor.java 2011-08-04 17:51:11 UTC (rev 3363) +++ trunk/api/src/main/java/org/teiid/language/visitor/SQLStringVisitor.java 2011-08-05 18:33:39 UTC (rev 3364) @@ -611,9 +611,19 @@ buffer.append(Tokens.SPACE) .append(NOT); } - buffer.append(Tokens.SPACE) - .append(LIKE) - .append(Tokens.SPACE); + buffer.append(Tokens.SPACE); + switch (obj.getMode()) { + case LIKE: + buffer.append(LIKE); + break; + case SIMILAR: + buffer.append(SIMILAR) + .append(Tokens.SPACE) + .append(TO); + case REGEX: + buffer.append(getLikeRegexString()); + } + buffer.append(Tokens.SPACE); append(obj.getRightExpression()); if (obj.getEscapeCharacter() != null) { buffer.append(Tokens.SPACE) @@ -623,10 +633,13 @@ .append(obj.getEscapeCharacter().toString()) .append(Tokens.QUOTE); } - } - public void visit(Limit obj) { + protected String getLikeRegexString() { + return LIKE_REGEX; + } + + public void visit(Limit obj) { buffer.append(LIMIT) .append(Tokens.SPACE); if (obj.getRowOffset() > 0) { Modified: trunk/api/src/main/java/org/teiid/translator/ExecutionFactory.java =================================================================== --- trunk/api/src/main/java/org/teiid/translator/ExecutionFactory.java 2011-08-04 17:51:11 UTC (rev 3363) +++ trunk/api/src/main/java/org/teiid/translator/ExecutionFactory.java 2011-08-05 18:33:39 UTC (rev 3364) @@ -894,4 +894,20 @@ return false; } + /** + * @return true if the SIMILAR TO predicate is supported + * @since 7.5 + */ + public boolean supportsSimilarTo() { + return false; + } + + /** + * @return true if the LIKE_REGEX predicate is supported + * @since 7.4 + */ + public boolean supportsLikeRegex() { + return false; + } + } Modified: trunk/build/kits/jboss-container/teiid-releasenotes.html =================================================================== --- trunk/build/kits/jboss-container/teiid-releasenotes.html 2011-08-04 17:51:11 UTC (rev 3363) +++ trunk/build/kits/jboss-container/teiid-releasenotes.html 2011-08-05 18:33:39 UTC (rev 3364) @@ -27,6 +27,7 @@ <H2><A NAME="Highlights"></A>Highlights</H2> <UL> <LI><B>Window function support</B> - Teiid and pushdown support was added for SQL 2003 OLAP window functions. The analytical ranking functions RANK, DENSE_RANK, and ROW_NUMBER were also added. See the reference for more. + <LI><B>Additional ANSI support</B> - Teiid and pushdown support was added for SUBSTRING using FROM/FOR syntax, TRIM, EXTRACT, SIMILAR TO, and LIKE_REGEX. See the reference for more. <LI><B>Subquery optimization control</B> - added the DJ hint to indicate that a subquery should be the independent side of a dependent join. <LI><B>MAKEIND Hint</B> - The MAKEIND hint can be used to indicate that the other side of the join should be made dependent. <LI><B>ODBC SSL</B> - added support for SSL encrypted ODBC connections. @@ -53,7 +54,7 @@ <h4>from 7.4</h4> <ul> - <li>OFFSET was added as a keyword. + <li>OFFSET and LIKE_REGEX were added as reserved words. <li>ColumnReference.getName will always return just the element name. Previously it inconsistently returned the qualified and unqualified form depending upon where the ColumnReference appeared. <li>As per JDBC4, ResultSetMetadata.getColumnName will return the unaliased column name if available rather than return the alias. Set useJDBC4ColumnNameAndLabelSemantics to false to use the alias name as the column name. </ul> 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 2011-08-04 17:51:11 UTC (rev 3363) +++ trunk/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/JDBCExecutionFactory.java 2011-08-05 18:33:39 UTC (rev 3364) @@ -57,6 +57,7 @@ import org.teiid.language.Limit; import org.teiid.language.Literal; import org.teiid.language.QueryExpression; +import org.teiid.language.SQLConstants; import org.teiid.language.SetQuery; import org.teiid.language.Argument.Direction; import org.teiid.language.SetQuery.Operation; @@ -1147,5 +1148,9 @@ } return nameParts; } + + public String getLikeRegexString() { + return SQLConstants.Reserved.LIKE_REGEX; + } } 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 2011-08-04 17:51:11 UTC (rev 3363) +++ trunk/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/SQLConversionVisitor.java 2011-08-05 18:33:39 UTC (rev 3364) @@ -350,4 +350,9 @@ return executionFactory.useSelectLimit(); } + @Override + protected String getLikeRegexString() { + return executionFactory.getLikeRegexString(); + } + } Modified: trunk/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/h2/H2ExecutionFactory.java =================================================================== --- trunk/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/h2/H2ExecutionFactory.java 2011-08-04 17:51:11 UTC (rev 3363) +++ trunk/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/h2/H2ExecutionFactory.java 2011-08-05 18:33:39 UTC (rev 3364) @@ -200,4 +200,14 @@ public boolean supportsAggregatesEnhancedNumeric() { return true; } + + @Override + public boolean supportsLikeRegex() { + return true; + } + + @Override + public String getLikeRegexString() { + return "REGEXP"; //$NON-NLS-1$ + } } Modified: trunk/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/mysql/MySQL5ExecutionFactory.java =================================================================== --- trunk/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/mysql/MySQL5ExecutionFactory.java 2011-08-04 17:51:11 UTC (rev 3363) +++ trunk/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/mysql/MySQL5ExecutionFactory.java 2011-08-05 18:33:39 UTC (rev 3364) @@ -27,9 +27,9 @@ import java.util.List; import org.teiid.language.Function; +import org.teiid.translator.SourceSystemFunctions; import org.teiid.translator.Translator; import org.teiid.translator.TranslatorException; -import org.teiid.translator.SourceSystemFunctions; import org.teiid.translator.jdbc.FunctionModifier; @Translator(name="mysql5", description="A translator for open source MySQL5 Database") @@ -65,5 +65,15 @@ public boolean supportsAggregatesEnhancedNumeric() { return true; } + + @Override + public boolean supportsLikeRegex() { + return true; + } + + @Override + public String getLikeRegexString() { + return "REGEXP"; //$NON-NLS-1$ + } } 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 2011-08-04 17:51:11 UTC (rev 3363) +++ trunk/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/oracle/OracleExecutionFactory.java 2011-08-05 18:33:39 UTC (rev 3364) @@ -538,4 +538,14 @@ return true; } + @Override + public boolean supportsLikeRegex() { + return true; + } + + @Override + public String getLikeRegexString() { + return "REGEXP_LIKE"; //$NON-NLS-1$ + } + } 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 2011-08-04 17:51:11 UTC (rev 3363) +++ trunk/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/postgresql/PostgreSQLExecutionFactory.java 2011-08-05 18:33:39 UTC (rev 3364) @@ -33,8 +33,10 @@ import org.teiid.language.Expression; import org.teiid.language.Function; import org.teiid.language.LanguageObject; +import org.teiid.language.Like; import org.teiid.language.Limit; import org.teiid.language.Literal; +import org.teiid.language.Like.MatchMode; import org.teiid.language.SQLConstants.NonReserved; import org.teiid.translator.ExecutionContext; import org.teiid.translator.SourceSystemFunctions; @@ -230,6 +232,11 @@ agg.setName("bool_or"); //$NON-NLS-1$ } } + } else if (obj instanceof Like) { + Like like = (Like)obj; + if (like.getMode() == MatchMode.REGEX) { + return Arrays.asList(like.getLeftExpression(), like.isNegated()?" !~ ":" ~ ", like.getRightExpression()); //$NON-NLS-1$ //$NON-NLS-2$ + } } return super.translate(obj, context); } @@ -498,4 +505,14 @@ return getDatabaseVersion().compareTo(EIGHT_4) >= 0; } + @Override + public boolean supportsSimilarTo() { + return true; + } + + @Override + public boolean supportsLikeRegex() { + return true; + } + } 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 2011-08-04 17:51:11 UTC (rev 3363) +++ trunk/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/teiid/TeiidExecutionFactory.java 2011-08-05 18:33:39 UTC (rev 3364) @@ -221,4 +221,14 @@ public boolean supportsArrayAgg() { return getDatabaseVersion().compareTo(SEVEN_5) >= 0; } + + @Override + public boolean supportsLikeRegex() { + return getDatabaseVersion().compareTo(SEVEN_5) >= 0; + } + + @Override + public boolean supportsSimilarTo() { + return getDatabaseVersion().compareTo(SEVEN_5) >= 0; + } } 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 2011-08-04 17:51:11 UTC (rev 3363) +++ trunk/connectors/translator-jdbc/src/test/java/org/teiid/translator/jdbc/oracle/TestOracleTranslator.java 2011-08-05 18:33:39 UTC (rev 3364) @@ -22,7 +22,7 @@ package org.teiid.translator.jdbc.oracle; -import static org.junit.Assert.assertEquals; +import static org.junit.Assert.*; import java.util.List; @@ -43,8 +43,8 @@ import org.teiid.query.metadata.QueryMetadataInterface; import org.teiid.query.metadata.TransformationMetadata; import org.teiid.query.unittest.RealMetadataFactory; +import org.teiid.translator.ExecutionContext; import org.teiid.translator.TranslatorException; -import org.teiid.translator.ExecutionContext; import org.teiid.translator.jdbc.TranslatedCommand; import org.teiid.translator.jdbc.TranslationHelper; @@ -780,5 +780,14 @@ String expected = "SELECT (1 / tan(SmallA.DoubleNum)) FROM SmallA"; //$NON-NLS-1$ helpTestVisitor(RealMetadataFactory.exampleBQTCached(), sql, EMPTY_CONTEXT, null, expected); } + + @Test public void testLikeRegex() throws Exception { + String input = "SELECT intkey FROM BQT1.SMALLA where stringkey like_regex 'ab.*c+' and stringkey not like_regex 'ab{3,5}c'"; //$NON-NLS-1$ + String output = "SELECT SmallA.IntKey FROM SmallA WHERE SmallA.StringKey REGEXP_LIKE 'ab.*c+' AND SmallA.StringKey NOT REGEXP_LIKE 'ab{3,5}c'"; //$NON-NLS-1$ + TranslationHelper.helpTestVisitor(TranslationHelper.BQT_VDB, + input, output, + TRANSLATOR); + } + } Modified: trunk/connectors/translator-jdbc/src/test/java/org/teiid/translator/jdbc/postgresql/TestPostgreSQLTranslator.java =================================================================== --- trunk/connectors/translator-jdbc/src/test/java/org/teiid/translator/jdbc/postgresql/TestPostgreSQLTranslator.java 2011-08-04 17:51:11 UTC (rev 3363) +++ trunk/connectors/translator-jdbc/src/test/java/org/teiid/translator/jdbc/postgresql/TestPostgreSQLTranslator.java 2011-08-05 18:33:39 UTC (rev 3364) @@ -544,5 +544,14 @@ input, output, TRANSLATOR); } + + @Test public void testLikeRegex() throws Exception { + String input = "SELECT intkey FROM BQT1.SMALLA where stringkey like_regex 'ab.*c+' and stringkey not like_regex 'ab{3,5}c'"; //$NON-NLS-1$ + String output = "SELECT SmallA.IntKey FROM SmallA WHERE SmallA.StringKey ~ 'ab.*c+' AND SmallA.StringKey !~ 'ab{3,5}c'"; //$NON-NLS-1$ + + TranslationHelper.helpTestVisitor(TranslationHelper.BQT_VDB, + input, output, + TRANSLATOR); + } } 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 2011-08-04 17:51:11 UTC (rev 3363) +++ trunk/documentation/developer-guide/src/main/docbook/en-US/content/translator-api.xml 2011-08-05 18:33:39 UTC (rev 3364) @@ -423,7 +423,7 @@ <listitem><para><code>SubqueryIn</code> – represents an IN criteria that uses a subquery to produce the value set</para></listitem> <listitem><para><code>IsNull</code> – represents an IS NULL criteria</para></listitem> <listitem><para><code>Exists</code> – represents an EXISTS criteria that determines whether a subquery will return any values</para></listitem> - <listitem><para><code>Like</code> – represents a LIKE criteria that compares string values</para></listitem> + <listitem><para><code>Like</code> – represents a LIKE/SIMILAR TO/LIKE_REGEX criteria that compares string values</para></listitem> </itemizedlist> </section> @@ -879,6 +879,28 @@ </row> <row> <entry> + <para>SimilarTo</para> + </entry> + <entry> + <para/> + </entry> + <entry> + <para>Translator can support SIMILAR TO criteria.</para> + </entry> + </row> + <row> + <entry> + <para>LikeRegexCriteria</para> + </entry> + <entry> + <para/> + </entry> + <entry> + <para>Translator can support LIKE_REGEX criteria.</para> + </entry> + </row> + <row> + <entry> <para>InCriteria</para> </entry> <entry> Modified: trunk/documentation/reference/src/main/docbook/en-US/content/grammar.xml =================================================================== --- trunk/documentation/reference/src/main/docbook/en-US/content/grammar.xml 2011-08-04 17:51:11 UTC (rev 3363) +++ trunk/documentation/reference/src/main/docbook/en-US/content/grammar.xml 2011-08-05 18:33:39 UTC (rev 3364) @@ -170,6 +170,7 @@ | &lt;LARGE: "large"&gt; | &lt;LEADING: "leading"&gt; | &lt;LIKE: "like"&gt; +| &lt;LIKE_REGEX: "like_regex"&gt; | &lt;LIMIT: "limit"&gt; | &lt;LOCAL: "local"&gt; | &lt;LOOP: "loop"&gt; @@ -717,32 +718,36 @@ <row> <entry align="right" valign="top"><para><anchor id="prod71" xreflabel="booleanPrimary"/>booleanPrimary</para></entry> <entry align="left" valign="top"><para>::= -( <link linkend="prod41">translateCriteria</link> | ( <link linkend="prod91">commonValueExpression</link> ( ( <link linkend="prod92">betweenCrit</link> | <link linkend="prod93">matchCrit</link> | <link linkend="prod94">setCrit</link> | <link linkend="prod95">isNullCrit</link> | <link linkend="prod96">subqueryCompareCriteria</link> | <link linkend="prod97">compareCrit</link> ) )? ) | <link linkend="prod98">existsCriteria</link> | <link linkend="prod37">hasCriteria</link> )</para></entry></row> +( <link linkend="prod41">translateCriteria</link> | ( <link linkend="prod91">commonValueExpression</link> ( ( <link linkend="prod92">betweenCrit</link> | <link linkend="prod93">matchCrit</link> | <link linkend="prod94">regexMatchCrit</link> | <link linkend="prod95">setCrit</link> | <link linkend="prod96">isNullCrit</link> | <link linkend="prod97">subqueryCompareCriteria</link> | <link linkend="prod98">compareCrit</link> ) )? ) | <link linkend="prod99">existsCriteria</link> | <link linkend="prod37">hasCriteria</link> )</para></entry></row> <row> -<entry align="right" valign="top"><para><anchor id="prod99" xreflabel="operator"/>operator</para></entry> +<entry align="right" valign="top"><para><anchor id="prod100" xreflabel="operator"/>operator</para></entry> <entry align="left" valign="top"><para>::= ( &lt;EQ&gt; | &lt;NE&gt; | &lt;NE2&gt; | &lt;LT&gt; | &lt;LE&gt; | &lt;GT&gt; | &lt;GE&gt; )</para></entry></row> <row> -<entry align="right" valign="top"><para><anchor id="prod97" xreflabel="compareCrit"/>compareCrit</para></entry> +<entry align="right" valign="top"><para><anchor id="prod98" xreflabel="compareCrit"/>compareCrit</para></entry> <entry align="left" valign="top"><para>::= -<link linkend="prod99">operator</link> <link linkend="prod91">commonValueExpression</link></para></entry></row> +<link linkend="prod100">operator</link> <link linkend="prod91">commonValueExpression</link></para></entry></row> <row> -<entry align="right" valign="top"><para><anchor id="prod100" xreflabel="subquery"/>subquery</para></entry> +<entry align="right" valign="top"><para><anchor id="prod101" xreflabel="subquery"/>subquery</para></entry> <entry align="left" valign="top"><para>::= &lt;LPAREN&gt; ( <link linkend="prod13">queryExpression</link> | ( <link linkend="prod15">storedProcedure</link> ) ) &lt;RPAREN&gt;</para></entry></row> <row> -<entry align="right" valign="top"><para><anchor id="prod101" xreflabel="subqueryAndHint"/>subqueryAndHint</para></entry> +<entry align="right" valign="top"><para><anchor id="prod102" xreflabel="subqueryAndHint"/>subqueryAndHint</para></entry> <entry align="left" valign="top"><para>::= -<link linkend="prod100">subquery</link></para></entry></row> +<link linkend="prod101">subquery</link></para></entry></row> <row> -<entry align="right" valign="top"><para><anchor id="prod96" xreflabel="subqueryCompareCriteria"/>subqueryCompareCriteria</para></entry> +<entry align="right" valign="top"><para><anchor id="prod97" xreflabel="subqueryCompareCriteria"/>subqueryCompareCriteria</para></entry> <entry align="left" valign="top"><para>::= -<link linkend="prod99">operator</link> ( &lt;ANY&gt; | &lt;SOME&gt; | &lt;ALL&gt; ) <link linkend="prod100">subquery</link></para></entry></row> +<link linkend="prod100">operator</link> ( &lt;ANY&gt; | &lt;SOME&gt; | &lt;ALL&gt; ) <link linkend="prod101">subquery</link></para></entry></row> <row> <entry align="right" valign="top"><para><anchor id="prod93" xreflabel="matchCrit"/>matchCrit</para></entry> <entry align="left" valign="top"><para>::= -( &lt;NOT&gt; )? &lt;LIKE&gt; <link linkend="prod91">commonValueExpression</link> ( &lt;ESCAPE&gt; <link linkend="prod69">charVal</link> | ( &lt;LBRACE&gt; &lt;ESCAPE&gt; <link linkend="prod69">charVal</link> &lt;RBRACE&gt; ) )?</para></entry></row> +( &lt;NOT&gt; )? ( &lt;LIKE&gt; | ( &lt;SIMILAR&gt; &lt;TO&gt; ) ) <link linkend="prod91">commonValueExpression</link> ( &lt;ESCAPE&gt; <link linkend="prod69">charVal</link> | ( &lt;LBRACE&gt; &lt;ESCAPE&gt; <link linkend="prod69">charVal</link> &lt;RBRACE&gt; ) )?</para></entry></row> <row> +<entry align="right" valign="top"><para><anchor id="prod94" xreflabel="regexMatchCrit"/>regexMatchCrit</para></entry> +<entry align="left" valign="top"><para>::= +( &lt;NOT&gt; )? &lt;LIKE_REGEX&gt; <link linkend="prod91">commonValueExpression</link></para></entry></row> +<row> <entry align="right" valign="top"><para><anchor id="prod69" xreflabel="charVal"/>charVal</para></entry> <entry align="left" valign="top"><para>::= <link linkend="prod1">stringVal</link></para></entry></row> @@ -751,17 +756,17 @@ <entry align="left" valign="top"><para>::= ( &lt;NOT&gt; )? &lt;BETWEEN&gt; <link linkend="prod91">commonValueExpression</link> &lt;AND&gt; <link linkend="prod91">commonValueExpression</link></para></entry></row> <row> -<entry align="right" valign="top"><para><anchor id="prod95" xreflabel="isNullCrit"/>isNullCrit</para></entry> +<entry align="right" valign="top"><para><anchor id="prod96" xreflabel="isNullCrit"/>isNullCrit</para></entry> <entry align="left" valign="top"><para>::= &lt;IS&gt; ( &lt;NOT&gt; )? &lt;NULL&gt;</para></entry></row> <row> -<entry align="right" valign="top"><para><anchor id="prod94" xreflabel="setCrit"/>setCrit</para></entry> +<entry align="right" valign="top"><para><anchor id="prod95" xreflabel="setCrit"/>setCrit</para></entry> <entry align="left" valign="top"><para>::= -( &lt;NOT&gt; )? &lt;IN&gt; ( ( <link linkend="prod101">subqueryAndHint</link> ) | ( &lt;LPAREN&gt; <link linkend="prod91">commonValueExpression</link> ( &lt;COMMA&gt; <link linkend="prod91">commonValueExpression</link> )* &lt;RPAREN&gt; ) )</para></entry></row> +( &lt;NOT&gt; )? &lt;IN&gt; ( ( <link linkend="prod102">subqueryAndHint</link> ) | ( &lt;LPAREN&gt; <link linkend="prod91">commonValueExpression</link> ( &lt;COMMA&gt; <link linkend="prod91">commonValueExpression</link> )* &lt;RPAREN&gt; ) )</para></entry></row> <row> -<entry align="right" valign="top"><para><anchor id="prod98" xreflabel="existsCriteria"/>existsCriteria</para></entry> +<entry align="right" valign="top"><para><anchor id="prod99" xreflabel="existsCriteria"/>existsCriteria</para></entry> <entry align="left" valign="top"><para>::= -&lt;EXISTS&gt; <link linkend="prod101">subqueryAndHint</link></para></entry></row> +&lt;EXISTS&gt; <link linkend="prod102">subqueryAndHint</link></para></entry></row> <row> <entry align="right" valign="top"><para><anchor id="prod60" xreflabel="groupBy"/>groupBy</para></entry> <entry align="left" valign="top"><para>::= @@ -773,27 +778,27 @@ <row> <entry align="right" valign="top"><para><anchor id="prod53" xreflabel="orderby"/>orderby</para></entry> <entry align="left" valign="top"><para>::= -&lt;ORDER&gt; &lt;BY&gt; <link linkend="prod102">sortSpecification</link> ( &lt;COMMA&gt; <link linkend="prod102">sortSpecification</link> )*</para></entry></row> +&lt;ORDER&gt; &lt;BY&gt; <link linkend="prod103">sortSpecification</link> ( &lt;COMMA&gt; <link linkend="prod103">sortSpecification</link> )*</para></entry></row> <row> -<entry align="right" valign="top"><para><anchor id="prod102" xreflabel="sortSpecification"/>sortSpecification</para></entry> +<entry align="right" valign="top"><para><anchor id="prod103" xreflabel="sortSpecification"/>sortSpecification</para></entry> <entry align="left" valign="top"><para>::= -<link linkend="prod103">sortKey</link> ( &lt;ASC&gt; | &lt;DESC&gt; )? ( <link linkend="prod11">nonReserved</link> <link linkend="prod11">nonReserved</link> )?</para></entry></row> +<link linkend="prod104">sortKey</link> ( &lt;ASC&gt; | &lt;DESC&gt; )? ( <link linkend="prod11">nonReserved</link> <link linkend="prod11">nonReserved</link> )?</para></entry></row> <row> -<entry align="right" valign="top"><para><anchor id="prod103" xreflabel="sortKey"/>sortKey</para></entry> +<entry align="right" valign="top"><para><anchor id="prod104" xreflabel="sortKey"/>sortKey</para></entry> <entry align="left" valign="top"><para>::= <link linkend="prod24">expression</link></para></entry></row> <row> -<entry align="right" valign="top"><para><anchor id="prod104" xreflabel="intParam"/>intParam</para></entry> +<entry align="right" valign="top"><para><anchor id="prod105" xreflabel="intParam"/>intParam</para></entry> <entry align="left" valign="top"><para>::= ( <link linkend="prod84">intVal</link> | &lt;QMARK&gt; )</para></entry></row> <row> <entry align="right" valign="top"><para><anchor id="prod54" xreflabel="limit"/>limit</para></entry> <entry align="left" valign="top"><para>::= -( ( &lt;LIMIT&gt; <link linkend="prod104">intParam</link> ( &lt;COMMA&gt; <link linkend="prod104">intParam</link> )? ) | ( &lt;OFFSET&gt; <link linkend="prod104">intParam</link> ( &lt;ROW&gt; | &lt;ROWS&gt; ) ( <link linkend="prod105">fetchLimit</link> )? ) | ( <link linkend="prod105">fetchLimit</link> ) )</para></entry></row> +( ( &lt;LIMIT&gt; <link linkend="prod105">intParam</link> ( &lt;COMMA&gt; <link linkend="prod105">intParam</link> )? ) | ( &lt;OFFSET&gt; <link linkend="prod105">intParam</link> ( &lt;ROW&gt; | &lt;ROWS&gt; ) ( <link linkend="prod106">fetchLimit</link> )? ) | ( <link linkend="prod106">fetchLimit</link> ) )</para></entry></row> <row> -<entry align="right" valign="top"><para><anchor id="prod105" xreflabel="fetchLimit"/>fetchLimit</para></entry> +<entry align="right" valign="top"><para><anchor id="prod106" xreflabel="fetchLimit"/>fetchLimit</para></entry> <entry align="left" valign="top"><para>::= -&lt;FETCH&gt; <link linkend="prod11">nonReserved</link> ( <link linkend="prod104">intParam</link> )? ( &lt;ROW&gt; | &lt;ROWS&gt; ) &lt;ONLY&gt;</para></entry></row> +&lt;FETCH&gt; <link linkend="prod11">nonReserved</link> ( <link linkend="prod105">intParam</link> )? ( &lt;ROW&gt; | &lt;ROWS&gt; ) &lt;ONLY&gt;</para></entry></row> <row> <entry align="right" valign="top"><para><anchor id="prod45" xreflabel="option"/>option</para></entry> <entry align="left" valign="top"><para>::= @@ -805,73 +810,73 @@ <row> <entry align="right" valign="top"><para><anchor id="prod91" xreflabel="commonValueExpression"/>commonValueExpression</para></entry> <entry align="left" valign="top"><para>::= -( <link linkend="prod106">plusExpression</link> ( &lt;CONCAT_OP&gt; <link linkend="prod106">plusExpression</link> )* )</para></entry></row> +( <link linkend="prod107">plusExpression</link> ( &lt;CONCAT_OP&gt; <link linkend="prod107">plusExpression</link> )* )</para></entry></row> <row> -<entry align="right" valign="top"><para><anchor id="prod106" xreflabel="plusExpression"/>plusExpression</para></entry> +<entry align="right" valign="top"><para><anchor id="prod107" xreflabel="plusExpression"/>plusExpression</para></entry> <entry align="left" valign="top"><para>::= -( <link linkend="prod107">timesExpression</link> ( <link linkend="prod108">plusOperator</link> <link linkend="prod107">timesExpression</link> )* )</para></entry></row> +( <link linkend="prod108">timesExpression</link> ( <link linkend="prod109">plusOperator</link> <link linkend="prod108">timesExpression</link> )* )</para></entry></row> <row> -<entry align="right" valign="top"><para><anchor id="prod108" xreflabel="plusOperator"/>plusOperator</para></entry> +<entry align="right" valign="top"><para><anchor id="prod109" xreflabel="plusOperator"/>plusOperator</para></entry> <entry align="left" valign="top"><para>::= ( &lt;PLUS&gt; | &lt;MINUS&gt; )</para></entry></row> <row> -<entry align="right" valign="top"><para><anchor id="prod107" xreflabel="timesExpression"/>timesExpression</para></entry> +<entry align="right" valign="top"><para><anchor id="prod108" xreflabel="timesExpression"/>timesExpression</para></entry> <entry align="left" valign="top"><para>::= -( <link linkend="prod109">valueExpressionPrimary</link> ( <link linkend="prod110">timesOperator</link> <link linkend="prod109">valueExpressionPrimary</link> )* )</para></entry></row> +( <link linkend="prod110">valueExpressionPrimary</link> ( <link linkend="prod111">timesOperator</link> <link linkend="prod110">valueExpressionPrimary</link> )* )</para></entry></row> <row> -<entry align="right" valign="top"><para><anchor id="prod110" xreflabel="timesOperator"/>timesOperator</para></entry> +<entry align="right" valign="top"><para><anchor id="prod111" xreflabel="timesOperator"/>timesOperator</para></entry> <entry align="left" valign="top"><para>::= ( &lt;STAR&gt; | &lt;SLASH&gt; )</para></entry></row> <row> -<entry align="right" valign="top"><para><anchor id="prod109" xreflabel="valueExpressionPrimary"/>valueExpressionPrimary</para></entry> +<entry align="right" valign="top"><para><anchor id="prod110" xreflabel="valueExpressionPrimary"/>valueExpressionPrimary</para></entry> <entry align="left" valign="top"><para>::= -( &lt;QMARK&gt; | &lt;POS_REF&gt; | <link linkend="prod111">literal</link> | ( &lt;LBRACE&gt; <link linkend="prod11">nonReserved</link> <link linkend="prod112">function</link> &lt;RBRACE&gt; ) | ( <link linkend="prod68">textAgg</link> ( <link linkend="prod113">windowSpecification</link> )? ) | ( <link linkend="prod70">aggregateSymbol</link> ( <link linkend="prod113">windowSpecification</link> )? ) | ( <link linkend="prod70">aggregateSymbol</link> ( <link linkend="prod113">windowSpecification</link> )? ) | <link linkend="prod66">orderedAgg</link> ( <link linkend="prod113">windowSpecification</link> )? | ( <link linkend="prod70">aggregateSymbol</link> <link linkend="prod113">windowSpecification</link> ) | ( <link linkend="prod112">function</link> ) | ( &lt;ID&gt; ( &lt;LSBRACE&gt; <link linkend="prod84">intVal</link> &lt;RSBRACE&gt; )? ) | <link linkend="prod100">subquery</link> | ( &lt;LPAREN&gt; <link linkend="prod24">expression</link> &lt;RPAREN&gt; ( &lt;LSBRACE&gt; <link! linkend="prod84">intVal</link> &lt;RSBRACE&gt; )? ) | <link linkend="prod114">searchedCaseExpression</link> | <link linkend="prod115">caseExpression</link> )</para></entry></row> +( &lt;QMARK&gt; | &lt;POS_REF&gt; | <link linkend="prod112">literal</link> | ( &lt;LBRACE&gt; <link linkend="prod11">nonReserved</link> <link linkend="prod113">function</link> &lt;RBRACE&gt; ) | ( <link linkend="prod68">textAgg</link> ( <link linkend="prod114">windowSpecification</link> )? ) | ( <link linkend="prod70">aggregateSymbol</link> ( <link linkend="prod114">windowSpecification</link> )? ) | ( <link linkend="prod70">aggregateSymbol</link> ( <link linkend="prod114">windowSpecification</link> )? ) | <link linkend="prod66">orderedAgg</link> ( <link linkend="prod114">windowSpecification</link> )? | ( <link linkend="prod70">aggregateSymbol</link> <link linkend="prod114">windowSpecification</link> ) | ( <link linkend="prod113">function</link> ) | ( &lt;ID&gt; ( &lt;LSBRACE&gt; <link linkend="prod84">intVal</link> &lt;RSBRACE&gt; )? ) | <link linkend="prod101">subquery</link> | ( &lt;LPAREN&gt; <link linkend="prod24">expression</link> &lt;RPAREN&gt; ( &lt;LSBRACE&gt; <link! linkend="prod84">intVal</link> &lt;RSBRACE&gt; )? ) | <link linkend="prod115">searchedCaseExpression</link> | <link linkend="prod116">caseExpression</link> )</para></entry></row> <row> -<entry align="right" valign="top"><para><anchor id="prod113" xreflabel="windowSpecification"/>windowSpecification</para></entry> +<entry align="right" valign="top"><para><anchor id="prod114" xreflabel="windowSpecification"/>windowSpecification</para></entry> <entry align="left" valign="top"><para>::= &lt;OVER&gt; &lt;LPAREN&gt; ( &lt;PARTITION&gt; &lt;BY&gt; <link linkend="prod48">expressionList</link> )? ( <link linkend="prod53">orderby</link> )? &lt;RPAREN&gt;</para></entry></row> <row> -<entry align="right" valign="top"><para><anchor id="prod115" xreflabel="caseExpression"/>caseExpression</para></entry> +<entry align="right" valign="top"><para><anchor id="prod116" xreflabel="caseExpression"/>caseExpression</para></entry> <entry align="left" valign="top"><para>::= &lt;CASE&gt; <link linkend="prod24">expression</link> ( &lt;WHEN&gt; <link linkend="prod24">expression</link> &lt;THEN&gt; <link linkend="prod24">expression</link> )+ ( &lt;ELSE&gt; <link linkend="prod24">expression</link> )? &lt;END&gt;</para></entry></row> <row> -<entry align="right" valign="top"><para><anchor id="prod114" xreflabel="searchedCaseExpression"/>searchedCaseExpression</para></entry> +<entry align="right" valign="top"><para><anchor id="prod115" xreflabel="searchedCaseExpression"/>searchedCaseExpression</para></entry> <entry align="left" valign="top"><para>::= &lt;CASE&gt; ( &lt;WHEN&gt; <link linkend="prod35">criteria</link> &lt;THEN&gt; <link linkend="prod24">expression</link> )+ ( &lt;ELSE&gt; <link linkend="prod24">expression</link> )? &lt;END&gt;</para></entry></row> <row> -<entry align="right" valign="top"><para><anchor id="prod112" xreflabel="function"/>function</para></entry> +<entry align="right" valign="top"><para><anchor id="prod113" xreflabel="function"/>function</para></entry> <entry align="left" valign="top"><para>::= -( ( &lt;CONVERT&gt; &lt;LPAREN&gt; <link linkend="prod24">expression</link> &lt;COMMA&gt; <link linkend="prod38">dataType</link> &lt;RPAREN&gt; ) | ( &lt;CAST&gt; &lt;LPAREN&gt; <link linkend="prod24">expression</link> &lt;AS&gt; <link linkend="prod38">dataType</link> &lt;RPAREN&gt; ) | ( <link linkend="prod11">nonReserved</link> &lt;LPAREN&gt; <link linkend="prod24">expression</link> &lt;COMMA&gt; <link linkend="prod116">stringConstant</link> &lt;RPAREN&gt; ) | ( <link linkend="prod11">nonReserved</link> &lt;LPAREN&gt; <link linkend="prod117">intervalType</link> &lt;COMMA&gt; <link linkend="prod24">expression</link> &lt;COMMA&gt; <link linkend="prod24">expression</link> &lt;RPAREN&gt; ) | <link linkend="prod118">queryString</link> | ( ( &lt;LEFT&gt; | &lt;RIGHT&gt; | &lt;CHAR&gt; | &lt;USER&gt; | &lt;YEAR&gt; | &lt;MONTH&gt; | &lt;HOUR&gt; | &lt;MINUTE&gt; | &lt;SECOND&gt; | &lt;XMLCONCAT&gt; | &lt;XMLCOMMENT&gt; ) &lt;LPAREN&gt; ( <link linkend="prod48">expressionList</li! nk> )? &lt;RPAREN&gt; ) | ( ( &lt;TRANSLATE&gt; | &lt;INSERT&gt; ) &lt;LPAREN&gt; ( <link linkend="prod48">expressionList</link> )? &lt;RPAREN&gt; ) | <link linkend="prod119">xmlParse</link> | <link linkend="prod120">xmlElement</link> | ( &lt;XMLPI&gt; &lt;LPAREN&gt; ( &lt;ID&gt; <link linkend="prod121">idExpression</link> | <link linkend="prod121">idExpression</link> ) ( &lt;COMMA&gt; <link linkend="prod24">expression</link> )? &lt;RPAREN&gt; ) | <link linkend="prod122">xmlForest</link> | <link linkend="prod82">xmlSerialize</link> | <link linkend="prod85">xmlQuery</link> | ( <link linkend="prod2">id</link> &lt;LPAREN&gt; ( <link linkend="prod48">expressionList</link> )? &lt;RPAREN&gt; ) )</para></entry></row> +( ( &lt;CONVERT&gt; &lt;LPAREN&gt; <link linkend="prod24">expression</link> &lt;COMMA&gt; <link linkend="prod38">dataType</link> &lt;RPAREN&gt; ) | ( &lt;CAST&gt; &lt;LPAREN&gt; <link linkend="prod24">expression</link> &lt;AS&gt; <link linkend="prod38">dataType</link> &lt;RPAREN&gt; ) | ( <link linkend="prod11">nonReserved</link> &lt;LPAREN&gt; <link linkend="prod24">expression</link> &lt;FROM&gt; <link linkend="prod24">expression</link> ( &lt;FOR&gt; <link linkend="prod24">expression</link> )? &lt;RPAREN&gt; ) | ( <link linkend="prod11">nonReserved</link> &lt;LPAREN&gt; ( &lt;YEAR&gt; | &lt;MONTH&gt; | &lt;DAY&gt; | &lt;HOUR&gt; | &lt;MINUTE&gt; | &lt;SECOND&gt; ) &lt;FROM&gt; <link linkend="prod24">expression</link> &lt;RPAREN&gt; ) | ( <link linkend="prod11">nonReserved</link> &lt;LPAREN&gt; ( ( ( ( &lt;LEADING&gt; | &lt;TRAILING&gt; | &lt;BOTH&gt; ) ( <link linkend="prod24">expression</link> )? ) | <link linkend="prod24">expression</link> ) &lt;FROM&gt; )? <link linkend! ="prod24">expression</link> &lt;RPAREN&gt; ) | ( <link linkend="prod11">nonReserved</link> &lt;LPAREN&gt; <link linkend="prod24">expression</link> &lt;COMMA&gt; <link linkend="prod117">stringConstant</link> &lt;RPAREN&gt; ) | ( <link linkend="prod11">nonReserved</link> &lt;LPAREN&gt; <link linkend="prod118">intervalType</link> &lt;COMMA&gt; <link linkend="prod24">expression</link> &lt;COMMA&gt; <link linkend="prod24">expression</link> &lt;RPAREN&gt; ) | <link linkend="prod119">queryString</link> | ( ( &lt;LEFT&gt; | &lt;RIGHT&gt; | &lt;CHAR&gt; | &lt;USER&gt; | &lt;YEAR&gt; | &lt;MONTH&gt; | &lt;HOUR&gt; | &lt;MINUTE&gt; | &lt;SECOND&gt; | &lt;XMLCONCAT&gt; | &lt;XMLCOMMENT&gt; ) &lt;LPAREN&gt; ( <link linkend="prod48">expressionList</link> )? &lt;RPAREN&gt; ) | ( ( &lt;TRANSLATE&gt; | &lt;INSERT&gt; ) &lt;LPAREN&gt; ( <link linkend="prod48">expressionList</link> )? &lt;RPAREN&gt; ) | <link linkend="prod120">xmlParse</link> | <link linkend="prod121">xmlElement</link> | ( &l! t;XMLPI&gt; &lt;LPAREN&gt; ( &lt;ID&gt; <link linkend="prod122! ">idExpr ession</link> | <link linkend="prod122">idExpression</link> ) ( &lt;COMMA&gt; <link linkend="prod24">expression</link> )? &lt;RPAREN&gt; ) | <link linkend="prod123">xmlForest</link> | <link linkend="prod82">xmlSerialize</link> | <link linkend="prod85">xmlQuery</link> | ( <link linkend="prod2">id</link> &lt;LPAREN&gt; ( <link linkend="prod48">expressionList</link> )? &lt;RPAREN&gt; ) )</para></entry></row> <row> -<entry align="right" valign="top"><para><anchor id="prod116" xreflabel="stringConstant"/>stringConstant</para></entry> +<entry align="right" valign="top"><para><anchor id="prod117" 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="prod119" xreflabel="xmlParse"/>xmlParse</para></entry> +<entry align="right" valign="top"><para><anchor id="prod120" xreflabel="xmlParse"/>xmlParse</para></entry> <entry align="left" valign="top"><para>::= &lt;XMLPARSE&gt; &lt;LPAREN&gt; <link linkend="prod11">nonReserved</link> <link linkend="prod24">expression</link> ( <link linkend="prod11">nonReserved</link> )? &lt;RPAREN&gt;</para></entry></row> <row> -<entry align="right" valign="top"><para><anchor id="prod118" xreflabel="queryString"/>queryString</para></entry> +<entry align="right" valign="top"><para><anchor id="prod119" xreflabel="queryString"/>queryString</para></entry> <entry align="left" valign="top"><para>::= <link linkend="prod11">nonReserved</link> &lt;LPAREN&gt; <link linkend="prod24">expression</link> ( &lt;COMMA&gt; <link linkend="prod65">derivedColumn</link> )* &lt;RPAREN&gt;</para></entry></row> <row> -<entry align="right" valign="top"><para><anchor id="prod120" xreflabel="xmlElement"/>xmlElement</para></entry> +<entry align="right" valign="top"><para><anchor id="prod121" xreflabel="xmlElement"/>xmlElement</para></entry> <entry align="left" valign="top"><para>::= -&lt;XMLELEMENT&gt; &lt;LPAREN&gt; ( &lt;ID&gt; <link linkend="prod2">id</link> | <link linkend="prod2">id</link> ) ( &lt;COMMA&gt; <link linkend="prod86">xmlNamespaces</link> )? ( &lt;COMMA&gt; <link linkend="prod123">xmlAttributes</link> )? ( &lt;COMMA&gt; <link linkend="prod24">expression</link> )* &lt;RPAREN&gt;</para></entry></row> +&lt;XMLELEMENT&gt; &lt;LPAREN&gt; ( &lt;ID&gt; <link linkend="prod2">id</link> | <link linkend="prod2">id</link> ) ( &lt;COMMA&gt; <link linkend="prod86">xmlNamespaces</link> )? ( &lt;COMMA&gt; <link linkend="prod124">xmlAttributes</link> )? ( &lt;COMMA&gt; <link linkend="prod24">expression</link> )* &lt;RPAREN&gt;</para></entry></row> <row> -<entry align="right" valign="top"><para><anchor id="prod123" xreflabel="xmlAttributes"/>xmlAttributes</para></entry> +<entry align="right" valign="top"><para><anchor id="prod124" xreflabel="xmlAttributes"/>xmlAttributes</para></entry> <entry align="left" valign="top"><para>::= &lt;XMLATTRIBUTES&gt; &lt;LPAREN&gt; <link linkend="prod65">derivedColumn</link> ( &lt;COMMA&gt; <link linkend="prod65">derivedColumn</link> )* &lt;RPAREN&gt;</para></entry></row> <row> -<entry align="right" valign="top"><para><anchor id="prod122" xreflabel="xmlForest"/>xmlForest</para></entry> +<entry align="right" valign="top"><para><anchor id="prod123" xreflabel="xmlForest"/>xmlForest</para></entry> <entry align="left" valign="top"><para>::= &lt;XMLFOREST&gt; &lt;LPAREN&gt; ( <link linkend="prod86">xmlNamespaces</link> &lt;COMMA&gt; )? <link linkend="prod65">derivedColumn</link> ( &lt;COMMA&gt; <link linkend="prod65">derivedColumn</link> )* &lt;RPAREN&gt;</para></entry></row> <row> <entry align="right" valign="top"><para><anchor id="prod86" xreflabel="xmlNamespaces"/>xmlNamespaces</para></entry> <entry align="left" valign="top"><para>::= -&lt;XMLNAMESPACES&gt; &lt;LPAREN&gt; <link linkend="prod124">namespaceItem</link> ( &lt;COMMA&gt; <link linkend="prod124">namespaceItem</link> )* &lt;RPAREN&gt;</para></entry></row> +&lt;XMLNAMESPACES&gt; &lt;LPAREN&gt; <link linkend="prod125">namespaceItem</link> ( &lt;COMMA&gt; <link linkend="prod125">namespaceItem</link> )* &lt;RPAREN&gt;</para></entry></row> <row> -<entry align="right" valign="top"><para><anchor id="prod124" xreflabel="namespaceItem"/>namespaceItem</para></entry> +<entry align="right" valign="top"><para><anchor id="prod125" xreflabel="namespaceItem"/>namespaceItem</para></entry> <entry align="left" valign="top"><para>::= ( <link linkend="prod1">stringVal</link> &lt;AS&gt; <link linkend="prod2">id</link> )</para></entry></row> <row> @@ -883,7 +888,7 @@ <entry align="left" valign="top"><para>::= ( &lt;DEFAULT_KEYWORD&gt; <link linkend="prod1">stringVal</link> )</para></entry></row> <row> -<entry align="right" valign="top"><para><anchor id="prod121" xreflabel="idExpression"/>idExpression</para></entry> +<entry align="right" valign="top"><para><anchor id="prod122" xreflabel="idExpression"/>idExpression</para></entry> <entry align="left" valign="top"><para>::= <link linkend="prod2">id</link></para></entry></row> <row> @@ -895,11 +900,11 @@ <entry align="left" valign="top"><para>::= <link linkend="prod22">dataTypeString</link></para></entry></row> <row> -<entry align="right" valign="top"><para><anchor id="prod117" xreflabel="intervalType"/>intervalType</para></entry> +<entry align="right" valign="top"><para><anchor id="prod118" xreflabel="intervalType"/>intervalType</para></entry> <entry align="left" valign="top"><para>::= ( <link linkend="prod11">nonReserved</link> )</para></entry></row> <row> -<entry align="right" valign="top"><para><anchor id="prod111" xreflabel="literal"/>literal</para></entry> +<entry align="right" valign="top"><para><anchor id="prod112" xreflabel="literal"/>literal</para></entry> <entry align="left" valign="top"><para>::= ( <link linkend="prod1">stringVal</link> | &lt;INTEGERVAL&gt; | &lt;FLOATVAL&gt; | &lt;FALSE&gt; | &lt;TRUE&gt; | &lt;UNKNOWN&gt; | &lt;NULL&gt; | ( ( &lt;BOOLEANTYPE&gt; | &lt;TIMESTAMPTYPE&gt; | &lt;DATETYPE&gt; | &lt;TIMETYPE&gt; ) <link linkend="prod1">stringVal</link> &lt;RBRACE&gt; ) )</para></entry></row> </tbody> Modified: trunk/documentation/reference/src/main/docbook/en-US/content/scalar_functions.xml =================================================================== --- trunk/documentation/reference/src/main/docbook/en-US/content/scalar_functions.xml 2011-08-04 17:51:11 UTC (rev 3363) +++ trunk/documentation/reference/src/main/docbook/en-US/content/scalar_functions.xml 2011-08-05 18:33:39 UTC (rev 3364) @@ -1208,6 +1208,21 @@ </row> <row> <entry> + <para>EXTRACT(YEAR|MONTH|DAY|HOUR|MINUTE|SECOND FROM x)</para> + </entry> + <entry> + <para>Return the given field value from the date value x. + Produces the same result as the assoceated YEAR, MONTH, DAYOFMONTH, HOUR, MINUTE, SECOND functions. + </para> + <para>The SQL specification also allows for TIMEZONE_HOUR and TIMEZONE_MINUTE as extraction targets. + In Teiid all date values are in the timezone of the server.</para> + </entry> + <entry> + <para>x in {date, time, timestamp}, returns integer</para> + </entry> + </row> + <row> + <entry> <para>FORMATDATE(x, y)</para> </entry> <entry> @@ -1843,100 +1858,44 @@ <section> <title>System Functions</title> <para>System functions provide access to information in the Teiid system from within a query. </para> - <informaltable frame="all"> - <tgroup cols="3"> - <thead> - <row> - <entry> - <para>Function</para> - </entry> - <entry> - <para>Definition</para> - </entry> - <entry> - <para>Datatype Constraint</para> - </entry> - </row> - </thead> - <tbody> - <row> - <entry> - <para><code>COMMANDPAYLOAD()</code></para> - </entry> - <entry> - <para>Retrieve the string form of the command payload + <section> + <title>COMMANDPAYLOAD</title> + <para>Retrieve a string from the command payload or null if no command payload was specified. The command payload is set by a method on the Teiid JDBC API extensions on a per-query basis.</para> - </entry> - <entry> - <para>Returns a string</para> - </entry> - </row> - <row> - <entry> - <para><code>COMMANDPAYLOAD(key)</code></para> - </entry> - <entry> - <para>Cast the command payload object to a - java.util.Properties object and look up the specified key in - the object</para> - </entry> - <entry> - <para>key in {string}, return is string</para> - </entry> - </row> - <row> - <entry> - <para><code>ENV(key)</code></para> - </entry> - <entry> - <para>Retrieve a system environment property. The only key specific to the current session is ‘sessionid’. + <para><synopsis>COMMANDPAYLOAD([key])</synopsis></para> + <para>If the key parameter is provided, the command payload object is cast to a + java.util.Properties object and the corresponding property value for the key is returned. If the key is not specified the return value is the command payload object toString value.</para> + <para>key, return value are strings</para> + </section> + <section> + <title>ENV</title> + <para>Retrieve a system environment property.</para> + <para><synopsis>ENV(key)</synopsis></para> + <para>The only key specific to the current session is 'sessionid'. However the preferred mechanism for getting the session id is with the session_id() function. - To prevent untrusted access to system properties, the use of this function must be specifically enabled in the &jboss-beans; file. - </para> - </entry> - <entry> - <para>key in {string}, return is string</para> - </entry> - </row> - <row> - <entry> - <para><code>SESSION_ID()</code></para> - </entry> - <entry> - <para>Retrieve the string form of the current session id.</para> - </entry> - <entry> - <para>return is string</para> - </entry> - </row> - <row> - <entry> - <para><code>USER()</code></para> - </entry> - <entry> - <para>Retrieve the name of the user executing the - query</para> - </entry> - <entry> - <para>return is string</para> - </entry> - </row> - <row> - <entry> - <para><code>CURRENT_DATABASE()</code></para> - </entry> - <entry> - <para>Retrieve the catalog name of the database. Currently VDB's name is also the catalog name.</para> - </entry> - <entry> - <para>return is string</para> - </entry> - </row> - </tbody> - </tgroup> - </informaltable> + To prevent untrusted access to system properties, the use of this function must be specifically enabled in the &jboss-beans; file.</para> + <para>key, return value are strings</para> + </section> + <section> + <title>SESSION_ID</title> + <para>Retrieve the string form of the current session id.</para> + <para><synopsis>SESSION_ID()</synopsis></para> + <para>return value is string.</para> + </section> + <section> + <title>USER</title> + <para>Retrieve the name of the user executing the query.</para> + <para><synopsis>USER()</synopsis></para> + <para>return value is string.</para> + </section> + <section> + <title>CURRENT_DATABASE</title> + <para>Retrieve the catalog name of the database. The VDB name is always the catalog name.</para> + <para><synopsis>CURRENT_DATABASE()</synopsis></para> + <para>return value is string.</para> + </section> </section> <section id="xml_functions"> <title>XML Functions</title> 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 2011-08-04 17:51:11 UTC (rev 3363) +++ trunk/documentation/reference/src/main/docbook/en-US/content/sql_support.xml 2011-08-05 18:33:39 UTC (rev 3364) @@ -471,50 +471,81 @@ </listitem> </itemizedlist> <itemizedlist> - <para>Syntax Rules: + <para>Usage: </para> <listitem> - <para>expression (=|&lt;&gt;|!=|&lt;|>|&lt;=|>=) (expression|((ANY|ALL|SOME) subquery)) + <para><synopsis label="Usage">criteria AND|OR criteria</synopsis> </para> </listitem> <listitem> - <para>expression [NOT] IS NULL + <para><synopsis label="Usage">NOT criteria</synopsis> </para> </listitem> <listitem> - <para>expression [NOT] IN (expression[,expression]*)|subquery + <para><synopsis label="Usage">(criteria)</synopsis> </para> </listitem> <listitem> - <para>expression [NOT] LIKE expression [ESCAPE char] + <para><synopsis label="Usage">expression (=|&lt;&gt;|!=|&lt;|>|&lt;=|>=) (expression|((ANY|ALL|SOME) subquery))</synopsis> </para> </listitem> <listitem> - <para>EXISTS(subquery) + <para><synopsis label="Usage">expression [NOT] IS NULL</synopsis> </para> </listitem> <listitem> - <para>expression BETWEEN minExpression AND maxExpression + <para><synopsis label="Usage">expression [NOT] IN (expression[,expression]*)|subquery</synopsis> </para> </listitem> <listitem> - <para>criteria AND|OR criteria + <para><synopsis label="Usage">expression [NOT] LIKE pattern [ESCAPE char]</synopsis> + Matches the string expression against the given string pattern. + The pattern may contain % to match any number of characters and _ to match any single character. The escape character can be used to escape the match characters % and _.</para> + </listitem> + <listitem> + <para><synopsis label="Usage">expression [NOT] SIMILAR TO pattern [ESCAPE char]</synopsis> + SIMILAR TO is a cross between LIKE and standard regular expression syntax. % and _ are still used, rather than .* and . respectively.</para> + <note><para>Teiid does not exhaustively validate SIMILAR TO pattern values. Rather the pattern is converted to an equivalent regular expression. + Care should be taken not to rely on general regular expression features when using SIMILAR TO. If additional features are needed, then LIKE_REGEX should be used. + Usage of a non-literal pattern is discouraged as pushdown support is limited.</para></note> + </listitem> + <listitem> + <para><synopsis label="Usage">expression [NOT] LIKE_REGEX pattern</synopsis> + LIKE_REGEX allows for standard regular expression syntax to be used for matching. This differs from SIMILAR TO and LIKE in that the escape character is no longer used (\ is already the + standard escape mechansim in regular expressions and % and _ have no special meaning. + The runtime engine uses the JRE implementation of regular expressions - see the <ulink url="http://download.oracle.com/javase/6/docs/api/java/util/regex/Pa... class for details. + <note><para>Teiid does not exhaustively validate LIKE_REGEX pattern values. It is possible to use JRE only regular expression features that are not specified by the SQL specification. Additional not all sources support + the same regular expression flavor or extensions. Care should be taken in pushdown situations to ensure that the pattern used will have same meaning in Teiid and across all applicable sources.</para></note> </para> </listitem> <listitem> - <para>NOT criteria + <para><synopsis label="Usage">EXISTS(subquery)</synopsis> </para> </listitem> <listitem> - <para>expression + <para><synopsis label="Usage">expression [NOT] BETWEEN minExpression AND maxExpression</synopsis> + Teiid converts BETWEEN into the equivalent form expression &gt;= minExpression AND expression &lt;= maxExpression </para> </listitem> <listitem> - <para>Criteria may be nested using parenthesis. + <para><synopsis label="Usage">expression</synopsis> + Where expression has type boolean. </para> </listitem> </itemizedlist> <itemizedlist> + <para>Syntax Rules: + </para> + <listitem> + <para>The precedence ordering from lowest to highest is comparison, NOT, AND, OR + </para> + </listitem> + <listitem> + <para>Criteria nested by parenthesis will be logically evaluated prior to evaluating the parent criteria. + </para> + </listitem> + </itemizedlist> + <itemizedlist> <para>Some examples of valid criteria are: </para> <listitem> Modified: trunk/engine/src/main/java/org/teiid/dqp/internal/datamgr/CapabilitiesConverter.java =================================================================== --- trunk/engine/src/main/java/org/teiid/dqp/internal/datamgr/CapabilitiesConverter.java 2011-08-04 17:51:11 UTC (rev 3363) +++ trunk/engine/src/main/java/org/teiid/dqp/internal/datamgr/CapabilitiesConverter.java 2011-08-05 18:33:39 UTC (rev 3364) @@ -103,6 +103,9 @@ tgtCaps.setCapabilitySupport(Capability.ELEMENTARY_OLAP, srcCaps.supportsAdvancedOlapOperations()); tgtCaps.setCapabilitySupport(Capability.WINDOW_FUNCTION_ORDER_BY_AGGREGATES, srcCaps.supportsWindowOrderByWithAggregates()); tgtCaps.setCapabilitySupport(Capability.QUERY_AGGREGATES_ARRAY, srcCaps.supportsArrayAgg()); + tgtCaps.setCapabilitySupport(Capability.CRITERIA_SIMILAR, srcCaps.supportsSimilarTo()); + tgtCaps.setCapabilitySupport(Capability.CRITERIA_LIKE_REGEX, srcCaps.supportsLikeRegex()); + List functions = srcCaps.getSupportedFunctions(); if(functions != null && functions.size() > 0) { Iterator iter = functions.iterator(); Modified: trunk/engine/src/main/java/org/teiid/dqp/internal/datamgr/LanguageBridgeFactory.java =================================================================== --- trunk/engine/src/main/java/org/teiid/dqp/internal/datamgr/LanguageBridgeFactory.java 2011-08-04 17:51:11 UTC (rev 3363) +++ trunk/engine/src/main/java/org/teiid/dqp/internal/datamgr/LanguageBridgeFactory.java 2011-08-05 18:33:39 UTC (rev 3364) @@ -363,10 +363,12 @@ if(criteria.getEscapeChar() != MatchCriteria.NULL_ESCAPE_CHAR) { escapeChar = new Character(criteria.getEscapeChar()); } - return new Like(translate(criteria.getLeftExpression()), + Like like = new Like(translate(criteria.getLeftExpression()), translate(criteria.getRightExpression()), escapeChar, criteria.isNegated()); + like.setMode(criteria.getMode()); + return like; } In translate(SetCriteria criteria) { Modified: trunk/engine/src/main/java/org/teiid/query/eval/Evaluator.java =================================================================== --- trunk/engine/src/main/java/org/teiid/query/eval/Evaluator.java 2011-08-04 17:51:11 UTC (rev 3363) +++ trunk/engine/src/main/java/org/teiid/query/eval/Evaluator.java 2011-08-05 18:33:39 UTC (rev 3364) @@ -40,7 +40,6 @@ import java.util.Set; import java.util.regex.Matcher; import java.util.regex.Pattern; -import java.util.regex.PatternSyntaxException; import javax.xml.transform.stream.StreamResult; @@ -68,6 +67,7 @@ import org.teiid.core.types.XMLType.Type; import org.teiid.core.types.basic.StringToSQLXMLTransform; import org.teiid.core.util.EquivalenceUtil; +import org.teiid.language.Like.MatchMode; import org.teiid.metadata.FunctionMethod.PushDown; import org.teiid.query.QueryPlugin; import org.teiid.query.function.FunctionDescriptor; @@ -203,9 +203,15 @@ } } - private final static char[] REGEX_RESERVED = new char[] {'$', '(', ')', '*', '.', '?', '[', '\\', ']', '^', '{', '|', '}'}; //in sorted order - private final static MatchCriteria.PatternTranslator LIKE_TO_REGEX = new MatchCriteria.PatternTranslator(".*", ".", REGEX_RESERVED, '\\'); //$NON-NLS-1$ //$NON-NLS-2$ - + public final static char[] REGEX_RESERVED = new char[] {'$', '(', ')', '*', '.', '?', '[', '\\', ']', '^', '{', '|', '}'}; //in sorted order + private final static MatchCriteria.PatternTranslator LIKE_TO_REGEX = new MatchCriteria.PatternTranslator(new char[] {'%', '_'}, new String[] {".*", "."}, REGEX_RESERVED, '\\', Pattern.DOTALL); //$NON-NLS-1$ //$NON-NLS-2$ + + private final static char[] SIMILAR_REGEX_RESERVED = new char[] {'$', '.', '\\', '^'}; //in sorted order + public final static MatchCriteria.PatternTranslator SIMILAR_TO_REGEX = new MatchCriteria.PatternTranslator( + new char[] {'%', '(', ')', '*', '?', '+', '[', ']', '_', '{', '|', '}'}, + new String[] {"([a]|[^a])*", "(", ")", "*", "?", "+", //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ //$NON-NLS-4$ //$NON-NLS-5$ //$NON-NLS-6$ + "[", "]", "([a]|[^a])", "{", "|", "}"}, SIMILAR_REGEX_RESERVED, '\\', 0); //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ //$NON-NLS-4$ //$NON-NLS-5$ //$NON-NLS-6$ + private Map elements; protected ProcessorDataManager dataMgr; @@ -404,27 +410,31 @@ return null; } - result = match(rightValue, criteria.getEscapeChar(), leftValue); + result = match(rightValue, criteria.getEscapeChar(), leftValue, criteria.getMode()); return Boolean.valueOf(result ^ criteria.isNegated()); } - private boolean match(String pattern, char escape, CharSequence search) + private boolean match(String pattern, char escape, CharSequence search, MatchMode mode) throws ExpressionEvaluationException { - StringBuffer rePattern = LIKE_TO_REGEX.translate(pattern, escape); + Pattern patternRegex = null; + switch (mode) { + case LIKE: + patternRegex = LIKE_TO_REGEX.translate(pattern, escape); + break; + case SIMILAR: + patternRegex = SIMILAR_TO_REGEX.translate(pattern, escape); + break; + case REGEX: + patternRegex = MatchCriteria.getPattern(pattern, pattern, 0); + break; + default: + throw new AssertionError(); + } - // Insert leading and trailing characters to ensure match of full string - rePattern.insert(0, '^'); - rePattern.append('$'); - - try { - Pattern patternRegex = Pattern.compile(rePattern.toString(), Pattern.DOTALL); - Matcher matcher = patternRegex.matcher(search); - return matcher.matches(); - } catch(PatternSyntaxException e) { - throw new ExpressionEvaluationException(e, "ERR.015.006.0014", QueryPlugin.Util.getString("ERR.015.006.0014", new Object[]{pattern, e.getMessage()})); //$NON-NLS-1$ //$NON-NLS-2$ - } + Matcher matcher = patternRegex.matcher(search); + return matcher.matches(); } private Boolean evaluate(AbstractSetCriteria criteria, List<?> tuple) Modified: trunk/engine/src/main/java/org/teiid/query/metadata/TempCapabilitiesFinder.java =================================================================== --- trunk/engine/src/main/java/org/teiid/query/metadata/TempCapabilitiesFinder.java 2011-08-04 17:51:11 UTC (rev 3363) +++ trunk/engine/src/main/java/org/teiid/query/metadata/TempCapabilitiesFinder.java 2011-08-05 18:33:39 UTC (rev 3364) @@ -52,6 +52,8 @@ tempCaps.setCapabilitySupport(Capability.CRITERIA_ISNULL, true); tempCaps.setCapabilitySupport(Capability.CRITERIA_LIKE, true); tempCaps.setCapabilitySupport(Capability.CRITERIA_LIKE_ESCAPE, true); + tempCaps.setCapabilitySupport(Capability.CRITERIA_LIKE_REGEX, true); + tempCaps.setCapabilitySupport(Capability.CRITERIA_SIMILAR, true); tempCaps.setCapabilitySupport(Capability.QUERY_AGGREGATES_COUNT_STAR, true); } return tempCaps; 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 2011-08-04 17:51:11 UTC (rev 3363) +++ trunk/engine/src/main/java/org/teiid/query/optimizer/capabilities/SourceCapabilities.java 2011-08-05 18:33:39 UTC (rev 3364) @@ -324,7 +324,9 @@ ADVANCED_OLAP, QUERY_AGGREGATES_ARRAY, ELEMENTARY_OLAP, - WINDOW_FUNCTION_ORDER_BY_AGGREGATES + WINDOW_FUNCTION_ORDER_BY_AGGREGATES, + CRITERIA_SIMILAR, + CRITERIA_LIKE_REGEX } public enum Scope { Modified: trunk/engine/src/main/java/org/teiid/query/optimizer/relational/rules/CriteriaCapabilityValidatorVisitor.java =================================================================== --- trunk/engine/src/main/java/org/teiid/query/optimizer/relational/rules/CriteriaCapabilityValidatorVisitor.java 2011-08-04 17:51:11 UTC (rev 3363) +++ trunk/engine/src/main/java/org/teiid/query/optimizer/relational/rules/CriteriaCapabilityValidatorVisitor.java 2011-08-05 18:33:39 UTC (rev 3364) @@ -270,18 +270,32 @@ } public void visit(MatchCriteria obj) { - // Check if compares are allowed - if(! this.caps.supportsCapability(Capability.CRITERIA_LIKE)) { - markInvalid(obj, "Like is not supported by source"); //$NON-NLS-1$ - return; - } + switch (obj.getMode()) { + case LIKE: + if(! this.caps.supportsCapability(Capability.CRITERIA_LIKE)) { + markInvalid(obj, "Like is not supported by source"); //$NON-NLS-1$ + return; + } + break; + case SIMILAR: + if(! this.caps.supportsCapability(Capability.CRITERIA_SIMILAR)) { + markInvalid(obj, "Similar to is not supported by source"); //$NON-NLS-1$ + return; + } + break; + case REGEX: + if(! this.caps.supportsCapability(Capability.CRITERIA_LIKE_REGEX)) { + markInvalid(obj, "Like_regex is not supported by source"); //$NON-NLS-1$ + return; + } + break; + } // Check ESCAPE char if necessary - if(obj.getEscapeChar() != MatchCriteria.NULL_ESCAPE_CHAR) { - if(! this.caps.supportsCapability(Capability.CRITERIA_LIKE_ESCAPE)) { - markInvalid(obj, "Like escape is not supported by source"); //$NON-NLS-1$ - return; - } + if(obj.getEscapeChar() != MatchCriteria.NULL_ESCAPE_CHAR + && ! this.caps.supportsCapability(Capability.CRITERIA_LIKE_ESCAPE)) { + markInvalid(obj, "Like escape is not supported by source"); //$NON-NLS-1$ + return; } //check NOT Modified: trunk/engine/src/main/java/org/teiid/query/optimizer/relational/rules/NewCalculateCostUtil.java =================================================================== --- trunk/engine/src/main/java/org/teiid/query/optimizer/relational/rules/NewCalculateCostUtil.java 2011-08-04 17:51:11 UTC (rev 3363) +++ trunk/engine/src/main/java/org/teiid/query/optimizer/relational/rules/NewCalculateCostUtil.java 2011-08-05 18:33:39 UTC (rev 3364) @@ -43,6 +43,7 @@ import org.teiid.common.buffer.BufferManager; import org.teiid.core.TeiidComponentException; import org.teiid.core.types.DataTypeManager; +import org.teiid.language.Like.MatchMode; import org.teiid.logging.LogConstants; import org.teiid.logging.LogManager; import org.teiid.query.QueryPlugin; @@ -917,7 +918,8 @@ Expression matchExpression = criteria.getRightExpression(); if(matchExpression instanceof Constant && ((Constant)matchExpression).getType().equals(DataTypeManager.DefaultDataClasses.STRING)) { String compareValue = (String) ((Constant)matchExpression).getValue(); - if(compareValue != null && compareValue.indexOf('%') < 0) { + if(criteria.getMode() != MatchMode.REGEX && criteria.getEscapeChar() == MatchCriteria.NULL_ESCAPE_CHAR + && compareValue != null && compareValue.indexOf('%') < 0) { return (childCost / 2) * (1 / 3f + 1 / ndv); //without knowing length constraints we'll make an average guess } } else if (EvaluatableVisitor.willBecomeConstant(criteria.getLeftExpression())) { Modified: trunk/engine/src/main/java/org/teiid/query/rewriter/QueryRewriter.java =================================================================== --- trunk/engine/src/main/java/org/teiid/query/rewriter/QueryRewriter.java 2011-08-04 17:51:11 UTC (rev 3363) +++ trunk/engine/src/main/java/org/teiid/query/rewriter/QueryRewriter.java 2011-08-05 18:33:39 UTC (rev 3364) @@ -56,6 +56,7 @@ import org.teiid.core.util.Assertion; import org.teiid.core.util.TimestampWithTimezone; import org.teiid.language.SQLConstants; +import org.teiid.language.Like.MatchMode; import org.teiid.language.SQLConstants.NonReserved; import org.teiid.query.QueryPlugin; import org.teiid.query.eval.Evaluator; @@ -2023,24 +2024,34 @@ Constant constant = (Constant) rightExpr; String value = (String) constant.getValue(); - char escape = criteria.getEscapeChar(); - - // Check whether escape char is unnecessary and remove it - if(escape != MatchCriteria.NULL_ESCAPE_CHAR && value.indexOf(escape) < 0) { - criteria.setEscapeChar(MatchCriteria.NULL_ESCAPE_CHAR); + if (criteria.getMode() != MatchMode.REGEX) { + char escape = criteria.getEscapeChar(); + + // Check whether escape char is unnecessary and remove it + if(escape != MatchCriteria.NULL_ESCAPE_CHAR && value.indexOf(escape) < 0) { + criteria.setEscapeChar(MatchCriteria.NULL_ESCAPE_CHAR); + } + + // if the value of this string constant is '%', then we know the crit will + // always be true + if ( value.equals( String.valueOf(MatchCriteria.WILDCARD_CHAR)) ) { + return getSimpliedCriteria(criteria, criteria.getLeftExpression(), !criteria.isNegated(), true); + } + + if (criteria.getMode() == MatchMode.SIMILAR) { + //regex is more widely supported + criteria.setMode(MatchMode.REGEX); + criteria.setRightExpression(new Constant(Evaluator.SIMILAR_TO_REGEX.getPatternString(value, escape))); + criteria.setEscapeChar(MatchCriteria.NULL_ESCAPE_CHAR); + } else if(DataTypeManager.DefaultDataClasses.STRING.equals(criteria.getLeftExpression().getType()) + && value.indexOf(escape) < 0 + && value.indexOf(MatchCriteria.MATCH_CHAR) < 0 + && value.indexOf(MatchCriteria.WILDCARD_CHAR) < 0) { + // if both left and right expressions are strings, and the LIKE match characters ('*', '_') are not present + // in the right expression, rewrite the criteria as EQUALs rather than LIKE + return rewriteCriteria(new CompareCriteria(criteria.getLeftExpression(), criteria.isNegated()?CompareCriteria.NE:CompareCriteria.EQ, criteria.getRightExpression())); + } } - - // if the value of this string constant is '%', then we know the crit will - // always be true - if ( value.equals( String.valueOf(MatchCriteria.WILDCARD_CHAR)) ) { - return getSimpliedCriteria(criteria, criteria.getLeftExpression(), !criteria.isNegated(), true); - } - - // if both left and right expressions are strings, and the LIKE match characters ('*', '_') are not present - // in the right expression, rewrite the criteria as EQUALs rather than LIKE - if(DataTypeManager.DefaultDataClasses.STRING.equals(criteria.getLeftExpression().getType()) && value.indexOf(escape) < 0 && value.indexOf(MatchCriteria.MATCH_CHAR) < 0 && value.indexOf(MatchCriteria.WILDCARD_CHAR) < 0) { - return rewriteCriteria(new CompareCriteria(criteria.getLeftExpression(), criteria.isNegated()?CompareCriteria.NE:CompareCriteria.EQ, criteria.getRightExpression())); - } } return criteria; Modified: trunk/engine/src/main/java/org/teiid/query/sql/lang/MatchCriteria.java =================================================================== --- trunk/engine/src/main/java/org/teiid/query/sql/lang/MatchCriteria.java 2011-08-04 17:51:11 UTC (rev 3363) +++ trunk/engine/src/main/java/org/teiid/query/sql/lang/MatchCriteria.java 2011-08-05 18:33:39 UTC (rev 3364) @@ -23,10 +23,15 @@ package org.teiid.query.sql.lang; import java.util.Arrays; +import java.util.List; +import java.util.regex.Pattern; +import java.util.regex.PatternSyntaxException; import org.teiid.api.exception.query.ExpressionEvaluationException; import org.teiid.core.util.EquivalenceUtil; import org.teiid.core.util.HashCodeUtil; +import org.teiid.core.util.LRUCache; +import org.teiid.language.Like.MatchMode; import org.teiid.query.QueryPlugin; import org.teiid.query.sql.LanguageVisitor; import org.teiid.query.sql.lang.PredicateCriteria.Negatable; @@ -61,7 +66,8 @@ private char escapeChar = NULL_ESCAPE_CHAR; /** Negation flag. Indicates whether the criteria expression contains a NOT. */ - private boolean negated = false; + private boolean negated; + private MatchMode mode = MatchMode.LIKE; /** * Constructs a default instance of this class. @@ -200,10 +206,14 @@ MatchCriteria mc = (MatchCriteria)obj; - if (isNegated() ^ mc.isNegated()) { + if (isNegated() != mc.isNegated()) { return false; } + if (this.mode != mc.mode) { + return false; + } + return getEscapeChar() == mc.getEscapeChar() && EquivalenceUtil.areEqual(getLeftExpression(), mc.getLeftExpression()) && EquivalenceUtil.areEqual(getRightExpression(), mc.getRightExpression()); @@ -224,18 +234,36 @@ } MatchCriteria criteriaCopy = new MatchCriteria(leftCopy, rightCopy, getEscapeChar()); criteriaCopy.setNegated(isNegated()); + criteriaCopy.mode = mode; return criteriaCopy; } + private final static LRUCache<List<?>, Pattern> patternCache = new LRUCache<List<?>, Pattern>(100); + + public static Pattern getPattern(String newPattern, String originalPattern, int flags) throws ExpressionEvaluationException { + List<?> key = Arrays.asList(newPattern, flags); + Pattern p = patternCache.get(key); + if (p == null) { + try { + p = Pattern.compile(newPattern, Pattern.DOTALL); + patternCache.put(key, p); + } catch(PatternSyntaxException e) { + throw new ExpressionEvaluationException(e, "ERR.015.006.0014", QueryPlugin.Util.getString("ERR.015.006.0014", new Object[]{originalPattern, e.getMessage()})); //$NON-NLS-1$ //$NON-NLS-2$ + } + } + return p; + } + /** * <p>Utility to convert the pattern into a different match syntax</p> */ public static class PatternTranslator { - private char[] reserved; private char newEscape; - private String newWildCard; - private String newSingleMatch; + private char[] toReplace; + private String[] replacements; + private int flags; + private final LRUCache<List<?>, Pattern> cache = new LRUCache<List<?>, Pattern>(100); /** * @param newWildCard replacement for % @@ -243,17 +271,29 @@ * @param reserved sorted array of reserved chars in the new match syntax * @param newEscape escape char in the new syntax */ - public PatternTranslator(String newWildCard, String newSingleMatch, char[] reserved, char newEscape) { + public PatternTranslator(char[] toReplace, String[] replacements, char[] reserved, char newEscape, int flags) { this.reserved = reserved; this.newEscape = newEscape; - this.newSingleMatch = newSingleMatch; - this.newWildCard = newWildCard; + this.toReplace = toReplace; + this.replacements = replacements; + this.flags = flags; } - public StringBuffer translate(String pattern, char escape) throws ExpressionEvaluationException { + public Pattern translate(String pattern, char escape) throws ExpressionEvaluationException { + List<?> key = Arrays.asList(pattern, escape); + Pattern result = cache.get(key); + if (result == null) { + String newPattern = getPatternString(pattern, escape); + result = getPattern(newPattern, pattern, flags); + cache.put(key, result); + } + return result; + } + + public String getPatternString(String pattern, char escape) + throws ExpressionEvaluationException { + StringBuffer newPattern = new StringBuffer("^"); //$NON-NLS-1$ - StringBuffer newPattern = new StringBuffer(); - boolean escaped = false; for (int i = 0; i < pattern.length(); i++) { @@ -266,25 +306,21 @@ } else { escaped = true; } - } else if (character == MatchCriteria.WILDCARD_CHAR) { - if (escaped) { - appendCharacter(newPattern, character); - escaped = false; - } else { - newPattern.append(newWildCard); - } - } else if (character == MatchCriteria.MATCH_CHAR) { - if (escaped) { - appendCharacter(newPattern, character); - escaped = false; - } else { - newPattern.append(newSingleMatch); - } } else { - if (escaped) { - throw new ExpressionEvaluationException(QueryPlugin.Util.getString("MatchCriteria.invalid_escape", new Object[] {pattern, new Character(escape)})); //$NON-NLS-1$ - } - appendCharacter(newPattern, character); + int index = Arrays.binarySearch(toReplace, character); + if (index >= 0) { + if (escaped) { + appendCharacter(newPattern, character); + escaped = false; + } else { + newPattern.append(replacements[index]); + } + } else { + if (escaped) { + throw new ExpressionEvaluationException(QueryPlugin.Util.getString("MatchCriteria.invalid_escape", new Object[] {pattern, new Character(escape)})); //$NON-NLS-1$ + } + appendCharacter(newPattern, character); + } } } @@ -292,8 +328,9 @@ throw new ExpressionEvaluationException(QueryPlugin.Util.getString("MatchCriteria.invalid_escape", new Object[] {pattern, new Character(escape)})); //$NON-NLS-1$ } - return newPattern; - } + newPattern.append('$'); + return newPattern.toString(); + } private void appendCharacter(StringBuffer newPattern, char character) { if (Arrays.binarySearch(this.reserved, character) >= 0) { @@ -304,4 +341,12 @@ } + public MatchMode getMode() { + return mode; + } + + public void setMode(MatchMode mode) { + this.mode = mode; + } + } // END CLASS 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 2011-08-04 17:51:11 UTC (rev 3363) +++ trunk/engine/src/main/java/org/teiid/query/sql/visitor/SQLStringVisitor.java 2011-08-05 18:33:39 UTC (rev 3364) @@ -569,7 +569,19 @@ append(NOT); append(SPACE); } - append(LIKE); + switch (obj.getMode()) { + case SIMILAR: + append(SIMILAR); + append(SPACE); + append(TO); + break; + case LIKE: + append(LIKE); + break; + case REGEX: + append(LIKE_REGEX); + break; + } append(SPACE); visitNode(obj.getRightExpression()); @@ -578,7 +590,7 @@ append(SPACE); append(ESCAPE); append(" '"); //$NON-NLS-1$ - append("" + obj.getEscapeChar()); //$NON-NLS-1$ + append(String.valueOf(obj.getEscapeChar())); append("'"); //$NON-NLS-1$ } } @@ -593,16 +605,16 @@ public void visit( Option obj ) { append(OPTION); - Collection groups = obj.getDependentGroups(); + Collection<String> groups = obj.getDependentGroups(); if (groups != null && groups.size() > 0) { append(" "); //$NON-NLS-1$ append(MAKEDEP); append(" "); //$NON-NLS-1$ - Iterator iter = groups.iterator(); + Iterator<String> iter = groups.iterator(); while (iter.hasNext()) { - outputDisplayName((String)iter.next()); + outputDisplayName(iter.next()); if (iter.hasNext()) { append(", ");//$NON-NLS-1$ Modified: trunk/engine/src/main/java/org/teiid/query/tempdata/IndexInfo.java =================================================================== --- trunk/engine/src/main/java/org/teiid/query/tempdata/IndexInfo.java 2011-08-04 17:51:11 UTC (rev 3363) +++ trunk/engine/src/main/java/org/teiid/query/tempdata/IndexInfo.java 2011-08-05 18:33:39 UTC (rev 3364) @@ -23,6 +23,7 @@ package org.teiid.query.tempdata; import java.util.ArrayList; +import java.util.Arrays; import java.util.Collection; import java.util.Iterator; import java.util.LinkedList; @@ -32,8 +33,10 @@ import org.teiid.common.buffer.TupleBrowser; import org.teiid.common.buffer.TupleSource; import org.teiid.core.TeiidComponentException; +import org.teiid.language.Like.MatchMode; import org.teiid.logging.LogConstants; import org.teiid.logging.LogManager; +import org.teiid.query.eval.Evaluator; import org.teiid.query.processor.CollectionTupleSource; import org.teiid.query.processor.relational.RelationalNode; import org.teiid.query.sql.lang.CompareCriteria; @@ -126,22 +129,45 @@ Constant value = (Constant)matchCriteria.getRightExpression(); String pattern = (String)value.getValue(); boolean escaped = false; + char escapeChar = matchCriteria.getEscapeChar(); + if (matchCriteria.getMode() == MatchMode.REGEX) { + escapeChar = '\\'; + } StringBuilder prefix = new StringBuilder(); - for (int j = 0; i < pattern.length(); j++) { + + if (pattern.length() > 0 && matchCriteria.getMode() == MatchMode.REGEX && pattern.charAt(0) != '^') { + //make the assumption that we require an anchor + continue; + } + + for (int j = matchCriteria.getMode() == MatchMode.REGEX?1:0; i < pattern.length(); j++) { char character = pattern.charAt(j); - if (character == matchCriteria.getEscapeChar() && character != MatchCriteria.NULL_ESCAPE_CHAR) { + if (character == escapeChar && character != MatchCriteria.NULL_ESCAPE_CHAR) { if (escaped) { prefix.append(character); escaped = false; } else { escaped = true; } - } else if (character == MatchCriteria.WILDCARD_CHAR || character == MatchCriteria.MATCH_CHAR) { - break; + continue; + } + if (!escaped) { + if (matchCriteria.getMode() == MatchMode.LIKE) { + if (character == MatchCriteria.WILDCARD_CHAR || character == MatchCriteria.MATCH_CHAR) { + break; + } + } else { + int index = Arrays.binarySearch(Evaluator.REGEX_RESERVED, character); + if (index >= 0 && pattern.length() > 0) { + getRegexPrefix(pattern, escapeChar, prefix, j, character); + break; + } + } } else { - prefix.append(character); + escaped = false; } + prefix.append(character); } if (prefix.length() > 0) { this.addCondition(i, new Constant(prefix.toString()), CompareCriteria.GE); @@ -158,6 +184,32 @@ } } } + + private void getRegexPrefix(String pattern, char escapeChar, + StringBuilder prefix, int j, char character) { + boolean escaped = false; + //check the rest of the expression for | + int level = 0; + for (int k = j; k < pattern.length(); k++) { + character = pattern.charAt(k); + if (character == escapeChar && character != MatchCriteria.NULL_ESCAPE_CHAR) { + escaped = !escaped; + } else if (!escaped) { + if (character == '(') { + level++; + } else if (character == ')') { + level--; + } else if (character == '|' && level == 0) { + prefix.setLength(0); //TODO: turn this into an IN condition + return; + } + } + } + + if (character == '{' || character == '?' || character == '*') { + prefix.setLength(prefix.length() - 1); + } + } void addCondition(int i, Constant value, int comparisionMode) { switch (comparisionMode) { Modified: trunk/engine/src/main/javacc/org/teiid/query/parser/SQLParser.jj =================================================================== --- trunk/engine/src/main/javacc/org/teiid/query/parser/SQLParser.jj 2011-08-04 17:51:11 UTC (rev 3363) +++ trunk/engine/src/main/javacc/org/teiid/query/parser/SQLParser.jj 2011-08-05 18:33:39 UTC (rev 3364) @@ -23,6 +23,7 @@ import org.teiid.query.sql.symbol.*; import org.teiid.metadata.*; import org.teiid.metadata.BaseColumn.NullType; +import org.teiid.language.Like.MatchMode; /** * <p>The SQLParser is a JavaCC-generated parser that reads a SQL string and produces a @@ -193,6 +194,7 @@ | <LARGE: "large"> | <LEADING: "leading"> | <LIKE: "like"> +| <LIKE_REGEX: "like_regex"> | <LIMIT: "limit"> | <LOCAL: "local"> | <LOOP: "loop"> @@ -2692,6 +2694,7 @@ [( LOOKAHEAD(2) ex=betweenCrit(info, ex) | LOOKAHEAD(2) ex=matchCrit(info, ex) | + LOOKAHEAD(2) ex=regexMatchCrit(info, ex) | ex=setCrit(info, ex) | ex=isNullCrit(info, ex) | LOOKAHEAD(operator() (<ANY>|<SOME>|<ALL>) subquery(info)) ex=subqueryCompareCriteria(info, ex) | @@ -2841,13 +2844,14 @@ Character esc = null; Expression value = null; boolean negated = false; + boolean similar = false; } { [<NOT> {negated = true;}] - <LIKE> + (<LIKE>|(<SIMILAR> <TO> {similar = true;})) value=commonValueExpression(info) - [ <ESCAPE> esc = charVal(info, "LIKE ESCAPE") | - (<LBRACE> <ESCAPE> esc = charVal(info, "LIKE ESCAPE") <RBRACE>) + [ <ESCAPE> esc = charVal(info, "LIKE/SIMILAR TO ESCAPE") | + (<LBRACE> <ESCAPE> esc = charVal(info, "LIKE/SIMILAR TO ESCAPE") <RBRACE>) ] { MatchCriteria matchcriteria = new MatchCriteria(expression, value); @@ -2855,10 +2859,28 @@ if(esc != null) { matchcriteria.setEscapeChar(esc.charValue()); } + matchcriteria.setMode(similar?MatchMode.SIMILAR:MatchMode.LIKE); return matchcriteria; } } +MatchCriteria regexMatchCrit(ParseInfo info, Expression expression) : +{ + Expression value = null; + boolean negated = false; +} +{ + [<NOT> {negated = true;}] + <LIKE_REGEX> + value=commonValueExpression(info) + { + MatchCriteria matchcriteria = new MatchCriteria(expression, value); + matchcriteria.setNegated(negated); + matchcriteria.setMode(MatchMode.REGEX); + return matchcriteria; + } +} + Character charVal(ParseInfo info, String type) : { String escStr = null; @@ -3591,7 +3613,26 @@ <RPAREN> ) | - LOOKAHEAD(1, {getToken(1).image.equalsIgnoreCase("TRIM")}) ( + LOOKAHEAD(2, {getToken(1).image.equalsIgnoreCase("EXTRACT")}) ( + nonReserved("EXTRACT") + <LPAREN> + (funcToken = <YEAR> | + funcToken = <MONTH> | + funcToken = <DAY> | + funcToken = <HOUR> | + funcToken = <MINUTE> | + funcToken = <SECOND>) + <FROM> expression = expression(info) + { + if (funcToken.image.equalsIgnoreCase("DAY")) { + funcName = "DAYOFMONTH"; + } + args.add(expression); + } + <RPAREN> + ) + | + LOOKAHEAD(2, {getToken(1).image.equalsIgnoreCase("TRIM")}) ( funcName= nonReserved("TRIM") <LPAREN> { funcToken = null; expression = null;} Modified: trunk/engine/src/test/java/org/teiid/query/parser/TestParser.java =================================================================== --- trunk/engine/src/test/java/org/teiid/query/parser/TestParser.java 2011-08-04 17:51:11 UTC (rev 3363) +++ trunk/engine/src/test/java/org/teiid/query/parser/TestParser.java 2011-08-05 18:33:39 UTC (rev 3364) @@ -2790,7 +2790,7 @@ } @Test public void testLikeWithEscapeException(){ - helpException("SELECT a from db.g where b like '#String' escape '#1'", "Parsing error: LIKE ESCAPE value must be a single character: [#1]."); //$NON-NLS-1$ //$NON-NLS-2$ + helpException("SELECT a from db.g where b like '#String' escape '#1'", "Parsing error: LIKE/SIMILAR TO ESCAPE value must be a single character: [#1]."); //$NON-NLS-1$ //$NON-NLS-2$ } /** SELECT "date"."time" from db.g */ Modified: trunk/engine/src/test/java/org/teiid/query/processor/TestTempTables.java =================================================================== --- trunk/engine/src/test/java/org/teiid/query/processor/TestTempTables.java 2011-08-04 17:51:11 UTC (rev 3363) +++ trunk/engine/src/test/java/org/teiid/query/processor/TestTempTables.java 2011-08-05 18:33:39 UTC (rev 3364) @@ -197,6 +197,15 @@ execute("select * from x where e1 like 'z%'", new List[0]); //$NON-NLS-1$ } + @Test public void testLikeRegexWithIndex() throws Exception { + execute("create local temporary table x (e1 string, e2 integer, primary key (e1))", new List[] {Arrays.asList(0)}); //$NON-NLS-1$ + execute("insert into x (e2, e1) values (3, 'ab')", new List[] {Arrays.asList(1)}); //$NON-NLS-1$ + execute("insert into x (e2, e1) values (2, 'b')", new List[] {Arrays.asList(1)}); //$NON-NLS-1$ + execute("select * from x where e1 like_regex '^b?.*'", new List[] {Arrays.asList("ab", 3), Arrays.asList("b", 2)}); //$NON-NLS-1$ + execute("select * from x where e1 like_regex '^ab+.*'", new List[] {Arrays.asList("ab", 3)}); //$NON-NLS-1$ + execute("select * from x where e1 like_regex '^ab|b'", new List[] {Arrays.asList("ab", 3), Arrays.asList("b", 2)}); //$NON-NLS-1$ + } + @Test public void testIsNullWithIndex() throws Exception { execute("create local temporary table x (e1 string, e2 integer, primary key (e1))", new List[] {Arrays.asList(0)}); //$NON-NLS-1$ execute("insert into x (e2, e1) values (3, null)", new List[] {Arrays.asList(1)}); //$NON-NLS-1$ Modified: trunk/engine/src/test/java/org/teiid/query/processor/eval/TestExpressionEvaluator.java =================================================================== --- trunk/engine/src/test/java/org/teiid/query/processor/eval/TestExpressionEvaluator.java 2011-08-04 17:51:11 UTC (rev 3363) +++ trunk/engine/src/test/java/org/teiid/query/processor/eval/TestExpressionEvaluator.java 2011-08-05 18:33:39 UTC (rev 3364) @@ -437,4 +437,69 @@ assertEquals("bc", Evaluator.evaluate(ex)); } + @Test public void testExtract() throws Exception { + Expression ex = TestFunctionResolving.getExpression("extract(year from cast('2011-01-01' as date))"); + assertEquals(2011, Evaluator.evaluate(ex)); + } + + @Test public void testExtract1() throws Exception { + Expression ex = TestFunctionResolving.getExpression("extract(day from cast('2011-01-01' as date))"); + assertEquals(1, Evaluator.evaluate(ex)); + } + + @Test public void testSimilarTo() throws Exception { + Expression ex = TestFunctionResolving.getExpression("'aaaxy' similar to 'a+%'"); + assertEquals(Boolean.TRUE, Evaluator.evaluate(ex)); + } + + @Test public void testSimilarTo1() throws Exception { + Expression ex = TestFunctionResolving.getExpression("'xaay' similar to 'xa{2,3}y'"); + assertEquals(Boolean.TRUE, Evaluator.evaluate(ex)); + } + + @Test public void testSimilarTo2() throws Exception { + Expression ex = TestFunctionResolving.getExpression("'xay' similar to 'xa{2,3}y'"); + assertEquals(Boolean.FALSE, Evaluator.evaluate(ex)); + } + + @Test(expected=ExpressionEvaluationException.class) public void testSimilarTo3() throws Exception { + Expression ex = TestFunctionResolving.getExpression("'xay' similar to '{'"); + assertEquals(Boolean.FALSE, Evaluator.evaluate(ex)); + } + + @Test public void testSimilarTo4() throws Exception { + Expression ex = TestFunctionResolving.getExpression("'xay' similar to 'xa{2,}y'"); + assertEquals(Boolean.FALSE, Evaluator.evaluate(ex)); + } + + @Test public void testSimilarTo5() throws Exception { + Expression ex = TestFunctionResolving.getExpression("'x1y' similar to 'x([a-z]+|[0-9])_'"); + assertEquals(Boolean.TRUE, Evaluator.evaluate(ex)); + } + + @Test public void testSimilarTo6() throws Exception { + Expression ex = TestFunctionResolving.getExpression("'xx' similar to 'x([a-z]+|[0-9])_'"); + assertEquals(Boolean.FALSE, Evaluator.evaluate(ex)); + } + + @Test public void testLikeRegex() throws Exception { + Expression ex = TestFunctionResolving.getExpression("'aaaxy' like_regex 'a+.*'"); + assertEquals(Boolean.TRUE, Evaluator.evaluate(ex)); + } + + @Test public void testLikeRegex1() throws Exception { + Expression ex = TestFunctionResolving.getExpression("'xaay' similar to 'xa{2,3}y'"); + assertEquals(Boolean.TRUE, Evaluator.evaluate(ex)); + } + + @Test public void testLikeRegex2() throws Exception { + Expression ex = TestFunctionResolving.getExpression("'xay' like_regex 'xa{2,3}y'"); + assertEquals(Boolean.FALSE, Evaluator.evaluate(ex)); + } + + @Test(expected=ExpressionEvaluationException.class) public void testLikeRegex3() throws Exception { + Expression ex = TestFunctionResolving.getExpression("'xay' like_regex '{'"); + assertEquals(Boolean.FALSE, Evaluator.evaluate(ex)); + } + } Modified: trunk/engine/src/test/java/org/teiid/query/sql/visitor/TestSQLStringVisitor.java =================================================================== --- trunk/engine/src/test/java/org/teiid/query/sql/visitor/TestSQLStringVisitor.java 2011-08-04 17:51:11 UTC (rev 3363) +++ trunk/engine/src/test/java/org/teiid/query/sql/visitor/TestSQLStringVisitor.java 2011-08-05 18:33:39 UTC (rev 3364) @@ -22,13 +22,15 @@ package org.teiid.query.sql.visitor; +import static org.junit.Assert.*; + import java.math.BigDecimal; import java.util.ArrayList; import java.util.Arrays; import java.util.List; -import junit.framework.TestCase; - +import org.junit.Test; +import org.teiid.api.exception.query.QueryParserException; import org.teiid.client.metadata.ParameterInfo; import org.teiid.core.types.DataTypeManager; import org.teiid.language.SQLConstants.NonReserved; @@ -92,15 +94,9 @@ import org.teiid.query.sql.symbol.TestSearchedCaseExpression; import org.teiid.query.unittest.RealMetadataFactory; +@SuppressWarnings("nls") +public class TestSQLStringVisitor { -public class TestSQLStringVisitor extends TestCase { - - // ################################## FRAMEWORK ################################ - - public TestSQLStringVisitor(String name) { - super(name); - } - // ################################## TEST HELPERS ################################ private void helpTest(LanguageObject obj, String expectedStr) { @@ -110,13 +106,13 @@ // ################################## ACTUAL TESTS ################################ - public void testNull() { + @Test public void testNull() { String sql = SQLStringVisitor.getSQLString(null); assertEquals("Incorrect string for null object", SQLStringVisitor.UNDEFINED, sql); //$NON-NLS-1$ } - public void testBetweenCriteria1() { + @Test public void testBetweenCriteria1() { BetweenCriteria bc = new BetweenCriteria( new ElementSymbol("m.g.c1"), //$NON-NLS-1$ new Constant(new Integer(1000)), @@ -124,7 +120,7 @@ helpTest(bc, "m.g.c1 BETWEEN 1000 AND 2000"); //$NON-NLS-1$ } - public void testBetweenCriteria2() { + @Test public void testBetweenCriteria2() { BetweenCriteria bc = new BetweenCriteria( new ElementSymbol("m.g.c1"), //$NON-NLS-1$ new Constant(new Integer(1000)), @@ -133,7 +129,7 @@ helpTest(bc, "m.g.c1 NOT BETWEEN 1000 AND 2000"); //$NON-NLS-1$ } - public void testCompareCriteria1() { + @Test public void testCompareCriteria1() { CompareCriteria cc = new CompareCriteria( new ElementSymbol("m.g.c1"), //$NON-NLS-1$ CompareCriteria.EQ, @@ -142,7 +138,7 @@ helpTest(cc, "m.g.c1 = 'abc'"); //$NON-NLS-1$ } - public void testCompareCriteria2() { + @Test public void testCompareCriteria2() { CompareCriteria cc = new CompareCriteria( new ElementSymbol("m.g.c1"), //$NON-NLS-1$ CompareCriteria.NE, @@ -151,7 +147,7 @@ helpTest(cc, "m.g.c1 <> 'abc'"); //$NON-NLS-1$ } - public void testCompareCriteria3() { + @Test public void testCompareCriteria3() { CompareCriteria cc = new CompareCriteria( new ElementSymbol("m.g.c1"), //$NON-NLS-1$ CompareCriteria.GT, @@ -160,7 +156,7 @@ helpTest(cc, "m.g.c1 > 'abc'"); //$NON-NLS-1$ } - public void testCompareCriteria4() { + @Test public void testCompareCriteria4() { CompareCriteria cc = new CompareCriteria( new ElementSymbol("m.g.c1"), //$NON-NLS-1$ CompareCriteria.GE, @@ -169,7 +165,7 @@ helpTest(cc, "m.g.c1 >= 'abc'"); //$NON-NLS-1$ } - public void testCompareCriteria5() { + @Test public void testCompareCriteria5() { CompareCriteria cc = new CompareCriteria( new ElementSymbol("m.g.c1"), //$NON-NLS-1$ CompareCriteria.LT, @@ -178,7 +174,7 @@ helpTest(cc, "m.g.c1 < 'abc'"); //$NON-NLS-1$ } - public void testCompareCriteria6() { + @Test public void testCompareCriteria6() { CompareCriteria cc = new CompareCriteria( new ElementSymbol("m.g.c1"), //$NON-NLS-1$ CompareCriteria.LE, @@ -187,7 +183,7 @@ helpTest(cc, "m.g.c1 <= 'abc'"); //$NON-NLS-1$ } - public void testCompareCriteria7() { + @Test public void testCompareCriteria7() { CompareCriteria cc = new CompareCriteria( null, CompareCriteria.EQ, @@ -196,7 +192,7 @@ helpTest(cc, "<undefined> = <undefined>"); //$NON-NLS-1$ } - public void testCompoundCriteria1() { + @Test public void testCompoundCriteria1() { CompareCriteria cc = new CompareCriteria( new ElementSymbol("m.g.c1"), //$NON-NLS-1$ CompareCriteria.EQ, @@ -208,7 +204,7 @@ helpTest(comp, "m.g.c1 = 'abc'"); //$NON-NLS-1$ } - public void testCompoundCriteria2() { + @Test public void testCompoundCriteria2() { CompareCriteria cc1 = new CompareCriteria( new ElementSymbol("m.g.c1"), //$NON-NLS-1$ CompareCriteria.EQ, @@ -225,7 +221,7 @@ helpTest(comp, "(m.g.c1 = 'abc') AND (m.g.c2 = 'abc')"); //$NON-NLS-1$ } - public void testCompoundCriteria3() { + @Test public void testCompoundCriteria3() { CompareCriteria cc1 = new CompareCriteria( new ElementSymbol("m.g.c1"), //$NON-NLS-1$ CompareCriteria.EQ, @@ -247,7 +243,7 @@ helpTest(comp, "(m.g.c1 = 'abc') OR (m.g.c2 = 'abc') OR (m.g.c3 = 'abc')"); //$NON-NLS-1$ } - public void testCompoundCriteria4() { + @Test public void testCompoundCriteria4() { CompareCriteria cc1 = new CompareCriteria( new ElementSymbol("m.g.c1"), //$NON-NLS-1$ CompareCriteria.EQ, @@ -260,7 +256,7 @@ helpTest(comp, "(m.g.c1 = 'abc') OR (<undefined>)"); //$NON-NLS-1$ } - public void testCompoundCriteria5() { + @Test public void testCompoundCriteria5() { CompareCriteria cc1 = new CompareCriteria( new ElementSymbol("m.g.c1"), //$NON-NLS-1$ CompareCriteria.EQ, @@ -273,7 +269,7 @@ helpTest(comp, "(<undefined>) OR (m.g.c1 = 'abc')"); //$NON-NLS-1$ } - public void testCompoundCriteria6() { + @Test public void testCompoundCriteria6() { CompareCriteria cc1 = new CompareCriteria( new ElementSymbol("m.g.c1"), //$NON-NLS-1$ CompareCriteria.EQ, @@ -286,14 +282,14 @@ helpTest(comp, "(m.g.c1 = 'abc') OR (<undefined>)"); //$NON-NLS-1$ } - public void testDelete1() { + @Test public void testDelete1() { Delete delete = new Delete(); delete.setGroup(new GroupSymbol("m.g")); //$NON-NLS-1$ helpTest(delete, "DELETE FROM m.g"); //$NON-NLS-1$ } - public void testDelete2() { + @Test public void testDelete2() { Delete delete = new Delete(); delete.setGroup(new GroupSymbol("m.g")); //$NON-NLS-1$ delete.setCriteria(new CompareCriteria( @@ -305,7 +301,7 @@ helpTest(delete, "DELETE FROM m.g WHERE m.g.c1 = 'abc'"); //$NON-NLS-1$ } - public void testFrom1() { + @Test public void testFrom1() { From from = new From(); from.addGroup(new GroupSymbol("m.g1")); //$NON-NLS-1$ from.addGroup(new GroupSymbol("m.g2")); //$NON-NLS-1$ @@ -313,7 +309,7 @@ helpTest(from, "FROM m.g1, m.g2"); //$NON-NLS-1$ } - public void testFrom2() { + @Test public void testFrom2() { From from = new From(); from.addClause(new UnaryFromClause(new GroupSymbol("m.g1"))); //$NON-NLS-1$ from.addClause(new JoinPredicate( @@ -324,14 +320,14 @@ helpTest(from, "FROM m.g1, m.g2 CROSS JOIN m.g3"); //$NON-NLS-1$ } - public void testGroupBy1() { + @Test public void testGroupBy1() { GroupBy gb = new GroupBy(); gb.addSymbol(new ElementSymbol("m.g.e1")); //$NON-NLS-1$ helpTest(gb, "GROUP BY m.g.e1"); //$NON-NLS-1$ } - public void testGroupBy2() { + @Test public void testGroupBy2() { GroupBy gb = new GroupBy(); gb.addSymbol(new ElementSymbol("m.g.e1")); //$NON-NLS-1$ gb.addSymbol(new ElementSymbol("m.g.e2")); //$NON-NLS-1$ @@ -340,7 +336,7 @@ helpTest(gb, "GROUP BY m.g.e1, m.g.e2, m.g.e3"); //$NON-NLS-1$ } - public void testInsert1() { + @Test public void testInsert1() { Insert insert = new Insert(); insert.setGroup(new GroupSymbol("m.g1")); //$NON-NLS-1$ @@ -356,33 +352,33 @@ helpTest(insert, "INSERT INTO m.g1 (e1, e2) VALUES (5, 'abc')"); //$NON-NLS-1$ } - public void testIsNullCriteria1() { + @Test public void testIsNullCriteria1() { IsNullCriteria inc = new IsNullCriteria(); inc.setExpression(new Constant("abc")); //$NON-NLS-1$ helpTest(inc, "'abc' IS NULL"); //$NON-NLS-1$ } - public void testIsNullCriteria2() { + @Test public void testIsNullCriteria2() { IsNullCriteria inc = new IsNullCriteria(); inc.setExpression(new ElementSymbol("m.g.e1")); //$NON-NLS-1$ helpTest(inc, "m.g.e1 IS NULL"); //$NON-NLS-1$ } - public void testIsNullCriteria3() { + @Test public void testIsNullCriteria3() { IsNullCriteria inc = new IsNullCriteria(); helpTest(inc, "<undefined> IS NULL"); //$NON-NLS-1$ } - public void testIsNullCriteria4() { + @Test public void testIsNullCriteria4() { IsNullCriteria inc = new IsNullCriteria(); inc.setExpression(new ElementSymbol("m.g.e1")); //$NON-NLS-1$ inc.setNegated(true); helpTest(inc, "m.g.e1 IS NOT NULL"); //$NON-NLS-1$ } - public void testJoinPredicate1() { + @Test public void testJoinPredicate1() { JoinPredicate jp = new JoinPredicate( new UnaryFromClause(new GroupSymbol("m.g2")), //$NON-NLS-1$ new UnaryFromClause(new GroupSymbol("m.g3")), //$NON-NLS-1$ @@ -391,7 +387,7 @@ helpTest(jp, "m.g2 CROSS JOIN m.g3"); //$NON-NLS-1$ } - public void testOptionalJoinPredicate1() { + @Test public void testOptionalJoinPredicate1() { JoinPredicate jp = new JoinPredicate( new UnaryFromClause(new GroupSymbol("m.g2")), //$NON-NLS-1$ new UnaryFromClause(new GroupSymbol("m.g3")), //$NON-NLS-1$ @@ -400,7 +396,7 @@ helpTest(jp, "/*+ optional */ (m.g2 CROSS JOIN m.g3)"); //$NON-NLS-1$ } - public void testJoinPredicate2() { + @Test public void testJoinPredicate2() { ArrayList<Criteria> crits = new ArrayList<Criteria>(); crits.add(new CompareCriteria(new ElementSymbol("m.g2.e1"), CompareCriteria.EQ, new ElementSymbol("m.g3.e1"))); //$NON-NLS-1$ //$NON-NLS-2$ JoinPredicate jp = new JoinPredicate( @@ -412,7 +408,7 @@ helpTest(jp, "m.g2 INNER JOIN m.g3 ON m.g2.e1 = m.g3.e1"); //$NON-NLS-1$ } - public void testJoinPredicate3() { + @Test public void testJoinPredicate3() { ArrayList<Criteria> crits = new ArrayList<Criteria>(); crits.add(new CompareCriteria(new ElementSymbol("m.g2.e1"), CompareCriteria.EQ, new ElementSymbol("m.g3.e1"))); //$NON-NLS-1$ //$NON-NLS-2$ crits.add(new CompareCriteria(new ElementSymbol("m.g2.e2"), CompareCriteria.EQ, new ElementSymbol("m.g3.e2"))); //$NON-NLS-1$ //$NON-NLS-2$ @@ -425,7 +421,7 @@ helpTest(jp, "m.g2 INNER JOIN m.g3 ON m.g2.e1 = m.g3.e1 AND m.g2.e2 = m.g3.e2"); //$NON-NLS-1$ } - public void testJoinPredicate4() { + @Test public void testJoinPredicate4() { ArrayList<Criteria> crits = new ArrayList<Criteria>(); crits.add(new CompareCriteria(new ElementSymbol("m.g2.e1"), CompareCriteria.EQ, new ElementSymbol("m.g3.e1"))); //$NON-NLS-1$ //$NON-NLS-2$ JoinPredicate jp = new JoinPredicate( @@ -442,7 +438,7 @@ helpTest(jp2, "(m.g2 INNER JOIN m.g3 ON m.g2.e1 = m.g3.e1) CROSS JOIN m.g1"); //$NON-NLS-1$ } - public void testJoinPredicate5() { + @Test public void testJoinPredicate5() { ArrayList<Criteria> crits = new ArrayList<Criteria>(); crits.add(new NotCriteria(new CompareCriteria(new ElementSymbol("m.g2.e1"), CompareCriteria.EQ, new ElementSymbol("m.g3.e1")))); //$NON-NLS-1$ //$NON-NLS-2$ JoinPredicate jp = new JoinPredicate( @@ -454,27 +450,27 @@ helpTest(jp, "m.g2 INNER JOIN m.g3 ON NOT (m.g2.e1 = m.g3.e1)"); //$NON-NLS-1$ } - public void testJoinType1() { + @Test public void testJoinType1() { helpTest(JoinType.JOIN_CROSS, "CROSS JOIN"); //$NON-NLS-1$ } - public void testJoinType2() { + @Test public void testJoinType2() { helpTest(JoinType.JOIN_INNER, "INNER JOIN"); //$NON-NLS-1$ } - public void testJoinType3() { + @Test public void testJoinType3() { helpTest(JoinType.JOIN_RIGHT_OUTER, "RIGHT OUTER JOIN"); //$NON-NLS-1$ } - public void testJoinType4() { + @Test public void testJoinType4() { helpTest(JoinType.JOIN_LEFT_OUTER, "LEFT OUTER JOIN"); //$NON-NLS-1$ } - public void testJoinType5() { + @Test public void testJoinType5() { helpTest(JoinType.JOIN_FULL_OUTER, "FULL OUTER JOIN"); //$NON-NLS-1$ } - public void testMatchCriteria1() { + @Test public void testMatchCriteria1() { MatchCriteria mc = new MatchCriteria(); mc.setLeftExpression(new ElementSymbol("m.g.e1")); //$NON-NLS-1$ mc.setRightExpression(new Constant("abc")); //$NON-NLS-1$ @@ -482,7 +478,7 @@ helpTest(mc, "m.g.e1 LIKE 'abc'"); //$NON-NLS-1$ } - public void testMatchCriteria2() { + @Test public void testMatchCriteria2() { MatchCriteria mc = new MatchCriteria(); mc.setLeftExpression(new ElementSymbol("m.g.e1")); //$NON-NLS-1$ mc.setRightExpression(new Constant("%")); //$NON-NLS-1$ @@ -491,7 +487,7 @@ helpTest(mc, "m.g.e1 LIKE '%' ESCAPE '#'"); //$NON-NLS-1$ } - public void testMatchCriteria3() { + @Test public void testMatchCriteria3() { MatchCriteria mc = new MatchCriteria(); mc.setLeftExpression(new ElementSymbol("m.g.e1")); //$NON-NLS-1$ mc.setRightExpression(new Constant("abc")); //$NON-NLS-1$ @@ -499,22 +495,22 @@ helpTest(mc, "m.g.e1 NOT LIKE 'abc'"); //$NON-NLS-1$ } - public void testNotCriteria1() { + @Test public void testNotCriteria1() { NotCriteria not = new NotCriteria(new IsNullCriteria(new ElementSymbol("m.g.e1"))); //$NON-NLS-1$ helpTest(not, "NOT (m.g.e1 IS NULL)"); //$NON-NLS-1$ } - public void testNotCriteria2() { + @Test public void testNotCriteria2() { NotCriteria not = new NotCriteria(); helpTest(not, "NOT (<undefined>)"); //$NON-NLS-1$ } - public void testOption1() { + @Test public void testOption1() { Option option = new Option(); helpTest(option, "OPTION"); //$NON-NLS-1$ } - public void testOption5() { + @Test public void testOption5() { Option option = new Option(); option.addDependentGroup("abc"); //$NON-NLS-1$ option.addDependentGroup("def"); //$NON-NLS-1$ @@ -522,7 +518,7 @@ helpTest(option, "OPTION MAKEDEP abc, def, xyz"); //$NON-NLS-1$ } - public void testOption6() { + @Test public void testOption6() { Option option = new Option(); option.addDependentGroup("abc"); //$NON-NLS-1$ option.addDependentGroup("def"); //$NON-NLS-1$ @@ -530,7 +526,7 @@ helpTest(option, "OPTION MAKEDEP abc, def, xyz"); //$NON-NLS-1$ } - public void testOption8() { + @Test public void testOption8() { Option option = new Option(); option.addNoCacheGroup("abc"); //$NON-NLS-1$ option.addNoCacheGroup("def"); //$NON-NLS-1$ @@ -539,20 +535,20 @@ } // related to defect 14423 - public void testOption9() { + @Test public void testOption9() { Option option = new Option(); option.setNoCache(true); helpTest(option, "OPTION NOCACHE"); //$NON-NLS-1$ } - public void testOrderBy1() { + @Test public void testOrderBy1() { OrderBy ob = new OrderBy(); ob.addVariable(new ElementSymbol("e1")); //$NON-NLS-1$ helpTest(ob, "ORDER BY e1"); //$NON-NLS-1$ } - public void testOrderBy2() { + @Test public void testOrderBy2() { OrderBy ob = new OrderBy(); ob.addVariable(new ElementSymbol("e1")); //$NON-NLS-1$ ob.addVariable(new AliasSymbol("x", new ElementSymbol("e2"))); //$NON-NLS-1$ //$NON-NLS-2$ @@ -560,7 +556,7 @@ helpTest(ob, "ORDER BY e1, x"); //$NON-NLS-1$ } - public void testOrderBy3() { + @Test public void testOrderBy3() { OrderBy ob = new OrderBy(); ob.addVariable(new ElementSymbol("e1"), OrderBy.DESC); //$NON-NLS-1$ ob.addVariable(new ElementSymbol("x"), OrderBy.DESC); //$NON-NLS-1$ @@ -568,7 +564,7 @@ helpTest(ob, "ORDER BY e1 DESC, x DESC"); //$NON-NLS-1$ } - public void testQuery1() { + @Test public void testQuery1() { Select select = new Select(); select.addSymbol(new MultipleElementSymbol()); From from = new From(); @@ -580,7 +576,7 @@ helpTest(query, "SELECT * FROM m.g"); //$NON-NLS-1$ } - public void testQuery2() { + @Test public void testQuery2() { Select select = new Select(); select.addSymbol(new MultipleElementSymbol()); From from = new From(); @@ -603,7 +599,7 @@ helpTest(query, "SELECT * FROM m.g WHERE e1 = 5 GROUP BY e1 HAVING e1 > 0 ORDER BY e1"); //$NON-NLS-1$ } - public void testQuery3() { + @Test public void testQuery3() { Select select = new Select(); select.addSymbol(new MultipleElementSymbol()); From from = new From(); @@ -624,7 +620,7 @@ helpTest(query, "SELECT * FROM m.g GROUP BY e1 HAVING e1 > 0 ORDER BY e1"); //$NON-NLS-1$ } - public void testQuery4() { + @Test public void testQuery4() { Select select = new Select(); select.addSymbol(new MultipleElementSymbol()); From from = new From(); @@ -644,7 +640,7 @@ helpTest(query, "SELECT * FROM m.g WHERE e1 = 5 HAVING e1 > 0 ORDER BY e1"); //$NON-NLS-1$ } - public void testQuery5() { + @Test public void testQuery5() { Select select = new Select(); select.addSymbol(new MultipleElementSymbol()); From from = new From(); @@ -665,7 +661,7 @@ helpTest(query, "SELECT * FROM m.g WHERE e1 = 5 GROUP BY e1 ORDER BY e1"); //$NON-NLS-1$ } - public void testQuery6() { + @Test public void testQuery6() { Select select = new Select(); select.addSymbol(new MultipleElementSymbol()); From from = new From(); @@ -685,7 +681,7 @@ helpTest(query, "SELECT * FROM m.g WHERE e1 = 5 GROUP BY e1 HAVING e1 > 0"); //$NON-NLS-1$ } - public void testQuery7() { + @Test public void testQuery7() { Select select = new Select(); select.addSymbol(new MultipleElementSymbol()); From from = new From(); @@ -708,14 +704,14 @@ helpTest(query, "SELECT * FROM m.g WHERE e1 = 5 GROUP BY e1 HAVING e1 > 0 ORDER BY e1"); //$NON-NLS-1$ } - public void testSelect1() { + @Test public void testSelect1() { Select select = new Select(); select.addSymbol(new ElementSymbol("e1")); //$NON-NLS-1$ helpTest(select, "SELECT e1"); //$NON-NLS-1$ } - public void testSelect2() { + @Test public void testSelect2() { Select select = new Select(); select.setDistinct(true); select.addSymbol(new ElementSymbol("e1")); //$NON-NLS-1$ @@ -723,7 +719,7 @@ helpTest(select, "SELECT DISTINCT e1"); //$NON-NLS-1$ } - public void testSelect3() { + @Test public void testSelect3() { Select select = new Select(); select.addSymbol(new ElementSymbol("e1")); //$NON-NLS-1$ select.addSymbol(new ElementSymbol("e2")); //$NON-NLS-1$ @@ -731,7 +727,7 @@ helpTest(select, "SELECT e1, e2"); //$NON-NLS-1$ } - public void testSetCriteria1() { + @Test public void testSetCriteria1() { SetCriteria sc = new SetCriteria(); sc.setExpression(new ElementSymbol("e1")); //$NON-NLS-1$ sc.setValues(new ArrayList()); @@ -739,7 +735,7 @@ helpTest(sc, "e1 IN ()"); //$NON-NLS-1$ } - public void testSetCriteria2() { + @Test public void testSetCriteria2() { SetCriteria sc = new SetCriteria(); sc.setExpression(new ElementSymbol("e1")); //$NON-NLS-1$ ArrayList values = new ArrayList(); @@ -750,7 +746,7 @@ helpTest(sc, "e1 IN (e2, 'abc')"); //$NON-NLS-1$ } - public void testSetCriteria3() { + @Test public void testSetCriteria3() { SetCriteria sc = new SetCriteria(); sc.setExpression(new ElementSymbol("e1")); //$NON-NLS-1$ ArrayList values = new ArrayList(); @@ -761,7 +757,7 @@ helpTest(sc, "e1 IN (<undefined>, 'b')"); //$NON-NLS-1$ } - public void testSetCriteria4() { + @Test public void testSetCriteria4() { SetCriteria sc = new SetCriteria(); sc.setExpression(new ElementSymbol("e1")); //$NON-NLS-1$ ArrayList values = new ArrayList(); @@ -772,7 +768,7 @@ helpTest(sc, "e1 NOT IN (e2, 'abc')"); //$NON-NLS-1$ } - public void testSetQuery1() { + @Test public void testSetQuery1() { Select s1 = new Select(); s1.addSymbol(new ElementSymbol("e1")); //$NON-NLS-1$ From f1 = new From(); @@ -794,7 +790,7 @@ helpTest(sq, "SELECT e1 FROM m.g1 UNION SELECT e1 FROM m.g2"); //$NON-NLS-1$ } - public void testSetQuery2() { + @Test public void testSetQuery2() { Select s1 = new Select(); s1.addSymbol(new ElementSymbol("e1")); //$NON-NLS-1$ From f1 = new From(); @@ -816,7 +812,7 @@ helpTest(sq, "SELECT e1 FROM m.g1 UNION ALL SELECT e1 FROM m.g2"); //$NON-NLS-1$ } - public void testSetQuery3() { + @Test public void testSetQuery3() { Select s1 = new Select(); s1.addSymbol(new ElementSymbol("e1")); //$NON-NLS-1$ From f1 = new From(); @@ -842,7 +838,7 @@ helpTest(sq, "SELECT e1 FROM m.g1 UNION SELECT e1 FROM m.g2 ORDER BY e1"); //$NON-NLS-1$ } - public void testSetQuery4() { + @Test public void testSetQuery4() { Select s1 = new Select(); s1.addSymbol(new ElementSymbol("e1")); //$NON-NLS-1$ From f1 = new From(); @@ -864,7 +860,7 @@ helpTest(sq, "SELECT e1 FROM m.g1 UNION SELECT e1 FROM m.g2"); //$NON-NLS-1$ } - public void testSetQuery5() { + @Test public void testSetQuery5() { Select s1 = new Select(); s1.addSymbol(new ElementSymbol("e1")); //$NON-NLS-1$ From f1 = new From(); @@ -896,7 +892,7 @@ helpTest(sq2, "SELECT e3 FROM m.g3 UNION ALL (SELECT e1 FROM m.g1 UNION SELECT e1 FROM m.g2)"); //$NON-NLS-1$ } - public void testSubqueryFromClause1() { + @Test public void testSubqueryFromClause1() { Select s1 = new Select(); s1.addSymbol(new ElementSymbol("e1")); //$NON-NLS-1$ From f1 = new From(); @@ -909,7 +905,7 @@ helpTest(sfc, "(SELECT e1 FROM m.g1) AS temp"); //$NON-NLS-1$ } - public void testOptionalSubqueryFromClause1() { + @Test public void testOptionalSubqueryFromClause1() { Select s1 = new Select(); s1.addSymbol(new ElementSymbol("e1")); //$NON-NLS-1$ From f1 = new From(); @@ -923,7 +919,7 @@ helpTest(sfc, "/*+ optional */ (SELECT e1 FROM m.g1) AS temp"); //$NON-NLS-1$ } - public void testSubquerySetCriteria1() { + @Test public void testSubquerySetCriteria1() { Select s1 = new Select(); s1.addSymbol(new ElementSymbol("e1")); //$NON-NLS-1$ From f1 = new From(); @@ -938,7 +934,7 @@ helpTest(ssc, "e2 IN (SELECT e1 FROM m.g1)"); //$NON-NLS-1$ } - public void testSubquerySetCriteria2() { + @Test public void testSubquerySetCriteria2() { Select s1 = new Select(); s1.addSymbol(new ElementSymbol("e1")); //$NON-NLS-1$ From f1 = new From(); @@ -954,17 +950,17 @@ helpTest(ssc, "e2 NOT IN (SELECT e1 FROM m.g1)"); //$NON-NLS-1$ } - public void testUnaryFromClause() { + @Test public void testUnaryFromClause() { helpTest(new UnaryFromClause(new GroupSymbol("m.g1")), "m.g1"); //$NON-NLS-1$ //$NON-NLS-2$ } - public void testOptionalUnaryFromClause() { + @Test public void testOptionalUnaryFromClause() { UnaryFromClause unaryFromClause = new UnaryFromClause(new GroupSymbol("m.g1"));//$NON-NLS-1$ unaryFromClause.setOptional(true); helpTest(unaryFromClause, "/*+ optional */ m.g1"); //$NON-NLS-1$ } - public void testUpdate1() { + @Test public void testUpdate1() { Update update = new Update(); update.setGroup(new GroupSymbol("m.g1")); //$NON-NLS-1$ update.addChange(new ElementSymbol("e1"), new Constant("abc")); //$NON-NLS-1$ //$NON-NLS-2$ @@ -972,7 +968,7 @@ helpTest(update, "UPDATE m.g1 SET e1 = 'abc'"); //$NON-NLS-1$ } - public void testUpdate2() { + @Test public void testUpdate2() { Update update = new Update(); update.setGroup(new GroupSymbol("m.g1")); //$NON-NLS-1$ update.addChange(new ElementSymbol("e1"), new Constant("abc")); //$NON-NLS-1$ //$NON-NLS-2$ @@ -981,7 +977,7 @@ helpTest(update, "UPDATE m.g1 SET e1 = 'abc', e2 = 'xyz'"); //$NON-NLS-1$ } - public void testUpdate3() { + @Test public void testUpdate3() { Update update = new Update(); update.setGroup(new GroupSymbol("m.g1")); //$NON-NLS-1$ update.addChange(new ElementSymbol("e1"), new Constant("abc")); //$NON-NLS-1$ //$NON-NLS-2$ @@ -994,163 +990,163 @@ helpTest(update, "UPDATE m.g1 SET e1 = 'abc' WHERE e2 = 'abc'"); //$NON-NLS-1$ } - public void testAggregateSymbol1() { + @Test public void testAggregateSymbol1() { AggregateSymbol agg = new AggregateSymbol("abc", NonReserved.COUNT, false, new Constant("abc")); //$NON-NLS-1$ //$NON-NLS-2$ helpTest(agg, "COUNT('abc')"); //$NON-NLS-1$ } - public void testAggregateSymbol2() { + @Test public void testAggregateSymbol2() { AggregateSymbol agg = new AggregateSymbol("abc", NonReserved.COUNT, true, new Constant("abc")); //$NON-NLS-1$ //$NON-NLS-2$ helpTest(agg, "COUNT(DISTINCT 'abc')"); //$NON-NLS-1$ } - public void testAggregateSymbol3() { + @Test public void testAggregateSymbol3() { AggregateSymbol agg = new AggregateSymbol("abc", NonReserved.COUNT, false, null); //$NON-NLS-1$ helpTest(agg, "COUNT(*)"); //$NON-NLS-1$ } - public void testAggregateSymbol4() { + @Test public void testAggregateSymbol4() { AggregateSymbol agg = new AggregateSymbol("abc", NonReserved.AVG, false, new Constant("abc")); //$NON-NLS-1$ //$NON-NLS-2$ helpTest(agg, "AVG('abc')"); //$NON-NLS-1$ } - public void testAggregateSymbol5() { + @Test public void testAggregateSymbol5() { AggregateSymbol agg = new AggregateSymbol("abc", NonReserved.SUM, false, new Constant("abc")); //$NON-NLS-1$ //$NON-NLS-2$ helpTest(agg, "SUM('abc')"); //$NON-NLS-1$ } - public void testAggregateSymbol6() { + @Test public void testAggregateSymbol6() { AggregateSymbol agg = new AggregateSymbol("abc", NonReserved.MIN, false, new Constant("abc")); //$NON-NLS-1$ //$NON-NLS-2$ helpTest(agg, "MIN('abc')"); //$NON-NLS-1$ } - public void testAggregateSymbol7() { + @Test public void testAggregateSymbol7() { AggregateSymbol agg = new AggregateSymbol("abc", NonReserved.MAX, false, new Constant("abc")); //$NON-NLS-1$ //$NON-NLS-2$ helpTest(agg, "MAX('abc')"); //$NON-NLS-1$ } - public void testAliasSymbol1() { + @Test public void testAliasSymbol1() { AliasSymbol as = new AliasSymbol("x", new ElementSymbol("y")); //$NON-NLS-1$ //$NON-NLS-2$ helpTest(as, "y AS x"); //$NON-NLS-1$ } // Test alias symbol with reserved word - public void testAliasSymbol2() { + @Test public void testAliasSymbol2() { AliasSymbol as = new AliasSymbol("select", new ElementSymbol("y")); //$NON-NLS-1$ //$NON-NLS-2$ helpTest(as, "y AS \"select\""); //$NON-NLS-1$ } - public void testAllSymbol() { + @Test public void testAllSymbol() { helpTest(new MultipleElementSymbol(), "*"); //$NON-NLS-1$ } - public void testAllInGroupSymbol() { + @Test public void testAllInGroupSymbol() { helpTest(new MultipleElementSymbol("m.g"), "m.g.*"); //$NON-NLS-1$ //$NON-NLS-2$ } - public void testConstantNull() { + @Test public void testConstantNull() { helpTest(new Constant(null), "null"); //$NON-NLS-1$ } - public void testConstantString() { + @Test public void testConstantString() { helpTest(new Constant("abc"), "'abc'"); //$NON-NLS-1$ //$NON-NLS-2$ } - public void testConstantInteger() { + @Test public void testConstantInteger() { helpTest(new Constant(new Integer(5)), "5"); //$NON-NLS-1$ } - public void testConstantBigDecimal() { + @Test public void testConstantBigDecimal() { helpTest(new Constant(new BigDecimal("5.4")), "5.4"); //$NON-NLS-1$ //$NON-NLS-2$ } - public void testConstantStringWithTick() { + @Test public void testConstantStringWithTick() { helpTest(new Constant("O'Leary"), "'O''Leary'"); //$NON-NLS-1$ //$NON-NLS-2$ } - public void testConstantStringWithTicks() { + @Test public void testConstantStringWithTicks() { helpTest(new Constant("'abc'"), "'''abc'''"); //$NON-NLS-1$ //$NON-NLS-2$ } - public void testConstantStringWithMoreTicks() { + @Test public void testConstantStringWithMoreTicks() { helpTest(new Constant("a'b'c"), "'a''b''c'"); //$NON-NLS-1$ //$NON-NLS-2$ } - public void testConstantStringWithDoubleTick() { + @Test public void testConstantStringWithDoubleTick() { helpTest(new Constant("group=\"x\""), "'group=\"x\"'"); //$NON-NLS-1$ //$NON-NLS-2$ } - public void testConstantBooleanTrue() { + @Test public void testConstantBooleanTrue() { helpTest(new Constant(Boolean.TRUE), "TRUE"); //$NON-NLS-1$ } - public void testConstantBooleanFalse() { + @Test public void testConstantBooleanFalse() { helpTest(new Constant(Boolean.FALSE), "FALSE"); //$NON-NLS-1$ } - public void testConstantDate() { + @Test public void testConstantDate() { helpTest(new Constant(java.sql.Date.valueOf("2002-10-02")), "{d'2002-10-02'}"); //$NON-NLS-1$ //$NON-NLS-2$ } - public void testConstantTime() { + @Test public void testConstantTime() { helpTest(new Constant(java.sql.Time.valueOf("5:00:00")), "{t'05:00:00'}"); //$NON-NLS-1$ //$NON-NLS-2$ } - public void testConstantTimestamp() { + @Test public void testConstantTimestamp() { helpTest(new Constant(java.sql.Timestamp.valueOf("2002-10-02 17:10:35.0234")), "{ts'2002-10-02 17:10:35.0234'}"); //$NON-NLS-1$ //$NON-NLS-2$ } - public void testElementSymbol1() { + @Test public void testElementSymbol1() { ElementSymbol es = new ElementSymbol("elem"); //$NON-NLS-1$ helpTest(es, "elem"); //$NON-NLS-1$ } - public void testElementSymbol2() { + @Test public void testElementSymbol2() { ElementSymbol es = new ElementSymbol("elem", false); //$NON-NLS-1$ es.setGroupSymbol(new GroupSymbol("m.g")); //$NON-NLS-1$ helpTest(es, "elem"); //$NON-NLS-1$ } - public void testElementSymbol3() { + @Test public void testElementSymbol3() { ElementSymbol es = new ElementSymbol("m.g.elem", true); //$NON-NLS-1$ es.setGroupSymbol(new GroupSymbol("m.g")); //$NON-NLS-1$ helpTest(es, "m.g.elem"); //$NON-NLS-1$ } - public void testElementSymbol4() { + @Test public void testElementSymbol4() { ElementSymbol es = new ElementSymbol("vdb.m.g.elem", true); //$NON-NLS-1$ helpTest(es, "vdb.m.g.elem"); //$NON-NLS-1$ } - public void testElementSymbol5() { + @Test public void testElementSymbol5() { ElementSymbol es = new ElementSymbol("m.g.select", false); //$NON-NLS-1$ es.setGroupSymbol(new GroupSymbol("m.g")); //$NON-NLS-1$ helpTest(es, "\"select\""); //$NON-NLS-1$ } - public void testExpressionSymbol1() { + @Test public void testExpressionSymbol1() { ExpressionSymbol expr = new ExpressionSymbol("abc", new Constant("abc")); //$NON-NLS-1$ //$NON-NLS-2$ helpTest(expr, "'abc'"); //$NON-NLS-1$ } - public void testFunction1() { + @Test public void testFunction1() { Function func = new Function("concat", new Expression[] { //$NON-NLS-1$ new Constant("a"), null //$NON-NLS-1$ }); helpTest(func, "concat('a', <undefined>)"); //$NON-NLS-1$ } - public void testFunction2() { + @Test public void testFunction2() { Function func = new Function("now", new Expression[] {}); //$NON-NLS-1$ helpTest(func, "now()"); //$NON-NLS-1$ } - public void testFunction3() { + @Test public void testFunction3() { Function func = new Function("concat", new Expression[] {null, null}); //$NON-NLS-1$ helpTest(func, "concat(<undefined>, <undefined>)"); //$NON-NLS-1$ } - public void testFunction4() { + @Test public void testFunction4() { Function func1 = new Function("power", new Expression[] { //$NON-NLS-1$ new Constant(new Integer(5)), new Constant(new Integer(3)) }); @@ -1163,7 +1159,7 @@ helpTest(func3, "(1000 + power(power(5, 3), 3))"); //$NON-NLS-1$ } - public void testFunction5() { + @Test public void testFunction5() { Function func1 = new Function("concat", new Expression[] { //$NON-NLS-1$ new ElementSymbol("elem2"), //$NON-NLS-1$ null }); @@ -1173,7 +1169,7 @@ helpTest(func2, "concat(elem1, concat(elem2, <undefined>))"); //$NON-NLS-1$ } - public void testConvertFunction1() { + @Test public void testConvertFunction1() { Function func = new Function("convert", new Expression[] { //$NON-NLS-1$ new Constant("5"), //$NON-NLS-1$ new Constant("integer") //$NON-NLS-1$ @@ -1181,7 +1177,7 @@ helpTest(func, "convert('5', integer)"); //$NON-NLS-1$ } - public void testConvertFunction2() { + @Test public void testConvertFunction2() { Function func = new Function("convert", new Expression[] { //$NON-NLS-1$ null, new Constant("integer") //$NON-NLS-1$ @@ -1189,7 +1185,7 @@ helpTest(func, "convert(<undefined>, integer)"); //$NON-NLS-1$ } - public void testConvertFunction3() { + @Test public void testConvertFunction3() { Function func = new Function("convert", new Expression[] { //$NON-NLS-1$ new Constant(null), new Constant("integer") //$NON-NLS-1$ @@ -1197,7 +1193,7 @@ helpTest(func, "convert(null, integer)"); //$NON-NLS-1$ } - public void testConvertFunction4() { + @Test public void testConvertFunction4() { Function func = new Function("convert", new Expression[] { //$NON-NLS-1$ new Constant("abc"), //$NON-NLS-1$ null @@ -1205,22 +1201,22 @@ helpTest(func, "convert('abc', <undefined>)"); //$NON-NLS-1$ } - public void testConvertFunction5() { + @Test public void testConvertFunction5() { Function func = new Function("convert", null); //$NON-NLS-1$ helpTest(func, "convert()"); //$NON-NLS-1$ } - public void testConvertFunction6() { + @Test public void testConvertFunction6() { Function func = new Function("convert", new Expression[0]); //$NON-NLS-1$ helpTest(func, "convert()"); //$NON-NLS-1$ } - public void testConvertFunction7() { + @Test public void testConvertFunction7() { Function func = new Function("convert", new Expression[] {new Constant("abc")}); //$NON-NLS-1$ //$NON-NLS-2$ helpTest(func, "convert('abc', <undefined>)"); //$NON-NLS-1$ } - public void testCastFunction1() { + @Test public void testCastFunction1() { Function func = new Function("cast", new Expression[] { //$NON-NLS-1$ new Constant("5"), //$NON-NLS-1$ new Constant("integer") //$NON-NLS-1$ @@ -1228,7 +1224,7 @@ helpTest(func, "cast('5' AS integer)"); //$NON-NLS-1$ } - public void testCastFunction2() { + @Test public void testCastFunction2() { Function func = new Function("cast", new Expression[] { //$NON-NLS-1$ null, new Constant("integer") //$NON-NLS-1$ @@ -1236,7 +1232,7 @@ helpTest(func, "cast(<undefined> AS integer)"); //$NON-NLS-1$ } - public void testCastFunction3() { + @Test public void testCastFunction3() { Function func = new Function("cast", new Expression[] { //$NON-NLS-1$ new Constant(null), new Constant("integer") //$NON-NLS-1$ @@ -1244,7 +1240,7 @@ helpTest(func, "cast(null AS integer)"); //$NON-NLS-1$ } - public void testCastFunction4() { + @Test public void testCastFunction4() { Function func = new Function("cast", new Expression[] { //$NON-NLS-1$ new Constant("abc"), //$NON-NLS-1$ null @@ -1252,50 +1248,50 @@ helpTest(func, "cast('abc' AS <undefined>)"); //$NON-NLS-1$ } - public void testArithemeticFunction1() { + @Test public void testArithemeticFunction1() { Function func = new Function("-", new Expression[] { //$NON-NLS-1$ new Constant(new Integer(-2)), new Constant(new Integer(-1))}); helpTest(func, "(-2 - -1)"); //$NON-NLS-1$ } - public void testGroupSymbol1() { + @Test public void testGroupSymbol1() { GroupSymbol gs = new GroupSymbol("g"); //$NON-NLS-1$ helpTest(gs, "g"); //$NON-NLS-1$ } - public void testGroupSymbol2() { + @Test public void testGroupSymbol2() { GroupSymbol gs = new GroupSymbol("x", "g"); //$NON-NLS-1$ //$NON-NLS-2$ helpTest(gs, "g AS x"); //$NON-NLS-1$ } - public void testGroupSymbol3() { + @Test public void testGroupSymbol3() { GroupSymbol gs = new GroupSymbol("vdb.g"); //$NON-NLS-1$ helpTest(gs, "vdb.g"); //$NON-NLS-1$ } - public void testGroupSymbol4() { + @Test public void testGroupSymbol4() { GroupSymbol gs = new GroupSymbol("x", "vdb.g"); //$NON-NLS-1$ //$NON-NLS-2$ helpTest(gs, "vdb.g AS x"); //$NON-NLS-1$ } - public void testGroupSymbol5() { + @Test public void testGroupSymbol5() { GroupSymbol gs = new GroupSymbol("from", "m.g"); //$NON-NLS-1$ //$NON-NLS-2$ helpTest(gs, "m.g AS \"from\""); //$NON-NLS-1$ } - public void testGroupSymbol6() { + @Test public void testGroupSymbol6() { GroupSymbol gs = new GroupSymbol("x", "on.select"); //$NON-NLS-1$ //$NON-NLS-2$ helpTest(gs, "\"on\".\"select\" AS x"); //$NON-NLS-1$ } - public void testExecNoParams() { + @Test public void testExecNoParams() { StoredProcedure proc = new StoredProcedure(); proc.setProcedureName("myproc"); //$NON-NLS-1$ helpTest(proc, "EXEC myproc()"); //$NON-NLS-1$ } - public void testExecInputParam() { + @Test public void testExecInputParam() { StoredProcedure proc = new StoredProcedure(); proc.setProcedureName("myproc"); //$NON-NLS-1$ SPParameter param = new SPParameter(1, new Reference(0)); @@ -1303,7 +1299,7 @@ helpTest(proc, "EXEC myproc(?)"); //$NON-NLS-1$ } - public void testExecInputOutputParam() { + @Test public void testExecInputOutputParam() { StoredProcedure proc = new StoredProcedure(); proc.setProcedureName("myproc"); //$NON-NLS-1$ SPParameter param1 = new SPParameter(1, new Constant(new Integer(5))); @@ -1316,7 +1312,7 @@ helpTest(proc, "EXEC myproc(5)"); //$NON-NLS-1$ } - public void testExecOutputInputParam() { + @Test public void testExecOutputInputParam() { StoredProcedure proc = new StoredProcedure(); proc.setProcedureName("myproc"); //$NON-NLS-1$ @@ -1330,7 +1326,7 @@ helpTest(proc, "EXEC myproc(5)"); //$NON-NLS-1$ } - public void testExecReturnParam() { + @Test public void testExecReturnParam() { StoredProcedure proc = new StoredProcedure(); proc.setProcedureName("myproc"); //$NON-NLS-1$ @@ -1339,7 +1335,7 @@ helpTest(proc, "EXEC myproc()"); //$NON-NLS-1$ } - public void testExecNamedParam() { + @Test public void testExecNamedParam() { StoredProcedure proc = new StoredProcedure(); proc.setDisplayNamedParameters(true); proc.setProcedureName("myproc"); //$NON-NLS-1$ @@ -1349,7 +1345,7 @@ helpTest(proc, "EXEC myproc(p1 => ?)"); //$NON-NLS-1$ } - public void testExecNamedParams() { + @Test public void testExecNamedParams() { StoredProcedure proc = new StoredProcedure(); proc.setDisplayNamedParameters(true); proc.setProcedureName("myproc"); //$NON-NLS-1$ @@ -1369,7 +1365,7 @@ * * @since 4.3 */ - public void testExecNamedParamsReservedWord() { + @Test public void testExecNamedParamsReservedWord() { StoredProcedure proc = new StoredProcedure(); proc.setDisplayNamedParameters(true); proc.setProcedureName("myproc"); //$NON-NLS-1$ @@ -1384,27 +1380,27 @@ // Test methods for Update Procedure Language Objects - public void testDeclareStatement() { + @Test public void testDeclareStatement() { DeclareStatement dclStmt = new DeclareStatement(new ElementSymbol("a"), "String"); //$NON-NLS-1$ //$NON-NLS-2$ helpTest(dclStmt, "DECLARE String a;"); //$NON-NLS-1$ } - public void testRaiseErrorStatement() { + @Test public void testRaiseErrorStatement() { RaiseErrorStatement errStmt = new RaiseErrorStatement(new Constant("My Error")); //$NON-NLS-1$ helpTest(errStmt, "ERROR 'My Error';"); //$NON-NLS-1$ } - public void testRaiseErrorStatementWithExpression() { + @Test public void testRaiseErrorStatementWithExpression() { RaiseErrorStatement errStmt = new RaiseErrorStatement(new ElementSymbol("a")); //$NON-NLS-1$ helpTest(errStmt, "ERROR a;"); //$NON-NLS-1$ } - public void testAssignmentStatement1() { + @Test public void testAssignmentStatement1() { AssignmentStatement assigStmt = new AssignmentStatement(new ElementSymbol("a"), new Constant(new Integer(1))); //$NON-NLS-1$ helpTest(assigStmt, "a = 1;"); //$NON-NLS-1$ } - public void testAssignmentStatement2() { + @Test public void testAssignmentStatement2() { Query q1 = new Query(); Select select = new Select(); select.addSymbol(new ElementSymbol("x")); //$NON-NLS-1$ @@ -1417,7 +1413,7 @@ helpTest(assigStmt, "a = (SELECT x FROM g);"); //$NON-NLS-1$ } - public void testCriteriaSelector1() { + @Test public void testCriteriaSelector1() { ElementSymbol sy1 = new ElementSymbol("a"); //$NON-NLS-1$ ElementSymbol sy2 = new ElementSymbol("b"); //$NON-NLS-1$ ElementSymbol sy3 = new ElementSymbol("c"); //$NON-NLS-1$ @@ -1429,7 +1425,7 @@ helpTest(cs, "= CRITERIA ON (a, b, c)"); //$NON-NLS-1$ } - public void testCriteriaSelector2() { + @Test public void testCriteriaSelector2() { ElementSymbol sy1 = new ElementSymbol("x"); //$NON-NLS-1$ ElementSymbol sy2 = new ElementSymbol("y"); //$NON-NLS-1$ ElementSymbol sy3 = new ElementSymbol("z"); //$NON-NLS-1$ @@ -1441,7 +1437,7 @@ helpTest(cs, "LIKE CRITERIA ON (x, y, z)"); //$NON-NLS-1$ } - public void testCriteriaSelector3() { + @Test public void testCriteriaSelector3() { ElementSymbol sy1 = new ElementSymbol("x"); //$NON-NLS-1$ ElementSymbol sy2 = new ElementSymbol("y"); //$NON-NLS-1$ ElementSymbol sy3 = new ElementSymbol("z"); //$NON-NLS-1$ @@ -1453,7 +1449,7 @@ helpTest(cs, "BETWEEN CRITERIA ON (x, y, z)"); //$NON-NLS-1$ } - public void testHasCriteria1() { + @Test public void testHasCriteria1() { ElementSymbol sy1 = new ElementSymbol("x"); //$NON-NLS-1$ ElementSymbol sy2 = new ElementSymbol("y"); //$NON-NLS-1$ ElementSymbol sy3 = new ElementSymbol("z"); //$NON-NLS-1$ @@ -1465,7 +1461,7 @@ helpTest(new HasCriteria(cs), "HAS LIKE CRITERIA ON (x, y, z)"); //$NON-NLS-1$ } - public void testHasCriteria2() { + @Test public void testHasCriteria2() { ElementSymbol sy1 = new ElementSymbol("x"); //$NON-NLS-1$ ElementSymbol sy2 = new ElementSymbol("y"); //$NON-NLS-1$ ElementSymbol sy3 = new ElementSymbol("z"); //$NON-NLS-1$ @@ -1477,7 +1473,7 @@ helpTest(new HasCriteria(cs), "HAS LIKE CRITERIA ON (x, y, z)"); //$NON-NLS-1$ } - public void testHasCriteria3() { + @Test public void testHasCriteria3() { ElementSymbol sy1 = new ElementSymbol("x"); //$NON-NLS-1$ ElementSymbol sy2 = new ElementSymbol("y"); //$NON-NLS-1$ ElementSymbol sy3 = new ElementSymbol("z"); //$NON-NLS-1$ @@ -1489,7 +1485,7 @@ helpTest(new HasCriteria(cs), "HAS BETWEEN CRITERIA ON (x, y, z)"); //$NON-NLS-1$ } - public void testCommandStatement1() { + @Test public void testCommandStatement1() { Query q1 = new Query(); Select select = new Select(); select.addSymbol(new ElementSymbol("x")); //$NON-NLS-1$ @@ -1502,14 +1498,14 @@ helpTest(cmdStmt, "SELECT x FROM g;"); //$NON-NLS-1$ } - public void testCommandStatement2() { + @Test public void testCommandStatement2() { Delete d1 = new Delete(); d1.setGroup(new GroupSymbol("g")); //$NON-NLS-1$ CommandStatement cmdStmt = new CommandStatement(d1); helpTest(cmdStmt, "DELETE FROM g;"); //$NON-NLS-1$ } - public void testBlock1() { + @Test public void testBlock1() { Delete d1 = new Delete(); d1.setGroup(new GroupSymbol("g")); //$NON-NLS-1$ CommandStatement cmdStmt = new CommandStatement(d1); @@ -1522,7 +1518,7 @@ helpTest(b, "BEGIN\nDELETE FROM g;\na = 1;\nERROR 'My Error';\nEND"); //$NON-NLS-1$ } - public void testBlock2() { + @Test public void testBlock2() { // construct If statement Delete d1 = new Delete(); @@ -1547,7 +1543,7 @@ helpTest(b, "BEGIN\nDELETE FROM g;\nIF(HAS LIKE CRITERIA ON (x))\nBEGIN\nDELETE FROM g;\nEND\nERROR 'My Error';\nEND"); //$NON-NLS-1$ } - public void testIfStatement1() { + @Test public void testIfStatement1() { // construct If block Delete d1 = new Delete(); d1.setGroup(new GroupSymbol("g")); //$NON-NLS-1$ @@ -1570,7 +1566,7 @@ helpTest(ifStmt, "IF(HAS LIKE CRITERIA ON (x))\nBEGIN\nDELETE FROM g;\na = 1;\nERROR 'My Error';\nEND"); //$NON-NLS-1$ } - public void testIfStatement2() { + @Test public void testIfStatement2() { // construct If block Delete d1 = new Delete(); d1.setGroup(new GroupSymbol("g")); //$NON-NLS-1$ @@ -1588,7 +1584,7 @@ helpTest(ifStmt, "IF(HAS LIKE CRITERIA ON (x))\nBEGIN\nDELETE FROM g;\nEND"); //$NON-NLS-1$ } - public void testIfStatement3() { + @Test public void testIfStatement3() { // construct If block Delete d1 = new Delete(); d1.setGroup(new GroupSymbol("g")); //$NON-NLS-1$ @@ -1614,7 +1610,7 @@ helpTest(ifStmt, "IF(HAS LIKE CRITERIA ON (x))\nBEGIN\nDELETE FROM g;\na = 1;\nERROR 'My Error';\nEND\nELSE\nBEGIN\nDELETE FROM g;\nEND"); //$NON-NLS-1$ } - public void testCreateUpdateProcedure1() { + @Test public void testCreateUpdateProcedure1() { Delete d1 = new Delete(); d1.setGroup(new GroupSymbol("g")); //$NON-NLS-1$ CommandStatement cmdStmt = new CommandStatement(d1); @@ -1628,7 +1624,7 @@ helpTest(cup, "CREATE PROCEDURE\nBEGIN\nDELETE FROM g;\na = 1;\nERROR 'My Error';\nEND"); //$NON-NLS-1$ } - public void testCreateUpdateProcedure2() { + @Test public void testCreateUpdateProcedure2() { Delete d1 = new Delete(); d1.setGroup(new GroupSymbol("g")); //$NON-NLS-1$ CommandStatement cmdStmt = new CommandStatement(d1); @@ -1642,7 +1638,7 @@ helpTest(cup, "CREATE PROCEDURE\nBEGIN\nDELETE FROM g;\na = 1;\nERROR 'My Error';\nEND"); //$NON-NLS-1$ } - public void testCreateUpdateProcedure3() { + @Test public void testCreateUpdateProcedure3() { Delete d1 = new Delete(); d1.setGroup(new GroupSymbol("g")); //$NON-NLS-1$ CommandStatement cmdStmt = new CommandStatement(d1); @@ -1656,7 +1652,7 @@ helpTest(cup, "CREATE PROCEDURE\nBEGIN\nDELETE FROM g;\na = 1;\nERROR 'My Error';\nEND"); //$NON-NLS-1$ } - public void testSubqueryCompareCriteria1() { + @Test public void testSubqueryCompareCriteria1() { Select s1 = new Select(); s1.addSymbol(new ElementSymbol("e1")); //$NON-NLS-1$ @@ -1673,7 +1669,7 @@ helpTest(scc, "e2 = ANY (SELECT e1 FROM m.g1)"); //$NON-NLS-1$ } - public void testSubqueryCompareCriteria2() { + @Test public void testSubqueryCompareCriteria2() { Select s1 = new Select(); s1.addSymbol(new ElementSymbol("e1")); //$NON-NLS-1$ @@ -1690,7 +1686,7 @@ helpTest(scc, "e2 <= SOME (SELECT e1 FROM m.g1)"); //$NON-NLS-1$ } - public void testExistsCriteria1() { + @Test public void testExistsCriteria1() { Select s1 = new Select(); s1.addSymbol(new ElementSymbol("e1")); //$NON-NLS-1$ @@ -1705,7 +1701,7 @@ helpTest(ec, "EXISTS (SELECT e1 FROM m.g1)"); //$NON-NLS-1$ } - public void testDynamicCommand() { + @Test public void testDynamicCommand() { List symbols = new ArrayList(); ElementSymbol a1 = new ElementSymbol("a1"); //$NON-NLS-1$ @@ -1723,7 +1719,7 @@ helpTest(obj, "EXECUTE 'SELECT a1 FROM g WHERE a2 = 5' AS a1 string INTO #g"); //$NON-NLS-1$ } - public void testScalarSubquery() { + @Test public void testScalarSubquery() { Select s1 = new Select(); s1.addSymbol(new ElementSymbol("e1")); //$NON-NLS-1$ @@ -1738,7 +1734,7 @@ helpTest(obj, "(SELECT e1 FROM m.g1)"); //$NON-NLS-1$ } - public void testNewSubqueryObjects(){ + @Test public void testNewSubqueryObjects(){ Select s1 = new Select(); s1.addSymbol(new ElementSymbol("e1")); //$NON-NLS-1$ @@ -1764,35 +1760,35 @@ helpTest(q2, "SELECT e1, (SELECT e1 FROM m.g1) FROM m.g2 WHERE (e3 >= ANY (SELECT e1 FROM m.g1)) AND (EXISTS (SELECT e1 FROM m.g1))"); //$NON-NLS-1$ } - public void testCaseExpression1() { + @Test public void testCaseExpression1() { helpTest(TestCaseExpression.example(2), "CASE x WHEN 'a' THEN 0 WHEN 'b' THEN 1 ELSE 9999 END"); //$NON-NLS-1$ } - public void testCaseExpression2() { + @Test public void testCaseExpression2() { CaseExpression example = TestCaseExpression.example(2); example.setElseExpression(null); helpTest(example, "CASE x WHEN 'a' THEN 0 WHEN 'b' THEN 1 END"); //$NON-NLS-1$ } - public void testCaseExpression3() { + @Test public void testCaseExpression3() { CaseExpression example = TestCaseExpression.example(3, 0, true); helpTest(example, "CASE x WHEN null THEN 0 WHEN 'b' THEN 1 WHEN 'c' THEN 2 ELSE 9999 END"); //$NON-NLS-1$ } - public void testCaseExpression4() { + @Test public void testCaseExpression4() { CaseExpression example = TestCaseExpression.example(3, 2, true); example.setElseExpression(null); helpTest(example, "CASE x WHEN 'a' THEN 0 WHEN 'b' THEN 1 WHEN null THEN 2 END"); //$NON-NLS-1$ } - public void testSearchedCaseExpression1() { + @Test public void testSearchedCaseExpression1() { helpTest(TestSearchedCaseExpression.example(2), "CASE WHEN x = 0 THEN 0 WHEN x = 1 THEN 1 ELSE 9999 END"); //$NON-NLS-1$ } - public void testSearchedCaseExpression2() { + @Test public void testSearchedCaseExpression2() { SearchedCaseExpression example = TestSearchedCaseExpression.example(2); example.setElseExpression(null); helpTest(example, @@ -1804,7 +1800,7 @@ * For some reason this test was outputting * SELECT 'A' AS FOO UNION SELECT 'A' AS FOO */ - public void testSetQueryUnionOfLiteralsCase3102() { + @Test public void testSetQueryUnionOfLiteralsCase3102() { String expected = "SELECT 'A' AS FOO UNION SELECT 'B' AS FOO"; //$NON-NLS-1$ @@ -1829,7 +1825,7 @@ * Same as above except that ExpressionSymbols' internal names (which aren't visible * in the query) are different */ - public void testSetQueryUnionOfLiteralsCase3102a() { + @Test public void testSetQueryUnionOfLiteralsCase3102a() { String expected = "SELECT 'A' AS FOO UNION SELECT 'B' AS FOO"; //$NON-NLS-1$ @@ -1848,7 +1844,7 @@ helpTest(sq, expected); } - public void testLimit() { + @Test public void testLimit() { Query query = new Query(); Select select = new Select(Arrays.asList(new MultipleElementSymbol())); From from = new From(Arrays.asList(new UnaryFromClause(new GroupSymbol("a")))); //$NON-NLS-1$ @@ -1858,7 +1854,7 @@ helpTest(query, "SELECT * FROM a LIMIT 100"); //$NON-NLS-1$ } - public void testLimitWithOffset() { + @Test public void testLimitWithOffset() { Query query = new Query(); Select select = new Select(Arrays.asList(new MultipleElementSymbol())); From from = new From(Arrays.asList(new UnaryFromClause(new GroupSymbol("a")))); //$NON-NLS-1$ @@ -1868,28 +1864,42 @@ helpTest(query, "SELECT * FROM a LIMIT 50, 100"); //$NON-NLS-1$ } - public void testUnionOrderBy() throws Exception { + @Test public void testUnionOrderBy() throws Exception { Command command = QueryParser.getQueryParser().parseCommand("select pm1.g1.e1 from pm1.g1 union select e2 from pm1.g2 order by e1"); //$NON-NLS-1$ QueryResolver.resolveCommand(command, RealMetadataFactory.example1Cached()); helpTest(command, "SELECT pm1.g1.e1 FROM pm1.g1 UNION SELECT e2 FROM pm1.g2 ORDER BY e1"); //$NON-NLS-1$ } - public void testUnionBranchOrderBy() throws Exception { + @Test public void testUnionBranchOrderBy() throws Exception { Command command = QueryParser.getQueryParser().parseCommand("select pm1.g1.e1 from pm1.g1 union (select e2 from pm1.g2 order by e1)"); //$NON-NLS-1$ QueryResolver.resolveCommand(command, RealMetadataFactory.example1Cached()); helpTest(command, "SELECT pm1.g1.e1 FROM pm1.g1 UNION (SELECT e2 FROM pm1.g2 ORDER BY e1)"); //$NON-NLS-1$ } - public void testAliasedOrderBy() throws Exception { + @Test public void testAliasedOrderBy() throws Exception { Command command = QueryParser.getQueryParser().parseCommand("select pm1.g1.e1 as a from pm1.g1 order by a"); //$NON-NLS-1$ QueryResolver.resolveCommand(command, RealMetadataFactory.example1Cached()); helpTest(command, "SELECT pm1.g1.e1 AS a FROM pm1.g1 ORDER BY a"); //$NON-NLS-1$ } - public void testNumberOrderBy() throws Exception { + @Test public void testNumberOrderBy() throws Exception { Command command = QueryParser.getQueryParser().parseCommand("select pm1.g1.e1 as a from pm1.g1 order by 1"); //$NON-NLS-1$ QueryResolver.resolveCommand(command, RealMetadataFactory.example1Cached()); helpTest(command, "SELECT pm1.g1.e1 AS a FROM pm1.g1 ORDER BY 1"); //$NON-NLS-1$ } + + public Expression helpTestExpression(String sql, String expected) throws QueryParserException { + Expression expr = QueryParser.getQueryParser().parseExpression(sql); + helpTest(expr, expected); + return expr; + } + + @Test public void testLikeRegex() throws Exception { + helpTestExpression("x like_regex 'b'", "x LIKE_REGEX 'b'"); + } + + @Test public void testSimilar() throws Exception { + helpTestExpression("x similar to 'b' escape 'c'", "x SIMILAR TO 'b' ESCAPE 'c'"); + } }