You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-dev@db.apache.org by "Bryan Pendleton (Resolved) (JIRA)" <ji...@apache.org> on 2012/02/22 07:08:48 UTC
[jira] [Resolved] (DERBY-5584) Select statement with subqueries
with group by and count distinct statements returns wrong number of results
[ https://issues.apache.org/jira/browse/DERBY-5584?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Bryan Pendleton resolved DERBY-5584.
------------------------------------
Resolution: Fixed
Fix Version/s: 10.9.0.0
Issue & fix info: High Value Fix,Repro attached (was: Repro attached,Patch Available,High Value Fix)
Thanks all for the reviews and suggestions and assistance running tests
and preparing the patch. It's been very helpful, especially as I don't have
so much time available to work on Derby nowadays.
Committed the patch to the trunk as revision 1292134.
> 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: SQL
> 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
> Labels: derby_triage10_9
> Fix For: 10.9.0.0
>
> Attachments: patch1.txt, patch2.txt, query.log, tests.out, tests.sql, 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