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 "Bernt M. Johnsen (JIRA)" <de...@db.apache.org> on 2005/05/13 10:08:14 UTC
[jira] Created: (DERBY-280) Wrong result from select when aliasing to same name as used in group by
Wrong result from select when aliasing to same name as used in group by
-----------------------------------------------------------------------
Key: DERBY-280
URL: http://issues.apache.org/jira/browse/DERBY-280
Project: Derby
Type: Bug
Components: SQL
Reporter: Bernt M. Johnsen
Priority: Minor
Wrong result from select when aliasing to same name as used in group by. Example:
If we have the following table:
ij> select * from tt;
I |J
-----------------------
1 |2
2 |3
1 |2
2 |3
2 |3
5 rows selected
The following select is ok:
ij> select i, count(*) as cnt from tt group by i;
I |CNT
-----------------------
1 |2
2 |3
2 rows selected
But this one returns wrong result in the aliased column:
ij> select i, count(*) as i from tt group by i;
I |I
-----------------------
1 |1
2 |2
2 rows selected
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
Re: [jira] Commented: (DERBY-280) Wrong result from select when aliasing to same name as used in group by
Posted by Francois Orsini <fr...@gmail.com>.
There was a reported of a possible NPE in a particular code path and
condition which Satheesh identified - I remember Rick fixing it and
re-running the tests...
On 11/30/05, Kathey Marsden (JIRA) <de...@db.apache.org> wrote:
>
> [
> http://issues.apache.org/jira/browse/DERBY-280?page=comments#action_12358945]
>
> Kathey Marsden commented on DERBY-280:
> --------------------------------------
>
> In an earlier comment you mentioned a valid query which worked before the
> patch which now fails with:
>
> ij> select a+1 as a, a+1 as a from bug280 group by a;
> ERROR X0A00: The select list mentions column 'A' twice. This is not
> allowed in queries with GROUP BY or HAVING clauses. Try aliasing one of the
> conflicting columns to a unique name.
>
>
> There was a discussion of whether this new failure was worth introducing
> given the seriousness of returning wrong results. A choice of the lesser
> of evils I thought, regress an edge case rather than return wrong results.
> But I might have been mistaken.
>
>
>
>
> > Wrong result from select when aliasing to same name as used in group by
> > -----------------------------------------------------------------------
> >
> > Key: DERBY-280
> > URL: http://issues.apache.org/jira/browse/DERBY-280
> > Project: Derby
> > Type: Bug
> > Components: SQL
> > Reporter: Bernt M. Johnsen
> > Assignee: Rick Hillegas
> > Priority: Minor
> > Attachments: bug280.diff
> >
> > Wrong result from select when aliasing to same name as used in group by.
> Example:
> > If we have the following table:
> > ij> select * from tt;
> > I |J
> > -----------------------
> > 1 |2
> > 2 |3
> > 1 |2
> > 2 |3
> > 2 |3
> >
> > 5 rows selected
> > The following select is ok:
> > ij> select i, count(*) as cnt from tt group by i;
> > I |CNT
> > -----------------------
> > 1 |2
> > 2 |3
> >
> > 2 rows selected
> > But this one returns wrong result in the aliased column:
> > ij> select i, count(*) as i from tt group by i;
> > I |I
> > -----------------------
> > 1 |1
> > 2 |2
> >
> > 2 rows selected
> >
>
> --
> This message is automatically generated by JIRA.
> -
> If you think it was sent incorrectly contact one of the administrators:
> http://issues.apache.org/jira/secure/Administrators.jspa
> -
> For more information on JIRA, see:
> http://www.atlassian.com/software/jira
>
>
[jira] Commented: (DERBY-280) Wrong result from select when aliasing to same name as used in group by
Posted by "Rick Hillegas (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-280?page=comments#action_12356646 ]
Rick Hillegas commented on DERBY-280:
-------------------------------------
>From my reading of Part 2 of the ANSI spec, this syntax should be disallowed:
select i, count(*) as i from tt group by i;
This query gives rise to a table whose row type contains two fields with the same name. I believe this is illegal. Here is my reasoning:
1) According to syntax rule 2 in section 7.12, a <query specification> gives rise to a table.
2) According to section 4.14.1, all rows of a table have the same row type.
3) According to section 4.13, two fields in the same row type may not have the same name.
I propose to raise a syntax error in the parser when a <query specification> gives rise to a table with two identically named columns.
> Wrong result from select when aliasing to same name as used in group by
> -----------------------------------------------------------------------
>
> Key: DERBY-280
> URL: http://issues.apache.org/jira/browse/DERBY-280
> Project: Derby
> Type: Bug
> Components: SQL
> Reporter: Bernt M. Johnsen
> Assignee: Rick Hillegas
> Priority: Minor
>
> Wrong result from select when aliasing to same name as used in group by. Example:
> If we have the following table:
> ij> select * from tt;
> I |J
> -----------------------
> 1 |2
> 2 |3
> 1 |2
> 2 |3
> 2 |3
>
> 5 rows selected
> The following select is ok:
> ij> select i, count(*) as cnt from tt group by i;
> I |CNT
> -----------------------
> 1 |2
> 2 |3
>
> 2 rows selected
> But this one returns wrong result in the aliased column:
> ij> select i, count(*) as i from tt group by i;
> I |I
> -----------------------
> 1 |1
> 2 |2
>
> 2 rows selected
>
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
[jira] Commented: (DERBY-280) Wrong result from select when aliasing to same name as used in group by
Posted by "Francois Orsini (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-280?page=comments#action_12356678 ]
Francois Orsini commented on DERBY-280:
---------------------------------------
Would it help to allow a numeric expression in the GROUP BY clause to (force) indicate which column to use in the result table? The leftmost column being 1 obviously.
> Wrong result from select when aliasing to same name as used in group by
> -----------------------------------------------------------------------
>
> Key: DERBY-280
> URL: http://issues.apache.org/jira/browse/DERBY-280
> Project: Derby
> Type: Bug
> Components: SQL
> Reporter: Bernt M. Johnsen
> Assignee: Rick Hillegas
> Priority: Minor
>
> Wrong result from select when aliasing to same name as used in group by. Example:
> If we have the following table:
> ij> select * from tt;
> I |J
> -----------------------
> 1 |2
> 2 |3
> 1 |2
> 2 |3
> 2 |3
>
> 5 rows selected
> The following select is ok:
> ij> select i, count(*) as cnt from tt group by i;
> I |CNT
> -----------------------
> 1 |2
> 2 |3
>
> 2 rows selected
> But this one returns wrong result in the aliased column:
> ij> select i, count(*) as i from tt group by i;
> I |I
> -----------------------
> 1 |1
> 2 |2
>
> 2 rows selected
>
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
[jira] Commented: (DERBY-280) Wrong result from select when aliasing to same name as used in group by
Posted by "Satheesh Bandaram (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-280?page=comments#action_12356660 ]
Satheesh Bandaram commented on DERBY-280:
-----------------------------------------
I agree... I was just doing research to find all places where Derby does handle identical names. JDBC spec actually specifies how to handle the cases of multiple identical names.
Also I have seen several postings of user queries that have identical names.
> Wrong result from select when aliasing to same name as used in group by
> -----------------------------------------------------------------------
>
> Key: DERBY-280
> URL: http://issues.apache.org/jira/browse/DERBY-280
> Project: Derby
> Type: Bug
> Components: SQL
> Reporter: Bernt M. Johnsen
> Assignee: Rick Hillegas
> Priority: Minor
>
> Wrong result from select when aliasing to same name as used in group by. Example:
> If we have the following table:
> ij> select * from tt;
> I |J
> -----------------------
> 1 |2
> 2 |3
> 1 |2
> 2 |3
> 2 |3
>
> 5 rows selected
> The following select is ok:
> ij> select i, count(*) as cnt from tt group by i;
> I |CNT
> -----------------------
> 1 |2
> 2 |3
>
> 2 rows selected
> But this one returns wrong result in the aliased column:
> ij> select i, count(*) as i from tt group by i;
> I |I
> -----------------------
> 1 |1
> 2 |2
>
> 2 rows selected
>
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
[jira] Commented: (DERBY-280) Wrong result from select when aliasing to same name as used in group by
Posted by "Kathey Marsden (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-280?page=comments#action_12357377 ]
Kathey Marsden commented on DERBY-280:
--------------------------------------
I t hink it would be important to talk about this in the Release Notes. I really like the error message because it tells users exactly what is wrong and what to do.
> Wrong result from select when aliasing to same name as used in group by
> -----------------------------------------------------------------------
>
> Key: DERBY-280
> URL: http://issues.apache.org/jira/browse/DERBY-280
> Project: Derby
> Type: Bug
> Components: SQL
> Reporter: Bernt M. Johnsen
> Assignee: Rick Hillegas
> Priority: Minor
> Attachments: bug280.diff
>
> Wrong result from select when aliasing to same name as used in group by. Example:
> If we have the following table:
> ij> select * from tt;
> I |J
> -----------------------
> 1 |2
> 2 |3
> 1 |2
> 2 |3
> 2 |3
>
> 5 rows selected
> The following select is ok:
> ij> select i, count(*) as cnt from tt group by i;
> I |CNT
> -----------------------
> 1 |2
> 2 |3
>
> 2 rows selected
> But this one returns wrong result in the aliased column:
> ij> select i, count(*) as i from tt group by i;
> I |I
> -----------------------
> 1 |1
> 2 |2
>
> 2 rows selected
>
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
[jira] Commented: (DERBY-280) Wrong result from select when aliasing to same name as used in group by
Posted by "Kathey Marsden (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-280?page=comments#action_12358932 ]
Kathey Marsden commented on DERBY-280:
--------------------------------------
I was wondering was there another bug filed for the regression introduced by the patch? I did not see one linked to this issue.
> Wrong result from select when aliasing to same name as used in group by
> -----------------------------------------------------------------------
>
> Key: DERBY-280
> URL: http://issues.apache.org/jira/browse/DERBY-280
> Project: Derby
> Type: Bug
> Components: SQL
> Reporter: Bernt M. Johnsen
> Assignee: Rick Hillegas
> Priority: Minor
> Attachments: bug280.diff
>
> Wrong result from select when aliasing to same name as used in group by. Example:
> If we have the following table:
> ij> select * from tt;
> I |J
> -----------------------
> 1 |2
> 2 |3
> 1 |2
> 2 |3
> 2 |3
>
> 5 rows selected
> The following select is ok:
> ij> select i, count(*) as cnt from tt group by i;
> I |CNT
> -----------------------
> 1 |2
> 2 |3
>
> 2 rows selected
> But this one returns wrong result in the aliased column:
> ij> select i, count(*) as i from tt group by i;
> I |I
> -----------------------
> 1 |1
> 2 |2
>
> 2 rows selected
>
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
[jira] Closed: (DERBY-280) Wrong result from select when aliasing to same name as used in group by
Posted by "Rick Hillegas (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-280?page=all ]
Rick Hillegas closed DERBY-280:
-------------------------------
The regression test for this patch (in aggregate.sql) succeeds. So I am closing this bug.
> Wrong result from select when aliasing to same name as used in group by
> -----------------------------------------------------------------------
>
> Key: DERBY-280
> URL: http://issues.apache.org/jira/browse/DERBY-280
> Project: Derby
> Type: Bug
> Components: SQL
> Reporter: Bernt M. Johnsen
> Assignee: Rick Hillegas
> Priority: Minor
> Attachments: bug280.diff
>
> Wrong result from select when aliasing to same name as used in group by. Example:
> If we have the following table:
> ij> select * from tt;
> I |J
> -----------------------
> 1 |2
> 2 |3
> 1 |2
> 2 |3
> 2 |3
>
> 5 rows selected
> The following select is ok:
> ij> select i, count(*) as cnt from tt group by i;
> I |CNT
> -----------------------
> 1 |2
> 2 |3
>
> 2 rows selected
> But this one returns wrong result in the aliased column:
> ij> select i, count(*) as i from tt group by i;
> I |I
> -----------------------
> 1 |1
> 2 |2
>
> 2 rows selected
>
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
[jira] Resolved: (DERBY-280) Wrong result from select when aliasing to same name as used in group by
Posted by "Rick Hillegas (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-280?page=all ]
Rick Hillegas resolved DERBY-280:
---------------------------------
Resolution: Incomplete
I am resolving this bug as Incomplete. The complete fix to this bug is part of the cleanup described in jira 681.
> Wrong result from select when aliasing to same name as used in group by
> -----------------------------------------------------------------------
>
> Key: DERBY-280
> URL: http://issues.apache.org/jira/browse/DERBY-280
> Project: Derby
> Type: Bug
> Components: SQL
> Reporter: Bernt M. Johnsen
> Assignee: Rick Hillegas
> Priority: Minor
> Attachments: bug280.diff
>
> Wrong result from select when aliasing to same name as used in group by. Example:
> If we have the following table:
> ij> select * from tt;
> I |J
> -----------------------
> 1 |2
> 2 |3
> 1 |2
> 2 |3
> 2 |3
>
> 5 rows selected
> The following select is ok:
> ij> select i, count(*) as cnt from tt group by i;
> I |CNT
> -----------------------
> 1 |2
> 2 |3
>
> 2 rows selected
> But this one returns wrong result in the aliased column:
> ij> select i, count(*) as i from tt group by i;
> I |I
> -----------------------
> 1 |1
> 2 |2
>
> 2 rows selected
>
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
[jira] Updated: (DERBY-280) Wrong result from select when aliasing to same name as used in group by
Posted by "Rick Hillegas (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-280?page=all ]
Rick Hillegas updated DERBY-280:
--------------------------------
Attachment: (was: bug280.diff)
> Wrong result from select when aliasing to same name as used in group by
> -----------------------------------------------------------------------
>
> Key: DERBY-280
> URL: http://issues.apache.org/jira/browse/DERBY-280
> Project: Derby
> Type: Bug
> Components: SQL
> Reporter: Bernt M. Johnsen
> Assignee: Rick Hillegas
> Priority: Minor
>
> Wrong result from select when aliasing to same name as used in group by. Example:
> If we have the following table:
> ij> select * from tt;
> I |J
> -----------------------
> 1 |2
> 2 |3
> 1 |2
> 2 |3
> 2 |3
>
> 5 rows selected
> The following select is ok:
> ij> select i, count(*) as cnt from tt group by i;
> I |CNT
> -----------------------
> 1 |2
> 2 |3
>
> 2 rows selected
> But this one returns wrong result in the aliased column:
> ij> select i, count(*) as i from tt group by i;
> I |I
> -----------------------
> 1 |1
> 2 |2
>
> 2 rows selected
>
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
Re: [jira] Commented: (DERBY-280) Wrong result from select when
aliasing to same name as used in group by
Posted by Knut Anders Hatlen <Kn...@Sun.COM>.
"Satheesh Bandaram (JIRA)" <de...@db.apache.org> writes:
> [ http://issues.apache.org/jira/browse/DERBY-280?page=comments#action_12357654 ]
>
> Satheesh Bandaram commented on DERBY-280:
> -----------------------------------------
>
> Rick, what have you decided about this patch? Would you like me to
> commit this? As I mentioned, I believe, this patch improves on the
> current situation, though doesn't address the underlying issue.
>
> If you want me to commit the patch, please do update the patch with
> latest code base... sqlgrammar has changed.
Please fix the indentation too. In vetSelectList280(), some of the
lines are indented with tabs and others with spaces.
--
Knut Anders
[jira] Commented: (DERBY-280) Wrong result from select when aliasing to same name as used in group by
Posted by "Satheesh Bandaram (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-280?page=comments#action_12357654 ]
Satheesh Bandaram commented on DERBY-280:
-----------------------------------------
Rick, what have you decided about this patch? Would you like me to commit this? As I mentioned, I believe, this patch improves on the current situation, though doesn't address the underlying issue.
If you want me to commit the patch, please do update the patch with latest code base... sqlgrammar has changed.
> Wrong result from select when aliasing to same name as used in group by
> -----------------------------------------------------------------------
>
> Key: DERBY-280
> URL: http://issues.apache.org/jira/browse/DERBY-280
> Project: Derby
> Type: Bug
> Components: SQL
> Reporter: Bernt M. Johnsen
> Assignee: Rick Hillegas
> Priority: Minor
> Attachments: bug280.diff
>
> Wrong result from select when aliasing to same name as used in group by. Example:
> If we have the following table:
> ij> select * from tt;
> I |J
> -----------------------
> 1 |2
> 2 |3
> 1 |2
> 2 |3
> 2 |3
>
> 5 rows selected
> The following select is ok:
> ij> select i, count(*) as cnt from tt group by i;
> I |CNT
> -----------------------
> 1 |2
> 2 |3
>
> 2 rows selected
> But this one returns wrong result in the aliased column:
> ij> select i, count(*) as i from tt group by i;
> I |I
> -----------------------
> 1 |1
> 2 |2
>
> 2 rows selected
>
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
[jira] Commented: (DERBY-280) Wrong result from select when aliasing to same name as used in group by
Posted by "Satheesh Bandaram (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-280?page=comments#action_12357306 ]
Satheesh Bandaram commented on DERBY-280:
-----------------------------------------
I have briefly looked at your patch... While not addressing the underlying problem, the patch attempts to improve the situation by raising exception instead of possibly wrong results. This may be a better short term solution, but the patch incorrectly disables unambiguous cases too.
Using the same table as the patch adds, the following correctly works today:
ij> select a+1 as a, a+1 as a from bug280 group by a;
A |A
-----------------------
2 |2
3 |3
2 rows selected
There is no ambiguity here, since both expressions are the same. But after applying the patch, the query fails:
ij> select a+1 as a, a+1 as a from bug280 group by a;
ERROR X0A00: The select list mentions column 'A' twice. This is not allowed in queries with GROUP BY or HAVING clauses. Try aliasing one of the conflicting columns to a unique name.
> Wrong result from select when aliasing to same name as used in group by
> -----------------------------------------------------------------------
>
> Key: DERBY-280
> URL: http://issues.apache.org/jira/browse/DERBY-280
> Project: Derby
> Type: Bug
> Components: SQL
> Reporter: Bernt M. Johnsen
> Assignee: Rick Hillegas
> Priority: Minor
> Attachments: bug280.diff
>
> Wrong result from select when aliasing to same name as used in group by. Example:
> If we have the following table:
> ij> select * from tt;
> I |J
> -----------------------
> 1 |2
> 2 |3
> 1 |2
> 2 |3
> 2 |3
>
> 5 rows selected
> The following select is ok:
> ij> select i, count(*) as cnt from tt group by i;
> I |CNT
> -----------------------
> 1 |2
> 2 |3
>
> 2 rows selected
> But this one returns wrong result in the aliased column:
> ij> select i, count(*) as i from tt group by i;
> I |I
> -----------------------
> 1 |1
> 2 |2
>
> 2 rows selected
>
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
[jira] Assigned: (DERBY-280) Wrong result from select when aliasing to same name as used in group by
Posted by "Rick Hillegas (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-280?page=all ]
Rick Hillegas reassigned DERBY-280:
-----------------------------------
Assign To: Rick Hillegas
> Wrong result from select when aliasing to same name as used in group by
> -----------------------------------------------------------------------
>
> Key: DERBY-280
> URL: http://issues.apache.org/jira/browse/DERBY-280
> Project: Derby
> Type: Bug
> Components: SQL
> Reporter: Bernt M. Johnsen
> Assignee: Rick Hillegas
> Priority: Minor
>
> Wrong result from select when aliasing to same name as used in group by. Example:
> If we have the following table:
> ij> select * from tt;
> I |J
> -----------------------
> 1 |2
> 2 |3
> 1 |2
> 2 |3
> 2 |3
>
> 5 rows selected
> The following select is ok:
> ij> select i, count(*) as cnt from tt group by i;
> I |CNT
> -----------------------
> 1 |2
> 2 |3
>
> 2 rows selected
> But this one returns wrong result in the aliased column:
> ij> select i, count(*) as i from tt group by i;
> I |I
> -----------------------
> 1 |1
> 2 |2
>
> 2 rows selected
>
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
[jira] Updated: (DERBY-280) Wrong result from select when aliasing to same name as used in group by
Posted by "Rick Hillegas (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-280?page=all ]
Rick Hillegas updated DERBY-280:
--------------------------------
Attachment: bug280.diff
Hi Satheesh,
Thanks for finding this howler. I have fixed the npe and added some more test cases to verify that fact. Derbyall runs cleanly on this patch.
> Wrong result from select when aliasing to same name as used in group by
> -----------------------------------------------------------------------
>
> Key: DERBY-280
> URL: http://issues.apache.org/jira/browse/DERBY-280
> Project: Derby
> Type: Bug
> Components: SQL
> Reporter: Bernt M. Johnsen
> Assignee: Rick Hillegas
> Priority: Minor
> Attachments: bug280.diff
>
> Wrong result from select when aliasing to same name as used in group by. Example:
> If we have the following table:
> ij> select * from tt;
> I |J
> -----------------------
> 1 |2
> 2 |3
> 1 |2
> 2 |3
> 2 |3
>
> 5 rows selected
> The following select is ok:
> ij> select i, count(*) as cnt from tt group by i;
> I |CNT
> -----------------------
> 1 |2
> 2 |3
>
> 2 rows selected
> But this one returns wrong result in the aliased column:
> ij> select i, count(*) as i from tt group by i;
> I |I
> -----------------------
> 1 |1
> 2 |2
>
> 2 rows selected
>
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
[jira] Commented: (DERBY-280) Wrong result from select when aliasing to same name as used in group by
Posted by "Satheesh Bandaram (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-280?page=comments#action_12313377 ]
Satheesh Bandaram commented on DERBY-280:
-----------------------------------------
I think this problem is caused because of rewriting of group by clause and having multiple result columns with same name. ("i" in this case). A query like SELECT I, COUNT(*) AS I FROM TT GROUP BY I gets rewritten to something like SELECT I, I FROM (select I, COUNT(*) AS I FROM TT GROUP BY I) internally, to facilitate processing of GROUP BY/HAVING clauses. When each of the resultColumn "I" is bound to expressions from the inner query, both resolve to the first "I" causing the wrong results.
If the query were to flip the expressions in the original query, to something like: SELECT COUNT(*) AS I, I FROM TT GROUP BY I, then the result would still be wrong, but both result columns for I would be for COUNT(*) column.
ij> select i, count(*) as i from tt group by i;
I |I
-----------------------
1 |1
2 |2
2 rows selected
ij> select count(*) as i, i from tt group by i;
I |I
-----------------------
1 |1
1 |1
2 rows selected
I think a fix needs to address the binding of rewritten querries correctly. When Derby rewrites the query to facilitate group by/having processing, the correlation name for the inner query is NULL.
> Wrong result from select when aliasing to same name as used in group by
> -----------------------------------------------------------------------
>
> Key: DERBY-280
> URL: http://issues.apache.org/jira/browse/DERBY-280
> Project: Derby
> Type: Bug
> Components: SQL
> Reporter: Bernt M. Johnsen
> Priority: Minor
>
> Wrong result from select when aliasing to same name as used in group by. Example:
> If we have the following table:
> ij> select * from tt;
> I |J
> -----------------------
> 1 |2
> 2 |3
> 1 |2
> 2 |3
> 2 |3
>
> 5 rows selected
> The following select is ok:
> ij> select i, count(*) as cnt from tt group by i;
> I |CNT
> -----------------------
> 1 |2
> 2 |3
>
> 2 rows selected
> But this one returns wrong result in the aliased column:
> ij> select i, count(*) as i from tt group by i;
> I |I
> -----------------------
> 1 |1
> 2 |2
>
> 2 rows selected
>
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
[jira] Commented: (DERBY-280) Wrong result from select when aliasing to same name as used in group by
Posted by "Satheesh Bandaram (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-280?page=comments#action_12357307 ]
Satheesh Bandaram commented on DERBY-280:
-----------------------------------------
In case you are planning on submitting a new enhanced patch, please update your client... Patch can't be applied as sqlgrammar.jj has evolved.
> Wrong result from select when aliasing to same name as used in group by
> -----------------------------------------------------------------------
>
> Key: DERBY-280
> URL: http://issues.apache.org/jira/browse/DERBY-280
> Project: Derby
> Type: Bug
> Components: SQL
> Reporter: Bernt M. Johnsen
> Assignee: Rick Hillegas
> Priority: Minor
> Attachments: bug280.diff
>
> Wrong result from select when aliasing to same name as used in group by. Example:
> If we have the following table:
> ij> select * from tt;
> I |J
> -----------------------
> 1 |2
> 2 |3
> 1 |2
> 2 |3
> 2 |3
>
> 5 rows selected
> The following select is ok:
> ij> select i, count(*) as cnt from tt group by i;
> I |CNT
> -----------------------
> 1 |2
> 2 |3
>
> 2 rows selected
> But this one returns wrong result in the aliased column:
> ij> select i, count(*) as i from tt group by i;
> I |I
> -----------------------
> 1 |1
> 2 |2
>
> 2 rows selected
>
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
[jira] Commented: (DERBY-280) Wrong result from select when aliasing to same name as used in group by
Posted by "Rick Hillegas (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-280?page=comments#action_12357313 ]
Rick Hillegas commented on DERBY-280:
-------------------------------------
It is true that, given this patch, that edge case will raise an error. The query would have to be rewritten as the error message indicates to something like the following:
select a+1 a, a+1 b from bug280 group by a;
The following other queries will raise the same error message and need rephrasing:
select 1+a as a, a+1 as a from bug280 group by a;
select ((a+a)-a)+1 as a, a+1 as a from bug280 group by a;
And so on. I don't think that this patch can be patched to allow these queries to pass. These sort of semantic checks can only be performed during the bind and normalization phases. By that time we have lost the information needed to identify the original bug.
I can only repeat my belief that it is better to require the user to rewrite some edge cases than to silently return wrong results. Thanks for taking the time to review this patch.
> Wrong result from select when aliasing to same name as used in group by
> -----------------------------------------------------------------------
>
> Key: DERBY-280
> URL: http://issues.apache.org/jira/browse/DERBY-280
> Project: Derby
> Type: Bug
> Components: SQL
> Reporter: Bernt M. Johnsen
> Assignee: Rick Hillegas
> Priority: Minor
> Attachments: bug280.diff
>
> Wrong result from select when aliasing to same name as used in group by. Example:
> If we have the following table:
> ij> select * from tt;
> I |J
> -----------------------
> 1 |2
> 2 |3
> 1 |2
> 2 |3
> 2 |3
>
> 5 rows selected
> The following select is ok:
> ij> select i, count(*) as cnt from tt group by i;
> I |CNT
> -----------------------
> 1 |2
> 2 |3
>
> 2 rows selected
> But this one returns wrong result in the aliased column:
> ij> select i, count(*) as i from tt group by i;
> I |I
> -----------------------
> 1 |1
> 2 |2
>
> 2 rows selected
>
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
[jira] Commented: (DERBY-280) Wrong result from select when aliasing to same name as used in group by
Posted by "Satheesh Bandaram (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-280?page=comments#action_12357330 ]
Satheesh Bandaram commented on DERBY-280:
-----------------------------------------
I agree the patch is not fixable to make this query work... Your patch currently assumes two instances of expressions are not equivalent and it is not easy to confirm it. I tried similar querries on DB2, that I have access to. DB2 handles them correctly... without raising an error. Guess they don't have the query rewrite issue Derby does.
I also attempted to "fix" the problem some time ago... I tried your second approach, match by position. The patch was getting ugly, like you mentioned, so I also abandoned going that way.
As I see it, your patch improves the situation from what we have today, but also incorrectly fails on a much smaller number of querries. I wouldn't mind committing the patch... Anyone else have any other comments?
> Wrong result from select when aliasing to same name as used in group by
> -----------------------------------------------------------------------
>
> Key: DERBY-280
> URL: http://issues.apache.org/jira/browse/DERBY-280
> Project: Derby
> Type: Bug
> Components: SQL
> Reporter: Bernt M. Johnsen
> Assignee: Rick Hillegas
> Priority: Minor
> Attachments: bug280.diff
>
> Wrong result from select when aliasing to same name as used in group by. Example:
> If we have the following table:
> ij> select * from tt;
> I |J
> -----------------------
> 1 |2
> 2 |3
> 1 |2
> 2 |3
> 2 |3
>
> 5 rows selected
> The following select is ok:
> ij> select i, count(*) as cnt from tt group by i;
> I |CNT
> -----------------------
> 1 |2
> 2 |3
>
> 2 rows selected
> But this one returns wrong result in the aliased column:
> ij> select i, count(*) as i from tt group by i;
> I |I
> -----------------------
> 1 |1
> 2 |2
>
> 2 rows selected
>
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
[jira] Updated: (DERBY-280) Wrong result from select when aliasing to same name as used in group by
Posted by "Rick Hillegas (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-280?page=all ]
Rick Hillegas updated DERBY-280:
--------------------------------
Attachment: bug280.diff
I am attaching a patch which makes the situation better. I tried a couple approaches to fixing this bug: 1) I tried fabricating unique aliases in the inner query block and propagating the original aliases just to the outer query block; this broke ORDER BY logic. 2) I tried binding the outer select list by position rather than by name; this broke whenever the compiler, to solve other problems, fabricated dummy columns. In addition, with both approaches I found myself grafting special-case logic across many classes. I felt that these solutions made the code more brittle and were not warranted by this edge-case problem. I feel that the cleanest way to solve this problem is to redo the special parser logic which mangles the abstract syntax tree for queries with GROUP BY and HAVING clauses (see bug 681). That, however, is a big task. Until bug 681 is fixed, I'm offering the attached patch. The patch raises an exception if the customer writes a query with the following profile:
o The query contains a GROUP BY or HAVING clause
o The query contains an expression which is aliased to the same name as a selected column
The exception tells the customer how to rewrite their query so that it will compile. I think this is better than the current situation in which the query silently returns the wrong results. It is worth pointing out that this patch does not break any queries in our existing regression tests.
Derbyall runs cleanly with this patch. The patch contains the following files:
M java\engine\org\apache\derby\impl\sql\compile\sqlgrammar.jj
M java\engine\org\apache\derby\iapi\reference\SQLState.java
M java\engine\org\apache\derby\loc\messages_en.properties
M java\testing\org\apache\derbyTesting\functionTests\tests\lang\aggregate.sql
M java\testing\org\apache\derbyTesting\functionTests\master\aggregate.out
> Wrong result from select when aliasing to same name as used in group by
> -----------------------------------------------------------------------
>
> Key: DERBY-280
> URL: http://issues.apache.org/jira/browse/DERBY-280
> Project: Derby
> Type: Bug
> Components: SQL
> Reporter: Bernt M. Johnsen
> Assignee: Rick Hillegas
> Priority: Minor
> Attachments: bug280.diff
>
> Wrong result from select when aliasing to same name as used in group by. Example:
> If we have the following table:
> ij> select * from tt;
> I |J
> -----------------------
> 1 |2
> 2 |3
> 1 |2
> 2 |3
> 2 |3
>
> 5 rows selected
> The following select is ok:
> ij> select i, count(*) as cnt from tt group by i;
> I |CNT
> -----------------------
> 1 |2
> 2 |3
>
> 2 rows selected
> But this one returns wrong result in the aliased column:
> ij> select i, count(*) as i from tt group by i;
> I |I
> -----------------------
> 1 |1
> 2 |2
>
> 2 rows selected
>
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
[jira] Commented: (DERBY-280) Wrong result from select when aliasing to same name as used in group by
Posted by "Satheesh Bandaram (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-280?page=comments#action_12358935 ]
Satheesh Bandaram commented on DERBY-280:
-----------------------------------------
What regression? I don't know of any regression introduced by this patch. Let me know if there is one.
RICK, assuming there is no regression introduced, you should RESOLVE this bug and then CLOSE it after confirming the fix.
> Wrong result from select when aliasing to same name as used in group by
> -----------------------------------------------------------------------
>
> Key: DERBY-280
> URL: http://issues.apache.org/jira/browse/DERBY-280
> Project: Derby
> Type: Bug
> Components: SQL
> Reporter: Bernt M. Johnsen
> Assignee: Rick Hillegas
> Priority: Minor
> Attachments: bug280.diff
>
> Wrong result from select when aliasing to same name as used in group by. Example:
> If we have the following table:
> ij> select * from tt;
> I |J
> -----------------------
> 1 |2
> 2 |3
> 1 |2
> 2 |3
> 2 |3
>
> 5 rows selected
> The following select is ok:
> ij> select i, count(*) as cnt from tt group by i;
> I |CNT
> -----------------------
> 1 |2
> 2 |3
>
> 2 rows selected
> But this one returns wrong result in the aliased column:
> ij> select i, count(*) as i from tt group by i;
> I |I
> -----------------------
> 1 |1
> 2 |2
>
> 2 rows selected
>
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
[jira] Commented: (DERBY-280) Wrong result from select when aliasing to same name as used in group by
Posted by "Satheesh Bandaram (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-280?page=comments#action_12359116 ]
Satheesh Bandaram commented on DERBY-280:
-----------------------------------------
Thanks Kathey... Your comment explains what you were refering to. There was a potential regression that was found and fixed during the review/commit process, so I thought you were refering to that.
About the SQL: select a+1 as a, a+1 as a from bug280 group by a, the patch does prevent its use now. But the statement is so (likely) rare and with the patch it gives a nice error message on what to change, I think it might be sufficient. Also Rick only marked this incomplete, keeping it open for improvement, I think. If you still have an itch to file a bug, go ahead!
> Wrong result from select when aliasing to same name as used in group by
> -----------------------------------------------------------------------
>
> Key: DERBY-280
> URL: http://issues.apache.org/jira/browse/DERBY-280
> Project: Derby
> Type: Bug
> Components: SQL
> Reporter: Bernt M. Johnsen
> Assignee: Rick Hillegas
> Priority: Minor
> Attachments: bug280.diff
>
> Wrong result from select when aliasing to same name as used in group by. Example:
> If we have the following table:
> ij> select * from tt;
> I |J
> -----------------------
> 1 |2
> 2 |3
> 1 |2
> 2 |3
> 2 |3
>
> 5 rows selected
> The following select is ok:
> ij> select i, count(*) as cnt from tt group by i;
> I |CNT
> -----------------------
> 1 |2
> 2 |3
>
> 2 rows selected
> But this one returns wrong result in the aliased column:
> ij> select i, count(*) as i from tt group by i;
> I |I
> -----------------------
> 1 |1
> 2 |2
>
> 2 rows selected
>
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
[jira] Commented: (DERBY-280) Wrong result from select when aliasing to same name as used in group by
Posted by "Satheesh Bandaram (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-280?page=comments#action_12357918 ]
Satheesh Bandaram commented on DERBY-280:
-----------------------------------------
I have submitted this fix to trunk. Please Resolve and Close the bug after confirming the fix.
I have added a comment to indicate this problem is caused by current parser rewrite and that any fix to address Derby-681 would make this problem go away. Rick, I hope you don't mind this comment:
** This issue is caused by parser rewriting of group by/having clauses
** into table expressions. (See function tableExpression() in this file)
** There is an improvement request filed under DERBY-681 to eliminate this
** rewrite, after which it should be possible to allow multiple columns to
** have same name in the select list.
Sending java\engine\org\apache\derby\iapi\reference\SQLState.java
Sending java\engine\org\apache\derby\impl\sql\compile\sqlgrammar.jj
Sending java\engine\org\apache\derby\loc\messages_en.properties
Sending java\testing\org\apache\derbyTesting\functionTests\master\aggregate.out
Sending java\testing\org\apache\derbyTesting\functionTests\tests\lang\aggregate.sql
Transmitting file data .....
Committed revision 345304.
> Wrong result from select when aliasing to same name as used in group by
> -----------------------------------------------------------------------
>
> Key: DERBY-280
> URL: http://issues.apache.org/jira/browse/DERBY-280
> Project: Derby
> Type: Bug
> Components: SQL
> Reporter: Bernt M. Johnsen
> Assignee: Rick Hillegas
> Priority: Minor
> Attachments: bug280.diff
>
> Wrong result from select when aliasing to same name as used in group by. Example:
> If we have the following table:
> ij> select * from tt;
> I |J
> -----------------------
> 1 |2
> 2 |3
> 1 |2
> 2 |3
> 2 |3
>
> 5 rows selected
> The following select is ok:
> ij> select i, count(*) as cnt from tt group by i;
> I |CNT
> -----------------------
> 1 |2
> 2 |3
>
> 2 rows selected
> But this one returns wrong result in the aliased column:
> ij> select i, count(*) as i from tt group by i;
> I |I
> -----------------------
> 1 |1
> 2 |2
>
> 2 rows selected
>
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
[jira] Commented: (DERBY-280) Wrong result from select when aliasing to same name as used in group by
Posted by "Kathey Marsden (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-280?page=comments#action_12358945 ]
Kathey Marsden commented on DERBY-280:
--------------------------------------
In an earlier comment you mentioned a valid query which worked before the patch which now fails with:
ij> select a+1 as a, a+1 as a from bug280 group by a;
ERROR X0A00: The select list mentions column 'A' twice. This is not allowed in queries with GROUP BY or HAVING clauses. Try aliasing one of the conflicting columns to a unique name.
There was a discussion of whether this new failure was worth introducing given the seriousness of returning wrong results. A choice of the lesser of evils I thought, regress an edge case rather than return wrong results.
But I might have been mistaken.
> Wrong result from select when aliasing to same name as used in group by
> -----------------------------------------------------------------------
>
> Key: DERBY-280
> URL: http://issues.apache.org/jira/browse/DERBY-280
> Project: Derby
> Type: Bug
> Components: SQL
> Reporter: Bernt M. Johnsen
> Assignee: Rick Hillegas
> Priority: Minor
> Attachments: bug280.diff
>
> Wrong result from select when aliasing to same name as used in group by. Example:
> If we have the following table:
> ij> select * from tt;
> I |J
> -----------------------
> 1 |2
> 2 |3
> 1 |2
> 2 |3
> 2 |3
>
> 5 rows selected
> The following select is ok:
> ij> select i, count(*) as cnt from tt group by i;
> I |CNT
> -----------------------
> 1 |2
> 2 |3
>
> 2 rows selected
> But this one returns wrong result in the aliased column:
> ij> select i, count(*) as i from tt group by i;
> I |I
> -----------------------
> 1 |1
> 2 |2
>
> 2 rows selected
>
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
[jira] Commented: (DERBY-280) Wrong result from select when aliasing to same name as used in group by
Posted by "Satheesh Bandaram (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-280?page=comments#action_12357757 ]
Satheesh Bandaram commented on DERBY-280:
-----------------------------------------
Thanks for updating the patch, Rick. But I think I found one issue, that definitely need fixing. I should have seen these at the first round itself...
The fix can cause NullPointerExceptions. The following check is incorrect.
+ if (
+ ( (newTableName == null) && (oldTableName == null) ) ||
+ ( newTableName.equals( oldTableName ) )
+ )
What happens if newTableName is NULL and oldTableName is NOT NULL? The code causes NullPointerException.
ij> select bug280.a, a from bug280 group by a;
ERROR XJ001: Java exception: ': java.lang.NullPointerException'.
ij>
> Wrong result from select when aliasing to same name as used in group by
> -----------------------------------------------------------------------
>
> Key: DERBY-280
> URL: http://issues.apache.org/jira/browse/DERBY-280
> Project: Derby
> Type: Bug
> Components: SQL
> Reporter: Bernt M. Johnsen
> Assignee: Rick Hillegas
> Priority: Minor
> Attachments: bug280.diff
>
> Wrong result from select when aliasing to same name as used in group by. Example:
> If we have the following table:
> ij> select * from tt;
> I |J
> -----------------------
> 1 |2
> 2 |3
> 1 |2
> 2 |3
> 2 |3
>
> 5 rows selected
> The following select is ok:
> ij> select i, count(*) as cnt from tt group by i;
> I |CNT
> -----------------------
> 1 |2
> 2 |3
>
> 2 rows selected
> But this one returns wrong result in the aliased column:
> ij> select i, count(*) as i from tt group by i;
> I |I
> -----------------------
> 1 |1
> 2 |2
>
> 2 rows selected
>
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
[jira] Commented: (DERBY-280) Wrong result from select when aliasing to same name as used in group by
Posted by "Rick Hillegas (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-280?page=comments#action_12356658 ]
Rick Hillegas commented on DERBY-280:
-------------------------------------
Hm, I'm afraid these illegal relations (relations with identically named columns) turn up everywhere. Disallowing them is not going to fly.
> Wrong result from select when aliasing to same name as used in group by
> -----------------------------------------------------------------------
>
> Key: DERBY-280
> URL: http://issues.apache.org/jira/browse/DERBY-280
> Project: Derby
> Type: Bug
> Components: SQL
> Reporter: Bernt M. Johnsen
> Assignee: Rick Hillegas
> Priority: Minor
>
> Wrong result from select when aliasing to same name as used in group by. Example:
> If we have the following table:
> ij> select * from tt;
> I |J
> -----------------------
> 1 |2
> 2 |3
> 1 |2
> 2 |3
> 2 |3
>
> 5 rows selected
> The following select is ok:
> ij> select i, count(*) as cnt from tt group by i;
> I |CNT
> -----------------------
> 1 |2
> 2 |3
>
> 2 rows selected
> But this one returns wrong result in the aliased column:
> ij> select i, count(*) as i from tt group by i;
> I |I
> -----------------------
> 1 |1
> 2 |2
>
> 2 rows selected
>
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
[jira] Updated: (DERBY-280) Wrong result from select when aliasing to same name as used in group by
Posted by "Rick Hillegas (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-280?page=all ]
Rick Hillegas updated DERBY-280:
--------------------------------
Attachment: bug280.diff
I have reapplied this patch to a current snapshot of the mainline. I ran derbyall. Two tests failed (dataSourcePermissions_net and ConcurrentImplicitCreateSchema) but it's hard for me to believe that these failures were triggered by my changes.
> Wrong result from select when aliasing to same name as used in group by
> -----------------------------------------------------------------------
>
> Key: DERBY-280
> URL: http://issues.apache.org/jira/browse/DERBY-280
> Project: Derby
> Type: Bug
> Components: SQL
> Reporter: Bernt M. Johnsen
> Assignee: Rick Hillegas
> Priority: Minor
> Attachments: bug280.diff
>
> Wrong result from select when aliasing to same name as used in group by. Example:
> If we have the following table:
> ij> select * from tt;
> I |J
> -----------------------
> 1 |2
> 2 |3
> 1 |2
> 2 |3
> 2 |3
>
> 5 rows selected
> The following select is ok:
> ij> select i, count(*) as cnt from tt group by i;
> I |CNT
> -----------------------
> 1 |2
> 2 |3
>
> 2 rows selected
> But this one returns wrong result in the aliased column:
> ij> select i, count(*) as i from tt group by i;
> I |I
> -----------------------
> 1 |1
> 2 |2
>
> 2 rows selected
>
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
[jira] Updated: (DERBY-280) Wrong result from select when aliasing to same name as used in group by
Posted by "Rick Hillegas (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-280?page=all ]
Rick Hillegas updated DERBY-280:
--------------------------------
Attachment: (was: bug280.diff)
> Wrong result from select when aliasing to same name as used in group by
> -----------------------------------------------------------------------
>
> Key: DERBY-280
> URL: http://issues.apache.org/jira/browse/DERBY-280
> Project: Derby
> Type: Bug
> Components: SQL
> Reporter: Bernt M. Johnsen
> Assignee: Rick Hillegas
> Priority: Minor
>
> Wrong result from select when aliasing to same name as used in group by. Example:
> If we have the following table:
> ij> select * from tt;
> I |J
> -----------------------
> 1 |2
> 2 |3
> 1 |2
> 2 |3
> 2 |3
>
> 5 rows selected
> The following select is ok:
> ij> select i, count(*) as cnt from tt group by i;
> I |CNT
> -----------------------
> 1 |2
> 2 |3
>
> 2 rows selected
> But this one returns wrong result in the aliased column:
> ij> select i, count(*) as i from tt group by i;
> I |I
> -----------------------
> 1 |1
> 2 |2
>
> 2 rows selected
>
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira