MySQL Bugs: #92092: getTableName gives inconsistent results

Bug #92092	getTableName gives inconsistent results
Submitted:	20 Aug 2018 19:21	Modified:	21 Aug 2018 15:11
Reporter:	Paul Johnson	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	8.0.11, 5.7.17	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Depending upon query and data, getTableName will sometimes return the table name, and, sometimes return null/empty.

We've reproduced on older 5.7.17 server, and on newer 8.0.11 with the same behavior.

when query includes ORDER BY m.sender_name ASC the first sender_guid (col 2) is null, when query omits ORDER BY, getTableName returns sender_ha_cluster_map.

This could be related to bug https://bugs.mysql.com/bug.php?id=20191

How to repeat:
Sample java driver code:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;

/*
 * Demonstration of TableName returning as null depending upon query, bears some simularity to https://bugs.mysql.com/bug.php?id=20191
 */

public class TableNameBug {

       public static void main(String[] args) throws Exception {
              String password = "password";
        String jdbc = "jdbc:mysql://127.0.0.1/tablenamebug?useSSL=false&user=rootr&password="+password;
        
        Connection conn = DriverManager.getConnection(jdbc);
        
        test(conn,"SELECT ha.*,m.* FROM mcastsenders m left join sender_ha_cluster ha on m.sender_guid = ha.sender_guid");
        test(conn,"SELECT ha.*,m.* FROM mcastsenders m left join sender_ha_cluster ha on m.sender_guid = ha.sender_guid ORDER BY m.sender_name ASC");
       }

       private static void test(Connection conn, String query) throws SQLException {
        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery(query);

        ResultSetMetaData md = rs.getMetaData();
        System.out.println("Query: " + query);
        if("sender_ha_cluster_map".equalsIgnoreCase(md.getTableName(3)))
              System.out.println(" --> PASS");
        else
                    System.out.println(" --> FAIL");
        
        System.out.println("   All column metadata getTableName; getColumnName");
        for(int i=1;i<=md.getColumnCount();i++) {
          System.out.println("   Col " +  i + ": " + md.getTableName(i) + "; " + md.getColumnName(i));
        }
        stmt.close();
       }
}

And output:
Query: SELECT ha.*,m.* FROM mcastsenders m left join sender_ha_cluster ha on m.sender_guid = ha.sender_guid
 --> PASS
   All column metadata getTableName; getColumnName
   Col 1: null; primary_guid
   Col 2: sender_ha_cluster_map; sender_guid
   Col 3: sender_ha_cluster_map; cluster_id
   Col 4: sender_ha_cluster_map; ha_priority
   Col 5: sender_ha_cluster_map; ha_group
   Col 6: sender_ha_cluster_map; modified_time
   Col 7: mcastsenders; SENDER_GUID
   Col 8: mcastsenders; SENDER_NAME
   Col 9: mcastsenders; MODIFIED_TIME
Query: SELECT ha.*,m.* FROM mcastsenders m left join sender_ha_cluster ha on m.sender_guid = ha.sender_guid ORDER BY m.sender_name ASC
 --> FAIL
   All column metadata getTableName; getColumnName
   Col 1: null; primary_guid
   Col 2: null; sender_guid
   Col 3: null; cluster_id
   Col 4: null; ha_priority
   Col 5: null; ha_group
   Col 6: null; modified_time
   Col 7: mcastsenders; SENDER_GUID
   Col 8: mcastsenders; SENDER_NAME
   Col 9: mcastsenders; MODIFIED_TIME

mysql dump to create reproduction dataset

Attachment: tablenamebug.sql (application/octet-stream, text), 3.37 KiB.

Hi,

I don't believe this is a bug. 

What you get is:

Field   2:  `sender_guid`
Catalog:    `def`
Database:   ``
Table:      `ha`
Org_table:  `sender_ha_cluster_map`
Type:       VAR_STRING
Collation:  utf8mb4_0900_ai_ci (255)
Length:     400
Max_length: 0
Decimals:   0
Flags:      NO_DEFAULT_VALUE

vs

Field   2:  `sender_guid`
Catalog:    `def`
Database:   ``
Table:      `ha`
Org_table:  ``
Type:       VAR_STRING
Collation:  utf8mb4_0900_ai_ci (255)
Length:     400
Max_length: 0
Decimals:   0
Flags:      NO_DEFAULT_VALUE

I don't believe we are required to have Org_table here, especially as you did a sort but I'll have to check

Will update the bug as soon as I investigate this further. So the behavior is not in question (all versions where I tested including last 8 behave same), question is if this is a bug or not.

Bogdan

mysql [localhost] {msandbox} (tablenamebug) > SELECT sender_ha_cluster.*,mcastsenders.* FROM mcastsenders  left join sender_ha_cluster on mcastsenders.sender_guid = sender_ha_cluster.sender_guid ORDER BY mcastsenders.sender_name ASC;
Field   1:  `primary_guid`
Catalog:    `def`
Database:   ``
Table:      `sender_ha_cluster`
Org_table:  ``
Type:       VAR_STRING
Collation:  utf8mb4_0900_ai_ci (255)
Length:     400
Max_length: 0
Decimals:   0
Flags:

Field   2:  `sender_guid`
Catalog:    `def`
Database:   ``
Table:      `sender_ha_cluster`
Org_table:  ``
Type:       VAR_STRING
Collation:  utf8mb4_0900_ai_ci (255)
Length:     400
Max_length: 0
Decimals:   0
Flags:      NO_DEFAULT_VALUE

Field   3:  `cluster_id`
Catalog:    `def`
Database:   ``
Table:      `sender_ha_cluster`
Org_table:  ``
Type:       VAR_STRING
Collation:  utf8mb4_0900_ai_ci (255)
Length:     1020
Max_length: 0
Decimals:   0
Flags:      NO_DEFAULT_VALUE

Field   4:  `ha_priority`
Catalog:    `def`
Database:   ``
Table:      `sender_ha_cluster`
Org_table:  ``
Type:       LONG
Collation:  binary (63)
Length:     11
Max_length: 0
Decimals:   0
Flags:      NUM

Field   5:  `ha_group`
Catalog:    `def`
Database:   ``
Table:      `sender_ha_cluster`
Org_table:  ``
Type:       LONG
Collation:  binary (63)
Length:     11
Max_length: 0
Decimals:   0
Flags:      NUM

Field   6:  `modified_time`
Catalog:    `def`
Database:   ``
Table:      `sender_ha_cluster`
Org_table:  ``
Type:       TIMESTAMP
Collation:  binary (63)
Length:     19
Max_length: 0
Decimals:   0
Flags:      BINARY

Field   7:  `SENDER_GUID`
Catalog:    `def`
Database:   `tablenamebug`
Table:      `mcastsenders`
Org_table:  `mcastsenders`
Type:       VAR_STRING
Collation:  utf8mb4_0900_ai_ci (255)
Length:     400
Max_length: 1
Decimals:   0
Flags:      NOT_NULL NO_DEFAULT_VALUE

Field   8:  `SENDER_NAME`
Catalog:    `def`
Database:   `tablenamebug`
Table:      `mcastsenders`
Org_table:  `mcastsenders`
Type:       VAR_STRING
Collation:  utf8mb4_0900_ai_ci (255)
Length:     2048
Max_length: 1
Decimals:   0
Flags:      NOT_NULL NO_DEFAULT_VALUE

Field   9:  `MODIFIED_TIME`
Catalog:    `def`
Database:   `tablenamebug`
Table:      `mcastsenders`
Org_table:  `mcastsenders`
Type:       TIMESTAMP
Collation:  binary (63)
Length:     19
Max_length: 19
Decimals:   0
Flags:      NOT_NULL BINARY

+--------------+-------------+------------+-------------+----------+---------------+-------------+-------------+---------------------+
| primary_guid | sender_guid | cluster_id | ha_priority | ha_group | modified_time | SENDER_GUID | SENDER_NAME | MODIFIED_TIME       |
+--------------+-------------+------------+-------------+----------+---------------+-------------+-------------+---------------------+
| NULL         | NULL        | NULL       |        NULL |     NULL | NULL          | 1           | 1           | 2018-08-20 16:29:35 |
| NULL         | NULL        | NULL       |        NULL |     NULL | NULL          | 2           | 2           | 2018-08-20 16:29:35 |
| NULL         | NULL        | NULL       |        NULL |     NULL | NULL          | 3           | 3           | 2018-08-20 16:29:35 |
| NULL         | NULL        | NULL       |        NULL |     NULL | NULL          | 4           | 4           | 2018-08-20 16:29:35 |
| NULL         | NULL        | NULL       |        NULL |     NULL | NULL          | 5           | 5           | 2018-08-20 16:29:12 |
| NULL         | NULL        | NULL       |        NULL |     NULL | NULL          | 6           | 6           | 2018-08-20 16:27:10 |
| NULL         | NULL        | NULL       |        NULL |     NULL | NULL          | 7           | 7           | 2018-08-20 16:27:10 |
| NULL         | NULL        | NULL       |        NULL |     NULL | NULL          | 8           | 8           | 2018-08-20 16:27:10 |
+--------------+-------------+------------+-------------+----------+---------------+-------------+-------------+---------------------+
8 rows in set (0.01 sec)

mysql [localhost] {msandbox} (tablenamebug) >

Since "Table" is stored and "Org_table" is lost I'm tend to verify this as a bug but dev team might decide it is not. For now I'm verifying it as not having consistency here makes no sense.

Thanks for the report
Bogdan

I have a similar bug to report shortly. Is there any progress on this one?

This problem seems to be fixed in later 8.0 versions.
Can you check if it works out for you?

Bug #106633 is set as a duplicate of this bug. It actually extends this bug a bit

@Roy Lyseng I don't know you're asking me or the OP Paul Johnson, but the use case that I reported in #106633 is not reproducible 8.0.28, but is reproducible in 5.7.37.

@Roy Lyseng I don't know you're asking me or the OP Paul Johnson, but the use case that I reported in #106633 is not reproducible 8.0.28, but is reproducible in 5.7.37.

It was a question for either of you...
Are you able to upgrade to 8.0? It is a mature release and has been available for almost 4 years.

(Sorry about the double post earlier.)

We actually use AWS Aurora which only just recently made 8.0 available, and some engineers on our team are concerned about the prospect of switching to 8.0 because its Aurora support is so new.

Also, because of our anticipation of quirks such this bug, we will have to repeat a lot of testing performed with 5.7 before we can be confident we can upgrade to 8. We don't know what other quirks we may encounter.

I spoke with my team about our options. Is there a strong chance this bug will be fixed in 5.7 within a few months? If that is unlikely, we would just like to know whenever that is decided so we can get started testing with 8.0 for an eventual upgrade.

5.7 is close to the end of the Extended Support period
(see https://www.oracle.com/us/support/library/lifetime-support-technology-069183.pdf) and is not receiving many bug fixes. If you need critical bug fixes in 5.7, you will have to contact the MySQL Support Team.