Bug #89442 Connector/J ResultSetMetaData getTableName() returns table alias for inline view

• Submitted: 26 Jan 2018 20:17
• Modified: 30 Jan 2018 16:58
• Reporter: xobaa Corporation
• Status: Not a Bug
• Category: Connector / J
• Severity: S3 (Non-critical)
• Version: 5.7.21
• OS: Any
• CPU Architecture: Any
• Tags: Connector/J jdbc metadata

[26 Jan 2018 20:17] xobaa Corporation

Description:
When using ResultSetMetaData getTableName() with Connector/J to get the table name for a column that is part of an inline view (derived table) where the column's table is aliased inside the inline view, the table alias is returned, not the table name:

   select t.col from ( select tt.col from test tt ) t

In 5.7.21 the table name returned for 'col' is 'tt'

This is a behavior change since 5.5.  In 5.5.54 the table name returned for 'col' is 'test'.

The table name returned for tables that are not aliased inside inline views is the correct table name in compliance with JDBC.

How to repeat:
create database test;
create user test identified by 'test';
grant all privileges on test.* to test;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.Statement;

public class AliasTest
{
   public static void main( String[] args )
   {
      try
      {
         Connection conn = null;
         Statement stmt = null;

         Class.forName( "com.mysql.jdbc.Driver" );
         conn = DriverManager.getConnection( "jdbc:mysql://localhost/test", "test", "test" );
         stmt = conn.createStatement();
         stmt.executeUpdate( "drop table if exists test" );
         stmt.executeUpdate( "create table test( col int )" );
         stmt.close();

         PreparedStatement ps = conn.prepareStatement( "select col from test" );
         System.out.println( ps.getMetaData().getTableName( 1 ) );
         ps.close();

         ps = conn.prepareStatement( "select t.col from test t" );
         System.out.println( ps.getMetaData().getTableName( 1 ) );
         ps.close();

         ps = conn.prepareStatement( "select t.col from ( select col from test ) t" );
         System.out.println( ps.getMetaData().getTableName( 1 ) );
         ps.close();

         ps = conn.prepareStatement( "select t.col from ( select col from test tt ) t" );
         System.out.println( ps.getMetaData().getTableName( 1 ) );
         ps.close();

         ps = conn.prepareStatement( "select t.col from ( select tt.col from test tt ) t" );
         System.out.println( ps.getMetaData().getTableName( 1 ) );
         ps.close();

         conn.close();

      } catch( Exception e )
      {
         e.printStackTrace();
      }
   }
}

5.7.21 output:

test
test
test
tt
tt

5.5.54 output:

test
test
test
test
test

Suggested fix:
Modify ResultSetMetaData getTableName() to return the table name, not the table alias, when a table is aliased inside an inline view (derived table)

[29 Jan 2018 10:03] Chiranjeevi Battula

Hello xobaa,

Thank you for the bug report and testcase.
Verified this behavior on MySQL Connector / J 5.1.45.

Thanks,
Chiranjeevi.

[30 Jan 2018 16:58] Filipe Silva

Hi,

MySQL 5.7 (since version 5.7.6) executes sub-queries differently than before by materializing the data instead of using temporary tables only, which results in improved overall performance. As a side effect the derived tables end up begin exposed with their aliases instead of the original table names.

Please consult the topic *Optimizer notes* in https://dev.mysql.com/doc/relnotes/mysql/5.7/en/news-5-7-6.html and also https://dev.mysql.com/doc/refman/5.7/en/subquery-materialization.html for detailed information.

Later on, since MySQL 5.7.17, Bug#79641 fixed a bug on the table names being returned in this same situation (it was "*" instead of the temporary table name, i.e., its alias).

As you can see in the documentation I pointed before, you can workaround this by turning off the optimizer switch 'derived_merge' with:
  SET optimizer_switch = 'derived_merge=off';
Another option is to use any of the methods that turn this feature off if they meet your needs (as per documented in https://dev.mysql.com/doc/refman/5.7/en/derived-table-optimization.html). For example adding the clause DISCTINCT in the sub-query ends up causing the behavior you expected in Connector/J.

Therefore, this is not a bug, at least not in Connector/J side.

You can check progress on this subject in Bug#89473.

Thank you,