[
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.
