db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Kathey Marsden (JIRA)" <j...@apache.org>
Subject [jira] Updated: (DERBY-3257) SELECT with HAVING clause containing OR conditional incorrectly return 1 row - should return 2 rows - works correctly with 10.2 DB
Date Fri, 18 Jan 2008 03:17:34 GMT

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

Kathey Marsden updated DERBY-3257:
----------------------------------

    Attachment: 42X24_error.sql

Here is the script to reproduce the 42X24 error message with the patch.

ij> run '42X24_error.sql';
ij> connect 'jdbc:derby:wombat;create=true';
WARNING 01J01: Database 'wombat' not created, connection made to existing database instead.
ij> drop table t1;
0 rows inserted/updated/deleted
ij> drop table t2;
0 rows inserted/updated/deleted
ij> create table t1 (c1 int, c2 int);
0 rows inserted/updated/deleted
ij> create table t2 (c1 int, c2 int);
0 rows inserted/updated/deleted
ij> create table oneRow (c1 int, c2 int);
ERROR X0Y32: Table/View 'ONEROW' already exists in Schema 'APP'.
java.sql.SQLException: Table/View 'ONEROW' already exists in Schema 'APP'.
        at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(SQLExceptionFactory.java:45)
        at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Util.java:202)
        at org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(TransactionResourceImpl.java:391)
        at org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(TransactionResourceImpl.java:346)
        at org.apache.derby.impl.jdbc.EmbedConnection.handleException(EmbedConnection.java:1666)
        at org.apache.derby.impl.jdbc.ConnectionChild.handleException(ConnectionChild.java:81)
        at org.apache.derby.impl.jdbc.EmbedStatement.executeStatement(EmbedStatement.java:1324)
        at org.apache.derby.impl.jdbc.EmbedStatement.execute(EmbedStatement.java:624)
        at org.apache.derby.impl.jdbc.EmbedStatement.execute(EmbedStatement.java:556)
        at org.apache.derby.impl.tools.ij.ij.executeImmediate(ij.java:330)
        at org.apache.derby.impl.tools.ij.utilMain.doCatch(utilMain.java:508)
        at org.apache.derby.impl.tools.ij.utilMain.runScriptGuts(utilMain.java:350)
        at org.apache.derby.impl.tools.ij.utilMain.go(utilMain.java:248)
        at org.apache.derby.impl.tools.ij.Main.go(Main.java:215)
        at org.apache.derby.impl.tools.ij.Main.mainCore(Main.java:181)
        at org.apache.derby.impl.tools.ij.Main.main(Main.java:73)
        at org.apache.derby.tools.ij.main(ij.java:59)
Caused by: ERROR X0Y32: Table/View 'ONEROW' already exists in Schema 'APP'.
        at org.apache.derby.iapi.error.StandardException.newException(StandardException.java:371)
        at org.apache.derby.impl.sql.catalog.DataDictionaryImpl.duplicateDescriptorException(DataDictionaryImpl.java:167
3)
        at org.apache.derby.impl.sql.catalog.DataDictionaryImpl.addDescriptor(DataDictionaryImpl.java:1664)
        at org.apache.derby.impl.sql.catalog.DataDictionaryImpl.addDescriptor(DataDictionaryImpl.java:1643)
        at org.apache.derby.impl.sql.execute.CreateTableConstantAction.executeConstantAction(CreateTableConstantAction.j
ava:238)
        at org.apache.derby.impl.sql.execute.MiscResultSet.open(MiscResultSet.java:64)
        at org.apache.derby.impl.sql.GenericPreparedStatement.execute(GenericPreparedStatement.java:370)
        at org.apache.derby.impl.jdbc.EmbedStatement.executeStatement(EmbedStatement.java:1234)
        ... 10 more
ij> insert into oneRow values(1,1);
1 row inserted/updated/deleted
ij> insert into t1 values (null, null), (1,1), (null, null), (2,1), (3,1), (10,10);
6 rows inserted/updated/deleted
ij> insert into t2 values (null, null), (1,1), (null, null), (2,1), (3,1), (10,10);
6 rows inserted/updated/deleted
ij> select c1 from t1
group by c1
having max(c2) in (select c1 from t2);
ERROR 42X24: Column C1 is referenced in the HAVING clause but is not in the GROUP BY list.
java.sql.SQLException: Column C1 is referenced in the HAVING clause but is not in the GROUP
BY list.
        at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(SQLExceptionFactory.java:45)
        at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Util.java:202)
        at org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(TransactionResourceImpl.java:391)
        at org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(TransactionResourceImpl.java:346)
        at org.apache.derby.impl.jdbc.EmbedConnection.handleException(EmbedConnection.java:1666)
        at org.apache.derby.impl.jdbc.ConnectionChild.handleException(ConnectionChild.java:81)
        at org.apache.derby.impl.jdbc.EmbedStatement.execute(EmbedStatement.java:613)
        at org.apache.derby.impl.jdbc.EmbedStatement.execute(EmbedStatement.java:556)
        at org.apache.derby.impl.tools.ij.ij.executeImmediate(ij.java:330)
        at org.apache.derby.impl.tools.ij.utilMain.doCatch(utilMain.java:508)
        at org.apache.derby.impl.tools.ij.utilMain.runScriptGuts(utilMain.java:350)
        at org.apache.derby.impl.tools.ij.utilMain.go(utilMain.java:248)
        at org.apache.derby.impl.tools.ij.Main.go(Main.java:215)
        at org.apache.derby.impl.tools.ij.Main.mainCore(Main.java:181)
        at org.apache.derby.impl.tools.ij.Main.main(Main.java:73)
        at org.apache.derby.tools.ij.main(ij.java:59)
Caused by: ERROR 42X24: Column C1 is referenced in the HAVING clause but is not in the GROUP
BY list.
        at org.apache.derby.iapi.error.StandardException.newException(StandardException.java:290)
        at org.apache.derby.impl.sql.compile.GroupByNode.addNewColumnsForAggregation(GroupByNode.java:529)
        at org.apache.derby.impl.sql.compile.GroupByNode.addAggregates(GroupByNode.java:237)
        at org.apache.derby.impl.sql.compile.GroupByNode.init(GroupByNode.java:181)
        at org.apache.derby.iapi.sql.compile.NodeFactory.getNode(NodeFactory.java:273)
        at org.apache.derby.impl.sql.compile.SelectNode.genProjectRestrict(SelectNode.java:1242)
        at org.apache.derby.impl.sql.compile.SelectNode.modifyAccessPaths(SelectNode.java:1816)
        at org.apache.derby.impl.sql.compile.DMLStatementNode.optimizeStatement(DMLStatementNode.java:307)
        at org.apache.derby.impl.sql.compile.CursorNode.optimizeStatement(CursorNode.java:515)
        at org.apache.derby.impl.sql.GenericStatement.prepMinion(GenericStatement.java:365)
        at org.apache.derby.impl.sql.GenericStatement.prepare(GenericStatement.java:88)
        at org.apache.derby.impl.sql.conn.GenericLanguageConnectionContext.prepareInternalStatement(GenericLanguageConne
ctionContext.java:756)
        at org.apache.derby.impl.jdbc.EmbedStatement.execute(EmbedStatement.java:607)
        ... 9 more
ij>

> SELECT with HAVING clause containing OR conditional incorrectly return 1 row - should
return 2 rows - works correctly with 10.2 DB
> ----------------------------------------------------------------------------------------------------------------------------------
>
>                 Key: DERBY-3257
>                 URL: https://issues.apache.org/jira/browse/DERBY-3257
>             Project: Derby
>          Issue Type: Bug
>          Components: SQL
>    Affects Versions: 10.3.1.4, 10.3.2.1, 10.4.0.0
>            Reporter: Stan Bradbury
>         Attachments: 42X24_error.sql, d3257_doNOTCommit.patch, derby-3257_plan_10.2.txt,
derby-3257_plan_10.4.txt, TestHaving.java
>
>
> Attached program demonstrates the problem.  Only one count is returned (matching CODE=
GBR) - the count of CODE=CHA is not returned.  Works fine with versions 10.1 and 10.2 or if
program is run using 10.3 jars and 10.2 database (soft upgrade).
> Query:
> SELECT COUNT(t0.ID) FROM CTS1.TEST_TABLE t0 
>   GROUP BY t0.CODE 
>     HAVING (t0.CODE = 'GBR' OR t0.CODE = 'CHA') AND t0.CODE IS NOT NULL
> Incorrect results (see last line):
> Database product: Apache Derby
> Database version: 10.3.1.5 - (579866)
> Driver name:      Apache Derby Embedded JDBC Driver
> Driver version:   10.3.1.5 - (579866)
> result: 2
> Correct results:
> Database product: Apache Derby
> Database version: 10.2.2.0 - (485682)
> Driver name:      Apache Derby Embedded JDBC Driver
> Driver version:   10.2.2.0 - (485682)
> result: 4
> result: 2

-- 
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.


Mime
View raw message