Debian Codegraph @

mysql-test/suite/engines/funcs/t/in_number_2_unique_constraints_duplicate_update.test

raw
1 --disable_warnings
2 DROP TABLE IF EXISTS t1;
3 --enable_warnings
4 CREATE TABLE t1(c1 TINYINT NOT NULL, c2 TINYINT NULL, c3 TINYINT NULL, PRIMARY KEY (c1), UNIQUE (c2));
5 INSERT INTO t1 (c1,c2,c3) VALUES(10,14,10);
6 INSERT INTO t1 (c1,c2,c3) VALUES(11,13,10);
7 INSERT INTO t1 (c1,c2,c3) VALUES(12,12,10);
8 INSERT INTO t1 (c1,c2,c3) VALUES(13,11,10);
9 INSERT INTO t1 (c1,c2,c3) VALUES(14,10,10);
10 INSERT INTO t1 (c1,c2,c3) VALUES(12,12,10) ON DUPLICATE KEY UPDATE c3=100; # match 1 record
11 INSERT INTO t1 (c1,c2,c3) VALUES(10,10,10) ON DUPLICATE KEY UPDATE c3=100; # match 2 records, only 1 record updated
12 --sorted_result
13 SELECT * FROM t1;
14 DROP TABLE t1;
15 CREATE TABLE t1(c1 SMALLINT NOT NULL, c2 SMALLINT NULL, c3 SMALLINT NULL, PRIMARY KEY (c1), UNIQUE (c2));
16 INSERT INTO t1 (c1,c2,c3) VALUES(10,14,10);
17 INSERT INTO t1 (c1,c2,c3) VALUES(11,13,10);
18 INSERT INTO t1 (c1,c2,c3) VALUES(12,12,10);
19 INSERT INTO t1 (c1,c2,c3) VALUES(13,11,10);
20 INSERT INTO t1 (c1,c2,c3) VALUES(14,10,10);
21 INSERT INTO t1 (c1,c2,c3) VALUES(12,12,10) ON DUPLICATE KEY UPDATE c3=100; # match 1 record
22 INSERT INTO t1 (c1,c2,c3) VALUES(10,10,10) ON DUPLICATE KEY UPDATE c3=100; # match 2 records, only 1 record updated
23 --sorted_result
24 SELECT * FROM t1;
25 DROP TABLE t1;
26 CREATE TABLE t1(c1 MEDIUMINT NOT NULL, c2 MEDIUMINT NULL, c3 MEDIUMINT NULL, PRIMARY KEY (c1), UNIQUE (c2));
27 INSERT INTO t1 (c1,c2,c3) VALUES(10,14,10);
28 INSERT INTO t1 (c1,c2,c3) VALUES(11,13,10);
29 INSERT INTO t1 (c1,c2,c3) VALUES(12,12,10);
30 INSERT INTO t1 (c1,c2,c3) VALUES(13,11,10);
31 INSERT INTO t1 (c1,c2,c3) VALUES(14,10,10);
32 INSERT INTO t1 (c1,c2,c3) VALUES(12,12,10) ON DUPLICATE KEY UPDATE c3=100; # match 1 record
33 INSERT INTO t1 (c1,c2,c3) VALUES(10,10,10) ON DUPLICATE KEY UPDATE c3=100; # match 2 records, only 1 record updated
34 --sorted_result
35 SELECT * FROM t1;
36 DROP TABLE t1;
37 CREATE TABLE t1(c1 INT NOT NULL, c2 INT NULL, c3 INT NULL, PRIMARY KEY (c1), UNIQUE (c2));
38 INSERT INTO t1 (c1,c2,c3) VALUES(10,14,10);
39 INSERT INTO t1 (c1,c2,c3) VALUES(11,13,10);
40 INSERT INTO t1 (c1,c2,c3) VALUES(12,12,10);
41 INSERT INTO t1 (c1,c2,c3) VALUES(13,11,10);
42 INSERT INTO t1 (c1,c2,c3) VALUES(14,10,10);
43 INSERT INTO t1 (c1,c2,c3) VALUES(12,12,10) ON DUPLICATE KEY UPDATE c3=100; # match 1 record
44 INSERT INTO t1 (c1,c2,c3) VALUES(10,10,10) ON DUPLICATE KEY UPDATE c3=100; # match 2 records, only 1 record updated
45 --sorted_result
46 SELECT * FROM t1;
47 DROP TABLE t1;
48 CREATE TABLE t1(c1 INTEGER NOT NULL, c2 INTEGER NULL, c3 INTEGER NULL, PRIMARY KEY (c1), UNIQUE (c2));
49 INSERT INTO t1 (c1,c2,c3) VALUES(10,14,10);
50 INSERT INTO t1 (c1,c2,c3) VALUES(11,13,10);
51 INSERT INTO t1 (c1,c2,c3) VALUES(12,12,10);
52 INSERT INTO t1 (c1,c2,c3) VALUES(13,11,10);
53 INSERT INTO t1 (c1,c2,c3) VALUES(14,10,10);
54 INSERT INTO t1 (c1,c2,c3) VALUES(12,12,10) ON DUPLICATE KEY UPDATE c3=100; # match 1 record
55 INSERT INTO t1 (c1,c2,c3) VALUES(10,10,10) ON DUPLICATE KEY UPDATE c3=100; # match 2 records, only 1 record updated
56 --sorted_result
57 SELECT * FROM t1;
58 DROP TABLE t1;
59 CREATE TABLE t1(c1 BIGINT NOT NULL, c2 BIGINT NULL, c3 BIGINT NULL, PRIMARY KEY (c1), UNIQUE (c2));
60 INSERT INTO t1 (c1,c2,c3) VALUES(10,14,10);
61 INSERT INTO t1 (c1,c2,c3) VALUES(11,13,10);
62 INSERT INTO t1 (c1,c2,c3) VALUES(12,12,10);
63 INSERT INTO t1 (c1,c2,c3) VALUES(13,11,10);
64 INSERT INTO t1 (c1,c2,c3) VALUES(14,10,10);
65 INSERT INTO t1 (c1,c2,c3) VALUES(12,12,10) ON DUPLICATE KEY UPDATE c3=100; # match 1 record
66 INSERT INTO t1 (c1,c2,c3) VALUES(10,10,10) ON DUPLICATE KEY UPDATE c3=100; # match 2 records, only 1 record updated
67 --sorted_result
68 SELECT * FROM t1;
69 DROP TABLE t1;
70 CREATE TABLE t1(c1 DECIMAL NOT NULL, c2 DECIMAL NULL, c3 DECIMAL NULL, PRIMARY KEY (c1), UNIQUE (c2));
71 INSERT INTO t1 (c1,c2,c3) VALUES(10,14,10);
72 INSERT INTO t1 (c1,c2,c3) VALUES(11,13,10);
73 INSERT INTO t1 (c1,c2,c3) VALUES(12,12,10);
74 INSERT INTO t1 (c1,c2,c3) VALUES(13,11,10);
75 INSERT INTO t1 (c1,c2,c3) VALUES(14,10,10);
76 INSERT INTO t1 (c1,c2,c3) VALUES(12,12,10) ON DUPLICATE KEY UPDATE c3=100; # match 1 record
77 INSERT INTO t1 (c1,c2,c3) VALUES(10,10,10) ON DUPLICATE KEY UPDATE c3=100; # match 2 records, only 1 record updated
78 --sorted_result
79 SELECT * FROM t1;
80 DROP TABLE t1;
81 CREATE TABLE t1(c1 DEC NOT NULL, c2 DEC NULL, c3 DEC NULL, PRIMARY KEY (c1), UNIQUE (c2));
82 INSERT INTO t1 (c1,c2,c3) VALUES(10,14,10);
83 INSERT INTO t1 (c1,c2,c3) VALUES(11,13,10);
84 INSERT INTO t1 (c1,c2,c3) VALUES(12,12,10);
85 INSERT INTO t1 (c1,c2,c3) VALUES(13,11,10);
86 INSERT INTO t1 (c1,c2,c3) VALUES(14,10,10);
87 INSERT INTO t1 (c1,c2,c3) VALUES(12,12,10) ON DUPLICATE KEY UPDATE c3=100; # match 1 record
88 INSERT INTO t1 (c1,c2,c3) VALUES(10,10,10) ON DUPLICATE KEY UPDATE c3=100; # match 2 records, only 1 record updated
89 --sorted_result
90 SELECT * FROM t1;
91 DROP TABLE t1;
92 CREATE TABLE t1(c1 FIXED NOT NULL, c2 FIXED NULL, c3 FIXED NULL, PRIMARY KEY (c1), UNIQUE (c2));
93 INSERT INTO t1 (c1,c2,c3) VALUES(10,14,10);
94 INSERT INTO t1 (c1,c2,c3) VALUES(11,13,10);
95 INSERT INTO t1 (c1,c2,c3) VALUES(12,12,10);
96 INSERT INTO t1 (c1,c2,c3) VALUES(13,11,10);
97 INSERT INTO t1 (c1,c2,c3) VALUES(14,10,10);
98 INSERT INTO t1 (c1,c2,c3) VALUES(12,12,10) ON DUPLICATE KEY UPDATE c3=100; # match 1 record
99 INSERT INTO t1 (c1,c2,c3) VALUES(10,10,10) ON DUPLICATE KEY UPDATE c3=100; # match 2 records, only 1 record updated
100 --sorted_result
101 SELECT * FROM t1;
102 DROP TABLE t1;
103 CREATE TABLE t1(c1 NUMERIC NOT NULL, c2 NUMERIC NULL, c3 NUMERIC NULL, PRIMARY KEY (c1), UNIQUE (c2));
104 INSERT INTO t1 (c1,c2,c3) VALUES(10,14,10);
105 INSERT INTO t1 (c1,c2,c3) VALUES(11,13,10);
106 INSERT INTO t1 (c1,c2,c3) VALUES(12,12,10);
107 INSERT INTO t1 (c1,c2,c3) VALUES(13,11,10);
108 INSERT INTO t1 (c1,c2,c3) VALUES(14,10,10);
109 INSERT INTO t1 (c1,c2,c3) VALUES(12,12,10) ON DUPLICATE KEY UPDATE c3=100; # match 1 record
110 INSERT INTO t1 (c1,c2,c3) VALUES(10,10,10) ON DUPLICATE KEY UPDATE c3=100; # match 2 records, only 1 record updated
111 --sorted_result
112 SELECT * FROM t1;
113 DROP TABLE t1;
114 CREATE TABLE t1(c1 DOUBLE NOT NULL, c2 DOUBLE NULL, c3 DOUBLE NULL, PRIMARY KEY (c1), UNIQUE (c2));
115 INSERT INTO t1 (c1,c2,c3) VALUES(10,14,10);
116 INSERT INTO t1 (c1,c2,c3) VALUES(11,13,10);
117 INSERT INTO t1 (c1,c2,c3) VALUES(12,12,10);
118 INSERT INTO t1 (c1,c2,c3) VALUES(13,11,10);
119 INSERT INTO t1 (c1,c2,c3) VALUES(14,10,10);
120 INSERT INTO t1 (c1,c2,c3) VALUES(12,12,10) ON DUPLICATE KEY UPDATE c3=100; # match 1 record
121 INSERT INTO t1 (c1,c2,c3) VALUES(10,10,10) ON DUPLICATE KEY UPDATE c3=100; # match 2 records, only 1 record updated
122 --sorted_result
123 SELECT * FROM t1;
124 DROP TABLE t1;
125 CREATE TABLE t1(c1 REAL NOT NULL, c2 REAL NULL, c3 REAL NULL, PRIMARY KEY (c1), UNIQUE (c2));
126 INSERT INTO t1 (c1,c2,c3) VALUES(10,14,10);
127 INSERT INTO t1 (c1,c2,c3) VALUES(11,13,10);
128 INSERT INTO t1 (c1,c2,c3) VALUES(12,12,10);
129 INSERT INTO t1 (c1,c2,c3) VALUES(13,11,10);
130 INSERT INTO t1 (c1,c2,c3) VALUES(14,10,10);
131 INSERT INTO t1 (c1,c2,c3) VALUES(12,12,10) ON DUPLICATE KEY UPDATE c3=100; # match 1 record
132 INSERT INTO t1 (c1,c2,c3) VALUES(10,10,10) ON DUPLICATE KEY UPDATE c3=100; # match 2 records, only 1 record updated
133 --sorted_result
134 SELECT * FROM t1;
135 DROP TABLE t1;
136 CREATE TABLE t1(c1 DOUBLE PRECISION NOT NULL, c2 DOUBLE PRECISION NULL, c3 DOUBLE PRECISION NULL, PRIMARY KEY (c1), UNIQUE (c2));
137 INSERT INTO t1 (c1,c2,c3) VALUES(10,14,10);
138 INSERT INTO t1 (c1,c2,c3) VALUES(11,13,10);
139 INSERT INTO t1 (c1,c2,c3) VALUES(12,12,10);
140 INSERT INTO t1 (c1,c2,c3) VALUES(13,11,10);
141 INSERT INTO t1 (c1,c2,c3) VALUES(14,10,10);
142 INSERT INTO t1 (c1,c2,c3) VALUES(12,12,10) ON DUPLICATE KEY UPDATE c3=100; # match 1 record
143 INSERT INTO t1 (c1,c2,c3) VALUES(10,10,10) ON DUPLICATE KEY UPDATE c3=100; # match 2 records, only 1 record updated
144 --sorted_result
145 SELECT * FROM t1;
146 DROP TABLE t1;
147 CREATE TABLE t1(c1 FLOAT NOT NULL, c2 FLOAT NULL, c3 FLOAT NULL, PRIMARY KEY (c1), UNIQUE (c2));
148 INSERT INTO t1 (c1,c2,c3) VALUES(10,14,10);
149 INSERT INTO t1 (c1,c2,c3) VALUES(11,13,10);
150 INSERT INTO t1 (c1,c2,c3) VALUES(12,12,10);
151 INSERT INTO t1 (c1,c2,c3) VALUES(13,11,10);
152 INSERT INTO t1 (c1,c2,c3) VALUES(14,10,10);
153 INSERT INTO t1 (c1,c2,c3) VALUES(12,12,10) ON DUPLICATE KEY UPDATE c3=100; # match 1 record
154 INSERT INTO t1 (c1,c2,c3) VALUES(10,10,10) ON DUPLICATE KEY UPDATE c3=100; # match 2 records, only 1 record updated
155 --sorted_result
156 SELECT * FROM t1;
157 DROP TABLE t1;
158