MySQL Bugs: #71396: setMaxRows (SQL_SELECT_LIMIT) from one query used in later queries (sometimes)

Bug #71396	setMaxRows (SQL_SELECT_LIMIT) from one query used in later queries (sometimes)
Submitted:	16 Jan 2014 8:59	Modified:	10 Mar 2014 22:21
Reporter:	Pasi Eronen	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / J	Severity:	S2 (Serious)
Version:	5.1.28	OS:	Any
Assigned to:	Filipe Silva	CPU Architecture:	Any

Description:
If a previous query on a connection has used setMaxRows (leading to SET SQL_SELECT_LIMIT=x), then the next query (without setMaxRows) should do SET SQL_SELECT_LIMIT=DEFAULT.

Usually this is done correctly, but it seems Connector/J skips the "SET SQL_SELECT_LIMIT=DEFAULT" part if the next query contains a LIMIT clause.

However, the "contains a LIMIT clause" check is buggy, and can be triggered by things containing the substring "limit".

The test program below shows that a query with WHERE x!='NOLIMITS' is processed incorrectly. The same bug can be triggered with a table name containing the substring "limit", a JOIN subquery with a LIMIT, and probably other things as well.

Tested with mysql-server-5.5.34, mysql-connector-java-5.1.28.jar on Ubuntu 12.04 LTS with OpenJDK 7 (7u25/IcedTea 2.3.10).

How to repeat:
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
import java.sql.ResultSet;
import java.sql.PreparedStatement;

class MaxRowsTest {
   public static void main(String[] args) throws Exception {
       String url = "jdbc:mysql://127.0.0.1:3306/test"; // ?profileSQL=true
       String userName = "root";
       String userPassword = "";
       
       Connection conn = DriverManager.getConnection(url, userName, userPassword);
       
       Statement st = conn.createStatement();
       st.execute("drop table if exists maxrows_test");
       st.execute("create table maxrows_test ( x varchar(255) )");
       st.execute("insert into maxrows_test values ( 'One' ), ( 'Two' )");

       System.out.println("First query (expecting 1 row)");
       PreparedStatement pst = conn.prepareStatement("select x from maxrows_test");
       pst.setMaxRows(1);
       ResultSet rs = pst.executeQuery();
       while (rs.next()) {
           System.out.println("Got "+rs.getString(1));
       }
       
       System.out.println("Second query (expecting 2 rows)");
       rs = st.executeQuery("select x from maxrows_test where x != 'NOLIMITS'");
       while (rs.next()) {
           System.out.println("Got "+rs.getString(1));
       }

       System.out.println("Third query (expecting 2 rows)");
       rs = st.executeQuery("select x from maxrows_test where x != 'NOLINITS'");
       while (rs.next()) {
           System.out.println("Got "+rs.getString(1));
       }
   }
}

Prints output:

First query (expecting 1 row)
Got One
Second query (expecting 2 rows)
Got One
Third query (expecting 2 rows)
Got One
Got Two

Suggested fix:
Always send SQL_SELECT_LIMIT=DEFAULT if the previous query used setMaxRows.

Hi Pasi,

Thank you for this bug report. We'll analyze this issue and answer back as soon as possible.

Hi Pasi,

Verified as described. Thank you.

Added the following entry into the Connector/J 5.1.30 changelog:

"It was intended that if a previous query on a connection had used the setMaxRows() method, in the next query, Connector/J would not cancel that by setting SQL_SELECT_LIMIT=DEFAULT if the query contained a LIMIT clause. However, in the actual implementation, the maximum row setting was reused in the subsequent query in various situations beyond expectation (for example, when a table name contains the string 'limit' in it). This fix removes the LIMIT-clause parsing and replaces it by a better way of controlling the maximum rows per session."