This is an automated email from the ASF dual-hosted git repository.
hansva pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/hop.git
The following commit(s) were added to refs/heads/main by this push:
new 3b3dc8fdf4 Let a database limit rows before the query as well as after
it, fixes #8013 (#8032)
3b3dc8fdf4 is described below
commit 3b3dc8fdf40babae47ddfe4e644dbd1269847edc
Author: Hans Van Akelyen <[email protected]>
AuthorDate: Fri Aug 21 09:28:17 2026 +0200
Let a database limit rows before the query as well as after it, fixes #8013
(#8032)
---
.../org/apache/hop/core/database/Database.java | 8 +-
.../org/apache/hop/core/database/DatabaseMeta.java | 4 +
.../org/apache/hop/core/database/IDatabase.java | 16 ++++
.../hop/core/database/FirstRowsLimitTest.java | 98 ++++++++++++++++++++++
.../ROOT/pages/database/creating-a-dialect.adoc | 8 +-
.../hop/databases/access/AccessDatabaseMeta.java | 7 ++
.../databases/access/AccessLimitClauseTest.java | 36 ++++++++
.../hop/databases/as400/AS400DatabaseMeta.java | 7 ++
.../hop/databases/as400/AS400LimitClauseTest.java | 36 ++++++++
.../hop/databases/cache/CacheDatabaseMeta.java | 6 ++
.../hop/databases/cache/CacheLimitClauseTest.java | 36 ++++++++
.../apache/hop/databases/db2/DB2DatabaseMeta.java | 6 ++
.../hop/databases/db2/DB2LimitClauseTest.java | 36 ++++++++
.../hop/databases/derby/DerbyDatabaseMeta.java | 7 ++
.../hop/databases/derby/DerbyLimitClauseTest.java | 36 ++++++++
.../hop/databases/duckdb/DuckDBDatabaseMeta.java | 6 ++
.../databases/duckdb/DuckDBLimitClauseTest.java | 36 ++++++++
.../databases/firebird/FirebirdDatabaseMeta.java | 6 ++
.../firebird/FirebirdLimitClauseTest.java | 36 ++++++++
.../apache/hop/databases/h2/H2DatabaseMeta.java | 7 ++
.../apache/hop/databases/h2/H2LimitClauseTest.java | 36 ++++++++
.../hypersonic/HypersonicDatabaseMeta.java | 7 ++
.../hypersonic/HypersonicLimitClauseTest.java | 36 ++++++++
.../hop/databases/impala/ImpalaDatabaseMeta.java | 7 ++
.../databases/impala/ImpalaLimitClauseTest.java | 36 ++++++++
.../databases/informix/InformixDatabaseMeta.java | 6 ++
.../informix/InformixLimitClauseTest.java | 36 ++++++++
.../databases/interbase/InterbaseDatabaseMeta.java | 6 ++
.../interbase/InterbaseLimitClauseTest.java | 36 ++++++++
.../hop/databases/iris/IrisDatabaseMeta.java | 6 ++
.../hop/databases/iris/IrisLimitClauseTest.java | 36 ++++++++
.../hop/databases/monetdb/MonetDBDatabaseMeta.java | 6 ++
.../databases/monetdb/MonetDBLimitClauseTest.java | 36 ++++++++
.../databases/mssql/MsSqlServerDatabaseMeta.java | 10 +++
.../mssql/MsSqlServerLimitClauseTest.java | 36 ++++++++
.../hop/databases/sqlite/SqliteDatabaseMeta.java | 6 ++
.../databases/sqlite/SqliteLimitClauseTest.java | 36 ++++++++
.../hop/databases/sybase/SybaseDatabaseMeta.java | 7 ++
.../databases/sybase/SybaseLimitClauseTest.java | 36 ++++++++
.../databases/sybaseiq/SybaseIQDatabaseMeta.java | 14 +---
.../sybaseiq/SybaseIQLimitClauseTest.java | 36 ++++++++
.../databases/teradata/TeradataDatabaseMeta.java | 6 ++
.../teradata/TeradataLimitClauseTest.java | 36 ++++++++
43 files changed, 936 insertions(+), 15 deletions(-)
diff --git a/core/src/main/java/org/apache/hop/core/database/Database.java
b/core/src/main/java/org/apache/hop/core/database/Database.java
index 3b9ed239b3..0993104ef5 100644
--- a/core/src/main/java/org/apache/hop/core/database/Database.java
+++ b/core/src/main/java/org/apache/hop/core/database/Database.java
@@ -3716,9 +3716,11 @@ public class Database implements IVariables,
ILoggingObject, AutoCloseable {
public List<Object[]> getFirstRows(String tableName, int limit,
IProgressMonitor monitor)
throws HopDatabaseException {
// How a database limits rows is its own syntax, so it is asked rather
than recognised here.
- // The row count is capped while reading in any case, so a dialect with no
limit clause simply
- // fetches a little more than it needs.
- String sql = "SELECT * FROM " + tableName;
+ // Some put the clause directly after SELECT and others at the end of the
statement, so both
+ // are asked for. The row count is capped while reading in any case, so a
dialect offering
+ // neither simply fetches a little more than it needs.
+ String prefix = limit > 0 ? databaseMeta.getLimitClausePrefix(limit) : "";
+ String sql = "SELECT" + prefix + " * FROM " + tableName;
if (limit > 0) {
sql += databaseMeta.getLimitClause(limit);
diff --git a/core/src/main/java/org/apache/hop/core/database/DatabaseMeta.java
b/core/src/main/java/org/apache/hop/core/database/DatabaseMeta.java
index 9ecf163eda..12c23dc364 100644
--- a/core/src/main/java/org/apache/hop/core/database/DatabaseMeta.java
+++ b/core/src/main/java/org/apache/hop/core/database/DatabaseMeta.java
@@ -1177,6 +1177,10 @@ public class DatabaseMeta extends HopMetadataBase
implements Cloneable, IHopMeta
return iDatabase.getLimitClause(nrRows);
}
+ public String getLimitClausePrefix(int nrRows) {
+ return iDatabase.getLimitClausePrefix(nrRows);
+ }
+
/**
* @param tableName The table or schema-table combination. We expect this to
be quoted properly
* already!
diff --git a/core/src/main/java/org/apache/hop/core/database/IDatabase.java
b/core/src/main/java/org/apache/hop/core/database/IDatabase.java
index ecca2f56ff..845ea47507 100644
--- a/core/src/main/java/org/apache/hop/core/database/IDatabase.java
+++ b/core/src/main/java/org/apache/hop/core/database/IDatabase.java
@@ -239,6 +239,22 @@ public interface IDatabase extends Cloneable {
*/
String getLimitClause(int nrRows);
+ /**
+ * The clause that limits the number of rows when the database puts it
directly after SELECT,
+ * rather than at the end of the statement.
+ *
+ * <p>SQL Server and Sybase IQ write {@code SELECT TOP 10 * FROM t}, where
PostgreSQL writes
+ * {@code SELECT * FROM t LIMIT 10}. A database uses one form or the other,
so a dialect overrides
+ * this or {@link #getLimitClause(int)}, not both.
+ *
+ * @param nrRows the number of rows to limit the result to
+ * @return the clause to place after SELECT, with a leading space, or an
empty string when this
+ * database limits rows at the end of the statement instead
+ */
+ default String getLimitClausePrefix(int nrRows) {
+ return "";
+ }
+
/**
* Returns the minimal SQL to launch in order to determine the layout of the
resultset for a given
* database table
diff --git
a/core/src/test/java/org/apache/hop/core/database/FirstRowsLimitTest.java
b/core/src/test/java/org/apache/hop/core/database/FirstRowsLimitTest.java
new file mode 100644
index 0000000000..a99c89b4d4
--- /dev/null
+++ b/core/src/test/java/org/apache/hop/core/database/FirstRowsLimitTest.java
@@ -0,0 +1,98 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one or more
+ * contributor license agreements. See the NOTICE file distributed with
+ * this work for additional information regarding copyright ownership.
+ * The ASF licenses this file to You under the Apache License, Version 2.0
+ * (the "License"); you may not use this file except in compliance with
+ * the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+
+package org.apache.hop.core.database;
+
+import static org.junit.jupiter.api.Assertions.assertEquals;
+import static org.mockito.ArgumentMatchers.any;
+import static org.mockito.ArgumentMatchers.anyInt;
+import static org.mockito.ArgumentMatchers.anyString;
+import static org.mockito.ArgumentMatchers.eq;
+import static org.mockito.Mockito.doReturn;
+import static org.mockito.Mockito.mock;
+import static org.mockito.Mockito.spy;
+import static org.mockito.Mockito.verify;
+import static org.mockito.Mockito.when;
+
+import java.util.List;
+import org.apache.hop.core.HopClientEnvironment;
+import org.apache.hop.core.logging.ILoggingObject;
+import org.apache.hop.core.logging.LogLevel;
+import org.apache.hop.core.variables.Variables;
+import org.junit.jupiter.api.BeforeAll;
+import org.junit.jupiter.api.Test;
+import org.mockito.ArgumentCaptor;
+
+/**
+ * getFirstRows has to work for databases that limit rows after SELECT as well
as those that limit
+ * at the end of the statement. See issue 8013.
+ *
+ * <p>The statement is captured on its way to getRows, so this exercises the
real method rather than
+ * a copy of what it is believed to build.
+ */
+class FirstRowsLimitTest {
+
+ @BeforeAll
+ static void setUpClass() throws Exception {
+ HopClientEnvironment.init();
+ }
+
+ private String firstRowsSql(String prefix, String suffix, int limit) throws
Exception {
+ IDatabase dialect = mock(IDatabase.class);
+ when(dialect.getLimitClausePrefix(limit)).thenReturn(prefix);
+ when(dialect.getLimitClause(limit)).thenReturn(suffix);
+ DatabaseMeta databaseMeta = new DatabaseMeta();
+ databaseMeta.setIDatabase(dialect);
+
+ ILoggingObject log = mock(ILoggingObject.class);
+ when(log.getLogLevel()).thenReturn(LogLevel.NOTHING);
+
+ Database database = spy(new Database(log, new Variables(), databaseMeta));
+ doReturn(List.of()).when(database).getRows(anyString(), anyInt(), any());
+
+ database.getFirstRows("CUSTOMER", limit, null);
+
+ ArgumentCaptor<String> sql = ArgumentCaptor.forClass(String.class);
+ verify(database).getRows(sql.capture(), eq(limit), any());
+ return sql.getValue();
+ }
+
+ @Test
+ void aDatabaseThatLimitsAfterSelectGetsItsClauseThere() throws Exception {
+ assertEquals("SELECT TOP 10 * FROM CUSTOMER", firstRowsSql(" TOP 10", "",
10));
+ }
+
+ @Test
+ void aDatabaseThatLimitsAtTheEndIsUnaffected() throws Exception {
+ assertEquals("SELECT * FROM CUSTOMER LIMIT 10", firstRowsSql("", " LIMIT
10", 10));
+ }
+
+ @Test
+ void aDatabaseWithNoLimitClauseAtAllStillProducesValidSql() throws Exception
{
+ assertEquals("SELECT * FROM CUSTOMER", firstRowsSql("", "", 10));
+ }
+
+ @Test
+ void noLimitMeansNeitherClauseIsAskedFor() throws Exception {
+ assertEquals("SELECT * FROM CUSTOMER", firstRowsSql(" TOP 10", " LIMIT
10", 0));
+ }
+
+ @Test
+ void theDefaultForADialectThatDeclaresNothingIsEmpty() {
+ assertEquals("", new NoneDatabaseMeta().getLimitClausePrefix(10));
+ }
+}
diff --git
a/docs/hop-dev-manual/modules/ROOT/pages/database/creating-a-dialect.adoc
b/docs/hop-dev-manual/modules/ROOT/pages/database/creating-a-dialect.adoc
index 3a8c4b1223..be55bf25c8 100644
--- a/docs/hop-dev-manual/modules/ROOT/pages/database/creating-a-dialect.adoc
+++ b/docs/hop-dev-manual/modules/ROOT/pages/database/creating-a-dialect.adoc
@@ -95,9 +95,15 @@ They are ordinary methods with sensible defaults, and the
ones that matter most
|Set this false for databases that invalidate a statement, or the whole
connection, when a statement fails. SQLite is the example in the tree.
|`getLimitClause(int)`
-|The clause appended *after* the FROM clause to limit rows, such as `" LIMIT
10"`. It is a suffix; databases that limit rows with a prefix such as `TOP`
cannot express it here.
+|The clause appended *after* the FROM clause to limit rows, such as `" LIMIT
10"`.
+
+|`getLimitClausePrefix(int)`
+|The clause placed directly after `SELECT` instead, such as `" TOP 10"`.
Databases use one form or the other, so override this or `getLimitClause`, not
both.
|===
+Both include their own leading space, and both default to returning nothing.
+A dialect that offers neither still works: the row count is capped while
reading, so it simply fetches a little more than it needs.
+
Prefer overriding a capability over overriding behaviour.
A capability is a fact about the database that Hop can act on in several
places; overridden behaviour only fixes the one place you overrode.
diff --git
a/plugins/databases/access/src/main/java/org/apache/hop/databases/access/AccessDatabaseMeta.java
b/plugins/databases/access/src/main/java/org/apache/hop/databases/access/AccessDatabaseMeta.java
index aa6110aa86..f851c67344 100644
---
a/plugins/databases/access/src/main/java/org/apache/hop/databases/access/AccessDatabaseMeta.java
+++
b/plugins/databases/access/src/main/java/org/apache/hop/databases/access/AccessDatabaseMeta.java
@@ -37,6 +37,13 @@ import org.apache.hop.core.row.IValueMeta;
classLoaderGroup = "access-db")
@GuiPlugin(id = "GUI-MSAccessDatabaseMeta")
public class AccessDatabaseMeta extends BaseDatabaseMeta implements IDatabase {
+
+ /** Access limits rows with TOP, between SELECT and the column list. */
+ @Override
+ public String getLimitClausePrefix(int nrRows) {
+ return " TOP " + nrRows;
+ }
+
@Override
public int[] getAccessTypeList() {
return new int[] {DatabaseMeta.TYPE_ACCESS_NATIVE};
diff --git
a/plugins/databases/access/src/test/java/org/apache/hop/databases/access/AccessLimitClauseTest.java
b/plugins/databases/access/src/test/java/org/apache/hop/databases/access/AccessLimitClauseTest.java
new file mode 100644
index 0000000000..02cc016ba0
--- /dev/null
+++
b/plugins/databases/access/src/test/java/org/apache/hop/databases/access/AccessLimitClauseTest.java
@@ -0,0 +1,36 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one or more
+ * contributor license agreements. See the NOTICE file distributed with
+ * this work for additional information regarding copyright ownership.
+ * The ASF licenses this file to You under the Apache License, Version 2.0
+ * (the "License"); you may not use this file except in compliance with
+ * the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+
+package org.apache.hop.databases.access;
+
+import static org.junit.jupiter.api.Assertions.assertEquals;
+
+import org.junit.jupiter.api.Test;
+
+/** This database limits rows after SELECT. See issue 8013. */
+class AccessLimitClauseTest {
+
+ @Test
+ void rowsAreLimitedAfterSelect() {
+ assertEquals(" TOP 5", new AccessDatabaseMeta().getLimitClausePrefix(5));
+ }
+
+ @Test
+ void theOtherFormIsNotUsed() {
+ assertEquals("", new AccessDatabaseMeta().getLimitClause(5));
+ }
+}
diff --git
a/plugins/databases/as400/src/main/java/org/apache/hop/databases/as400/AS400DatabaseMeta.java
b/plugins/databases/as400/src/main/java/org/apache/hop/databases/as400/AS400DatabaseMeta.java
index 40bb6ae1a9..fbbf13b040 100644
---
a/plugins/databases/as400/src/main/java/org/apache/hop/databases/as400/AS400DatabaseMeta.java
+++
b/plugins/databases/as400/src/main/java/org/apache/hop/databases/as400/AS400DatabaseMeta.java
@@ -36,6 +36,13 @@ import org.apache.hop.core.row.IValueMeta;
classLoaderGroup = "as400-db")
@GuiPlugin(id = "GUI-AS400DatabaseMeta")
public class AS400DatabaseMeta extends BaseDatabaseMeta implements IDatabase {
+
+ /** DB2 for i limits rows at the end of the statement. */
+ @Override
+ public String getLimitClause(int nrRows) {
+ return " FETCH FIRST " + nrRows + " ROWS ONLY";
+ }
+
@Override
public int[] getAccessTypeList() {
return new int[] {DatabaseMeta.TYPE_ACCESS_NATIVE};
diff --git
a/plugins/databases/as400/src/test/java/org/apache/hop/databases/as400/AS400LimitClauseTest.java
b/plugins/databases/as400/src/test/java/org/apache/hop/databases/as400/AS400LimitClauseTest.java
new file mode 100644
index 0000000000..b0fc9815f5
--- /dev/null
+++
b/plugins/databases/as400/src/test/java/org/apache/hop/databases/as400/AS400LimitClauseTest.java
@@ -0,0 +1,36 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one or more
+ * contributor license agreements. See the NOTICE file distributed with
+ * this work for additional information regarding copyright ownership.
+ * The ASF licenses this file to You under the Apache License, Version 2.0
+ * (the "License"); you may not use this file except in compliance with
+ * the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+
+package org.apache.hop.databases.as400;
+
+import static org.junit.jupiter.api.Assertions.assertEquals;
+
+import org.junit.jupiter.api.Test;
+
+/** This database limits rows at the end of the statement. See issue 8013. */
+class AS400LimitClauseTest {
+
+ @Test
+ void rowsAreLimitedAtTheEnd() {
+ assertEquals(" FETCH FIRST 5 ROWS ONLY", new
AS400DatabaseMeta().getLimitClause(5));
+ }
+
+ @Test
+ void theOtherFormIsNotUsed() {
+ assertEquals("", new AS400DatabaseMeta().getLimitClausePrefix(5));
+ }
+}
diff --git
a/plugins/databases/cache/src/main/java/org/apache/hop/databases/cache/CacheDatabaseMeta.java
b/plugins/databases/cache/src/main/java/org/apache/hop/databases/cache/CacheDatabaseMeta.java
index f0f4824662..936a707aa0 100644
---
a/plugins/databases/cache/src/main/java/org/apache/hop/databases/cache/CacheDatabaseMeta.java
+++
b/plugins/databases/cache/src/main/java/org/apache/hop/databases/cache/CacheDatabaseMeta.java
@@ -36,6 +36,12 @@ import org.apache.hop.core.row.IValueMeta;
@GuiPlugin(id = "GUI-CacheDatabaseMeta")
public class CacheDatabaseMeta extends BaseDatabaseMeta implements IDatabase {
+ /** Cache limits rows with TOP, between SELECT and the column list. */
+ @Override
+ public String getLimitClausePrefix(int nrRows) {
+ return " TOP " + nrRows;
+ }
+
public static final String CONST_ALTER_TABLE = "ALTER TABLE ";
@Override
diff --git
a/plugins/databases/cache/src/test/java/org/apache/hop/databases/cache/CacheLimitClauseTest.java
b/plugins/databases/cache/src/test/java/org/apache/hop/databases/cache/CacheLimitClauseTest.java
new file mode 100644
index 0000000000..0e3b19337e
--- /dev/null
+++
b/plugins/databases/cache/src/test/java/org/apache/hop/databases/cache/CacheLimitClauseTest.java
@@ -0,0 +1,36 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one or more
+ * contributor license agreements. See the NOTICE file distributed with
+ * this work for additional information regarding copyright ownership.
+ * The ASF licenses this file to You under the Apache License, Version 2.0
+ * (the "License"); you may not use this file except in compliance with
+ * the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+
+package org.apache.hop.databases.cache;
+
+import static org.junit.jupiter.api.Assertions.assertEquals;
+
+import org.junit.jupiter.api.Test;
+
+/** This database limits rows after SELECT. See issue 8013. */
+class CacheLimitClauseTest {
+
+ @Test
+ void rowsAreLimitedAfterSelect() {
+ assertEquals(" TOP 5", new CacheDatabaseMeta().getLimitClausePrefix(5));
+ }
+
+ @Test
+ void theOtherFormIsNotUsed() {
+ assertEquals("", new CacheDatabaseMeta().getLimitClause(5));
+ }
+}
diff --git
a/plugins/databases/db2/src/main/java/org/apache/hop/databases/db2/DB2DatabaseMeta.java
b/plugins/databases/db2/src/main/java/org/apache/hop/databases/db2/DB2DatabaseMeta.java
index 9436a8ccf9..650866139a 100644
---
a/plugins/databases/db2/src/main/java/org/apache/hop/databases/db2/DB2DatabaseMeta.java
+++
b/plugins/databases/db2/src/main/java/org/apache/hop/databases/db2/DB2DatabaseMeta.java
@@ -37,6 +37,12 @@ import org.apache.hop.core.row.IValueMeta;
@GuiPlugin(id = "GUI-DB2DatabaseMeta")
public class DB2DatabaseMeta extends BaseDatabaseMeta implements IDatabase {
+ /** DB2 limits rows at the end of the statement. */
+ @Override
+ public String getLimitClause(int nrRows) {
+ return " FETCH FIRST " + nrRows + " ROWS ONLY";
+ }
+
private static final String ALTER_TABLE = "ALTER TABLE ";
@Override
diff --git
a/plugins/databases/db2/src/test/java/org/apache/hop/databases/db2/DB2LimitClauseTest.java
b/plugins/databases/db2/src/test/java/org/apache/hop/databases/db2/DB2LimitClauseTest.java
new file mode 100644
index 0000000000..0bbb2aa02f
--- /dev/null
+++
b/plugins/databases/db2/src/test/java/org/apache/hop/databases/db2/DB2LimitClauseTest.java
@@ -0,0 +1,36 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one or more
+ * contributor license agreements. See the NOTICE file distributed with
+ * this work for additional information regarding copyright ownership.
+ * The ASF licenses this file to You under the Apache License, Version 2.0
+ * (the "License"); you may not use this file except in compliance with
+ * the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+
+package org.apache.hop.databases.db2;
+
+import static org.junit.jupiter.api.Assertions.assertEquals;
+
+import org.junit.jupiter.api.Test;
+
+/** This database limits rows at the end of the statement. See issue 8013. */
+class DB2LimitClauseTest {
+
+ @Test
+ void rowsAreLimitedAtTheEnd() {
+ assertEquals(" FETCH FIRST 5 ROWS ONLY", new
DB2DatabaseMeta().getLimitClause(5));
+ }
+
+ @Test
+ void theOtherFormIsNotUsed() {
+ assertEquals("", new DB2DatabaseMeta().getLimitClausePrefix(5));
+ }
+}
diff --git
a/plugins/databases/derby/src/main/java/org/apache/hop/databases/derby/DerbyDatabaseMeta.java
b/plugins/databases/derby/src/main/java/org/apache/hop/databases/derby/DerbyDatabaseMeta.java
index 60476c7ae8..1fb2410ca2 100644
---
a/plugins/databases/derby/src/main/java/org/apache/hop/databases/derby/DerbyDatabaseMeta.java
+++
b/plugins/databases/derby/src/main/java/org/apache/hop/databases/derby/DerbyDatabaseMeta.java
@@ -36,6 +36,13 @@ import org.apache.hop.core.util.Utils;
classLoaderGroup = "derby-db")
@GuiPlugin(id = "GUI-DerbyDatabaseMeta")
public class DerbyDatabaseMeta extends BaseDatabaseMeta implements IDatabase {
+
+ /** Derby limits rows at the end of the statement. */
+ @Override
+ public String getLimitClause(int nrRows) {
+ return " FETCH FIRST " + nrRows + " ROWS ONLY";
+ }
+
@Override
public int[] getAccessTypeList() {
return new int[] {DatabaseMeta.TYPE_ACCESS_NATIVE};
diff --git
a/plugins/databases/derby/src/test/java/org/apache/hop/databases/derby/DerbyLimitClauseTest.java
b/plugins/databases/derby/src/test/java/org/apache/hop/databases/derby/DerbyLimitClauseTest.java
new file mode 100644
index 0000000000..884b1199be
--- /dev/null
+++
b/plugins/databases/derby/src/test/java/org/apache/hop/databases/derby/DerbyLimitClauseTest.java
@@ -0,0 +1,36 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one or more
+ * contributor license agreements. See the NOTICE file distributed with
+ * this work for additional information regarding copyright ownership.
+ * The ASF licenses this file to You under the Apache License, Version 2.0
+ * (the "License"); you may not use this file except in compliance with
+ * the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+
+package org.apache.hop.databases.derby;
+
+import static org.junit.jupiter.api.Assertions.assertEquals;
+
+import org.junit.jupiter.api.Test;
+
+/** This database limits rows at the end of the statement. See issue 8013. */
+class DerbyLimitClauseTest {
+
+ @Test
+ void rowsAreLimitedAtTheEnd() {
+ assertEquals(" FETCH FIRST 5 ROWS ONLY", new
DerbyDatabaseMeta().getLimitClause(5));
+ }
+
+ @Test
+ void theOtherFormIsNotUsed() {
+ assertEquals("", new DerbyDatabaseMeta().getLimitClausePrefix(5));
+ }
+}
diff --git
a/plugins/databases/duckdb/src/main/java/org/apache/hop/databases/duckdb/DuckDBDatabaseMeta.java
b/plugins/databases/duckdb/src/main/java/org/apache/hop/databases/duckdb/DuckDBDatabaseMeta.java
index 039b817e5d..00794c691c 100644
---
a/plugins/databases/duckdb/src/main/java/org/apache/hop/databases/duckdb/DuckDBDatabaseMeta.java
+++
b/plugins/databases/duckdb/src/main/java/org/apache/hop/databases/duckdb/DuckDBDatabaseMeta.java
@@ -41,6 +41,12 @@ import org.apache.hop.core.row.IValueMeta;
@GuiPlugin(id = "GUI-DuckDBDatabaseMeta")
public class DuckDBDatabaseMeta extends BaseDatabaseMeta implements IDatabase {
+ /** DuckDB limits rows at the end of the statement. */
+ @Override
+ public String getLimitClause(int nrRows) {
+ return " LIMIT " + nrRows;
+ }
+
private static final List<IDatabaseTypeRule> TYPE_RULES =
DatabaseTypes.rules()
// As of DuckDB JDBC 0.10.0 the Calendar overloads of setDate and
setTimestamp are not
diff --git
a/plugins/databases/duckdb/src/test/java/org/apache/hop/databases/duckdb/DuckDBLimitClauseTest.java
b/plugins/databases/duckdb/src/test/java/org/apache/hop/databases/duckdb/DuckDBLimitClauseTest.java
new file mode 100644
index 0000000000..eb0015f2b9
--- /dev/null
+++
b/plugins/databases/duckdb/src/test/java/org/apache/hop/databases/duckdb/DuckDBLimitClauseTest.java
@@ -0,0 +1,36 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one or more
+ * contributor license agreements. See the NOTICE file distributed with
+ * this work for additional information regarding copyright ownership.
+ * The ASF licenses this file to You under the Apache License, Version 2.0
+ * (the "License"); you may not use this file except in compliance with
+ * the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+
+package org.apache.hop.databases.duckdb;
+
+import static org.junit.jupiter.api.Assertions.assertEquals;
+
+import org.junit.jupiter.api.Test;
+
+/** This database limits rows at the end of the statement. See issue 8013. */
+class DuckDBLimitClauseTest {
+
+ @Test
+ void rowsAreLimitedAtTheEnd() {
+ assertEquals(" LIMIT 5", new DuckDBDatabaseMeta().getLimitClause(5));
+ }
+
+ @Test
+ void theOtherFormIsNotUsed() {
+ assertEquals("", new DuckDBDatabaseMeta().getLimitClausePrefix(5));
+ }
+}
diff --git
a/plugins/databases/firebird/src/main/java/org/apache/hop/databases/firebird/FirebirdDatabaseMeta.java
b/plugins/databases/firebird/src/main/java/org/apache/hop/databases/firebird/FirebirdDatabaseMeta.java
index 3b34b1c8bd..813304f52f 100644
---
a/plugins/databases/firebird/src/main/java/org/apache/hop/databases/firebird/FirebirdDatabaseMeta.java
+++
b/plugins/databases/firebird/src/main/java/org/apache/hop/databases/firebird/FirebirdDatabaseMeta.java
@@ -38,6 +38,12 @@ import org.apache.hop.core.row.IValueMeta;
@GuiPlugin(id = "GUI-FirebirdDatabaseMeta")
public class FirebirdDatabaseMeta extends BaseDatabaseMeta implements
IDatabase {
+ /** Firebird limits rows with FIRST, between SELECT and the column list. */
+ @Override
+ public String getLimitClausePrefix(int nrRows) {
+ return " FIRST " + nrRows;
+ }
+
public static final String CONST_TIMESTAMP = "TIMESTAMP";
public static final String CONST_SMALLINT = "SMALLINT";
public static final String CONST_INTEGER = "INTEGER";
diff --git
a/plugins/databases/firebird/src/test/java/org/apache/hop/databases/firebird/FirebirdLimitClauseTest.java
b/plugins/databases/firebird/src/test/java/org/apache/hop/databases/firebird/FirebirdLimitClauseTest.java
new file mode 100644
index 0000000000..cd89a34949
--- /dev/null
+++
b/plugins/databases/firebird/src/test/java/org/apache/hop/databases/firebird/FirebirdLimitClauseTest.java
@@ -0,0 +1,36 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one or more
+ * contributor license agreements. See the NOTICE file distributed with
+ * this work for additional information regarding copyright ownership.
+ * The ASF licenses this file to You under the Apache License, Version 2.0
+ * (the "License"); you may not use this file except in compliance with
+ * the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+
+package org.apache.hop.databases.firebird;
+
+import static org.junit.jupiter.api.Assertions.assertEquals;
+
+import org.junit.jupiter.api.Test;
+
+/** This database limits rows after SELECT. See issue 8013. */
+class FirebirdLimitClauseTest {
+
+ @Test
+ void rowsAreLimitedAfterSelect() {
+ assertEquals(" FIRST 5", new
FirebirdDatabaseMeta().getLimitClausePrefix(5));
+ }
+
+ @Test
+ void theOtherFormIsNotUsed() {
+ assertEquals("", new FirebirdDatabaseMeta().getLimitClause(5));
+ }
+}
diff --git
a/plugins/databases/h2/src/main/java/org/apache/hop/databases/h2/H2DatabaseMeta.java
b/plugins/databases/h2/src/main/java/org/apache/hop/databases/h2/H2DatabaseMeta.java
index 7534d52d4e..e012d7bf23 100644
---
a/plugins/databases/h2/src/main/java/org/apache/hop/databases/h2/H2DatabaseMeta.java
+++
b/plugins/databases/h2/src/main/java/org/apache/hop/databases/h2/H2DatabaseMeta.java
@@ -36,6 +36,13 @@ import org.apache.hop.core.util.Utils;
classLoaderGroup = "h2-db")
@GuiPlugin(id = "GUI-H2DatabaseMeta")
public class H2DatabaseMeta extends BaseDatabaseMeta implements IDatabase {
+
+ /** H2 limits rows at the end of the statement. */
+ @Override
+ public String getLimitClause(int nrRows) {
+ return " LIMIT " + nrRows;
+ }
+
@Override
public int[] getAccessTypeList() {
return new int[] {DatabaseMeta.TYPE_ACCESS_NATIVE};
diff --git
a/plugins/databases/h2/src/test/java/org/apache/hop/databases/h2/H2LimitClauseTest.java
b/plugins/databases/h2/src/test/java/org/apache/hop/databases/h2/H2LimitClauseTest.java
new file mode 100644
index 0000000000..307886dc49
--- /dev/null
+++
b/plugins/databases/h2/src/test/java/org/apache/hop/databases/h2/H2LimitClauseTest.java
@@ -0,0 +1,36 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one or more
+ * contributor license agreements. See the NOTICE file distributed with
+ * this work for additional information regarding copyright ownership.
+ * The ASF licenses this file to You under the Apache License, Version 2.0
+ * (the "License"); you may not use this file except in compliance with
+ * the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+
+package org.apache.hop.databases.h2;
+
+import static org.junit.jupiter.api.Assertions.assertEquals;
+
+import org.junit.jupiter.api.Test;
+
+/** This database limits rows at the end of the statement. See issue 8013. */
+class H2LimitClauseTest {
+
+ @Test
+ void rowsAreLimitedAtTheEnd() {
+ assertEquals(" LIMIT 5", new H2DatabaseMeta().getLimitClause(5));
+ }
+
+ @Test
+ void theOtherFormIsNotUsed() {
+ assertEquals("", new H2DatabaseMeta().getLimitClausePrefix(5));
+ }
+}
diff --git
a/plugins/databases/hypersonic/src/main/java/org/apache/hop/databases/hypersonic/HypersonicDatabaseMeta.java
b/plugins/databases/hypersonic/src/main/java/org/apache/hop/databases/hypersonic/HypersonicDatabaseMeta.java
index cea2328802..b8cf813709 100644
---
a/plugins/databases/hypersonic/src/main/java/org/apache/hop/databases/hypersonic/HypersonicDatabaseMeta.java
+++
b/plugins/databases/hypersonic/src/main/java/org/apache/hop/databases/hypersonic/HypersonicDatabaseMeta.java
@@ -36,6 +36,13 @@ import org.apache.hop.core.util.Utils;
classLoaderGroup = "hypersonic-db")
@GuiPlugin(id = "GUI-HypersonicDatabaseMeta")
public class HypersonicDatabaseMeta extends BaseDatabaseMeta implements
IDatabase {
+
+ /** HSQLDB limits rows at the end of the statement. */
+ @Override
+ public String getLimitClause(int nrRows) {
+ return " LIMIT " + nrRows;
+ }
+
@Override
public int[] getAccessTypeList() {
return new int[] {DatabaseMeta.TYPE_ACCESS_NATIVE};
diff --git
a/plugins/databases/hypersonic/src/test/java/org/apache/hop/databases/hypersonic/HypersonicLimitClauseTest.java
b/plugins/databases/hypersonic/src/test/java/org/apache/hop/databases/hypersonic/HypersonicLimitClauseTest.java
new file mode 100644
index 0000000000..c4bdf5e9fa
--- /dev/null
+++
b/plugins/databases/hypersonic/src/test/java/org/apache/hop/databases/hypersonic/HypersonicLimitClauseTest.java
@@ -0,0 +1,36 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one or more
+ * contributor license agreements. See the NOTICE file distributed with
+ * this work for additional information regarding copyright ownership.
+ * The ASF licenses this file to You under the Apache License, Version 2.0
+ * (the "License"); you may not use this file except in compliance with
+ * the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+
+package org.apache.hop.databases.hypersonic;
+
+import static org.junit.jupiter.api.Assertions.assertEquals;
+
+import org.junit.jupiter.api.Test;
+
+/** This database limits rows at the end of the statement. See issue 8013. */
+class HypersonicLimitClauseTest {
+
+ @Test
+ void rowsAreLimitedAtTheEnd() {
+ assertEquals(" LIMIT 5", new HypersonicDatabaseMeta().getLimitClause(5));
+ }
+
+ @Test
+ void theOtherFormIsNotUsed() {
+ assertEquals("", new HypersonicDatabaseMeta().getLimitClausePrefix(5));
+ }
+}
diff --git
a/plugins/databases/impala/src/main/java/org/apache/hop/databases/impala/ImpalaDatabaseMeta.java
b/plugins/databases/impala/src/main/java/org/apache/hop/databases/impala/ImpalaDatabaseMeta.java
index d43a4fd8cb..70ed4bf463 100644
---
a/plugins/databases/impala/src/main/java/org/apache/hop/databases/impala/ImpalaDatabaseMeta.java
+++
b/plugins/databases/impala/src/main/java/org/apache/hop/databases/impala/ImpalaDatabaseMeta.java
@@ -39,6 +39,13 @@ import org.apache.hop.metadata.api.HopMetadataProperty;
classLoaderGroup = "impala-db")
@GuiPlugin(id = "GUI-ClouderaImpalaDatabaseMeta")
public class ImpalaDatabaseMeta extends BaseDatabaseMeta implements IDatabase {
+
+ /** Impala limits rows at the end of the statement. */
+ @Override
+ public String getLimitClause(int nrRows) {
+ return " LIMIT " + nrRows;
+ }
+
public static final String CONST_ALTER_TABLE = "ALTER TABLE ";
private static final int VARCHAR_LIMIT = 65_535;
diff --git
a/plugins/databases/impala/src/test/java/org/apache/hop/databases/impala/ImpalaLimitClauseTest.java
b/plugins/databases/impala/src/test/java/org/apache/hop/databases/impala/ImpalaLimitClauseTest.java
new file mode 100644
index 0000000000..19b3b818fd
--- /dev/null
+++
b/plugins/databases/impala/src/test/java/org/apache/hop/databases/impala/ImpalaLimitClauseTest.java
@@ -0,0 +1,36 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one or more
+ * contributor license agreements. See the NOTICE file distributed with
+ * this work for additional information regarding copyright ownership.
+ * The ASF licenses this file to You under the Apache License, Version 2.0
+ * (the "License"); you may not use this file except in compliance with
+ * the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+
+package org.apache.hop.databases.impala;
+
+import static org.junit.jupiter.api.Assertions.assertEquals;
+
+import org.junit.jupiter.api.Test;
+
+/** This database limits rows at the end of the statement. See issue 8013. */
+class ImpalaLimitClauseTest {
+
+ @Test
+ void rowsAreLimitedAtTheEnd() {
+ assertEquals(" LIMIT 5", new ImpalaDatabaseMeta().getLimitClause(5));
+ }
+
+ @Test
+ void theOtherFormIsNotUsed() {
+ assertEquals("", new ImpalaDatabaseMeta().getLimitClausePrefix(5));
+ }
+}
diff --git
a/plugins/databases/informix/src/main/java/org/apache/hop/databases/informix/InformixDatabaseMeta.java
b/plugins/databases/informix/src/main/java/org/apache/hop/databases/informix/InformixDatabaseMeta.java
index e2c32c7369..26b5f92152 100644
---
a/plugins/databases/informix/src/main/java/org/apache/hop/databases/informix/InformixDatabaseMeta.java
+++
b/plugins/databases/informix/src/main/java/org/apache/hop/databases/informix/InformixDatabaseMeta.java
@@ -38,6 +38,12 @@ import org.apache.hop.core.row.IValueMeta;
@GuiPlugin(id = "GUI-InformixDatabaseMeta")
public class InformixDatabaseMeta extends BaseDatabaseMeta implements
IDatabase {
+ /** Informix limits rows with FIRST, between SELECT and the column list. */
+ @Override
+ public String getLimitClausePrefix(int nrRows) {
+ return " FIRST " + nrRows;
+ }
+
@GuiWidgetElement(
id = "servername",
order = "10",
diff --git
a/plugins/databases/informix/src/test/java/org/apache/hop/databases/informix/InformixLimitClauseTest.java
b/plugins/databases/informix/src/test/java/org/apache/hop/databases/informix/InformixLimitClauseTest.java
new file mode 100644
index 0000000000..7a9825cccb
--- /dev/null
+++
b/plugins/databases/informix/src/test/java/org/apache/hop/databases/informix/InformixLimitClauseTest.java
@@ -0,0 +1,36 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one or more
+ * contributor license agreements. See the NOTICE file distributed with
+ * this work for additional information regarding copyright ownership.
+ * The ASF licenses this file to You under the Apache License, Version 2.0
+ * (the "License"); you may not use this file except in compliance with
+ * the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+
+package org.apache.hop.databases.informix;
+
+import static org.junit.jupiter.api.Assertions.assertEquals;
+
+import org.junit.jupiter.api.Test;
+
+/** This database limits rows after SELECT. See issue 8013. */
+class InformixLimitClauseTest {
+
+ @Test
+ void rowsAreLimitedAfterSelect() {
+ assertEquals(" FIRST 5", new
InformixDatabaseMeta().getLimitClausePrefix(5));
+ }
+
+ @Test
+ void theOtherFormIsNotUsed() {
+ assertEquals("", new InformixDatabaseMeta().getLimitClause(5));
+ }
+}
diff --git
a/plugins/databases/interbase/src/main/java/org/apache/hop/databases/interbase/InterbaseDatabaseMeta.java
b/plugins/databases/interbase/src/main/java/org/apache/hop/databases/interbase/InterbaseDatabaseMeta.java
index 715597838f..c98129f507 100644
---
a/plugins/databases/interbase/src/main/java/org/apache/hop/databases/interbase/InterbaseDatabaseMeta.java
+++
b/plugins/databases/interbase/src/main/java/org/apache/hop/databases/interbase/InterbaseDatabaseMeta.java
@@ -36,6 +36,12 @@ import org.apache.hop.core.row.IValueMeta;
@GuiPlugin(id = "GUI-InterbaseDatabaseMeta")
public class InterbaseDatabaseMeta extends BaseDatabaseMeta implements
IDatabase {
+ /** Interbase limits rows with FIRST, between SELECT and the column list. */
+ @Override
+ public String getLimitClausePrefix(int nrRows) {
+ return " FIRST " + nrRows;
+ }
+
public static final String CONST_SMALLINT = "SMALLINT";
public static final String CONST_INTEGER = "INTEGER";
public static final String CONST_VARCHAR = "VARCHAR";
diff --git
a/plugins/databases/interbase/src/test/java/org/apache/hop/databases/interbase/InterbaseLimitClauseTest.java
b/plugins/databases/interbase/src/test/java/org/apache/hop/databases/interbase/InterbaseLimitClauseTest.java
new file mode 100644
index 0000000000..7675882547
--- /dev/null
+++
b/plugins/databases/interbase/src/test/java/org/apache/hop/databases/interbase/InterbaseLimitClauseTest.java
@@ -0,0 +1,36 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one or more
+ * contributor license agreements. See the NOTICE file distributed with
+ * this work for additional information regarding copyright ownership.
+ * The ASF licenses this file to You under the Apache License, Version 2.0
+ * (the "License"); you may not use this file except in compliance with
+ * the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+
+package org.apache.hop.databases.interbase;
+
+import static org.junit.jupiter.api.Assertions.assertEquals;
+
+import org.junit.jupiter.api.Test;
+
+/** This database limits rows after SELECT. See issue 8013. */
+class InterbaseLimitClauseTest {
+
+ @Test
+ void rowsAreLimitedAfterSelect() {
+ assertEquals(" FIRST 5", new
InterbaseDatabaseMeta().getLimitClausePrefix(5));
+ }
+
+ @Test
+ void theOtherFormIsNotUsed() {
+ assertEquals("", new InterbaseDatabaseMeta().getLimitClause(5));
+ }
+}
diff --git
a/plugins/databases/iris/src/main/java/org/apache/hop/databases/iris/IrisDatabaseMeta.java
b/plugins/databases/iris/src/main/java/org/apache/hop/databases/iris/IrisDatabaseMeta.java
index c6976a9e5a..1c20f3882b 100644
---
a/plugins/databases/iris/src/main/java/org/apache/hop/databases/iris/IrisDatabaseMeta.java
+++
b/plugins/databases/iris/src/main/java/org/apache/hop/databases/iris/IrisDatabaseMeta.java
@@ -37,6 +37,12 @@ import org.apache.hop.core.row.IValueMeta;
@GuiPlugin(id = "GUI-IrisDatabaseMeta")
public class IrisDatabaseMeta extends BaseDatabaseMeta implements IDatabase {
+ /** IRIS limits rows with TOP, between SELECT and the column list. */
+ @Override
+ public String getLimitClausePrefix(int nrRows) {
+ return " TOP " + nrRows;
+ }
+
public static final String CONST_ALTER_TABLE = "ALTER TABLE ";
@Override
diff --git
a/plugins/databases/iris/src/test/java/org/apache/hop/databases/iris/IrisLimitClauseTest.java
b/plugins/databases/iris/src/test/java/org/apache/hop/databases/iris/IrisLimitClauseTest.java
new file mode 100644
index 0000000000..7b1eec0e2d
--- /dev/null
+++
b/plugins/databases/iris/src/test/java/org/apache/hop/databases/iris/IrisLimitClauseTest.java
@@ -0,0 +1,36 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one or more
+ * contributor license agreements. See the NOTICE file distributed with
+ * this work for additional information regarding copyright ownership.
+ * The ASF licenses this file to You under the Apache License, Version 2.0
+ * (the "License"); you may not use this file except in compliance with
+ * the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+
+package org.apache.hop.databases.iris;
+
+import static org.junit.jupiter.api.Assertions.assertEquals;
+
+import org.junit.jupiter.api.Test;
+
+/** This database limits rows after SELECT. See issue 8013. */
+class IrisLimitClauseTest {
+
+ @Test
+ void rowsAreLimitedAfterSelect() {
+ assertEquals(" TOP 5", new IrisDatabaseMeta().getLimitClausePrefix(5));
+ }
+
+ @Test
+ void theOtherFormIsNotUsed() {
+ assertEquals("", new IrisDatabaseMeta().getLimitClause(5));
+ }
+}
diff --git
a/plugins/databases/monetdb/src/main/java/org/apache/hop/databases/monetdb/MonetDBDatabaseMeta.java
b/plugins/databases/monetdb/src/main/java/org/apache/hop/databases/monetdb/MonetDBDatabaseMeta.java
index 14262c7a02..85cb97a872 100644
---
a/plugins/databases/monetdb/src/main/java/org/apache/hop/databases/monetdb/MonetDBDatabaseMeta.java
+++
b/plugins/databases/monetdb/src/main/java/org/apache/hop/databases/monetdb/MonetDBDatabaseMeta.java
@@ -37,6 +37,12 @@ import org.apache.hop.core.util.Utils;
@GuiPlugin(id = "GUI-MonetDBDatabaseMeta")
public class MonetDBDatabaseMeta extends BaseDatabaseMeta implements IDatabase
{
+ /** MonetDB limits rows at the end of the statement. */
+ @Override
+ public String getLimitClause(int nrRows) {
+ return " LIMIT " + nrRows;
+ }
+
public static final String CONST_BIGINT = "BIGINT";
public static final String CONST_DOUBLE = "DOUBLE";
public static ThreadLocal<Boolean> safeModeLocal = new ThreadLocal<>();
diff --git
a/plugins/databases/monetdb/src/test/java/org/apache/hop/databases/monetdb/MonetDBLimitClauseTest.java
b/plugins/databases/monetdb/src/test/java/org/apache/hop/databases/monetdb/MonetDBLimitClauseTest.java
new file mode 100644
index 0000000000..0e6f97bde6
--- /dev/null
+++
b/plugins/databases/monetdb/src/test/java/org/apache/hop/databases/monetdb/MonetDBLimitClauseTest.java
@@ -0,0 +1,36 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one or more
+ * contributor license agreements. See the NOTICE file distributed with
+ * this work for additional information regarding copyright ownership.
+ * The ASF licenses this file to You under the Apache License, Version 2.0
+ * (the "License"); you may not use this file except in compliance with
+ * the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+
+package org.apache.hop.databases.monetdb;
+
+import static org.junit.jupiter.api.Assertions.assertEquals;
+
+import org.junit.jupiter.api.Test;
+
+/** This database limits rows at the end of the statement. See issue 8013. */
+class MonetDBLimitClauseTest {
+
+ @Test
+ void rowsAreLimitedAtTheEnd() {
+ assertEquals(" LIMIT 5", new MonetDBDatabaseMeta().getLimitClause(5));
+ }
+
+ @Test
+ void theOtherFormIsNotUsed() {
+ assertEquals("", new MonetDBDatabaseMeta().getLimitClausePrefix(5));
+ }
+}
diff --git
a/plugins/databases/mssql/src/main/java/org/apache/hop/databases/mssql/MsSqlServerDatabaseMeta.java
b/plugins/databases/mssql/src/main/java/org/apache/hop/databases/mssql/MsSqlServerDatabaseMeta.java
index 18217215ec..25c9ef4361 100644
---
a/plugins/databases/mssql/src/main/java/org/apache/hop/databases/mssql/MsSqlServerDatabaseMeta.java
+++
b/plugins/databases/mssql/src/main/java/org/apache/hop/databases/mssql/MsSqlServerDatabaseMeta.java
@@ -44,6 +44,16 @@ import org.apache.hop.metadata.api.HopMetadataProperty;
@GuiPlugin(id = "GUI-MSSQLServerDatabaseMeta")
public class MsSqlServerDatabaseMeta extends BaseDatabaseMeta implements
IDatabase {
+ /**
+ * SQL Server limits rows with TOP, between SELECT and the column list.
+ *
+ * <p>Inherited by the native dialect, which shares this syntax.
+ */
+ @Override
+ public String getLimitClausePrefix(int nrRows) {
+ return " TOP " + nrRows;
+ }
+
public static final String CONST_ALTER_TABLE = "ALTER TABLE ";
@GuiWidgetElement(
diff --git
a/plugins/databases/mssql/src/test/java/org/apache/hop/databases/mssql/MsSqlServerLimitClauseTest.java
b/plugins/databases/mssql/src/test/java/org/apache/hop/databases/mssql/MsSqlServerLimitClauseTest.java
new file mode 100644
index 0000000000..2aa6655095
--- /dev/null
+++
b/plugins/databases/mssql/src/test/java/org/apache/hop/databases/mssql/MsSqlServerLimitClauseTest.java
@@ -0,0 +1,36 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one or more
+ * contributor license agreements. See the NOTICE file distributed with
+ * this work for additional information regarding copyright ownership.
+ * The ASF licenses this file to You under the Apache License, Version 2.0
+ * (the "License"); you may not use this file except in compliance with
+ * the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+
+package org.apache.hop.databases.mssql;
+
+import static org.junit.jupiter.api.Assertions.assertEquals;
+
+import org.junit.jupiter.api.Test;
+
+/** This database limits rows after SELECT rather than at the end of the
statement. */
+class MsSqlServerLimitClauseTest {
+
+ @Test
+ void rowsAreLimitedAfterSelect() {
+ assertEquals(" TOP 25", new
MsSqlServerDatabaseMeta().getLimitClausePrefix(25));
+ }
+
+ @Test
+ void thereIsNoClauseAtTheEndOfTheStatement() {
+ assertEquals("", new MsSqlServerDatabaseMeta().getLimitClause(25));
+ }
+}
diff --git
a/plugins/databases/sqlite/src/main/java/org/apache/hop/databases/sqlite/SqliteDatabaseMeta.java
b/plugins/databases/sqlite/src/main/java/org/apache/hop/databases/sqlite/SqliteDatabaseMeta.java
index 24b229708a..631c425d8e 100644
---
a/plugins/databases/sqlite/src/main/java/org/apache/hop/databases/sqlite/SqliteDatabaseMeta.java
+++
b/plugins/databases/sqlite/src/main/java/org/apache/hop/databases/sqlite/SqliteDatabaseMeta.java
@@ -47,6 +47,12 @@ import org.apache.hop.core.row.value.ValueMetaFactory;
@GuiPlugin(id = "GUI-SQLiteDatabaseMeta")
public class SqliteDatabaseMeta extends BaseDatabaseMeta implements IDatabase {
+ /** SQLite limits rows at the end of the statement. */
+ @Override
+ public String getLimitClause(int nrRows) {
+ return " LIMIT " + nrRows;
+ }
+
private static final List<IDatabaseTypeRule> TYPE_RULES =
DatabaseTypes.rules()
// Dynamic typing means a binary column is as likely to hold text.
diff --git
a/plugins/databases/sqlite/src/test/java/org/apache/hop/databases/sqlite/SqliteLimitClauseTest.java
b/plugins/databases/sqlite/src/test/java/org/apache/hop/databases/sqlite/SqliteLimitClauseTest.java
new file mode 100644
index 0000000000..74af4cf490
--- /dev/null
+++
b/plugins/databases/sqlite/src/test/java/org/apache/hop/databases/sqlite/SqliteLimitClauseTest.java
@@ -0,0 +1,36 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one or more
+ * contributor license agreements. See the NOTICE file distributed with
+ * this work for additional information regarding copyright ownership.
+ * The ASF licenses this file to You under the Apache License, Version 2.0
+ * (the "License"); you may not use this file except in compliance with
+ * the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+
+package org.apache.hop.databases.sqlite;
+
+import static org.junit.jupiter.api.Assertions.assertEquals;
+
+import org.junit.jupiter.api.Test;
+
+/** This database limits rows at the end of the statement. See issue 8013. */
+class SqliteLimitClauseTest {
+
+ @Test
+ void rowsAreLimitedAtTheEnd() {
+ assertEquals(" LIMIT 5", new SqliteDatabaseMeta().getLimitClause(5));
+ }
+
+ @Test
+ void theOtherFormIsNotUsed() {
+ assertEquals("", new SqliteDatabaseMeta().getLimitClausePrefix(5));
+ }
+}
diff --git
a/plugins/databases/sybase/src/main/java/org/apache/hop/databases/sybase/SybaseDatabaseMeta.java
b/plugins/databases/sybase/src/main/java/org/apache/hop/databases/sybase/SybaseDatabaseMeta.java
index e61ec769de..beceadf2e8 100644
---
a/plugins/databases/sybase/src/main/java/org/apache/hop/databases/sybase/SybaseDatabaseMeta.java
+++
b/plugins/databases/sybase/src/main/java/org/apache/hop/databases/sybase/SybaseDatabaseMeta.java
@@ -35,6 +35,13 @@ import org.apache.hop.core.row.IValueMeta;
classLoaderGroup = "sybase-db")
@GuiPlugin(id = "GUI-SybaseDatabaseMeta")
public class SybaseDatabaseMeta extends BaseDatabaseMeta implements IDatabase {
+
+ /** Sybase ASE limits rows with TOP, between SELECT and the column list. */
+ @Override
+ public String getLimitClausePrefix(int nrRows) {
+ return " TOP " + nrRows;
+ }
+
@Override
public int[] getAccessTypeList() {
return new int[] {DatabaseMeta.TYPE_ACCESS_NATIVE};
diff --git
a/plugins/databases/sybase/src/test/java/org/apache/hop/databases/sybase/SybaseLimitClauseTest.java
b/plugins/databases/sybase/src/test/java/org/apache/hop/databases/sybase/SybaseLimitClauseTest.java
new file mode 100644
index 0000000000..88722fc962
--- /dev/null
+++
b/plugins/databases/sybase/src/test/java/org/apache/hop/databases/sybase/SybaseLimitClauseTest.java
@@ -0,0 +1,36 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one or more
+ * contributor license agreements. See the NOTICE file distributed with
+ * this work for additional information regarding copyright ownership.
+ * The ASF licenses this file to You under the Apache License, Version 2.0
+ * (the "License"); you may not use this file except in compliance with
+ * the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+
+package org.apache.hop.databases.sybase;
+
+import static org.junit.jupiter.api.Assertions.assertEquals;
+
+import org.junit.jupiter.api.Test;
+
+/** This database limits rows after SELECT. See issue 8013. */
+class SybaseLimitClauseTest {
+
+ @Test
+ void rowsAreLimitedAfterSelect() {
+ assertEquals(" TOP 5", new SybaseDatabaseMeta().getLimitClausePrefix(5));
+ }
+
+ @Test
+ void theOtherFormIsNotUsed() {
+ assertEquals("", new SybaseDatabaseMeta().getLimitClause(5));
+ }
+}
diff --git
a/plugins/databases/sybaseiq/src/main/java/org/apache/hop/databases/sybaseiq/SybaseIQDatabaseMeta.java
b/plugins/databases/sybaseiq/src/main/java/org/apache/hop/databases/sybaseiq/SybaseIQDatabaseMeta.java
index 572e47df26..ed540d0657 100644
---
a/plugins/databases/sybaseiq/src/main/java/org/apache/hop/databases/sybaseiq/SybaseIQDatabaseMeta.java
+++
b/plugins/databases/sybaseiq/src/main/java/org/apache/hop/databases/sybaseiq/SybaseIQDatabaseMeta.java
@@ -45,18 +45,10 @@ public class SybaseIQDatabaseMeta extends BaseDatabaseMeta
implements IDatabase
return true;
}
- /**
- * Sybase IQ limits rows with TOP, which goes between SELECT and the column
list rather than at
- * the end of the statement. {@link IDatabase#getLimitClause} is appended
after the FROM clause,
- * so TOP cannot be expressed through it; returning it here would produce
{@code SELECT * FROM t
- * TOP 10}, which does not parse.
- *
- * <p>Callers cap the row count while reading, so the effect of having no
clause here is that a
- * few more rows cross the wire, not that too many are returned.
- */
+ /** Sybase IQ limits rows with TOP, between SELECT and the column list. */
@Override
- public String getLimitClause(int nrRows) {
- return "";
+ public String getLimitClausePrefix(int nrRows) {
+ return " TOP " + nrRows;
}
@Override
diff --git
a/plugins/databases/sybaseiq/src/test/java/org/apache/hop/databases/sybaseiq/SybaseIQLimitClauseTest.java
b/plugins/databases/sybaseiq/src/test/java/org/apache/hop/databases/sybaseiq/SybaseIQLimitClauseTest.java
new file mode 100644
index 0000000000..ddd153e9a1
--- /dev/null
+++
b/plugins/databases/sybaseiq/src/test/java/org/apache/hop/databases/sybaseiq/SybaseIQLimitClauseTest.java
@@ -0,0 +1,36 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one or more
+ * contributor license agreements. See the NOTICE file distributed with
+ * this work for additional information regarding copyright ownership.
+ * The ASF licenses this file to You under the Apache License, Version 2.0
+ * (the "License"); you may not use this file except in compliance with
+ * the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+
+package org.apache.hop.databases.sybaseiq;
+
+import static org.junit.jupiter.api.Assertions.assertEquals;
+
+import org.junit.jupiter.api.Test;
+
+/** This database limits rows after SELECT rather than at the end of the
statement. */
+class SybaseIQLimitClauseTest {
+
+ @Test
+ void rowsAreLimitedAfterSelect() {
+ assertEquals(" TOP 25", new
SybaseIQDatabaseMeta().getLimitClausePrefix(25));
+ }
+
+ @Test
+ void thereIsNoClauseAtTheEndOfTheStatement() {
+ assertEquals("", new SybaseIQDatabaseMeta().getLimitClause(25));
+ }
+}
diff --git
a/plugins/databases/teradata/src/main/java/org/apache/hop/databases/teradata/TeradataDatabaseMeta.java
b/plugins/databases/teradata/src/main/java/org/apache/hop/databases/teradata/TeradataDatabaseMeta.java
index afeea9afac..bd7782fe65 100644
---
a/plugins/databases/teradata/src/main/java/org/apache/hop/databases/teradata/TeradataDatabaseMeta.java
+++
b/plugins/databases/teradata/src/main/java/org/apache/hop/databases/teradata/TeradataDatabaseMeta.java
@@ -44,6 +44,12 @@ import org.apache.hop.core.util.Utils;
@GuiPlugin(id = "GUI-TeradataDatabaseMeta")
public class TeradataDatabaseMeta extends BaseDatabaseMeta implements
IDatabase {
+ /** Teradata limits rows with TOP, between SELECT and the column list. */
+ @Override
+ public String getLimitClausePrefix(int nrRows) {
+ return " TOP " + nrRows;
+ }
+
private static final List<IDatabaseTypeRule> TYPE_RULES =
DatabaseTypes.rules()
// Hop marks "a date, not a timestamp" with a precision of one.
diff --git
a/plugins/databases/teradata/src/test/java/org/apache/hop/databases/teradata/TeradataLimitClauseTest.java
b/plugins/databases/teradata/src/test/java/org/apache/hop/databases/teradata/TeradataLimitClauseTest.java
new file mode 100644
index 0000000000..1a4a7894d9
--- /dev/null
+++
b/plugins/databases/teradata/src/test/java/org/apache/hop/databases/teradata/TeradataLimitClauseTest.java
@@ -0,0 +1,36 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one or more
+ * contributor license agreements. See the NOTICE file distributed with
+ * this work for additional information regarding copyright ownership.
+ * The ASF licenses this file to You under the Apache License, Version 2.0
+ * (the "License"); you may not use this file except in compliance with
+ * the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+
+package org.apache.hop.databases.teradata;
+
+import static org.junit.jupiter.api.Assertions.assertEquals;
+
+import org.junit.jupiter.api.Test;
+
+/** This database limits rows after SELECT. See issue 8013. */
+class TeradataLimitClauseTest {
+
+ @Test
+ void rowsAreLimitedAfterSelect() {
+ assertEquals(" TOP 5", new TeradataDatabaseMeta().getLimitClausePrefix(5));
+ }
+
+ @Test
+ void theOtherFormIsNotUsed() {
+ assertEquals("", new TeradataDatabaseMeta().getLimitClause(5));
+ }
+}
