You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-dev@db.apache.org by "Daniel John Debrunner (JIRA)" <ji...@apache.org> on 2008/01/23 23:36:35 UTC

[jira] Created: (DERBY-3346) DatabaseMetaData.getIndexInfo returns the wrong nullabilty for column 4 NON_UNIQUE

DatabaseMetaData.getIndexInfo returns the wrong nullabilty for column 4 NON_UNIQUE
----------------------------------------------------------------------------------

                 Key: DERBY-3346
                 URL: https://issues.apache.org/jira/browse/DERBY-3346
             Project: Derby
          Issue Type: Bug
          Components: JDBC, SQL
    Affects Versions: 10.4.0.0
            Reporter: Daniel John Debrunner
            Assignee: Daniel John Debrunner
            Priority: Minor


Derby's implementation of the NON_UNIQUE column for getIndexInfo() resolves to this expression:

(CASE WHEN CONGLOMS.DESCRIPTOR.isUnique() THEN FALSE ELSE TRUE END) AS NON_UNIQUE

The possible return values for this are TRUE or FALSE (this is internal SQL), which are both non-null constants,
thus its type should be not nullable. A similar external SQL expression by itself does return the correct nullability,
thus it seems to be a possible case where one node's nullability is affecting another. See DERBY-2775 and DERBY-3342. This like DERBY-3342 was found while fixing DERBY-2775.


Working SQL with correct nullability:
VALUES CASE WHEN 3=? THEN 1 ELSE 2 END

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


[jira] Issue Comment Edited: (DERBY-3346) DatabaseMetaData.getIndexInfo returns the wrong nullabilty for column 4 NON_UNIQUE

Posted by "Daniel John Debrunner (JIRA)" <ji...@apache.org>.
    [ https://issues.apache.org/jira/browse/DERBY-3346?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12564148#action_12564148 ] 

djd edited comment on DERBY-3346 at 1/30/08 11:40 AM:
------------------------------------------------------------------------

Fixed by Revision: 616853 for DERBY-2775

      was (Author: djd):
    Fixed by Revision: 616853 for DERBY-2275
  
> DatabaseMetaData.getIndexInfo returns the wrong nullabilty for column 4 NON_UNIQUE
> ----------------------------------------------------------------------------------
>
>                 Key: DERBY-3346
>                 URL: https://issues.apache.org/jira/browse/DERBY-3346
>             Project: Derby
>          Issue Type: Bug
>          Components: JDBC, SQL
>    Affects Versions: 10.4.0.0
>            Reporter: Daniel John Debrunner
>            Assignee: Daniel John Debrunner
>            Priority: Minor
>             Fix For: 10.4.0.0
>
>
> Derby's implementation of the NON_UNIQUE column for getIndexInfo() resolves to this expression:
> (CASE WHEN CONGLOMS.DESCRIPTOR.isUnique() THEN FALSE ELSE TRUE END) AS NON_UNIQUE
> The possible return values for this are TRUE or FALSE (this is internal SQL), which are both non-null constants,
> thus its type should be not nullable. A similar external SQL expression by itself does return the correct nullability,
> thus it seems to be a possible case where one node's nullability is affecting another. See DERBY-2775 and DERBY-3342. This like DERBY-3342 was found while fixing DERBY-2775.
> Working SQL with correct nullability:
> VALUES CASE WHEN 3=? THEN 1 ELSE 2 END

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


[jira] Commented: (DERBY-3346) DatabaseMetaData.getIndexInfo returns the wrong nullabilty for column 4 NON_UNIQUE

Posted by "Daniel John Debrunner (JIRA)" <ji...@apache.org>.
    [ https://issues.apache.org/jira/browse/DERBY-3346?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12562174#action_12562174 ] 

Daniel John Debrunner commented on DERBY-3346:
----------------------------------------------

Hit another problem with the NON_UNIQUE column. Changes for DERBY-2775 fix it for the JDBC query  but the ODBC query continues to indicate the column is nullable. This is because the JDBC column is CAST for the ODBC query.

The CAST operator incorrectly always sets its type to  nullable, even when the source is not nullable.
E.g.
  expression: 'a' - not nullable
  expression CAST ('a' AS CHAR(4)) - nullable

Simple tests appear to show the CAST is working correctly:
  expresion: 3 - not nullable
  expression CAST (3 AS SMALLINT) - not nullable

I think this is because the bind is removing the cast node and modifying the constant node's type, as the code for CastNode always seems to set its type to be nullable.

> DatabaseMetaData.getIndexInfo returns the wrong nullabilty for column 4 NON_UNIQUE
> ----------------------------------------------------------------------------------
>
>                 Key: DERBY-3346
>                 URL: https://issues.apache.org/jira/browse/DERBY-3346
>             Project: Derby
>          Issue Type: Bug
>          Components: JDBC, SQL
>    Affects Versions: 10.4.0.0
>            Reporter: Daniel John Debrunner
>            Assignee: Daniel John Debrunner
>            Priority: Minor
>
> Derby's implementation of the NON_UNIQUE column for getIndexInfo() resolves to this expression:
> (CASE WHEN CONGLOMS.DESCRIPTOR.isUnique() THEN FALSE ELSE TRUE END) AS NON_UNIQUE
> The possible return values for this are TRUE or FALSE (this is internal SQL), which are both non-null constants,
> thus its type should be not nullable. A similar external SQL expression by itself does return the correct nullability,
> thus it seems to be a possible case where one node's nullability is affecting another. See DERBY-2775 and DERBY-3342. This like DERBY-3342 was found while fixing DERBY-2775.
> Working SQL with correct nullability:
> VALUES CASE WHEN 3=? THEN 1 ELSE 2 END

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


[jira] Commented: (DERBY-3346) DatabaseMetaData.getIndexInfo returns the wrong nullabilty for column 4 NON_UNIQUE

Posted by "Daniel John Debrunner (JIRA)" <ji...@apache.org>.
    [ https://issues.apache.org/jira/browse/DERBY-3346?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12561880#action_12561880 ] 

Daniel John Debrunner commented on DERBY-3346:
----------------------------------------------

and for column 10 ASC_OR_DESC (same issue as NON_UNIQUE)

> DatabaseMetaData.getIndexInfo returns the wrong nullabilty for column 4 NON_UNIQUE
> ----------------------------------------------------------------------------------
>
>                 Key: DERBY-3346
>                 URL: https://issues.apache.org/jira/browse/DERBY-3346
>             Project: Derby
>          Issue Type: Bug
>          Components: JDBC, SQL
>    Affects Versions: 10.4.0.0
>            Reporter: Daniel John Debrunner
>            Assignee: Daniel John Debrunner
>            Priority: Minor
>
> Derby's implementation of the NON_UNIQUE column for getIndexInfo() resolves to this expression:
> (CASE WHEN CONGLOMS.DESCRIPTOR.isUnique() THEN FALSE ELSE TRUE END) AS NON_UNIQUE
> The possible return values for this are TRUE or FALSE (this is internal SQL), which are both non-null constants,
> thus its type should be not nullable. A similar external SQL expression by itself does return the correct nullability,
> thus it seems to be a possible case where one node's nullability is affecting another. See DERBY-2775 and DERBY-3342. This like DERBY-3342 was found while fixing DERBY-2775.
> Working SQL with correct nullability:
> VALUES CASE WHEN 3=? THEN 1 ELSE 2 END

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


[jira] Closed: (DERBY-3346) DatabaseMetaData.getIndexInfo returns the wrong nullabilty for column 4 NON_UNIQUE

Posted by "Daniel John Debrunner (JIRA)" <ji...@apache.org>.
     [ https://issues.apache.org/jira/browse/DERBY-3346?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Daniel John Debrunner closed DERBY-3346.
----------------------------------------

       Resolution: Fixed
    Fix Version/s: 10.4.0.0

Fixed by Revision: 616853 for DERBY-2275

> DatabaseMetaData.getIndexInfo returns the wrong nullabilty for column 4 NON_UNIQUE
> ----------------------------------------------------------------------------------
>
>                 Key: DERBY-3346
>                 URL: https://issues.apache.org/jira/browse/DERBY-3346
>             Project: Derby
>          Issue Type: Bug
>          Components: JDBC, SQL
>    Affects Versions: 10.4.0.0
>            Reporter: Daniel John Debrunner
>            Assignee: Daniel John Debrunner
>            Priority: Minor
>             Fix For: 10.4.0.0
>
>
> Derby's implementation of the NON_UNIQUE column for getIndexInfo() resolves to this expression:
> (CASE WHEN CONGLOMS.DESCRIPTOR.isUnique() THEN FALSE ELSE TRUE END) AS NON_UNIQUE
> The possible return values for this are TRUE or FALSE (this is internal SQL), which are both non-null constants,
> thus its type should be not nullable. A similar external SQL expression by itself does return the correct nullability,
> thus it seems to be a possible case where one node's nullability is affecting another. See DERBY-2775 and DERBY-3342. This like DERBY-3342 was found while fixing DERBY-2775.
> Working SQL with correct nullability:
> VALUES CASE WHEN 3=? THEN 1 ELSE 2 END

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