Author: shawkins Date: 2010-12-06 13:06:30 -0500 (Mon, 06 Dec 2010) New Revision: 2760 Modified: trunk/engine/src/main/java/org/teiid/query/optimizer/relational/RelationalPlanner.java trunk/engine/src/main/java/org/teiid/query/processor/proc/ForEachRowPlan.java trunk/engine/src/main/java/org/teiid/query/resolver/QueryResolver.java trunk/engine/src/main/java/org/teiid/query/resolver/command/DeleteResolver.java trunk/engine/src/main/java/org/teiid/query/resolver/command/InsertResolver.java trunk/engine/src/main/java/org/teiid/query/resolver/command/UpdateProcedureResolver.java trunk/engine/src/main/java/org/teiid/query/resolver/command/UpdateResolver.java trunk/engine/src/main/java/org/teiid/query/rewriter/QueryRewriter.java trunk/engine/src/main/resources/org/teiid/query/i18n.properties trunk/engine/src/test/java/org/teiid/query/processor/TestProcessor.java Log: TEIID-1267 adding support for compensating updates/deletes with non-pushdown logic as a procedure Modified: trunk/engine/src/main/java/org/teiid/query/optimizer/relational/RelationalPlanner.java =================================================================== --- trunk/engine/src/main/java/org/teiid/query/optimizer/relational/RelationalPlanner.java 2010-12-06 16:56:22 UTC (rev 2759) +++ trunk/engine/src/main/java/org/teiid/query/optimizer/relational/RelationalPlanner.java 2010-12-06 18:06:30 UTC (rev 2760) @@ -79,6 +79,7 @@ import org.teiid.query.sql.lang.CacheHint; import org.teiid.query.sql.lang.Command; import org.teiid.query.sql.lang.Criteria; +import org.teiid.query.sql.lang.Delete; import org.teiid.query.sql.lang.From; import org.teiid.query.sql.lang.FromClause; import org.teiid.query.sql.lang.GroupBy; @@ -97,7 +98,9 @@ import org.teiid.query.sql.lang.SubqueryContainer; import org.teiid.query.sql.lang.SubqueryFromClause; import org.teiid.query.sql.lang.TableFunctionReference; +import org.teiid.query.sql.lang.TranslatableProcedureContainer; import org.teiid.query.sql.lang.UnaryFromClause; +import org.teiid.query.sql.lang.Update; import org.teiid.query.sql.lang.WithQueryCommand; import org.teiid.query.sql.navigator.PreOrPostOrderNavigator; import org.teiid.query.sql.proc.CreateUpdateProcedureCommand; @@ -586,9 +589,21 @@ //skip the rewrite here, we'll do that in the optimizer //so that we know what the determinism level is. addNestedCommand(sourceNode, container.getGroup(), container, c, false); - } else if (!(container instanceof Insert) && !container.getGroup().isTempGroupSymbol() && + } else if (container instanceof TranslatableProcedureContainer && !container.getGroup().isTempGroupSymbol() && !CriteriaCapabilityValidatorVisitor.canPushLanguageObject(container, metadata.getModelID(container.getGroup().getMetadataID()), metadata, capFinder, analysisRecord)) { - throw new QueryPlannerException(QueryPlugin.Util.getString("RelationalPlanner.nonpushdown_command", container)); //$NON-NLS-1$ + if (metadata.getUniqueKeysInGroup(container.getGroup().getMetadataID()).isEmpty() + || !CapabilitiesUtil.supports(Capability.CRITERIA_COMPARE_EQ, metadata.getModelID(container.getGroup().getMetadataID()), metadata, capFinder)) { + throw new QueryPlannerException(QueryPlugin.Util.getString("RelationalPlanner.nonpushdown_command", container)); //$NON-NLS-1$ + } + + //treat this as an update procedure + if (container instanceof Update) { + c = QueryRewriter.createUpdateProcedure((Update)container, metadata, context); + } else { + c = QueryRewriter.createDeleteProcedure((Delete)container, metadata, context); + } + addNestedCommand(sourceNode, container.getGroup(), container, c, false); + return false; } //plan any subqueries in criteria/parameters/values Modified: trunk/engine/src/main/java/org/teiid/query/processor/proc/ForEachRowPlan.java =================================================================== --- trunk/engine/src/main/java/org/teiid/query/processor/proc/ForEachRowPlan.java 2010-12-06 16:56:22 UTC (rev 2759) +++ trunk/engine/src/main/java/org/teiid/query/processor/proc/ForEachRowPlan.java 2010-12-06 18:06:30 UTC (rev 2760) @@ -156,5 +156,10 @@ this.queryProcessor = null; this.tupleSource = null; } + + @Override + public boolean requiresTransaction(boolean transactionalReads) { + return true; + } } Modified: trunk/engine/src/main/java/org/teiid/query/resolver/QueryResolver.java =================================================================== --- trunk/engine/src/main/java/org/teiid/query/resolver/QueryResolver.java 2010-12-06 16:56:22 UTC (rev 2759) +++ trunk/engine/src/main/java/org/teiid/query/resolver/QueryResolver.java 2010-12-06 18:06:30 UTC (rev 2760) @@ -300,11 +300,13 @@ } public static void resolveSubqueries(Command command, - TempMetadataAdapter metadata, AnalysisRecord analysis) + TempMetadataAdapter metadata, AnalysisRecord analysis, Collection<GroupSymbol> externalGroups) throws QueryResolverException, TeiidComponentException { for (SubqueryContainer container : ValueIteratorProviderCollectorVisitor.getValueIteratorProviders(command)) { QueryResolver.setChildMetadata(container.getCommand(), command); - + if (externalGroups != null) { + container.getCommand().pushNewResolvingContext(externalGroups); + } QueryResolver.resolveCommand(container.getCommand(), Collections.EMPTY_MAP, metadata.getMetadata(), analysis); } } Modified: trunk/engine/src/main/java/org/teiid/query/resolver/command/DeleteResolver.java =================================================================== --- trunk/engine/src/main/java/org/teiid/query/resolver/command/DeleteResolver.java 2010-12-06 16:56:22 UTC (rev 2759) +++ trunk/engine/src/main/java/org/teiid/query/resolver/command/DeleteResolver.java 2010-12-06 18:06:30 UTC (rev 2760) @@ -58,8 +58,8 @@ Set<GroupSymbol> groups = new HashSet<GroupSymbol>(); groups.add(delete.getGroup()); + QueryResolver.resolveSubqueries(command, metadata, analysis, groups); ResolverVisitor.resolveLanguageObject(delete, groups, delete.getExternalGroupContexts(), metadata); - QueryResolver.resolveSubqueries(command, metadata, analysis); } /** Modified: trunk/engine/src/main/java/org/teiid/query/resolver/command/InsertResolver.java =================================================================== --- trunk/engine/src/main/java/org/teiid/query/resolver/command/InsertResolver.java 2010-12-06 16:56:22 UTC (rev 2759) +++ trunk/engine/src/main/java/org/teiid/query/resolver/command/InsertResolver.java 2010-12-06 18:06:30 UTC (rev 2760) @@ -75,7 +75,7 @@ Insert insert = (Insert) command; if (insert.getValues() != null) { - QueryResolver.resolveSubqueries(command, metadata, analysis); + QueryResolver.resolveSubqueries(command, metadata, analysis, null); //variables and values must be resolved separately to account for implicitly defined temp groups resolveList(insert.getValues(), metadata, insert.getExternalGroupContexts(), null); } Modified: trunk/engine/src/main/java/org/teiid/query/resolver/command/UpdateProcedureResolver.java =================================================================== --- trunk/engine/src/main/java/org/teiid/query/resolver/command/UpdateProcedureResolver.java 2010-12-06 16:56:22 UTC (rev 2759) +++ trunk/engine/src/main/java/org/teiid/query/resolver/command/UpdateProcedureResolver.java 2010-12-06 18:06:30 UTC (rev 2760) @@ -103,6 +103,8 @@ break; } } + } else if (!metadata.isVirtualGroup(virtualGroup.getMetadataID())) { + return; } // If still haven't found virtual group, the external metadata is bad Modified: trunk/engine/src/main/java/org/teiid/query/resolver/command/UpdateResolver.java =================================================================== --- trunk/engine/src/main/java/org/teiid/query/resolver/command/UpdateResolver.java 2010-12-06 16:56:22 UTC (rev 2759) +++ trunk/engine/src/main/java/org/teiid/query/resolver/command/UpdateResolver.java 2010-12-06 18:06:30 UTC (rev 2760) @@ -73,8 +73,8 @@ for (SetClause clause : update.getChangeList().getClauses()) { ResolverVisitor.resolveLanguageObject(clause.getSymbol(), groups, null, metadata); } + QueryResolver.resolveSubqueries(command, metadata, analysis, groups); ResolverVisitor.resolveLanguageObject(update, groups, update.getExternalGroupContexts(), metadata); - QueryResolver.resolveSubqueries(command, metadata, analysis); } /** Modified: trunk/engine/src/main/java/org/teiid/query/rewriter/QueryRewriter.java =================================================================== --- trunk/engine/src/main/java/org/teiid/query/rewriter/QueryRewriter.java 2010-12-06 16:56:22 UTC (rev 2759) +++ trunk/engine/src/main/java/org/teiid/query/rewriter/QueryRewriter.java 2010-12-06 18:06:30 UTC (rev 2760) @@ -2560,12 +2560,12 @@ if (!info.getUnionBranches().isEmpty()) { List<Command> batchedUpdates = new ArrayList<Command>(info.getUnionBranches().size() + 1); for (UpdateInfo branchInfo : info.getUnionBranches()) { - batchedUpdates.add(createInherentUpdateProc((Update)update.clone(), branchInfo)); + batchedUpdates.add(rewriteInherentUpdate((Update)update.clone(), branchInfo)); } - batchedUpdates.add(0, createInherentUpdateProc(update, info)); + batchedUpdates.add(0, rewriteInherentUpdate(update, info)); return new BatchedUpdateCommand(batchedUpdates); } - return createInherentUpdateProc(update, info); + return rewriteInherentUpdate(update, info); } if (commandType == Command.TYPE_UPDATE && variables != null) { @@ -2594,7 +2594,7 @@ return update; } - private Command createInherentUpdateProc(Update update, UpdateInfo info) + private Command rewriteInherentUpdate(Update update, UpdateInfo info) throws QueryValidatorException, QueryMetadataException, TeiidComponentException, QueryResolverException, TeiidProcessingException { @@ -2621,6 +2621,24 @@ query.setOrderBy(null); SymbolMap expressionMapping = SymbolMap.createSymbolMap(update.getGroup(), query.getProjectedSymbols(), metadata); + ArrayList<SingleElementSymbol> selectSymbols = mapChangeList(update, symbolMap); + query.setSelect(new Select(selectSymbols)); + ExpressionMappingVisitor emv = new ExpressionMappingVisitor(expressionMapping.asMap(), true); + PostOrderNavigator.doVisit(query.getSelect(), emv); + + Criteria crit = update.getCriteria(); + if (crit != null) { + PostOrderNavigator.doVisit(crit, emv); + query.setCriteria(Criteria.combineCriteria(query.getCriteria(), crit)); + } + GroupSymbol group = mapping.getGroup(); + String correlationName = mapping.getCorrelatedName().getName(); + + return createUpdateProcedure(update, query, group, correlationName); + } + + private ArrayList<SingleElementSymbol> mapChangeList(Update update, + Map<ElementSymbol, ElementSymbol> symbolMap) { ArrayList<SingleElementSymbol> selectSymbols = new ArrayList<SingleElementSymbol>(update.getChangeList().getClauses().size()); int i = 0; for (SetClause clause : update.getChangeList().getClauses()) { @@ -2635,29 +2653,30 @@ selectSymbols.add(new AliasSymbol("s_" +i, selectSymbol)); //$NON-NLS-1$ ex = new ElementSymbol("s_" +i); //$NON-NLS-1$ } - clause.setSymbol(symbolMap.get(clause.getSymbol())); + if (symbolMap != null) { + clause.setSymbol(symbolMap.get(clause.getSymbol())); + } i++; } - query.setSelect(new Select(selectSymbols)); - ExpressionMappingVisitor emv = new ExpressionMappingVisitor(expressionMapping.asMap(), true); - PostOrderNavigator.doVisit(query.getSelect(), emv); - - Criteria crit = update.getCriteria(); - if (crit != null) { - PostOrderNavigator.doVisit(crit, emv); - query.setCriteria(Criteria.combineCriteria(query.getCriteria(), crit)); - } - + return selectSymbols; + } + + private Command createUpdateProcedure(Update update, Query query, + GroupSymbol group, String correlationName) + throws TeiidComponentException, QueryMetadataException, + QueryResolverException, TeiidProcessingException { Update newUpdate = new Update(); newUpdate.setChangeList(update.getChangeList()); - newUpdate.setGroup(mapping.getGroup().clone()); - - List<Criteria> pkCriteria = createPkCriteria(mapping, query, i); - newUpdate.setCriteria(Criteria.combineCriteria(newUpdate.getCriteria(), new CompoundCriteria(pkCriteria))); - return createUpdateProcedure(update, query, newUpdate); + newUpdate.setGroup(group.clone()); + List<Criteria> pkCriteria = createPkCriteria(group, correlationName, query); + newUpdate.setCriteria(new CompoundCriteria(pkCriteria)); + return asLoopProcedure(update.getGroup(), query, newUpdate); } - private Command createUpdateProcedure(ProcedureContainer update, Query query, + /** + * rewrite as loop on (query) as X begin newupdate; rows_updated = rows_updated + 1; + */ + private Command asLoopProcedure(GroupSymbol group, Query query, ProcedureContainer newUpdate) throws QueryResolverException, TeiidComponentException, TeiidProcessingException { Block b = new Block(); @@ -2672,23 +2691,23 @@ as.setExpression(new Function("+", new Expression[] {rowsUpdate, new Constant(1)})); //$NON-NLS-1$ b.addStatement(as); cupc.setBlock(parent); - cupc.setVirtualGroup(update.getGroup()); + cupc.setVirtualGroup(group); QueryResolver.resolveCommand(cupc, metadata); return rewrite(cupc, metadata, context); } - private List<Criteria> createPkCriteria(UpdateMapping mapping, Query query, - int i) throws TeiidComponentException, QueryMetadataException { - Object pk = metadata.getPrimaryKey(mapping.getGroup().getMetadataID()); + private List<Criteria> createPkCriteria(GroupSymbol group, String correlationName, Query query) throws TeiidComponentException, QueryMetadataException { + Object pk = metadata.getPrimaryKey(group.getMetadataID()); if (pk == null) { - pk = metadata.getUniqueKeysInGroup(mapping.getGroup().getMetadataID()).iterator().next(); + pk = metadata.getUniqueKeysInGroup(group.getMetadataID()).iterator().next(); } + int i = query.getSelect().getSymbols().size(); List<Object> ids = metadata.getElementIDsInKey(pk); List<Criteria> pkCriteria = new ArrayList<Criteria>(ids.size()); for (Object object : ids) { - ElementSymbol es = new ElementSymbol(mapping.getCorrelatedName().getName() + ElementSymbol.SEPARATOR + SingleElementSymbol.getShortName(metadata.getFullName(object))); + ElementSymbol es = new ElementSymbol(correlationName + ElementSymbol.SEPARATOR + SingleElementSymbol.getShortName(metadata.getFullName(object))); query.getSelect().addSymbol(new AliasSymbol("s_" +i, es)); //$NON-NLS-1$ - es = new ElementSymbol(mapping.getGroup().getName() + ElementSymbol.SEPARATOR + SingleElementSymbol.getShortName(metadata.getFullName(object))); + es = new ElementSymbol(group.getName() + ElementSymbol.SEPARATOR + SingleElementSymbol.getShortName(metadata.getFullName(object))); pkCriteria.add(new CompareCriteria(es, CompareCriteria.EQ, new ElementSymbol("X.s_" + i))); //$NON-NLS-1$ i++; } @@ -2733,12 +2752,12 @@ if (!info.getUnionBranches().isEmpty()) { List<Command> batchedUpdates = new ArrayList<Command>(info.getUnionBranches().size() + 1); for (UpdateInfo branchInfo : info.getUnionBranches()) { - batchedUpdates.add(createInherentDeleteProc((Delete)delete.clone(), branchInfo)); + batchedUpdates.add(rewriteInherentDelete((Delete)delete.clone(), branchInfo)); } - batchedUpdates.add(0, createInherentDeleteProc(delete, info)); + batchedUpdates.add(0, rewriteInherentDelete(delete, info)); return new BatchedUpdateCommand(batchedUpdates); } - return createInherentDeleteProc(delete, info); + return rewriteInherentDelete(delete, info); } // Rewrite criteria Criteria crit = delete.getCriteria(); @@ -2749,7 +2768,7 @@ return delete; } - private Command createInherentDeleteProc(Delete delete, UpdateInfo info) + private Command rewriteInherentDelete(Delete delete, UpdateInfo info) throws QueryMetadataException, TeiidComponentException, QueryResolverException, TeiidProcessingException { UpdateMapping mapping = info.getDeleteTarget(); @@ -2770,20 +2789,41 @@ query.setSelect(new Select()); ExpressionMappingVisitor emv = new ExpressionMappingVisitor(expressionMapping.asMap(), true); - PostOrderNavigator.doVisit(query.getSelect(), emv); Criteria crit = delete.getCriteria(); if (crit != null) { PostOrderNavigator.doVisit(crit, emv); query.setCriteria(Criteria.combineCriteria(query.getCriteria(), crit)); } - + GroupSymbol group = mapping.getGroup(); + String correlationName = mapping.getCorrelatedName().getName(); + return createDeleteProcedure(delete, query, group, correlationName); + } + + public static Command createDeleteProcedure(Delete delete, QueryMetadataInterface metadata, CommandContext context) throws QueryResolverException, QueryMetadataException, TeiidComponentException, TeiidProcessingException { + QueryRewriter rewriter = new QueryRewriter(metadata, context, null); + Criteria crit = delete.getCriteria(); + Query query = new Query(new Select(), new From(Arrays.asList(new UnaryFromClause(delete.getGroup()))), crit, null, null); + return rewriter.createDeleteProcedure(delete, query, delete.getGroup(), delete.getGroup().getName()); + } + + public static Command createUpdateProcedure(Update update, QueryMetadataInterface metadata, CommandContext context) throws QueryResolverException, QueryMetadataException, TeiidComponentException, TeiidProcessingException { + QueryRewriter rewriter = new QueryRewriter(metadata, context, null); + Criteria crit = update.getCriteria(); + ArrayList<SingleElementSymbol> selectSymbols = rewriter.mapChangeList(update, null); + Query query = new Query(new Select(selectSymbols), new From(Arrays.asList(new UnaryFromClause(update.getGroup()))), crit, null, null); + return rewriter.createUpdateProcedure(update, query, update.getGroup(), update.getGroup().getName()); + } + + private Command createDeleteProcedure(Delete delete, Query query, + GroupSymbol group, String correlationName) + throws TeiidComponentException, QueryMetadataException, + QueryResolverException, TeiidProcessingException { Delete newUpdate = new Delete(); - newUpdate.setGroup(mapping.getGroup().clone()); - - List<Criteria> pkCriteria = createPkCriteria(mapping, query, 0); - newUpdate.setCriteria(Criteria.combineCriteria(newUpdate.getCriteria(), new CompoundCriteria(pkCriteria))); - return createUpdateProcedure(delete, query, newUpdate); + newUpdate.setGroup(group.clone()); + List<Criteria> pkCriteria = createPkCriteria(group, correlationName, query); + newUpdate.setCriteria(new CompoundCriteria(pkCriteria)); + return asLoopProcedure(delete.getGroup(), query, newUpdate); } private Limit rewriteLimitClause(Limit limit) throws TeiidComponentException, TeiidProcessingException{ Modified: trunk/engine/src/main/resources/org/teiid/query/i18n.properties =================================================================== --- trunk/engine/src/main/resources/org/teiid/query/i18n.properties 2010-12-06 16:56:22 UTC (rev 2759) +++ trunk/engine/src/main/resources/org/teiid/query/i18n.properties 2010-12-06 18:06:30 UTC (rev 2760) @@ -886,6 +886,6 @@ failed_to_unwrap_connection=Failed to unwrap the source connection. connection_factory_not_found=Failed to the Connection Factory with JNDI name {0}. Please check the name for spelling or deploy the Connection Factory with specified name. -RelationalPlanner.nonpushdown_command=Source command "{0}" contains non-pushdown constructs. +RelationalPlanner.nonpushdown_command=Source UPDATE or DELETE command "{0}" contains non-pushdown constructs and no compensating action can be taken as the table lacks a unique key or the source does not support equality predicates . Translate.error=Cannot translate criteria "{0}", it is not matched by selector "{1}" \ No newline at end of file Modified: trunk/engine/src/test/java/org/teiid/query/processor/TestProcessor.java =================================================================== --- trunk/engine/src/test/java/org/teiid/query/processor/TestProcessor.java 2010-12-06 16:56:22 UTC (rev 2759) +++ trunk/engine/src/test/java/org/teiid/query/processor/TestProcessor.java 2010-12-06 18:06:30 UTC (rev 2760) @@ -7596,5 +7596,35 @@ helpProcess(plan, dataManager, expected); } + @Test public void testDeleteCompensation() { + String sql = "delete from pm1.g1 where e1 = 'a' and e2 in (select e2 from pm1.g2)"; //$NON-NLS-1$ + + List[] expected = new List[] { + Arrays.asList(3), + }; + + FakeDataManager dataManager = new FakeDataManager(); + sampleData1(dataManager); + + ProcessorPlan plan = helpGetPlan(sql, FakeMetadataFactory.example4(), TestOptimizer.getGenericFinder()); + + helpProcess(plan, dataManager, expected); + } + + @Test public void testUpdateCompensation() { + String sql = "update pm1.g1 set e4 = null where e1 = 'a' and exists (select 1 from pm1.g2 where e2 = pm1.g1.e2)"; //$NON-NLS-1$ + + List[] expected = new List[] { + Arrays.asList(3), + }; + + FakeDataManager dataManager = new FakeDataManager(); + sampleData1(dataManager); + + ProcessorPlan plan = helpGetPlan(sql, FakeMetadataFactory.example4(), TestOptimizer.getGenericFinder()); + + helpProcess(plan, dataManager, expected); + } + private static final boolean DEBUG = false; }