db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "A B (JIRA)" <j...@apache.org>
Subject [jira] Updated: (DERBY-3257) SELECT with HAVING clause containing OR conditional incorrectly return 1 row - should return 2 rows - works correctly with 10.2 DB
Date Thu, 17 Jan 2008 23:41:35 GMT

     [ https://issues.apache.org/jira/browse/DERBY-3257?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]

A B updated DERBY-3257:
-----------------------

    Attachment: d3257_doNOTCommit.patch

I started worrying that the underlying cause of this might be DERBY-47 after all, so I did
a little more tracing.  From what I can tell, the code in OrNode.preprocess() that converts
an OR chain into a IN list was doing the wrong thing--esp. it was only grabbing the left OR
value, not the right.  If the same predicate appears in the WHERE clause the preprocessing
is fine, though...

It turns out that the OrNode preprocess() method expects everything to be normalized to Conjunctive
Normal Form--and we do in fact normalize the WHERE as part of SelectNode.normExpressions().
 But we do *NOT* currently normalize the HAVING clause, so when the OrNode in the HAVING clause
was preprocessed, we were doing the wrong thing.

I made a very quick change to normalize the HAVING clause, as well, and the queries posted
to this issue now return correct results (2 rows).  I have not run any other tests on it,
though, so this might not be a valid fix.  But it does indicate what the problem is to some
degree...

Why this worked before DERBY-681 and fails after I'm not sure, but given the scope of DERBY-681
it seems quite possible that the changes for that issue affected HAVING predicates in a way
such that normalization is now a requirement...?

I'm calling my quick change d3257_doNOTCommit.patch because I haven't tested it at all (except
for the queries in this issue).  It should not be considered a viable approach until someone
does more verification...

> 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: d3257_doNOTCommit.patch, 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.


Mime
View raw message