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

SELECT LENGTH(JSON_LOOSE(IF(a IS NOT NULL,'{"a":1}',
       SUBSTRING(a,2147483649)))) AS control_direct_len,
       HEX(JSON_LOOSE(IF(a IS NOT NULL,'{"a":1}',
       SUBSTRING(a,2147483649)))) AS control_direct_hex
FROM source_data;
SELECT LENGTH(JSON_LOOSE(IF(a IS NOT NULL,'{"a":1}',
       SUBSTRING(a,2147483648)))) AS trigger_direct_len,
       HEX(JSON_LOOSE(IF(a IS NOT NULL,'{"a":1}',
       SUBSTRING(a,2147483648)))) AS trigger_direct_hex
FROM source_data;

CREATE VIEW control_view AS
SELECT JSON_LOOSE(IF(a IS NOT NULL,'{"a":1}',
       SUBSTRING(a,2147483649))) AS j
FROM source_data;
CREATE VIEW trigger_view AS
SELECT JSON_LOOSE(IF(a IS NOT NULL,'{"a":1}',
       SUBSTRING(a,2147483648))) AS j
FROM source_data;
SELECT TABLE_NAME,DATA_TYPE,CHARACTER_MAXIMUM_LENGTH
FROM information_schema.columns
WHERE TABLE_SCHEMA='h68' AND TABLE_NAME IN ('control_view','trigger_view')
ORDER BY TABLE_NAME;

SELECT LENGTH(JSON_LOOSE(IF(a IS NOT NULL,'{"a":1}',
       SUBSTRING(a,2147483649)))) AS control_grouped_len,
       HEX(JSON_LOOSE(IF(a IS NOT NULL,'{"a":1}',
       SUBSTRING(a,2147483649)))) AS control_grouped_hex
FROM source_data GROUP BY control_grouped_len,control_grouped_hex;
SELECT LENGTH(JSON_LOOSE(IF(a IS NOT NULL,'{"a":1}',
       SUBSTRING(a,2147483648)))) AS trigger_grouped_len,
       HEX(JSON_LOOSE(IF(a IS NOT NULL,'{"a":1}',
       SUBSTRING(a,2147483648)))) AS trigger_grouped_hex
FROM source_data GROUP BY trigger_grouped_len,trigger_grouped_hex;

SET SESSION sql_mode='STRICT_ALL_TABLES';
CREATE TABLE control_ctas AS
SELECT JSON_LOOSE(IF(a IS NOT NULL,'{"a":1}',
       SUBSTRING(a,2147483649))) AS j FROM source_data;
SELECT LENGTH(j),HEX(j) FROM control_ctas;
CREATE TABLE trigger_ctas AS
SELECT JSON_LOOSE(IF(a IS NOT NULL,'{"a":1}',
       SUBSTRING(a,2147483648))) AS j FROM source_data;
SELECT LENGTH(j),HEX(j) FROM trigger_ctas;
SELECT 1 AS alive;
