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 "Artur Kuś (JIRA)" <ji...@apache.org> on 2008/04/11 15:30:07 UTC
[jira] Created: (DERBY-3613) SELECT DISTINCT field FROM TABLE_NAME
GROUP BY field, field2
SELECT DISTINCT field FROM TABLE_NAME GROUP BY field, field2
-------------------------------------------------------------
Key: DERBY-3613
URL: https://issues.apache.org/jira/browse/DERBY-3613
Project: Derby
Issue Type: Bug
Affects Versions: 10.3.2.1
Environment: Windows XP
Reporter: Artur Kuś
Query 'SELECT DISTINCT field FROM TABLE_NAME GROUP BY field, field2' not work ok.
Distinct is ignored !!!
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Updated: (DERBY-3613) SELECT DISTINCT field FROM TABLE_NAME
GROUP BY field, field2
Posted by "Bryan Pendleton (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3613?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Bryan Pendleton updated DERBY-3613:
-----------------------------------
Attachment: addCommentAndAnotherTest.diff
Thanks for the review and testing Thomas!
I updated the patch with (a) a short comment on the code change,
and (b) a few other test cases which I created by looking at some
of the other test cases in GroupByTest and adding DISTINCT to them.
The revised patch is attached.
> SELECT DISTINCT field FROM TABLE_NAME GROUP BY field, field2
> -------------------------------------------------------------
>
> Key: DERBY-3613
> URL: https://issues.apache.org/jira/browse/DERBY-3613
> Project: Derby
> Issue Type: Bug
> Affects Versions: 10.3.2.1
> Environment: Windows XP
> Reporter: Artur Kuś
> Assignee: Bryan Pendleton
> Attachments: addCommentAndAnotherTest.diff, dontGenerateForDistinct.diff
>
>
> Query 'SELECT DISTINCT field FROM TABLE_NAME GROUP BY field, field2' not work ok.
> Distinct is ignored !!!
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-3613) SELECT DISTINCT field FROM
TABLE_NAME GROUP BY field, field2
Posted by "Rick Hillegas (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3613?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12620378#action_12620378 ]
Rick Hillegas commented on DERBY-3613:
--------------------------------------
Does this issue need a release note? With this fix, a family of queries will now return different results.
> SELECT DISTINCT field FROM TABLE_NAME GROUP BY field, field2
> -------------------------------------------------------------
>
> Key: DERBY-3613
> URL: https://issues.apache.org/jira/browse/DERBY-3613
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.3.2.1
> Environment: Windows XP
> Reporter: Artur Kuś
> Assignee: Bryan Pendleton
> Fix For: 10.4.2.0, 10.5.0.0
>
> Attachments: addCommentAndAnotherTest.diff, dontGenerateForDistinct.diff, tenThree.diff
>
>
> Query 'SELECT DISTINCT field FROM TABLE_NAME GROUP BY field, field2' not work ok.
> Distinct is ignored !!!
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Updated: (DERBY-3613) SELECT DISTINCT field FROM TABLE_NAME
GROUP BY field, field2
Posted by "Bryan Pendleton (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3613?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Bryan Pendleton updated DERBY-3613:
-----------------------------------
Derby Info: [Patch Available]
> SELECT DISTINCT field FROM TABLE_NAME GROUP BY field, field2
> -------------------------------------------------------------
>
> Key: DERBY-3613
> URL: https://issues.apache.org/jira/browse/DERBY-3613
> Project: Derby
> Issue Type: Bug
> Affects Versions: 10.3.2.1
> Environment: Windows XP
> Reporter: Artur Kuś
> Assignee: Bryan Pendleton
> Attachments: dontGenerateForDistinct.diff
>
>
> Query 'SELECT DISTINCT field FROM TABLE_NAME GROUP BY field, field2' not work ok.
> Distinct is ignored !!!
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Updated: (DERBY-3613) SELECT DISTINCT field FROM TABLE_NAME
GROUP BY field, field2
Posted by "Bryan Pendleton (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3613?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Bryan Pendleton updated DERBY-3613:
-----------------------------------
Attachment: tenThree.diff
GroupByTest.java is quite different in 10.3, making it hard to do an automated merge.
Attached file tenThree.diff is the patch I intend to submit to 10.3, if testing goes well.
> SELECT DISTINCT field FROM TABLE_NAME GROUP BY field, field2
> -------------------------------------------------------------
>
> Key: DERBY-3613
> URL: https://issues.apache.org/jira/browse/DERBY-3613
> Project: Derby
> Issue Type: Bug
> Affects Versions: 10.3.2.1
> Environment: Windows XP
> Reporter: Artur Kuś
> Assignee: Bryan Pendleton
> Attachments: addCommentAndAnotherTest.diff, dontGenerateForDistinct.diff, tenThree.diff
>
>
> Query 'SELECT DISTINCT field FROM TABLE_NAME GROUP BY field, field2' not work ok.
> Distinct is ignored !!!
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-3613) SELECT DISTINCT field FROM
TABLE_NAME GROUP BY field, field2
Posted by "Bryan Pendleton (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3613?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12591513#action_12591513 ]
Bryan Pendleton commented on DERBY-3613:
----------------------------------------
Committed addCommentAndAnotherTest.diff to the trunk as revision 650728.
I'll investigate merging this patch back to the 10.4 and 10.3 lines.
> SELECT DISTINCT field FROM TABLE_NAME GROUP BY field, field2
> -------------------------------------------------------------
>
> Key: DERBY-3613
> URL: https://issues.apache.org/jira/browse/DERBY-3613
> Project: Derby
> Issue Type: Bug
> Affects Versions: 10.3.2.1
> Environment: Windows XP
> Reporter: Artur Kuś
> Assignee: Bryan Pendleton
> Attachments: addCommentAndAnotherTest.diff, dontGenerateForDistinct.diff
>
>
> Query 'SELECT DISTINCT field FROM TABLE_NAME GROUP BY field, field2' not work ok.
> Distinct is ignored !!!
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-3613) SELECT DISTINCT field FROM
TABLE_NAME GROUP BY field, field2
Posted by "Thomas Nielsen (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3613?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12591224#action_12591224 ]
Thomas Nielsen commented on DERBY-3613:
---------------------------------------
The approach taken in the proposed patch looks good. Test changes seem to cover the failing scenarios well.
Running a few tests on the patch now, but unless they show any problems I'm +1 to commit.
> SELECT DISTINCT field FROM TABLE_NAME GROUP BY field, field2
> -------------------------------------------------------------
>
> Key: DERBY-3613
> URL: https://issues.apache.org/jira/browse/DERBY-3613
> Project: Derby
> Issue Type: Bug
> Affects Versions: 10.3.2.1
> Environment: Windows XP
> Reporter: Artur Kuś
> Assignee: Bryan Pendleton
> Attachments: dontGenerateForDistinct.diff
>
>
> Query 'SELECT DISTINCT field FROM TABLE_NAME GROUP BY field, field2' not work ok.
> Distinct is ignored !!!
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Updated: (DERBY-3613) SELECT DISTINCT field FROM TABLE_NAME
GROUP BY field, field2
Posted by "Kathey Marsden (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3613?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Kathey Marsden updated DERBY-3613:
----------------------------------
Component/s: SQL
> SELECT DISTINCT field FROM TABLE_NAME GROUP BY field, field2
> -------------------------------------------------------------
>
> Key: DERBY-3613
> URL: https://issues.apache.org/jira/browse/DERBY-3613
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.3.2.1
> Environment: Windows XP
> Reporter: Artur Kuś
> Assignee: Bryan Pendleton
> Fix For: 10.3.3.0, 10.4.1.4, 10.5.0.0
>
> Attachments: addCommentAndAnotherTest.diff, dontGenerateForDistinct.diff, tenThree.diff
>
>
> Query 'SELECT DISTINCT field FROM TABLE_NAME GROUP BY field, field2' not work ok.
> Distinct is ignored !!!
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Assigned: (DERBY-3613) SELECT DISTINCT field FROM TABLE_NAME
GROUP BY field, field2
Posted by "Bryan Pendleton (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3613?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Bryan Pendleton reassigned DERBY-3613:
--------------------------------------
Assignee: Bryan Pendleton
> SELECT DISTINCT field FROM TABLE_NAME GROUP BY field, field2
> -------------------------------------------------------------
>
> Key: DERBY-3613
> URL: https://issues.apache.org/jira/browse/DERBY-3613
> Project: Derby
> Issue Type: Bug
> Affects Versions: 10.3.2.1
> Environment: Windows XP
> Reporter: Artur Kuś
> Assignee: Bryan Pendleton
>
> Query 'SELECT DISTINCT field FROM TABLE_NAME GROUP BY field, field2' not work ok.
> Distinct is ignored !!!
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-3613) SELECT DISTINCT field FROM
TABLE_NAME GROUP BY field, field2
Posted by "Artur Kuś (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3613?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12588001#action_12588001 ]
Artur Kuś commented on DERBY-3613:
----------------------------------
Simple :
CREATE TABLE tabla_name(field varchar(10), field2 varchar(10 ));
INSERT INTO tabla_name VALUES('first', 'first');
INSERT INTO tabla_name VALUES('first', 'second');
SELECT DISTINCT field FROM tabla_name GROUP BY field, field2.
I expect only one 1 row, but I get two rows.
Why??
> SELECT DISTINCT field FROM TABLE_NAME GROUP BY field, field2
> -------------------------------------------------------------
>
> Key: DERBY-3613
> URL: https://issues.apache.org/jira/browse/DERBY-3613
> Project: Derby
> Issue Type: Bug
> Affects Versions: 10.3.2.1
> Environment: Windows XP
> Reporter: Artur Kuś
>
> Query 'SELECT DISTINCT field FROM TABLE_NAME GROUP BY field, field2' not work ok.
> Distinct is ignored !!!
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-3613) SELECT DISTINCT field FROM
TABLE_NAME GROUP BY field, field2
Posted by "Knut Anders Hatlen (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3613?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12588565#action_12588565 ]
Knut Anders Hatlen commented on DERBY-3613:
-------------------------------------------
I tried the query with different versions of Derby:
10.0.1.2: two rows
10.1.1.0: two rows
10.1.2.1: one row
10.2.1.6: one row
10.2.2.0: one row
10.3.1.4: two rows
10.3.2.1: two rows
10.4.1.1 (RC): two rows
So, given that one row is the correct result, the bug was fixed at some point, but it regressed back later.
> SELECT DISTINCT field FROM TABLE_NAME GROUP BY field, field2
> -------------------------------------------------------------
>
> Key: DERBY-3613
> URL: https://issues.apache.org/jira/browse/DERBY-3613
> Project: Derby
> Issue Type: Bug
> Affects Versions: 10.3.2.1
> Environment: Windows XP
> Reporter: Artur Kuś
>
> Query 'SELECT DISTINCT field FROM TABLE_NAME GROUP BY field, field2' not work ok.
> Distinct is ignored !!!
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-3613) SELECT DISTINCT field FROM
TABLE_NAME GROUP BY field, field2
Posted by "Knut Anders Hatlen (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3613?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12588600#action_12588600 ]
Knut Anders Hatlen commented on DERBY-3613:
-------------------------------------------
The first change (two rows --> one row) happened in this commit:
------------------------------------------------------------------------
r267239 | bandaram | 2005-09-02 20:07:39 +0200 (Fri, 02 Sep 2005) | 6 lines
DERBY-504: Disable pushing down DISTINCT when number of columns from the parent query and the subquery don't match. Can lead to wrong results.
Thanks to Rick for persistant reviews and running the tests.
Submitted by Knut Anders Hatlen (Knut.Hatlen@Sun.COM)
------------------------------------------------------------------------
The old behaviour (one row --> two rows) was reintroduced in this commit:
------------------------------------------------------------------------
r516454 | abrown | 2007-03-09 17:37:20 +0100 (Fri, 09 Mar 2007) | 8 lines
DERBY-681: Remove the "wrap group by's in a subselect" rewrite in the parser.
This patch preserves the having clause through bind and optimize phases and
then, during the final rewrite for aggregates in the GroupByNode, it transforms
the having clause to a valid restriction. See text file attached to the Jira
for more information.
Contributed by Manish Khettry (manish_khettry@yahoo.com)
------------------------------------------------------------------------
> SELECT DISTINCT field FROM TABLE_NAME GROUP BY field, field2
> -------------------------------------------------------------
>
> Key: DERBY-3613
> URL: https://issues.apache.org/jira/browse/DERBY-3613
> Project: Derby
> Issue Type: Bug
> Affects Versions: 10.3.2.1
> Environment: Windows XP
> Reporter: Artur Kuś
>
> Query 'SELECT DISTINCT field FROM TABLE_NAME GROUP BY field, field2' not work ok.
> Distinct is ignored !!!
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-3613) SELECT DISTINCT field FROM
TABLE_NAME GROUP BY field, field2
Posted by "Artur Kuś (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3613?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12588503#action_12588503 ]
Artur Kuś commented on DERBY-3613:
----------------------------------
I starting from the page
http://en.wikipedia.org/wiki/SQL
and next http://en.wikipedia.org/wiki/SQL-92
and next open the page http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt.
In the specyfication I found that
"If DISTINCT is specified, then let TXA be the result of elimi-
nating redundant duplicate values from TX. Otherwise, let TXA be
TX."
So, in JavaDB is not ok :)
> SELECT DISTINCT field FROM TABLE_NAME GROUP BY field, field2
> -------------------------------------------------------------
>
> Key: DERBY-3613
> URL: https://issues.apache.org/jira/browse/DERBY-3613
> Project: Derby
> Issue Type: Bug
> Affects Versions: 10.3.2.1
> Environment: Windows XP
> Reporter: Artur Kuś
>
> Query 'SELECT DISTINCT field FROM TABLE_NAME GROUP BY field, field2' not work ok.
> Distinct is ignored !!!
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Updated: (DERBY-3613) SELECT DISTINCT field FROM TABLE_NAME
GROUP BY field, field2
Posted by "Bryan Pendleton (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3613?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Bryan Pendleton updated DERBY-3613:
-----------------------------------
Attachment: dontGenerateForDistinct.diff
Attached is 'dontGenerateForDistinct.diff', a patch proposal.
This patch modifies the processing of GROUP BY columns
which are "generated" into the select's result column list.
Specifically, the patch causes columns to be generated
into the select RCL only if the select does *not* specify DISTINCT.
If the select specifies DISTINCT, we don't want to include
any additional columns into the RCL because we want to
be sure that we only perform DISTINCT processing on
the columns that were specified by the user.
The patch also includes some additional test cases, based
on the repro case in the issue description.
I ran a complete set of regression tests with the modified code
and it passed all the existing tests, as well as the new tests
added by this patch.
Please have a look and let me know what you think.
> SELECT DISTINCT field FROM TABLE_NAME GROUP BY field, field2
> -------------------------------------------------------------
>
> Key: DERBY-3613
> URL: https://issues.apache.org/jira/browse/DERBY-3613
> Project: Derby
> Issue Type: Bug
> Affects Versions: 10.3.2.1
> Environment: Windows XP
> Reporter: Artur Kuś
> Assignee: Bryan Pendleton
> Attachments: dontGenerateForDistinct.diff
>
>
> Query 'SELECT DISTINCT field FROM TABLE_NAME GROUP BY field, field2' not work ok.
> Distinct is ignored !!!
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-3613) SELECT DISTINCT field FROM
TABLE_NAME GROUP BY field, field2
Posted by "Artur Kuś (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3613?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12588004#action_12588004 ]
Artur Kuś commented on DERBY-3613:
----------------------------------
Thanks.
It is very funny.
Oracle, MSDE and Postgres returns only one rows. :)
JavaDB is worse ??
> SELECT DISTINCT field FROM TABLE_NAME GROUP BY field, field2
> -------------------------------------------------------------
>
> Key: DERBY-3613
> URL: https://issues.apache.org/jira/browse/DERBY-3613
> Project: Derby
> Issue Type: Bug
> Affects Versions: 10.3.2.1
> Environment: Windows XP
> Reporter: Artur Kuś
>
> Query 'SELECT DISTINCT field FROM TABLE_NAME GROUP BY field, field2' not work ok.
> Distinct is ignored !!!
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-3613) SELECT DISTINCT field FROM
TABLE_NAME GROUP BY field, field2
Posted by "Thomas Nielsen (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3613?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12591268#action_12591268 ]
Thomas Nielsen commented on DERBY-3613:
---------------------------------------
No errors seen in my testing - so +1 for commit.
> SELECT DISTINCT field FROM TABLE_NAME GROUP BY field, field2
> -------------------------------------------------------------
>
> Key: DERBY-3613
> URL: https://issues.apache.org/jira/browse/DERBY-3613
> Project: Derby
> Issue Type: Bug
> Affects Versions: 10.3.2.1
> Environment: Windows XP
> Reporter: Artur Kuś
> Assignee: Bryan Pendleton
> Attachments: dontGenerateForDistinct.diff
>
>
> Query 'SELECT DISTINCT field FROM TABLE_NAME GROUP BY field, field2' not work ok.
> Distinct is ignored !!!
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-3613) SELECT DISTINCT field FROM
TABLE_NAME GROUP BY field, field2
Posted by "Bryan Pendleton (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3613?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12588007#action_12588007 ]
Bryan Pendleton commented on DERBY-3613:
----------------------------------------
Derby might be interpreting the standards incorrectly here, I don't know.
Do you have access to the SQL standard to help us understand what
the specified behavior should be?
> SELECT DISTINCT field FROM TABLE_NAME GROUP BY field, field2
> -------------------------------------------------------------
>
> Key: DERBY-3613
> URL: https://issues.apache.org/jira/browse/DERBY-3613
> Project: Derby
> Issue Type: Bug
> Affects Versions: 10.3.2.1
> Environment: Windows XP
> Reporter: Artur Kuś
>
> Query 'SELECT DISTINCT field FROM TABLE_NAME GROUP BY field, field2' not work ok.
> Distinct is ignored !!!
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-3613) SELECT DISTINCT field FROM
TABLE_NAME GROUP BY field, field2
Posted by "Bryan Pendleton (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3613?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12592080#action_12592080 ]
Bryan Pendleton commented on DERBY-3613:
----------------------------------------
Merged the trunk change to the 10.4 branch as revision 651275.
> SELECT DISTINCT field FROM TABLE_NAME GROUP BY field, field2
> -------------------------------------------------------------
>
> Key: DERBY-3613
> URL: https://issues.apache.org/jira/browse/DERBY-3613
> Project: Derby
> Issue Type: Bug
> Affects Versions: 10.3.2.1
> Environment: Windows XP
> Reporter: Artur Kuś
> Assignee: Bryan Pendleton
> Attachments: addCommentAndAnotherTest.diff, dontGenerateForDistinct.diff
>
>
> Query 'SELECT DISTINCT field FROM TABLE_NAME GROUP BY field, field2' not work ok.
> Distinct is ignored !!!
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Resolved: (DERBY-3613) SELECT DISTINCT field FROM TABLE_NAME
GROUP BY field, field2
Posted by "Bryan Pendleton (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3613?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Bryan Pendleton resolved DERBY-3613.
------------------------------------
Resolution: Fixed
Fix Version/s: 10.5.0.0
10.4.1.4
10.3.2.2
Derby Info: (was: [Patch Available])
Committed tenThree.diff to the 10.3 branch as revision 651612.
Marking the issue as resolved. Please confirm that the fix is working,
and close the issue if the problem is gone. Thanks for helping us
find this problem!
> SELECT DISTINCT field FROM TABLE_NAME GROUP BY field, field2
> -------------------------------------------------------------
>
> Key: DERBY-3613
> URL: https://issues.apache.org/jira/browse/DERBY-3613
> Project: Derby
> Issue Type: Bug
> Affects Versions: 10.3.2.1
> Environment: Windows XP
> Reporter: Artur Kuś
> Assignee: Bryan Pendleton
> Fix For: 10.3.2.2, 10.4.1.4, 10.5.0.0
>
> Attachments: addCommentAndAnotherTest.diff, dontGenerateForDistinct.diff, tenThree.diff
>
>
> Query 'SELECT DISTINCT field FROM TABLE_NAME GROUP BY field, field2' not work ok.
> Distinct is ignored !!!
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-3613) SELECT DISTINCT field FROM
TABLE_NAME GROUP BY field, field2
Posted by "Bryan Pendleton (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3613?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12588003#action_12588003 ]
Bryan Pendleton commented on DERBY-3613:
----------------------------------------
I believe you get two rows because the results have been partitioned
into two groups, as you specified in the GROUP BY.
Within each group you are getting only one distinct value of 'field',
but there are two groups total.
> SELECT DISTINCT field FROM TABLE_NAME GROUP BY field, field2
> -------------------------------------------------------------
>
> Key: DERBY-3613
> URL: https://issues.apache.org/jira/browse/DERBY-3613
> Project: Derby
> Issue Type: Bug
> Affects Versions: 10.3.2.1
> Environment: Windows XP
> Reporter: Artur Kuś
>
> Query 'SELECT DISTINCT field FROM TABLE_NAME GROUP BY field, field2' not work ok.
> Distinct is ignored !!!
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-3613) SELECT DISTINCT field FROM
TABLE_NAME GROUP BY field, field2
Posted by "Bryan Pendleton (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3613?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12590731#action_12590731 ]
Bryan Pendleton commented on DERBY-3613:
----------------------------------------
This page suggests that certain versions of Sybase may have similar behavior to Derby:
http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.help.ase_15.0.sqlug/html/sqlug/sqlug126.htm
It seems like Derby could recognize that:
select distinct a from t group by a, b
is identical to
select distinct a from t group by a
I can think of 3 possible ways to give Derby this behavior:
1) When processing GROUP by columns, and deciding whether to pull them up
into the result column list as generated columns, detect this particular case and
don't pull up the generated GROUP BY column "b" into the result list.
2) In the compilation process, prior to generating the distinct scan result set,
detect that un-necessary column "b" has been included into the result column list
and skip it when generating the data structures for execution.
3) At execution time, when setting up the sorter, recognize that column "b" isn't
needed in the sort key, and only sort/collapse the records on column "a".
It seems best to detect this situation earlier rather than later, so I'm partial
to solutions which can solve the problem at compilation time, not execution time.
> SELECT DISTINCT field FROM TABLE_NAME GROUP BY field, field2
> -------------------------------------------------------------
>
> Key: DERBY-3613
> URL: https://issues.apache.org/jira/browse/DERBY-3613
> Project: Derby
> Issue Type: Bug
> Affects Versions: 10.3.2.1
> Environment: Windows XP
> Reporter: Artur Kuś
>
> Query 'SELECT DISTINCT field FROM TABLE_NAME GROUP BY field, field2' not work ok.
> Distinct is ignored !!!
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-3613) SELECT DISTINCT field FROM
TABLE_NAME GROUP BY field, field2
Posted by "Bryan Pendleton (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3613?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12587984#action_12587984 ]
Bryan Pendleton commented on DERBY-3613:
----------------------------------------
Can you provide an example SQL script, perhaps a captured IJ session,
which shows the specific tables, statements, and data you are using,
the results you get, and the results you expect?
> SELECT DISTINCT field FROM TABLE_NAME GROUP BY field, field2
> -------------------------------------------------------------
>
> Key: DERBY-3613
> URL: https://issues.apache.org/jira/browse/DERBY-3613
> Project: Derby
> Issue Type: Bug
> Affects Versions: 10.3.2.1
> Environment: Windows XP
> Reporter: Artur Kuś
>
> Query 'SELECT DISTINCT field FROM TABLE_NAME GROUP BY field, field2' not work ok.
> Distinct is ignored !!!
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.