[jira] Commented: (DERBY-3257) SELECT with HAVING clause containing OR conditional incorrectly return 1 row - should return 2 rows - works correctly with 10.2 DB

[A B (JIRA)]

    [ 
https://issues.apache.org/jira/browse/DERBY-3257?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12560151#action_12560151
 ] 

A B commented on DERBY-3257:
----------------------------

I noticed that if the ordering of the predicates inside the HAVING clause is 
reversed, the query returns the "other" results, i.e:

  -- Returns "2"
  SELECT COUNT(t0.ID) FROM TEST_TABLE t0
   GROUP BY t0.CODE HAVING (t0.CODE = 'GBR' OR t0.CODE = 'CHA') AND t0.CODE IS 
NOT NULL;

  -- Returns "4"
  SELECT COUNT(t0.ID) FROM TEST_TABLE t0
   GROUP BY t0.CODE HAVING (t0.CODE = 'CHA' OR t0.CODE = 'GBR') AND t0.CODE IS 
NOT NULL;

With a little tracing it turns out that the HAVING clause is somehow getting 
messed up during preprocessing.  More specifically, see line 883 in 
SelectNode.preprocess():

    if (havingClause != null) {
        havingClause = havingClause.preprocess(
                numTables, fromList, havingSubquerys, wherePredicates);
    }

Before the call to "havingClause.preprocess(...)" the HAVING clause is correct:

  havingClause ==> (OrNode AND NotNullNode)

where:

  -- OrNode ==> (BinaryRelationalOperatorNode_0  OR 
BinaryRelationalOperatorNode_1)
  -- BinaryRelationalOperatorNode_0 ==> (t0.CODE = 'GBR')
  -- BinaryRelationalOperatorNode_1 ==> (t0.CODE = 'CHA')

But after the call to that method, the OrNode has been replaced with its left 
operand:

  havingClause ==> (BinaryRelationalOperatorNode_0 AND NotNullNode)

So we lose half of the OR condition, and that's why we end up with missing 
rows.  A good start for this issue would be to set a break point inside the 
above "if" statement and then trace through the preprocessing logic to see 
when/how/why the OrNode gets replaced with its left operand...

> SELECT with HAVING clause containing OR conditional incorrectly return 1 row 
> - should return 2 rows - works correctly with 10.2 DB
> ----------------------------------------------------------------------------------------------------------------------------------
>
>                 Key: DERBY-3257
>                 URL: https://issues.apache.org/jira/browse/DERBY-3257
>             Project: Derby
>          Issue Type: Bug
>          Components: SQL
>    Affects Versions: 10.3.1.4, 10.3.2.1, 10.4.0.0
>            Reporter: Stan Bradbury
>         Attachments: derby-3257_plan_10.2.txt, derby-3257_plan_10.4.txt, 
> TestHaving.java
>
>
> Attached program demonstrates the problem.  Only one count is returned 
> (matching CODE= GBR) - the count of CODE=CHA is not returned.  Works fine 
> with versions 10.1 and 10.2 or if program is run using 10.3 jars and 10.2 
> database (soft upgrade).
> Query:
> SELECT COUNT(t0.ID) FROM CTS1.TEST_TABLE t0 
>   GROUP BY t0.CODE 
>     HAVING (t0.CODE = 'GBR' OR t0.CODE = 'CHA') AND t0.CODE IS NOT NULL
> Incorrect results (see last line):
> Database product: Apache Derby
> Database version: 10.3.1.5 - (579866)
> Driver name:      Apache Derby Embedded JDBC Driver
> Driver version:   10.3.1.5 - (579866)
> result: 2
> Correct results:
> Database product: Apache Derby
> Database version: 10.2.2.0 - (485682)
> Driver name:      Apache Derby Embedded JDBC Driver
> Driver version:   10.2.2.0 - (485682)
> result: 4
> result: 2

-- 
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.