Author: shawkins Date: 2010-07-30 16:30:08 -0400 (Fri, 30 Jul 2010) New Revision: 2393 Modified: trunk/documentation/reference/src/main/docbook/en-US/content/sql_support.xml trunk/engine/src/main/java/org/teiid/query/resolver/command/InsertResolver.java trunk/engine/src/test/java/org/teiid/query/resolver/TestResolver.java Log: TEIID-1177 refining the resolving logic to omit the columns for defining a temp table with a query expression Modified: trunk/documentation/reference/src/main/docbook/en-US/content/sql_support.xml =================================================================== --- trunk/documentation/reference/src/main/docbook/en-US/content/sql_support.xml 2010-07-30 20:06:10 UTC (rev 2392) +++ trunk/documentation/reference/src/main/docbook/en-US/content/sql_support.xml 2010-07-30 20:30:08 UTC (rev 2393) @@ -647,13 +647,21 @@ <para>Creation syntax: </para> <listitem> - <para>CREATE LOCAL TEMPORARY TABLE x (column type, ... [PRIMARY KEY (column, ...)]) + <para>Explicit: CREATE LOCAL TEMPORARY TABLE x (column type, ... [PRIMARY KEY (column, ...)]) </para> </listitem> <listitem> - <para>INSERT INTO #x (column, ...) VALUES (value, ...) + <para>Implicit: INSERT INTO #x (column, ...) VALUES (value, ...) </para> + <para>If #x doen't exist, it will be defined using the given column names and types from the value expressions. + </para> </listitem> + <listitem> + <para>Implicit: INSERT INTO #x [(column, ...)] select c1, c2 from t + </para> + <para>If #x doesn't exist, it will be defined using the target column names (in not supplied, the column names will match the derived column names from the query), and the types from the query derived columns. + </para> + </listitem> </itemizedlist> <itemizedlist> <para>Drop syntax: 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-07-30 20:06:10 UTC (rev 2392) +++ trunk/engine/src/main/java/org/teiid/query/resolver/command/InsertResolver.java 2010-07-30 20:30:08 UTC (rev 2393) @@ -56,6 +56,7 @@ import org.teiid.query.sql.symbol.Expression; import org.teiid.query.sql.symbol.GroupSymbol; import org.teiid.query.sql.symbol.Reference; +import org.teiid.query.sql.symbol.SingleElementSymbol; import org.teiid.query.util.ErrorMessageKeys; @@ -88,6 +89,14 @@ Set<GroupSymbol> groups = new HashSet<GroupSymbol>(); groups.add(insert.getGroup()); + // resolve any functions in the values + List values = insert.getValues(); + boolean usingQuery = insert.getQueryExpression() != null; + + if (usingQuery) { + values = insert.getQueryExpression().getProjectedSymbols(); + } + if (insert.getVariables().isEmpty()) { if (insert.getGroup().isResolved()) { List variables = ResolverUtil.resolveElementsInGroup(insert.getGroup(), metadata); @@ -95,15 +104,22 @@ insert.addVariable((ElementSymbol)((ElementSymbol)i.next()).clone()); } } else { - for (int i = 0; i < insert.getValues().size(); i++) { - insert.addVariable(new ElementSymbol("expr" + i)); //$NON-NLS-1$ + for (int i = 0; i < values.size(); i++) { + if (usingQuery) { + SingleElementSymbol ses = (SingleElementSymbol)values.get(i); + ElementSymbol es = new ElementSymbol(ses.getShortName()); + es.setType(ses.getType()); + insert.addVariable(es); + } else { + insert.addVariable(new ElementSymbol("expr" + i)); //$NON-NLS-1$ + } } } } else if (insert.getGroup().isResolved()) { resolveVariables(metadata, insert, groups); } - resolveTypes(insert, metadata); + resolveTypes(insert, metadata, values, usingQuery); if (!insert.getGroup().isResolved()) { //define the implicit temp group if(insert.getQueryExpression() != null) { @@ -114,7 +130,7 @@ resolveVariables(metadata, insert, groups); //ensure that the types match - resolveTypes(insert, metadata); + resolveTypes(insert, metadata, values, usingQuery); } if (insert.getQueryExpression() != null && metadata.isVirtualGroup(insert.getGroup().getMetadataID())) { @@ -150,24 +166,17 @@ /** * @param insert + * @param values + * @param usingQuery * @throws QueryResolverException */ - public void resolveTypes(Insert insert, TempMetadataAdapter metadata) throws QueryResolverException { + public void resolveTypes(Insert insert, TempMetadataAdapter metadata, List values, boolean usingQuery) throws QueryResolverException { - boolean usingQuery = insert.getQueryExpression() != null; - - // resolve any functions in the values - List values = insert.getValues(); - - if (usingQuery) { - values = insert.getQueryExpression().getProjectedSymbols(); - } - List newValues = new ArrayList(values.size()); // check that # of variables == # of values if(values.size() != insert.getVariables().size()) { - throw new QueryResolverException(ErrorMessageKeys.RESOLVER_0010, QueryPlugin.Util.getString(ErrorMessageKeys.RESOLVER_0010, new Object[] {new Integer(insert.getVariables().size()), new Integer(insert.getValues().size())})); + throw new QueryResolverException(ErrorMessageKeys.RESOLVER_0010, QueryPlugin.Util.getString(ErrorMessageKeys.RESOLVER_0010, insert.getVariables().size(), values.size())); } Iterator valueIter = values.iterator(); Modified: trunk/engine/src/test/java/org/teiid/query/resolver/TestResolver.java =================================================================== --- trunk/engine/src/test/java/org/teiid/query/resolver/TestResolver.java 2010-07-30 20:06:10 UTC (rev 2392) +++ trunk/engine/src/test/java/org/teiid/query/resolver/TestResolver.java 2010-07-30 20:30:08 UTC (rev 2393) @@ -2706,6 +2706,17 @@ helpResolveException(sql, "Cannot convert insert query expression projected symbol '3' of type java.lang.Integer to insert column 'pm1.g1.e3' of type java.lang.Boolean"); //$NON-NLS-1$ } + + @Test public void testInsertWithQueryImplicitWithoutColumns() { + String sql = "Insert into #X select 1 as x, 2 as y, 3 as z"; //$NON-NLS-1$ + helpResolve(sql); //$NON-NLS-1$ + } + + @Test public void testInsertWithQueryImplicitWithoutColumns1() { + String sql = "Insert into #X select 1 as x, 2 as y, 3 as y"; //$NON-NLS-1$ + + helpResolveException(sql, "Cannot create group '#X' with multiple columns named 'y'"); //$NON-NLS-1$ + } @Test public void testInsertWithoutColumnsPasses() { String sql = "Insert into pm1.g1 values (1, 2, true, 4)"; //$NON-NLS-1$