#102 [QA Bug] select inner join on large table from different nodes. So slow and No error message available given out

[Jira Trac]

*[Description]*
1. 1 fact table (60855 records), 3 dimension tables (606,294,11 records)
2. select inner join 4 tables in the same time on different nodes
3. it took about 1 minute to execute sql for node1
4. on node2 or node3, ERROR: No error message available. was given out.

*[Repro Steps]*
1. Deploy a cluster with 3 nodes
2. Loaddb with schema and objects file (please find them in attachment)
{noformat}
cubrid loaddb -u dba -s ..../AdventureWorksDW_schema basic
cubrid loaddb -u dba -d ..../FactResellerSales_l_objects basic
cubrid loaddb -u dba -d ..../DimEmployee_l_objects basic
cubrid loaddb -u dba -d ..../DimProduct_l_objects basic
cubrid loaddb -u dba -d ..../DimSalesTerritory_l_objects basic
{noformat}
3. cubrid server start basic
4. create global tables
{noformat}
(1)
create global table factresellersales(productkey INTEGER NOT NULL,
orderdatekey INTEGER NOT NULL,
duedatekey INTEGER NOT NULL,
shipdatekey INTEGER NOT NULL,
resellerkey INTEGER NOT NULL,
employeekey INTEGER NOT NULL,
promotionkey INTEGER NOT NULL,
currencykey INTEGER NOT NULL,
salesterritorykey INTEGER NOT NULL,
salesordernumber CHARACTER VARYING(22) NOT NULL,
salesorderlinenumber SMALLINT NOT NULL,
revisionnumber SMALLINT,
orderquantity SMALLINT,
unitprice MONETARY,
extendedamount MONETARY,
unitpricediscountpct FLOAT,
discountamount FLOAT,
productstandardcost MONETARY,
totalproductcost MONETARY,
salesamount MONETARY,
taxamt MONETARY,
freight MONETARY,
carriertrackingnumber CHARACTER VARYING(30),
customerponumber CHARACTER VARYING(30))
partition by hash(productkey) partitions 3 on node 'node1','node2','node3';
(2)
create global table dimemployee(employeekey INTEGER NOT NULL,
parentemployeekey INTEGER,
employeenationalidalternatekey CHARACTER VARYING(20),
salesterritorykey INTEGER,
firstname CHARACTER VARYING(50),
lastname CHARACTER VARYING(50),
middlename CHARACTER VARYING(50),
namestyle BIT(1),
title CHARACTER VARYING(50),
phone CHARACTER VARYING(25),
maritalstatus CHARACTER VARYING(5),
emergencycontactname CHARACTER VARYING(50),
emergencycontactphone CHARACTER VARYING(50),
salariedflag BIT(1),
gender CHARACTER VARYING(5),
payfrequency SMALLINT,
baserate MONETARY,
vacationhours SMALLINT,
sickleavehours SMALLINT,
currentflag BIT(1),
salespersonflag BIT(1),
departmentname CHARACTER VARYING(50),
statusn CHARACTER VARYING(50));
(3)
create global table dimproduct(productkey INTEGER NOT NULL,
productalternatekey CHARACTER VARYING(30),
productsubcategorykey INTEGER,
weightunitmeasurecode CHARACTER VARYING(5),
sizeunitmeasurecode CHARACTER VARYING(5),
standardcost MONETARY,
finishedgoodsflag BIT(1) NOT NULL,
color CHARACTER VARYING(20) NOT NULL,
safetystocklevel SMALLINT,
reorderpoint SMALLINT,
listprice MONETARY,
sizen CHARACTER VARYING(50),
sizerange CHARACTER VARYING(50),
weight FLOAT,
daystomanufacture INTEGER,
productline CHARACTER VARYING(5),
dealerprice MONETARY,
classn CHARACTER VARYING(5),
style CHARACTER VARYING(5),
modelname CHARACTER VARYING(50),
statusn CHARACTER VARYING(10))
(4)
create global table dimsalesterritory(salesterritorykey INTEGER NOT NULL,
salesterritoryalternatekey INTEGER,
salesterritoryregion CHARACTER VARYING(50) NOT NULL,
salesterritorycountry CHARACTER VARYING(50) NOT NULL,
salesterritorygroup CHARACTER VARYING(50));
{noformat}
5. insert data to global tables
{noformat}
insert into DimEmployee select * from DimEmployee_l;
insert into DimProduct select * from DimProduct_l;
insert into DimSalesTerritory select * from DimSalesTerritory_l;
insert into FactResellerSales select * from FactResellerSales_l;
{noformat}

6. on Node1:
{noformat}
select dp.productkey,de.employeekey,dt.salesterritorykey,f.totalproductcost,salesamount
from dimproduct dp,dimemployee de,dimsalesterritory dt,factresellersales f
where f.totalproductcost4000 and f.salesamount800 and f.productkey=dp.productkey
and f.employeekey=de.employeekey and f.salesterritorykey=dt.salesterritorykey order by 1,2,3;
{noformat}

7. at same time, execute sql on Node2:
{noformat}
select dp.productkey,de.employeekey,dt.salesterritorykey,f.totalproductcost,salesamount
from dimproduct dp,dimemployee de,dimsalesterritory dt,factresellersales f
where f.totalproductcost4000 and f.salesamount800 and f.productkey=dp.productkey
and f.employeekey=de.employeekey and f.salesterritorykey=dt.salesterritorykey order by 1,2,3;
{noformat}

*[Actual Result]*
{noformat}
csql select dp.productkey,de.employeekey,dt.salesterritorykey,f.totalproductcost,salesamount
from dimproduct dp,dimemployee de,dimsalesterritory dt,factresellersales f
where f.totalproductcost4000 and f.salesamount800 and f.productkey=dp.productkey
and f.employeekey=de.employeekey and f.salesterritorykey=dt.salesterritorykey order by 1,2,3;
csql ;x

ERROR: No error message available.

0 command(s) successfully processed.
{noformat}