DROP DATABASE IF EXISTS mcol_4222_db;
CREATE DATABASE mcol_4222_db;
USE mcol_4222_db;
CREATE TABLE t1_i (
`C_` varchar(100) DEFAULT NULL
) ENGINE=innodb DEFAULT CHARSET=utf8 ;
CREATE TABLE t1_cs (
`C_` varchar(100) DEFAULT NULL
) ENGINE=columnstore DEFAULT CHARSET=utf8 ;
CREATE PROCEDURE generate_data()
BEGIN NOT ATOMIC
DECLARE i INT DEFAULT 0;
TRUNCATE TABLE t1_i;
TRUNCATE TABLE t1_cs;
START TRANSACTION;
WHILE i < 70000 DO
INSERT INTO t1_i VALUES (i);
SET i = i + 1;
END WHILE;
COMMIT;
END//
CALL generate_data();
INSERT INTO t1_cs SELECT * FROM t1_i;
SELECT COUNT(*), SUM(t1_i.C_ IS NULL) FROM t1_i;
COUNT(*)	SUM(t1_i.C_ IS NULL)
70000	0
SELECT COUNT(*), SUM(t1_cs.C_ IS NULL) FROM t1_cs;
COUNT(*)	SUM(t1_cs.C_ IS NULL)
70000	0
CREATE TABLE t2 LIKE t1_i;
INSERT INTO t2 SELECT *  FROM (SELECT * FROM t1_cs) derived;
SELECT COUNT(*), SUM(C_ IS NULL) FROM t2;
COUNT(*)	SUM(C_ IS NULL)
70000	0
DROP TABLE t2;
CREATE TABLE t2 LIKE t1_cs;
INSERT INTO t2 SELECT * FROM (SELECT * FROM t1_i) derived;
SELECT COUNT(*), SUM(C_ IS NULL) FROM t2;
COUNT(*)	SUM(C_ IS NULL)
70000	0
DROP TABLE t2;
CREATE TABLE t2 LIKE t1_cs;
INSERT INTO t2 SELECT * FROM (SELECT * FROM t1_cs) derived;
SELECT COUNT(*), SUM(C_ IS NULL) FROM t2;
COUNT(*)	SUM(C_ IS NULL)
70000	0
DROP TABLE t2;
DROP TABLE IF EXISTS t1_i;
DROP TABLE IF EXISTS t1_cs;
CREATE TABLE t1_i ( 
`C_` varchar(100) NOT NULL DEFAULT 123
) ENGINE=innodb DEFAULT CHARSET=utf8 ;
CREATE TABLE t1_cs ( 
`C_` varchar(100) NOT NULL DEFAULT 123
) ENGINE=columnstore DEFAULT CHARSET=utf8 ;
CALL generate_data();
INSERT INTO t1_cs SELECT * FROM t1_i;
SELECT COUNT(*), SUM(t1_i.C_ IS NULL) FROM t1_i;
COUNT(*)	SUM(t1_i.C_ IS NULL)
70000	0
SELECT COUNT(*), SUM(t1_cs.C_ IS NULL) FROM t1_cs;
COUNT(*)	SUM(t1_cs.C_ IS NULL)
70000	0
CREATE TABLE t2 LIKE t1_i;
INSERT INTO t2 SELECT *  FROM (SELECT * FROM t1_cs) derived;
SELECT * FROM t2 LIMIT 3;
C_
0
1
2
DROP TABLE t2;
CREATE TABLE t2 LIKE t1_cs;
INSERT INTO t2 SELECT * FROM (SELECT * FROM t1_i) derived;
SELECT * FROM t2 LIMIT 3;
C_
0
1
2
DROP TABLE t2;
CREATE TABLE t2 LIKE t1_cs;
INSERT INTO t2 SELECT * FROM (SELECT * FROM t1_cs) derived;
SELECT * FROM t2 LIMIT 3;
C_
0
1
2
DROP TABLE t2;
DROP DATABASE mcol_4222_db;