[
https://issues.apache.org/jira/browse/DERBY-4071?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12676912#action_12676912
]
digulla edited comment on DERBY-4071 at 2/26/09 12:23 AM:
----------------------------------------------------------------
Good. :) Is it broken in 10.5, too?
I've found another version that works:
[code]
sql = "SELECT *\r\n" +
"FROM DEMO.TEST2 S\r\n" +
"WHERE S.VCHR IN (\r\n" +
" SELECT VCHR\r\n" +
" FROM DEMO.TEST2\r\n" +
" GROUP BY VCHR\r\n" +
" HAVING COUNT (VCHR) > 1 \r\n" +
")\r\n" +
" AND S.CHR NOT IN (\r\n" + // Table S
" SELECT MAX(T.CHR)\r\n" + // Table T
" FROM DEMO.TEST2 T\r\n" +
" WHERE S.VCHR = T.VCHR\r\n" +
" GROUP BY T.VCHR\r\n" +
" HAVING COUNT(T.VCHR) > 1 \r\n" +
")";
i = dump (sql);
assertEquals (1, i);
[code]
Change: I prefix every CHR column with the table alias. It seems that Derby
somehow mixes the columns in the second sub-SELECT.
was (Author: digulla):
Good. :) Is it broken in 10.5, too?
I've found another version that works:
[code]
sql = "SELECT *\r\n" +
"FROM DEMO.TEST2 S\r\n" +
"WHERE S.VCHR IN (\r\n" +
" SELECT VCHR\r\n" +
" FROM DEMO.TEST2\r\n" +
" GROUP BY VCHR\r\n" +
" HAVING COUNT (VCHR) > 1 \r\n" +
")\r\n" +
" AND S.CHR NOT IN (\r\n" +
" SELECT MAX(T.CHR)\r\n" +
" FROM DEMO.TEST2 T\r\n" +
" WHERE S.VCHR = T.VCHR\r\n" +
" GROUP BY T.VCHR\r\n" +
" HAVING COUNT(T.VCHR) > 1 \r\n" +
")";
i = dump (sql);
assertEquals (1, i);
[code]
Change: I prefix every CHR column with the table alias. It seems that Derby
somehow mixes the columns in the second sub-SELECT.
> AssertFailure when selecting rows from a table with CHARACTER and VARCHAR
> columns
> ---------------------------------------------------------------------------------
>
> Key: DERBY-4071
> URL: https://issues.apache.org/jira/browse/DERBY-4071
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.4.2.0
> Reporter: Aaron Digulla
> Attachments: DerbyTest.java
>
>
> When running a complex query on this table:
> [code]
> Create table DEMO.TEST (
> CHR CHARACTER(26) ,
> VCHR VARCHAR(25) )
> [code]
> then I get this exception:
> AssertFailure: ASSERT FAILED col1.getClass() (class ...SQLChar) expected to
> be the same as col2.getClass() (class ....SQLVarchar)' was thrown while
> evaluating an expression.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
