[#DERBY-3257] SELECT with HAVING clause containing OR conditional incorrectly return 1 row - should return 2 rows - works correctly with 10.2 DB

Derby

SELECT with HAVING clause containing OR conditional incorrectly return 1 row - should return 2 rows - works correctly with 10.2 DB

Created: 06/Dec/07 12:57 AM Updated: 30/Jun/09 03:55 PM

Return to search

Component/s:

SQL

Affects Version/s:

10.3.1.4, 10.3.2.1, 10.4.1.3

Fix Version/s:

10.3.3.0, 10.4.1.3

Time Tracking:

Not Specified

File Attachments:

File Name	Date Attached	Attached By	Size
42X24_error.sql	2008-01-18 03:16 AM	Kathey Marsden	0.4 kB
d3257_doNOTCommit.patch	2008-01-17 11:50 PM	A B	3 kB
derby-3257_diff.txt	2008-01-19 11:13 PM	Kathey Marsden	8 kB
derby-3257_diff2.txt	2008-01-21 07:18 PM	Kathey Marsden	8 kB
derby-3257_plan_10.2.txt	2008-01-17 07:06 PM	Kathey Marsden	3 kB
derby-3257_plan_10.4.txt	2008-01-17 07:06 PM	Kathey Marsden	2 kB
derby-3257_stat.txt	2008-01-19 11:13 PM	Kathey Marsden	0.3 kB
TestHaving.java	2008-01-18 05:26 PM	Kathey Marsden	5 kB

Issue Links:

Incorporates

This issue is part of:

~~DERBY-2034~~ Tracking of bugs that lead to incorrect results being stored or returned to the client

Reference

This issue is related to:

~~DERBY-681~~ Eliminate the parser's rewriting of the abstract syntax tree for queries with GROUP BY and/or HAVING clauses

Bug behavior facts:	Regression
Resolution Date:	22/Jan/08 08:22 PM

Description

« Hide

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

All

Comments

Work Log

Change History

Subversion Commits

Sort Order:

Repository	Revision	Date	User	Message
ASF	#614017	Mon Jan 21 21:29:52 UTC 2008	kmarsden	~~DERBY-3257~~ SELECT with HAVING clause containing OR conditional incorrectly return 1 row - should return 2 rows - works correctly with 10.2 DB Normalize the havingClause before calling preprocess. This made it necessary to explicitly exclude having subqueries from flattenning, before they were implicitly excluded because the clause was not normalized. Army Brown contributed the main part of the fix to normalize the having clause.
				Files Changed
				MODIFY /db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/SubqueryNode.java MODIFY /db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/SubqueryList.java MODIFY /db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/GroupByTest.java MODIFY /db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/SelectNode.java

Repository	Revision	Date	User	Message
ASF	#614304	Tue Jan 22 20:19:19 UTC 2008	kmarsden	~~DERBY-3257~~ SELECT with HAVING clause containing OR conditional incorrectly return 1 row - should return 2 rows - works correctly with 10.2 DB port revision 614017 from trunk
				Files Changed
				MODIFY /db/derby/code/branches/10.3/java/engine/org/apache/derby/impl/sql/compile/SubqueryNode.java MODIFY /db/derby/code/branches/10.3/java/engine/org/apache/derby/impl/sql/compile/SubqueryList.java MODIFY /db/derby/code/branches/10.3/java/testing/org/apache/derbyTesting/functionTests/tests/lang/GroupByTest.java MODIFY /db/derby/code/branches/10.3/java/engine/org/apache/derby/impl/sql/compile/SelectNode.java