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

[16 Jan 2014 8:59] Pasi Eronen
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.
[20 Jan 2014 10:35] Filipe Silva
Hi Pasi,

Thank you for this bug report. We'll analyze this issue and answer back as soon as possible.
[21 Jan 2014 15:21] Filipe Silva
Hi Pasi,

Verified as described. Thank you.
[10 Mar 2014 22:21] Daniel So
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."