[DERBY-5584] Select statement with subqueries with group by and count distinct statements returns wrong number of results

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 10.6.2.4, 10.7.1.1
• Fix Version/s: 10.8.3.0, 10.9.1.0
• Component/s: SQL
• Labels: derby_backport_reject_10_5 derby_triage10_9

Environment

Output from sysinfo
java.specification.name: Java Platform API Specification
java.specification.version: 1.6
java.runtime.version: 1.6.0_20-b02
--------- Derby Information --------
JRE - JDBC: Java SE 6 - JDBC 4.0
[/home/piotrz/Desktop/db-derby-10.7.1.1-bin/lib/derby.jar] 10.7.1.1 - (1040133)
[/home/piotrz/Desktop/db-derby-10.7.1.1-bin/lib/derbytools.jar] 10.7.1.1 - (1040133)
[/home/piotrz/Desktop/db-derby-10.7.1.1-bin/lib/derbynet.jar] 10.7.1.1 - (1040133)
[/home/piotrz/Desktop/db-derby-10.7.1.1-bin/lib/derbyclient.jar] 10.7.1.1 - (1040133)
------------------------------------------------------
----------------- Locale Information -----------------
Current Locale : [English/United States [en_US]]
Found support for locale: [cs]
version: 10.7.1.1 - (1040133)
Found support for locale: [de_DE]
version: 10.7.1.1 - (1040133)
Found support for locale: [es]
version: 10.7.1.1 - (1040133)
Found support for locale: [fr]
version: 10.7.1.1 - (1040133)
Found support for locale: [hu]
version: 10.7.1.1 - (1040133)
Found support for locale: [it]
version: 10.7.1.1 - (1040133)
Found support for locale: [ja_JP]
version: 10.7.1.1 - (1040133)
Found support for locale: [ko_KR]
version: 10.7.1.1 - (1040133)
Found support for locale: [pl]
version: 10.7.1.1 - (1040133)
Found support for locale: [pt_BR]
version: 10.7.1.1 - (1040133)
Found support for locale: [ru]
version: 10.7.1.1 - (1040133)
Found support for locale: [zh_CN]
version: 10.7.1.1 - (1040133)
Found support for locale: [zh_TW]
version: 10.7.1.1 - (1040133)

• Urgency: Urgent
• Issue & fix info: High Value Fix, Repro attached
• Bug behavior facts: Deviation from standard, Regression, Wrong query result
• Assignee: Bryan Pendleton
• Reporter: Piotr Zgadzaj
• Created: 23/Jan/12 13:44
• Updated: 15/Nov/13 08:15
• Resolved: 22/Feb/12 06:07

Description

Steps to reproduce:

1. Create database, connect to database with any JDBC client

2. create two tables:

CREATE TABLE TEST_5 (
profile_id INTEGER NOT NULL,
group_ref INTEGER NOT NULL,
matched_count INTEGER NOT NULL
);

CREATE TABLE TEST_6 (
profile_id INTEGER NOT NULL,
group_ref INTEGER NOT NULL,
matched_count INTEGER NOT NULL
);

3. Insert two records for each table:

insert into test_5 values (1, 10000,1);
insert into test_5 values (2, 10000, 2);

insert into test_6 values (1, 10000,1);
insert into test_6 values (2, 10000, 2);

4. Run following statement

SELECT *
FROM
(SELECT ps1.group_ref,
COUNT(DISTINCT ps1.matched_count) AS matched_count
FROM test_5 ps1
GROUP BY ps1.group_ref,
ps1.profile_id
) a,
(SELECT ps2.group_ref,
COUNT( DISTINCT ps2.matched_count) AS matched_count
FROM test_6 ps2
GROUP BY ps2.group_ref,
ps2.profile_id
) b

As a result I've got 3 records instead of 4 - at least Oracle 10g
returns 4 records for this statement. Maybe i'm doing something wrong.
Do you have any suggestions / possible workarounds for this problem

Issue Links

• relates to: DERBY-3002 Add support for GROUP BY ROLLUP