EVOLUTION-MANAGER

Edit File: allow_no_primary_key.result

DROP TABLE IF EXISTS t1; CREATE TABLE t1 (a INT, b CHAR(8)) ENGINE=rocksdb; SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) DEFAULT NULL, `b` char(8) DEFAULT NULL ) ENGINE=ROCKSDB DEFAULT CHARSET=latin1 SHOW COLUMNS IN t1; Field Type Null Key Default Extra a int(11) YES NULL b char(8) YES NULL INSERT INTO t1 (a,b) VALUES (76,'bar'); INSERT INTO t1 (a,b) VALUES (35,'foo'); INSERT INTO t1 (a,b) VALUES (77,'baz'); SELECT * FROM t1 WHERE a = 35; a b 35 foo SELECT * FROM t1 WHERE a = 35 AND b = 'foo'; a b 35 foo SELECT * FROM t1 WHERE a = 77 OR b = 'bar'; a b 76 bar 77 baz SELECT * FROM t1 WHERE a > 35; a b 76 bar 77 baz SELECT * FROM t1; a b 35 foo 76 bar 77 baz UPDATE t1 SET a=a+100; SELECT * FROM t1; a b 135 foo 176 bar 177 baz UPDATE t1 SET a=a-100, b='bbb' WHERE a>100; SELECT * FROM t1; a b 35 bbb 76 bbb 77 bbb UPDATE t1 SET a=300, b='ccc' WHERE a>70; SELECT * FROM t1; a b 300 ccc 300 ccc 35 bbb UPDATE t1 SET a=123 WHERE a=35; SELECT * FROM t1; a b 123 bbb 300 ccc 300 ccc UPDATE t1 SET a=321 WHERE b='ccc'; SELECT * FROM t1; a b 123 bbb 321 ccc 321 ccc INSERT INTO t1 (a,b) VALUES (45,'bob'); SELECT * FROM t1; a b 123 bbb 321 ccc 321 ccc 45 bob DELETE FROM t1 WHERE a=123; SELECT * FROM t1; a b 321 ccc 321 ccc 45 bob DELETE FROM t1 WHERE b > 'bbb' AND a > 100; SELECT * FROM t1; a b 45 bob TRUNCATE TABLE t1; DROP TABLE t1; CREATE TABLE t1 (a INT, c CHAR(8)) ENGINE=rocksdb; INSERT INTO t1 VALUES (1,'a'),(5,'z'); ALTER TABLE t1 ADD COLUMN b INT; SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) DEFAULT NULL, `c` char(8) DEFAULT NULL, `b` int(11) DEFAULT NULL ) ENGINE=ROCKSDB DEFAULT CHARSET=latin1 SELECT * FROM t1; a c b 1 a NULL 5 z NULL ALTER TABLE t1 DROP COLUMN b; SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) DEFAULT NULL, `c` char(8) DEFAULT NULL ) ENGINE=ROCKSDB DEFAULT CHARSET=latin1 SELECT * FROM t1; a c 1 a 5 z DROP TABLE t1; CREATE TABLE t1 (a INT, b CHAR(8), pk INT AUTO_INCREMENT PRIMARY KEY) ENGINE=rocksdb; ALTER TABLE t1 DROP COLUMN pk; SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) DEFAULT NULL, `b` char(8) DEFAULT NULL ) ENGINE=ROCKSDB DEFAULT CHARSET=latin1 SHOW COLUMNS IN t1; Field Type Null Key Default Extra a int(11) YES NULL b char(8) YES NULL INSERT INTO t1 (a,b) VALUES (76,'bar'); INSERT INTO t1 (a,b) VALUES (35,'foo'); INSERT INTO t1 (a,b) VALUES (77,'baz'); SELECT * FROM t1 WHERE a = 35; a b 35 foo SELECT * FROM t1 WHERE a = 35 AND b = 'foo'; a b 35 foo SELECT * FROM t1 WHERE a = 77 OR b = 'bar'; a b 76 bar 77 baz SELECT * FROM t1 WHERE a > 35; a b 76 bar 77 baz SELECT * FROM t1; a b 35 foo 76 bar 77 baz UPDATE t1 SET a=a+100; SELECT * FROM t1; a b 135 foo 176 bar 177 baz UPDATE t1 SET a=a-100, b='bbb' WHERE a>100; SELECT * FROM t1; a b 35 bbb 76 bbb 77 bbb UPDATE t1 SET a=300, b='ccc' WHERE a>70; SELECT * FROM t1; a b 300 ccc 300 ccc 35 bbb UPDATE t1 SET a=123 WHERE a=35; SELECT * FROM t1; a b 123 bbb 300 ccc 300 ccc UPDATE t1 SET a=321 WHERE b='ccc'; SELECT * FROM t1; a b 123 bbb 321 ccc 321 ccc INSERT INTO t1 (a,b) VALUES (45,'bob'); SELECT * FROM t1; a b 123 bbb 321 ccc 321 ccc 45 bob DELETE FROM t1 WHERE a=123; SELECT * FROM t1; a b 321 ccc 321 ccc 45 bob DELETE FROM t1 WHERE b > 'bbb' AND a > 100; SELECT * FROM t1; a b 45 bob TRUNCATE TABLE t1; DROP TABLE t1; DROP TABLE IF EXISTS t1,t2; CREATE TABLE t1 (a INT, b CHAR(8)) ENGINE=rocksdb; INSERT INTO t1 (a,b) VALUES (1,'a'),(2,'b'); CREATE TABLE t2 (a INT, b CHAR(8)) ENGINE=rocksdb; CHECK TABLE t1; Table Op Msg_type Msg_text test.t1 check status OK INSERT INTO t1 (a,b) VALUES (3,'c'); INSERT INTO t2 (a,b) VALUES (4,'d'); CHECK TABLE t1, t2 FOR UPGRADE; Table Op Msg_type Msg_text test.t1 check status OK test.t2 check status OK INSERT INTO t2 (a,b) VALUES (5,'e'); CHECK TABLE t2 QUICK; Table Op Msg_type Msg_text test.t2 check status OK INSERT INTO t1 (a,b) VALUES (6,'f'); CHECK TABLE t1 FAST; Table Op Msg_type Msg_text test.t1 check status OK INSERT INTO t1 (a,b) VALUES (7,'g'); INSERT INTO t2 (a,b) VALUES (8,'h'); CHECK TABLE t2, t1 MEDIUM; Table Op Msg_type Msg_text test.t2 check status OK test.t1 check status OK INSERT INTO t1 (a,b) VALUES (9,'i'); INSERT INTO t2 (a,b) VALUES (10,'j'); CHECK TABLE t1, t2 EXTENDED; Table Op Msg_type Msg_text test.t1 check status OK test.t2 check status OK INSERT INTO t1 (a,b) VALUES (11,'k'); CHECK TABLE t1 CHANGED; Table Op Msg_type Msg_text test.t1 check status OK DROP TABLE t1, t2; CREATE TABLE t1 (a INT, b CHAR(8), UNIQUE INDEX(a)) ENGINE=rocksdb; INSERT INTO t1 (a,b) VALUES (1,'a'),(2,'b'); INSERT INTO t1 (a,b) VALUES (1,'c'); ERROR 23000: Duplicate entry '1' for key 'a' SELECT * FROM t1; a b 1 a 2 b SELECT * FROM t1 WHERE a = 2; a b 2 b EXPLAIN SELECT * FROM t1 WHERE a = 2; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 const a a 5 const 1 DROP TABLE t1; CREATE TABLE t1 (a INT, b CHAR(8)) ENGINE=rocksdb; SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) DEFAULT NULL, `b` char(8) DEFAULT NULL ) ENGINE=ROCKSDB DEFAULT CHARSET=latin1 SHOW COLUMNS IN t1; Field Type Null Key Default Extra a int(11) YES NULL b char(8) YES NULL INSERT INTO t1 (a,b) VALUES (35,'foo'); INSERT INTO t1 (a,b) VALUES (35,'foo'); INSERT INTO t1 (a,b) VALUES (36,'foo'); DELETE FROM t1 WHERE a = 35 AND b = 'foo'; SELECT * FROM t1; a b 36 foo DROP TABLE t1; # # Issue #834/MDEV-15304 ALTER TABLE table_with_hidden_pk causes Can't # write; duplicate key in table error and/or crash # CREATE TABLE t1 (a INT, KEY(a)) ENGINE=RocksDB; INSERT INTO t1 VALUES (1),(1+1); create table t2 (a int); insert into t2 values (10),(20),(30); BEGIN; select * from t2; a 10 20 30 connect con1,localhost,root,,; connection con1; alter table t1 force; connection default; select * from t1; a connection con1; insert into t1 values (100); select * from t1; a 1 2 100 disconnect con1; connection default; rollback; drop table t1,t2;