SNOW-1757888: Limit for the maximum number of rows in the Statement is ignored (Statement.setMaxRows) when fetching results

[josemanuel-hita]

The limit for the maximum number of rows is ignored by the Snowflake driver.

JDBC Driver 3.14.4 Operating system - processor: MacOS - Apple Silicon Java version 11.0.24

I'm trying to fetch results but with a limited number of rows, using the method Statement.setMaxRows(int). Code example:

try {
      connection = getMyConnection();
      st = connection.createStatement();
      st.setQueryTimeout(timeout);
      st.setMaxRows(10);
      rs = st.executeQuery(query);
      ResultSetMetaData resultSetMetaData = rs.getMetaData();
      int columnCount = resultSetMetaData.getColumnCount();
      while (rs.next()) {
        Map<String, Object> row = new HashMap<>();
        for (int i = 1; i <= columnCount; i++) {
          String colName = resultSetMetaData.getColumnName(i);
          Object colVal = rs.getObject(i);
          row.put(colName, colVal);
        }
        results.add(row);
      }
}
assert(results.size() <= 10) // it fails for querys with > 10 rows

The code returns all rows, but following JDBC API the expected behaviour is to return the specified maximum number of rows: https://docs.oracle.com/javase/8/docs/api/java/sql/Statement.html#setMaxRows-int-

Current Snowflake documentation says Statement.setMaxRows(int) has the standard behaviour. And ResultSet.next() behaviour doesn't mention any special case for it, following https://docs.snowflake.com/en/developer-guide/jdbc/jdbc-api#object-resultset:

next()

This makes the next row in the result set the “current” row. Calls to the get*() methods, such as getInt(), get values from the current row.

If the ResultSet has been closed by a call to the close method, then subsequent calls to next return false, rather than raise an exception.

If the ResultSet object is for an asynchronous query, then this method will block until the results are available. You can use resultSet.unwrap(SnowflakeResultSet.class).getStatus() to get the query status before calling this method.

[sfc-gh-wfateem]

@josemanuel-hita thanks for reporting this. I'll take a look at this towards the end of this week or early next week. In the meantime, as I'm sure you're probably already aware, you could simply workaround this by adding LIMIT 10 to your SQL query text. Have you also confirmed if this issue exists in the latest 3.19.1 version?

[josemanuel-hita]

Thank you @sfc-gh-wfateem I know you have that workaround for regular queries, but I'm launching the next one: SHOW USER FUNCTIONS IN ACCOUNT (I don't want to use the information_schema as I want to avoid running a query for each database and schema)

By now, I cannot confirm if the issue exists in the latest version.

[sfc-gh-wfateem]

@josemanuel-hita then in that case, you can just run your SHOW command followed by a RESULT_SCAN query:

SELECT * FROM TABLE(RESULT_SCAN(LAST_QUERY_ID())) LIMIT 10;