db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Aaron Digulla (JIRA)" <j...@apache.org>
Subject [jira] Issue Comment Edited: (DERBY-4071) AssertFailure when selecting rows from a table with CHARACTER and VARCHAR columns
Date Thu, 26 Feb 2009 08:25:01 GMT

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


Mime
View raw message