Exception using setMaxRows() followed by query execution when showplan_text is on

[tseylerd]

Driver version or jar name

6.0.8112.100

SQL Server version

14.0 (Linux)

Client operating system

macOS

Java/JVM version

1.8 OpenJDK

Problem description

When the SHOWPLAN_TEXT is on, driver may fail on statement execution. Especially, using setMaxRows() on statement before execution. The problem is that before querying driver sends SET ROWCOUNT to the database and expects nothing as a results. But showplan_text is on and database will return result set causing driver failure with:

com.microsoft.sqlserver.jdbc.SQLServerException: The TDS protocol stream is not valid. Unexpected token TDS_COLMETADATA (0x81).
	at com.microsoft.sqlserver.jdbc.SQLServerConnection.terminate(SQLServerConnection.java:2400)
	at com.microsoft.sqlserver.jdbc.SQLServerConnection.terminate(SQLServerConnection.java:2384)
	at com.microsoft.sqlserver.jdbc.SQLServerConnection.throwInvalidTDSToken(SQLServerConnection.java:2373)
	at com.microsoft.sqlserver.jdbc.TDSReader.throwInvalidTDSToken(IOBuffer.java:6558)
	at com.microsoft.sqlserver.jdbc.TDSParser.throwUnexpectedTokenException(tdsparser.java:119)
	at com.microsoft.sqlserver.jdbc.TDSTokenHandler.onColMetaData(tdsparser.java:247)
	at com.microsoft.sqlserver.jdbc.TDSParser.parse(tdsparser.java:87)
	at com.microsoft.sqlserver.jdbc.TDSParser.parse(tdsparser.java:36)
	at com.microsoft.sqlserver.jdbc.SQLServerConnection$1ConnectionCommand.doExecute(SQLServerConnection.java:2495)
	at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7505)
	at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:2445)
	at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectionCommand(SQLServerConnection.java:2500)
	at com.microsoft.sqlserver.jdbc.SQLServerConnection.setMaxRows(SQLServerConnection.java:519)
	at com.microsoft.sqlserver.jdbc.SQLServerStatement.setMaxRowsAndMaxFieldSize(SQLServerStatement.java:810)
	at com.microsoft.sqlserver.jdbc.SQLServerStatement.doExecuteStatement(SQLServerStatement.java:848)
	at com.microsoft.sqlserver.jdbc.SQLServerStatement$StmtExecCmd.doExecute(SQLServerStatement.java:778)
	at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7505)
	at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:2445)
	at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:191)
	at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:166)
	at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeQuery(SQLServerStatement.java:677)

Expected behavior and actual behavior

I think the code should be executed without errors.

Actually there is an exception.

Repro code

Connection connection = DriverManager.getConnection("jdbc:sqlserver://localhost:1433;databaseName=testdb", "test", "***");
DatabaseMetaData data = connection.getMetaData();
connection.createStatement().execute("SET SHOWPLAN_TEXT ON");
Statement statement = connection.createStatement();
statement.setMaxRows(20);
statement.executeQuery("SELECT 1");

[tjlee]

+1

[moscas]

+1

[kassak]

+1

[ant-druha]

+1

[leo-from-spb]

+1

[ulvii]

Hi @tseylerd ,

I can confirm that this is a bug in the driver and we will address the issue in one of the upcoming preview releases.

[tseylerd]

@ulvii Hi! Thanks a lot, guys.

[ulvii]

Hi @tseylerd,

I created a PR with the fix. Could you give it a try and let me know if the issue is resolved?

[tseylerd]

@ulvii First of all, thank you for a fix. We'll test it today. I know that I'm not a reviewer, but in my opinion it would be better to put warning in statement object in this case. This way makes possible to notify a client that something unexpected happens without any exception. Please, just ignore it if you don't think so.

[tseylerd]

@ulvii I've tested and it doesn't work. Now it fails with "[08S01] The TDS protocol stream is not valid. Unexpected token unknown token (0x0)"

[ulvii]

Hi @tseylerd ,

Looks like I accidentally uploaded the wrong jars. Please try the updated zip file again, I apologize for inconvenience.

I added a few more lines that generate additional log for this case. I agree that removing the exception might not seem like the best approach, but other solutions I looked into either result in performance degradation or do not completely solve the issue.

Please also feel free to create and review pull requests, we appreciate contributions from the community.

[tseylerd]

@ulvii Thanks, it works!

[cheenamalhotra]

Closing the issue since PR merged and fix released with 6.5.1 preview release.