MySQL Bugs: #79641: COM_QUERY Response metadata with '*' in field org

Bug #79641	COM_QUERY Response metadata with '*' in field org_table
Submitted:	14 Dec 2015 17:37	Modified:	25 Oct 2016 15:31
Reporter:	Filipe Silva	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	5.7.10	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
In certain situations a COM_QUERY Response contains the value '*' in the field org_table for 5.7 servers while 5.6 servers don't. This difference breaks Connector/J when interpreting the resulting meta data. See also Bug#74723.

How to repeat:
The sequence of commands:

mysql (test) > CREATE TABLE tbl (c CHAR(1));
mysql (test) > INSERT INTO tbl VALUES ('A');
mysql (test) > SELECT * FROM (SELECT c FROM tbl) T;

result in the following protocol traces:

1. In MySQL 5.6.28:
00000167  1d 00 00 00 03 43 52 45  41 54 45 20 54 41 42 4c .....CRE ATE TABL
00000177  45 20 74 62 6c 20 28 63  20 43 48 41 52 28 31 29 E tbl (c  CHAR(1)
00000187  29                                               )
    0000049B  07 00 00 01 00 00 00 02  00 00 00                ........ ...
00000188  1d 00 00 00 03 49 4e 53  45 52 54 20 49 4e 54 4f .....INS ERT INTO
00000198  20 74 62 6c 20 56 41 4c  55 45 53 20 28 27 41 27  tbl VAL UES ('A'
000001A8  29                                               )
    000004A6  07 00 00 01 00 01 00 02  00 00 00                ........ ...
000001A9  24 00 00 00 03 53 45 4c  45 43 54 20 2a 20 46 52 $....SEL ECT * FR
000001B9  4f 4d 20 28 53 45 4c 45  43 54 20 63 20 46 52 4f OM (SELE CT c FRO
000001C9  4d 20 74 62 6c 29 20 54                          M tbl) T
    000004B1  01 00 00 01 01 1c 00 00  02 03 64 65 66 00 01 54 ........ ..def..T
    000004C1  03 74 62 6c 01 63 01 63  0c 21 00 03 00 00 00 fe .tbl.c.c .!......
    000004D1  00 00 00 00 00 05 00 00  03 fe 00 00 22 00 02 00 ........ ...."...
    000004E1  00 04 01 41 05 00 00 05  fe 00 00 22 00          ...A.... ...".

2. In MySQL 5.7.10:
00000000  1d 00 00 00 03 43 52 45  41 54 45 20 54 41 42 4c .....CRE ATE TABL
00000010  45 20 74 62 6c 20 28 63  20 43 48 41 52 28 31 29 E tbl (c  CHAR(1)
00000020  29                                               )
    00000000  07 00 00 01 00 00 00 02  00 00 00                ........ ...
00000021  1d 00 00 00 03 49 4e 53  45 52 54 20 49 4e 54 4f .....INS ERT INTO
00000031  20 74 62 6c 20 56 41 4c  55 45 53 20 28 27 41 27  tbl VAL UES ('A'
00000041  29                                               )
    0000000B  07 00 00 01 00 01 00 02  00 00 00                ........ ...
00000042  24 00 00 00 03 53 45 4c  45 43 54 20 2a 20 46 52 $....SEL ECT * FR
00000052  4f 4d 20 28 53 45 4c 45  43 54 20 63 20 46 52 4f OM (SELE CT c FRO
00000062  4d 20 74 62 6c 29 20 54                          M tbl) T
    00000016  01 00 00 01 01 1e 00 00  02 03 64 65 66 04 74 65 ........ ..def.te
    00000026  73 74 01 54 01 2a 01 63  01 63 0c 21 00 03 00 00 st.T.*.c .c.!....
    00000036  00 fe 00 00 00 00 00 02  00 00 03 01 41 07 00 00 ........ ....A...
    00000046  04 fe 00 00 22 00 00 00                          ...."... 

With the relevant differences in the COM_QUERY response metadata:
          | 5.6.28 | 5.7.10
----------+--------+-------
catalog   | def    | def
schema    |        | test
table     | T      | T
org_table | tbl    | *
name      | c      | c
org_name  | c      | c

The schema info is now filled, which seems to be a fix that should be backported, if possible.

But the replacement of the org_table info by '*' is a problem for the connector.

Suggested fix:
Org_table should be 'tbl' as in MySQL 5.6.

btw, you don't need packet traces to see this. client has an option...

E:\mysql-5.7.10-winx64\bin>mysql -root test -P3307 --column-type-info
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 5.7.10 MySQL Community Server (GPL)

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> CREATE TABLE tbl (c CHAR(1));
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO tbl VALUES ('A');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM (SELECT c FROM tbl) T;
Field   1:  `c`
Catalog:    `def`
Database:   `test`
Table:      `T`
Org_table:  `*`
Type:       STRING
Collation:  cp850_general_ci (4)
Length:     1
Max_length: 1
Decimals:   0
Flags:

+------+
| c    |
+------+
| A    |
+------+
1 row in set (0.00 sec)

Nice :). Didn't know that, thanks!

Posted by developer:
 
Noted in 5.7.17, 8.0.1 changelogs.

In the MYSQL_FIELD C API structure, the org_table value for derived
tables was *, which could cause failure for queries that depend on
this value. The org_table value for views and derived tables now is
set as follows: If the column is selected from a view, org_table
names the view. If the column is selected from a derived table,
org_table names the base table. If a derived table wraps a view,
org_table still names the base table. If the column is an expression,
org_table is the empty string.