[
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.
