
GRANT ALL PRIVILEGES ON *.* TO 'cejuser'@'localhost';
FLUSH PRIVILEGES;

-- Create ColumnStore functions
CREATE OR REPLACE FUNCTION mcsgetstats RETURNS STRING SONAME 'ha_columnstore.so';
CREATE OR REPLACE FUNCTION calgetstats RETURNS STRING SONAME 'ha_columnstore.so';
CREATE OR REPLACE FUNCTION mcssettrace RETURNS INTEGER SONAME 'ha_columnstore.so';
CREATE OR REPLACE FUNCTION calsettrace RETURNS INTEGER SONAME 'ha_columnstore.so';
CREATE OR REPLACE FUNCTION mcssetparms RETURNS STRING SONAME 'ha_columnstore.so';
CREATE OR REPLACE FUNCTION calsetparms RETURNS STRING SONAME 'ha_columnstore.so';
CREATE OR REPLACE FUNCTION mcsflushcache RETURNS INTEGER SONAME 'ha_columnstore.so';
CREATE OR REPLACE FUNCTION calflushcache RETURNS INTEGER SONAME 'ha_columnstore.so';
CREATE OR REPLACE FUNCTION mcsgettrace RETURNS STRING SONAME 'ha_columnstore.so';
CREATE OR REPLACE FUNCTION calgettrace RETURNS STRING SONAME 'ha_columnstore.so';
CREATE OR REPLACE FUNCTION mcsgetversion RETURNS STRING SONAME 'ha_columnstore.so';
CREATE OR REPLACE FUNCTION calgetversion RETURNS STRING SONAME 'ha_columnstore.so';
CREATE OR REPLACE FUNCTION calonlinealter RETURNS INTEGER SONAME 'ha_columnstore.so';
CREATE OR REPLACE FUNCTION mcsviewtablelock RETURNS STRING SONAME 'ha_columnstore.so';
CREATE OR REPLACE FUNCTION calviewtablelock RETURNS STRING SONAME 'ha_columnstore.so';
CREATE OR REPLACE FUNCTION mcscleartablelock RETURNS STRING SONAME 'ha_columnstore.so';
CREATE OR REPLACE FUNCTION calcleartablelock RETURNS STRING SONAME 'ha_columnstore.so';
CREATE OR REPLACE FUNCTION mcslastinsertid RETURNS INTEGER SONAME 'ha_columnstore.so';
CREATE OR REPLACE FUNCTION callastinsertid RETURNS INTEGER SONAME 'ha_columnstore.so';
CREATE OR REPLACE FUNCTION mcsgetsqlcount RETURNS STRING SONAME 'ha_columnstore.so';
CREATE OR REPLACE FUNCTION calgetsqlcount RETURNS STRING SONAME 'ha_columnstore.so';
CREATE OR REPLACE FUNCTION idbpm RETURNS INTEGER SONAME 'ha_columnstore.so';
CREATE OR REPLACE FUNCTION idbdbroot RETURNS INTEGER SONAME 'ha_columnstore.so';
CREATE OR REPLACE FUNCTION idbsegment RETURNS INTEGER SONAME 'ha_columnstore.so';
CREATE OR REPLACE FUNCTION idbsegmentdir RETURNS INTEGER SONAME 'ha_columnstore.so';
CREATE OR REPLACE FUNCTION idbextentrelativerid RETURNS INTEGER SONAME 'ha_columnstore.so';
CREATE OR REPLACE FUNCTION idbblockid RETURNS INTEGER SONAME 'ha_columnstore.so';
CREATE OR REPLACE FUNCTION idbextentid RETURNS INTEGER SONAME 'ha_columnstore.so';
CREATE OR REPLACE FUNCTION idbextentmin RETURNS STRING SONAME 'ha_columnstore.so';
CREATE OR REPLACE FUNCTION idbextentmax RETURNS STRING SONAME 'ha_columnstore.so';
CREATE OR REPLACE FUNCTION idbpartition RETURNS STRING SONAME 'ha_columnstore.so';
CREATE OR REPLACE FUNCTION idblocalpm RETURNS INTEGER SONAME 'ha_columnstore.so';
CREATE OR REPLACE FUNCTION mcssystemready RETURNS INTEGER SONAME 'ha_columnstore.so';
CREATE OR REPLACE FUNCTION mcssystemreadonly RETURNS INTEGER SONAME 'ha_columnstore.so';
CREATE OR REPLACE FUNCTION mcssystemprimary RETURNS INTEGER SONAME 'ha_columnstore.so';
CREATE OR REPLACE FUNCTION mcs_emindex_size RETURNS INTEGER SONAME 'ha_columnstore.so';
CREATE OR REPLACE FUNCTION mcs_emindex_free RETURNS INTEGER SONAME 'ha_columnstore.so';
CREATE OR REPLACE FUNCTION columnstore_dataload RETURNS STRING SONAME 'ha_columnstore.so';
CREATE OR REPLACE AGGREGATE FUNCTION regr_avgx RETURNS REAL SONAME 'libregr_mysql.so';
CREATE OR REPLACE AGGREGATE FUNCTION regr_avgy RETURNS REAL SONAME 'libregr_mysql.so';
CREATE OR REPLACE AGGREGATE FUNCTION regr_count RETURNS INTEGER SONAME 'libregr_mysql.so';
CREATE OR REPLACE AGGREGATE FUNCTION regr_slope RETURNS REAL SONAME 'libregr_mysql.so';
CREATE OR REPLACE AGGREGATE FUNCTION regr_intercept RETURNS REAL SONAME 'libregr_mysql.so';
CREATE OR REPLACE AGGREGATE FUNCTION regr_r2 RETURNS REAL SONAME 'libregr_mysql.so';
CREATE OR REPLACE AGGREGATE FUNCTION corr RETURNS REAL SONAME 'libregr_mysql.so';
CREATE OR REPLACE AGGREGATE FUNCTION regr_sxx RETURNS REAL SONAME 'libregr_mysql.so';
CREATE OR REPLACE AGGREGATE FUNCTION regr_syy RETURNS REAL SONAME 'libregr_mysql.so';
CREATE OR REPLACE AGGREGATE FUNCTION regr_sxy RETURNS REAL SONAME 'libregr_mysql.so';
CREATE OR REPLACE AGGREGATE FUNCTION covar_pop RETURNS REAL SONAME 'libregr_mysql.so';
CREATE OR REPLACE AGGREGATE FUNCTION covar_samp RETURNS REAL SONAME 'libregr_mysql.so';
CREATE OR REPLACE AGGREGATE FUNCTION distinct_count RETURNS INTEGER SONAME 'libudf_mysql.so';
CREATE OR REPLACE FUNCTION caldisablepartitions RETURNS STRING SONAME 'ha_columnstore.so';
CREATE OR REPLACE FUNCTION calenablepartitions RETURNS STRING SONAME 'ha_columnstore.so';
CREATE OR REPLACE FUNCTION caldroppartitions RETURNS STRING SONAME 'ha_columnstore.so';
CREATE OR REPLACE FUNCTION calshowpartitions RETURNS STRING SONAME 'ha_columnstore.so';
CREATE OR REPLACE FUNCTION caldroppartitionsbyvalue RETURNS STRING SONAME 'ha_columnstore.so';
CREATE OR REPLACE FUNCTION caldisablepartitionsbyvalue RETURNS STRING SONAME 'ha_columnstore.so';
CREATE OR REPLACE FUNCTION calenablepartitionsbyvalue RETURNS STRING SONAME 'ha_columnstore.so';
CREATE OR REPLACE FUNCTION calshowpartitionsbyvalue RETURNS STRING SONAME 'ha_columnstore.so';
CREATE OR REPLACE AGGREGATE FUNCTION moda RETURNS STRING SONAME 'libregr_mysql.so';
CREATE OR REPLACE FUNCTION mcs_set_ddldebug_level RETURNS STRING SONAME 'ha_columnstore.so';
CREATE OR REPLACE FUNCTION mcs_get_plan RETURNS STRING SONAME 'ha_columnstore.so';

-- Database setup
DROP DATABASE IF EXISTS tpchqa;
CREATE DATABASE tpchqa;
USE tpchqa;

-- Create TPCH tables
create table region (
        r_regionkey int PRIMARY KEY,
        r_name char (25),
        r_comment varchar (152)
) ENGINE=InnoDB;

create table nation (
        n_nationkey int PRIMARY KEY,
        n_name char (25),
        n_regionkey int,
        n_comment varchar (152)
) ENGINE=InnoDB;

create table supplier (
        s_suppkey int PRIMARY KEY,
        s_name char (25),
        s_address varchar (40),
        s_nationkey int,
        s_phone char (15),
        s_acctbal decimal(15,2),
        s_comment varchar (101)
) ENGINE=InnoDB;

create table customer (
        c_custkey int PRIMARY KEY,
        c_name varchar (25),
        c_address varchar (40),
        c_nationkey int,
        c_phone char (15),
        c_acctbal decimal(15,2),
        c_mktsegment char (10),
        c_comment varchar (117)
) ENGINE=InnoDB;

create table part (
        p_partkey int PRIMARY KEY,
        p_name varchar (55),
        p_mfgr char (25),
        p_brand char (10),
        p_type varchar (25),
        p_size int,
        p_container char (10),
        p_retailprice decimal(15,2),
        p_comment varchar (23)
) ENGINE=InnoDB;

create table partsupp (
        ps_partkey int,
        ps_suppkey int,
        ps_availqty int,
        ps_supplycost decimal(15,2),
        ps_comment varchar (199),
        PRIMARY KEY (ps_partkey, ps_suppkey)
) ENGINE=InnoDB;

create table orders (
        o_orderkey int PRIMARY KEY,
        o_custkey int,
        o_orderstatus char (1),
        o_totalprice decimal(15,2),
        o_orderdate date,
        o_orderpriority char (15),
        o_clerk char (15),
        o_shippriority int,
        o_comment varchar (79)
) ENGINE=InnoDB;

create table lineitem (
        l_orderkey int,
        l_partkey int,
        l_suppkey int,
        l_linenumber int,
        l_quantity decimal(15,2),
        l_extendedprice decimal(15,2),
        l_discount decimal(15,2),
        l_tax decimal(15,2),
        l_returnflag char (1),
        l_linestatus char (1),
        l_shipdate date,
        l_commitdate date,
        l_receiptdate date,
        l_shipinstruct char (25),
        l_shipmode char (10),
        l_comment varchar (44),
        PRIMARY KEY (l_orderkey, l_linenumber)
) ENGINE=InnoDB;

-- Generate data: Run this command in shell before loading:
-- tpchgen-cli -s 0.1 -o /tmp

-- Load data (update paths if needed)
LOAD DATA LOCAL INFILE '/tmp/region.tbl' INTO TABLE region FIELDS TERMINATED BY '|';
LOAD DATA LOCAL INFILE '/tmp/nation.tbl' INTO TABLE nation FIELDS TERMINATED BY '|';
LOAD DATA LOCAL INFILE '/tmp/customer.tbl' INTO TABLE customer FIELDS TERMINATED BY '|';
LOAD DATA LOCAL INFILE '/tmp/supplier.tbl' INTO TABLE supplier FIELDS TERMINATED BY '|';
LOAD DATA LOCAL INFILE '/tmp/part.tbl' INTO TABLE part FIELDS TERMINATED BY '|';
LOAD DATA LOCAL INFILE '/tmp/partsupp.tbl' INTO TABLE partsupp FIELDS TERMINATED BY '|';
LOAD DATA LOCAL INFILE '/tmp/orders.tbl' INTO TABLE orders FIELDS TERMINATED BY '|';
LOAD DATA LOCAL INFILE '/tmp/lineitem.tbl' INTO TABLE lineitem FIELDS TERMINATED BY '|';

-- Create indexes
ALTER TABLE partsupp ADD INDEX I1 (PS_PARTKEY, PS_SUPPKEY);
ALTER TABLE lineitem ADD INDEX(`l_orderkey`, `l_suppkey`);
ALTER TABLE orders   ADD INDEX(`o_orderkey`, `o_orderstatus`);
ALTER TABLE supplier ADD INDEX(s_nationkey, s_suppkey,s_name);
SET histogram_size=10;
ANALYZE TABLE lineitem PERSISTENT FOR COLUMNS(l_orderkey) INDEXES();
SET histogram_size=3;
ANALYZE TABLE partsupp PERSISTENT FOR COLUMNS(ps_partkey) INDEXES();
SET histogram_size=2;
ANALYZE TABLE part PERSISTENT FOR COLUMNS(p_partkey) INDEXES();
SET histogram_size=5;
ANALYZE TABLE orders PERSISTENT FOR COLUMNS(o_orderkey) INDEXES();

-- ============================================
-- QUERY 1 (activates Query Accelerator)
-- ============================================

-- Enable RBO and Query Accelerator
set columnstore_unstable_optimizer=on;
set optimizer_switch="index_merge=off,index_merge_union=off,index_merge_sort_union=off,index_merge_intersection=off,index_merge_sort_intersection=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=on,in_to_exists=off,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=off,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=off,join_cache_hashed=off,join_cache_bka=off,optimize_join_buffer_size=off,table_elimination=off,extended_keys=off,exists_to_in=off,orderby_uses_equalities=off,condition_pushdown_for_derived=on,split_materialized=off,condition_pushdown_for_subquery=off,rowid_filter=off,condition_pushdown_from_having=on,not_null_range_scan=off,hash_join_cardinality=off,cset_narrowing=off,sargable_casefold=off";

-- Execute Query 1 first (this is important!)
SELECT  "QUERY 1";
SELECT
    l_returnflag,
    l_linestatus,
    SUM(l_quantity) AS sum_qty,
    SUM(l_extendedprice) AS sum_base_price,
    SUM(l_extendedprice * (1 - l_discount)) AS sum_disc_price,
    SUM(l_extendedprice * (1 - l_discount) * (1 + l_tax)) AS sum_charge,
    AVG(l_quantity) AS avg_qty,
    AVG(l_extendedprice) AS avg_price,
    AVG(l_discount) AS avg_disc,
    COUNT(*) AS count_order
FROM lineitem
WHERE l_shipdate <= DATE '1998-12-01' - INTERVAL 90 DAY
GROUP BY l_returnflag, l_linestatus
ORDER BY l_returnflag, l_linestatus;

-- ============================================
-- QUERY 13 - THIS SHOULD FAIL NOW!
-- ============================================
-- Note: disable_rbo_parallel_ces.inc in the test is a WORKAROUND to avoid the bug
-- We keep RBO enabled here, which triggers the bug after Query 1 execution

SELECT  "QUERY 13";
SELECT
    c_count,
    COUNT(*) AS custdist
FROM (
    SELECT c_custkey, COUNT(o_orderkey) AS c_count
    FROM customer LEFT OUTER JOIN orders
    ON c_custkey = o_custkey
      AND o_comment NOT LIKE '%special%requests%'
    GROUP BY c_custkey
) AS c_orders
GROUP BY c_count
ORDER BY custdist DESC, c_count DESC;

-- Cleanup
DROP DATABASE tpchqa;
