Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
1.1.0
-
None
-
CentOS
-
2017-15, 2017-16, 2017-17, 2017-18
Description
working_tpch1_compareLogOnly/fnJoin.tpch11:
select ps_partkey, sum(ps_supplycost * ps_availqty) as value
from partsupp, supplier, nation
where replace(concat(ps_suppkey, 'A'), 'A', '') = s_suppkey
and 0-s_nationkey = 0-n_nationkey
and n_name = 'GERMANY'
group by ps_partkey
having sum(ps_supplycost * ps_availqty) > ( select sum(ps_supplycost * ps_availqty) * 0.0001 from partsupp, supplier, nation where ps_suppkey = s_suppkey and s_nationkey = n_nationkey and n_name = 'GERMANY' )
order by value desc;
In older Columnstore versions, this caused an error:
ERROR 1815 (HY000) at line 1: Internal error: IDB-1002: 'supplier' and 'partsupp' have incompatible column type specified for join condition.
Columnstore 1.1 returns an empty set.
Run against MariaDB 10.2:
MariaDB [tpch1]> select ps_partkey, sum(ps_supplycost * ps_availqty) as value
-> from partsupp, supplier, nation
-> where replace(concat(ps_suppkey, 'A'), 'A', '') = s_suppkey
-> and 0-s_nationkey = 0-n_nationkey
-> and n_name = 'GERMANY'
-> group by ps_partkey
-> having sum(ps_supplycost * ps_availqty) > ( select sum(ps_supplycost * ps_availqty) * 0.0001 from partsupp, supplier, nation where ps_suppkey = s_suppkey and s_nationkey = n_nationkey and n_name = 'GERMANY' )
-> order by value desc;
-----------------------+
ps_partkey | value |
-----------------------+
129760 | 17538456.86 |
166726 | 16503353.92 |
191287 | 16474801.97 |
161758 | 16101755.54 |
34452 | 15983844.72 |
139035 | 15907078.34 |
9403 | 15451755.62 |
154358 | 15212937.88 |
.
.
.
-----------------------+
1048 rows in set (18.88 sec)
MariaDB [tpch1]>