[ 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" + " 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. was (Author: digulla): Good. :) Is it broken in 10.5, too? This might give you a hint: [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.