Author: shawkins Date: 2011-06-08 12:54:23 -0400 (Wed, 08 Jun 2011) New Revision: 3234 Added: trunk/engine/src/test/java/org/teiid/query/parser/TestLimitParsing.java Modified: trunk/api/src/main/java/org/teiid/language/SQLConstants.java trunk/build/kits/jboss-container/teiid-releasenotes.html trunk/documentation/reference/src/main/docbook/en-US/content/sql_support.xml trunk/engine/src/main/java/org/teiid/dqp/internal/datamgr/LanguageBridgeFactory.java trunk/engine/src/main/java/org/teiid/query/sql/visitor/SQLStringVisitor.java trunk/engine/src/main/javacc/org/teiid/query/parser/SQLParser.jj trunk/engine/src/test/java/org/teiid/query/parser/TestParser.java trunk/engine/src/test/java/org/teiid/query/processor/TestProcessor.java trunk/engine/src/test/java/org/teiid/query/processor/relational/TestLimitNode.java Log: TEIID-1620 adding support for offset/fetch query clauses Modified: trunk/api/src/main/java/org/teiid/language/SQLConstants.java =================================================================== --- trunk/api/src/main/java/org/teiid/language/SQLConstants.java 2011-06-08 16:02:59 UTC (rev 3233) +++ trunk/api/src/main/java/org/teiid/language/SQLConstants.java 2011-06-08 16:54:23 UTC (rev 3234) @@ -273,6 +273,7 @@ public static final String NUMERIC = "NUMERIC"; //$NON-NLS-1$ public static final String OBJECT = "OBJECT"; //$NON-NLS-1$ public static final String OF = "OF"; //$NON-NLS-1$ + public static final String OFFSET = "OFFSET"; //$NON-NLS-1$ public static final String OLD = "OLD"; //$NON-NLS-1$ public static final String ON = "ON"; //$NON-NLS-1$ public static final String ONLY = "ONLY"; //$NON-NLS-1$ Modified: trunk/build/kits/jboss-container/teiid-releasenotes.html =================================================================== --- trunk/build/kits/jboss-container/teiid-releasenotes.html 2011-06-08 16:02:59 UTC (rev 3233) +++ trunk/build/kits/jboss-container/teiid-releasenotes.html 2011-06-08 16:54:23 UTC (rev 3234) @@ -30,6 +30,7 @@ <LI><B>MAKEIND Hint</B> - The MAKEIND hint can be used to indicate that the other side of the join should be made dependent. <LI><B>ODBC SSL</B> - added support for SSL encrypted ODBC connections. <LI><B>Reauthentication Statement</B> - SET SESSION AUTHORIZATION can now be used to perform a reauthentication via JDBC or ODBC. + <LI><B>ANSI OFFSET/FETCH FIRST</B> - instead of the limit clause, a standard OFFSET and/or FETCH FIRST/NEXT clause can be used to limit results. </UL> <h2><a name="Compatibility">Compatibility Issues</a></h2> <ul> @@ -38,6 +39,11 @@ <li>Support for using the FROM clause post item hints MAKEDEP/MAKENOTDEP has been deprecated. Use the pre item comment hint syntax instead, e.g. /*+ MAKEDEP */ tbl </ul> +<h4>from 7.4</h4> +<ul> + <li>OFFSET was added as a keyword. +</ul> + <h4>from 7.3</h4> <ul> <li>SYS.PROPERTIES has a new column, ClobValue, to get values exceeding the max string length Modified: trunk/documentation/reference/src/main/docbook/en-US/content/sql_support.xml =================================================================== --- trunk/documentation/reference/src/main/docbook/en-US/content/sql_support.xml 2011-06-08 16:02:59 UTC (rev 3233) +++ trunk/documentation/reference/src/main/docbook/en-US/content/sql_support.xml 2011-06-08 16:54:23 UTC (rev 3234) @@ -445,7 +445,7 @@ </listitem> <listitem> <para> - <link linkend="limit_clause">[LIMIT [offset,] limit]</link> + <link linkend="limit_clause">[(LIMIT ...) | ([OFFSET ...] [FETCH ...])]</link> </para> </listitem> <listitem> @@ -1032,13 +1032,27 @@ <section id="limit_clause"> <title>LIMIT Clause</title> <para> - The LIMIT clause specifies a limit on the number of records returned from the SELECT command. An optional offset (the number of rows to skip) can be specified. + The LIMIT clause specifies a limit on the number of records returned from the SELECT command. An optional offset (the number of rows to skip) can be specified. The LIMIT clause can also be specfied using the SQL 2008 OFFSET/FETCH FIRST clauses. + If an ORDER BY is also specified, it will be applied before the OFFSET/LIMIT are applied. If an ORDER BY is not specified there is generally no guarantee what subset of rows will be returned. </para> <para> Usage: <synopsis label="Usage">LIMIT [offset,] limit</synopsis> + <synopsis label="Usage">[OFFSET offset ROW|ROWS] [FETCH FIRST|NEXT [limit] ROW|ROWS ONLY</synopsis> </para> <itemizedlist> + <para>Syntax Rules: + </para> + <listitem> + <para>The limit/offset expressions must be a non-negative integer or a parameter reference (?). An offset of 0 is ignored. A limit of 0 will return no rows. + </para> + </listitem> + <listitem> + <para>The terms FIRST/NEXT are interchangable as well as ROW/ROWS. + </para> + </listitem> + </itemizedlist> + <itemizedlist> <para>Examples: </para> <listitem> @@ -1047,6 +1061,15 @@ <listitem> <para>LIMIT 500, 100 - skips 500 records and returns the next 100 records (rows 501-600)</para> </listitem> + <listitem> + <para>OFFSET 500 ROWS - skips 500 records</para> + </listitem> + <listitem> + <para>OFFSET 500 ROWS FETCH NEXT 100 ROWS ONLY - skips 500 records and returns the next 100 records (rows 501-600)</para> + </listitem> + <listitem> + <para>FETCH FIRST ROW ONLY - returns only the first record</para> + </listitem> </itemizedlist> </section> <section id="into_clause"> Modified: trunk/engine/src/main/java/org/teiid/dqp/internal/datamgr/LanguageBridgeFactory.java =================================================================== --- trunk/engine/src/main/java/org/teiid/dqp/internal/datamgr/LanguageBridgeFactory.java 2011-06-08 16:02:59 UTC (rev 3233) +++ trunk/engine/src/main/java/org/teiid/dqp/internal/datamgr/LanguageBridgeFactory.java 2011-06-08 16:54:23 UTC (rev 3234) @@ -762,7 +762,10 @@ rowOffset = ((Integer)c1.getValue()).intValue(); } Literal c2 = (Literal)translate(limit.getRowLimit()); - int rowLimit = ((Integer)c2.getValue()).intValue(); + int rowLimit = Integer.MAX_VALUE; + if (c2 != null) { + rowLimit = ((Integer)c2.getValue()).intValue(); + } return new org.teiid.language.Limit(rowOffset, rowLimit); } } Modified: trunk/engine/src/main/java/org/teiid/query/sql/visitor/SQLStringVisitor.java =================================================================== --- trunk/engine/src/main/java/org/teiid/query/sql/visitor/SQLStringVisitor.java 2011-06-08 16:02:59 UTC (rev 3233) +++ trunk/engine/src/main/java/org/teiid/query/sql/visitor/SQLStringVisitor.java 2011-06-08 16:54:23 UTC (rev 3234) @@ -1706,6 +1706,14 @@ } public void visit( Limit obj ) { + if (obj.getRowLimit() == null) { + append(OFFSET); + append(SPACE); + visitNode(obj.getOffset()); + append(SPACE); + append(ROWS); + return; + } append(LIMIT); if (obj.getOffset() != null) { append(SPACE); Modified: trunk/engine/src/main/javacc/org/teiid/query/parser/SQLParser.jj =================================================================== --- trunk/engine/src/main/javacc/org/teiid/query/parser/SQLParser.jj 2011-06-08 16:02:59 UTC (rev 3233) +++ trunk/engine/src/main/javacc/org/teiid/query/parser/SQLParser.jj 2011-06-08 16:54:23 UTC (rev 3234) @@ -213,6 +213,7 @@ | <NOT: "not"> | <NULL: "null"> | <OF: "of"> +| <OFFSET: "offset"> | <OLD: "old"> | <ON: "on"> | <ONLY: "only"> @@ -3157,41 +3158,63 @@ } } +Expression intParam(ParseInfo info) : +{ + Integer val = null; +} +{ + (val = intVal() | <QMARK>) + { + if (val == null) { + return new Reference(info.referenceCount++); + } + return new Constant(val, DataTypeManager.DefaultDataClasses.INTEGER); + } +} + /** * <p>Parse an LIMIT clause.</p> * @return Parsed LIMIT - * @throws ParseException if parsing failed + * @throws ParseException if parsing failed */ Limit limit(ParseInfo info) : { - Token val = null; - Token rowLimit = null; - Token ref = null; - Expression expr1 = null; - Expression expr2 = null; + Expression limit = null; + Expression offset = null; } { - <LIMIT> - (val=<INTEGERVAL> | <QMARK>) - [<COMMA> (rowLimit=<INTEGERVAL> | ref=<QMARK>)] + ((<LIMIT> offset = intParam(info) + [<COMMA> limit = intParam(info)]) { - if (val == null) { - expr1 = new Reference(info.referenceCount++); - } else { - expr1 = new Constant(Integer.valueOf(val.image), DataTypeManager.DefaultDataClasses.INTEGER); - } - if (rowLimit == null && ref == null) { - return new Limit(null, expr1); - } - if (rowLimit == null) { - expr2 = new Reference(info.referenceCount++); - } else { - expr2 = new Constant(Integer.valueOf(rowLimit.image), DataTypeManager.DefaultDataClasses.INTEGER); - } - return new Limit(expr1, expr2); + if (limit == null) { + limit = offset; + offset = null; + } } + | + (<OFFSET> offset = intParam(info) (<ROW>|<ROWS>) + [limit = fetchLimit(info)]) + | + (limit = fetchLimit(info))) + { + return new Limit(offset, limit); + } } +Expression fetchLimit(ParseInfo info) : +{ + Expression limit = null; +} +{ + <FETCH> nonReserved("FIRST", "NEXT") [limit = intParam(info)] (<ROW>|<ROWS>) <ONLY> + { + if (limit == null) { + return new Constant(1, DataTypeManager.DefaultDataClasses.INTEGER); + } + return limit; + } +} + /** * <p>Parse an OPTION clause.</p> * @return Parsed OPTION clause Added: trunk/engine/src/test/java/org/teiid/query/parser/TestLimitParsing.java =================================================================== --- trunk/engine/src/test/java/org/teiid/query/parser/TestLimitParsing.java (rev 0) +++ trunk/engine/src/test/java/org/teiid/query/parser/TestLimitParsing.java 2011-06-08 16:54:23 UTC (rev 3234) @@ -0,0 +1,145 @@ +/* + * 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.parser; + +import static org.teiid.query.parser.TestParser.*; + +import java.util.Arrays; + +import org.junit.Test; +import org.teiid.query.sql.lang.From; +import org.teiid.query.sql.lang.Limit; +import org.teiid.query.sql.lang.Query; +import org.teiid.query.sql.lang.Select; +import org.teiid.query.sql.lang.SetQuery; +import org.teiid.query.sql.lang.UnaryFromClause; +import org.teiid.query.sql.lang.SetQuery.Operation; +import org.teiid.query.sql.symbol.AllSymbol; +import org.teiid.query.sql.symbol.Constant; +import org.teiid.query.sql.symbol.GroupSymbol; +import org.teiid.query.sql.symbol.Reference; + +public class TestLimitParsing { + + @Test public void testLimit() { + Query query = new Query(); + Select select = new Select(Arrays.asList(new AllSymbol())); + From from = new From(Arrays.asList(new UnaryFromClause(new GroupSymbol("a")))); //$NON-NLS-1$ + query.setSelect(select); + query.setFrom(from); + query.setLimit(new Limit(null, new Constant(new Integer(100)))); + helpTest("Select * from a limit 100", "SELECT * FROM a LIMIT 100", query); //$NON-NLS-1$ //$NON-NLS-2$ + } + + @Test public void testLimitWithOffset() { + Query query = new Query(); + Select select = new Select(Arrays.asList(new AllSymbol())); + From from = new From(Arrays.asList(new UnaryFromClause(new GroupSymbol("a")))); //$NON-NLS-1$ + query.setSelect(select); + query.setFrom(from); + query.setLimit(new Limit(new Constant(new Integer(50)), new Constant(new Integer(100)))); + helpTest("Select * from a limit 50,100", "SELECT * FROM a LIMIT 50, 100", query); //$NON-NLS-1$ //$NON-NLS-2$ + } + + @Test public void testLimitWithReferences1() { + Query query = new Query(); + Select select = new Select(Arrays.asList(new AllSymbol())); + From from = new From(Arrays.asList(new UnaryFromClause(new GroupSymbol("a")))); //$NON-NLS-1$ + query.setSelect(select); + query.setFrom(from); + query.setLimit(new Limit(new Reference(0), new Constant(new Integer(100)))); + helpTest("Select * from a limit ?,100", "SELECT * FROM a LIMIT ?, 100", query); //$NON-NLS-1$ //$NON-NLS-2$ + } + + @Test public void testLimitWithReferences2() { + Query query = new Query(); + Select select = new Select(Arrays.asList(new AllSymbol())); + From from = new From(Arrays.asList(new UnaryFromClause(new GroupSymbol("a")))); //$NON-NLS-1$ + query.setSelect(select); + query.setFrom(from); + query.setLimit(new Limit(new Constant(new Integer(50)), new Reference(0))); + helpTest("Select * from a limit 50,?", "SELECT * FROM a LIMIT 50, ?", query); //$NON-NLS-1$ //$NON-NLS-2$ + } + + @Test public void testLimitWithReferences3() { + Query query = new Query(); + Select select = new Select(Arrays.asList(new AllSymbol())); + From from = new From(Arrays.asList(new UnaryFromClause(new GroupSymbol("a")))); //$NON-NLS-1$ + query.setSelect(select); + query.setFrom(from); + query.setLimit(new Limit(new Reference(0), new Reference(1))); + helpTest("Select * from a limit ?,?", "SELECT * FROM a LIMIT ?, ?", query); //$NON-NLS-1$ //$NON-NLS-2$ + } + + @Test public void testSetQueryLimit() { + Query query = new Query(); + Select select = new Select(Arrays.asList(new AllSymbol())); + From from = new From(Arrays.asList(new UnaryFromClause(new GroupSymbol("a")))); //$NON-NLS-1$ + query.setSelect(select); + query.setFrom(from); + SetQuery setQuery = new SetQuery(Operation.UNION, true, query, query); + setQuery.setLimit(new Limit(new Reference(0), new Reference(1))); + helpTest("Select * from a union all Select * from a limit ?,?", "SELECT * FROM a UNION ALL SELECT * FROM a LIMIT ?, ?", setQuery); //$NON-NLS-1$ //$NON-NLS-2$ + } + + @Test public void testOffset() { + Query query = new Query(); + Select select = new Select(Arrays.asList(new AllSymbol())); + From from = new From(Arrays.asList(new UnaryFromClause(new GroupSymbol("a")))); //$NON-NLS-1$ + query.setSelect(select); + query.setFrom(from); + query.setLimit(new Limit(new Reference(0), null)); + helpTest("Select * from a offset ? rows", "SELECT * FROM a OFFSET ? ROWS", query); //$NON-NLS-1$ //$NON-NLS-2$ + } + + @Test public void testFetchFirst() { + Query query = new Query(); + Select select = new Select(Arrays.asList(new AllSymbol())); + From from = new From(Arrays.asList(new UnaryFromClause(new GroupSymbol("a")))); //$NON-NLS-1$ + query.setSelect(select); + query.setFrom(from); + query.setLimit(new Limit(null, new Constant(2))); + helpTest("Select * from a fetch first 2 rows only", "SELECT * FROM a LIMIT 2", query); //$NON-NLS-1$ //$NON-NLS-2$ + } + + @Test public void testFetchFirstRow() { + Query query = new Query(); + Select select = new Select(Arrays.asList(new AllSymbol())); + From from = new From(Arrays.asList(new UnaryFromClause(new GroupSymbol("a")))); //$NON-NLS-1$ + query.setSelect(select); + query.setFrom(from); + query.setLimit(new Limit(null, new Constant(1))); + helpTest("Select * from a fetch first row only", "SELECT * FROM a LIMIT 1", query); //$NON-NLS-1$ //$NON-NLS-2$ + } + + @Test public void testOffsetFetch() { + Query query = new Query(); + Select select = new Select(Arrays.asList(new AllSymbol())); + From from = new From(Arrays.asList(new UnaryFromClause(new GroupSymbol("a")))); //$NON-NLS-1$ + query.setSelect(select); + query.setFrom(from); + query.setLimit(new Limit(new Constant(2), new Constant(5))); + helpTest("Select * from a offset 2 rows fetch first 5 rows only", "SELECT * FROM a LIMIT 2, 5", query); //$NON-NLS-1$ //$NON-NLS-2$ + } + +} Property changes on: trunk/engine/src/test/java/org/teiid/query/parser/TestLimitParsing.java ___________________________________________________________________ Added: svn:mime-type + text/plain Modified: trunk/engine/src/test/java/org/teiid/query/parser/TestParser.java =================================================================== --- trunk/engine/src/test/java/org/teiid/query/parser/TestParser.java 2011-06-08 16:02:59 UTC (rev 3233) +++ trunk/engine/src/test/java/org/teiid/query/parser/TestParser.java 2011-06-08 16:54:23 UTC (rev 3234) @@ -61,7 +61,6 @@ import org.teiid.query.sql.lang.IsNullCriteria; import org.teiid.query.sql.lang.JoinPredicate; import org.teiid.query.sql.lang.JoinType; -import org.teiid.query.sql.lang.Limit; import org.teiid.query.sql.lang.MatchCriteria; import org.teiid.query.sql.lang.NotCriteria; import org.teiid.query.sql.lang.OrderBy; @@ -6434,56 +6433,6 @@ } } - @Test public void testLimit() { - Query query = new Query(); - Select select = new Select(Arrays.asList(new AllSymbol())); - From from = new From(Arrays.asList(new UnaryFromClause(new GroupSymbol("a")))); //$NON-NLS-1$ - query.setSelect(select); - query.setFrom(from); - query.setLimit(new Limit(null, new Constant(new Integer(100)))); - helpTest("Select * from a limit 100", "SELECT * FROM a LIMIT 100", query); //$NON-NLS-1$ //$NON-NLS-2$ - } - - @Test public void testLimitWithOffset() { - Query query = new Query(); - Select select = new Select(Arrays.asList(new AllSymbol())); - From from = new From(Arrays.asList(new UnaryFromClause(new GroupSymbol("a")))); //$NON-NLS-1$ - query.setSelect(select); - query.setFrom(from); - query.setLimit(new Limit(new Constant(new Integer(50)), new Constant(new Integer(100)))); - helpTest("Select * from a limit 50,100", "SELECT * FROM a LIMIT 50, 100", query); //$NON-NLS-1$ //$NON-NLS-2$ - } - - @Test public void testLimitWithReferences1() { - Query query = new Query(); - Select select = new Select(Arrays.asList(new AllSymbol())); - From from = new From(Arrays.asList(new UnaryFromClause(new GroupSymbol("a")))); //$NON-NLS-1$ - query.setSelect(select); - query.setFrom(from); - query.setLimit(new Limit(new Reference(0), new Constant(new Integer(100)))); - helpTest("Select * from a limit ?,100", "SELECT * FROM a LIMIT ?, 100", query); //$NON-NLS-1$ //$NON-NLS-2$ - } - - @Test public void testLimitWithReferences2() { - Query query = new Query(); - Select select = new Select(Arrays.asList(new AllSymbol())); - From from = new From(Arrays.asList(new UnaryFromClause(new GroupSymbol("a")))); //$NON-NLS-1$ - query.setSelect(select); - query.setFrom(from); - query.setLimit(new Limit(new Constant(new Integer(50)), new Reference(0))); - helpTest("Select * from a limit 50,?", "SELECT * FROM a LIMIT 50, ?", query); //$NON-NLS-1$ //$NON-NLS-2$ - } - - @Test public void testLimitWithReferences3() { - Query query = new Query(); - Select select = new Select(Arrays.asList(new AllSymbol())); - From from = new From(Arrays.asList(new UnaryFromClause(new GroupSymbol("a")))); //$NON-NLS-1$ - query.setSelect(select); - query.setFrom(from); - query.setLimit(new Limit(new Reference(0), new Reference(1))); - helpTest("Select * from a limit ?,?", "SELECT * FROM a LIMIT ?, ?", query); //$NON-NLS-1$ //$NON-NLS-2$ - } - @Test public void testEmptyOuterJoinCriteria() { helpException("select a from b left outer join c on ()"); //$NON-NLS-1$ } Modified: trunk/engine/src/test/java/org/teiid/query/processor/TestProcessor.java =================================================================== --- trunk/engine/src/test/java/org/teiid/query/processor/TestProcessor.java 2011-06-08 16:02:59 UTC (rev 3233) +++ trunk/engine/src/test/java/org/teiid/query/processor/TestProcessor.java 2011-06-08 16:54:23 UTC (rev 3234) @@ -6740,7 +6740,28 @@ helpProcess(plan, manager, expected); assertEquals("SELECT g_0.e3 AS c_0, g_0.e2 AS c_1 FROM pm1.g1 AS g_0 ORDER BY c_1", manager.getQueries().iterator().next()); //$NON-NLS-1$ } + + @Test public void testSortWithOffset() { + String sql = "select e1 from (select pm1.g1.e1, pm1.g1.e2 from pm1.g1 order by pm1.g1.e1, pm1.g1.e2 offset 4 rows) x"; //$NON-NLS-1$ + + QueryMetadataInterface metadata = RealMetadataFactory.example1Cached(); + FakeCapabilitiesFinder capFinder = new FakeCapabilitiesFinder(); + BasicSourceCapabilities caps = new BasicSourceCapabilities(); + caps.setCapabilitySupport(Capability.ROW_OFFSET, true); + caps.setCapabilitySupport(Capability.QUERY_ORDERBY, true); + capFinder.addCapabilities("pm1", caps); //$NON-NLS-1$ + + ProcessorPlan plan = helpGetPlan(helpParse(sql), metadata, capFinder); + + List[] expected = new List[] { + Arrays.asList(new Object[] { "a"}), + }; + HardcodedDataManager manager = new HardcodedDataManager(RealMetadataFactory.example1Cached()); + manager.addData("SELECT g1.e1, g1.e2 FROM g1 ORDER BY g1.e1, g1.e2 LIMIT 4, 2147483647", new List[] {Arrays.asList("a", 1)}); + helpProcess(plan, manager, expected); + } + @Test public void testCountWithHaving() { String sql = "select e1, count(*) from pm1.g1 group by e1 having count(*) > 1"; //$NON-NLS-1$ Modified: trunk/engine/src/test/java/org/teiid/query/processor/relational/TestLimitNode.java =================================================================== --- trunk/engine/src/test/java/org/teiid/query/processor/relational/TestLimitNode.java 2011-06-08 16:02:59 UTC (rev 3233) +++ trunk/engine/src/test/java/org/teiid/query/processor/relational/TestLimitNode.java 2011-06-08 16:54:23 UTC (rev 3234) @@ -239,8 +239,8 @@ assertTrue(batch.getTerminationFlag()); } - static List[] getRows(int rows) { - List[] data = new List[rows]; + static List<?>[] getRows(int rows) { + List<?>[] data = new List[rows]; for (int i = 0; i < rows; i++) { data[i] = Arrays.asList(new Object[] {new Integer(i+1)}); }