tcph's q2 query fails with enabled QA:


CURRENT_TEST: columnstore/future.query_accelerator
mysqltest: At line 46: query 'SELECT
s_acctbal,
s_name,
n_name,
p_partkey,
p_mfgr,
s_address,
s_phone,
s_comment
FROM part, supplier, partsupp, nation, region
WHERE p_partkey = ps_partkey
AND s_suppkey = ps_suppkey
AND p_size = 15
AND p_type LIKE '%BRASS'
      AND s_nationkey = n_nationkey
AND n_regionkey = r_regionkey
AND r_name = 'EUROPE'
      AND ps_supplycost = (
SELECT MIN(ps_supplycost)
FROM partsupp, supplier, nation, region
WHERE p_partkey = ps_partkey
AND s_suppkey = ps_suppkey
AND s_nationkey = n_nationkey
AND n_regionkey = r_regionkey
AND r_name = 'EUROPE'
      )
ORDER BY s_acctbal DESC, n_name, s_name, p_partkey
LIMIT 100' failed: ER_INTERNAL_ERROR (1815): Internal error: MCS-1000: 'sub-query' and '$added_sub_tpchqa_partsupp_0, nation, region, supplier' are not joined.

The result from queries just before the failure was:
< snip >
s_comment
FROM part, supplier, partsupp, nation, region
WHERE p_partkey = ps_partkey
AND s_suppkey = ps_suppkey
AND p_size = 15
AND p_type LIKE '%BRASS'
      AND s_nationkey = n_nationkey
AND n_regionkey = r_regionkey
AND r_name = 'EUROPE'
      AND ps_supplycost = (
SELECT MIN(ps_supplycost)
FROM partsupp, supplier, nation, region
WHERE p_partkey = ps_partkey
AND s_suppkey = ps_suppkey
AND s_nationkey = n_nationkey
AND n_regionkey = r_regionkey
AND r_name = 'EUROPE'
      )
ORDER BY s_acctbal DESC, n_name, s_name, p_partkey
LIMIT 100;


========================================================================================


cat /usr/share/mariadb/mariadb-test/var/log/query_accelerator.log
DROP DATABASE IF EXISTS tpchqa;
CREATE DATABASE tpchqa;
USE tpchqa;
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;
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();
Table	Op	Msg_type	Msg_text
tpchqa.lineitem	analyze	status	Engine-independent statistics collected
tpchqa.lineitem	analyze	status	OK
SET histogram_size=3;
ANALYZE TABLE partsupp PERSISTENT FOR COLUMNS(ps_partkey) INDEXES();
Table	Op	Msg_type	Msg_text
tpchqa.partsupp	analyze	status	Engine-independent statistics collected
tpchqa.partsupp	analyze	status	OK
SET histogram_size=2;
ANALYZE TABLE part PERSISTENT FOR COLUMNS(p_partkey) INDEXES();
Table	Op	Msg_type	Msg_text
tpchqa.part	analyze	status	Engine-independent statistics collected
tpchqa.part	analyze	status	OK
SET histogram_size=5;
ANALYZE TABLE orders PERSISTENT FOR COLUMNS(o_orderkey) INDEXES();
Table	Op	Msg_type	Msg_text
tpchqa.orders	analyze	status	Engine-independent statistics collected
tpchqa.orders	analyze	status	OK
SELECT count(*) from orders;
count(*)
150000
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";
SET @@columnstore_query_accel_parallel_factor=5;
SELECT  "QUERY 1";
QUERY 1
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;
l_returnflag	l_linestatus	sum_qty	sum_base_price	sum_disc_price	sum_charge	avg_qty	avg_price	avg_disc	count_order
A	F	3774200.00	5320753880.69	5054096266.6828	5256751331.449234	25.537587	36002.123829	0.050145	147790
N	F	95257.00	133737795.84	127132372.6512	132286291.229445	25.300664	35521.326916	0.049394	3765
N	O	7459297.00	10512270008.90	9986238338.3847	10385578376.585467	25.545538	36000.924688	0.050096	292000
R	F	3785523.00	5337950526.47	5071818532.9420	5274405503.049367	25.525944	35994.029214	0.049989	148301
SELECT mcs_get_plan('rules') LIKE '%parallel_ces%' AS qa_q1_applied;
qa_q1_applied
1
SELECT  "QUERY 2";
QUERY 2
QUERY 2
SELECT
s_acctbal,
s_name,
n_name,
p_partkey,
p_mfgr,
s_address,
s_phone,
s_comment
FROM part, supplier, partsupp, nation, region
WHERE p_partkey = ps_partkey
AND s_suppkey = ps_suppkey
AND p_size = 15
AND p_type LIKE '%BRASS'
      AND s_nationkey = n_nationkey
AND n_regionkey = r_regionkey
AND r_name = 'EUROPE'
      AND ps_supplycost = (
SELECT MIN(ps_supplycost)
FROM partsupp, supplier, nation, region
WHERE p_partkey = ps_partkey
AND s_suppkey = ps_suppkey
AND s_nationkey = n_nationkey
AND n_regionkey = r_regionkey
AND r_name = 'EUROPE'
      )
ORDER BY s_acctbal DESC, n_name, s_name, p_partkey
LIMIT 100;
ubuntu@ip-172-31-13-58:~/MariaDBEnterprise/storage/columnstore/columnstore/tests/scripts$