MySQL Bugs: #83445: highly recommended for mysql to show table name with generated column

Bug #83445	highly recommended for mysql to show table name with generated column
Submitted:	19 Oct 2016 12:09	Modified:	19 Oct 2016 15:55
Reporter:	帅 Bang	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: DDL	Severity:	S3 (Non-critical)
Version:	5.7	OS:	Linux
Assigned to:		CPU Architecture:	Any

Description:
mysql> create table t1(a int, b int, c int as (1+t2.a));
Query OK, 0 rows affected (0.00 sec)

mysql> show create table t1;
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                              |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t1    | CREATE TABLE `t1` (
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL,
  `c` int(11) GENERATED ALWAYS AS ((1 + `a`)) VIRTUAL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

IMHO,  `c` int(11) GENERATED ALWAYS AS ((1 + `a`)) VIRTUAL 

should be

 `c` int(11) GENERATED ALWAYS AS ((1 + `t2.a`)) VIRTUAL   // 't2.a' , not 'a'

How to repeat:
 create table t1(a int, b int, c int as (1+t2.a));
 show create table t1;

Suggested fix:
 `c` int(11) GENERATED ALWAYS AS ((1 + `t2.a`)) VIRTUAL

rather than

  `c` int(11) GENERATED ALWAYS AS ((1 + `a`)) VIRTUAL

is displayed

it surprises me that it is legal to use references to another table in the expression defining a virtual/computed column.  But I don't know about otehr RDBMs and standards in this respect.

-- MySQL:
CREATE TABLE tt2(a INT, b INT);
CREATE TABLE tt1(a INT, b INT, c INT AS (1+tt2.a)); -- success
CREATE TABLE tt4(a INT, b INT, c INT AS (1+tt3.a)); -- also succeeds though no table `tt3` exists

-- MariaDB:
CREATE TABLE tt2(a INT, b INT);
CREATE TABLE tt1(a INT, b INT, c INT AS (1+tt2.a));
-- Error Code: 1054
-- Unknown column 'tt2.a' in 'virtual column function'

-- Peter
-- not a MySQL/Oracle eprson

Also here is no protection against DROPPING or ALTERING the referenced table invalidating the expression - like for instance (with my example): 

ALTER TABLE `tt2` CHANGE `a` `c` INT(11) NULL;

Try with some data

INSERT INTO `tt2` (`c`, `b`) VALUES ('1', '2'); 
INSERT INTO `tt2` (`c`, `b`) VALUES ('3', '4'); 
INSERT INTO `tt1` (`a`, `b`) VALUES ('11', '22'); 
INSERT INTO `tt1` (`a`, `b`) VALUES ('33', '44'); 
SELECT * FROM `tt1`;
/*
     a       b       c  
------  ------  --------
    11      22        12
    33      44        34
*/

Does it make any sense whatsoever? I don't think so.

Adding a Pk on first column of both tables makes no diference.

Oracle:

https://oracle-base.com/articles/11g/virtual-columns-11gr1

The expression used in the virtual column definition has the following restrictions:
...
It can only refer to columns defined in the same table.
...

SQL server: 

https://msdn.microsoft.com/en-us/library/ms188300.aspx

"A computed column expression can use data from other columns to calculate a value for the column ..".  Though not very explicit I would read it like "A computed column expression can use data from other columns [of the table] to calculate a value for the column"

Thank you for the bug report. t2 is ignored and column applied if exist in t1 table anyway a bug or documentation issue?.

Server version: 5.7.17-debug Source distribution PULL: 2016-OCT-14

Copyright (c) 2000, 2016, 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 5.7 > use test
Database changed
mysql 5.7 >  create table t1(a int, b int, c int as (1+t2.a));
Query OK, 0 rows affected (0.31 sec)

mysql 5.7 >  show create table t1;
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                             |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t1    | CREATE TABLE `t1` (
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL,
  `c` int(11) GENERATED ALWAYS AS ((1 + `a`)) VIRTUAL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.02 sec)

mysql 5.7 > drop table t1;
Query OK, 0 rows affected (0.15 sec)

mysql 5.7 >  create table t1(a int, b int, c int as (1+t2.k));
ERROR 1054 (42S22): Unknown column 'k' in 'generated column function'