|
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();
|
|