Re: [PATCH] (DERBY-251) DISTINCT query is returning duplicate rows

[Jack Klebanoff]

+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 <[EMAIL PROTECTED]> 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