Statement property maxRows incorrectly affects count(*) on table valued functions.

[jedvardsson]

Setting statement property maxRows results in faulty row count on table valued functions.
Below is the output from the supplied test program. The two functions numbers_tf and numbers_itf both return 10 rows. The former is table-valued function while the latter is an inlined version instead.

maxRows(0): select count(*) from dbo.numbers_itf() => 10  OK!
maxRows(5): select count(*) from dbo.numbers_itf() => 10  OK!
maxRows(0): select count(*) from dbo.numbers_tf() => 10  OK!
maxRows(5): select count(*) from dbo.numbers_tf() => 5  FAILED!

As can be seen setting maxRows to 5 changes the count to 5 instead of the expected 10. Clearly this is a bug. MaxRows should only affect the number of rows that the result set returns.

import java.math.BigInteger;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.concurrent.ThreadLocalRandom;

public class Main {
    public static void main(String[] args) throws SQLException {
        String url = "jdbc:sqlserver://localhost:1433";
        String user = "sa";
        String password = "Test1234!";
        String database = randomName("test_");
        try (
                Connection c = DriverManager.getConnection(url, user, password);
        ) {
            try {
                executeUpdate(c, "create database " + database);
                executeUpdate(c, "use " + database);


                executeUpdate(c, "create function dbo.numbers_itf()\n" +
                        "returns table\n" +
                        "as return\n" +
                        "    select * from (values (1), (2), (3), (4), (5), (6), (7), (8), (9), (10)) t(num)\n" +
                        "\n");

                executeUpdate(c, "create function dbo.numbers_tf()\n" +
                        "  returns @result table (\n" +
                        "    num int\n" +
                        "  )\n" +
                        "  as\n" +
                        "  begin\n" +
                        "    insert into @result\n" +
                        "    select * from (values (1), (2), (3), (4), (5), (6), (7), (8), (9), (10)) t(num)\n" +
                        "    return\n" +
                        "  end\n" +
                        "\n");


                executeTest(c, 0, "select count(*) from dbo.numbers_itf()", 10);
                executeTest(c, 5, "select count(*) from dbo.numbers_itf()", 10);
                executeTest(c, 0, "select count(*) from dbo.numbers_tf()", 10);
                executeTest(c, 5, "select count(*) from dbo.numbers_tf()", 10);

            } finally {
                executeUpdate(c,"use master");
                executeUpdate(c, "drop database " + database);
            }
        }
    }

    private static int executeUpdate(Connection c, String sql) throws SQLException {
        try (Statement s = c.createStatement()) {
            return s.executeUpdate(sql);
        }
    }

    private static void executeTest(Connection c, int maxRows, String sql, int expected) throws SQLException {
        try (Statement s = c.createStatement()) {
            s.setMaxRows(maxRows);
            s.execute(sql);
            try (ResultSet r = s.getResultSet()) {
                r.next();
                int actual = r.getInt(1);
                System.out.format("maxRows(%d): %s => %d  %s%n", s.getMaxRows(), sql, actual, (actual != expected ? "FAILED!" : "OK!"));
            }
        }
    }

    private static String randomName(String prefix) {
        return prefix + (new BigInteger(40, ThreadLocalRandom.current())).toString(32);
    }

}

[AfsanehR-zz]

Hi @jedvardsson! This is expected behaviour from sql server. If you run the same queries in sql server you will get the same resutls:

  SET ROWCOUNT 0  
  select count(*) from dbo.numbers_itf()    //returns 10
  select count(*) from dbo.numbers_tf()     //returns 10
  SET ROWCOUNT 5  
  select count(*) from dbo.numbers_itf()   //returns 10
  select count(*) from dbo.numbers_tf()    //returns 5

just to clarify, SET ROWCOUNT applies to any scope for INSERT/UPDATE/DELETE operations and is not effective in inner scopes for SELECT statement (like dbo.numbers_itf()).
Hope this resolves your issue.

[gordthompson]

@v-afrafi - I see some merit in the argument that @jedvardsson is making because

https://docs.oracle.com/javase/7/docs/api/java/sql/Statement.html#setMaxRows(int)

specifically refers to the maximum number of rows in a ResultSet object:

Sets the limit for the maximum number of rows that any ResultSet object generated by this Statement object can contain

[AfsanehR-zz]

@gordthompson When using setMaxRows(int), the maxRows will be set to the limit and later when the statement is getting executed, method setMaxRowsAndMaxFieldSize() is called which internally executes "SET ROWCOUNT limit". This will affect the number of rows returned from sql server in ResultSet.

In this example, in the numbers_itf() function, the INSERT command is affected by the setMaxRow query, therefore server returns only 5 since only 5 is inserted.

[jedvardsson]

@v-afrafi I understand the cause of this is ROWCOUNT. However, as @gordthompson points out this goes against the jdbc spec. maxRows is not semantically equivalent to ROWCOUNT.
The current behavior is breaking the JDBC spec.

[AfsanehR-zz]

@jedvardsson , @gordthompson Thanks for clarifying this. I understand your point. We will investigate this and update you further. Thanks again.

[AfsanehR-zz]

Hi @jedvardsson, according to JDBC specs, in the remarks it also mentions that Microsoft JDBC driver uses set ROWCOUNT and its limitations which we discussed before. While we are investigating this, I suggest using SELECT TOP N in your query as a work around. Thank you.

[jedvardsson]

Hi @v-afrafi, Thank you for your your attention to this issue. The link is not to the JDBC spec but to MS JDBC driver implementation of the spec. Anyway, in my case a workaround is not possible. I stumbled across this in IntelliJ Database Console. It seems that the console relies on maxRows in order to paginate the result when executing SQL. Using JTDS driver instead works as expected.

[AfsanehR-zz]

@jedvardsson I just tried running your sample code with jtds v1.3.1 and got the same results with our driver. Did you use the same sample code with same jtds version that is working as expected for you?

In the meantime, according to this, it seems that using SET ROWCOUNT will not be affecting the delete, insert, update statements in future releases of SQL Server. We really appreciate your finding this issue and we will be considering this in our future development work.

[ajlam]

@jedvardsson, I think there are two ways we can go about this:

1. Trying to parse and change the SQL text to use TOP N instead
2. Changing the driver to only return no more than max rows even though the server may have sent more rows