summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--include/my_base.h3
-rw-r--r--mysql-test/r/features.result1
-rw-r--r--mysql-test/r/invisible_binlog.result65
-rw-r--r--mysql-test/r/invisible_field.result526
-rw-r--r--mysql-test/t/invisible_binlog.test32
-rw-r--r--mysql-test/t/invisible_field.test216
-rw-r--r--sql/field.cc6
-rw-r--r--sql/field.h6
-rw-r--r--sql/lex.h1
-rw-r--r--sql/mysqld.cc1
-rw-r--r--sql/share/errmsg-utf8.txt2
-rw-r--r--sql/sql_base.cc19
-rw-r--r--sql/sql_class.h10
-rw-r--r--sql/sql_insert.cc5
-rw-r--r--sql/sql_show.cc36
-rw-r--r--sql/sql_table.cc165
-rw-r--r--sql/sql_yacc.yy8
-rw-r--r--sql/sql_yacc_ora.yy6
-rw-r--r--sql/table.cc25
-rw-r--r--sql/table.h11
-rw-r--r--sql/unireg.cc35
-rw-r--r--sql/unireg.h1
22 files changed, 1144 insertions, 36 deletions
diff --git a/include/my_base.h b/include/my_base.h
index 1e7cacd3426..71dca2e831a 100644
--- a/include/my_base.h
+++ b/include/my_base.h
@@ -282,7 +282,8 @@ enum ha_base_keytype {
This flag can be calculated -- it's based on key lengths comparison.
*/
#define HA_KEY_HAS_PART_KEY_SEG 65536
-
+/* Internal Flag Can be calcaluted */
+#define HA_INVISIBLE_KEY 2<<18
/* Automatic bits in key-flag */
#define HA_SPACE_PACK_USED 4 /* Test for if SPACE_PACK used */
diff --git a/mysql-test/r/features.result b/mysql-test/r/features.result
index c6d1a6b0bac..fc276e2f885 100644
--- a/mysql-test/r/features.result
+++ b/mysql-test/r/features.result
@@ -8,6 +8,7 @@ Feature_delay_key_write 0
Feature_dynamic_columns 0
Feature_fulltext 0
Feature_gis 0
+Feature_invisible_columns 0
Feature_locale 0
Feature_subquery 0
Feature_timezone 0
diff --git a/mysql-test/r/invisible_binlog.result b/mysql-test/r/invisible_binlog.result
new file mode 100644
index 00000000000..088bc858e28
--- /dev/null
+++ b/mysql-test/r/invisible_binlog.result
@@ -0,0 +1,65 @@
+include/master-slave.inc
+[connection master]
+connection master;
+create table t1(a int , b int invisible);
+insert into t1 values(1);
+insert into t1(a,b) values(2,2);
+select a,b from t1;
+a b
+1 NULL
+2 2
+desc t1;
+Field Type Null Key Default Extra
+a int(11) YES NULL
+b int(11) YES NULL INVISIBLE
+create table t2(a int , b int invisible default 5);
+insert into t2 values(1);
+insert into t2(a,b) values(2,2);
+select a,b from t2;
+a b
+1 5
+2 2
+desc t2;
+Field Type Null Key Default Extra
+a int(11) YES NULL
+b int(11) YES 5 INVISIBLE
+connection slave;
+select * from t1;
+a
+1
+2
+select a,b from t1;
+a b
+1 NULL
+2 2
+desc t1;
+Field Type Null Key Default Extra
+a int(11) YES NULL
+b int(11) YES NULL INVISIBLE
+show create table t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `a` int(11) DEFAULT NULL,
+ `b` int(11) INVISIBLE DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+select * from t2;
+a
+1
+2
+select a,b from t2;
+a b
+1 5
+2 2
+desc t2;
+Field Type Null Key Default Extra
+a int(11) YES NULL
+b int(11) YES 5 INVISIBLE
+show create table t2;
+Table Create Table
+t2 CREATE TABLE `t2` (
+ `a` int(11) DEFAULT NULL,
+ `b` int(11) INVISIBLE DEFAULT 5
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+connection master;
+drop table t1,t2;
+include/rpl_end.inc
diff --git a/mysql-test/r/invisible_field.result b/mysql-test/r/invisible_field.result
new file mode 100644
index 00000000000..22bc1063649
--- /dev/null
+++ b/mysql-test/r/invisible_field.result
@@ -0,0 +1,526 @@
+FLUSH STATUS;
+create table t1(abc int primary key, xyz int invisible);
+SHOW STATUS LIKE 'Feature_invisible_columns';
+Variable_name Value
+Feature_invisible_columns 1
+desc t1;
+Field Type Null Key Default Extra
+abc int(11) NO PRI NULL
+xyz int(11) YES NULL INVISIBLE
+show create table t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `abc` int(11) NOT NULL,
+ `xyz` int(11) INVISIBLE DEFAULT NULL,
+ PRIMARY KEY (`abc`)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+select * from INFORMATION_SCHEMA.COLUMNS where TABLE_SCHEMA='test' and TABLE_NAME='t1';
+TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME ORDINAL_POSITION COLUMN_DEFAULT IS_NULLABLE DATA_TYPE CHARACTER_MAXIMUM_LENGTH CHARACTER_OCTET_LENGTH NUMERIC_PRECISION NUMERIC_SCALE DATETIME_PRECISION CHARACTER_SET_NAME COLLATION_NAME COLUMN_TYPE COLUMN_KEY EXTRA PRIVILEGES COLUMN_COMMENT IS_GENERATED GENERATION_EXPRESSION
+def test t1 abc 1 NULL NO int NULL NULL 10 0 NULL NULL NULL int(11) PRI select,insert,update,references NEVER NULL
+def test t1 xyz 2 NULL YES int NULL NULL 10 0 NULL NULL NULL int(11) INVISIBLE select,insert,update,references NEVER NULL
+drop table t1;
+create table t1(a1 int invisible);
+ERROR 42000: A table must have at least 1 column
+create table t1(a1 blob,invisible(a1));
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '(a1))' at line 1
+create table t1(a1 int primary key invisible ,a2 int unique invisible , a3 blob,a4 int not null invisible unique);
+ERROR HY000: Invisible column `a1` must have a default value
+create table t1(abc int not null invisible);
+ERROR HY000: Invisible column `abc` must have a default value
+create table t1(a int invisible, b int);
+insert into t1 values(1);
+insert into t1(a) values(2);
+insert into t1(b) values(3);
+insert into t1(a,b) values(5,5);
+select * from t1;
+b
+1
+NULL
+3
+5
+select a,b from t1;
+a b
+NULL 1
+2 NULL
+NULL 3
+5 5
+delete from t1;
+insert into t1 values(1),(2),(3),(4);
+select * from t1;
+b
+1
+2
+3
+4
+select a from t1;
+a
+NULL
+NULL
+NULL
+NULL
+drop table t1;
+#more complex case of invisible
+create table t1(a int , b int invisible , c int invisible auto_increment unique, d blob , e int unique, f int);
+desc t1;
+Field Type Null Key Default Extra
+a int(11) YES NULL
+b int(11) YES NULL INVISIBLE
+c int(11) NO PRI NULL auto_increment, INVISIBLE
+d blob YES NULL
+e int(11) YES UNI NULL
+f int(11) YES NULL
+insert into t1 values(1,'d blob',1,1),(1,'d blob',11,1),(1,'d blob',2,1),(1,'d blob',3,1),(1,'d blob',41,1);
+select * from t1;
+a d e f
+1 d blob 1 1
+1 d blob 11 1
+1 d blob 2 1
+1 d blob 3 1
+1 d blob 41 1
+select a,b,c,d,e,f from t1;
+a b c d e f
+1 NULL 1 d blob 1 1
+1 NULL 2 d blob 11 1
+1 NULL 3 d blob 2 1
+1 NULL 4 d blob 3 1
+1 NULL 5 d blob 41 1
+drop table t1;
+#more complex case of invisible with sql_mode=NO_AUTO_VALUE_ON_ZERO
+set sql_mode='NO_AUTO_VALUE_ON_ZERO';
+create table t1(a int , b int invisible , c int invisible auto_increment unique, d blob , e int unique, f int);
+desc t1;
+Field Type Null Key Default Extra
+a int(11) YES NULL
+b int(11) YES NULL INVISIBLE
+c int(11) NO PRI NULL auto_increment, INVISIBLE
+d blob YES NULL
+e int(11) YES UNI NULL
+f int(11) YES NULL
+insert into t1 values(1,'d blob',1,1),(1,'d blob',11,1),(1,'d blob',2,1),(1,'d blob',3,1),(1,'d blob',41,1);
+select * from t1;
+a d e f
+1 d blob 1 1
+1 d blob 11 1
+1 d blob 2 1
+1 d blob 3 1
+1 d blob 41 1
+select a,b,c,d,e,f from t1;
+a b c d e f
+1 NULL 1 d blob 1 1
+1 NULL 2 d blob 11 1
+1 NULL 3 d blob 2 1
+1 NULL 4 d blob 3 1
+1 NULL 5 d blob 41 1
+drop table t1;
+set sql_mode='';
+create table sdsdsd(a int , b int, invisible(a,b));
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '(a,b))' at line 1
+create table t1(a int,abc int as (a mod 3) virtual invisible);
+desc t1;
+Field Type Null Key Default Extra
+a int(11) YES NULL
+abc int(11) YES NULL VIRTUAL GENERATED, INVISIBLE
+insert into t1 values(1,default);
+ERROR 21S01: Column count doesn't match value count at row 1
+insert into t1 values(1),(22),(233);
+select * from t1;
+a
+1
+22
+233
+select a,abc from t1;
+a abc
+1 1
+22 1
+233 2
+drop table t1;
+create table t1(abc int primary key invisible auto_increment, a int);
+desc t1;
+Field Type Null Key Default Extra
+abc int(11) NO PRI NULL auto_increment, INVISIBLE
+a int(11) YES NULL
+show create table t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `abc` int(11) NOT NULL INVISIBLE AUTO_INCREMENT,
+ `a` int(11) DEFAULT NULL,
+ PRIMARY KEY (`abc`)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+insert into t1 values(1);
+insert into t1 values(2);
+insert into t1 values(3);
+select * from t1;
+a
+1
+2
+3
+select abc,a from t1;
+abc a
+1 1
+2 2
+3 3
+delete from t1;
+insert into t1 values(1),(2),(3),(4),(6);
+select abc,a from t1;
+abc a
+4 1
+5 2
+6 3
+7 4
+8 6
+drop table t1;
+create table t1(abc int);
+alter table t1 change abc ss int invisible;
+ERROR 42000: A table must have at least 1 column
+alter table t1 add column xyz int;
+alter table t1 modify column abc int ;
+desc t1;
+Field Type Null Key Default Extra
+abc int(11) YES NULL
+xyz int(11) YES NULL
+insert into t1 values(22);
+ERROR 21S01: Column count doesn't match value count at row 1
+alter table t1 modify column abc int invisible;
+desc t1;
+Field Type Null Key Default Extra
+abc int(11) YES NULL INVISIBLE
+xyz int(11) YES NULL
+insert into t1 values(12);
+drop table t1;
+#some test on copy table structure with table data;
+#table with invisible fields and unique keys;
+create table t1(a int , b int invisible , c int invisible auto_increment unique, d blob , e int unique, f int);
+desc t1;
+Field Type Null Key Default Extra
+a int(11) YES NULL
+b int(11) YES NULL INVISIBLE
+c int(11) NO PRI NULL auto_increment, INVISIBLE
+d blob YES NULL
+e int(11) YES UNI NULL
+f int(11) YES NULL
+insert into t1 values(1,'d blob',1,1),(1,'d blob',11,1),(1,'d blob',2,1),(1,'d blob',3,1),(1,'d blob',41,1);
+select * from t1;
+a d e f
+1 d blob 1 1
+1 d blob 11 1
+1 d blob 2 1
+1 d blob 3 1
+1 d blob 41 1
+select a,b,c,d,e,f from t1;
+a b c d e f
+1 NULL 1 d blob 1 1
+1 NULL 2 d blob 11 1
+1 NULL 3 d blob 2 1
+1 NULL 4 d blob 3 1
+1 NULL 5 d blob 41 1
+#this won't copy invisible fields and keys;
+create table t2 as select * from t1;
+desc t2;
+Field Type Null Key Default Extra
+a int(11) YES NULL
+d blob YES NULL
+e int(11) YES NULL
+f int(11) YES NULL
+select * from t2;
+a d e f
+1 d blob 1 1
+1 d blob 11 1
+1 d blob 2 1
+1 d blob 3 1
+1 d blob 41 1
+select a,b,c,d,e,f from t2;
+ERROR 42S22: Unknown column 'b' in 'field list'
+drop table t2;
+#now this will copy invisible fields
+create table t2 as select a,b,c,d,e,f from t1;
+desc t2;
+Field Type Null Key Default Extra
+a int(11) YES NULL
+b int(11) YES NULL
+c int(11) NO 0
+d blob YES NULL
+e int(11) YES NULL
+f int(11) YES NULL
+select * from t2;
+a b c d e f
+1 NULL 1 d blob 1 1
+1 NULL 2 d blob 11 1
+1 NULL 3 d blob 2 1
+1 NULL 4 d blob 3 1
+1 NULL 5 d blob 41 1
+select a,b,c,d,e,f from t2;
+a b c d e f
+1 NULL 1 d blob 1 1
+1 NULL 2 d blob 11 1
+1 NULL 3 d blob 2 1
+1 NULL 4 d blob 3 1
+1 NULL 5 d blob 41 1
+drop table t2,t1;
+#some test related to copy of data from one table to another;
+create table t1(a int , b int invisible , c int invisible auto_increment unique, d blob , e int unique, f int);
+insert into t1 values(1,'d blob',1,1),(1,'d blob',11,1),(1,'d blob',2,1),(1,'d blob',3,1),(1,'d blob',41,1);
+select a,b,c,d,e,f from t1;
+a b c d e f
+1 NULL 1 d blob 1 1
+1 NULL 2 d blob 11 1
+1 NULL 3 d blob 2 1
+1 NULL 4 d blob 3 1
+1 NULL 5 d blob 41 1
+create table t2(a int , b int invisible , c int invisible , d blob , e int unique, f int);
+insert into t2 select * from t1;
+select a,b,c,d,e,f from t2;
+a b c d e f
+1 NULL NULL d blob 1 1
+1 NULL NULL d blob 11 1
+1 NULL NULL d blob 2 1
+1 NULL NULL d blob 3 1
+1 NULL NULL d blob 41 1
+truncate t2;
+insert into t2 (a,b,c,d,e,f) select a,b,c,d,e,f from t1;
+select a,b,c,d,e,f from t2;
+a b c d e f
+1 NULL 1 d blob 1 1
+1 NULL 2 d blob 11 1
+1 NULL 3 d blob 2 1
+1 NULL 4 d blob 3 1
+1 NULL 5 d blob 41 1
+truncate t2;
+drop table t1,t2;
+#some test related to creating view on table with invisible column;
+create table t1(a int , b int invisible , c int invisible auto_increment unique, d blob , e int unique, f int);
+insert into t1 values(1,'d blob',1,1),(1,'d blob',11,1),(1,'d blob',2,1),(1,'d blob',3,1),(1,'d blob',41,1);
+create view v as select * from t1;
+desc v;
+Field Type Null Key Default Extra
+a int(11) YES NULL
+d blob YES NULL
+e int(11) YES NULL
+f int(11) YES NULL
+select * from v;
+a d e f
+1 d blob 1 1
+1 d blob 11 1
+1 d blob 2 1
+1 d blob 3 1
+1 d blob 41 1
+#v does not have invisible column;
+select a,b,c,d,e,f from v;
+ERROR 42S22: Unknown column 'b' in 'field list'
+insert into v values(1,21,32,4);
+select * from v;
+a d e f
+1 d blob 1 1
+1 d blob 11 1
+1 d blob 2 1
+1 d blob 3 1
+1 d blob 41 1
+1 21 32 4
+insert into v(a,b,c,d,e,f) values(1,12,3,4,5,6);
+ERROR 42S22: Unknown column 'b' in 'field list'
+drop view v;
+create view v as select a,b,c,d,e,f from t1;
+desc v;
+Field Type Null Key Default Extra
+a int(11) YES NULL
+b int(11) YES NULL
+c int(11) NO 0
+d blob YES NULL
+e int(11) YES NULL
+f int(11) YES NULL
+select * from v;
+a b c d e f
+1 NULL 1 d blob 1 1
+1 NULL 2 d blob 11 1
+1 NULL 3 d blob 2 1
+1 NULL 4 d blob 3 1
+1 NULL 5 d blob 41 1
+1 NULL 6 21 32 4
+#v does have invisible column but they aren't invisible anymore.
+select a,b,c,d,e,f from v;
+a b c d e f
+1 NULL 1 d blob 1 1
+1 NULL 2 d blob 11 1
+1 NULL 3 d blob 2 1
+1 NULL 4 d blob 3 1
+1 NULL 5 d blob 41 1
+1 NULL 6 21 32 4
+insert into v values(1,26,33,4,45,66);
+select a,b,c,d,e,f from v;
+a b c d e f
+1 NULL 1 d blob 1 1
+1 NULL 2 d blob 11 1
+1 NULL 3 d blob 2 1
+1 NULL 4 d blob 3 1
+1 NULL 5 d blob 41 1
+1 NULL 6 21 32 4
+1 26 33 4 45 66
+insert into v(a,b,c,d,e,f) values(1,32,31,41,5,6);
+select a,b,c,d,e,f from v;
+a b c d e f
+1 NULL 1 d blob 1 1
+1 NULL 2 d blob 11 1
+1 NULL 3 d blob 2 1
+1 NULL 4 d blob 3 1
+1 NULL 5 d blob 41 1
+1 NULL 6 21 32 4
+1 26 33 4 45 66
+1 32 31 41 5 6
+drop view v;
+drop table t1;
+#now invisible column in where and some join query
+create table t1 (a int unique , b int invisible unique, c int unique invisible);
+insert into t1(a,b,c) values(1,1,1);
+insert into t1(a,b,c) values(2,2,2);
+insert into t1(a,b,c) values(3,3,3);
+insert into t1(a,b,c) values(4,4,4);
+insert into t1(a,b,c) values(21,21,26);
+insert into t1(a,b,c) values(31,31,35);
+insert into t1(a,b,c) values(41,41,45);
+insert into t1(a,b,c) values(22,22,24);
+insert into t1(a,b,c) values(32,32,33);
+insert into t1(a,b,c) values(42,42,43);
+explain select * from t1 where b=3;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 const b b 5 const 1
+select * from t1 where b=3;
+a
+3
+explain select * from t1 where c=3;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 const c c 5 const 1
+select * from t1 where c=3;
+a
+3
+create table t2 as select a,b,c from t1;
+desc t2;
+Field Type Null Key Default Extra
+a int(11) YES NULL
+b int(11) YES NULL
+c int(11) YES NULL
+explain select * from t1,t2 where t1.b = t2.c and t1.c = t2.b;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t2 ALL NULL NULL NULL NULL 10
+1 SIMPLE t1 ALL b,c NULL NULL NULL 10 Using where; Using join buffer (flat, BNL join)
+select * from t1,t2 where t1.b = t2.c and t1.c = t2.b;
+a a b c
+1 1 1 1
+2 2 2 2
+3 3 3 3
+4 4 4 4
+drop table t1,t2;
+#Unhide invisible columns
+create table t1 (a int primary key, b int invisible, c int invisible unique);
+show create table t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `a` int(11) NOT NULL,
+ `b` int(11) INVISIBLE DEFAULT NULL,
+ `c` int(11) INVISIBLE DEFAULT NULL,
+ PRIMARY KEY (`a`),
+ UNIQUE KEY `c` (`c`)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+desc t1;
+Field Type Null Key Default Extra
+a int(11) NO PRI NULL
+b int(11) YES NULL INVISIBLE
+c int(11) YES UNI NULL INVISIBLE
+alter table t1 modify column b int;
+desc t1;
+Field Type Null Key Default Extra
+a int(11) NO PRI NULL
+b int(11) YES NULL
+c int(11) YES UNI NULL INVISIBLE
+alter table t1 change column c d int;
+desc t1;
+Field Type Null Key Default Extra
+a int(11) NO PRI NULL
+b int(11) YES NULL
+d int(11) YES UNI NULL
+drop table t1;
+SHOW STATUS LIKE 'Feature_invisible_columns';
+Variable_name Value
+Feature_invisible_columns 50
+#invisible is non reserved
+create table t1(a int unique , invisible int invisible, c int );
+desc t1;
+Field Type Null Key Default Extra
+a int(11) YES UNI NULL
+invisible int(11) YES NULL INVISIBLE
+c int(11) YES NULL
+alter table t1 change column invisible hid int invisible;
+desc t1;
+Field Type Null Key Default Extra
+a int(11) YES UNI NULL
+hid int(11) YES NULL INVISIBLE
+c int(11) YES NULL
+drop table t1;
+CREATE TABLE t1 (b int);
+INSERT t1 values(1);
+INSERT t1 values(2);
+INSERT t1 values(3);
+INSERT t1 values(4);
+INSERT t1 values(5);
+CREATE TABLE t2 (a int invisible) SELECT * FROM t1;
+select * from t2 order by b;
+b
+1
+2
+3
+4
+5
+select a,b from t2 order by b;
+a b
+NULL 1
+NULL 2
+NULL 3
+NULL 4
+NULL 5
+CREATE TABLE t3 (b int, a int invisible) SELECT * FROM t1;
+select * from t3 order by b;
+b
+1
+2
+3
+4
+5
+select a,b from t3 order by b;
+a b
+NULL 1
+NULL 2
+NULL 3
+NULL 4
+NULL 5
+CREATE TABLE t4 (b int invisible) SELECT * FROM t1;
+ERROR 42000: A table must have at least 1 column
+CREATE TABLE t5 (a int invisible) SELECT b as a FROM t1;
+ERROR 42000: A table must have at least 1 column
+drop table t1,t2,t3;
+create table t1 (a int , b int invisible default 3, c int , d int invisible default 6);
+CREATE PROCEDURE
+insert_t1(a int, b int)
+MODIFIES SQL DATA
+insert into t1 values(a,b);
+//
+call insert_t1(1,1);
+call insert_t1(2,2);
+select * from t1 order by a;
+a c
+1 1
+2 2
+select a,b,c,d from t1 order by a;
+a b c d
+1 3 1 6
+2 3 2 6
+DROP PROCEDURE insert_t1;
+delete from t1;
+prepare insert_1 from "insert into t1 values(@a,@c)";
+prepare insert_2 from "insert into t1(a,b,c) values(@a,@b,@c)";
+set @a=1, @c=1;
+execute insert_1;
+set @a=2,@b=2, @c=2;
+execute insert_2;
+select a,b,c,d from t1 order by a;
+a b c d
+1 3 1 6
+2 2 2 6
+drop table t1;
diff --git a/mysql-test/t/invisible_binlog.test b/mysql-test/t/invisible_binlog.test
new file mode 100644
index 00000000000..654afcc39c3
--- /dev/null
+++ b/mysql-test/t/invisible_binlog.test
@@ -0,0 +1,32 @@
+--source include/master-slave.inc
+
+--connection master
+create table t1(a int , b int invisible);
+insert into t1 values(1);
+insert into t1(a,b) values(2,2);
+select a,b from t1;
+desc t1;
+
+create table t2(a int , b int invisible default 5);
+insert into t2 values(1);
+insert into t2(a,b) values(2,2);
+select a,b from t2;
+desc t2;
+
+
+--sync_slave_with_master
+select * from t1;
+select a,b from t1;
+desc t1;
+show create table t1;
+
+select * from t2;
+select a,b from t2;
+desc t2;
+show create table t2;
+
+
+--connection master
+drop table t1,t2;
+
+--source include/rpl_end.inc
diff --git a/mysql-test/t/invisible_field.test b/mysql-test/t/invisible_field.test
new file mode 100644
index 00000000000..55f3463a310
--- /dev/null
+++ b/mysql-test/t/invisible_field.test
@@ -0,0 +1,216 @@
+FLUSH STATUS;
+create table t1(abc int primary key, xyz int invisible);
+SHOW STATUS LIKE 'Feature_invisible_columns';
+desc t1;
+show create table t1;
+select * from INFORMATION_SCHEMA.COLUMNS where TABLE_SCHEMA='test' and TABLE_NAME='t1';
+drop table t1;
+--error ER_TABLE_MUST_HAVE_COLUMNS
+create table t1(a1 int invisible);
+--error ER_PARSE_ERROR
+create table t1(a1 blob,invisible(a1));
+--error ER_INVISIBLE_NOT_NULL_WITHOUT_DEFAULT
+create table t1(a1 int primary key invisible ,a2 int unique invisible , a3 blob,a4 int not null invisible unique);
+--error ER_INVISIBLE_NOT_NULL_WITHOUT_DEFAULT
+create table t1(abc int not null invisible);
+create table t1(a int invisible, b int);
+#should automatically add null
+insert into t1 values(1);
+insert into t1(a) values(2);
+insert into t1(b) values(3);
+insert into t1(a,b) values(5,5);
+select * from t1;
+select a,b from t1;
+delete from t1;
+insert into t1 values(1),(2),(3),(4);
+select * from t1;
+select a from t1;
+drop table t1;
+
+--echo #more complex case of invisible
+create table t1(a int , b int invisible , c int invisible auto_increment unique, d blob , e int unique, f int);
+desc t1;
+insert into t1 values(1,'d blob',1,1),(1,'d blob',11,1),(1,'d blob',2,1),(1,'d blob',3,1),(1,'d blob',41,1);
+select * from t1;
+select a,b,c,d,e,f from t1;
+drop table t1;
+
+--echo #more complex case of invisible with sql_mode=NO_AUTO_VALUE_ON_ZERO
+set sql_mode='NO_AUTO_VALUE_ON_ZERO';
+create table t1(a int , b int invisible , c int invisible auto_increment unique, d blob , e int unique, f int);
+desc t1;
+insert into t1 values(1,'d blob',1,1),(1,'d blob',11,1),(1,'d blob',2,1),(1,'d blob',3,1),(1,'d blob',41,1);
+select * from t1;
+select a,b,c,d,e,f from t1;
+drop table t1;
+set sql_mode='';
+
+--error ER_PARSE_ERROR
+create table sdsdsd(a int , b int, invisible(a,b));
+create table t1(a int,abc int as (a mod 3) virtual invisible);
+desc t1;
+--error ER_WRONG_VALUE_COUNT_ON_ROW
+insert into t1 values(1,default);
+insert into t1 values(1),(22),(233);
+select * from t1;
+select a,abc from t1;
+drop table t1;
+create table t1(abc int primary key invisible auto_increment, a int);
+desc t1;
+show create table t1;
+insert into t1 values(1);
+insert into t1 values(2);
+insert into t1 values(3);
+select * from t1;
+select abc,a from t1;
+delete from t1;
+insert into t1 values(1),(2),(3),(4),(6);
+select abc,a from t1;
+drop table t1;
+create table t1(abc int);
+--error ER_TABLE_MUST_HAVE_COLUMNS
+alter table t1 change abc ss int invisible;
+alter table t1 add column xyz int;
+alter table t1 modify column abc int ;
+desc t1;
+--error ER_WRONG_VALUE_COUNT_ON_ROW
+insert into t1 values(22);
+alter table t1 modify column abc int invisible;
+desc t1;
+insert into t1 values(12);
+drop table t1;
+--echo #some test on copy table structure with table data;
+--echo #table with invisible fields and unique keys;
+create table t1(a int , b int invisible , c int invisible auto_increment unique, d blob , e int unique, f int);
+desc t1;
+insert into t1 values(1,'d blob',1,1),(1,'d blob',11,1),(1,'d blob',2,1),(1,'d blob',3,1),(1,'d blob',41,1);
+select * from t1;
+select a,b,c,d,e,f from t1;
+--echo #this won't copy invisible fields and keys;
+create table t2 as select * from t1;
+desc t2;
+select * from t2;
+--error ER_BAD_FIELD_ERROR
+select a,b,c,d,e,f from t2;
+drop table t2;
+--echo #now this will copy invisible fields
+create table t2 as select a,b,c,d,e,f from t1;
+desc t2;
+select * from t2;
+select a,b,c,d,e,f from t2;
+drop table t2,t1;
+--echo #some test related to copy of data from one table to another;
+create table t1(a int , b int invisible , c int invisible auto_increment unique, d blob , e int unique, f int);
+insert into t1 values(1,'d blob',1,1),(1,'d blob',11,1),(1,'d blob',2,1),(1,'d blob',3,1),(1,'d blob',41,1);
+select a,b,c,d,e,f from t1;
+create table t2(a int , b int invisible , c int invisible , d blob , e int unique, f int);
+insert into t2 select * from t1;
+select a,b,c,d,e,f from t2;
+truncate t2;
+insert into t2 (a,b,c,d,e,f) select a,b,c,d,e,f from t1;
+select a,b,c,d,e,f from t2;
+truncate t2;
+drop table t1,t2;
+--echo #some test related to creating view on table with invisible column;
+create table t1(a int , b int invisible , c int invisible auto_increment unique, d blob , e int unique, f int);
+insert into t1 values(1,'d blob',1,1),(1,'d blob',11,1),(1,'d blob',2,1),(1,'d blob',3,1),(1,'d blob',41,1);
+create view v as select * from t1;
+desc v;
+select * from v;
+--echo #v does not have invisible column;
+--error ER_BAD_FIELD_ERROR
+select a,b,c,d,e,f from v;
+insert into v values(1,21,32,4);
+select * from v;
+--error ER_BAD_FIELD_ERROR
+insert into v(a,b,c,d,e,f) values(1,12,3,4,5,6);
+drop view v;
+create view v as select a,b,c,d,e,f from t1;
+desc v;
+select * from v;
+--echo #v does have invisible column but they aren't invisible anymore.
+select a,b,c,d,e,f from v;
+insert into v values(1,26,33,4,45,66);
+select a,b,c,d,e,f from v;
+insert into v(a,b,c,d,e,f) values(1,32,31,41,5,6);
+select a,b,c,d,e,f from v;
+drop view v;
+drop table t1;
+--echo #now invisible column in where and some join query
+create table t1 (a int unique , b int invisible unique, c int unique invisible);
+insert into t1(a,b,c) values(1,1,1);
+insert into t1(a,b,c) values(2,2,2);
+insert into t1(a,b,c) values(3,3,3);
+insert into t1(a,b,c) values(4,4,4);
+insert into t1(a,b,c) values(21,21,26);
+insert into t1(a,b,c) values(31,31,35);
+insert into t1(a,b,c) values(41,41,45);
+insert into t1(a,b,c) values(22,22,24);
+insert into t1(a,b,c) values(32,32,33);
+insert into t1(a,b,c) values(42,42,43);
+explain select * from t1 where b=3;
+select * from t1 where b=3;
+explain select * from t1 where c=3;
+select * from t1 where c=3;
+create table t2 as select a,b,c from t1;
+desc t2;
+explain select * from t1,t2 where t1.b = t2.c and t1.c = t2.b;
+select * from t1,t2 where t1.b = t2.c and t1.c = t2.b;
+drop table t1,t2;
+--echo #Unhide invisible columns
+create table t1 (a int primary key, b int invisible, c int invisible unique);
+show create table t1;
+desc t1;
+alter table t1 modify column b int;
+desc t1;
+alter table t1 change column c d int;
+desc t1;
+drop table t1;
+SHOW STATUS LIKE 'Feature_invisible_columns';
+--echo #invisible is non reserved
+create table t1(a int unique , invisible int invisible, c int );
+desc t1;
+alter table t1 change column invisible hid int invisible;
+desc t1;
+drop table t1;
+##Internal temp table
+CREATE TABLE t1 (b int);
+INSERT t1 values(1);
+INSERT t1 values(2);
+INSERT t1 values(3);
+INSERT t1 values(4);
+INSERT t1 values(5);
+CREATE TABLE t2 (a int invisible) SELECT * FROM t1;
+select * from t2 order by b;
+select a,b from t2 order by b;
+CREATE TABLE t3 (b int, a int invisible) SELECT * FROM t1;
+select * from t3 order by b;
+select a,b from t3 order by b;
+--error ER_TABLE_MUST_HAVE_COLUMNS
+CREATE TABLE t4 (b int invisible) SELECT * FROM t1;
+--error ER_TABLE_MUST_HAVE_COLUMNS
+CREATE TABLE t5 (a int invisible) SELECT b as a FROM t1;
+drop table t1,t2,t3;
+
+create table t1 (a int , b int invisible default 3, c int , d int invisible default 6);
+DELIMITER //;
+CREATE PROCEDURE
+insert_t1(a int, b int)
+MODIFIES SQL DATA
+insert into t1 values(a,b);
+//
+DELIMITER ;//
+call insert_t1(1,1);
+call insert_t1(2,2);
+select * from t1 order by a;
+select a,b,c,d from t1 order by a;
+DROP PROCEDURE insert_t1;
+delete from t1;
+prepare insert_1 from "insert into t1 values(@a,@c)";
+prepare insert_2 from "insert into t1(a,b,c) values(@a,@b,@c)";
+set @a=1, @c=1;
+execute insert_1;
+set @a=2,@b=2, @c=2;
+execute insert_2;
+select a,b,c,d from t1 order by a;
+drop table t1; \ No newline at end of file
diff --git a/sql/field.cc b/sql/field.cc
index 7d8f8a9af20..5ad1981b752 100644
--- a/sql/field.cc
+++ b/sql/field.cc
@@ -1603,7 +1603,8 @@ String *Field::val_int_as_str(String *val_buffer, bool unsigned_val)
Field::Field(uchar *ptr_arg,uint32 length_arg,uchar *null_ptr_arg,
uchar null_bit_arg,
utype unireg_check_arg, const LEX_CSTRING *field_name_arg)
- :ptr(ptr_arg), null_ptr(null_ptr_arg), table(0), orig_table(0),
+ :ptr(ptr_arg), field_visibility(NOT_INVISIBLE),
+ null_ptr(null_ptr_arg), table(0), orig_table(0),
table_name(0), field_name(*field_name_arg), option_list(0),
option_struct(0), key_start(0), part_of_key(0),
part_of_key_not_clustered(0), part_of_sortkey(0),
@@ -2195,6 +2196,7 @@ Field *Field::make_new_field(MEM_ROOT *root, TABLE *new_table,
tmp->flags&= (NOT_NULL_FLAG | BLOB_FLAG | UNSIGNED_FLAG |
ZEROFILL_FLAG | BINARY_FLAG | ENUM_FLAG | SET_FLAG);
tmp->reset_fields();
+ tmp->field_visibility= NOT_INVISIBLE;
return tmp;
}
@@ -10645,6 +10647,7 @@ Column_definition::Column_definition(THD *thd, Field *old_field,
option_list= old_field->option_list;
pack_flag= 0;
compression_method_ptr= 0;
+ field_visibility= old_field->field_visibility;
if (orig_field)
{
@@ -10782,6 +10785,7 @@ Column_definition::redefine_stage1_common(const Column_definition *dup_field,
flags= dup_field->flags;
interval= dup_field->interval;
vcol_info= dup_field->vcol_info;
+ field_visibility= dup_field->field_visibility;
}
diff --git a/sql/field.h b/sql/field.h
index 624921b628e..7338a8ff679 100644
--- a/sql/field.h
+++ b/sql/field.h
@@ -674,6 +674,8 @@ public:
{ DBUG_ASSERT(0); }
uchar *ptr; // Position to field in record
+
+ field_visible_type field_visibility;
/**
Byte where the @c NULL bit is stored inside a record. If this Field is a
@c NOT @c NULL field, this member is @c NULL.
@@ -4046,6 +4048,7 @@ public:
max number of characters.
*/
ulonglong length;
+ field_visible_type field_visibility;
/*
The value of `length' as set by parser: is the number of characters
for most of the types, or of bytes for BLOBs or numeric types.
@@ -4076,7 +4079,7 @@ public:
:Type_handler_hybrid_field_type(&type_handler_null),
compression_method_ptr(0),
comment(null_clex_str),
- on_update(NULL), length(0), decimals(0),
+ on_update(NULL), length(0),field_visibility(NOT_INVISIBLE), decimals(0),
flags(0), pack_length(0), key_length(0), unireg_check(Field::NONE),
interval(0), charset(&my_charset_bin),
srid(0), geom_type(Field::GEOM_GEOMETRY),
@@ -4553,5 +4556,6 @@ bool check_expression(Virtual_column_info *vcol, LEX_CSTRING *name,
#define f_no_default(x) ((x) & FIELDFLAG_NO_DEFAULT)
#define f_bit_as_char(x) ((x) & FIELDFLAG_TREAT_BIT_AS_CHAR)
#define f_is_hex_escape(x) ((x) & FIELDFLAG_HEX_ESCAPE)
+#define f_visibility(x) (static_cast<field_visible_type> ((x) & 3))
#endif /* FIELD_INCLUDED */
diff --git a/sql/lex.h b/sql/lex.h
index a0068069660..da985ad26a6 100644
--- a/sql/lex.h
+++ b/sql/lex.h
@@ -273,6 +273,7 @@ static SYMBOL symbols[] = {
{ "HAVING", SYM(HAVING)},
{ "HELP", SYM(HELP_SYM)},
{ "HIGH_PRIORITY", SYM(HIGH_PRIORITY)},
+ { "INVISIBLE", SYM(INVISIBLE_SYM)},
{ "HOST", SYM(HOST_SYM)},
{ "HOSTS", SYM(HOSTS_SYM)},
{ "HOUR", SYM(HOUR_SYM)},
diff --git a/sql/mysqld.cc b/sql/mysqld.cc
index 1f679ee6e9e..f3cb39959a7 100644
--- a/sql/mysqld.cc
+++ b/sql/mysqld.cc
@@ -8490,6 +8490,7 @@ SHOW_VAR status_vars[]= {
{"Feature_dynamic_columns", (char*) offsetof(STATUS_VAR, feature_dynamic_columns), SHOW_LONG_STATUS},
{"Feature_fulltext", (char*) offsetof(STATUS_VAR, feature_fulltext), SHOW_LONG_STATUS},
{"Feature_gis", (char*) offsetof(STATUS_VAR, feature_gis), SHOW_LONG_STATUS},
+ {"Feature_invisible_columns", (char*) offsetof(STATUS_VAR, feature_invisible_columns), SHOW_LONG_STATUS},
{"Feature_locale", (char*) offsetof(STATUS_VAR, feature_locale), SHOW_LONG_STATUS},
{"Feature_subquery", (char*) offsetof(STATUS_VAR, feature_subquery), SHOW_LONG_STATUS},
{"Feature_timezone", (char*) offsetof(STATUS_VAR, feature_timezone), SHOW_LONG_STATUS},
diff --git a/sql/share/errmsg-utf8.txt b/sql/share/errmsg-utf8.txt
index 1b7696bc056..949e3bd9d17 100644
--- a/sql/share/errmsg-utf8.txt
+++ b/sql/share/errmsg-utf8.txt
@@ -7810,3 +7810,5 @@ ER_INVALID_AGGREGATE_FUNCTION
eng "Aggregate specific instruction(FETCH GROUP NEXT ROW) missing from the aggregate function"
ER_INVALID_VALUE_TO_LIMIT
eng "Limit only accepts integer values"
+ER_INVISIBLE_NOT_NULL_WITHOUT_DEFAULT
+ eng "Invisible column %`s must have a default value"
diff --git a/sql/sql_base.cc b/sql/sql_base.cc
index a08078c5cff..ef8d4afdd3c 100644
--- a/sql/sql_base.cc
+++ b/sql/sql_base.cc
@@ -5528,7 +5528,10 @@ find_field_in_table(THD *thd, TABLE *table, const char *name, uint length,
if (field_ptr && *field_ptr)
{
- *cached_field_index_ptr= (uint)(field_ptr - table->field);
+ if ((*field_ptr)->field_visibility == COMPLETELY_INVISIBLE)
+ DBUG_RETURN((Field*)0);
+
+ *cached_field_index_ptr= field_ptr - table->field;
field= *field_ptr;
}
else
@@ -7607,6 +7610,14 @@ insert_fields(THD *thd, Name_resolution_context *context, const char *db_name,
for (; !field_iterator.end_of_fields(); field_iterator.next())
{
+ /*
+ field() is always NULL for views (see, e.g. Field_iterator_view or
+ Field_iterator_natural_join).
+ But view fields can never be invisible.
+ */
+ if ((field= field_iterator.field()) &&
+ field->field_visibility != NOT_INVISIBLE)
+ continue;
Item *item;
if (!(item= field_iterator.create_item(thd)))
@@ -8213,7 +8224,6 @@ fill_record(THD *thd, TABLE *table, Field **ptr, List<Item> &values,
? table->next_number_field->field_index
: ~0U;
DBUG_ENTER("fill_record");
-
if (!*ptr)
{
/* No fields to update, quite strange!*/
@@ -8236,7 +8246,10 @@ fill_record(THD *thd, TABLE *table, Field **ptr, List<Item> &values,
/* Ensure that all fields are from the same table */
DBUG_ASSERT(field->table == table);
- value=v++;
+ if (field->field_visibility != NOT_INVISIBLE)
+ continue;
+ else
+ value=v++;
if (field->field_index == autoinc_index)
table->auto_increment_field_not_null= TRUE;
if (field->vcol_info)
diff --git a/sql/sql_class.h b/sql/sql_class.h
index 46dea27dc7a..4249bc6bb5b 100644
--- a/sql/sql_class.h
+++ b/sql/sql_class.h
@@ -304,22 +304,25 @@ public:
LEX_CSTRING name;
engine_option_value *option_list;
bool generated;
+ bool invisible;
Key(enum Keytype type_par, const LEX_CSTRING *name_arg,
ha_key_alg algorithm_arg, bool generated_arg, DDL_options_st ddl_options)
:DDL_options(ddl_options),
type(type_par), key_create_info(default_key_create_info),
- name(*name_arg), option_list(NULL), generated(generated_arg)
+ name(*name_arg), option_list(NULL), generated(generated_arg),
+ invisible(false)
{
key_create_info.algorithm= algorithm_arg;
- }
+ }
Key(enum Keytype type_par, const LEX_CSTRING *name_arg,
KEY_CREATE_INFO *key_info_arg,
bool generated_arg, List<Key_part_spec> *cols,
engine_option_value *create_opt, DDL_options_st ddl_options)
:DDL_options(ddl_options),
type(type_par), key_create_info(*key_info_arg), columns(*cols),
- name(*name_arg), option_list(create_opt), generated(generated_arg)
+ name(*name_arg), option_list(create_opt), generated(generated_arg),
+ invisible(false)
{}
Key(const Key &rhs, MEM_ROOT *mem_root);
virtual ~Key() {}
@@ -791,6 +794,7 @@ typedef struct system_status_var
ulong feature_dynamic_columns; /* +1 when creating a dynamic column */
ulong feature_fulltext; /* +1 when MATCH is used */
ulong feature_gis; /* +1 opening a table with GIS features */
+ ulong feature_invisible_columns; /* +1 opening a table with invisible column */
ulong feature_locale; /* +1 when LOCALE is set */
ulong feature_subquery; /* +1 when subqueries are used */
ulong feature_timezone; /* +1 when XPATH is used */
diff --git a/sql/sql_insert.cc b/sql/sql_insert.cc
index 51c6c80fd66..0cf121fdd59 100644
--- a/sql/sql_insert.cc
+++ b/sql/sql_insert.cc
@@ -222,7 +222,7 @@ static int check_insert_fields(THD *thd, TABLE_LIST *table_list,
table_list->view_db.str, table_list->view_name.str);
DBUG_RETURN(-1);
}
- if (values.elements != table->s->fields)
+ if (values.elements != table->s->visible_fields)
{
my_error(ER_WRONG_VALUE_COUNT_ON_ROW, MYF(0), 1L);
DBUG_RETURN(-1);
@@ -980,7 +980,8 @@ bool mysql_insert(THD *thd,TABLE_LIST *table_list,
No field list, all fields are set explicitly:
INSERT INTO t1 VALUES (values)
*/
- if (thd->lex->used_tables) // Column used in values()
+ if (thd->lex->used_tables || // Column used in values()
+ table->s->visible_fields != table->s->fields)
restore_record(table,s->default_values); // Get empty record
else
{
diff --git a/sql/sql_show.cc b/sql/sql_show.cc
index 8ac472bd626..d0a0293bf1b 100644
--- a/sql/sql_show.cc
+++ b/sql/sql_show.cc
@@ -2162,13 +2162,18 @@ int show_create_table(THD *thd, TABLE_LIST *table_list, String *packet,
*/
old_map= tmp_use_all_columns(table, table->read_set);
+ bool not_the_first_field= false;
for (ptr=table->field ; (field= *ptr); ptr++)
{
+
uint flags = field->flags;
- if (ptr != table->field)
+ if (field->field_visibility > USER_DEFINED_INVISIBLE)
+ continue;
+ if (not_the_first_field)
packet->append(STRING_WITH_LEN(",\n"));
+ not_the_first_field= true;
packet->append(STRING_WITH_LEN(" "));
append_identifier(thd,packet,field->field_name.str,
field->field_name.length);
@@ -2221,6 +2226,10 @@ int show_create_table(THD *thd, TABLE_LIST *table_list, String *packet,
packet->append(STRING_WITH_LEN(" NULL"));
}
+ if (field->field_visibility == USER_DEFINED_INVISIBLE)
+ {
+ packet->append(STRING_WITH_LEN(" INVISIBLE"));
+ }
def_value.set(def_value_buf, sizeof(def_value_buf), system_charset_info);
if (get_field_default_value(thd, field, &def_value, 1))
{
@@ -2262,6 +2271,8 @@ int show_create_table(THD *thd, TABLE_LIST *table_list, String *packet,
for (uint i=0 ; i < share->keys ; i++,key_info++)
{
+ if (key_info->flags & HA_INVISIBLE_KEY)
+ continue;
KEY_PART_INFO *key_part= key_info->key_part;
bool found_primary=0;
packet->append(STRING_WITH_LEN(",\n "));
@@ -5711,6 +5722,8 @@ static int get_schema_column_record(THD *thd, TABLE_LIST *tables,
for (; (field= *ptr) ; ptr++)
{
+ if(field->field_visibility > USER_DEFINED_INVISIBLE)
+ continue;
uchar *pos;
char tmp[MAX_FIELD_WIDTH];
String type(tmp,sizeof(tmp), system_charset_info);
@@ -5768,11 +5781,11 @@ static int get_schema_column_record(THD *thd, TABLE_LIST *tables,
table->field[16]->store((const char*) pos,
strlen((const char*) pos), cs);
+ StringBuffer<256> buf;
if (field->unireg_check == Field::NEXT_NUMBER)
- table->field[17]->store(STRING_WITH_LEN("auto_increment"), cs);
+ buf.set(STRING_WITH_LEN("auto_increment"),cs);
if (print_on_update_clause(field, &type, true))
- table->field[17]->store(type.ptr(), type.length(), cs);
-
+ buf.set(type.ptr(), type.length(),cs);
if (field->vcol_info)
{
String gen_s(tmp,sizeof(tmp), system_charset_info);
@@ -5783,13 +5796,20 @@ static int get_schema_column_record(THD *thd, TABLE_LIST *tables,
table->field[20]->store(STRING_WITH_LEN("ALWAYS"), cs);
if (field->vcol_info->stored_in_db)
- table->field[17]->store(STRING_WITH_LEN("STORED GENERATED"), cs);
+ buf.set(STRING_WITH_LEN("STORED GENERATED"), cs);
else
- table->field[17]->store(STRING_WITH_LEN("VIRTUAL GENERATED"), cs);
+ buf.set(STRING_WITH_LEN("VIRTUAL GENERATED"), cs);
}
else
table->field[20]->store(STRING_WITH_LEN("NEVER"), cs);
-
+ /*Invisible can coexist with auto_increment and virtual */
+ if (field->field_visibility == USER_DEFINED_INVISIBLE)
+ {
+ if (buf.length())
+ buf.append(STRING_WITH_LEN(", "));
+ buf.append(STRING_WITH_LEN("INVISIBLE"),cs);
+ }
+ table->field[17]->store(buf.ptr(), buf.length(), cs);
table->field[19]->store(field->comment.str, field->comment.length, cs);
if (schema_table_store_record(thd, table))
DBUG_RETURN(1);
@@ -6369,6 +6389,8 @@ static int get_schema_stat_record(THD *thd, TABLE_LIST *tables,
}
for (uint i=0 ; i < show_table->s->keys ; i++,key_info++)
{
+ if ((key_info->flags & HA_INVISIBLE_KEY))
+ continue;
KEY_PART_INFO *key_part= key_info->key_part;
LEX_CSTRING *str;
LEX_CSTRING unknown= {STRING_WITH_LEN("?unknown field?") };
diff --git a/sql/sql_table.cc b/sql/sql_table.cc
index dbf198d722a..44774d5fcff 100644
--- a/sql/sql_table.cc
+++ b/sql/sql_table.cc
@@ -62,12 +62,16 @@
const char *primary_key_name="PRIMARY";
-static bool check_if_keyname_exists(const char *name,KEY *start, KEY *end);
+static int check_if_keyname_exists(const char *name,KEY *start, KEY *end);
static char *make_unique_key_name(THD *thd, const char *field_name, KEY *start,
KEY *end);
static void make_unique_constraint_name(THD *thd, LEX_CSTRING *name,
List<Virtual_column_info> *vcol,
uint *nr);
+static const
+char * make_unique_invisible_field_name(THD *thd, const char *field_name,
+ List<Create_field> *fields);
+
static int copy_data_between_tables(THD *thd, TABLE *from,TABLE *to,
List<Create_field> &create, bool ignore,
uint order_num, ORDER *order,
@@ -3263,8 +3267,70 @@ bool Column_definition::prepare_stage1_check_typelib_default()
}
return false;
}
+/*
+ This function adds a invisible field to field_list
+ SYNOPSIS
+ mysql_add_invisible_field()
+ thd Thread Object
+ field_list list of all table fields
+ field_name name/prefix of invisible field
+ ( Prefix in the case when it is
+ *COMPLETELY_INVISIBLE*
+ and given name is duplicate)
+ type_handler field data type
+ field_visibility
+ default value
+ RETURN VALUE
+ Create_field pointer
+*/
+int mysql_add_invisible_field(THD *thd, List<Create_field> * field_list,
+ const char *field_name, Type_handler *type_handler,
+ field_visible_type field_visibility, Item* default_value)
+{
+ Create_field *fld= new(thd->mem_root)Create_field();
+ const char *new_name= NULL;
+ /* Get unique field name if field_visibility == COMPLETELY_INVISIBLE */
+ if (field_visibility == COMPLETELY_INVISIBLE)
+ {
+ if ((new_name= make_unique_invisible_field_name(thd, field_name,
+ field_list)))
+ {
+ fld->field_name.str= new_name;
+ fld->field_name.length= strlen(new_name);
+ }
+ else
+ return 1; //Should not happen
+ }
+ else
+ {
+ fld->field_name.str= thd->strmake(field_name, strlen(field_name));
+ fld->field_name.length= strlen(field_name);
+ }
+ fld->set_handler(type_handler);
+ fld->field_visibility= field_visibility;
+ if (default_value)
+ {
+ Virtual_column_info *v= new (thd->mem_root) Virtual_column_info();
+ v->expr= default_value;
+ v->utf8= 0;
+ fld->default_value= v;
+ }
+ field_list->push_front(fld, thd->mem_root);
+ return 0;
+}
-
+Key *
+mysql_add_invisible_index(THD *thd, List<Key> *key_list,
+ LEX_CSTRING* field_name, enum Key::Keytype type)
+{
+ Key *key= NULL;
+ key= new (thd->mem_root) Key(type, &null_clex_str, HA_KEY_ALG_UNDEF,
+ false, DDL_options(DDL_options::OPT_NONE));
+ key->columns.push_back(new(thd->mem_root) Key_part_spec(field_name, 0),
+ thd->mem_root);
+ key_list->push_back(key, thd->mem_root);
+ return key;
+}
/*
Preparation for table creation
@@ -3440,7 +3506,6 @@ mysql_prepare_create_table(THD *thd, HA_CREATE_INFO *create_info,
while ((sql_field=it++))
{
DBUG_ASSERT(sql_field->charset != 0);
-
if (sql_field->prepare_stage2(file, file->ha_table_flags()))
DBUG_RETURN(TRUE);
if (sql_field->real_field_type() == MYSQL_TYPE_VARCHAR)
@@ -3460,8 +3525,18 @@ mysql_prepare_create_table(THD *thd, HA_CREATE_INFO *create_info,
*/
if (sql_field->stored_in_db())
record_offset+= sql_field->pack_length;
+ if (sql_field->field_visibility == USER_DEFINED_INVISIBLE &&
+ sql_field->flags & NOT_NULL_FLAG &&
+ sql_field->flags & NO_DEFAULT_VALUE_FLAG)
+ {
+ my_error(ER_INVISIBLE_NOT_NULL_WITHOUT_DEFAULT, MYF(0),
+ sql_field->field_name.str);
+ DBUG_RETURN(TRUE);
+ }
}
- /* Update virtual fields' offset*/
+ /* Update virtual fields' offset and give error if
+ All fields are invisible */
+ bool is_all_invisible= true;
it.rewind();
while ((sql_field=it++))
{
@@ -3470,6 +3545,13 @@ mysql_prepare_create_table(THD *thd, HA_CREATE_INFO *create_info,
sql_field->offset= record_offset;
record_offset+= sql_field->pack_length;
}
+ if (sql_field->field_visibility == NOT_INVISIBLE)
+ is_all_invisible= false;
+ }
+ if (is_all_invisible)
+ {
+ my_error(ER_TABLE_MUST_HAVE_COLUMNS, MYF(0));
+ DBUG_RETURN(TRUE);
}
if (auto_increment > 1)
{
@@ -3720,11 +3802,21 @@ mysql_prepare_create_table(THD *thd, HA_CREATE_INFO *create_info,
&column->field_name,
&sql_field->field_name))
field++;
+ /*
+ Either field is not present or field visibility is >
+ USER_DEFINED_INVISIBLE
+ */
if (!sql_field)
{
my_error(ER_KEY_COLUMN_DOES_NOT_EXITS, MYF(0), column->field_name.str);
DBUG_RETURN(TRUE);
}
+ if (sql_field->field_visibility > USER_DEFINED_INVISIBLE &&
+ !key->invisible)
+ {
+ my_error(ER_KEY_COLUMN_DOES_NOT_EXITS, MYF(0), column->field_name.str);
+ DBUG_RETURN(TRUE);
+ }
while ((dup_column= cols2++) != column)
{
if (!lex_string_cmp(system_charset_info,
@@ -5034,17 +5126,36 @@ err:
/*
** Give the key name after the first field with an optional '_#' after
+ @returns
+ 0 if keyname does not exists
+ [1..) index + 1 of duplicate key name
**/
-static bool
+static int
check_if_keyname_exists(const char *name, KEY *start, KEY *end)
{
- for (KEY *key=start ; key != end ; key++)
+ uint i= 1;
+ for (KEY *key=start; key != end ; key++, i++)
if (!my_strcasecmp(system_charset_info, name, key->name.str))
- return 1;
+ return i;
return 0;
}
+/**
+ Returns 1 if field name exists otherwise 0
+*/
+static bool
+check_if_field_name_exists(const char *name, List<Create_field> * fields)
+{
+ Create_field *fld;
+ List_iterator<Create_field>it(*fields);
+ while ((fld = it++))
+ {
+ if (!my_strcasecmp(system_charset_info, fld->field_name.str, name))
+ return 1;
+ }
+ return 0;
+}
static char *
make_unique_key_name(THD *thd, const char *field_name,KEY *start,KEY *end)
@@ -5102,6 +5213,33 @@ static void make_unique_constraint_name(THD *thd, LEX_CSTRING *name,
}
}
+/**
+ COMPLETELY_INVISIBLE are internally created. They are completely invisible
+ to Alter command (Opposite of SYSTEM_INVISIBLE which throws an
+ error when same name column is added by Alter). So in the case of when
+ user added a same column name as of COMPLETELY_INVISIBLE , we change
+ COMPLETELY_INVISIBLE column name.
+*/
+static const
+char * make_unique_invisible_field_name(THD *thd, const char *field_name,
+ List<Create_field> *fields)
+{
+ if (!check_if_field_name_exists(field_name, fields))
+ return field_name;
+ char buff[MAX_FIELD_NAME], *buff_end;
+ buff_end= strmake_buf(buff, field_name);
+ if (buff_end - buff < 5)
+ return NULL; // Should not happen
+
+ for (uint i=1 ; i < 10000; i++)
+ {
+ char *real_end= int10_to_str(i, buff_end, 10);
+ if (check_if_field_name_exists(buff, fields))
+ continue;
+ return (const char *)thd->strmake(buff, real_end - buff);
+ }
+ return NULL; //Should not happen
+}
/****************************************************************************
** Alter a table definition
@@ -7530,6 +7668,8 @@ mysql_prepare_alter_table(THD *thd, TABLE *table,
bitmap_clear_all(&table->tmp_set);
for (f_ptr=table->field ; (field= *f_ptr) ; f_ptr++)
{
+ if (field->field_visibility == COMPLETELY_INVISIBLE)
+ continue;
Alter_drop *drop;
if (field->type() == MYSQL_TYPE_VARCHAR)
create_info->varchar= TRUE;
@@ -7541,7 +7681,7 @@ mysql_prepare_alter_table(THD *thd, TABLE *table,
!my_strcasecmp(system_charset_info,field->field_name.str, drop->name))
break;
}
- if (drop)
+ if (drop && field->field_visibility < SYSTEM_INVISIBLE)
{
/* Reset auto_increment value if it was dropped */
if (MTYP_TYPENR(field->unireg_check) == Field::NEXT_NUMBER &&
@@ -7566,7 +7706,7 @@ mysql_prepare_alter_table(THD *thd, TABLE *table,
&def->change))
break;
}
- if (def)
+ if (def && field->field_visibility < SYSTEM_INVISIBLE)
{ // Field is changed
def->field=field;
/*
@@ -7621,12 +7761,12 @@ mysql_prepare_alter_table(THD *thd, TABLE *table,
def_it.rewind();
while ((def=def_it++)) // Add new columns
{
+ Create_field *find;
if (def->change.str && ! def->field)
{
/*
Check if there is modify for newly added field.
*/
- Create_field *find;
find_it.rewind();
while((find=find_it++))
{
@@ -7666,7 +7806,6 @@ mysql_prepare_alter_table(THD *thd, TABLE *table,
new_create_list.push_back(def, thd->mem_root);
else
{
- Create_field *find;
if (def->change.str)
{
find_it.rewind();
@@ -7753,9 +7892,11 @@ mysql_prepare_alter_table(THD *thd, TABLE *table,
Collect all keys which isn't in drop list. Add only those
for which some fields exists.
*/
-
+
for (uint i=0 ; i < table->s->keys ; i++,key_info++)
{
+ if (key_info->flags & HA_INVISIBLE_KEY)
+ continue;
const char *key_name= key_info->name.str;
Alter_drop *drop;
drop_it.rewind();
diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy
index b2227bdbbbf..2e51cb6da11 100644
--- a/sql/sql_yacc.yy
+++ b/sql/sql_yacc.yy
@@ -1129,6 +1129,7 @@ bool my_yyoverflow(short **a, YYSTYPE **b, ulong *yystacksize);
%token HEX_NUM
%token HEX_STRING
%token HIGH_PRIORITY
+%token INVISIBLE_SYM
%token HOST_SYM
%token HOSTS_SYM
%token HOUR_MICROSECOND_SYM
@@ -6428,6 +6429,10 @@ vcol_attribute:
lex->alter_info.flags|= Alter_info::ALTER_ADD_INDEX;
}
| COMMENT_SYM TEXT_STRING_sys { Lex->last_field->comment= $2; }
+ | INVISIBLE_SYM
+ {
+ Lex->last_field->field_visibility= USER_DEFINED_INVISIBLE;
+ }
;
parse_vcol_expr:
@@ -6812,7 +6817,7 @@ attribute:
}
| AUTO_INC { Lex->last_field->flags|= AUTO_INCREMENT_FLAG | NOT_NULL_FLAG; }
| SERIAL_SYM DEFAULT VALUE_SYM
- {
+ {
LEX *lex=Lex;
lex->last_field->flags|= AUTO_INCREMENT_FLAG | NOT_NULL_FLAG | UNIQUE_KEY_FLAG;
lex->alter_info.flags|= Alter_info::ALTER_ADD_INDEX;
@@ -15166,6 +15171,7 @@ keyword_sp_not_data_type:
| GOTO_SYM {}
| HASH_SYM {}
| HARD_SYM {}
+ | INVISIBLE_SYM {}
| HOSTS_SYM {}
| HOUR_SYM {}
| ID_SYM {}
diff --git a/sql/sql_yacc_ora.yy b/sql/sql_yacc_ora.yy
index 54c27783b0a..1de303baf08 100644
--- a/sql/sql_yacc_ora.yy
+++ b/sql/sql_yacc_ora.yy
@@ -537,6 +537,7 @@ bool my_yyoverflow(short **a, YYSTYPE **b, ulong *yystacksize);
%token HEX_NUM
%token HEX_STRING
%token HIGH_PRIORITY
+%token INVISIBLE_SYM
%token HOST_SYM
%token HOSTS_SYM
%token HOUR_MICROSECOND_SYM
@@ -6125,6 +6126,10 @@ vcol_attribute:
lex->alter_info.flags|= Alter_info::ALTER_ADD_INDEX;
}
| COMMENT_SYM TEXT_STRING_sys { Lex->last_field->comment= $2; }
+ | INVISIBLE_SYM
+ {
+ Lex->last_field->field_visibility= USER_DEFINED_INVISIBLE;
+ }
;
parse_vcol_expr:
@@ -15046,6 +15051,7 @@ keyword_sp_not_data_type:
| GLOBAL_SYM {}
| HASH_SYM {}
| HARD_SYM {}
+ | INVISIBLE_SYM {}
| HOSTS_SYM {}
| HOUR_SYM {}
| ID_SYM {}
diff --git a/sql/table.cc b/sql/table.cc
index e8343903d96..f65d7fa123d 100644
--- a/sql/table.cc
+++ b/sql/table.cc
@@ -43,6 +43,7 @@
#include "rpl_filter.h"
#include "sql_cte.h"
#include "ha_sequence.h"
+#include "sql_show.h"
/* For MySQL 5.7 virtual fields */
#define MYSQL57_GENERATED_FIELD 128
@@ -1161,13 +1162,15 @@ int TABLE_SHARE::init_from_binary_frm_image(THD *thd, bool write,
uint db_create_options, keys, key_parts, n_length;
uint com_length, null_bit_pos, mysql57_vcol_null_bit_pos, bitmap_count;
uint i;
+ uint field_additional_property_length= 0;
bool use_hash, mysql57_null_bits= 0;
char *keynames, *names, *comment_pos;
const uchar *forminfo, *extra2;
const uchar *frm_image_end = frm_image + frm_length;
uchar *record, *null_flags, *null_pos, *mysql57_vcol_null_pos= 0;
const uchar *disk_buff, *strpos;
- ulong pos, record_offset;
+ const uchar *field_properties= NULL;
+ ulong pos, record_offset;
ulong rec_buff_length;
handler *handler_file= 0;
KEY *keyinfo;
@@ -1279,6 +1282,10 @@ int TABLE_SHARE::init_from_binary_frm_image(THD *thd, bool write,
}
#endif /*HAVE_SPATIAL*/
break;
+ case EXTRA2_FIELD_FLAGS:
+ field_properties = extra2;
+ field_additional_property_length= length;
+ break;
default:
/* abort frm parsing if it's an unknown but important extra2 value */
if (type >= EXTRA2_ENGINE_IMPORTANT)
@@ -1595,8 +1602,9 @@ int TABLE_SHARE::init_from_binary_frm_image(THD *thd, bool write,
memcpy(record, frm_image + record_offset, share->reclength);
disk_buff= frm_image + pos + FRM_FORMINFO_SIZE;
-
share->fields= uint2korr(forminfo+258);
+ if (field_properties && field_additional_property_length != share->fields)
+ goto err;
pos= uint2korr(forminfo+260); /* Length of all screens */
n_length= uint2korr(forminfo+268);
interval_count= uint2korr(forminfo+270);
@@ -1607,6 +1615,7 @@ int TABLE_SHARE::init_from_binary_frm_image(THD *thd, bool write,
vcol_screen_length= uint2korr(forminfo+286);
share->virtual_fields= share->default_expressions=
share->field_check_constraints= share->default_fields= 0;
+ share->visible_fields= 0;
share->stored_fields= share->fields;
if (forminfo[46] != (uchar)255)
{
@@ -1978,6 +1987,15 @@ int TABLE_SHARE::init_from_binary_frm_image(THD *thd, bool write,
reg_field->field_index= i;
reg_field->comment=comment;
reg_field->vcol_info= vcol_info;
+ if(field_properties!=NULL)
+ {
+ uint temp= *field_properties++;
+ reg_field->field_visibility= f_visibility(temp);
+ }
+ if (reg_field->field_visibility == USER_DEFINED_INVISIBLE)
+ status_var_increment(thd->status_var.feature_invisible_columns);
+ if (reg_field->field_visibility == NOT_INVISIBLE)
+ share->visible_fields++;
if (field_type == MYSQL_TYPE_BIT && !f_bit_as_char(pack_flag))
{
null_bits_are_used= 1;
@@ -2229,6 +2247,8 @@ int TABLE_SHARE::init_from_binary_frm_image(THD *thd, bool write,
field= key_part->field= share->field[key_part->fieldnr-1];
key_part->type= field->key_type();
+ if (field->field_visibility > USER_DEFINED_INVISIBLE)
+ keyinfo->flags |= HA_INVISIBLE_KEY;
if (field->null_ptr)
{
key_part->null_offset=(uint) ((uchar*) field->null_ptr -
@@ -5107,7 +5127,6 @@ int TABLE::verify_constraints(bool ignore_failure)
return(VIEW_CHECK_OK);
}
-
/*
Find table in underlying tables by mask and check that only this
table belong to given mask
diff --git a/sql/table.h b/sql/table.h
index aded1930a6b..72e70bf0312 100644
--- a/sql/table.h
+++ b/sql/table.h
@@ -335,6 +335,16 @@ enum enum_vcol_update_mode
VCOL_UPDATE_FOR_REPLACE
};
+/* Field visibility enums */
+
+enum field_visible_type{
+ NOT_INVISIBLE= 0,
+ USER_DEFINED_INVISIBLE,
+ /* automatically added by the server. Can be queried explicitly
+ in SELECT, otherwise invisible from anything" */
+ SYSTEM_INVISIBLE,
+ COMPLETELY_INVISIBLE
+};
/**
Category of table found in the table share.
@@ -668,6 +678,7 @@ struct TABLE_SHARE
uint blob_fields; /* number of blob fields */
uint varchar_fields; /* number of varchar fields */
uint default_fields; /* number of default fields */
+ uint visible_fields; /* number of visible fields */
uint default_expressions;
uint table_check_constraints, field_check_constraints;
diff --git a/sql/unireg.cc b/sql/unireg.cc
index 1ca0233552e..a0f42f68bf0 100644
--- a/sql/unireg.cc
+++ b/sql/unireg.cc
@@ -87,6 +87,21 @@ static uchar *extra2_write(uchar *pos, enum extra2_frm_value_type type,
return extra2_write(pos, type, reinterpret_cast<LEX_CSTRING *>(str));
}
+static uchar *extra2_write_additional_field_properties(uchar *pos,
+ int number_of_fields,List_iterator<Create_field> * it)
+{
+ *pos++=EXTRA2_FIELD_FLAGS;
+ /*
+ always first 2 for field visibility
+ */
+ pos= extra2_write_len(pos, number_of_fields);
+ Create_field *cf;
+ while((cf=(*it)++))
+ *pos++= cf->field_visibility;
+ it->rewind();
+ return pos;
+}
+
/**
Create a frm (table definition) file
@@ -121,6 +136,19 @@ LEX_CUSTRING build_frm_image(THD *thd, const char *table,
StringBuffer<MAX_FIELD_WIDTH> vcols;
DBUG_ENTER("build_frm_image");
+ List_iterator<Create_field> it(create_fields);
+ Create_field *field;
+ bool have_additional_field_properties= false;
+ while ((field=it++))
+ {
+ if (field->field_visibility != NOT_INVISIBLE)
+ {
+ have_additional_field_properties= true;
+ break;
+ }
+ }
+ it.rewind();
+
/* If fixed row records, we need one bit to check for deleted rows */
if (!(create_info->table_options & HA_OPTION_PACK_RECORD))
create_info->null_bits++;
@@ -218,7 +246,9 @@ LEX_CUSTRING build_frm_image(THD *thd, const char *table,
if (gis_extra2_len)
extra2_size+= 1 + (gis_extra2_len > 255 ? 3 : 1) + gis_extra2_len;
-
+ if(have_additional_field_properties)
+ extra2_size+=1 + (create_fields.elements > 255 ? 3 : 1) +
+ create_fields.elements;
key_buff_length= uint4korr(fileinfo+47);
@@ -274,7 +304,8 @@ LEX_CUSTRING build_frm_image(THD *thd, const char *table,
pos+= gis_field_options_image(pos, create_fields);
}
#endif /*HAVE_SPATIAL*/
-
+ if (have_additional_field_properties)
+ pos=extra2_write_additional_field_properties(pos,create_fields.elements,&it);
int4store(pos, filepos); // end of the extra2 segment
pos+= 4;
diff --git a/sql/unireg.h b/sql/unireg.h
index b0cfb3841ef..36f985ee1bb 100644
--- a/sql/unireg.h
+++ b/sql/unireg.h
@@ -176,6 +176,7 @@ enum extra2_frm_value_type {
#define EXTRA2_ENGINE_IMPORTANT 128
EXTRA2_ENGINE_TABLEOPTS=128,
+ EXTRA2_FIELD_FLAGS=129
};
int rea_create_table(THD *thd, LEX_CUSTRING *frm,
View Lorry Controller Status