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