db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Jack Klebanoff <klebanoff-de...@sbcglobal.net>
Subject Re: [PATCH] (DERBY-251) DISTINCT query is returning duplicate rows
Date Wed, 11 May 2005 21:00:53 GMT
+1

The revised patch looks OK to me. I think that it should be submitted if 
derbyall passes. Probably derbylang is enough.

Jack Klebanoff
Mamta Satoor wrote:

>Hi Jack,
>
>Appreciate you taking the time to do the review and catching the
>predicate with expression. I have changed the code and also added a
>test case for it. I have fired the derbyall suite on my codeline to
>make sure everything else runs smoothly. Attached is the updated patch
>anyways.
>
>thanks,
>Mamta
>
>On 5/11/05, Jack Klebanoff <klebanoff-derby@sbcglobal.net> wrote:
>  
>
>>The patch does not completely fix the problem. It does not handle the
>>case where the exists table column is embedded in an expression. Try the
>>following variation on the test select:
>>
>>select  distinct  q1."NO1" from IDEPT q1, IDEPT q2
>>where  ( q2."DISCRIM_DEPT" = 'HardwareDept')
>>and  ( q1."DISCRIM_DEPT" = 'SoftwareDept')  and  ( q1."NO1" <> ALL
>>(select  q3."NO1" from IDEPT q3 where  ( ABS(q3."REPORTTO_NO") =  q2."NO1")))
>>
>>Because q3."REPORTTO_NO" is inside a call to ABS the code added to
>>FromList.returnsAtMostSingleRow does not see it.
>>
>>I would suggest using
>>
>>                       JBitSet referencedTables =
>>and.getLeftOperand().getTablesReferenced();
>>                       if( referencedTables.get( existsTableNumber))
>>                       {
>>
>>predicatesTemp.removeElementAt(predicatesTempIndex);
>>                           break;
>>                       }
>>
>>instead of
>>                       BinaryRelationalOperatorNode beon =
>>(BinaryRelationalOperatorNode)
>>                           and.getLeftOperand();
>>                       ValueNode left = beon.getLeftOperand();
>>                       ValueNode right = beon.getRightOperand();
>>
>>                       /* If left or right side of predicate refer to
>>exists base table,
>>                       then remove it */
>>                       if ((left instanceof ColumnReference) &&
>>                           ((ColumnReference) left).getTableNumber() ==
>>existsTableNumber)
>>                       {
>>
>>predicatesTemp.removeElementAt(predicatesTempIndex);
>>                           break;
>>                       }
>>                       else if ((right instanceof ColumnReference) &&
>>                           ((ColumnReference) right).getTableNumber()
>>== existsTableNumber)
>>                       {
>>
>>predicatesTemp.removeElementAt(predicatesTempIndex);
>>                           break;
>>                       }
>>
>>I have tried it out and it seems to work.
>>
>>Jack Klebanoff
>>
>>    
>>


Mime
View raw message