Uploaded image for project: 'MariaDB Connector/J'
  1. MariaDB Connector/J
  2. CONJ-338

Inconsistent/wrong resultset when using variables and nested selects

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Minor
    • Resolution: Won't Do
    • Affects Version/s: 1.4.5, 1.4.6
    • Fix Version/s: N/A
    • Component/s: Other
    • Labels:
      None

      Description

      Ran into an issue where a query that uses user-defined variable and has multiple nested selects, returned right amount of rows, but all rows where identical (what should've been the last row).

      Reproduced a simplified example:

      Any table that has some (different) rows - SOMESCHEMA.SOMETABLE
      Query which uses a user-defined variable to return row count (original use was for paging results),

      SELECT X.* FROM (SELECT CODE, RN FROM (SELECT A.CODE, @cnt := @cnt + 1 AS RN FROM SOMESCHEMA.SOMETABLE A, (SELECT @cnt := 0) C) T) X;
      

      &

      SELECT CODE, RN FROM (SELECT A.CODE, @cnt := @cnt + 1 AS RN FROM SOMESCHEMA.SOMETABLE A, (SELECT @cnt := 0) C) T;
      

      They should return identical results, and they do when run manually (via HeidiSQL).
      But when 1st query is executed with mariadb connector, it returns right amount of rows, but each one is identical (what should be the last row), 2nd query works as expected.
      Does not reproduce with mysql connector (6.0.3).

      import java.sql.Connection;
      import java.sql.DriverManager;
      import java.sql.PreparedStatement;
      import java.sql.ResultSet;
      import java.sql.SQLException;
       
      public class MariaTest2 {
       
      	static String sql = "SELECT CODE, RN FROM (SELECT A.CODE, @cnt := @cnt + 1 AS RN FROM SOMESCHEMA.SOMETABLE A, (SELECT @cnt := 0) C) T";
      //	static String sql = "SELECT X.* FROM (SELECT CODE, RN FROM (SELECT A.CODE, @cnt := @cnt + 1 AS RN FROM SOMESCHEMA.SOMETABLE A, (SELECT @cnt := 0) C) T) X";
      	
      	static String connUrl = "jdbc:mariadb://url";
      	
      	
      	public static void main( String[] args ) throws SQLException {
      		Connection conn = null;
      		try {		
      			conn = DriverManager.getConnection( connUrl );
      			PreparedStatement stmt = conn.prepareStatement( sql );
      			ResultSet rs = stmt.executeQuery();
      			while ( rs.next() ) {
      				System.out.print( rs.getLong( "CODE" ) );
      				System.out.print( " " );
      				System.out.print( rs.getLong( "RN" ) );
      				System.out.println();
      			}
      			rs.close();
      			stmt.close();
       
      		} catch ( Exception e ) {
      			// TODO Auto-generated catch block
      			e.printStackTrace();
      		} finally {
      			conn.close();
      		}
       
      	}
       
      }
      

      Behaviour persists when using SET as separate execute before the query, e.g:

      String sql = "SELECT X.* FROM (SELECT CODE, RN FROM (SELECT A.CODE, @cnt := @cnt + 1 AS RN FROM SOMESCHEMA.SOMETABLE A) T) X";
      PreparedStatement stmt;			
      stmt = conn.prepareStatement( "SET @cnt := 0" );
      stmt.executeQuery();			
      stmt = conn.prepareStatement( sql );
      ResultSet rs = stmt.executeQuery();
      

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              diego dupin Diego Dupin
              Reporter:
              arturk Artur Kahre
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved: