Author: shawkins Date: 2010-11-24 13:11:42 -0500 (Wed, 24 Nov 2010) New Revision: 2743 Modified: trunk/documentation/reference/src/main/docbook/en-US/content/updatable_views.xml trunk/engine/src/main/java/org/teiid/query/optimizer/relational/rules/RuleMergeCriteria.java trunk/engine/src/main/java/org/teiid/query/resolver/util/ResolverUtil.java trunk/engine/src/main/java/org/teiid/query/rewriter/QueryRewriter.java trunk/engine/src/main/java/org/teiid/query/validator/UpdateValidator.java trunk/engine/src/test/java/org/teiid/query/validator/TestUpdateValidator.java Log: TEIID-1349 adding support to detect key preserved in non-ansi joins Modified: trunk/documentation/reference/src/main/docbook/en-US/content/updatable_views.xml =================================================================== --- trunk/documentation/reference/src/main/docbook/en-US/content/updatable_views.xml 2010-11-24 17:42:05 UTC (rev 2742) +++ trunk/documentation/reference/src/main/docbook/en-US/content/updatable_views.xml 2010-11-24 18:11:42 UTC (rev 2743) @@ -50,7 +50,7 @@ <para>A key-preserved table has a primary or unique key that would remain unique if it were projected into the result of the query. Note that it is not actually required for a view to reference the key columns in the SELECT clause. - The query engine can detect a key preserved table by analyzing the join structure - which is currently required to be in ANSI join form. + The query engine can detect a key preserved table by analyzing the join structure. The engine will ensure that a join of a key-preserved table must be against one of its foreign keys.</para> </section> Modified: trunk/engine/src/main/java/org/teiid/query/optimizer/relational/rules/RuleMergeCriteria.java =================================================================== --- trunk/engine/src/main/java/org/teiid/query/optimizer/relational/rules/RuleMergeCriteria.java 2010-11-24 17:42:05 UTC (rev 2742) +++ trunk/engine/src/main/java/org/teiid/query/optimizer/relational/rules/RuleMergeCriteria.java 2010-11-24 18:11:42 UTC (rev 2743) @@ -540,7 +540,7 @@ return true; } HashSet<GroupSymbol> keyPreservingGroups = new HashSet<GroupSymbol>(); - ResolverUtil.findKeyPreserinvg((FromClause)query.getFrom().getClauses().get(0), keyPreservingGroups, metadata); + ResolverUtil.findKeyPreserved(query, keyPreservingGroups, metadata); return NewCalculateCostUtil.usesKey(expressions, keyPreservingGroups, metadata); } Modified: trunk/engine/src/main/java/org/teiid/query/resolver/util/ResolverUtil.java =================================================================== --- trunk/engine/src/main/java/org/teiid/query/resolver/util/ResolverUtil.java 2010-11-24 17:42:05 UTC (rev 2742) +++ trunk/engine/src/main/java/org/teiid/query/resolver/util/ResolverUtil.java 2010-11-24 18:11:42 UTC (rev 2743) @@ -56,6 +56,7 @@ import org.teiid.query.optimizer.relational.rules.RuleChooseJoinStrategy; import org.teiid.query.optimizer.relational.rules.RuleRaiseAccess; import org.teiid.query.sql.LanguageObject; +import org.teiid.query.sql.lang.CompareCriteria; import org.teiid.query.sql.lang.Criteria; import org.teiid.query.sql.lang.FromClause; import org.teiid.query.sql.lang.JoinPredicate; @@ -941,8 +942,67 @@ symbol.setOutputName(name); } - public static void findKeyPreserinvg(FromClause clause, Set<GroupSymbol> keyPreservingGroups, QueryMetadataInterface metadata) + public static void findKeyPreserved(Query query, Set<GroupSymbol> keyPreservingGroups, QueryMetadataInterface metadata) throws TeiidComponentException, QueryMetadataException { + if (query.getFrom() == null) { + return; + } + if (query.getFrom().getClauses().size() == 1) { + findKeyPreserved((FromClause)query.getFrom().getClauses().get(0), keyPreservingGroups, metadata); + return; + } + //non-ansi join + Set<GroupSymbol> groups = new HashSet<GroupSymbol>(query.getFrom().getGroups()); + for (GroupSymbol groupSymbol : groups) { + if (metadata.getUniqueKeysInGroup(groupSymbol.getMetadataID()).isEmpty()) { + return; + } + } + LinkedList<Expression> leftExpressions = new LinkedList<Expression>(); + LinkedList<Expression> rightExpressions = new LinkedList<Expression>(); + for (Criteria crit : Criteria.separateCriteriaByAnd(query.getCriteria())) { + if (!(crit instanceof CompareCriteria)) { + continue; + } + CompareCriteria cc = (CompareCriteria)crit; + if (cc.getOperator() != CompareCriteria.EQ) { + continue; + } + if (cc.getLeftExpression() instanceof ElementSymbol && cc.getRightExpression() instanceof ElementSymbol) { + ElementSymbol left = (ElementSymbol)cc.getLeftExpression(); + ElementSymbol right = (ElementSymbol)cc.getRightExpression(); + int compare = left.getGroupSymbol().compareTo(right.getGroupSymbol()); + if (compare > 0) { + leftExpressions.add(left); + rightExpressions.add(right); + } else if (compare != 0) { + leftExpressions.add(right); + rightExpressions.add(left); + } + } + } + HashMap<List<GroupSymbol>, List<HashSet<Object>>> crits = createGroupMap(leftExpressions, rightExpressions); + HashSet<GroupSymbol> tempSet = new HashSet<GroupSymbol>(); + for (GroupSymbol group : groups) { + LinkedHashSet<GroupSymbol> visited = new LinkedHashSet<GroupSymbol>(); + LinkedList<GroupSymbol> toVisit = new LinkedList<GroupSymbol>(); + toVisit.add(group); + while (!toVisit.isEmpty()) { + GroupSymbol visiting = toVisit.removeLast(); + if (!visited.add(visiting)) { + continue; + } + toVisit.addAll(findKeyPreserved(tempSet, Collections.singleton(visiting), crits, true, metadata, groups)); + toVisit.addAll(findKeyPreserved(tempSet, Collections.singleton(visiting), crits, false, metadata, groups)); + } + if (visited.containsAll(groups)) { + keyPreservingGroups.add(group); + } + } + } + + public static void findKeyPreserved(FromClause clause, Set<GroupSymbol> keyPreservingGroups, QueryMetadataInterface metadata) + throws TeiidComponentException, QueryMetadataException { if (clause instanceof UnaryFromClause) { UnaryFromClause ufc = (UnaryFromClause)clause; @@ -956,9 +1016,9 @@ return; } HashSet<GroupSymbol> leftPk = new HashSet<GroupSymbol>(); - findKeyPreserinvg(jp.getLeftClause(), leftPk, metadata); + findKeyPreserved(jp.getLeftClause(), leftPk, metadata); HashSet<GroupSymbol> rightPk = new HashSet<GroupSymbol>(); - findKeyPreserinvg(jp.getRightClause(), rightPk, metadata); + findKeyPreserved(jp.getRightClause(), rightPk, metadata); if (leftPk.isEmpty() && rightPk.isEmpty()) { return; @@ -973,48 +1033,58 @@ LinkedList<Expression> rightExpressions = new LinkedList<Expression>(); RuleChooseJoinStrategy.separateCriteria(leftGroups, rightGroups, leftExpressions, rightExpressions, jp.getJoinCriteria(), new LinkedList<Criteria>()); - HashMap<List<GroupSymbol>, List<HashSet<Object>>> crits = new HashMap<List<GroupSymbol>, List<HashSet<Object>>>(); - - for (int i = 0; i < leftExpressions.size(); i++) { - Expression lexpr = leftExpressions.get(i); - Expression rexpr = rightExpressions.get(i); - if (!(lexpr instanceof ElementSymbol) || !(rexpr instanceof ElementSymbol)) { - continue; - } - ElementSymbol les = (ElementSymbol)lexpr; - ElementSymbol res = (ElementSymbol)rexpr; - List<GroupSymbol> tbls = Arrays.asList(les.getGroupSymbol(), res.getGroupSymbol()); - List<HashSet<Object>> ids = crits.get(tbls); - if (ids == null) { - ids = Arrays.asList(new HashSet<Object>(), new HashSet<Object>()); - crits.put(tbls, ids); - } - ids.get(0).add(les.getMetadataID()); - ids.get(1).add(res.getMetadataID()); - } + HashMap<List<GroupSymbol>, List<HashSet<Object>>> crits = createGroupMap(leftExpressions, rightExpressions); if (!leftPk.isEmpty() && (jp.getJoinType() == JoinType.JOIN_INNER || jp.getJoinType() == JoinType.JOIN_LEFT_OUTER)) { - findKeyPreserving(keyPreservingGroups, leftPk, crits, true, metadata); + findKeyPreserved(keyPreservingGroups, leftPk, crits, true, metadata, rightPk); } if (!rightPk.isEmpty() && (jp.getJoinType() == JoinType.JOIN_INNER || jp.getJoinType() == JoinType.JOIN_RIGHT_OUTER)) { - findKeyPreserving(keyPreservingGroups, rightPk, crits, false, metadata); + findKeyPreserved(keyPreservingGroups, rightPk, crits, false, metadata, leftPk); } } } - static private void findKeyPreserving(Set<GroupSymbol> keyPreservingGroups, - HashSet<GroupSymbol> pk, - HashMap<List<GroupSymbol>, List<HashSet<Object>>> crits, boolean left, QueryMetadataInterface metadata) + private static HashMap<List<GroupSymbol>, List<HashSet<Object>>> createGroupMap( + LinkedList<Expression> leftExpressions, + LinkedList<Expression> rightExpressions) { + HashMap<List<GroupSymbol>, List<HashSet<Object>>> crits = new HashMap<List<GroupSymbol>, List<HashSet<Object>>>(); + + for (int i = 0; i < leftExpressions.size(); i++) { + Expression lexpr = leftExpressions.get(i); + Expression rexpr = rightExpressions.get(i); + if (!(lexpr instanceof ElementSymbol) || !(rexpr instanceof ElementSymbol)) { + continue; + } + ElementSymbol les = (ElementSymbol)lexpr; + ElementSymbol res = (ElementSymbol)rexpr; + List<GroupSymbol> tbls = Arrays.asList(les.getGroupSymbol(), res.getGroupSymbol()); + List<HashSet<Object>> ids = crits.get(tbls); + if (ids == null) { + ids = Arrays.asList(new HashSet<Object>(), new HashSet<Object>()); + crits.put(tbls, ids); + } + ids.get(0).add(les.getMetadataID()); + ids.get(1).add(res.getMetadataID()); + } + return crits; + } + + static private HashSet<GroupSymbol> findKeyPreserved(Set<GroupSymbol> keyPreservingGroups, + Set<GroupSymbol> pk, + HashMap<List<GroupSymbol>, List<HashSet<Object>>> crits, boolean left, QueryMetadataInterface metadata, Set<GroupSymbol> otherGroups) throws TeiidComponentException, QueryMetadataException { + HashSet<GroupSymbol> result = new HashSet<GroupSymbol>(); for (GroupSymbol gs : pk) { for (Map.Entry<List<GroupSymbol>, List<HashSet<Object>>> entry : crits.entrySet()) { - if (!entry.getKey().get(left?0:1).equals(gs)) { + if (!entry.getKey().get(left?0:1).equals(gs) || !otherGroups.contains(entry.getKey().get(left?1:0))) { continue; } if (RuleRaiseAccess.matchesForeignKey(metadata, entry.getValue().get(left?0:1), entry.getValue().get(left?1:0), gs, false)) { keyPreservingGroups.add(gs); + result.add(entry.getKey().get(left?1:0)); } } } + return result; } } Modified: trunk/engine/src/main/java/org/teiid/query/rewriter/QueryRewriter.java =================================================================== --- trunk/engine/src/main/java/org/teiid/query/rewriter/QueryRewriter.java 2010-11-24 17:42:05 UTC (rev 2742) +++ trunk/engine/src/main/java/org/teiid/query/rewriter/QueryRewriter.java 2010-11-24 18:11:42 UTC (rev 2743) @@ -688,7 +688,7 @@ if (query.getFrom().getClauses().size() > 1) { continue; } - ResolverUtil.findKeyPreserinvg((FromClause)query.getFrom().getClauses().get(0), keyPreservingGroups, metadata); + ResolverUtil.findKeyPreserved(query, keyPreservingGroups, metadata); if (!NewCalculateCostUtil.usesKey(plannedResult.leftExpressions, keyPreservingGroups, metadata)) { continue; } Modified: trunk/engine/src/main/java/org/teiid/query/validator/UpdateValidator.java =================================================================== --- trunk/engine/src/main/java/org/teiid/query/validator/UpdateValidator.java 2010-11-24 17:42:05 UTC (rev 2742) +++ trunk/engine/src/main/java/org/teiid/query/validator/UpdateValidator.java 2010-11-24 18:11:42 UTC (rev 2743) @@ -35,7 +35,6 @@ import org.teiid.query.metadata.SupportConstants; import org.teiid.query.resolver.util.ResolverUtil; import org.teiid.query.sql.lang.Command; -import org.teiid.query.sql.lang.FromClause; import org.teiid.query.sql.lang.Query; import org.teiid.query.sql.lang.UnaryFromClause; import org.teiid.query.sql.symbol.ElementSymbol; @@ -233,9 +232,7 @@ List<GroupSymbol> allGroups = query.getFrom().getGroups(); HashSet<GroupSymbol> keyPreservingGroups = new HashSet<GroupSymbol>(); - if (query.getFrom().getClauses().size() == 1) { - ResolverUtil.findKeyPreserinvg((FromClause)query.getFrom().getClauses().get(0), keyPreservingGroups, metadata); - } + ResolverUtil.findKeyPreserved(query, keyPreservingGroups, metadata); for (GroupSymbol groupSymbol : keyPreservingGroups) { setUpdateFlags(groupSymbol); Modified: trunk/engine/src/test/java/org/teiid/query/validator/TestUpdateValidator.java =================================================================== --- trunk/engine/src/test/java/org/teiid/query/validator/TestUpdateValidator.java 2010-11-24 17:42:05 UTC (rev 2742) +++ trunk/engine/src/test/java/org/teiid/query/validator/TestUpdateValidator.java 2010-11-24 18:11:42 UTC (rev 2743) @@ -230,6 +230,11 @@ helpTest("SELECT pm1.g1.e1 FROM pm1.g1, pm1.g2", example1(), true); } + + @Test public void testCreateInsertCommand14(){ + helpTest("SELECT pm1.g2.e1 FROM pm1.g1, pm1.g2 where g1.e1 = g2.e1", + example1(), false); + } @Test public void testCreateInsertCommand2_fail(){ helpTest("SELECT CONCAT(pm1.g1.e1, convert(pm1.g2.e1, string)) as x FROM pm1.g1, pm1.g2",