db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Piotr Zgadzaj (Created) (JIRA)" <j...@apache.org>
Subject [jira] [Created] (DERBY-5584) Select statement with subqueries with group by and count distinct statements returns wrong number of results
Date Mon, 23 Jan 2012 13:44:41 GMT
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

        

Mime
View raw message