dynamobi/dynamobi — Issue #262: [LDB-59] join with multiple dimension tables, filters on both sometimes returns incorrect results
Author: dynamobi-build
Created: 2011-12-30 02:25:18+00:00
State: open
Labels: LDB

[reporter="elin", created="Tue, 11 Apr 2006 11:17:11 -0500 (GMT-05:00)"]
In company test suite, the following querie is getting incorrect results (compared to results using oracle). n There are repeated rows and sales prices included for sales which the customer was not involved in.

select customers.lname, products.name, sales.price

from sales, customers, products

where customers.custid=sales.custid

and sales.prodid = products.prodid

and customers.lname < 'C'

and products.name >= 'Soap'

order by 1,2,3;

Here's the plan for this:

'column0'

'FennelToIteratorConverter'

' FennelSortRel(key=[[0, 1, 2]], discardDuplicates=[false])'

' FennelCalcRel(expr#0..10=[{inputs}], expr#11=[=($t9, $t4)], LNAME=[$t2], NAME=[$t5], PRICE=[$t10], $condition=[$t11])'

' FennelCartesianProductRel(leftouterjoin=[false])'

'FennelCartesianProductRel(leftouterjoin=[false])'

' LcsRowScanRel(table=[[LOCALDB, S, CUSTOMERS]], projection=[*], clustered indexes=[[SYS$CLUSTERED_INDEX$S$CUSTOMERS$CUSTID, SYS$CLUSTERED_INDEX$S$CUSTOMERS$FNAME, SYS$CLUSTERED_INDEX$S$CUSTOMERS$LNAME, SYS$CLUSTERED_INDEX$S$CUSTOMERS$SEX]])'

' LcsIndexMergeRel'

' LcsIndexSearchRel(table=[[LOCALDB, S, CUSTOMERS]], projection=[*], index=[CUSTOMERS_NAME], uniqueKey=[false], preserveOrder=[false], outer=[false], inputKeyProj=[[1, 3]], inputJoinProj=[[]], inputDirectiveProj=[[0, 2]], startRidParamId=[0], rowLimitParamId=[0])'

'FennelValuesRel(tuples=[[{ '-', null, ')', _ISO-8859-1'C' }]])'

' LcsRowScanRel(table=[[LOCALDB, S, PRODUCTS]], projection=[*], clustered indexes=[[SYS$CLUSTERED_INDEX$S$PRODUCTS$NAME, SYS$CLUSTERED_INDEX$S$PRODUCTS$PRICE, SYS$CLUSTERED_INDEX$S$PRODUCTS$PRODID]])'

' LcsIndexMergeRel'

' LcsIndexSearchRel(table=[[LOCALDB, S, PRODUCTS]], projection=[*], index=[SYS$CONSTRAINT_INDEX$PRODUCTS$SYS$UNIQUE_KEY$NAME], uniqueKey=[false], preserveOrder=[false], outer=[false], inputKeyProj=[[1, 3]], inputJoinProj=[[]], inputDirectiveProj=[[0, 2]], startRidParamId=[0], rowLimitParamId=[0])'

'FennelValuesRel(tuples=[[{ '[', _ISO-8859-1'Soap', '+', null }]])'

'LcsRowScanRel(table=[[LOCALDB, S, SALES]], projection=[*], clustered indexes=[[SYS$CLUSTERED_INDEX$S$SALES$CUSTID, SYS$CLUSTERED_INDEX$S$SALES$EMPNO, SYS$CLUSTERED_INDEX$S$SALES$PRICE, SYS$CLUSTERED_INDEX$S$SALES$PRODID]])'

' LcsIndexIntersectRel(startRidParamId=[4], rowLimitParamId=[5])'

' LcsIndexMergeRel'

' LcsIndexSearchRel(table=[[LOCALDB, S, SALES]], projection=[*], index=[SALES_PRODID], uniqueKey=[true], preserveOrder=[false], outer=[false], inputKeyProj=[*], inputJoinProj=[[]], inputDirectiveProj=[[]], startRidParamId=[4], rowLimitParamId=[5])'

'FennelSortRel(key=[[0]], discardDuplicates=[true])'

' FennelCalcRel(expr#0..2=[{inputs}], expr#3=[CAST($t0):INTEGER], PRODID=[$t3])'

' LcsRowScanRel(table=[[LOCALDB, S, PRODUCTS]], projection=[*], clustered indexes=[[SYS$CLUSTERED_INDEX$S$PRODUCTS$NAME, SYS$CLUSTERED_INDEX$S$PRODUCTS$PRICE, SYS$CLUSTERED_INDEX$S$PRODUCTS$PRODID]])'

' LcsIndexMergeRel'

'LcsIndexSearchRel(table=[[LOCALDB, S, PRODUCTS]], projection=[*], index=[SYS$CONSTRAINT_INDEX$PRODUCTS$SYS$UNIQUE_KEY$NAME], uniqueKey=[false], preserveOrder=[false], outer=[false], inputKeyProj=[[1, 3]], inputJoinProj=[[]], inputDirectiveProj=[[0, 2]], startRidParamId=[0], rowLimitParamId=[0])'

' FennelValuesRel(tuples=[[{ '[', _ISO-8859-1'Soap', '+', null }]])'

' LcsIndexMergeRel'

' LcsIndexSearchRel(table=[[LOCALDB, S, SALES]], projection=[*], index=[SALES_CUSTID], uniqueKey=[true], preserveOrder=[false], outer=[false], inputKeyProj=[*], inputJoinProj=[[]], inputDirectiveProj=[[]], startRidParamId=[4], rowLimitParamId=[5])'

'FennelSortRel(key=[[0]], discardDuplicates=[true])'

' FennelCalcRel(expr#0..3=[{inputs}], expr#4=[CAST($t0):INTEGER], CUSTID=[$t4])'

' LcsRowScanRel(table=[[LOCALDB, S, CUSTOMERS]], projection=[*], clustered indexes=[[SYS$CLUSTERED_INDEX$S$CUSTOMERS$CUSTID, SYS$CLUSTERED_INDEX$S$CUSTOMERS$FNAME, SYS$CLUSTERED_INDEX$S$CUSTOMERS$LNAME, SYS$CLUSTERED_INDEX$S$CUSTOMERS$SEX]])'

' LcsIndexMergeRel'

'LcsIndexSearchRel(table=[[LOCALDB, S, CUSTOMERS]], projection=[*], index=[CUSTOMERS_NAME], uniqueKey=[false], preserveOrder=[false], outer=[false], inputKeyProj=[[1, 3]], inputJoinProj=[[]], inputDirectiveProj=[[0, 2]], startRidParamId=[0], rowLimitParamId=[0])'

' FennelValuesRel(tuples=[[{ '-', null, ')', _ISO-8859-1'C' }]])'

0: jdbc:luciddb:>

Another query with similar issues:

select customers.lname, products.name, sales.price

from sales, customers, products

where customers.custid=sales.custid

and sales.prodid = products.prodid

and customers.lname = 'Andrews'

order by 1,2,3;

Plan:

'column0'

'FennelToIteratorConverter'

' FennelSortRel(key=[[0, 1, 2]], discardDuplicates=[false])'

' FennelCalcRel(expr#0..10=[{inputs}], expr#11=[=($t9, $t4)], LNAME=[$t2], NAME=[$t5], PRICE=[$t10], $condition=[$t11])'

' FennelCartesianProductRel(leftouterjoin=[false])'

'FennelCartesianProductRel(leftouterjoin=[false])'

' LcsRowScanRel(table=[[LOCALDB, S, CUSTOMERS]], projection=[*], clustered indexes=[[SYS$CLUSTERED_INDEX$S$CUSTOMERS$CUSTID, SYS$CLUSTERED_INDEX$S$CUSTOMERS$FNAME, SYS$CLUSTERED_INDEX$S$CUSTOMERS$LNAME, SYS$CLUSTERED_INDEX$S$CUSTOMERS$SEX]])'

' LcsIndexMergeRel'

' LcsIndexSearchRel(table=[[LOCALDB, S, CUSTOMERS]], projection=[*], index=[CUSTOMERS_NAME], uniqueKey=[false], preserveOrder=[false], outer=[false], inputKeyProj=[[1, 3]], inputJoinProj=[[]], inputDirectiveProj=[[0, 2]], startRidParamId=[0], rowLimitParamId=[0])'

'FennelValuesRel(tuples=[[{ '[', _ISO-8859-1'Andrews', ']', _ISO-8859-1'Andrews' }]])'

' LcsRowScanRel(table=[[LOCALDB, S, PRODUCTS]], projection=[*], clustered indexes=[[SYS$CLUSTERED_INDEX$S$PRODUCTS$NAME, SYS$CLUSTERED_INDEX$S$PRODUCTS$PRICE, SYS$CLUSTERED_INDEX$S$PRODUCTS$PRODID]])'

'LcsRowScanRel(table=[[LOCALDB, S, SALES]], projection=[*], clustered indexes=[[SYS$CLUSTERED_INDEX$S$SALES$CUSTID, SYS$CLUSTERED_INDEX$S$SALES$EMPNO, SYS$CLUSTERED_INDEX$S$SALES$PRICE, SYS$CLUSTERED_INDEX$S$SALES$PRODID]])'

' LcsIndexIntersectRel(startRidParamId=[3], rowLimitParamId=[4])'

' LcsIndexMergeRel'

' LcsIndexSearchRel(table=[[LOCALDB, S, SALES]], projection=[*], index=[SALES_PRODID], uniqueKey=[true], preserveOrder=[false], outer=[false], inputKeyProj=[*], inputJoinProj=[[]], inputDirectiveProj=[[]], startRidParamId=[3], rowLimitParamId=[4])'

'FennelSortRel(key=[[0]], discardDuplicates=[true])'

' FennelCalcRel(expr#0..2=[{inputs}], expr#3=[CAST($t0):INTEGER], PRODID=[$t3])'

' LcsRowScanRel(table=[[LOCALDB, S, PRODUCTS]], projection=[*], clustered indexes=[[SYS$CLUSTERED_INDEX$S$PRODUCTS$NAME, SYS$CLUSTERED_INDEX$S$PRODUCTS$PRICE, SYS$CLUSTERED_INDEX$S$PRODUCTS$PRODID]])'

' LcsIndexMergeRel'

' LcsIndexSearchRel(table=[[LOCALDB, S, SALES]], projection=[*], index=[SALES_CUSTID], uniqueKey=[true], preserveOrder=[false], outer=[false], inputKeyProj=[*], inputJoinProj=[[]], inputDirectiveProj=[[]], startRidParamId=[3], rowLimitParamId=[4])'

'FennelSortRel(key=[[0]], discardDuplicates=[true])'

' FennelCalcRel(expr#0..3=[{inputs}], expr#4=[CAST($t0):INTEGER], CUSTID=[$t4])'

' LcsRowScanRel(table=[[LOCALDB, S, CUSTOMERS]], projection=[*], clustered indexes=[[SYS$CLUSTERED_INDEX$S$CUSTOMERS$CUSTID, SYS$CLUSTERED_INDEX$S$CUSTOMERS$FNAME, SYS$CLUSTERED_INDEX$S$CUSTOMERS$LNAME, SYS$CLUSTERED_INDEX$S$CUSTOMERS$SEX]])'

' LcsIndexMergeRel'

'LcsIndexSearchRel(table=[[LOCALDB, S, CUSTOMERS]], projection=[*], index=[CUSTOMERS_NAME], uniqueKey=[false], preserveOrder=[false], outer=[false], inputKeyProj=[[1, 3]], inputJoinProj=[[]], inputDirectiveProj=[[0, 2]], startRidParamId=[0], rowLimitParamId=[0])'

' FennelValuesRel(tuples=[[{ '[', _ISO-8859-1'Andrews', ']', _ISO-8859-1'Andrews' }]])'

0: jdbc:luciddb:>