Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-23341

shadowed field wrong column name metadata

Details

    Description

      Some shadowed field return alias name in place of column name. (This is only since 10.4)
      Connector then return an exception when using column name to retrieve data.

      To reproduced :

       
          try (Connection con = DriverManager.getConnection("jdbc:mariadb://localhost:3306/testj?user=root&log")) {
            Statement stmt = con.createStatement();
            stmt.execute("CREATE TABLE IF NOT EXISTS foo_outer (original_id varchar(100), key_p2 DATETIME, some_value text, PRIMARY KEY (original_id, key_p2))");
            stmt.execute("INSERT IGNORE INTO foo_outer (original_id, key_p2, some_value) VALUES ('oid_a', '2020-06-01 00:00', 'some_value_a')");
            stmt.execute("CREATE TABLE IF NOT EXISTS foo_inner (original_id varchar(100), other_id varchar(100), PRIMARY KEY (original_id, other_id))");
            stmt.execute("INSERT IGNORE INTO foo_inner (original_id, other_id) VALUES ('oid_a', 'otherid_1')");
            stmt.execute("INSERT IGNORE INTO foo_inner (original_id, other_id) VALUES ('oid_b', 'otherid_1')");
       
            ResultSet r = stmt.executeQuery("SELECT original_id AS alt_id, key_p2 as p2, some_value "
                + "FROM foo_outer "
                + "WHERE original_id IN ("
                + "SELECT DISTINCT(inner1.original_id) "
                + "FROM foo_inner AS inner1 "
                + "RIGHT JOIN foo_inner AS inner2 ON inner1.other_id = inner2.other_id "
                + "WHERE inner1.original_id = 'oid_a'"
                + ") ORDER BY key_p2");
            while (r.next()) {
              // will throw an error, since column name meta return alt_id
              ResultSetMetaData meta = r.getMetaData();
              System.out.println(meta.getColumnLabel(1)); // return alt_id, as expected
              System.out.println(meta.getColumnName(1)); // return alt_id, not original_id
       
              r.getString("original_id"); // will throw an exception
            }
          } catch (Exception ex) {
            ex.printStackTrace();
          }
      

      same code without order by work without issue.

      metadata is wrong :

      +--------------------------------------------------+
      |  0  1  2  3  4  5  6  7   8  9  a  b  c  d  e  f |
      +--------------------------------------------------+------------------+
      | 39 00 00 02 03 64 65 66  05 74 65 73 74 6A 09 66 | 9....def.testj.f |
      | 6F 6F 5F 6F 75 74 65 72  09 66 6F 6F 5F 6F 75 74 | oo_outer.foo_out |
      | 65 72 06 61 6C 74 5F 69  64 06 61 6C 74 5F 69 64 | er.alt_id.alt_id |
      | 0C E0 00 90 01 00 00 FD  01 10 00 00 00          | .............    |
      +--------------------------------------------------+------------------+
      

      with 10.3, no problem, original_id is returned as column name :

      +--------------------------------------------------+
      |  0  1  2  3  4  5  6  7   8  9  a  b  c  d  e  f |
      +--------------------------------------------------+------------------+
      | 3E 00 00 02 03 64 65 66  05 74 65 73 74 6A 09 66 | >....def.testj.f |
      | 6F 6F 5F 6F 75 74 65 72  09 66 6F 6F 5F 6F 75 74 | oo_outer.foo_out |
      | 65 72 06 61 6C 74 5F 69  64 0B 6F 72 69 67 69 6E | er.alt_id.origin |
      | 61 6C 5F 69 64 0C 2D 00  90 01 00 00 FD 01 10 00 | al_id.-......... |
      | 00 00                                            | ..               |
      +--------------------------------------------------+------------------+
      
      

      Attachments

        1. MDEV-23341.patch
          3 kB
        2. MDEV-23519.patch
          3 kB
        3. version.c
          2 kB

        Issue Links

          Activity

            Shouls it be problem for CC? (atually I can make test case with it). I will check at once...

            sanja Oleksandr Byelkin added a comment - Shouls it be problem for CC? (atually I can make test case with it). I will check at once...
            sanja Oleksandr Byelkin added a comment - - edited

            CREATE TABLE t1 (original_id varchar(100), key_p2
            DATETIME, some_value text, PRIMARY KEY (original_id, key_p2));
            INSERT IGNORE INTO t1 (original_id, key_p2, some_value) VALUES ('oid_a', '2020-06-01 00:00', 'some_value_a');
             
            CREATE TABLE t2 (original_id varchar(100), other_id varchar(100), PRIMARY KEY (original_id, other_id));
            INSERT IGNORE INTO t2 (original_id, other_id) VALUES ('oid_a', 'otherid_1');
            INSERT IGNORE INTO t2 (original_id, other_id) VALUES ('oid_b', 'otherid_1');
             
            SELECT original_id AS alt_id, key_p2 as p2, some_value
            FROM t1
            WHERE original_id IN (
              SELECT DISTINCT(inner1.original_id)
              FROM t2 AS inner1
                RIGHT JOIN t2 AS inner2 ON inner1.other_id = inner2.other_id
              WHERE inner1.original_id = 'oid_a'
              )
            ORDER BY key_p2;
             
            drop table t1;
            drop table t2;
            

            This test suite do not have problems with --ps-protocol so how I can repeat the problem in other ways?

            sanja Oleksandr Byelkin added a comment - - edited CREATE TABLE t1 (original_id varchar(100), key_p2 DATETIME, some_value text, PRIMARY KEY (original_id, key_p2)); INSERT IGNORE INTO t1 (original_id, key_p2, some_value) VALUES ('oid_a', '2020-06-01 00:00', 'some_value_a');   CREATE TABLE t2 (original_id varchar(100), other_id varchar(100), PRIMARY KEY (original_id, other_id)); INSERT IGNORE INTO t2 (original_id, other_id) VALUES ('oid_a', 'otherid_1'); INSERT IGNORE INTO t2 (original_id, other_id) VALUES ('oid_b', 'otherid_1'); SELECT original_id AS alt_id, key_p2 as p2, some_value FROM t1 WHERE original_id IN ( SELECT DISTINCT(inner1.original_id) FROM t2 AS inner1 RIGHT JOIN t2 AS inner2 ON inner1.other_id = inner2.other_id WHERE inner1.original_id = 'oid_a' ) ORDER BY key_p2;   drop table t1; drop table t2; This test suite do not have problems with --ps-protocol so how I can repeat the problem in other ways?

            I can reproduce it using Diego's example above and the latest Connector/J 2.7.2.

            Here is my code, test23341.java:

            import java.sql.*;
            public class test23341 {
            	public static void main(String args[]) {
            		System.out.println("welcome to area51");
            		try (Connection con = DriverManager.getConnection("jdbc:mariadb://localhost:3317/test", "root", "mysql")) {
            			Statement stmt = con.createStatement();
            			stmt.execute("CREATE TABLE IF NOT EXISTS foo_outer (original_id varchar(100), key_p2 DATETIME, some_value text, PRIMARY KEY (original_id, key_p2))");
            			stmt.execute("INSERT IGNORE INTO foo_outer (original_id, key_p2, some_value) VALUES ('oid_a', '2020-06-01 00:00', 'some_value_a')");
            			stmt.execute("CREATE TABLE IF NOT EXISTS foo_inner (original_id varchar(100), other_id varchar(100), PRIMARY KEY (original_id, other_id))");
            			stmt.execute("INSERT IGNORE INTO foo_inner (original_id, other_id) VALUES ('oid_a', 'otherid_1')");
            			stmt.execute("INSERT IGNORE INTO foo_inner (original_id, other_id) VALUES ('oid_b', 'otherid_1')");
            			ResultSet r = stmt.executeQuery("SELECT original_id AS alt_id, key_p2 as p2, some_value "
            			+ "FROM foo_outer "
            			+ "WHERE original_id IN ("
            			+ "SELECT DISTINCT(inner1.original_id) "
            			+ "FROM foo_inner AS inner1 "
            			+ "RIGHT JOIN foo_inner AS inner2 ON inner1.other_id = inner2.other_id "
            			+ "WHERE inner1.original_id = 'oid_a'"
            			+ ") ORDER BY key_p2");
            			while (r.next()) {
            				// will throw an error, since column name meta return alt_id
            				ResultSetMetaData meta = r.getMetaData();
            				System.out.println(meta.getColumnLabel(1)); // return alt_id, as expected
            				System.out.println(meta.getColumnName(1)); // return alt_id, not original_id
            				r.getString("original_id"); // will throw an exception
            			}
            		} catch (Exception ex) {
            			ex.printStackTrace();
            		}
            	}
            }
            

            You can see it works in MariaDB 10.2:

            C:\chris>javac test23341.java
             
            C:\chris>"C:\Program Files (x86)\Java\jre1.8.0_161\bin\java.exe" -cp "./mysql-connector-java-3.1.12-bin.jar;." test23341
            welcome to area51
            alt_id
            original_id
            

            You can see it works in MariaDB 10.3:

            C:\chris>javac test23341.java
             
            C:\chris>"C:\Program Files (x86)\Java\jre1.8.0_161\bin\java.exe" -cp "./mysql-connector-java-3.1.12-bin.jar;." test23341
            welcome to area51
            alt_id
            original_id
            

            And you can see it fails in MariaDB 10.4:

            C:\chris>javac test23341.java
             
            C:\chris>"C:\Program Files (x86)\Java\jre1.8.0_161\bin\java.exe" -cp "./mysql-connector-java-3.1.12-bin.jar;." test23341
            welcome to area51
            alt_id
            alt_id
            java.sql.SQLSyntaxErrorException: No such column: 'original_id'. 'original_id' must be in [p2, foo_outer.some_value, foo_outer.alt_id, foo_outer.p2, some_value, alt_id]
                    at org.mariadb.jdbc.internal.util.exceptions.ExceptionFactory.createException(ExceptionFactory.java:62)
                    at org.mariadb.jdbc.internal.util.exceptions.ExceptionFactory.create(ExceptionFactory.java:187)
                    at org.mariadb.jdbc.internal.com.read.dao.ColumnLabelIndexer.getIndex(ColumnLabelIndexer.java:137)
                    at org.mariadb.jdbc.internal.com.read.resultset.SelectResultSet.findColumn(SelectResultSet.java:1319)
                    at org.mariadb.jdbc.internal.com.read.resultset.SelectResultSet.getString(SelectResultSet.java:953)
                    at test23341.main(test23341.java:25)
            

            ccalender Chris Calender (Inactive) added a comment - I can reproduce it using Diego's example above and the latest Connector/J 2.7.2. Here is my code, test23341.java: import java.sql.*; public class test23341 { public static void main(String args[]) { System.out.println("welcome to area51"); try (Connection con = DriverManager.getConnection("jdbc:mariadb://localhost:3317/test", "root", "mysql")) { Statement stmt = con.createStatement(); stmt.execute("CREATE TABLE IF NOT EXISTS foo_outer (original_id varchar(100), key_p2 DATETIME, some_value text, PRIMARY KEY (original_id, key_p2))"); stmt.execute("INSERT IGNORE INTO foo_outer (original_id, key_p2, some_value) VALUES ('oid_a', '2020-06-01 00:00', 'some_value_a')"); stmt.execute("CREATE TABLE IF NOT EXISTS foo_inner (original_id varchar(100), other_id varchar(100), PRIMARY KEY (original_id, other_id))"); stmt.execute("INSERT IGNORE INTO foo_inner (original_id, other_id) VALUES ('oid_a', 'otherid_1')"); stmt.execute("INSERT IGNORE INTO foo_inner (original_id, other_id) VALUES ('oid_b', 'otherid_1')"); ResultSet r = stmt.executeQuery("SELECT original_id AS alt_id, key_p2 as p2, some_value " + "FROM foo_outer " + "WHERE original_id IN (" + "SELECT DISTINCT(inner1.original_id) " + "FROM foo_inner AS inner1 " + "RIGHT JOIN foo_inner AS inner2 ON inner1.other_id = inner2.other_id " + "WHERE inner1.original_id = 'oid_a'" + ") ORDER BY key_p2"); while (r.next()) { // will throw an error, since column name meta return alt_id ResultSetMetaData meta = r.getMetaData(); System.out.println(meta.getColumnLabel(1)); // return alt_id, as expected System.out.println(meta.getColumnName(1)); // return alt_id, not original_id r.getString("original_id"); // will throw an exception } } catch (Exception ex) { ex.printStackTrace(); } } } You can see it works in MariaDB 10.2: C:\chris>javac test23341.java   C:\chris>"C:\Program Files (x86)\Java\jre1.8.0_161\bin\java.exe" -cp "./mysql-connector-java-3.1.12-bin.jar;." test23341 welcome to area51 alt_id original_id You can see it works in MariaDB 10.3: C:\chris>javac test23341.java   C:\chris>"C:\Program Files (x86)\Java\jre1.8.0_161\bin\java.exe" -cp "./mysql-connector-java-3.1.12-bin.jar;." test23341 welcome to area51 alt_id original_id And you can see it fails in MariaDB 10.4: C:\chris>javac test23341.java   C:\chris>"C:\Program Files (x86)\Java\jre1.8.0_161\bin\java.exe" -cp "./mysql-connector-java-3.1.12-bin.jar;." test23341 welcome to area51 alt_id alt_id java.sql.SQLSyntaxErrorException: No such column: 'original_id'. 'original_id' must be in [p2, foo_outer.some_value, foo_outer.alt_id, foo_outer.p2, some_value, alt_id] at org.mariadb.jdbc.internal.util.exceptions.ExceptionFactory.createException(ExceptionFactory.java:62) at org.mariadb.jdbc.internal.util.exceptions.ExceptionFactory.create(ExceptionFactory.java:187) at org.mariadb.jdbc.internal.com.read.dao.ColumnLabelIndexer.getIndex(ColumnLabelIndexer.java:137) at org.mariadb.jdbc.internal.com.read.resultset.SelectResultSet.findColumn(SelectResultSet.java:1319) at org.mariadb.jdbc.internal.com.read.resultset.SelectResultSet.getString(SelectResultSet.java:953) at test23341.main(test23341.java:25)
            sanja Oleksandr Byelkin added a comment - - edited

            I can not put java in our test suite.

            sanja Oleksandr Byelkin added a comment - - edited I can not put java in our test suite.
            diego dupin Diego Dupin added a comment -

            Here is the test to reproduce error : MDEV-23519.patch
            Problem only occurs when using InnoDB engine, so in order to reproduce it, test must be run as :

            perl mysql-test\mysql-test-run.pl --mem mysql_client_test --mysqld=--innodb=ON
            

            diego dupin Diego Dupin added a comment - Here is the test to reproduce error : MDEV-23519.patch Problem only occurs when using InnoDB engine, so in order to reproduce it, test must be run as : perl mysql-test\mysql-test-run.pl --mem mysql_client_test --mysqld=--innodb=ON
            shulga Dmitry Shulga added a comment -

            This bug report is not reproduced against the latest MariaDB Server 10.4.22 (I pulled the latest changes from the branch 10.4)

            Both test case provided in the file version.c and in the file MDEV-23519.patch are finished successfully.

            Please re-check whether the bug report is still reproduced in Java and elaborate the C test if it does.

            shulga Dmitry Shulga added a comment - This bug report is not reproduced against the latest MariaDB Server 10.4.22 (I pulled the latest changes from the branch 10.4) Both test case provided in the file version.c and in the file MDEV-23519 .patch are finished successfully. Please re-check whether the bug report is still reproduced in Java and elaborate the C test if it does.
            diego dupin Diego Dupin added a comment -

            tested with 10.4 protocol exchanges are now ok (metadata alias and original name are now accurate).

            diego dupin Diego Dupin added a comment - tested with 10.4 protocol exchanges are now ok (metadata alias and original name are now accurate).
            ralf.gebhardt Ralf Gebhardt added a comment - - edited

            Bug was fixed with MDEV-23519

            ralf.gebhardt Ralf Gebhardt added a comment - - edited Bug was fixed with MDEV-23519

            People

              diego dupin Diego Dupin
              diego dupin Diego Dupin
              Votes:
              4 Vote for this issue
              Watchers:
              10 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.