mirror of
https://github.com/MariaDB/server.git
synced 2025-11-04 12:56:14 +01:00
7f444caa0a
MDEV-11 - Modifed tests and result files to use explicit column lists
in INSERT and SELECT statements
111 lines
4.7 KiB
Text
111 lines
4.7 KiB
Text
connect con1,localhost,root,,;
|
|
SET lock_wait_timeout=1;
|
|
connection default;
|
|
DROP TABLE IF EXISTS t1, t2, t3;
|
|
CREATE TABLE t1 (id <INT_COLUMN>, id2 <INT_COLUMN>) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
|
|
INSERT INTO t1 (id,id2) VALUES (1,1),(1,2),(1,3);
|
|
LOCK TABLE t1 LOW_PRIORITY WRITE;
|
|
SELECT id2,COUNT(DISTINCT id) FROM t1 GROUP BY id2;
|
|
id2 COUNT(DISTINCT id)
|
|
1 1
|
|
2 1
|
|
3 1
|
|
UPDATE t1 SET id=-1 WHERE id=1;
|
|
connection con1;
|
|
SELECT id,id2 FROM t1;
|
|
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
|
|
LOCK TABLE t1 READ;
|
|
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
|
|
connection default;
|
|
LOCK TABLE t1 READ;
|
|
UPDATE t1 SET id=1 WHERE id=1;
|
|
ERROR HY000: Table 't1' was locked with a READ lock and can't be updated
|
|
connection con1;
|
|
SELECT COUNT(DISTINCT id) FROM t1;
|
|
COUNT(DISTINCT id)
|
|
1
|
|
UPDATE t1 SET id=2 WHERE id=2;
|
|
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
|
|
LOCK TABLE t1 WRITE;
|
|
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
|
|
LOCK TABLE t1 READ;
|
|
UNLOCK TABLES;
|
|
connection default;
|
|
CREATE TABLE t2 (a <INT_COLUMN>, b <CHAR_COLUMN>) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
|
|
ERROR HY000: Table 't2' was not locked with LOCK TABLES
|
|
CREATE TEMPORARY TABLE t2 (a <INT_COLUMN>, b <CHAR_COLUMN>) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
|
|
DROP TABLE IF EXISTS t2;
|
|
UNLOCK TABLES;
|
|
CREATE TABLE t2 (id <INT_COLUMN>, id2 <INT_COLUMN>) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
|
|
LOCK TABLE t1 WRITE, t2 WRITE;
|
|
INSERT INTO t2 (id,id2) SELECT id,id2 FROM t1;
|
|
UPDATE t1 SET id=1 WHERE id=-1;
|
|
DROP TABLE t1,t2;
|
|
CREATE TABLE t1 (i1 <INT_COLUMN>, nr <INT_COLUMN>) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
|
|
CREATE TABLE t2 (nr <INT_COLUMN>, nm <INT_COLUMN>) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
|
|
INSERT INTO t2 (nr,nm) VALUES (1,3);
|
|
INSERT INTO t2 (nr,nm) VALUES (2,4);
|
|
lock tables t1 write, t2 read;
|
|
INSERT INTO t1 (i1,nr) SELECT 1, nr FROM t2 WHERE nm=3;
|
|
INSERT INTO t1 (i1,nr) SELECT 2, nr FROM t2 WHERE nm=4;
|
|
UNLOCK TABLES;
|
|
LOCK TABLES t1 WRITE;
|
|
INSERT INTO t1 (i1,nr) SELECT i1, nr FROM t1;
|
|
ERROR HY000: Table 't1' was not locked with LOCK TABLES
|
|
UNLOCK TABLES;
|
|
LOCK TABLES t1 WRITE, t1 AS t1_alias READ;
|
|
INSERT INTO t1 (i1,nr) SELECT i1, nr FROM t1 AS t1_alias;
|
|
DROP TABLE t1,t2;
|
|
ERROR HY000: Table 't2' was not locked with LOCK TABLES
|
|
UNLOCK TABLES;
|
|
DROP TABLE t1,t2;
|
|
CREATE TABLE t1 (a <INT_COLUMN>, b <CHAR_COLUMN>) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
|
|
CREATE TABLE t2 (a <INT_COLUMN>, b <CHAR_COLUMN>) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
|
|
CREATE TABLE t3 (a <INT_COLUMN>, b <CHAR_COLUMN>) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
|
|
LOCK TABLES t1 WRITE, t2 WRITE, t3 WRITE;
|
|
DROP TABLE t2, t3, t1;
|
|
CREATE TABLE t1 (a <INT_COLUMN>, b <CHAR_COLUMN>) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
|
|
CREATE TABLE t2 (a <INT_COLUMN>, b <CHAR_COLUMN>) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
|
|
CREATE TABLE t3 (a <INT_COLUMN>, b <CHAR_COLUMN>) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
|
|
LOCK TABLES t1 WRITE, t2 WRITE, t3 WRITE, t1 AS t4 READ;
|
|
ALTER TABLE t2 ADD COLUMN c2 <INT_COLUMN>;
|
|
DROP TABLE t1, t2, t3;
|
|
CREATE TABLE t1 (a <INT_COLUMN>, b <CHAR_COLUMN>) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
|
|
CREATE TABLE t2 (a <INT_COLUMN>, b <CHAR_COLUMN>) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
|
|
LOCK TABLE t1 READ, t2 READ;
|
|
FLUSH TABLE t1;
|
|
ERROR HY000: Table 't1' was locked with a READ lock and can't be updated
|
|
FLUSH TABLES;
|
|
ERROR HY000: Table 't2' was locked with a READ lock and can't be updated
|
|
FLUSH TABLES t1, t2 WITH READ LOCK;
|
|
ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction
|
|
UNLOCK TABLES;
|
|
FLUSH TABLES t1, t2 WITH READ LOCK;
|
|
connection con1;
|
|
INSERT INTO t1 (a,b) VALUES (1,'a'),(2,'b');
|
|
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
|
|
connection default;
|
|
UNLOCK TABLES;
|
|
FLUSH TABLES WITH READ LOCK;
|
|
connection con1;
|
|
INSERT INTO t1 (a,b) VALUES (1,'a'),(2,'b');
|
|
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
|
|
connection default;
|
|
UNLOCK TABLES;
|
|
INSERT INTO t1 (a,b) VALUES (1,'a'),(2,'b');
|
|
FLUSH TABLES WITH READ LOCK AND DISABLE CHECKPOINT;
|
|
DROP TABLE t1, t2;
|
|
ERROR HY000: Can't execute the query because you have a conflicting read lock
|
|
UNLOCK TABLES;
|
|
DROP TABLE t1, t2;
|
|
disconnect con1;
|
|
CREATE TABLE t1 (a <INT_COLUMN>, b <CHAR_COLUMN>) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
|
|
CREATE TABLE t2 (a <INT_COLUMN>, b <CHAR_COLUMN>) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
|
|
LOCK TABLE t1 WRITE, t2 WRITE;
|
|
CREATE TEMPORARY TABLE t1 (a <INT_COLUMN>, b <CHAR_COLUMN>) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
|
|
FLUSH TABLE t1;
|
|
DROP TEMPORARY TABLE t1;
|
|
SELECT a,b FROM t1;
|
|
a b
|
|
UNLOCK TABLES;
|
|
DROP TABLE t1, t2;
|