[teiid-commits] teiid SVN: r4584 - in branches/7.7.x/engine/src: test/java/org/teiid/query/rewriter and 1 other directory.

Author: jolee Date: 2013-07-31 16:33:58 -0400 (Wed, 31 Jul 2013) New Revision: 4584 Modified: branches/7.7.x/engine/src/main/java/org/teiid/query/rewriter/QueryRewriter.java branches/7.7.x/engine/src/test/java/org/teiid/query/rewriter/TestQueryRewriter.java Log: TEIID-2500: Rewrite of case with true condition is not correct Modified: branches/7.7.x/engine/src/main/java/org/teiid/query/rewriter/QueryRewriter.java =================================================================== --- branches/7.7.x/engine/src/main/java/org/teiid/query/rewriter/QueryRewriter.java 2013-07-31 20:13:22 UTC (rev 4583) +++ branches/7.7.x/engine/src/main/java/org/teiid/query/rewriter/QueryRewriter.java 2013-07-31 20:33:58 UTC (rev 4584) @@ -2568,16 +2568,20 @@ // Check the when to see if this CASE can be rewritten due to an always true/false when Criteria rewrittenWhen = rewriteCriteria(expr.getWhenCriteria(i)); - if(rewrittenWhen == TRUE_CRITERIA) { - // WHEN is always true, so just return the THEN - return rewriteExpressionDirect(expr.getThenExpression(i)); - } if (rewrittenWhen == FALSE_CRITERIA || rewrittenWhen == UNKNOWN_CRITERIA) { continue; } whens.add(rewrittenWhen); thens.add(rewriteExpressionDirect(expr.getThenExpression(i))); + + if(rewrittenWhen == TRUE_CRITERIA) { + if (i == 0) { + // WHEN is always true, so just return the THEN + return rewriteExpressionDirect(expr.getThenExpression(i)); + } + break; + } } if (expr.getElseExpression() != null) { Modified: branches/7.7.x/engine/src/test/java/org/teiid/query/rewriter/TestQueryRewriter.java =================================================================== --- branches/7.7.x/engine/src/test/java/org/teiid/query/rewriter/TestQueryRewriter.java 2013-07-31 20:13:22 UTC (rev 4583) +++ branches/7.7.x/engine/src/test/java/org/teiid/query/rewriter/TestQueryRewriter.java 2013-07-31 20:33:58 UTC (rev 4584) @@ -1,77 +1,77 @@ -/* - * JBoss, Home of Professional Open Source. - * See the COPYRIGHT.txt file distributed with this work for information - * regarding copyright ownership. Some portions may be licensed - * to Red Hat, Inc. under one or more contributor license agreements. - * - * This library is free software; you can redistribute it and/or - * modify it under the terms of the GNU Lesser General Public - * License as published by the Free Software Foundation; either - * version 2.1 of the License, or (at your option) any later version. - * - * This library is distributed in the hope that it will be useful, - * but WITHOUT ANY WARRANTY; without even the implied warranty of - * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU - * Lesser General Public License for more details. - * - * You should have received a copy of the GNU Lesser General Public - * License along with this library; if not, write to the Free Software - * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA - * 02110-1301 USA. - */ - +/* + * JBoss, Home of Professional Open Source. + * See the COPYRIGHT.txt file distributed with this work for information + * regarding copyright ownership. Some portions may be licensed + * to Red Hat, Inc. under one or more contributor license agreements. + * + * This library is free software; you can redistribute it and/or + * modify it under the terms of the GNU Lesser General Public + * License as published by the Free Software Foundation; either + * version 2.1 of the License, or (at your option) any later version. + * + * This library is distributed in the hope that it will be useful, + * but WITHOUT ANY WARRANTY; without even the implied warranty of + * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU + * Lesser General Public License for more details. + * + * You should have received a copy of the GNU Lesser General Public + * License along with this library; if not, write to the Free Software + * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA + * 02110-1301 USA. + */ + package org.teiid.query.rewriter; -import static org.junit.Assert.*; - -import java.util.ArrayList; -import java.util.Arrays; -import java.util.Collections; -import java.util.HashMap; -import java.util.LinkedList; -import java.util.List; -import java.util.Map; -import java.util.Properties; -import java.util.TimeZone; - -import org.junit.Before; -import org.junit.Ignore; -import org.junit.Test; -import org.teiid.api.exception.query.ExpressionEvaluationException; -import org.teiid.api.exception.query.QueryMetadataException; -import org.teiid.api.exception.query.QueryValidatorException; -import org.teiid.client.metadata.ParameterInfo; -import org.teiid.common.buffer.BufferManagerFactory; -import org.teiid.core.TeiidComponentException; -import org.teiid.core.TeiidException; -import org.teiid.core.TeiidProcessingException; -import org.teiid.core.TeiidRuntimeException; -import org.teiid.core.types.DataTypeManager; -import org.teiid.core.util.TimestampWithTimezone; -import org.teiid.metadata.Table; -import org.teiid.query.eval.Evaluator; -import org.teiid.query.function.FunctionTree; -import org.teiid.query.metadata.QueryMetadataInterface; -import org.teiid.query.optimizer.FakeFunctionMetadataSource; -import org.teiid.query.parser.QueryParser; -import org.teiid.query.resolver.QueryResolver; -import org.teiid.query.resolver.util.ResolverVisitor; -import org.teiid.query.sql.lang.*; -import org.teiid.query.sql.proc.CreateUpdateProcedureCommand; -import org.teiid.query.sql.symbol.Constant; -import org.teiid.query.sql.symbol.ElementSymbol; -import org.teiid.query.sql.symbol.Expression; -import org.teiid.query.sql.symbol.ExpressionSymbol; -import org.teiid.query.sql.symbol.GroupSymbol; -import org.teiid.query.sql.symbol.Reference; -import org.teiid.query.sql.symbol.SearchedCaseExpression; -import org.teiid.query.sql.symbol.SingleElementSymbol; -import org.teiid.query.sql.visitor.CorrelatedReferenceCollectorVisitor; -import org.teiid.query.unittest.RealMetadataFactory; -import org.teiid.query.util.CommandContext; -import org.teiid.query.util.ContextProperties; - - +import static org.junit.Assert.*; + +import java.util.ArrayList; +import java.util.Arrays; +import java.util.Collections; +import java.util.HashMap; +import java.util.LinkedList; +import java.util.List; +import java.util.Map; +import java.util.Properties; +import java.util.TimeZone; + +import org.junit.Before; +import org.junit.Ignore; +import org.junit.Test; +import org.teiid.api.exception.query.ExpressionEvaluationException; +import org.teiid.api.exception.query.QueryMetadataException; +import org.teiid.api.exception.query.QueryValidatorException; +import org.teiid.client.metadata.ParameterInfo; +import org.teiid.common.buffer.BufferManagerFactory; +import org.teiid.core.TeiidComponentException; +import org.teiid.core.TeiidException; +import org.teiid.core.TeiidProcessingException; +import org.teiid.core.TeiidRuntimeException; +import org.teiid.core.types.DataTypeManager; +import org.teiid.core.util.TimestampWithTimezone; +import org.teiid.metadata.Table; +import org.teiid.query.eval.Evaluator; +import org.teiid.query.function.FunctionTree; +import org.teiid.query.metadata.QueryMetadataInterface; +import org.teiid.query.optimizer.FakeFunctionMetadataSource; +import org.teiid.query.parser.QueryParser; +import org.teiid.query.resolver.QueryResolver; +import org.teiid.query.resolver.util.ResolverVisitor; +import org.teiid.query.sql.lang.*; +import org.teiid.query.sql.proc.CreateUpdateProcedureCommand; +import org.teiid.query.sql.symbol.Constant; +import org.teiid.query.sql.symbol.ElementSymbol; +import org.teiid.query.sql.symbol.Expression; +import org.teiid.query.sql.symbol.ExpressionSymbol; +import org.teiid.query.sql.symbol.GroupSymbol; +import org.teiid.query.sql.symbol.Reference; +import org.teiid.query.sql.symbol.SearchedCaseExpression; +import org.teiid.query.sql.symbol.SingleElementSymbol; +import org.teiid.query.sql.visitor.CorrelatedReferenceCollectorVisitor; +import org.teiid.query.unittest.RealMetadataFactory; +import org.teiid.query.util.CommandContext; +import org.teiid.query.util.ContextProperties; + + @SuppressWarnings("nls") public class TestQueryRewriter { @@ -109,66 +109,66 @@ expectedCrit = QueryRewriter.rewriteCriteria(expectedCrit, null, null, metadata); } return helpTestRewriteCriteria(original, expectedCrit, metadata); - } - - private Map<ElementSymbol, Integer> elements; - private List<List<? extends Object>> tuples; - - @Before public void setUp() { - elements = null; - tuples = new ArrayList<List<? extends Object>>(); } + + private Map<ElementSymbol, Integer> elements; + private List<List<? extends Object>> tuples; + + @Before public void setUp() { + elements = null; + tuples = new ArrayList<List<? extends Object>>(); + } private Criteria helpTestRewriteCriteria(String original, Criteria expectedCrit, QueryMetadataInterface metadata) { Criteria origCrit = parseCriteria(original, metadata); Criteria actual = null; // rewrite - try { - ArrayList<Boolean> booleanVals = new ArrayList<Boolean>(tuples.size()); - for (List<?> tuple : tuples) { - booleanVals.add(new Evaluator(elements, null, null).evaluate(origCrit, tuple)); - } - actual = QueryRewriter.rewriteCriteria(origCrit, null, null, metadata); - assertEquals("Did not rewrite correctly: ", expectedCrit, actual); //$NON-NLS-1$ - for (int i = 0; i < tuples.size(); i++) { - assertEquals(tuples.get(i).toString(), booleanVals.get(i), new Evaluator(elements, null, null).evaluate(actual, tuples.get(i))); + try { + ArrayList<Boolean> booleanVals = new ArrayList<Boolean>(tuples.size()); + for (List<?> tuple : tuples) { + booleanVals.add(new Evaluator(elements, null, null).evaluate(origCrit, tuple)); } + actual = QueryRewriter.rewriteCriteria(origCrit, null, null, metadata); + assertEquals("Did not rewrite correctly: ", expectedCrit, actual); //$NON-NLS-1$ + for (int i = 0; i < tuples.size(); i++) { + assertEquals(tuples.get(i).toString(), booleanVals.get(i), new Evaluator(elements, null, null).evaluate(actual, tuples.get(i))); + } } catch(TeiidException e) { throw new RuntimeException(e); } return actual; - } - - private Expression helpTestRewriteExpression(String original, String expected, QueryMetadataInterface metadata) throws TeiidComponentException, TeiidProcessingException { - Expression actualExp = QueryParser.getQueryParser().parseExpression(original); - ResolverVisitor.resolveLanguageObject(actualExp, metadata); - CommandContext context = new CommandContext(); - context.setBufferManager(BufferManagerFactory.getStandaloneBufferManager()); - actualExp = QueryRewriter.rewriteExpression(actualExp, null, context, metadata); - if (expected != null) { - Expression expectedExp = QueryParser.getQueryParser().parseExpression(expected); - ResolverVisitor.resolveLanguageObject(expectedExp, metadata); - assertEquals(expectedExp, actualExp); - } - return actualExp; + } + + private Expression helpTestRewriteExpression(String original, String expected, QueryMetadataInterface metadata) throws TeiidComponentException, TeiidProcessingException { + Expression actualExp = QueryParser.getQueryParser().parseExpression(original); + ResolverVisitor.resolveLanguageObject(actualExp, metadata); + CommandContext context = new CommandContext(); + context.setBufferManager(BufferManagerFactory.getStandaloneBufferManager()); + actualExp = QueryRewriter.rewriteExpression(actualExp, null, context, metadata); + if (expected != null) { + Expression expectedExp = QueryParser.getQueryParser().parseExpression(expected); + ResolverVisitor.resolveLanguageObject(expectedExp, metadata); + assertEquals(expectedExp, actualExp); + } + return actualExp; } private String getRewritenProcedure(String procedure, String userUpdateStr, Table.TriggerEvent procedureType) throws TeiidComponentException, TeiidProcessingException { QueryMetadataInterface metadata = RealMetadataFactory.exampleUpdateProc(procedureType, procedure); return getRewritenProcedure(userUpdateStr, metadata); - } - - private String getRewritenProcedure(String userUpdateStr, - QueryMetadataInterface metadata) throws TeiidComponentException, - QueryMetadataException, TeiidProcessingException { - ProcedureContainer userCommand = (ProcedureContainer)QueryParser.getQueryParser().parseCommand(userUpdateStr); - QueryResolver.resolveCommand(userCommand, metadata); + } + + private String getRewritenProcedure(String userUpdateStr, + QueryMetadataInterface metadata) throws TeiidComponentException, + QueryMetadataException, TeiidProcessingException { + ProcedureContainer userCommand = (ProcedureContainer)QueryParser.getQueryParser().parseCommand(userUpdateStr); + QueryResolver.resolveCommand(userCommand, metadata); CreateUpdateProcedureCommand proc = (CreateUpdateProcedureCommand)QueryResolver.expandCommand(userCommand, metadata, null); - QueryRewriter.rewrite(userCommand, metadata, null); + QueryRewriter.rewrite(userCommand, metadata, null); Command result = QueryRewriter.rewrite(proc, metadata, null); - return result.toString(); + return result.toString(); } static Command helpTestRewriteCommand(String original, String expected) { @@ -233,20 +233,20 @@ @Test public void testRewriteInCriteriaWithSingleValue1() { helpTestRewriteCriteria("pm1.g1.e1 not in ('1')", "pm1.g1.e1 != '1'"); //$NON-NLS-1$ //$NON-NLS-2$ - } - - @Test public void testRewriteInCriteriaWithConvert() { - helpTestRewriteCriteria("convert(pm1.g1.e2, string) not in ('x')", "pm1.g1.e2 IS NOT NULL"); //$NON-NLS-1$ //$NON-NLS-2$ - } + } - @Test public void testRewriteInCriteriaWithNoValues() throws Exception { - ElementSymbol e1 = new ElementSymbol("e1"); + @Test public void testRewriteInCriteriaWithConvert() { + helpTestRewriteCriteria("convert(pm1.g1.e2, string) not in ('x')", "pm1.g1.e2 IS NOT NULL"); //$NON-NLS-1$ //$NON-NLS-2$ + } + + @Test public void testRewriteInCriteriaWithNoValues() throws Exception { + ElementSymbol e1 = new ElementSymbol("e1"); e1.setGroupSymbol(new GroupSymbol("g1")); Criteria crit = new SetCriteria(e1, Collections.EMPTY_LIST); //$NON-NLS-1$ Criteria actual = QueryRewriter.rewriteCriteria(crit, null, null, null); - - IsNullCriteria inc = new IsNullCriteria(e1); + + IsNullCriteria inc = new IsNullCriteria(e1); inc.setNegated(true); assertEquals(inc, actual); } @@ -278,7 +278,7 @@ @Test public void testRewriteCrit5() { helpTestRewriteCriteria("pm1.g1.e1 in ('a')", "pm1.g1.e1 = 'a'"); //$NON-NLS-1$ //$NON-NLS-2$ } - + @Ignore(value="It's not generally possible to invert a narrowing conversion") @Test public void testRewriteCrit6() { helpTestRewriteCriteria("1 = convert(pm1.g1.e1,integer) + 10", "pm1.g1.e1 = '-9'"); //$NON-NLS-1$ //$NON-NLS-2$ @@ -403,25 +403,25 @@ @Test public void testRewriteCritExpr_unhandled2() { helpTestRewriteCriteria("5 - pm1.g1.e2 <= 10", "5 - pm1.g1.e2 <= 10"); //$NON-NLS-1$ //$NON-NLS-2$ } - + @Ignore(value="It's not generally possible to invert a narrowing conversion") @Test public void testRewriteCrit_parseDate() { helpTestRewriteCriteria("PARSEDATE(pm3.g1.e1, 'yyyyMMdd') = {d'2003-05-01'}", //$NON-NLS-1$ "pm3.g1.e1 = '20030501'" ); //$NON-NLS-1$ - } - - @Ignore(value="It's not generally possible to invert a narrowing conversion") + } + + @Ignore(value="It's not generally possible to invert a narrowing conversion") @Test public void testRewriteCrit_parseDate1() { helpTestRewriteCriteria("PARSEDATE(pm3.g1.e1, 'yyyyMM') = {d'2003-05-01'}", //$NON-NLS-1$ "pm3.g1.e1 = '200305'" ); //$NON-NLS-1$ } - - @Ignore(value="we're no longer considering parsedate directly") + + @Ignore(value="we're no longer considering parsedate directly") @Test public void testRewriteCrit_parseDate2() { helpTestRewriteCriteria("PARSEDATE(pm3.g1.e1, 'yyyyMM') = {d'2003-05-02'}", //$NON-NLS-1$ "1 = 0" ); //$NON-NLS-1$ } - + @Ignore(value="Should be moved to the validator") @Test public void testRewriteCrit_invalidParseDate() { QueryMetadataInterface metadata = RealMetadataFactory.example1Cached(); @@ -434,7 +434,7 @@ assertEquals("Error Code:ERR.015.001.0003 Message:Error simplifying criteria: PARSEDATE(pm3.g1.e1, '''') = {d'2003-05-01'}", e.getMessage()); //$NON-NLS-1$ } } - + @Ignore(value="It's not generally possible to invert a narrowing conversion") @Test public void testRewriteCrit_parseTime() { helpTestRewriteCriteria("PARSETIME(pm3.g1.e1, 'HH mm ss') = {t'13:25:04'}", //$NON-NLS-1$ @@ -445,7 +445,7 @@ helpTestRewriteCriteria("PARSETimestamp(pm3.g1.e1, 'yyyy dd mm') = {ts'2003-05-01 13:25:04.5'}", //$NON-NLS-1$ "1 = 0" ); //$NON-NLS-1$ } - + @Ignore(value="It's not generally possible to invert a narrowing conversion") @Test public void testRewriteCrit_parseTimestamp1() { helpTestRewriteCriteria("PARSETimestamp(pm3.g1.e1, 'yyyy dd mm') = {ts'2003-01-01 00:25:00.0'}", //$NON-NLS-1$ @@ -459,9 +459,9 @@ @Test public void testRewriteCrit_parseTimestamp3() { helpTestRewriteCriteria("PARSETimestamp(pm3.g1.e1, 'yyyy dd mm') <> {ts'2003-05-01 13:25:04.5'}", //$NON-NLS-1$ - "pm3.g1.e1 is not null" ); //$NON-NLS-1$ + "pm3.g1.e1 is not null" ); //$NON-NLS-1$ } - + @Ignore(value="It's not generally possible to invert a narrowing conversion") @Test public void testRewriteCrit_parseTimestamp4() { helpTestRewriteCriteria("PARSETimestamp(CONVERT(pm3.g1.e2, string), 'yyyy-MM-dd') = {ts'2003-05-01 00:00:00.0'}", //$NON-NLS-1$ @@ -471,61 +471,61 @@ @Test public void testRewriteCrit_parseTimestamp_notEquality() { helpTestRewriteCriteria("PARSETimestamp(pm3.g1.e1, 'yyyy dd mm') > {ts'2003-05-01 13:25:04.5'}", //$NON-NLS-1$ "PARSETimestamp(pm3.g1.e1, 'yyyy dd mm') > {ts'2003-05-01 13:25:04.5'}" ); //$NON-NLS-1$ - } - - @Ignore(value="It's not generally possible to invert a narrowing conversion") - @Test public void testRewriteCrit_parseTimestamp_decompose() { - helpTestRewriteCriteria("PARSETIMESTAMP(CONCAT(FORMATDATE(pm3.g1.e2, 'yyyyMMdd'), FORMATTIME(pm3.g1.e3, 'HHmmss')), 'yyyyMMddHHmmss') = PARSETIMESTAMP('19690920183045', 'yyyyMMddHHmmss')", //$NON-NLS-1$ - "(pm3.g1.e2 = {d'1969-09-20'}) AND (pm3.g1.e3 = {t'18:30:45'})" ); //$NON-NLS-1$ - } - - @Test public void testRewriteCrit_timestampCreate_decompose() { - helpTestRewriteCriteria("timestampCreate(pm3.g1.e2, pm3.g1.e3) = PARSETIMESTAMP('19690920183045', 'yyyyMMddHHmmss')", //$NON-NLS-1$ - "(pm3.g1.e2 = {d'1969-09-20'}) AND (pm3.g1.e3 = {t'18:30:45'})" ); //$NON-NLS-1$ } - + @Ignore(value="It's not generally possible to invert a narrowing conversion") + @Test public void testRewriteCrit_parseTimestamp_decompose() { + helpTestRewriteCriteria("PARSETIMESTAMP(CONCAT(FORMATDATE(pm3.g1.e2, 'yyyyMMdd'), FORMATTIME(pm3.g1.e3, 'HHmmss')), 'yyyyMMddHHmmss') = PARSETIMESTAMP('19690920183045', 'yyyyMMddHHmmss')", //$NON-NLS-1$ + "(pm3.g1.e2 = {d'1969-09-20'}) AND (pm3.g1.e3 = {t'18:30:45'})" ); //$NON-NLS-1$ + } + + @Test public void testRewriteCrit_timestampCreate_decompose() { + helpTestRewriteCriteria("timestampCreate(pm3.g1.e2, pm3.g1.e3) = PARSETIMESTAMP('19690920183045', 'yyyyMMddHHmmss')", //$NON-NLS-1$ + "(pm3.g1.e2 = {d'1969-09-20'}) AND (pm3.g1.e3 = {t'18:30:45'})" ); //$NON-NLS-1$ + } + + @Ignore(value="It's not generally possible to invert a narrowing conversion") @Test public void testRewriteCrit_parseInteger() { helpTestRewriteCriteria("parseInteger(pm1.g1.e1, '#,##0') = 1234", //$NON-NLS-1$ "pm1.g1.e1 = '1,234'" ); //$NON-NLS-1$ } - + @Ignore(value="It's not generally possible to invert a narrowing conversion") @Test public void testRewriteCrit_parseLong() { helpTestRewriteCriteria("parseLong(pm1.g1.e1, '#,##0') = convert(1234, long)", //$NON-NLS-1$ "pm1.g1.e1 = '1,234'" ); //$NON-NLS-1$ } - + @Ignore(value="It's not generally possible to invert a narrowing conversion") @Test public void testRewriteCrit_parseBigInteger() { helpTestRewriteCriteria("parseBigInteger(pm1.g1.e1, '#,##0') = convert(1234, biginteger)", //$NON-NLS-1$ "pm1.g1.e1 = '1,234'" ); //$NON-NLS-1$ } - + @Ignore(value="It's not generally possible to invert a narrowing conversion") @Test public void testRewriteCrit_parseFloat() { helpTestRewriteCriteria("parseFloat(pm1.g1.e1, '#,##0.###') = convert(1234.123, float)", //$NON-NLS-1$ "pm1.g1.e1 = '1,234.123'" ); //$NON-NLS-1$ } - + @Ignore(value="It's not generally possible to invert a narrowing conversion") @Test public void testRewriteCrit_parseDouble() { helpTestRewriteCriteria("parseDouble(pm1.g1.e1, '$#,##0.00') = convert(1234.5, double)", //$NON-NLS-1$ "pm1.g1.e1 = '$1,234.50'" ); //$NON-NLS-1$ } - + @Ignore(value="It's not generally possible to invert a narrowing conversion") @Test public void testRewriteCrit_parseBigDecimal() { helpTestRewriteCriteria("parseBigDecimal(pm1.g1.e1, '#,##0.###') = convert(1234.1234, bigdecimal)", //$NON-NLS-1$ "pm1.g1.e1 = '1,234.123'" ); //$NON-NLS-1$ } - + @Ignore(value="Cannot deterime if the format is narrowing") @Test public void testRewriteCrit_formatDate() { helpTestRewriteCriteria("formatDate(pm3.g1.e2, 'yyyyMMdd') = '20030501'", //$NON-NLS-1$ "pm3.g1.e2 = {d'2003-05-01'}" ); //$NON-NLS-1$ } - + @Ignore(value="Cannot deterime if the format is narrowing") @Test public void testRewriteCrit_formatTime() { helpTestRewriteCriteria("formatTime(pm3.g1.e3, 'HH mm ss') = '13 25 04'", //$NON-NLS-1$ @@ -536,13 +536,13 @@ helpTestRewriteCriteria("formatTimestamp(pm3.g1.e4, 'MM dd, yyyy - HH:mm:ss') = '05 01, 1974 - 07:00:00'", //$NON-NLS-1$ "formatTimestamp(pm3.g1.e4, 'MM dd, yyyy - HH:mm:ss') = '05 01, 1974 - 07:00:00'" ); //$NON-NLS-1$ } - + @Ignore(value="Cannot deterime if the format is narrowing") @Test public void testRewriteCrit_formatTimestamp1() { helpTestRewriteCriteria("formatTimestamp(pm3.g1.e4, 'MM dd, yyyy - HH:mm:ss.S') = '05 01, 1974 - 07:00:00.0'", //$NON-NLS-1$ "pm3.g1.e4 = {ts'1974-05-01 07:00:00.0'}" ); //$NON-NLS-1$ } - + @Ignore(value="Cannot deterime if the format is narrowing") @Test public void testRewriteCrit_formatInteger() { helpTestRewriteCriteria("formatInteger(pm1.g1.e2, '#,##0') = '1,234'", //$NON-NLS-1$ @@ -553,20 +553,20 @@ helpTestRewriteCriteria("formatInteger(pm1.g1.e2, '#5') = '105'", //$NON-NLS-1$ "formatbigdecimal(convert(pm1.g1.e2, bigdecimal), '#5') = '105'", true ); //$NON-NLS-1$ } - + @Ignore(value="Cannot deterime if the format is narrowing") @Test public void testRewriteCrit_formatLong() { helpTestRewriteCriteria("formatLong(convert(pm1.g1.e2, long), '#,##0') = '1,234,567,890,123'", //$NON-NLS-1$ "1 = 0" ); //$NON-NLS-1$ - } - - @Ignore(value="Cannot deterime if the format is narrowing") - @Test public void testRewriteCrit_formatLong1() { - helpTestRewriteCriteria("formatLong(convert(pm1.g1.e2, long), '#,##0') = '1,234,567,890'", //$NON-NLS-1$ - "pm1.g1.e2 = 1234567890" ); //$NON-NLS-1$ } - + @Ignore(value="Cannot deterime if the format is narrowing") + @Test public void testRewriteCrit_formatLong1() { + helpTestRewriteCriteria("formatLong(convert(pm1.g1.e2, long), '#,##0') = '1,234,567,890'", //$NON-NLS-1$ + "pm1.g1.e2 = 1234567890" ); //$NON-NLS-1$ + } + + @Ignore(value="Cannot deterime if the format is narrowing") @Test public void testRewriteCrit_formatTimestampInvert() { String original = "formatTimestamp(pm3.g1.e4, 'MM dd, yyyy - HH:mm:ss.S') = ?"; //$NON-NLS-1$ String expected = "pm3.g1.e4 = parseTimestamp(?, 'MM dd, yyyy - HH:mm:ss.S')"; //$NON-NLS-1$ @@ -580,7 +580,7 @@ helpTestRewriteCriteria(original, expected); } - + @Ignore(value="Cannot deterime if the format is narrowing") @Test public void testRewriteCrit_formatBigInteger() throws Exception { String original = "formatBigInteger(convert(pm1.g1.e2, biginteger), '#,##0') = '1,234,567,890'"; //$NON-NLS-1$ @@ -594,7 +594,7 @@ Criteria actual = QueryRewriter.rewriteCriteria(origCrit, null, null, null); assertEquals("Did not rewrite correctly: ", expectedCrit, actual); //$NON-NLS-1$ } - + @Ignore(value="Cannot deterime if the format is narrowing") @Test public void testRewriteCrit_formatFloat() throws Exception { String original = "formatFloat(convert(pm1.g1.e4, float), '#,##0.###') = '1,234.123'"; //$NON-NLS-1$ @@ -607,7 +607,7 @@ Criteria actual = QueryRewriter.rewriteCriteria(origCrit, null, null, null); assertEquals("Did not rewrite correctly: ", expected, actual.toString()); //$NON-NLS-1$ } - + @Ignore(value="Cannot deterime if the format is narrowing") @Test public void testRewriteCrit_formatDouble() throws Exception { String original = "formatDouble(convert(pm1.g1.e4, double), '$#,##0.00') = '$1,234.50'"; //$NON-NLS-1$ @@ -622,7 +622,7 @@ Criteria actual = QueryRewriter.rewriteCriteria(origCrit, null, null, null); assertEquals("Did not rewrite correctly: ", expectedCrit, actual); //$NON-NLS-1$ } - + @Ignore(value="Cannot deterime if the format is narrowing") @Test public void testRewriteCrit_formatBigDecimal() throws Exception { String original = "formatBigDecimal(convert(pm1.g1.e4, bigdecimal), '#,##0.###') = '1,234.5'"; //$NON-NLS-1$ @@ -820,7 +820,7 @@ @Test public void testCompareSubqueryUnknown() { helpTestRewriteCommand("SELECT e1 FROM pm1.g1 WHERE null = SOME (SELECT e1 FROM pm1.g2)", //$NON-NLS-1$ - "SELECT e1 FROM pm1.g1 WHERE 1 = 0"); //$NON-NLS-1$ + "SELECT e1 FROM pm1.g1 WHERE 1 = 0"); //$NON-NLS-1$ } @Test public void testINClauseSubquery() { @@ -1209,49 +1209,49 @@ Table.TriggerEvent.UPDATE); assertEquals("Rewritten command was not expected", rewritProc, procReturned); //$NON-NLS-1$ - } - - @Test public void testRewriteNoUserCriteria() throws Exception { - String procedure = exampleTranslateUpdate(); - - String userQuery = "UPDATE vm1.g3 SET x='x'"; //$NON-NLS-1$ - - String rewritProc = "CREATE PROCEDURE\n"; //$NON-NLS-1$ - rewritProc = rewritProc + "BEGIN\n"; //$NON-NLS-1$ - rewritProc = rewritProc + "DECLARE integer var1;\n"; //$NON-NLS-1$ - rewritProc = rewritProc + "UPDATE pm1.g1 SET e1 = 'x' WHERE 1 = 1;\n"; //$NON-NLS-1$ - rewritProc = rewritProc + "END"; //$NON-NLS-1$ - - String procReturned = this.getRewritenProcedure(procedure, userQuery, - Table.TriggerEvent.UPDATE); - - assertEquals("Rewritten command was not expected", rewritProc, procReturned); //$NON-NLS-1$ - } - - @Test public void testRewriteOrUserCriteria() throws Exception { - String procedure = exampleTranslateUpdate(); - - String userQuery = "UPDATE vm1.g3 SET x='x' where x = '1' or x = '2'"; //$NON-NLS-1$ - - String rewritProc = "CREATE PROCEDURE\n"; //$NON-NLS-1$ - rewritProc = rewritProc + "BEGIN\n"; //$NON-NLS-1$ - rewritProc = rewritProc + "DECLARE integer var1;\n"; //$NON-NLS-1$ - rewritProc = rewritProc + "UPDATE pm1.g1 SET e1 = 'x' WHERE CONCAT(e1, 'm') IN ('2', '1');\n"; //$NON-NLS-1$ - rewritProc = rewritProc + "END"; //$NON-NLS-1$ - - String procReturned = this.getRewritenProcedure(procedure, userQuery, - Table.TriggerEvent.UPDATE); - - assertEquals("Rewritten command was not expected", rewritProc, procReturned); //$NON-NLS-1$ - } - - private String exampleTranslateUpdate() { + } + + @Test public void testRewriteNoUserCriteria() throws Exception { + String procedure = exampleTranslateUpdate(); + + String userQuery = "UPDATE vm1.g3 SET x='x'"; //$NON-NLS-1$ + + String rewritProc = "CREATE PROCEDURE\n"; //$NON-NLS-1$ + rewritProc = rewritProc + "BEGIN\n"; //$NON-NLS-1$ + rewritProc = rewritProc + "DECLARE integer var1;\n"; //$NON-NLS-1$ + rewritProc = rewritProc + "UPDATE pm1.g1 SET e1 = 'x' WHERE 1 = 1;\n"; //$NON-NLS-1$ + rewritProc = rewritProc + "END"; //$NON-NLS-1$ + + String procReturned = this.getRewritenProcedure(procedure, userQuery, + Table.TriggerEvent.UPDATE); + + assertEquals("Rewritten command was not expected", rewritProc, procReturned); //$NON-NLS-1$ + } + + @Test public void testRewriteOrUserCriteria() throws Exception { + String procedure = exampleTranslateUpdate(); + + String userQuery = "UPDATE vm1.g3 SET x='x' where x = '1' or x = '2'"; //$NON-NLS-1$ + + String rewritProc = "CREATE PROCEDURE\n"; //$NON-NLS-1$ + rewritProc = rewritProc + "BEGIN\n"; //$NON-NLS-1$ + rewritProc = rewritProc + "DECLARE integer var1;\n"; //$NON-NLS-1$ + rewritProc = rewritProc + "UPDATE pm1.g1 SET e1 = 'x' WHERE CONCAT(e1, 'm') IN ('2', '1');\n"; //$NON-NLS-1$ + rewritProc = rewritProc + "END"; //$NON-NLS-1$ + + String procReturned = this.getRewritenProcedure(procedure, userQuery, + Table.TriggerEvent.UPDATE); + + assertEquals("Rewritten command was not expected", rewritProc, procReturned); //$NON-NLS-1$ + } + + private String exampleTranslateUpdate() { String procedure = "CREATE PROCEDURE "; //$NON-NLS-1$ procedure = procedure + "BEGIN\n"; //$NON-NLS-1$ procedure = procedure + "DECLARE integer var1;\n"; //$NON-NLS-1$ procedure = procedure + "update pm1.g1 set pm1.g1.e1 = inputs.x where TRANSLATE CRITERIA;\n"; //$NON-NLS-1$ - procedure = procedure + "END\n"; //$NON-NLS-1$ - return procedure; + procedure = procedure + "END\n"; //$NON-NLS-1$ + return procedure; } @Test public void testRewriteProcedure17() throws Exception { @@ -1413,7 +1413,7 @@ String userQuery = "UPDATE vm1.g1 set E2=1 where e2 = 1 and e1 LIKE 'mnopxyz_'"; //$NON-NLS-1$ this.getRewritenProcedure(procedure, userQuery, - Table.TriggerEvent.UPDATE); + Table.TriggerEvent.UPDATE); } // INPUT vars in insert statements replaced by default variable when user's inser ignores values @@ -1535,7 +1535,7 @@ QueryParser parser = new QueryParser(); Command command = parser.parseCommand("exec pm1.sp4(5)"); //$NON-NLS-1$ - // resolve + // resolve QueryMetadataInterface metadata = RealMetadataFactory.example1Cached(); QueryResolver.resolveCommand(command, metadata); @@ -1543,8 +1543,8 @@ Command rewriteCommand = QueryRewriter.rewrite(command, metadata, null); List<SPParameter> parameters = ((StoredProcedure)rewriteCommand).getParameters(); - - for (SPParameter param : parameters) { + + for (SPParameter param : parameters) { if(param.getParameterType() == ParameterInfo.IN || param.getParameterType() == ParameterInfo.INOUT){ assertTrue(param.getExpression() instanceof Constant); } @@ -1559,7 +1559,7 @@ try { QueryRewriter.rewriteCriteria(origCrit, null, null, metadata); fail("Expected QueryValidatorException due to divide by 0"); //$NON-NLS-1$ - } catch(TeiidException e) { + } catch(TeiidException e) { // looks like message is being wrapped with another exception with same message assertEquals("Error Code:ERR.015.001.0003 Message:Unable to evaluate (5 / 0): Error Code:ERR.015.001.0003 Message:Error while evaluating function /", e.getMessage()); //$NON-NLS-1$ } @@ -1625,7 +1625,7 @@ @Test public void testRewriteCase1954c() { helpTestRewriteCriteria("convert(pm1.g1.e1, string) = 'x'", "pm1.g1.e1 = 'x'"); //$NON-NLS-1$ //$NON-NLS-2$ } - + @Ignore(value="It's not generally possible to invert a narrowing conversion") @Test public void testRewriteCase1954d() { helpTestRewriteCriteria("convert(pm1.g1.e1, timestamp) = {ts '2005-01-03 00:00:00.0'}", "pm1.g1.e1 = '2005-01-03 00:00:00.0'"); //$NON-NLS-1$ //$NON-NLS-2$ @@ -1642,7 +1642,7 @@ /** Check that this returns true, x is not convertable to an int */ @Test public void testRewriteCase1954f1() { - helpTestRewriteCriteria("convert(pm1.g1.e2, string) != 'x'", "pm1.g1.e2 is not null"); //$NON-NLS-1$ //$NON-NLS-2$ + helpTestRewriteCriteria("convert(pm1.g1.e2, string) != 'x'", "pm1.g1.e2 is not null"); //$NON-NLS-1$ //$NON-NLS-2$ } @Test public void testRewriteCase1954Set() { @@ -1669,6 +1669,10 @@ @Test public void testRewriteCaseExpr1() { helpTestRewriteCriteria("case when 0=0 then 1 else 2 end = 1", "1 = 1"); //$NON-NLS-1$ //$NON-NLS-2$ } + + @Test public void testRewriteCaseExpr1a() { + helpTestRewriteCriteria("case when pm1.g1.e1 = 'a' then 3 when 0=0 then 1 when 1=1 then 4 else 2 end = 1", "CASE WHEN pm1.g1.e1 = 'a' THEN 3 WHEN 1 = 1 THEN 1 ELSE 2 END = 1"); //$NON-NLS-1$ //$NON-NLS-2$ + } // First WHEN always false, so rewrite as ELSE expression @Test public void testRewriteCaseExpr2() { @@ -1697,9 +1701,9 @@ helpTestRewriteCriteria("case when 0 = pm1.g1.e2 then null else null end IS NULL", TRUE_STR); //$NON-NLS-1$ } - @Test public void testRewriteConstantAgg2() throws Exception { - helpTestRewriteCommand("select count(2) from pm1.g1 group by e1", "SELECT COUNT(2) FROM pm1.g1 GROUP BY e1"); - } + @Test public void testRewriteConstantAgg2() throws Exception { + helpTestRewriteCommand("select count(2) from pm1.g1 group by e1", "SELECT COUNT(2) FROM pm1.g1 GROUP BY e1"); + } @Test public void testRewriteCaseExprForCase5413a() { helpTestRewriteCriteria("pm1.g2.e1 = case when 0 = pm1.g1.e2 then 2 else 2 end", "pm1.g2.e1 = '2'"); //$NON-NLS-1$ //$NON-NLS-2$ @@ -1761,18 +1765,18 @@ @Test public void testDefect16879_7(){ helpTestRewriteCommand("SELECT decodeinteger(e1, 'a, b, null, d, e') FROM pm1.g1", "SELECT convert(CASE WHEN e1 = 'a' THEN 'b' WHEN e1 IS NULL THEN 'd' ELSE 'e' END, integer) FROM pm1.g1"); //$NON-NLS-1$ //$NON-NLS-2$ } - - @Test public void testCaseExpressionThatResolvesToNull() { - String sqlBefore = "SELECT CASE 'x' WHEN 'Old Inventory System' THEN NULL WHEN 'New Inventory System' THEN NULL END"; //$NON-NLS-1$ - String sqlAfter = "SELECT null"; //$NON-NLS-1$ - - Command cmd = helpTestRewriteCommand( sqlBefore, sqlAfter ); - - ExpressionSymbol es = (ExpressionSymbol)cmd.getProjectedSymbols().get(0); - assertEquals( DataTypeManager.DefaultDataClasses.STRING, es.getType() ); - } - + + @Test public void testCaseExpressionThatResolvesToNull() { + String sqlBefore = "SELECT CASE 'x' WHEN 'Old Inventory System' THEN NULL WHEN 'New Inventory System' THEN NULL END"; //$NON-NLS-1$ + String sqlAfter = "SELECT null"; //$NON-NLS-1$ + Command cmd = helpTestRewriteCommand( sqlBefore, sqlAfter ); + + ExpressionSymbol es = (ExpressionSymbol)cmd.getProjectedSymbols().get(0); + assertEquals( DataTypeManager.DefaultDataClasses.STRING, es.getType() ); + } + + //note that the env is now treated as deterministic, however it is really only deterministic within a session @Test public void testRewriteExecEnv() throws Exception { Command command = QueryParser.getQueryParser().parseCommand("exec pm1.sq2(env('sessionid'))"); //$NON-NLS-1$ @@ -1787,12 +1791,12 @@ assertEquals("EXEC pm1.sq2('1')", rewriteCommand.toString()); //$NON-NLS-1$ } - + @Ignore(value="It's not generally possible to invert a narrowing conversion") @Test public void testRewriteNestedFunctions() { helpTestRewriteCommand("SELECT e1 FROM pm1.g1 where convert(parsedate(e1, 'yyyy-MM-dd'), string) = '2006-07-01'", "SELECT e1 FROM pm1.g1 WHERE e1 = '2006-07-01'"); //$NON-NLS-1$ //$NON-NLS-2$ } - + @Ignore(value="It's not generally possible to invert a narrowing conversion") @Test public void testRewriteWithReference() { helpTestRewriteCommand("SELECT e1 FROM pm1.g1 where parsetimestamp(e1, 'yyyy-MM-dd') != ?", "SELECT e1 FROM pm1.g1 WHERE e1 <> formattimestamp(?, 'yyyy-MM-dd')"); //$NON-NLS-1$ //$NON-NLS-2$ @@ -1819,7 +1823,7 @@ String userQuery = "Insert into vm1.g1 (e1, e2) values ('String', 1)"; //$NON-NLS-1$ try { - getRewritenProcedure(procedure, userQuery, Table.TriggerEvent.INSERT); + getRewritenProcedure(procedure, userQuery, Table.TriggerEvent.INSERT); fail("exception expected"); //$NON-NLS-1$ } catch (QueryValidatorException e) { assertEquals("Infinite loop detected, procedure will not be executed.", e.getMessage()); //$NON-NLS-1$ @@ -1893,9 +1897,9 @@ String userUpdateStr = "UPDATE vm1.g1 SET e1 = 'x' WHERE e2 = 5"; //$NON-NLS-1$ QueryMetadataInterface metadata = RealMetadataFactory.exampleUpdateProc(Table.TriggerEvent.UPDATE, procedure1, procedure2); - - String rewriten = getRewritenProcedure(userUpdateStr, metadata); + String rewriten = getRewritenProcedure(userUpdateStr, metadata); + String expected = "CREATE PROCEDURE\nBEGIN\nDECLARE string var1 = 'x';\nUPDATE vm1.g2 SET e1 = var1;\nROWS_UPDATED = VARIABLES.ROWCOUNT;\nEND"; //$NON-NLS-1$ assertEquals(expected, rewriten); @@ -2000,16 +2004,16 @@ new Class[] { DataTypeManager.DefaultDataClasses.STRING}); } - private void verifyProjectedTypesOnUnionBranches(String unionQuery, Class<?>[] types) throws TeiidComponentException, TeiidProcessingException { + private void verifyProjectedTypesOnUnionBranches(String unionQuery, Class<?>[] types) throws TeiidComponentException, TeiidProcessingException { SetQuery union = (SetQuery)QueryParser.getQueryParser().parseCommand(unionQuery); QueryResolver.resolveCommand(union, RealMetadataFactory.example1Cached()); union = (SetQuery)QueryRewriter.rewrite(union, RealMetadataFactory.example1Cached(), null); for (QueryCommand query : union.getQueryCommands()) { - List<SingleElementSymbol> projSymbols = query.getProjectedSymbols(); + List<SingleElementSymbol> projSymbols = query.getProjectedSymbols(); for(int i=0; i<projSymbols.size(); i++) { - assertEquals("Found type mismatch at column " + i, types[i], projSymbols.get(i).getType()); //$NON-NLS-1$ + assertEquals("Found type mismatch at column " + i, types[i], projSymbols.get(i).getType()); //$NON-NLS-1$ } } } @@ -2057,7 +2061,7 @@ } @Test public void testRewriteConcat2() { - helpTestRewriteCriteria("sys.concat2('a','b') = 'ab'", "1 = 1"); //$NON-NLS-1$ //$NON-NLS-2$ + helpTestRewriteCriteria("sys.concat2('a','b') = 'ab'", "1 = 1"); //$NON-NLS-1$ //$NON-NLS-2$ } @Test public void testRewriteConcat2_1() { @@ -2076,400 +2080,400 @@ helpTestRewriteCriteria("concat2('a', pm1.g1.e1) = 'xyz'", "concat('a', ifnull(pm1.g1.e1, '')) = 'xyz'"); //$NON-NLS-1$ //$NON-NLS-2$ } - @Test public void testRewriteFromUnixTime() throws Exception { - TimestampWithTimezone.resetCalendar(TimeZone.getTimeZone("GMT-06:00")); //$NON-NLS-1$ - try { - helpTestRewriteCriteria("from_unixtime(pm1.g1.e2) = '1992-12-01 07:00:00'", "timestampadd(SQL_TSI_SECOND, pm1.g1.e2, {ts'1969-12-31 18:00:00.0'}) = {ts'1992-12-01 07:00:00.0'}"); //$NON-NLS-1$ //$NON-NLS-2$ - } finally { - TimestampWithTimezone.resetCalendar(null); - } - } - - @Test public void testRewriteNullIf() throws Exception { - helpTestRewriteCriteria("nullif(pm1.g1.e2, pm1.g1.e4) = 1", "CASE WHEN pm1.g1.e2 = pm1.g1.e4 THEN convert(null, double) ELSE pm1.g1.e2 END = 1.0", true); //$NON-NLS-1$ //$NON-NLS-2$ - } - - @Test public void testRewriteCoalesce() throws Exception { - helpTestRewriteCriteria("coalesce(convert(pm1.g1.e2, double), pm1.g1.e4) = 1", "ifnull(convert(pm1.g1.e2, double), pm1.g1.e4) = 1", true); //$NON-NLS-1$ //$NON-NLS-2$ - } - - /** - * Test <code>QueryRewriter</code>'s ability to rewrite a query that - * contains an aggregate function which uses a <code>CASE</code> - * expression which contains <code>BETWEEN</code> criteria as its value. - * <p> - * An aggregate function list is defined and queries are created that - * use each function from the list. The list includes: - * <p> - * "SUM", "MAX", "MIN", "AVG", "COUNT" - * <p> - * It is expected that the BETWEEN expression will be rewritten as - * <code>CompoundCriteria</code>. - * <p> - * <table> - * <tr><th align="left" colspan=2>For example: - * <tr><td width="10*"><td>SELECT SUM(CASE WHEN e2 BETWEEN 3 AND 5 - * THEN e2 ELSE -1 END) FROM pm1.g1 - * <tr><th align="left" colspan=2>Is rewritten as: - * <tr><td width="10*"><td>SELECT SUM(CASE WHEN (e2 >= 3) AND (e2 <= 5) - * THEN e2 ELSE -1 END) FROM pm1.g1 - * </table> - * - * @see org.teiid.query.rewriter.QueryRewriter - * @see org.teiid.query.sql.lang.BetweenCriteria - * @see org.teiid.query.sql.lang.CompoundCriteria - * @see org.teiid.query.sql.symbol.AggregateSymbol - * @see org.teiid.query.sql.symbol.SearchedCaseExpression - */ - @Test public void testAggregateWithBetweenInCaseInSelect() { - // Define a list of aggregates to test against - List<String> aggregateCommands = Arrays.asList( new String[] { "SUM", "MAX", "MIN", "AVG", "COUNT" } ); //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ //$NON-NLS-4$ //$NON-NLS-5$ - - // Define a query and the expected rewritten query - // ?AGGREGATE? represents the string substitution for an aggregate from aggregateCommands - String sqlBefore = "SELECT ?AGGREGATE?(CASE WHEN e2 BETWEEN 3 AND 5 THEN e2 ELSE -1 END) FROM pm1.g1"; //$NON-NLS-1$ - String sqlAfter = "SELECT ?AGGREGATE?(CASE WHEN (e2 >= 3) AND (e2 <= 5) THEN e2 ELSE -1 END) FROM pm1.g1"; //$NON-NLS-1$ - - // Iterate through the aggregateCommands - for ( String aCmd : aggregateCommands ) { - // Replace ?AGGREGATE? with the command from aggregateCommands - String sql = sqlBefore.replace("?AGGREGATE?", aCmd); //$NON-NLS-1$ - String exSql = sqlAfter.replace("?AGGREGATE?", aCmd); //$NON-NLS-1$ - // Test QueryRewriter - Command cmd = helpTestRewriteCommand( sql, exSql ); - // Check the rewritten command to verify that CompundCriteria replaced BetweenCriteria - CompoundCriteria ccrit = (CompoundCriteria) ((SearchedCaseExpression) ((ExpressionSymbol) cmd.getProjectedSymbols().get(0)).getExpression()).getWhen().get(0); - assertEquals( "e2 >= 3", ccrit.getCriteria(0).toString() ); //$NON-NLS-1$ - assertEquals( "e2 <= 5", ccrit.getCriteria(1).toString() ); //$NON-NLS-1$ - } - } - - /** - * Test <code>QueryRewriter</code>'s ability to rewrite a query that - * contains a <code>CASE</code> expression which contains - * <code>BETWEEN</code> criteria in the queries <code>SELECT</code> clause. - * <p> - * It is expected that the BETWEEN expression will be rewritten as - * <code>CompoundCriteria</code>. - * <p> - * <table> - * <tr><th align="left" colspan=2>For example: - * <tr><td width="10*"><td>SELECT CASE WHEN e2 BETWEEN 3 AND 5 THEN e2 - * ELSE -1 END FROM pm1.g1 - * <tr><th align="left" colspan=2>Is rewritten as: - * <tr><td width="10*"><td>SELECT CASE WHEN (e2 >= 3) AND (e2 <= 5) THEN e2 - * ELSE -1 END FROM pm1.g1 - * </table> - * - * @see org.teiid.query.rewriter.QueryRewriter - * @see org.teiid.query.sql.lang.BetweenCriteria - * @see org.teiid.query.sql.lang.CompoundCriteria - * @see org.teiid.query.sql.symbol.SearchedCaseExpression - */ - @Test public void testBetweenInCaseInSelect() { - String sqlBefore = "SELECT CASE WHEN e2 BETWEEN 3 AND 5 THEN e2 ELSE -1 END FROM pm1.g1"; //$NON-NLS-1$ - String sqlAfter = "SELECT CASE WHEN (e2 >= 3) AND (e2 <= 5) THEN e2 ELSE -1 END FROM pm1.g1"; //$NON-NLS-1$ - - Command cmd = helpTestRewriteCommand( sqlBefore, sqlAfter ); - CompoundCriteria ccrit = (CompoundCriteria) ((SearchedCaseExpression) ((ExpressionSymbol) cmd.getProjectedSymbols().get(0)).getExpression()).getWhen().get(0); - assertEquals( "e2 >= 3", ccrit.getCriteria(0).toString() ); //$NON-NLS-1$ - assertEquals( "e2 <= 5", ccrit.getCriteria(1).toString() ); //$NON-NLS-1$ - } - - /** - * Test <code>QueryRewriter</code>'s ability to rewrite a query that - * contains a <code>CASE</code> expression which contains - * <code>BETWEEN</code> criteria in the queries <code>WHERE</code> clause. - * <p> - * It is expected that the BETWEEN expression will be rewritten as - * <code>CompoundCriteria</code>. - * <p> - * <table> - * <tr><th align="left" colspan=2>For example: - * <tr><td width="10*"><td>SELECT * FROM pm1.g1 WHERE e3 = CASE WHEN e2 - * BETWEEN 3 AND 5 THEN e2 ELSE -1 END - * <tr><th align="left" colspan=2>Is rewritten as: - * <tr><td width="10*"><td>SELECT * FROM pm1.g1 WHERE e3 = CASE WHEN - * (e2 >= 3) AND (e2 <= 5) THEN e2 ELSE -1 END - * </table> - * - * @see org.teiid.query.rewriter.QueryRewriter - * @see org.teiid.query.sql.lang.BetweenCriteria - * @see org.teiid.query.sql.lang.CompoundCriteria - * @see org.teiid.query.sql.symbol.SearchedCaseExpression - */ - @Test public void testBetweenInCase() { - String sqlBefore = "SELECT * FROM pm1.g1 WHERE e3 = CASE WHEN e2 BETWEEN 3 AND 5 THEN e2 ELSE -1 END"; //$NON-NLS-1$ - String sqlAfter = "SELECT * FROM pm1.g1 WHERE e3 = CASE WHEN (e2 >= 3) AND (e2 <= 5) THEN e2 ELSE -1 END"; //$NON-NLS-1$ - - Command cmd = helpTestRewriteCommand( sqlBefore, sqlAfter ); - CompoundCriteria ccrit = (CompoundCriteria) ((SearchedCaseExpression) ((CompareCriteria) ((Query) cmd).getCriteria()).getRightExpression()).getWhen().get(0); - assertEquals( "e2 >= 3", ccrit.getCriteria(0).toString() ); //$NON-NLS-1$ - assertEquals( "e2 <= 5", ccrit.getCriteria(1).toString() ); //$NON-NLS-1$ - } - - @Test public void testRewriteNullHandling() { - String original = "pm1.g1.e1 like '%'"; //$NON-NLS-1$ - String expected = "pm1.g1.e1 is not null"; //$NON-NLS-1$ - addTestData(); - - helpTestRewriteCriteria(original, expected); - } - - private void addTestData() { - this.elements = new HashMap<ElementSymbol, Integer>(); - elements.put(new ElementSymbol("pm1.g1.e1"), 0); - elements.put(new ElementSymbol("pm1.g1.e2"), 1); - elements.put(new ElementSymbol("pm1.g1.e3"), 2); - for (String s : Arrays.asList("a", null, "*")) { - for (Integer i : Arrays.asList(1, null, 6)) { - for (Boolean b : Arrays.asList(true, false, null)) { - tuples.add(Arrays.asList(s, i, b)); - } - } - } - } - - @Test public void testRewriteNullHandling1() { - String original = "not(pm1.g1.e1 like '%' or pm1.g1.e1 = '1')"; //$NON-NLS-1$ - String expected = "1 = 0"; //$NON-NLS-1$ - addTestData(); - helpTestRewriteCriteria(original, expected); - } - - @Test public void testRewriteNullHandling2() { - String original = "not(pm1.g1.e1 like '%' and pm1.g1.e1 = '1')"; //$NON-NLS-1$ - String expected = "pm1.g1.e1 <> '1'"; //$NON-NLS-1$ - addTestData(); - helpTestRewriteCriteria(original, expected); - } - - @Test public void testRewriteNullHandling3() { - String original = "pm1.g1.e1 like '%' or pm1.g1.e1 = '1'"; //$NON-NLS-1$ - String expected = "(pm1.g1.e1 IS NOT NULL) OR (pm1.g1.e1 = '1')"; //$NON-NLS-1$ - addTestData(); - helpTestRewriteCriteria(original, expected); - } - - @Test public void testRewriteNullHandling4() { - String original = "not((pm1.g1.e1 like '%' or pm1.g1.e3 = true) and pm1.g1.e2 < 5)"; //$NON-NLS-1$ - String expected = "pm1.g1.e2 >= 5"; //$NON-NLS-1$ - addTestData(); - helpTestRewriteCriteria(original, expected); - } - - @Test public void testRewriteNullHandling4a() { - String original = "not(not((pm1.g1.e1 like '%' or pm1.g1.e3 = true) and pm1.g1.e2 < 5))"; //$NON-NLS-1$ - String expected = "((pm1.g1.e1 IS NOT NULL) OR (pm1.g1.e3 = TRUE)) AND (pm1.g1.e2 < 5)"; //$NON-NLS-1$ - addTestData(); - helpTestRewriteCriteria(original, expected); - } - - @Test public void testRewriteNullHandling5() { - String original = "not((pm1.g1.e1 not like '%' or pm1.g1.e3 = true) and pm1.g1.e2 < 5)"; //$NON-NLS-1$ - String expected = "((pm1.g1.e1 IS NOT NULL) AND (pm1.g1.e3 <> TRUE)) OR (pm1.g1.e2 >= 5)"; //$NON-NLS-1$ - addTestData(); - helpTestRewriteCriteria(original, expected); - } - - @Test public void testRewriteNullHandling6() { - String original = "not((pm1.g1.e1 not like '%' and pm1.g1.e3 = true) or pm1.g1.e2 < 5)"; //$NON-NLS-1$ - String expected = "((pm1.g1.e1 IS NOT NULL) OR (pm1.g1.e3 <> TRUE)) AND (pm1.g1.e2 >= 5)"; //$NON-NLS-1$ - addTestData(); - helpTestRewriteCriteria(original, expected); - } - - @Test public void testRewriteNullHandling7() { - String original = "not(not(pm1.g1.e1 not like '%' and pm1.g1.e3 = true) or pm1.g1.e2 < 5)"; //$NON-NLS-1$ - String expected = "1 = 0"; //$NON-NLS-1$ - addTestData(); - helpTestRewriteCriteria(original, expected); - } - - @Test public void testRewriteNullHandling7a() { - String original = "not(not(pm1.g1.e1 like '*%' and pm1.g1.e3 = true) or pm1.g1.e2 < 5)"; //$NON-NLS-1$ - String expected = "(pm1.g1.e1 LIKE '*%') AND (pm1.g1.e3 = TRUE) AND (pm1.g1.e2 >= 5)"; //$NON-NLS-1$ - addTestData(); - helpTestRewriteCriteria(original, expected); - } - - @Test public void testRewriteChar() { - String original = "convert(pm1.g1.e1, char) = '100'"; //$NON-NLS-1$ - String expected = "1 = 0"; //$NON-NLS-1$ - - helpTestRewriteCriteria(original, expected); - } - - /** - * Test ensures that '22.0' is a valid long via bigdecimal - */ - @Test public void testRewriteBigDecimal() { - String original = "convert(BQT1.SmallA.LongNum, bigdecimal) = '22.0'"; //$NON-NLS-1$ - CompareCriteria crit = new CompareCriteria(new ElementSymbol("BQT1.SmallA.LongNum"), CompareCriteria.EQ, new Constant(new Long(22))); //$NON-NLS-1$ - helpTestRewriteCriteria(original, crit, RealMetadataFactory.exampleBQTCached()); - } - - /** - * Test ensures that we will not attempt to invert the widening conversion - */ - @Test public void testRewriteWideningIn() { - String original = "convert(BQT1.SmallA.TimestampValue, time) in ({t'10:00:00'}, {t'11:00:00'})"; //$NON-NLS-1$ - helpTestRewriteCriteria(original, parseCriteria("convert(BQT1.SmallA.TimestampValue, time) in ({t'10:00:00'}, {t'11:00:00'})", RealMetadataFactory.exampleBQTCached()), RealMetadataFactory.exampleBQTCached()); //$NON-NLS-1$ - } - - @Test public void testRewriteParseDate() { - String original = "parsedate(BQT1.SmallA.stringkey, 'yymmdd') = {d'1970-01-01'}"; //$NON-NLS-1$ - QueryMetadataInterface metadata = RealMetadataFactory.exampleBQTCached(); - helpTestRewriteCriteria(original, parseCriteria("convert(parsetimestamp(BQT1.SmallA.stringkey, 'yymmdd'), date) = {d'1970-01-01'}", metadata), metadata); //$NON-NLS-1$ - } - - @Test public void testRewriteFormatTime() { - String original = "formattime(BQT1.SmallA.timevalue, 'hh:mm') = '08:02'"; //$NON-NLS-1$ - QueryMetadataInterface metadata = RealMetadataFactory.exampleBQTCached(); - helpTestRewriteCriteria(original, parseCriteria("formattimestamp(convert(BQT1.SmallA.timevalue, timestamp), 'hh:mm') = '08:02'", metadata), metadata); //$NON-NLS-1$ - } - - @Test public void testRewriteTimestampAdd() { - String original = "timestampadd(SQL_TSI_SECOND, 1, BQT1.SmallA.timevalue) = {t'08:02:00'}"; //$NON-NLS-1$ - QueryMetadataInterface metadata = RealMetadataFactory.exampleBQTCached(); - helpTestRewriteCriteria(original, parseCriteria("convert(timestampadd(SQL_TSI_SECOND, 1, convert(BQT1.SmallA.timevalue, timestamp)), time) = {t'08:02:00'}", metadata), metadata); //$NON-NLS-1$ - } - - @Test public void testRewriteXmlElement() throws Exception { - String original = "xmlserialize(document xmlelement(name a, xmlattributes('b' as c)) as string)"; //$NON-NLS-1$ - QueryMetadataInterface metadata = RealMetadataFactory.exampleBQTCached(); - helpTestRewriteExpression(original, "'<a c=\"b\"></a>'", metadata); - } - - @Test public void testRewriteXmlElement1() throws Exception { - String original = "xmlelement(name a, xmlattributes(1+1 as c), BQT1.SmallA.timevalue)"; //$NON-NLS-1$ - QueryMetadataInterface metadata = RealMetadataFactory.exampleBQTCached(); - helpTestRewriteExpression(original, "XMLELEMENT(NAME a, XMLATTRIBUTES(2 AS c), BQT1.SmallA.timevalue)", metadata); - } - - @Test public void testRewriteXmlSerialize() throws Exception { - String original = "xmlserialize(document xmlelement(name a, xmlattributes('b' as c)) as string)"; //$NON-NLS-1$ - QueryMetadataInterface metadata = RealMetadataFactory.exampleBQTCached(); - helpTestRewriteExpression(original, "'<a c=\"b\"></a>'", metadata); - } - - @Test public void testRewriteXmlTable() throws Exception { - String original = "select * from xmltable('/' passing 1 + 1 as a columns x string default curdate()) as x"; //$NON-NLS-1$ - QueryMetadataInterface metadata = RealMetadataFactory.exampleBQTCached(); - helpTestRewriteCommand(original, "SELECT * FROM XMLTABLE('/' PASSING 2 AS a COLUMNS x string DEFAULT curdate()) AS x", metadata); - } - - @Test public void testRewriteQueryString() throws Exception { - String original = "querystring('path', 'value' as \"&x\", ' & ' as y, null as z)"; //$NON-NLS-1$ - QueryMetadataInterface metadata = RealMetadataFactory.exampleBQTCached(); - helpTestRewriteExpression(original, "'path?%26x=value&y=%20%26%20'", metadata); - } - - @Test public void testRewriteExpressionCriteria() throws Exception { - helpTestRewriteCriteria("pm1.g1.e3", "pm1.g1.e3 = true"); - } - - @Test public void testRewritePredicateOptimization() throws Exception { - helpTestRewriteCriteria("pm1.g1.e2 in (1, 2, 3) and pm1.g1.e2 in (2, 3, 4)", "pm1.g1.e2 in (2, 3)"); - } - - @Test public void testRewritePredicateOptimization1() throws Exception { - helpTestRewriteCriteria("pm1.g1.e2 < 5 and pm1.g1.e2 = 2", "pm1.g1.e2 = 2"); - } - - @Test public void testRewritePredicateOptimization2() throws Exception { - helpTestRewriteCriteria("pm1.g1.e2 < 5 and pm1.g1.e2 = 6", "1 = 0"); - } - - @Test public void testRewritePredicateOptimization2a() throws Exception { - helpTestRewriteCriteria("pm1.g1.e2 < 5 and pm1.g1.e2 = 2", "pm1.g1.e2 = 2"); - } - - @Test public void testRewritePredicateOptimization3() throws Exception { - helpTestRewriteCriteria("pm1.g1.e2 in (1, 2) and pm1.g1.e2 = 6", "1 = 0"); - } - - @Test public void testRewritePredicateOptimization4() throws Exception { - helpTestRewriteCriteria("pm1.g1.e2 in (1, 2) and pm1.g1.e2 is null", "1 = 0"); - } - - @Test public void testRewritePredicateOptimization5() throws Exception { - helpTestRewriteCriteria("pm1.g1.e2 <> 5 and pm1.g1.e2 in (2, 3, 5)", "pm1.g1.e2 in (2, 3)"); - } - - @Test public void testRewritePredicateOptimization6() throws Exception { - helpTestRewriteCriteria("pm1.g1.e2 = 5 and pm1.g1.e2 in (5, 6)", "pm1.g1.e2 = 5"); - } - - @Test public void testRewritePredicateOptimization6a() throws Exception { - helpTestRewriteCriteria("pm1.g1.e2 in (5, 6) and pm1.g1.e2 = 5", "pm1.g1.e2 = 5"); - } - - @Ignore("TODO") - @Test public void testRewritePredicateOptimization7() throws Exception { - helpTestRewriteCriteria("pm1.g1.e2 > 5 and pm1.g1.e2 < 2", "1 = 0"); - } - - @Test public void testRewritePredicateOptimization8() throws Exception { - helpTestRewriteCriteria("pm1.g1.e2 = 2 and pm1.g1.e2 > 1", "pm1.g1.e2 = 2"); - } - - @Test public void testRewritePredicateOptimization8a() throws Exception { - helpTestRewriteCriteria("pm1.g1.e2 in (0, 2) and pm1.g1.e2 > 1", "pm1.g1.e2 = 2"); - } - - @Test public void testRewritePredicateOptimization9() throws Exception { - helpTestRewriteCriteria("not(pm1.g1.e2 = 2 and pm1.g1.e2 = 3)", "(pm1.g1.e2 <> 2) OR (pm1.g1.e2 <> 3)"); - } - - @Test public void testRewritePredicateOptimizationOr() throws Exception { - helpTestRewriteCriteria("pm1.g1.e2 in (5, 6) or pm1.g1.e2 = 2", "pm1.g1.e2 IN (2, 5, 6)"); - } - - @Test public void testRewriteCritSubqueryNegate() { - helpTestRewriteCriteria("not(pm1.g1.e1 > SOME (select 'a' from pm1.g2))", "pm1.g1.e1 <= ALL (SELECT 'a' FROM pm1.g2)"); //$NON-NLS-1$ //$NON-NLS-2$ - } - - @Test public void testRewriteCritSubqueryFalse() { - helpTestRewriteCriteria("exists(select 1 from pm1.g1 where 1=0)", "1 = 0"); //$NON-NLS-1$ //$NON-NLS-2$ - } - - @Test public void testRewriteCritSubqueryFalse1() { - helpTestRewriteCriteria("not(pm1.g1.e1 > SOME (select 'a' from pm1.g1 where 1=0))", "pm1.g1.e1 IS NOT NULL"); //$NON-NLS-1$ //$NON-NLS-2$ - } - - @Test public void testRewriteCritSubqueryFalse2() { - helpTestRewriteCriteria("pm1.g1.e1 < ALL (select 'a' from pm1.g1 where 1=0)", "pm1.g1.e1 IS NOT NULL"); //$NON-NLS-1$ //$NON-NLS-2$ - } - - @Test public void testUDFParse() throws Exception { - QueryMetadataInterface metadata = RealMetadataFactory.createTransformationMetadata(RealMetadataFactory.example1Cached().getMetadataStore(), "example1", new FunctionTree("foo", new FakeFunctionMetadataSource())); - String sql = "parsedate_(pm1.g1.e1) = {d'2001-01-01'}"; - helpTestRewriteCriteria(sql, parseCriteria(sql, metadata), metadata); - } - - @Test public void testRewriteNestedConvert() throws Exception { - helpTestRewriteExpression("cast(cast(pm1.g1.e3 as integer) as long)", "cast(pm1.g1.e3 as long)", RealMetadataFactory.example1Cached()); //$NON-NLS-1$ //$NON-NLS-2$ - } - - @Test public void testRewriteNestedConvert1() throws Exception { - helpTestRewriteExpression("cast(cast(pm1.g1.e3 as integer) as string)", "convert(convert(pm1.g1.e3, integer), string)", RealMetadataFactory.example1Cached()); //$NON-NLS-1$ //$NON-NLS-2$ - } - - @Test public void testRewriteNestedConvert2() throws Exception { - helpTestRewriteExpression("cast(cast(pm1.g1.e3 as string) as clob)", "convert(convert(pm1.g1.e3, string), clob)", RealMetadataFactory.example1Cached()); //$NON-NLS-1$ //$NON-NLS-2$ - } - - @Test public void testRewriteConstantAgg() throws Exception { - helpTestRewriteCommand("select max(1) from pm1.g1 group by e1", "SELECT 1 FROM pm1.g1 GROUP BY e1"); - } - - @Test public void testRewriteTrim() throws Exception { - helpTestRewriteExpression("trim(pm1.g1.e1)", "rtrim(ltrim(pm1.g1.e1))", RealMetadataFactory.example1Cached()); - } - - @Test public void testRewriteTrim1() throws Exception { - helpTestRewriteExpression("trim(leading from pm1.g1.e1)", "ltrim(pm1.g1.e1)", RealMetadataFactory.example1Cached()); - } - + @Test public void testRewriteFromUnixTime() throws Exception { + TimestampWithTimezone.resetCalendar(TimeZone.getTimeZone("GMT-06:00")); //$NON-NLS-1$ + try { + helpTestRewriteCriteria("from_unixtime(pm1.g1.e2) = '1992-12-01 07:00:00'", "timestampadd(SQL_TSI_SECOND, pm1.g1.e2, {ts'1969-12-31 18:00:00.0'}) = {ts'1992-12-01 07:00:00.0'}"); //$NON-NLS-1$ //$NON-NLS-2$ + } finally { + TimestampWithTimezone.resetCalendar(null); + } + } + + @Test public void testRewriteNullIf() throws Exception { + helpTestRewriteCriteria("nullif(pm1.g1.e2, pm1.g1.e4) = 1", "CASE WHEN pm1.g1.e2 = pm1.g1.e4 THEN convert(null, double) ELSE pm1.g1.e2 END = 1.0", true); //$NON-NLS-1$ //$NON-NLS-2$ + } + + @Test public void testRewriteCoalesce() throws Exception { + helpTestRewriteCriteria("coalesce(convert(pm1.g1.e2, double), pm1.g1.e4) = 1", "ifnull(convert(pm1.g1.e2, double), pm1.g1.e4) = 1", true); //$NON-NLS-1$ //$NON-NLS-2$ + } + + /** + * Test <code>QueryRewriter</code>'s ability to rewrite a query that + * contains an aggregate function which uses a <code>CASE</code> + * expression which contains <code>BETWEEN</code> criteria as its value. + * <p> + * An aggregate function list is defined and queries are created that + * use each function from the list. The list includes: + * <p> + * "SUM", "MAX", "MIN", "AVG", "COUNT" + * <p> + * It is expected that the BETWEEN expression will be rewritten as + * <code>CompoundCriteria</code>. + * <p> + * <table> + * <tr><th align="left" colspan=2>For example: + * <tr><td width="10*"><td>SELECT SUM(CASE WHEN e2 BETWEEN 3 AND 5 + * THEN e2 ELSE -1 END) FROM pm1.g1 + * <tr><th align="left" colspan=2>Is rewritten as: + * <tr><td width="10*"><td>SELECT SUM(CASE WHEN (e2 >= 3) AND (e2 <= 5) + * THEN e2 ELSE -1 END) FROM pm1.g1 + * </table> + * + * @see org.teiid.query.rewriter.QueryRewriter + * @see org.teiid.query.sql.lang.BetweenCriteria + * @see org.teiid.query.sql.lang.CompoundCriteria + * @see org.teiid.query.sql.symbol.AggregateSymbol + * @see org.teiid.query.sql.symbol.SearchedCaseExpression + */ + @Test public void testAggregateWithBetweenInCaseInSelect() { + // Define a list of aggregates to test against + List<String> aggregateCommands = Arrays.asList( new String[] { "SUM", "MAX", "MIN", "AVG", "COUNT" } ); //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ //$NON-NLS-4$ //$NON-NLS-5$ + + // Define a query and the expected rewritten query + // ?AGGREGATE? represents the string substitution for an aggregate from aggregateCommands + String sqlBefore = "SELECT ?AGGREGATE?(CASE WHEN e2 BETWEEN 3 AND 5 THEN e2 ELSE -1 END) FROM pm1.g1"; //$NON-NLS-1$ + String sqlAfter = "SELECT ?AGGREGATE?(CASE WHEN (e2 >= 3) AND (e2 <= 5) THEN e2 ELSE -1 END) FROM pm1.g1"; //$NON-NLS-1$ + + // Iterate through the aggregateCommands + for ( String aCmd : aggregateCommands ) { + // Replace ?AGGREGATE? with the command from aggregateCommands + String sql = sqlBefore.replace("?AGGREGATE?", aCmd); //$NON-NLS-1$ + String exSql = sqlAfter.replace("?AGGREGATE?", aCmd); //$NON-NLS-1$ + // Test QueryRewriter + Command cmd = helpTestRewriteCommand( sql, exSql ); + // Check the rewritten command to verify that CompundCriteria replaced BetweenCriteria + CompoundCriteria ccrit = (CompoundCriteria) ((SearchedCaseExpression) ((ExpressionSymbol) cmd.getProjectedSymbols().get(0)).getExpression()).getWhen().get(0); + assertEquals( "e2 >= 3", ccrit.getCriteria(0).toString() ); //$NON-NLS-1$ + assertEquals( "e2 <= 5", ccrit.getCriteria(1).toString() ); //$NON-NLS-1$ + } + } + + /** + * Test <code>QueryRewriter</code>'s ability to rewrite a query that + * contains a <code>CASE</code> expression which contains + * <code>BETWEEN</code> criteria in the queries <code>SELECT</code> clause. + * <p> + * It is expected that the BETWEEN expression will be rewritten as + * <code>CompoundCriteria</code>. + * <p> + * <table> + * <tr><th align="left" colspan=2>For example: + * <tr><td width="10*"><td>SELECT CASE WHEN e2 BETWEEN 3 AND 5 THEN e2 + * ELSE -1 END FROM pm1.g1 + * <tr><th align="left" colspan=2>Is rewritten as: + * <tr><td width="10*"><td>SELECT CASE WHEN (e2 >= 3) AND (e2 <= 5) THEN e2 + * ELSE -1 END FROM pm1.g1 + * </table> + * + * @see org.teiid.query.rewriter.QueryRewriter + * @see org.teiid.query.sql.lang.BetweenCriteria + * @see org.teiid.query.sql.lang.CompoundCriteria + * @see org.teiid.query.sql.symbol.SearchedCaseExpression + */ + @Test public void testBetweenInCaseInSelect() { + String sqlBefore = "SELECT CASE WHEN e2 BETWEEN 3 AND 5 THEN e2 ELSE -1 END FROM pm1.g1"; //$NON-NLS-1$ + String sqlAfter = "SELECT CASE WHEN (e2 >= 3) AND (e2 <= 5) THEN e2 ELSE -1 END FROM pm1.g1"; //$NON-NLS-1$ + + Command cmd = helpTestRewriteCommand( sqlBefore, sqlAfter ); + CompoundCriteria ccrit = (CompoundCriteria) ((SearchedCaseExpression) ((ExpressionSymbol) cmd.getProjectedSymbols().get(0)).getExpression()).getWhen().get(0); + assertEquals( "e2 >= 3", ccrit.getCriteria(0).toString() ); //$NON-NLS-1$ + assertEquals( "e2 <= 5", ccrit.getCriteria(1).toString() ); //$NON-NLS-1$ + } + + /** + * Test <code>QueryRewriter</code>'s ability to rewrite a query that + * contains a <code>CASE</code> expression which contains + * <code>BETWEEN</code> criteria in the queries <code>WHERE</code> clause. + * <p> + * It is expected that the BETWEEN expression will be rewritten as + * <code>CompoundCriteria</code>. + * <p> + * <table> + * <tr><th align="left" colspan=2>For example: + * <tr><td width="10*"><td>SELECT * FROM pm1.g1 WHERE e3 = CASE WHEN e2 + * BETWEEN 3 AND 5 THEN e2 ELSE -1 END + * <tr><th align="left" colspan=2>Is rewritten as: + * <tr><td width="10*"><td>SELECT * FROM pm1.g1 WHERE e3 = CASE WHEN + * (e2 >= 3) AND (e2 <= 5) THEN e2 ELSE -1 END + * </table> + * + * @see org.teiid.query.rewriter.QueryRewriter + * @see org.teiid.query.sql.lang.BetweenCriteria + * @see org.teiid.query.sql.lang.CompoundCriteria + * @see org.teiid.query.sql.symbol.SearchedCaseExpression + */ + @Test public void testBetweenInCase() { + String sqlBefore = "SELECT * FROM pm1.g1 WHERE e3 = CASE WHEN e2 BETWEEN 3 AND 5 THEN e2 ELSE -1 END"; //$NON-NLS-1$ + String sqlAfter = "SELECT * FROM pm1.g1 WHERE e3 = CASE WHEN (e2 >= 3) AND (e2 <= 5) THEN e2 ELSE -1 END"; //$NON-NLS-1$ + + Command cmd = helpTestRewriteCommand( sqlBefore, sqlAfter ); + CompoundCriteria ccrit = (CompoundCriteria) ((SearchedCaseExpression) ((CompareCriteria) ((Query) cmd).getCriteria()).getRightExpression()).getWhen().get(0); + assertEquals( "e2 >= 3", ccrit.getCriteria(0).toString() ); //$NON-NLS-1$ + assertEquals( "e2 <= 5", ccrit.getCriteria(1).toString() ); //$NON-NLS-1$ + } + + @Test public void testRewriteNullHandling() { + String original = "pm1.g1.e1 like '%'"; //$NON-NLS-1$ + String expected = "pm1.g1.e1 is not null"; //$NON-NLS-1$ + addTestData(); + + helpTestRewriteCriteria(original, expected); + } + + private void addTestData() { + this.elements = new HashMap<ElementSymbol, Integer>(); + elements.put(new ElementSymbol("pm1.g1.e1"), 0); + elements.put(new ElementSymbol("pm1.g1.e2"), 1); + elements.put(new ElementSymbol("pm1.g1.e3"), 2); + for (String s : Arrays.asList("a", null, "*")) { + for (Integer i : Arrays.asList(1, null, 6)) { + for (Boolean b : Arrays.asList(true, false, null)) { + tuples.add(Arrays.asList(s, i, b)); + } + } + } + } + + @Test public void testRewriteNullHandling1() { + String original = "not(pm1.g1.e1 like '%' or pm1.g1.e1 = '1')"; //$NON-NLS-1$ + String expected = "1 = 0"; //$NON-NLS-1$ + addTestData(); + helpTestRewriteCriteria(original, expected); + } + + @Test public void testRewriteNullHandling2() { + String original = "not(pm1.g1.e1 like '%' and pm1.g1.e1 = '1')"; //$NON-NLS-1$ + String expected = "pm1.g1.e1 <> '1'"; //$NON-NLS-1$ + addTestData(); + helpTestRewriteCriteria(original, expected); + } + + @Test public void testRewriteNullHandling3() { + String original = "pm1.g1.e1 like '%' or pm1.g1.e1 = '1'"; //$NON-NLS-1$ + String expected = "(pm1.g1.e1 IS NOT NULL) OR (pm1.g1.e1 = '1')"; //$NON-NLS-1$ + addTestData(); + helpTestRewriteCriteria(original, expected); + } + + @Test public void testRewriteNullHandling4() { + String original = "not((pm1.g1.e1 like '%' or pm1.g1.e3 = true) and pm1.g1.e2 < 5)"; //$NON-NLS-1$ + String expected = "pm1.g1.e2 >= 5"; //$NON-NLS-1$ + addTestData(); + helpTestRewriteCriteria(original, expected); + } + + @Test public void testRewriteNullHandling4a() { + String original = "not(not((pm1.g1.e1 like '%' or pm1.g1.e3 = true) and pm1.g1.e2 < 5))"; //$NON-NLS-1$ + String expected = "((pm1.g1.e1 IS NOT NULL) OR (pm1.g1.e3 = TRUE)) AND (pm1.g1.e2 < 5)"; //$NON-NLS-1$ + addTestData(); + helpTestRewriteCriteria(original, expected); + } + + @Test public void testRewriteNullHandling5() { + String original = "not((pm1.g1.e1 not like '%' or pm1.g1.e3 = true) and pm1.g1.e2 < 5)"; //$NON-NLS-1$ + String expected = "((pm1.g1.e1 IS NOT NULL) AND (pm1.g1.e3 <> TRUE)) OR (pm1.g1.e2 >= 5)"; //$NON-NLS-1$ + addTestData(); + helpTestRewriteCriteria(original, expected); + } + + @Test public void testRewriteNullHandling6() { + String original = "not((pm1.g1.e1 not like '%' and pm1.g1.e3 = true) or pm1.g1.e2 < 5)"; //$NON-NLS-1$ + String expected = "((pm1.g1.e1 IS NOT NULL) OR (pm1.g1.e3 <> TRUE)) AND (pm1.g1.e2 >= 5)"; //$NON-NLS-1$ + addTestData(); + helpTestRewriteCriteria(original, expected); + } + + @Test public void testRewriteNullHandling7() { + String original = "not(not(pm1.g1.e1 not like '%' and pm1.g1.e3 = true) or pm1.g1.e2 < 5)"; //$NON-NLS-1$ + String expected = "1 = 0"; //$NON-NLS-1$ + addTestData(); + helpTestRewriteCriteria(original, expected); + } + + @Test public void testRewriteNullHandling7a() { + String original = "not(not(pm1.g1.e1 like '*%' and pm1.g1.e3 = true) or pm1.g1.e2 < 5)"; //$NON-NLS-1$ + String expected = "(pm1.g1.e1 LIKE '*%') AND (pm1.g1.e3 = TRUE) AND (pm1.g1.e2 >= 5)"; //$NON-NLS-1$ + addTestData(); + helpTestRewriteCriteria(original, expected); + } + + @Test public void testRewriteChar() { + String original = "convert(pm1.g1.e1, char) = '100'"; //$NON-NLS-1$ + String expected = "1 = 0"; //$NON-NLS-1$ + + helpTestRewriteCriteria(original, expected); + } + + /** + * Test ensures that '22.0' is a valid long via bigdecimal + */ + @Test public void testRewriteBigDecimal() { + String original = "convert(BQT1.SmallA.LongNum, bigdecimal) = '22.0'"; //$NON-NLS-1$ + CompareCriteria crit = new CompareCriteria(new ElementSymbol("BQT1.SmallA.LongNum"), CompareCriteria.EQ, new Constant(new Long(22))); //$NON-NLS-1$ + helpTestRewriteCriteria(original, crit, RealMetadataFactory.exampleBQTCached()); + } + + /** + * Test ensures that we will not attempt to invert the widening conversion + */ + @Test public void testRewriteWideningIn() { + String original = "convert(BQT1.SmallA.TimestampValue, time) in ({t'10:00:00'}, {t'11:00:00'})"; //$NON-NLS-1$ + helpTestRewriteCriteria(original, parseCriteria("convert(BQT1.SmallA.TimestampValue, time) in ({t'10:00:00'}, {t'11:00:00'})", RealMetadataFactory.exampleBQTCached()), RealMetadataFactory.exampleBQTCached()); //$NON-NLS-1$ + } + + @Test public void testRewriteParseDate() { + String original = "parsedate(BQT1.SmallA.stringkey, 'yymmdd') = {d'1970-01-01'}"; //$NON-NLS-1$ + QueryMetadataInterface metadata = RealMetadataFactory.exampleBQTCached(); + helpTestRewriteCriteria(original, parseCriteria("convert(parsetimestamp(BQT1.SmallA.stringkey, 'yymmdd'), date) = {d'1970-01-01'}", metadata), metadata); //$NON-NLS-1$ + } + + @Test public void testRewriteFormatTime() { + String original = "formattime(BQT1.SmallA.timevalue, 'hh:mm') = '08:02'"; //$NON-NLS-1$ + QueryMetadataInterface metadata = RealMetadataFactory.exampleBQTCached(); + helpTestRewriteCriteria(original, parseCriteria("formattimestamp(convert(BQT1.SmallA.timevalue, timestamp), 'hh:mm') = '08:02'", metadata), metadata); //$NON-NLS-1$ + } + + @Test public void testRewriteTimestampAdd() { + String original = "timestampadd(SQL_TSI_SECOND, 1, BQT1.SmallA.timevalue) = {t'08:02:00'}"; //$NON-NLS-1$ + QueryMetadataInterface metadata = RealMetadataFactory.exampleBQTCached(); + helpTestRewriteCriteria(original, parseCriteria("convert(timestampadd(SQL_TSI_SECOND, 1, convert(BQT1.SmallA.timevalue, timestamp)), time) = {t'08:02:00'}", metadata), metadata); //$NON-NLS-1$ + } + + @Test public void testRewriteXmlElement() throws Exception { + String original = "xmlserialize(document xmlelement(name a, xmlattributes('b' as c)) as string)"; //$NON-NLS-1$ + QueryMetadataInterface metadata = RealMetadataFactory.exampleBQTCached(); + helpTestRewriteExpression(original, "'<a c=\"b\"></a>'", metadata); + } + + @Test public void testRewriteXmlElement1() throws Exception { + String original = "xmlelement(name a, xmlattributes(1+1 as c), BQT1.SmallA.timevalue)"; //$NON-NLS-1$ + QueryMetadataInterface metadata = RealMetadataFactory.exampleBQTCached(); + helpTestRewriteExpression(original, "XMLELEMENT(NAME a, XMLATTRIBUTES(2 AS c), BQT1.SmallA.timevalue)", metadata); + } + + @Test public void testRewriteXmlSerialize() throws Exception { + String original = "xmlserialize(document xmlelement(name a, xmlattributes('b' as c)) as string)"; //$NON-NLS-1$ + QueryMetadataInterface metadata = RealMetadataFactory.exampleBQTCached(); + helpTestRewriteExpression(original, "'<a c=\"b\"></a>'", metadata); + } + + @Test public void testRewriteXmlTable() throws Exception { + String original = "select * from xmltable('/' passing 1 + 1 as a columns x string default curdate()) as x"; //$NON-NLS-1$ + QueryMetadataInterface metadata = RealMetadataFactory.exampleBQTCached(); + helpTestRewriteCommand(original, "SELECT * FROM XMLTABLE('/' PASSING 2 AS a COLUMNS x string DEFAULT curdate()) AS x", metadata); + } + + @Test public void testRewriteQueryString() throws Exception { + String original = "querystring('path', 'value' as \"&x\", ' & ' as y, null as z)"; //$NON-NLS-1$ + QueryMetadataInterface metadata = RealMetadataFactory.exampleBQTCached(); + helpTestRewriteExpression(original, "'path?%26x=value&y=%20%26%20'", metadata); + } + + @Test public void testRewriteExpressionCriteria() throws Exception { + helpTestRewriteCriteria("pm1.g1.e3", "pm1.g1.e3 = true"); + } + + @Test public void testRewritePredicateOptimization() throws Exception { + helpTestRewriteCriteria("pm1.g1.e2 in (1, 2, 3) and pm1.g1.e2 in (2, 3, 4)", "pm1.g1.e2 in (2, 3)"); + } + + @Test public void testRewritePredicateOptimization1() throws Exception { + helpTestRewriteCriteria("pm1.g1.e2 < 5 and pm1.g1.e2 = 2", "pm1.g1.e2 = 2"); + } + + @Test public void testRewritePredicateOptimization2() throws Exception { + helpTestRewriteCriteria("pm1.g1.e2 < 5 and pm1.g1.e2 = 6", "1 = 0"); + } + + @Test public void testRewritePredicateOptimization2a() throws Exception { + helpTestRewriteCriteria("pm1.g1.e2 < 5 and pm1.g1.e2 = 2", "pm1.g1.e2 = 2"); + } + + @Test public void testRewritePredicateOptimization3() throws Exception { + helpTestRewriteCriteria("pm1.g1.e2 in (1, 2) and pm1.g1.e2 = 6", "1 = 0"); + } + + @Test public void testRewritePredicateOptimization4() throws Exception { + helpTestRewriteCriteria("pm1.g1.e2 in (1, 2) and pm1.g1.e2 is null", "1 = 0"); + } + + @Test public void testRewritePredicateOptimization5() throws Exception { + helpTestRewriteCriteria("pm1.g1.e2 <> 5 and pm1.g1.e2 in (2, 3, 5)", "pm1.g1.e2 in (2, 3)"); + } + + @Test public void testRewritePredicateOptimization6() throws Exception { + helpTestRewriteCriteria("pm1.g1.e2 = 5 and pm1.g1.e2 in (5, 6)", "pm1.g1.e2 = 5"); + } + + @Test public void testRewritePredicateOptimization6a() throws Exception { + helpTestRewriteCriteria("pm1.g1.e2 in (5, 6) and pm1.g1.e2 = 5", "pm1.g1.e2 = 5"); + } + + @Ignore("TODO") + @Test public void testRewritePredicateOptimization7() throws Exception { + helpTestRewriteCriteria("pm1.g1.e2 > 5 and pm1.g1.e2 < 2", "1 = 0"); + } + + @Test public void testRewritePredicateOptimization8() throws Exception { + helpTestRewriteCriteria("pm1.g1.e2 = 2 and pm1.g1.e2 > 1", "pm1.g1.e2 = 2"); + } + + @Test public void testRewritePredicateOptimization8a() throws Exception { + helpTestRewriteCriteria("pm1.g1.e2 in (0, 2) and pm1.g1.e2 > 1", "pm1.g1.e2 = 2"); + } + + @Test public void testRewritePredicateOptimization9() throws Exception { + helpTestRewriteCriteria("not(pm1.g1.e2 = 2 and pm1.g1.e2 = 3)", "(pm1.g1.e2 <> 2) OR (pm1.g1.e2 <> 3)"); + } + + @Test public void testRewritePredicateOptimizationOr() throws Exception { + helpTestRewriteCriteria("pm1.g1.e2 in (5, 6) or pm1.g1.e2 = 2", "pm1.g1.e2 IN (2, 5, 6)"); + } + + @Test public void testRewriteCritSubqueryNegate() { + helpTestRewriteCriteria("not(pm1.g1.e1 > SOME (select 'a' from pm1.g2))", "pm1.g1.e1 <= ALL (SELECT 'a' FROM pm1.g2)"); //$NON-NLS-1$ //$NON-NLS-2$ + } + + @Test public void testRewriteCritSubqueryFalse() { + helpTestRewriteCriteria("exists(select 1 from pm1.g1 where 1=0)", "1 = 0"); //$NON-NLS-1$ //$NON-NLS-2$ + } + + @Test public void testRewriteCritSubqueryFalse1() { + helpTestRewriteCriteria("not(pm1.g1.e1 > SOME (select 'a' from pm1.g1 where 1=0))", "pm1.g1.e1 IS NOT NULL"); //$NON-NLS-1$ //$NON-NLS-2$ + } + + @Test public void testRewriteCritSubqueryFalse2() { + helpTestRewriteCriteria("pm1.g1.e1 < ALL (select 'a' from pm1.g1 where 1=0)", "pm1.g1.e1 IS NOT NULL"); //$NON-NLS-1$ //$NON-NLS-2$ + } + + @Test public void testUDFParse() throws Exception { + QueryMetadataInterface metadata = RealMetadataFactory.createTransformationMetadata(RealMetadataFactory.example1Cached().getMetadataStore(), "example1", new FunctionTree("foo", new FakeFunctionMetadataSource())); + String sql = "parsedate_(pm1.g1.e1) = {d'2001-01-01'}"; + helpTestRewriteCriteria(sql, parseCriteria(sql, metadata), metadata); + } + + @Test public void testRewriteNestedConvert() throws Exception { + helpTestRewriteExpression("cast(cast(pm1.g1.e3 as integer) as long)", "cast(pm1.g1.e3 as long)", RealMetadataFactory.example1Cached()); //$NON-NLS-1$ //$NON-NLS-2$ + } + + @Test public void testRewriteNestedConvert1() throws Exception { + helpTestRewriteExpression("cast(cast(pm1.g1.e3 as integer) as string)", "convert(convert(pm1.g1.e3, integer), string)", RealMetadataFactory.example1Cached()); //$NON-NLS-1$ //$NON-NLS-2$ + } + + @Test public void testRewriteNestedConvert2() throws Exception { + helpTestRewriteExpression("cast(cast(pm1.g1.e3 as string) as clob)", "convert(convert(pm1.g1.e3, string), clob)", RealMetadataFactory.example1Cached()); //$NON-NLS-1$ //$NON-NLS-2$ + } + + @Test public void testRewriteConstantAgg() throws Exception { + helpTestRewriteCommand("select max(1) from pm1.g1 group by e1", "SELECT 1 FROM pm1.g1 GROUP BY e1"); + } + + @Test public void testRewriteTrim() throws Exception { + helpTestRewriteExpression("trim(pm1.g1.e1)", "rtrim(ltrim(pm1.g1.e1))", RealMetadataFactory.example1Cached()); + } + + @Test public void testRewriteTrim1() throws Exception { + helpTestRewriteExpression("trim(leading from pm1.g1.e1)", "ltrim(pm1.g1.e1)", RealMetadataFactory.example1Cached()); + } + }