Debian Codegraph @

mysql-test/suite/ndbcluster/invisible_columns.result

raw
1 SET SESSION DEFAULT_STORAGE_ENGINE = ndbcluster;
2 #------------------------------------------------------------------------
3 # Test case to verify table creation with only INVISIBLE columns.
4 #------------------------------------------------------------------------
5 CREATE TABLE t1 (f1 INT INVISIBLE, f2 INT INVISIBLE);
6 ERROR HY000: A table must have at least one visible column.
7 CREATE TABLE t1 (f1 INT);
8 ALTER TABLE t1 ALTER f1 SET INVISIBLE;
9 ERROR HY000: A table must have at least one visible column.
10 DROP TABLE t1;
11 #------------------------------------------------------------------------
12 # Test case to verify table with INVISIBLE columns.
13 #------------------------------------------------------------------------
14 CREATE TABLE t1 (f1 INT, f2 INT INVISIBLE VISIBLE);
15 SHOW CREATE TABLE t1;
16 Table Create Table
17 t1 CREATE TABLE `t1` (
18 `f1` int DEFAULT NULL,
19 `f2` int DEFAULT NULL
20 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
21 SHOW COLUMNS FROM t1;
22 Field Type Null Key Default Extra
23 f1 int YES NULL
24 f2 int YES NULL
25 DROP TABLE t1;
26 CREATE TABLE t1 (f1 INT, f2 INT VISIBLE, f3 INT INVISIBLE,
27 f4 INT GENERATED ALWAYS AS
28 ((`f1` + `f2`)) VIRTUAL INVISIBLE,
29 f5 INT GENERATED ALWAYS AS
30 ((`f1` + `f2`)) STORED INVISIBLE);
31 #SHOW CREATE TABLE lists INVISIBLE columns.
32 SHOW CREATE TABLE t1;
33 Table Create Table
34 t1 CREATE TABLE `t1` (
35 `f1` int DEFAULT NULL,
36 `f2` int DEFAULT NULL,
37 `f3` int DEFAULT NULL /*!80023 INVISIBLE */,
38 `f4` int GENERATED ALWAYS AS ((`f1` + `f2`)) VIRTUAL /*!80023 INVISIBLE */,
39 `f5` int GENERATED ALWAYS AS ((`f1` + `f2`)) STORED /*!80023 INVISIBLE */
40 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
41 #SHOW COLUMNS FROM lists invisible columns with INVISIBLE value in EXTRA column.
42 SHOW COLUMNS FROM t1;
43 Field Type Null Key Default Extra
44 f1 int YES NULL
45 f2 int YES NULL
46 f3 int YES NULL INVISIBLE
47 f4 int YES NULL VIRTUAL GENERATED INVISIBLE
48 f5 int YES NULL STORED GENERATED INVISIBLE
49 # For SE hidden columns INVISIBLE value is not listed in EXTRA column.
50 SHOW EXTENDED COLUMNS FROM t1;
51 Field Type Null Key Default Extra
52 f1 int YES NULL
53 f2 int YES NULL
54 f3 int YES NULL INVISIBLE
55 f4 int YES NULL VIRTUAL GENERATED INVISIBLE
56 f5 int YES NULL STORED GENERATED INVISIBLE
57 DROP TABLE t1;
58 CREATE TEMPORARY TABLE temp (f1 INT, f2 INT VISIBLE, f3 INT INVISIBLE,
59 f4 INT GENERATED ALWAYS AS
60 ((`f1` + `f2`)) VIRTUAL INVISIBLE,
61 f5 INT GENERATED ALWAYS AS
62 ((`f1` + `f2`)) STORED INVISIBLE);
63 SHOW CREATE TABLE temp;
64 Table Create Table
65 temp CREATE TEMPORARY TABLE `temp` (
66 `f1` int DEFAULT NULL,
67 `f2` int DEFAULT NULL,
68 `f3` int DEFAULT NULL /*!80023 INVISIBLE */,
69 `f4` int GENERATED ALWAYS AS ((`f1` + `f2`)) VIRTUAL /*!80023 INVISIBLE */,
70 `f5` int GENERATED ALWAYS AS ((`f1` + `f2`)) STORED /*!80023 INVISIBLE */
71 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
72 SHOW COLUMNS FROM temp;
73 Field Type Null Key Default Extra
74 f1 int YES NULL NULL
75 f2 int YES NULL NULL
76 f3 int YES NULL INVISIBLE
77 f4 int YES NULL VIRTUAL GENERATED INVISIBLE
78 f5 int YES NULL STORED GENERATED INVISIBLE
79 SHOW EXTENDED COLUMNS FROM temp;
80 Field Type Null Key Default Extra
81 f1 int YES NULL NULL
82 f2 int YES NULL NULL
83 f3 int YES NULL INVISIBLE
84 f4 int YES NULL VIRTUAL GENERATED INVISIBLE
85 f5 int YES NULL STORED GENERATED INVISIBLE
86 DROP TABLE temp;
87 #------------------------------------------------------------------------
88 # Test case to verify wildcard expansion in SELECT operation on table
89 # with INVISIBLE columns.
90 #------------------------------------------------------------------------
91 CREATE TABLE t1 (f1 INT, f2 INT INVISIBLE);
92 INSERT INTO t1 (f1, f2) VALUES (1, 2);
93 SELECT * FROM t1;
94 f1
95 1
96 SELECT f1, f2 FROM t1;
97 f1 f2
98 1 2
99 SELECT *, f2 FROM t1;
100 f1 f2
101 1 2
102 SELECT t.f1 FROM (SELECT * FROM t1) AS t;
103 f1
104 1
105 SELECT t.f2 FROM (SELECT * FROM t1) AS t;
106 ERROR 42S22: Unknown column 't.f2' in 'field list'
107 CREATE TABLE t2 (f3 INT, f2 INT INVISIBLE);
108 INSERT INTO t2 (f3, f2) VALUES (5, 2);
109 SELECT t1.*, t2.f2 FROM t1 JOIN t2;
110 f1 f2
111 1 2
112 DROP TABLE t1, t2;
113 #------------------------------------------------------------------------
114 # Test case to verify INSERT and SELECT operations on table with
115 # INVISIBLE columns.
116 #------------------------------------------------------------------------
117 CREATE TABLE t1 (f1 INT, f2 INT VISIBLE, f3 INT INVISIBLE);
118 INSERT INTO t1 VALUES (10, 20);
119 SELECT * FROM t1;
120 f1 f2
121 10 20
122 SELECT f1, f2, f3 FROM t1;
123 f1 f2 f3
124 10 20 NULL
125 DELETE FROM t1;
126 INSERT INTO t1(f1, f2, f3) VALUES (1, 2, 3);
127 SELECT * FROM t1;
128 f1 f2
129 1 2
130 SELECT f1, f2, f3 FROM t1;
131 f1 f2 f3
132 1 2 3
133 DELETE FROM t1;
134 #------------------------------------------------------------------------
135 # Test case to verify INVISIBLE columns with DEFAULT value.
136 #------------------------------------------------------------------------
137 ALTER TABLE t1 ALTER f3 SET DEFAULT 8;
138 SHOW CREATE TABLE t1;
139 Table Create Table
140 t1 CREATE TABLE `t1` (
141 `f1` int DEFAULT NULL,
142 `f2` int DEFAULT NULL,
143 `f3` int DEFAULT '8' /*!80023 INVISIBLE */
144 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
145 INSERT INTO t1 VALUES (10, 20);
146 SELECT * FROM t1;
147 f1 f2
148 10 20
149 SELECT f1, f2, f3 FROM t1;
150 f1 f2 f3
151 10 20 8
152 #------------------------------------------------------------------------
153 # Test case to verify generated INVISIBLE columns.
154 #------------------------------------------------------------------------
155 ALTER TABLE t1 ADD COLUMN f4 INT AS (f1 + f2) INVISIBLE;
156 SHOW CREATE TABLE t1;
157 Table Create Table
158 t1 CREATE TABLE `t1` (
159 `f1` int DEFAULT NULL,
160 `f2` int DEFAULT NULL,
161 `f3` int DEFAULT '8' /*!80023 INVISIBLE */,
162 `f4` int GENERATED ALWAYS AS ((`f1` + `f2`)) VIRTUAL /*!80023 INVISIBLE */
163 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
164 SHOW COLUMNS FROM t1;
165 Field Type Null Key Default Extra
166 f1 int YES NULL
167 f2 int YES NULL
168 f3 int YES 8 INVISIBLE
169 f4 int YES NULL VIRTUAL GENERATED INVISIBLE
170 SELECT * FROM t1;
171 f1 f2
172 10 20
173 SELECT f4 FROM t1;
174 f4
175 30
176 DROP TABLE t1;
177 #------------------------------------------------------------------------
178 # Test case to verify INVISIBLE column attribute for all type of columns.
179 #------------------------------------------------------------------------
180 CREATE TABLE t1 (c0 INT,
181 c1 BIT(7) INVISIBLE,
182 c2 BOOLEAN INVISIBLE,
183 c3 TINYINT INVISIBLE,
184 c4 SMALLINT INVISIBLE,
185 c5 MEDIUMINT INVISIBLE,
186 c6 INT INVISIBLE,
187 c7 BIGINT INVISIBLE,
188 c8 DECIMAL(6,2) INVISIBLE,
189 c9 FLOAT INVISIBLE,
190 c10 DOUBLE INVISIBLE,
191 c11 CHAR(1) INVISIBLE,
192 c12 VARCHAR(1) INVISIBLE,
193 c13 BINARY(1) INVISIBLE,
194 c14 VARBINARY(1) INVISIBLE,
195 c15 TINYBLOB INVISIBLE,
196 c16 TINYTEXT INVISIBLE,
197 c17 BLOB INVISIBLE,
198 c18 TEXT INVISIBLE,
199 c19 MEDIUMBLOB INVISIBLE,
200 c20 MEDIUMTEXT INVISIBLE,
201 c21 LONGBLOB INVISIBLE,
202 c22 LONGTEXT INVISIBLE,
203 c23 DATE INVISIBLE,
204 c24 DATETIME INVISIBLE,
205 c25 TIMESTAMP INVISIBLE,
206 c26 TIME INVISIBLE,
207 c27 YEAR INVISIBLE,
208 c28 JSON INVISIBLE,
209 c29 ENUM('a', 'b') INVISIBLE,
210 c30 SET('a', 'b') INVISIBLE,
211 c31 POINT INVISIBLE,
212 c32 LINESTRING INVISIBLE,
213 c33 MULTILINESTRING INVISIBLE,
214 c34 POLYGON INVISIBLE,
215 c35 MULTIPOLYGON INVISIBLE
216 );
217 Warnings:
218 Warning 1478 Table storage engine 'ndbcluster' does not support the create option 'Binlog of table with BLOB attribute and no PK'
219 SHOW CREATE TABLE t1;
220 Table Create Table
221 t1 CREATE TABLE `t1` (
222 `c0` int DEFAULT NULL,
223 `c1` bit(7) DEFAULT NULL /*!80023 INVISIBLE */,
224 `c2` tinyint(1) DEFAULT NULL /*!80023 INVISIBLE */,
225 `c3` tinyint DEFAULT NULL /*!80023 INVISIBLE */,
226 `c4` smallint DEFAULT NULL /*!80023 INVISIBLE */,
227 `c5` mediumint DEFAULT NULL /*!80023 INVISIBLE */,
228 `c6` int DEFAULT NULL /*!80023 INVISIBLE */,
229 `c7` bigint DEFAULT NULL /*!80023 INVISIBLE */,
230 `c8` decimal(6,2) DEFAULT NULL /*!80023 INVISIBLE */,
231 `c9` float DEFAULT NULL /*!80023 INVISIBLE */,
232 `c10` double DEFAULT NULL /*!80023 INVISIBLE */,
233 `c11` char(1) DEFAULT NULL /*!80023 INVISIBLE */,
234 `c12` varchar(1) DEFAULT NULL /*!80023 INVISIBLE */,
235 `c13` binary(1) DEFAULT NULL /*!80023 INVISIBLE */,
236 `c14` varbinary(1) DEFAULT NULL /*!80023 INVISIBLE */,
237 `c15` tinyblob /*!80023 INVISIBLE */,
238 `c16` tinytext /*!80023 INVISIBLE */,
239 `c17` blob /*!80023 INVISIBLE */,
240 `c18` text /*!80023 INVISIBLE */,
241 `c19` mediumblob /*!80023 INVISIBLE */,
242 `c20` mediumtext /*!80023 INVISIBLE */,
243 `c21` longblob /*!80023 INVISIBLE */,
244 `c22` longtext /*!80023 INVISIBLE */,
245 `c23` date DEFAULT NULL /*!80023 INVISIBLE */,
246 `c24` datetime DEFAULT NULL /*!80023 INVISIBLE */,
247 `c25` timestamp NULL DEFAULT NULL /*!80023 INVISIBLE */,
248 `c26` time DEFAULT NULL /*!80023 INVISIBLE */,
249 `c27` year DEFAULT NULL /*!80023 INVISIBLE */,
250 `c28` json DEFAULT NULL /*!80023 INVISIBLE */,
251 `c29` enum('a','b') DEFAULT NULL /*!80023 INVISIBLE */,
252 `c30` set('a','b') DEFAULT NULL /*!80023 INVISIBLE */,
253 `c31` point DEFAULT NULL /*!80023 INVISIBLE */,
254 `c32` linestring DEFAULT NULL /*!80023 INVISIBLE */,
255 `c33` multilinestring DEFAULT NULL /*!80023 INVISIBLE */,
256 `c34` polygon DEFAULT NULL /*!80023 INVISIBLE */,
257 `c35` multipolygon DEFAULT NULL /*!80023 INVISIBLE */
258 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
259 SHOW COLUMNS FROM t1;
260 Field Type Null Key Default Extra
261 c0 int YES NULL
262 c1 bit(7) YES NULL INVISIBLE
263 c2 tinyint(1) YES NULL INVISIBLE
264 c3 tinyint YES NULL INVISIBLE
265 c4 smallint YES NULL INVISIBLE
266 c5 mediumint YES NULL INVISIBLE
267 c6 int YES NULL INVISIBLE
268 c7 bigint YES NULL INVISIBLE
269 c8 decimal(6,2) YES NULL INVISIBLE
270 c9 float YES NULL INVISIBLE
271 c10 double YES NULL INVISIBLE
272 c11 char(1) YES NULL INVISIBLE
273 c12 varchar(1) YES NULL INVISIBLE
274 c13 binary(1) YES NULL INVISIBLE
275 c14 varbinary(1) YES NULL INVISIBLE
276 c15 tinyblob YES NULL INVISIBLE
277 c16 tinytext YES NULL INVISIBLE
278 c17 blob YES NULL INVISIBLE
279 c18 text YES NULL INVISIBLE
280 c19 mediumblob YES NULL INVISIBLE
281 c20 mediumtext YES NULL INVISIBLE
282 c21 longblob YES NULL INVISIBLE
283 c22 longtext YES NULL INVISIBLE
284 c23 date YES NULL INVISIBLE
285 c24 datetime YES NULL INVISIBLE
286 c25 timestamp YES NULL INVISIBLE
287 c26 time YES NULL INVISIBLE
288 c27 year YES NULL INVISIBLE
289 c28 json YES NULL INVISIBLE
290 c29 enum('a','b') YES NULL INVISIBLE
291 c30 set('a','b') YES NULL INVISIBLE
292 c31 point YES NULL INVISIBLE
293 c32 linestring YES NULL INVISIBLE
294 c33 multilinestring YES NULL INVISIBLE
295 c34 polygon YES NULL INVISIBLE
296 c35 multipolygon YES NULL INVISIBLE
297 INSERT INTO t1 VALUES (10);
298 SELECT * FROM t1;
299 c0
300 10
301 DROP TABLE t1;
302 #------------------------------------------------------------------------
303 # Test case to verify INVISIBLE column update.
304 #------------------------------------------------------------------------
305 CREATE TABLE t1 (f1 INT, f2 INT INVISIBLE DEFAULT 8);
306 INSERT INTO t1(f1, f2) VALUES (10, 10), (20, 8);
307 SHOW CREATE TABLE t1;
308 Table Create Table
309 t1 CREATE TABLE `t1` (
310 `f1` int DEFAULT NULL,
311 `f2` int DEFAULT '8' /*!80023 INVISIBLE */
312 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
313 SHOW COLUMNS FROM t1;
314 Field Type Null Key Default Extra
315 f1 int YES NULL
316 f2 int YES 8 INVISIBLE
317 SELECT f1, f2 FROM t1 ORDER BY f1;
318 f1 f2
319 10 10
320 20 8
321 DELETE FROM t1 WHERE f2 = 10;
322 UPDATE t1 SET f2 = 20;
323 SELECT f1, f2 FROM t1;
324 f1 f2
325 20 20
326 DELETE FROM t1;
327 #------------------------------------------------------------------------
328 # Test case to verify Index creation on INVISIBLE column.
329 #------------------------------------------------------------------------
330 INSERT INTO t1 VALUES (10);
331 ALTER TABLE t1 ADD INDEX idx(f2);
332 SHOW CREATE TABLE t1;
333 Table Create Table
334 t1 CREATE TABLE `t1` (
335 `f1` int DEFAULT NULL,
336 `f2` int DEFAULT '8' /*!80023 INVISIBLE */,
337 KEY `idx` (`f2`)
338 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
339 SHOW COLUMNS FROM t1;
340 Field Type Null Key Default Extra
341 f1 int YES NULL
342 f2 int YES MUL 8 INVISIBLE
343 #------------------------------------------------------------------------
344 # Test case to verify Unique Index creation on INVISIBLE column.
345 #------------------------------------------------------------------------
346 ALTER TABLE t1 DROP INDEX idx, ADD UNIQUE INDEX (f2);
347 SHOW CREATE TABLE t1;
348 Table Create Table
349 t1 CREATE TABLE `t1` (
350 `f1` int DEFAULT NULL,
351 `f2` int DEFAULT '8' /*!80023 INVISIBLE */,
352 UNIQUE KEY `f2` (`f2`)
353 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
354 INSERT INTO t1 VALUES (10);
355 ERROR 23000: Duplicate entry '8' for key 't1.f2'
356 INSERT INTO t1(f1, f2) VALUES (10, 10);
357 SELECT f1, f2 FROM t1 ORDER BY f2;
358 f1 f2
359 10 8
360 10 10
361 #------------------------------------------------------------------------
362 # Test case to verify Primary Key creation on INVISIBLE column.
363 #------------------------------------------------------------------------
364 ALTER TABLE t1 DROP INDEX f2, ADD PRIMARY KEY (f2);
365 SHOW CREATE TABLE t1;
366 Table Create Table
367 t1 CREATE TABLE `t1` (
368 `f1` int DEFAULT NULL,
369 `f2` int NOT NULL DEFAULT '8' /*!80023 INVISIBLE */,
370 PRIMARY KEY (`f2`)
371 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
372 INSERT INTO t1 VALUES (10);
373 ERROR 23000: Duplicate entry '8' for key 't1.PRIMARY'
374 INSERT INTO t1(f1, f2) VALUES (10, 20);
375 SELECT f1, f2 FROM t1 ORDER BY f2;
376 f1 f2
377 10 8
378 10 10
379 10 20
380 #------------------------------------------------------------------------
381 # Test case to verify INVISIBLE auto_increment column.
382 #------------------------------------------------------------------------
383 ALTER TABLE t1 MODIFY f2 INT INVISIBLE AUTO_INCREMENT;
384 SHOW CREATE TABLE t1;
385 Table Create Table
386 t1 CREATE TABLE `t1` (
387 `f1` int DEFAULT NULL,
388 `f2` int NOT NULL AUTO_INCREMENT /*!80023 INVISIBLE */,
389 PRIMARY KEY (`f2`)
390 ) ENGINE=ndbcluster AUTO_INCREMENT=21 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
391 INSERT INTO t1 VALUES (10);
392 SELECT f1, f2 FROM t1 ORDER BY f2;
393 f1 f2
394 10 8
395 10 10
396 10 20
397 10 21
398 #------------------------------------------------------------------------
399 # Test case to verify Check Constraint on INVISIBLE column.
400 #------------------------------------------------------------------------
401 ALTER TABLE t1 MODIFY f2 INT INVISIBLE, ADD CHECK(f2 < 20000);
402 SHOW CREATE TABLE t1;
403 Table Create Table
404 t1 CREATE TABLE `t1` (
405 `f1` int DEFAULT NULL,
406 `f2` int NOT NULL /*!80023 INVISIBLE */,
407 PRIMARY KEY (`f2`),
408 CONSTRAINT `t1_chk_1` CHECK ((`f2` < 20000))
409 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
410 INSERT INTO t1(f1, f2) VALUES (10, 20000);
411 ERROR HY000: Check constraint 't1_chk_1' is violated.
412 SELECT f1, f2 FROM t1 ORDER BY f2;
413 f1 f2
414 10 8
415 10 10
416 10 20
417 10 21
418 DROP TABLE t1;
419 #------------------------------------------------------------------------
420 # Test case to verify column visibility alter using ALTER clause of
421 # ALTER TABLE statement.
422 #------------------------------------------------------------------------
423 CREATE TABLE t1 (f1 INT, f2 INT);
424 INSERT INTO t1 VALUES (10, 90);
425 SHOW CREATE TABLE t1;
426 Table Create Table
427 t1 CREATE TABLE `t1` (
428 `f1` int DEFAULT NULL,
429 `f2` int DEFAULT NULL
430 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
431 SELECT * FROM t1;
432 f1 f2
433 10 90
434 ALTER TABLE t1 ALTER COLUMN f1 SET INVISIBLE;
435 SHOW CREATE TABLE t1;
436 Table Create Table
437 t1 CREATE TABLE `t1` (
438 `f1` int DEFAULT NULL /*!80023 INVISIBLE */,
439 `f2` int DEFAULT NULL
440 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
441 SELECT * FROM t1;
442 f2
443 90
444 SELECT f1, f2 FROM t1;
445 f1 f2
446 10 90
447 ALTER TABLE t1 ALTER COLUMN f2 SET INVISIBLE;
448 ERROR HY000: A table must have at least one visible column.
449 ALTER TABLE t1 ALTER COLUMN f1 SET VISIBLE;
450 SHOW CREATE TABLE t1;
451 Table Create Table
452 t1 CREATE TABLE `t1` (
453 `f1` int DEFAULT NULL,
454 `f2` int DEFAULT NULL
455 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
456 SELECT * FROM t1;
457 f1 f2
458 10 90
459 #------------------------------------------------------------------------
460 # Test case to verify column visibility alter using CHANGE clause of
461 # ALTER TABLE statement.
462 #------------------------------------------------------------------------
463 ALTER TABLE t1 CHANGE f1 f1 INT INVISIBLE;
464 SHOW CREATE TABLE t1;
465 Table Create Table
466 t1 CREATE TABLE `t1` (
467 `f1` int DEFAULT NULL /*!80023 INVISIBLE */,
468 `f2` int DEFAULT NULL
469 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
470 SELECT * FROM t1;
471 f2
472 90
473 SELECT f1, f2 FROM t1;
474 f1 f2
475 10 90
476 ALTER TABLE t1 CHANGE f2 f2 INT INVISIBLE;
477 ERROR HY000: A table must have at least one visible column.
478 ALTER TABLE t1 CHANGE f1 f1 INT VISIBLE;
479 SHOW CREATE TABLE t1;
480 Table Create Table
481 t1 CREATE TABLE `t1` (
482 `f1` int DEFAULT NULL,
483 `f2` int DEFAULT NULL
484 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
485 SELECT * FROM t1;
486 f1 f2
487 10 90
488 #------------------------------------------------------------------------
489 # Test case to verify column visibility alter using MODIFY clause of
490 # ALTER TABLE statement.
491 #------------------------------------------------------------------------
492 ALTER TABLE t1 MODIFY f1 INT INVISIBLE;
493 SHOW CREATE TABLE t1;
494 Table Create Table
495 t1 CREATE TABLE `t1` (
496 `f1` int DEFAULT NULL /*!80023 INVISIBLE */,
497 `f2` int DEFAULT NULL
498 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
499 SELECT * FROM t1;
500 f2
501 90
502 SELECT f1, f2 FROM t1;
503 f1 f2
504 10 90
505 ALTER TABLE t1 MODIFY f2 INT INVISIBLE;
506 ERROR HY000: A table must have at least one visible column.
507 ALTER TABLE t1 MODIFY f1 INT VISIBLE;
508 SHOW CREATE TABLE t1;
509 Table Create Table
510 t1 CREATE TABLE `t1` (
511 `f1` int DEFAULT NULL,
512 `f2` int DEFAULT NULL
513 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
514 SELECT * FROM t1;
515 f1 f2
516 10 90
517 DROP TABLE t1;
518 #------------------------------------------------------------------------
519 # Test case to verify LOAD DATA.
520 #------------------------------------------------------------------------
521 CREATE TABLE t1(f1 INT INVISIBLE, f2 INT);
522 CREATE TABLE t2(f1 INT, f2 INT);
523 SHOW CREATE TABLE t1;
524 Table Create Table
525 t1 CREATE TABLE `t1` (
526 `f1` int DEFAULT NULL /*!80023 INVISIBLE */,
527 `f2` int DEFAULT NULL
528 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
529 SHOW CREATE TABLE t2;
530 Table Create Table
531 t2 CREATE TABLE `t2` (
532 `f1` int DEFAULT NULL,
533 `f2` int DEFAULT NULL
534 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
535 INSERT INTO t1(f1, f2) VALUES (10, 20), (20, 30);
536 INSERT INTO t2 VALUES (10, 20), (20, 30);
537 SELECT * FROM t1 INTO OUTFILE 'MYSQLTEST_VARDIR/tmp/test1.sql';;
538 DELETE FROM t1;
539 LOAD DATA INFILE 'MYSQLTEST_VARDIR/tmp/test1.sql' INTO TABLE t1;;
540 SELECT * FROM t1 ORDER BY f2;
541 f2
542 20
543 30
544 SELECT f1, f2 FROM t1 ORDER BY f2;
545 f1 f2
546 NULL 20
547 NULL 30
548 DELETE FROM t1;
549 LOAD DATA INFILE 'MYSQLTEST_VARDIR/tmp/test1.sql' INTO TABLE t1 (f1) SET f2 = 10;;
550 SELECT * FROM t1 ORDER BY f2;
551 f2
552 10
553 10
554 SELECT f1, f2 FROM t1 ORDER BY f1;
555 f1 f2
556 20 10
557 30 10
558 #------------------------------------------------------------------------
559 # Test case to verify LOAD DATA from fixed length row.
560 #------------------------------------------------------------------------
561 SELECT * FROM t1 INTO OUTFILE 'MYSQLTEST_VARDIR/tmp/test2.sql' FIELDS TERMINATED BY '' ENCLOSED BY '';;
562 DELETE FROM t1;
563 LOAD DATA INFILE 'MYSQLTEST_VARDIR/tmp/test2.sql' INTO TABLE t1 FIELDS TERMINATED BY '' ENCLOSED BY '';;
564 SELECT * FROM t1;
565 f2
566 10
567 10
568 SELECT f1, f2 FROM t1;
569 f1 f2
570 NULL 10
571 NULL 10
572 DELETE FROM t1;
573 LOAD DATA INFILE 'MYSQLTEST_VARDIR/tmp/test2.sql' INTO TABLE t1 FIELDS TERMINATED BY '' ENCLOSED BY '' (f1) SET f2 = f1 + 10;;
574 SELECT * FROM t1;
575 f2
576 20
577 20
578 SELECT f1, f2 FROM t1;
579 f1 f2
580 10 20
581 10 20
582 #------------------------------------------------------------------------
583 # Test case to verify LOAD DATA in XML format.
584 #------------------------------------------------------------------------
585 DELETE FROM t1;
586 LOAD XML INFILE "MYSQLTEST_VARDIR/tmp/tmp1.xml" INTO TABLE t1 (f1, f2);;
587 SELECT * FROM t1;
588 f2
589 20
590 20
591 SELECT f1, f2 FROM t1;
592 f1 f2
593 10 20
594 10 20
595 ALTER TABLE t1 DROP COLUMN f1;
596 DELETE FROM t1;
597 ALTER TABLE t1 ADD COLUMN f1 INT INVISIBLE;
598 LOAD XML INFILE "MYSQLTEST_VARDIR/tmp/tmp1.xml" INTO TABLE t1;;
599 SELECT * FROM t1;
600 f2
601 20
602 20
603 SELECT f1, f2 FROM t1;
604 f1 f2
605 NULL 20
606 NULL 20
607 DROP TABLE t1, t2;
608 #------------------------------------------------------------------------
609 # Test case to verify view on INVISIBLE column.
610 #------------------------------------------------------------------------
611 CREATE TABLE t1(f1 INT INVISIBLE, f2 INT);
612 INSERT INTO t1(f1, f2) VALUES (10, 20), (30, 40);
613 # View is created with only f2.
614 CREATE VIEW v1 AS SELECT * FROM t1;
615 SHOW CREATE VIEW v1;
616 View Create View character_set_client collation_connection
617 v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`f2` AS `f2` from `t1` utf8mb4 utf8mb4_0900_ai_ci
618 SHOW COLUMNS FROM v1;
619 Field Type Null Key Default Extra
620 f2 int YES NULL
621 SELECT * FROM v1 ORDER BY f2;
622 f2
623 20
624 40
625 # View is created with f1 and f2;
626 CREATE VIEW v2 AS SELECT f1, f2 FROM t1;
627 SHOW CREATE VIEW v2;
628 View Create View character_set_client collation_connection
629 v2 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v2` AS select `t1`.`f1` AS `f1`,`t1`.`f2` AS `f2` from `t1` utf8mb4 utf8mb4_0900_ai_ci
630 # Both columns are listed as VISIBLE.
631 SHOW COLUMNS FROM v2;
632 Field Type Null Key Default Extra
633 f1 int YES NULL
634 f2 int YES NULL
635 SELECT * FROM v2 ORDER BY f2;
636 f1 f2
637 10 20
638 30 40
639 # View is created with only f1;
640 CREATE VIEW v3 AS SELECT f1 FROM t1;
641 SHOW CREATE VIEW v3;
642 View Create View character_set_client collation_connection
643 v3 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v3` AS select `t1`.`f1` AS `f1` from `t1` utf8mb4 utf8mb4_0900_ai_ci
644 # Column f1 is listed as VISIBLE.
645 SHOW COLUMNS FROM v3;
646 Field Type Null Key Default Extra
647 f1 int YES NULL
648 SELECT * FROM v3 ORDER BY f1;
649 f1
650 10
651 30
652 DROP VIEW v1, v2;
653 #------------------------------------------------------------------------
654 # Test case to verify INFORMATION_SCHEMA.COLUMNS result set.
655 #------------------------------------------------------------------------
656 SELECT TABLE_NAME, COLUMN_NAME, EXTRA FROM INFORMATION_SCHEMA.COLUMNS
657 WHERE TABLE_SCHEMA='test' ORDER BY TABLE_NAME, COLUMN_NAME;
658 TABLE_NAME COLUMN_NAME EXTRA
659 t1 f1 INVISIBLE
660 t1 f2
661 v3 f1
662 DROP VIEW v3;
663 #------------------------------------------------------------------------
664 # Test case to verify REPLACE operation with INVISIBLE column.
665 #------------------------------------------------------------------------
666 ALTER TABLE t1 ADD PRIMARY KEY (f1);
667 SHOW CREATE TABLE t1;
668 Table Create Table
669 t1 CREATE TABLE `t1` (
670 `f1` int NOT NULL /*!80023 INVISIBLE */,
671 `f2` int DEFAULT NULL,
672 PRIMARY KEY (`f1`)
673 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
674 SELECT f1, f2 FROM t1 ORDER BY f2;
675 f1 f2
676 10 20
677 30 40
678 REPLACE INTO t1(f1, f2) VALUE (10, 88);
679 SELECT f1, f2 FROM t1 ORDER BY f2;
680 f1 f2
681 30 40
682 10 88
683 #------------------------------------------------------------------------
684 # Test case to verify INSERT ON DUPLICATE KEY UPDATE operation with
685 # INVISIBLE column.
686 #------------------------------------------------------------------------
687 INSERT INTO t1(f1, f2) VALUES (30, 44) ON DUPLICATE KEY UPDATE f2=88;
688 SELECT f1, f2 FROM t1 ORDER BY f1;
689 f1 f2
690 10 88
691 30 88
692 #------------------------------------------------------------------------
693 # Test case to verify INVISIBLE columns with CREATE TABLE LIKE
694 #------------------------------------------------------------------------
695 CREATE TABLE t2 LIKE t1;
696 SHOW CREATE TABLE t2;
697 Table Create Table
698 t2 CREATE TABLE `t2` (
699 `f1` int NOT NULL /*!80023 INVISIBLE */,
700 `f2` int DEFAULT NULL,
701 PRIMARY KEY (`f1`)
702 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
703 SHOW COLUMNS FROM t2;
704 Field Type Null Key Default Extra
705 f1 int NO PRI NULL INVISIBLE
706 f2 int YES NULL
707 DROP TABLE t2;
708 #------------------------------------------------------------------------
709 # Test case to verify INVISIBLE columns with CREATE TABLE ... SELECT
710 #------------------------------------------------------------------------
711 CREATE TABLE t2 SELECT * FROM t1;
712 # Table is created with only f2 column.
713 SHOW CREATE TABLE t2;
714 Table Create Table
715 t2 CREATE TABLE `t2` (
716 `f2` int DEFAULT NULL
717 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
718 SHOW COLUMNS FROM t2;
719 Field Type Null Key Default Extra
720 f2 int YES NULL
721 SELECT * FROM t2;
722 f2
723 88
724 88
725 DROP TABLE t2;
726 CREATE TABLE t2 SELECT f1, f2 FROM t1;
727 # Both f1 and f2 are created as VISIBLE columns.
728 SHOW CREATE TABLE t2;
729 Table Create Table
730 t2 CREATE TABLE `t2` (
731 `f1` int NOT NULL,
732 `f2` int DEFAULT NULL
733 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
734 SHOW COLUMNS FROM t2;
735 Field Type Null Key Default Extra
736 f1 int NO NULL
737 f2 int YES NULL
738 SELECT * FROM t2 ORDER BY f1;
739 f1 f2
740 10 88
741 30 88
742 DROP TABLE t2;
743 CREATE TABLE t2(f1 INT INVISIBLE) SELECT f1, f2 FROM t1;
744 # f1 is created as INVISIBLE columns and f2 as VISIBLE columns.
745 SHOW CREATE TABLE t2;
746 Table Create Table
747 t2 CREATE TABLE `t2` (
748 `f1` int DEFAULT NULL /*!80023 INVISIBLE */,
749 `f2` int DEFAULT NULL
750 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
751 SHOW COLUMNS FROM t2;
752 Field Type Null Key Default Extra
753 f1 int YES NULL INVISIBLE
754 f2 int YES NULL
755 SELECT * FROM t2;
756 f2
757 88
758 88
759 DROP TABLE t2;
760 CREATE TEMPORARY TABLE t2 SELECT * FROM t1;
761 # Table is created with only f2 column.
762 SHOW CREATE TABLE t2;
763 Table Create Table
764 t2 CREATE TEMPORARY TABLE `t2` (
765 `f2` int DEFAULT NULL
766 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
767 SHOW COLUMNS FROM t2;
768 Field Type Null Key Default Extra
769 f2 int YES NULL NULL
770 SELECT * FROM t2;
771 f2
772 88
773 88
774 DROP TABLE t2;
775 CREATE TEMPORARY TABLE t2 SELECT f1, f2 FROM t1;
776 # Both f1 and f2 are created as VISIBLE columns.
777 SHOW CREATE TABLE t2;
778 Table Create Table
779 t2 CREATE TEMPORARY TABLE `t2` (
780 `f1` int NOT NULL,
781 `f2` int DEFAULT NULL
782 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
783 SHOW COLUMNS FROM t2;
784 Field Type Null Key Default Extra
785 f1 int NO NULL NULL
786 f2 int YES NULL NULL
787 SELECT * FROM t2 ORDER BY f1;
788 f1 f2
789 10 88
790 30 88
791 DROP TABLE t2;
792 CREATE TEMPORARY TABLE t2(f1 INT INVISIBLE) SELECT f1, f2 FROM t1;
793 # f1 is created as INVISIBLE columns and f2 as VISIBLE columns.
794 SHOW CREATE TABLE t2;
795 Table Create Table
796 t2 CREATE TEMPORARY TABLE `t2` (
797 `f1` int DEFAULT NULL /*!80023 INVISIBLE */,
798 `f2` int DEFAULT NULL
799 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
800 SHOW COLUMNS FROM t2;
801 Field Type Null Key Default Extra
802 f1 int YES NULL INVISIBLE
803 f2 int YES NULL NULL
804 SELECT * FROM t2;
805 f2
806 88
807 88
808 DROP TABLE t2;
809 DROP TABLE t1;
810 #------------------------------------------------------------------------
811 # Test case to verify table and column privileges on INVISIBLE columns.
812 #------------------------------------------------------------------------
813 CREATE USER user1@localhost;
814 CREATE DATABASE db1;
815 USE db1;
816 CREATE TABLE t1(f1 INT DEFAULT 10, f2 INT);
817 INSERT INTO t1(f2) VALUES(1);
818 GRANT SELECT(f2) ON db1.t1 TO user1@localhost;
819 # user1 has SELECT privilege on only column f2;
820 connect con1, localhost, user1, , db1;
821 SELECT * FROM t1;
822 ERROR 42000: SELECT command denied to user 'user1'@'localhost' for column 'f1' in table 't1'
823 # Make t1.f1 invisible.
824 connection default;
825 ALTER TABLE t1 ALTER COLUMN f1 SET INVISIBLE;
826 # 'SELECT *' works as only column f2 is listed.
827 connection con1;
828 SELECT * FROM t1;
829 f2
830 1
831 SELECT f1 FROM t1;
832 ERROR 42000: SELECT command denied to user 'user1'@'localhost' for column 'f1' in table 't1'
833 connection default;
834 REVOKE ALL ON db1.t1 FROM user1@localhost;
835 # user1 does not have any privilges on t1 table.
836 connection con1;
837 INSERT INTO t1 VALUES(2);
838 ERROR 42000: INSERT command denied to user 'user1'@'localhost' for table 't1'
839 INSERT INTO t1(f1) VALUES (11);
840 ERROR 42000: INSERT command denied to user 'user1'@'localhost' for table 't1'
841 # Table privilege INSERT is granted to user1.
842 connection default;
843 GRANT INSERT ON db1.t1 TO user1@localhost;
844 connection con1;
845 INSERT INTO t1 VALUES(2);
846 INSERT INTO t1(f1) VALUES (11);
847 # ALL privileges are REVOKED from user1.
848 connection default;
849 REVOKE ALL ON db1.t1 FROM user1@localhost;
850 connection con1;
851 INSERT INTO t1 VALUES(3);
852 ERROR 42000: INSERT command denied to user 'user1'@'localhost' for table 't1'
853 INSERT INTO t1(f1) VALUES (12);
854 ERROR 42000: INSERT command denied to user 'user1'@'localhost' for table 't1'
855 # INSERT on t1.f1 column and SELECT on t1.f1, t1.f2 columns are granted to user1
856 connection default;
857 GRANT INSERT(f1), SELECT(f1, f2) ON db1.t1 TO user1@localhost;
858 connection con1;
859 INSERT INTO t1 VALUES(3);
860 ERROR 42000: INSERT command denied to user 'user1'@'localhost' for table 't1'
861 INSERT INTO t1(f1) VALUES (12);
862 connection default;
863 disconnect con1;
864 USE test;
865 DROP DATABASE db1;
866 DROP USER user1@localhost;
867 #------------------------------------------------------------------------
868 # Test case to verify natural join with invisible columns.
869 #------------------------------------------------------------------------
870 CREATE TABLE t1(a INT, b INT INVISIBLE);
871 CREATE TABLE t2(c INT, b INT INVISIBLE);
872 INSERT INTO t1(b) VALUES (1), (2), (3);
873 INSERT INTO t2(b) VALUES (1), (2), (3);
874 SELECT * FROM t1 JOIN t2;
875 a c
876 NULL NULL
877 NULL NULL
878 NULL NULL
879 NULL NULL
880 NULL NULL
881 NULL NULL
882 NULL NULL
883 NULL NULL
884 NULL NULL
885 SELECT * FROM t1 JOIN t2 WHERE (t1.b = t2.b);
886 a c
887 NULL NULL
888 NULL NULL
889 NULL NULL
890 SELECT * FROM t1 JOIN t2 ON (t1.b = t2.b);
891 a c
892 NULL NULL
893 NULL NULL
894 NULL NULL
895 SELECT * FROM t1 JOIN t2 USING(b) ORDER BY (b);
896 b a c
897 1 NULL NULL
898 2 NULL NULL
899 3 NULL NULL
900 DROP TABLE t1, t2;
901 #------------------------------------------------------------------------
902 # Test case to verify my_row_id name usage for invisible columns.
903 #------------------------------------------------------------------------
904 CREATE TABLE t1(a INT, my_row_id INT);
905 CREATE TABLE t2(a INT, my_row_id INT INVISIBLE);
906 DROP TABLE t1, t2;
907 SET SESSION DEFAULT_STORAGE_ENGINE = DEFAULT;