DROP DATABASE IF EXISTS h58;
CREATE DATABASE h58;
USE h58;
CREATE TABLE source_data(a LONGBLOB);
INSERT INTO source_data VALUES (_binary'A');

SELECT LENGTH(COMPRESS(IF(a IS NOT NULL,_binary'A',SUBSTRING(a,3758096383)))) AS control_direct_len,
       HEX(COMPRESS(IF(a IS NOT NULL,_binary'A',SUBSTRING(a,3758096383)))) AS control_direct_hex
FROM source_data;
SELECT LENGTH(COMPRESS(IF(a IS NOT NULL,_binary'A',SUBSTRING(a,3758096384)))) AS trigger_direct_len,
       HEX(COMPRESS(IF(a IS NOT NULL,_binary'A',SUBSTRING(a,3758096384)))) AS trigger_direct_hex
FROM source_data;

SELECT LENGTH(COMPRESS(IF(a IS NOT NULL,_binary'A',SUBSTRING(a,3758096383)))) AS control_grouped_len,
       HEX(COMPRESS(IF(a IS NOT NULL,_binary'A',SUBSTRING(a,3758096383)))) AS control_grouped_hex
FROM source_data GROUP BY control_grouped_len, control_grouped_hex;
SELECT LENGTH(COMPRESS(IF(a IS NOT NULL,_binary'A',SUBSTRING(a,3758096384)))) AS trigger_grouped_len,
       HEX(COMPRESS(IF(a IS NOT NULL,_binary'A',SUBSTRING(a,3758096384)))) AS trigger_grouped_hex
FROM source_data GROUP BY trigger_grouped_len, trigger_grouped_hex;

CREATE VIEW control_view AS
SELECT COMPRESS(IF(a IS NOT NULL,_binary'A',SUBSTRING(a,3758096383))) AS c
FROM source_data;
CREATE VIEW trigger_view AS
SELECT COMPRESS(IF(a IS NOT NULL,_binary'A',SUBSTRING(a,3758096384))) AS c
FROM source_data;
SELECT TABLE_NAME,DATA_TYPE,CHARACTER_MAXIMUM_LENGTH
FROM information_schema.columns
WHERE TABLE_SCHEMA='h58' AND TABLE_NAME IN ('control_view','trigger_view')
ORDER BY TABLE_NAME;

SET SESSION sql_mode='STRICT_ALL_TABLES';
CREATE TABLE control_ctas AS
SELECT COMPRESS(IF(a IS NOT NULL,_binary'A',SUBSTRING(a,3758096383))) AS c
FROM source_data;
SELECT LENGTH(c),HEX(c) FROM control_ctas;
CREATE TABLE trigger_ctas AS
SELECT COMPRESS(IF(a IS NOT NULL,_binary'A',SUBSTRING(a,3758096384))) AS c
FROM source_data;
SELECT LENGTH(c),HEX(c) FROM trigger_ctas;
