Debian Codegraph @

mysql-test/suite/gcol_ndb/t/gcol_stored_basic_dml.test

raw
1 ################################################################################
2 # #
3 # Test GENERATED ALWAYS AS ... STORED #
4 # CREATE TABLE #
5 # CREATE TABLE LIKE TABLE #
6 # INSERT INTO ... SELECT #
7 # INSERT #
8 # UPDATE #
9 # SELECT #
10 # DELETE #
11 ################################################################################
12
13
14 --source include/have_ndb.inc
15 SET @@session.default_storage_engine = 'ndbcluster';
16
17
18 create table stored_basic
19 (i int not null primary key,
20 gcol_int int GENERATED ALWAYS AS (100 + i) STORED,
21 misc int,
22 gcol_str varchar(20) GENERATED ALWAYS AS (concat("User ", i)) STORED
23 );
24
25 insert into stored_basic(i) values(1), (2), (3), (4), (5);
26
27 select * from stored_basic order by i;
28
29
30 # Show affected_rows for DELETE tests
31 --enable_info
32
33 # Delete by PK
34 delete from stored_basic where i = 1;
35
36 # Delete by non-indexed generated column
37 delete from stored_basic where gcol_int = 102;
38
39 # Scanning delete on non-indexed column
40 delete from stored_basic where misc is null;
41
42 --disable_info
43
44 # Repopulate table
45 insert into stored_basic(i) values(1), (2), (3), (4), (5);
46
47 # Select generated column only
48 -- sorted_result
49 select gcol_str from stored_basic;
50
51 # Update gcol to non-generated value. should fail.
52 --error ER_NON_DEFAULT_VALUE_FOR_GENERATED_COLUMN
53 update stored_basic set gcol_int = 44 where i = 4;
54
55 # Update non-key column based on gcol. should succeed.
56 update stored_basic set misc = 44 where gcol_int = 104;
57
58 # CREATE new_table ... LIKE old_table
59 create table stored_basic_2 like stored_basic;
60
61 # INSERT INTO new_table SELECT from old_table
62 insert into stored_basic_2 (i,misc) select i,misc from stored_basic;
63 select * from stored_basic_2 order by i;
64
65 # DROP TABLES
66 drop table stored_basic_2;
67 drop table stored_basic;
68