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

    • Bug
    • Status: Closed (View Workflow)
    • Minor
    • Resolution: Won't Do
    • 1.4.5, 1.4.6
    • N/A
    • Other
    • 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

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

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.