db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Bryan Pendleton (Updated) (JIRA)" <j...@apache.org>
Subject [jira] [Updated] (DERBY-5584) Select statement with subqueries with group by and count distinct statements returns wrong number of results
Date Wed, 25 Jan 2012 14:56:40 GMT

     [ https://issues.apache.org/jira/browse/DERBY-5584?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]

Bryan Pendleton updated DERBY-5584:
-----------------------------------

    Attachment: try1.txt

Attached 'try1.txt' contains a patch which alters the behavior
of this particular test case.

I haven't run any of the existing tests, nor have I written any
new tests; I just attached this patch to record the progress I've
made so far.

I'm not sure where to find more examples of DISTINCT
aggregates. I recall looking for such examples years ago, when
I was first working on GROUP BY, and didn't find many. Does
anybody have any good ideas about where to find more
DISTINCT aggregate queries that we could include in our
test suites?

                
> 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
>            Assignee: Bryan Pendleton
>         Attachments: query.log, try1.txt
>
>
> 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