Details
-
Bug
-
Resolution: Fixed
-
Critical
-
6.0.0
-
Untriaged
-
0
-
Unknown
-
Analytics Sprint 23, Analytics Sprint 24, Analytics Sprint 25, Analytics Sprint 26, Analytics Sprint 27, Analytics Sprint 28, Analytics Sprint 29, Analytics Sprint 32
Description
Using indexnl to do left outer join seems to produce incorrect result depending on the join condition. The below query using indexnl gives a different result than the query using hash-join:
use tpch;
|
|
select count (*) from Customer c left outer join Orders o on c.c_custkey = o.o_custkey and o.o_comment not like '%special%requests%';
|
|
use tpch;
|
|
select count (*) from Customer c left outer join Orders o on c.c_custkey /*+ indexnl */ = o.o_custkey and o.o_comment not like '%special%requests%';
|
DDLs:
drop dataverse tpch if exists;
|
create dataverse tpch;
|
|
use tpch;
|
|
create type tpch.LineItemType as
|
closed {
|
l_orderkey : bigint,
|
l_partkey : bigint,
|
l_suppkey : bigint,
|
l_linenumber : bigint,
|
l_quantity : bigint,
|
l_extendedprice : double,
|
l_discount : double,
|
l_tax : double,
|
l_returnflag : string,
|
l_linestatus : string,
|
l_shipdate : string,
|
l_commitdate : string,
|
l_receiptdate : string,
|
l_shipinstruct : string,
|
l_shipmode : string,
|
l_comment : string
|
};
|
|
create type tpch.OrderType as
|
closed {
|
o_orderkey : bigint,
|
o_custkey : bigint,
|
o_orderstatus : string,
|
o_totalprice : double,
|
o_orderdate : string,
|
o_orderpriority : string,
|
o_clerk : string,
|
o_shippriority : bigint,
|
o_comment : string
|
};
|
|
create type tpch.CustomerType as
|
closed {
|
c_custkey : bigint,
|
c_name : string,
|
c_address : string,
|
c_nationkey : bigint,
|
c_phone : string,
|
c_acctbal : double,
|
c_mktsegment : string,
|
c_comment : string
|
};
|
|
create type tpch.SupplierType as
|
closed {
|
s_suppkey : bigint,
|
s_name : string,
|
s_address : string,
|
s_nationkey : bigint,
|
s_phone : string,
|
s_acctbal : double,
|
s_comment : string
|
};
|
|
create type tpch.NationType as
|
closed {
|
n_nationkey : bigint,
|
n_name : string,
|
n_regionkey : bigint,
|
n_comment : string
|
};
|
|
create type tpch.RegionType as
|
closed {
|
r_regionkey : bigint,
|
r_name : string,
|
r_comment : string
|
};
|
|
create type tpch.PartType as
|
closed {
|
p_partkey : bigint,
|
p_name : string,
|
p_mfgr : string,
|
p_brand : string,
|
p_type : string,
|
p_size : bigint,
|
p_container : string,
|
p_retailprice : double,
|
p_comment : string
|
};
|
|
create type tpch.PartSuppType as
|
closed {
|
ps_partkey : bigint,
|
ps_suppkey : bigint,
|
ps_availqty : bigint,
|
ps_supplycost : double,
|
ps_comment : string
|
};
|
|
create dataset LineItem(LineItemType) primary key l_orderkey,l_linenumber;
|
|
create dataset Orders(OrderType) primary key o_orderkey;
|
|
create dataset Supplier(SupplierType) primary key s_suppkey;
|
|
create dataset Region(RegionType) primary key r_regionkey;
|
|
create dataset Nation(NationType) primary key n_nationkey;
|
|
create dataset Part(PartType) primary key p_partkey;
|
|
create dataset Partsupp(PartSuppType) primary key ps_partkey,ps_suppkey;
|
|
create dataset Customer(CustomerType) primary key c_custkey;
|
|
create index customer_fk_nation on Customer (c_nationkey);
|
create index lineitem_shipdateIx on LineItem (l_shipdate);
|
create index lineitem_receiptdateIx on LineItem (l_receiptdate);
|
create index lineitem_fk_orders on LineItem (l_orderkey);
|
create index lineitem_fk_part on LineItem (l_partkey);
|
create index lineitem_fk_supplier on LineItem (l_suppkey);
|
create index nation_fk_region on Nation(n_regionkey);
|
create index partsupp_fk_part on Partsupp (ps_partkey);
|
create index partsupp_fk_supplier on Partsupp (ps_suppkey);
|
create index orders_fk_customer on Orders (o_custkey);
|
create index orders_orderdateIx on Orders (o_orderdate);
|
create index supplier_fk_nation on Supplier (s_nationkey);
|
Test data:
use tpch;
|
|
load dataset LineItem using localfs ((`path`=`asterix_nc1://data/tpch0.001/lineitem.tbl`),(`format`=`delimited-text`),(`delimiter`=`|`));
|
|
load dataset Orders using localfs ((`path`=`asterix_nc1://data/tpch0.001/orders.tbl`),(`format`=`delimited-text`),(`delimiter`=`|`));
|
|
load dataset Supplier using localfs ((`path`=`asterix_nc1://data/tpch0.001/supplier.tbl`),(`format`=`delimited-text`),(`delimiter`=`|`));
|
|
load dataset Region using localfs ((`path`=`asterix_nc1://data/tpch0.001/region.tbl`),(`format`=`delimited-text`),(`delimiter`=`|`));
|
|
load dataset Nation using localfs ((`path`=`asterix_nc1://data/tpch0.001/nation.tbl`),(`format`=`delimited-text`),(`delimiter`=`|`));
|
|
load dataset Part using localfs ((`path`=`asterix_nc1://data/tpch0.001/part.tbl`),(`format`=`delimited-text`),(`delimiter`=`|`));
|
|
load dataset Partsupp using localfs ((`path`=`asterix_nc1://data/tpch0.001/partsupp.tbl`),(`format`=`delimited-text`),(`delimiter`=`|`));
|
|
load dataset Customer using localfs ((`path`=`asterix_nc1://data/tpch0.001/customer.tbl`),(`format`=`delimited-text`),(`delimiter`=`|`));
|