-- =========================================================
-- ColumnStore cross-engine NULL handling repro (schema-agnostic)
-- =========================================================

-- Version/context
SELECT @@version AS VERSION, @@version_comment AS version_comment;
SHOW GLOBAL STATUS LIKE 'Columnstore_version'; 
SHOW VARIABLES LIKE 'columnstore_select_handler';

-- Safety: use an empty scratch DB or your test DB before running
-- CREATE DATABASE IF NOT EXISTS scratch; USE scratch;
-- USE your_test_db;

-- Clean slate (ignore errors if not present)
DROP TABLE IF EXISTS cs_src_ids;
DROP TABLE IF EXISTS inn_probe_ids;
DROP TABLE IF EXISTS inn_ref_values;

-- Setup
CREATE TABLE cs_src_ids (id INT NOT NULL) ENGINE=COLUMNSTORE;
INSERT INTO cs_src_ids VALUES (1),(2),(3),(4);

CREATE TABLE inn_probe_ids (id INT NOT NULL) ENGINE=INNODB;
INSERT INTO inn_probe_ids VALUES (1),(2),(3),(4);

CREATE TABLE inn_ref_values (
  id       INT NOT NULL,
  char1    CHAR(1)    DEFAULT NULL,
  varchar1 VARCHAR(1) DEFAULT NULL,
  num1     INT        DEFAULT NULL,
  PRIMARY KEY(id)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci;

INSERT INTO inn_ref_values
VALUES (1,'a','A',1), (2,'','',2), (3,NULL,NULL,NULL), (4,'Z','z',4);

-- Baselines (no cross-engine)
SELECT * FROM inn_ref_values ORDER BY id;
SELECT * FROM inn_probe_ids ORDER BY id;
SELECT * FROM cs_src_ids ORDER BY id;

-- =========================
-- Test 1: handler ON/AUTO
-- =========================
SET SESSION columnstore_select_handler = ON;

-- Cross-engine join (ColumnStore + InnoDB)
-- Show raw + HEX to surface invalid-byte behavior
-- (this is where app sees the "Incorrect string value '\xFF…' for column '(temporary)'" error)
-- CREATE TABLE franchises.test_insert AS 
SELECT c.id, i.id,
       i.char1,   HEX(i.char1)   AS char1_hex,
       i.varchar1,HEX(i.varchar1) AS varchar1_hex,
       i.num1
FROM cs_src_ids c
JOIN inn_ref_values i ON i.id = c.id
ORDER BY c.id;

-- row 3 selects like it is NULL, but showing non-null chars
SELECT *
FROM cs_src_ids c
JOIN inn_ref_values i ON i.id=c.id
WHERE c.id=3 AND i.char1 IS NULL AND i.varchar1 IS NULL AND i.num1 IS NULL;


-- =========================
-- Test 2: handler OFF (workaround)
-- =========================
SET SESSION columnstore_select_handler = OFF;

SELECT c.id, i.id,
       i.char1,   HEX(i.char1)   AS char1_hex,
       i.varchar1,HEX(i.varchar1) AS varchar1_hex,
       i.num1
FROM cs_src_ids c
JOIN inn_ref_values i ON i.id = c.id
ORDER BY c.id;

-- Cleanup
DROP TABLE cs_src_ids;
DROP TABLE inn_probe_ids;
DROP TABLE inn_ref_values;
SET SESSION columnstore_select_handler = ON;