[
https://issues.apache.org/jira/browse/DERBY-6909?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16134405#comment-16134405
]
Indumini Ayomi commented on DERBY-6909:
---------------------------------------
I encountered following error while executing sample query:
ij> select * from TEST group by upper(name), upper(label);
But it should give this result:
johnny|label_1
johnny|label_2
ij> select * from TEST group by upper(name), upper(label);
Sun Aug 20 16:57:54 IST 2017 Thread[main,5,main] (XID = 125), (SESSIONID = 1),
(DATABASE = TestDb), (DRDAID = null), Cleanup action starting
Sun Aug 20 16:57:54 IST 2017 Thread[main,5,main] (XID = 125), (SESSIONID = 1),
(DATABASE = TestDb), (DRDAID = null), Failed Statement is: select * from TEST
group by upper(name), upper(label)
ERROR 42Y36: Column reference 'TEST.NAME' is invalid, or is part of an invalid
expression. For a SELECT list with a GROUP BY, the columns and expressions
being selected may only contain valid grouping expressions and valid aggregate
expressions.
at
org.apache.derby.iapi.error.StandardException.newException(StandardException.java:278)
at
org.apache.derby.impl.sql.compile.VerifyAggregateExpressionsVisitor.visit(VerifyAggregateExpressionsVisitor.java:85)
at
org.apache.derby.impl.sql.compile.QueryTreeNode.accept(QueryTreeNode.java:718)
at
org.apache.derby.impl.sql.compile.ResultColumn.acceptChildren(ResultColumn.java:1679)
at
org.apache.derby.impl.sql.compile.QueryTreeNode.accept(QueryTreeNode.java:721)
at
org.apache.derby.impl.sql.compile.QueryTreeNodeVector.acceptChildren(QueryTreeNodeVector.java:133)
at
org.apache.derby.impl.sql.compile.QueryTreeNode.accept(QueryTreeNode.java:721)
at
org.apache.derby.impl.sql.compile.SelectNode.bindExpressions(SelectNode.java:704)
at
org.apache.derby.impl.sql.compile.DMLStatementNode.bindExpressions(DMLStatementNode.java:225)
at
org.apache.derby.impl.sql.compile.DMLStatementNode.bind(DMLStatementNode.java:138)
at
org.apache.derby.impl.sql.compile.CursorNode.bindStatement(CursorNode.java:268)
at
org.apache.derby.impl.sql.GenericStatement.prepMinion(GenericStatement.java:400)
at
org.apache.derby.impl.sql.GenericStatement.prepare(GenericStatement.java:99)
at
org.apache.derby.impl.sql.conn.GenericLanguageConnectionContext.prepareInternalStatement(GenericLanguageConnectionContext.java:1111)
at
org.apache.derby.impl.jdbc.EmbedStatement.execute(EmbedStatement.java:692)
at
org.apache.derby.impl.jdbc.EmbedStatement.execute(EmbedStatement.java:640)
at org.apache.derby.impl.tools.ij.ij.executeImmediate(ij.java:367)
at org.apache.derby.impl.tools.ij.utilMain.doCatch(utilMain.java:527)
at
org.apache.derby.impl.tools.ij.utilMain.runScriptGuts(utilMain.java:372)
at org.apache.derby.impl.tools.ij.utilMain.go(utilMain.java:245)
at org.apache.derby.impl.tools.ij.Main.go(Main.java:229)
at org.apache.derby.impl.tools.ij.Main.mainCore(Main.java:184)
at org.apache.derby.impl.tools.ij.Main.main(Main.java:75)
at org.apache.derby.tools.ij.main(ij.java:59)
Cleanup action completed
ERROR 42Y36: Column reference 'TEST.NAME' is invalid, or is part of an invalid
expression. For a SELECT list with a GROUP BY, the columns and expressions
being selected may only contain valid grouping expressions and valid aggregate
expressions.
> Invalid grouping by multiple columns when use function (Apache Derby
> 10.10.2.0)
> -------------------------------------------------------------------------------
>
> Key: DERBY-6909
> URL: https://issues.apache.org/jira/browse/DERBY-6909
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.10.2.0, 10.13.1.1
> Reporter: Boris Brinza
> Assignee: Indumini Ayomi
> Priority: Minor
> Labels: queries
> Attachments: derby.log
>
>
> Create table TEST:
> create table TEST (name varchar(10), label varchar(10));
> insert into TEST values ('johnny', 'label_1');
> insert into TEST values ('johnny', 'label_2');
> These queries returns 2 rows 'JOHNNY':
> select upper(name) from TEST group by name, label
> select upper(name) from TEST group by upper(name), label
> When grouping is changed this way, query returns only 1 row 'JOHNNY':
> select upper(name) from TEST group by upper(name), upper(label)
--
This message was sent by Atlassian JIRA
(v6.4.14#64029)