Select statement with subqueries with group by and count distinct statements
returns wrong number of results
------------------------------------------------------------------------------------------------------------
Key: DERBY-5584
URL: https://issues.apache.org/jira/browse/DERBY-5584
Project: Derby
Issue Type: Bug
Components: Network Server
Affects Versions: 10.7.1.1
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)
Reporter: Piotr Zgadzaj
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
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators:
https://issues.apache.org/jira/secure/ContactAdministrators!default.jspa
For more information on JIRA, see: http://www.atlassian.com/software/jira
