Derby
  1. Derby
  2. DERBY-2002

Case expression allows NULL in all parts of <result>

    Details

    • Urgency:
      Normal
    • Issue & fix info:
      Release Note Needed, Repro attached
    • Bug behavior facts:
      Deviation from standard

      Description

      According to the SQL:2003 spec, section 6.11 <case expression> Syntax Rule 3. At least one <result> in a
      <case specification> shall specify a <result expression>. Derby currently is violating this rule. e.g.:

      ij> values case when 1=2 then NULL when 1=3 then NULL else NULL end;
      1


      NULL

      1 row selected

      6.11 <case expression> Format section defines <result> as the following:

      <result> ::=
      <result expression>

      NULL

      The above statement should have thrown a SQLException instead of returning a result.

      sysinfo:

      ------------------ Java Information ------------------
      Java Version: 1.4.2_12
      Java Vendor: Sun Microsystems Inc.
      Java home: C:\jdk142\jre
      Java classpath: classes;.
      OS name: Windows XP
      OS architecture: x86
      OS version: 5.1
      Java user name: yip
      Java user home: C:\Documents and Settings\Administrator
      Java user dir: C:\derby\trunk
      java.specification.name: Java Platform API Specification
      java.specification.version: 1.4
      --------- Derby Information --------
      JRE - JDBC: J2SE 1.4.2 - JDBC 3.0
      [C:\derby\trunk\classes] 10.3.0.0 alpha - (1)
      ------------------------------------------------------
      ----------------- Locale Information -----------------
      Current Locale : [English/United States [en_US]]
      Found support for locale: [de_DE]
      version: 10.3.0.0 alpha - (1)
      Found support for locale: [es]
      version: 10.3.0.0 alpha - (1)
      Found support for locale: [fr]
      version: 10.3.0.0 alpha - (1)
      Found support for locale: [it]
      version: 10.3.0.0 alpha - (1)
      Found support for locale: [ja_JP]
      version: 10.3.0.0 alpha - (1)
      Found support for locale: [ko_KR]
      version: 10.3.0.0 alpha - (1)
      Found support for locale: [pt_BR]
      version: 10.3.0.0 alpha - (1)
      Found support for locale: [zh_CN]
      version: 10.3.0.0 alpha - (1)
      Found support for locale: [zh_TW]
      version: 10.3.0.0 alpha - (1)
      ------------------------------------------------------

      1. d2002-1a.diff
        9 kB
        Knut Anders Hatlen
      2. releaseNote.html
        5 kB
        Knut Anders Hatlen

        Activity

        Hide
        Christian d'Heureuse added a comment -

        ISO/IEC 9075-1 6.3.3.2 states:
        "In the Syntax Rules, the term shall defines conditions that are required to be true of syntactically conforming
        SQL language. ... The treatment of language that does not conform to the SQL Formats and Syntax Rules is implementation-dependent. ..."

        As I understand that, an implementation is free to accept non-conforming SQL syntax. So there is no need to generate an SQLException when that syntax rule is violated because all <result>s are NULL.in the CASE expression.

        Show
        Christian d'Heureuse added a comment - ISO/IEC 9075-1 6.3.3.2 states: "In the Syntax Rules, the term shall defines conditions that are required to be true of syntactically conforming SQL language. ... The treatment of language that does not conform to the SQL Formats and Syntax Rules is implementation-dependent. ..." As I understand that, an implementation is free to accept non-conforming SQL syntax. So there is no need to generate an SQLException when that syntax rule is violated because all <result>s are NULL.in the CASE expression.
        Hide
        Yip Ng added a comment -

        Right, an implementation is free to have their own proprietary syntax. However, in 6.3.3.2 of ISO/IEC 9075-1 6.3.3.2, it also states that:

        "If any condition required by Syntax Rules is not satisfied when the evaluation of Access or General Rules is attempted and the implementation is neither processing non-conforming SQL language nor processing conforming SQL language in a nonconforming manner, then an exception condition is raised: syntax error or access rule violation."

        So to be conformant, an exception should be raised.

        Show
        Yip Ng added a comment - Right, an implementation is free to have their own proprietary syntax. However, in 6.3.3.2 of ISO/IEC 9075-1 6.3.3.2, it also states that: "If any condition required by Syntax Rules is not satisfied when the evaluation of Access or General Rules is attempted and the implementation is neither processing non-conforming SQL language nor processing conforming SQL language in a nonconforming manner, then an exception condition is raised: syntax error or access rule violation." So to be conformant, an exception should be raised.
        Hide
        Christian d'Heureuse added a comment -

        > So to be conformant, an exception should be raised.

        No,, it says "... and the implementation is neither processing non-conforming SQL language ...".
        When an implementation is "processing non-conforming SQL language", which is the case when Derby processes a CASE statement with all NULLs, it does not have to raise an exception.

        If it was otherwise, every proprietary syntax would be forbidden.

        Show
        Christian d'Heureuse added a comment - > So to be conformant, an exception should be raised. No,, it says "... and the implementation is neither processing non-conforming SQL language ...". When an implementation is "processing non-conforming SQL language", which is the case when Derby processes a CASE statement with all NULLs, it does not have to raise an exception. If it was otherwise, every proprietary syntax would be forbidden.
        Hide
        Yip Ng added a comment -

        I do not see anything proprietary about CASE expression or there are any proprietary extensions to that expression in Derby. The CASE expression is a conforming SQL language described in the SQL:2003 spec. So the implementation is trying to process a conforming SQL language in a nonconforming manner, so it should raise a syntax exception.

        Moreover, if all parts of the <result> are defined as NULL, what is the resultant datatype?

        RDBMSs that conform to the SQL spec will not allow this.

        Show
        Yip Ng added a comment - I do not see anything proprietary about CASE expression or there are any proprietary extensions to that expression in Derby. The CASE expression is a conforming SQL language described in the SQL:2003 spec. So the implementation is trying to process a conforming SQL language in a nonconforming manner, so it should raise a syntax exception. Moreover, if all parts of the <result> are defined as NULL, what is the resultant datatype? RDBMSs that conform to the SQL spec will not allow this.
        Hide
        Yip Ng added a comment -

        I think the conflict here is that you see this as a proprietary case expression behavior in Derby where as I see this a bug since the current implementation of case expression does not conform to the SQL:2003 spec.

        Show
        Yip Ng added a comment - I think the conflict here is that you see this as a proprietary case expression behavior in Derby where as I see this a bug since the current implementation of case expression does not conform to the SQL:2003 spec.
        Hide
        Christian d'Heureuse added a comment -

        > So the implementation is trying to process a conforming SQL language in a nonconforming
        > manner, so it should raise a syntax exception.

        No, the contrary is true. The standard says "... and the implementation is neither ... nor processing conforming SQL language in a nonconforming manner, then an exception condition is raised" (note the "nor" in the sentence), This means that if the "implementation is trying to process a conforming SQL language in a nonconforming manner" (as you have written), it does not have to raise an exception.

        The purpose of the conformance rules of the SQL standard is to define a common subset of SQL that is portable among the DBMS. Non-conforming SQL may be detected by an SQL flagger (see ISO 9075-1 8.5), but it's not forbidden for a conforming implementation. The standard only demands that conforming SQL behaves in the predicted way. All DBMS have non-conforming SQL syntax.

        > think the conflict here is that you see this as a proprietary case expression behavior
        > in Derby where as I see this a bug since the current implementation of case expression
        > does not conform to the SQL:2003 spec.

        I agree that a CASE statement with all NULLs does not conform to the syntax rule of the SQL standard and therefore it's not conforming SQL. But a conforming implementation is allowed to process non-conforming SQL without raising an exception.

        ISO 9075-1 8.6.2 (Requirements for SQL-implementations) states:
        "A conforming SQL-implementation shall process conforming SQL language according to the associated General Rules, Definitions, and Descriptions."
        This means that for conforming SQL (the common subset of the DBMS), the implementation must obey the rules. But for non-conforming SQL, the implementation is free to do what it likes.

        Show
        Christian d'Heureuse added a comment - > So the implementation is trying to process a conforming SQL language in a nonconforming > manner, so it should raise a syntax exception. No, the contrary is true. The standard says "... and the implementation is neither ... nor processing conforming SQL language in a nonconforming manner, then an exception condition is raised" (note the "nor" in the sentence), This means that if the "implementation is trying to process a conforming SQL language in a nonconforming manner" (as you have written), it does not have to raise an exception. The purpose of the conformance rules of the SQL standard is to define a common subset of SQL that is portable among the DBMS. Non-conforming SQL may be detected by an SQL flagger (see ISO 9075-1 8.5), but it's not forbidden for a conforming implementation. The standard only demands that conforming SQL behaves in the predicted way. All DBMS have non-conforming SQL syntax. > think the conflict here is that you see this as a proprietary case expression behavior > in Derby where as I see this a bug since the current implementation of case expression > does not conform to the SQL:2003 spec. I agree that a CASE statement with all NULLs does not conform to the syntax rule of the SQL standard and therefore it's not conforming SQL. But a conforming implementation is allowed to process non-conforming SQL without raising an exception. ISO 9075-1 8.6.2 (Requirements for SQL-implementations) states: "A conforming SQL-implementation shall process conforming SQL language according to the associated General Rules, Definitions, and Descriptions." This means that for conforming SQL (the common subset of the DBMS), the implementation must obey the rules. But for non-conforming SQL, the implementation is free to do what it likes.
        Hide
        Christian d'Heureuse added a comment -

        > The CASE expression is a conforming SQL language described in the SQL:2003 spec.
        > So the implementation is trying to process a conforming SQL language in a nonconforming
        > manner, ...

        I even don't agree with that.
        A CASE statement with all NULL values violates syntax rule 3.
        Therefore the expression
        case when 1=2 then NULL when 1=3 then NULL else NULL end;
        is syntactically not conforming, because it breaks a syntax rule.
        Therefore this CASE expression is non-conforming SQL.

        Show
        Christian d'Heureuse added a comment - > The CASE expression is a conforming SQL language described in the SQL:2003 spec. > So the implementation is trying to process a conforming SQL language in a nonconforming > manner, ... I even don't agree with that. A CASE statement with all NULL values violates syntax rule 3. Therefore the expression case when 1=2 then NULL when 1=3 then NULL else NULL end; is syntactically not conforming, because it breaks a syntax rule. Therefore this CASE expression is non-conforming SQL.
        Hide
        Yip Ng added a comment -

        >I agree that a CASE statement with all NULLs does not conform to the syntax rule of the SQL standard and >therefore it's not conforming SQL. But a conforming implementation is allowed to process non-conforming SQL >without raising an exception.

        Right, I am not disagreeing with you here. Of course, the implementation is free from choosing how to handle non-conforming SQL. We both agree that the CASE expression with all NULLs violates syntax rule 3, making it non-conforming SQL which it is the reason why I filed this jira in the first place - to make it conforming SQL. So from a conforming perspective, then it should raise the syntax exception which is what I tried to say in my previous comment.

        But I have to admit that the current behavior is somewhat awkward since the implementation has no way of deducing the resultant data type returned by such an expresson which will force the implementation to default to some default datatype. Not very portable for sure. =)

        If it is not handled in the conforming manner, then it should be documented in the reference manual so that there are no surprises.

        Show
        Yip Ng added a comment - >I agree that a CASE statement with all NULLs does not conform to the syntax rule of the SQL standard and >therefore it's not conforming SQL. But a conforming implementation is allowed to process non-conforming SQL >without raising an exception. Right, I am not disagreeing with you here. Of course, the implementation is free from choosing how to handle non-conforming SQL. We both agree that the CASE expression with all NULLs violates syntax rule 3, making it non-conforming SQL which it is the reason why I filed this jira in the first place - to make it conforming SQL . So from a conforming perspective, then it should raise the syntax exception which is what I tried to say in my previous comment. But I have to admit that the current behavior is somewhat awkward since the implementation has no way of deducing the resultant data type returned by such an expresson which will force the implementation to default to some default datatype. Not very portable for sure. =) If it is not handled in the conforming manner, then it should be documented in the reference manual so that there are no surprises.
        Hide
        Christian d'Heureuse added a comment -

        > the implementation has no way of deducing the resultant data
        > type returned by such an expression

        Such a CASE expression could be replaced by an untyped NULL constant, while the expression is parsed and optimized.

        > If it is not handled in the conforming manner, then it should be documented
        > in the reference manual so that there are no surprises.

        I think the main problem is the lack of an SQL flagger (see ISO 9075-1 8.5) in Derby.

        Show
        Christian d'Heureuse added a comment - > the implementation has no way of deducing the resultant data > type returned by such an expression Such a CASE expression could be replaced by an untyped NULL constant, while the expression is parsed and optimized. > If it is not handled in the conforming manner, then it should be documented > in the reference manual so that there are no surprises. I think the main problem is the lack of an SQL flagger (see ISO 9075-1 8.5) in Derby.
        Hide
        Yip Ng added a comment -

        >Such a CASE expression could be replaced by an untyped NULL constant, while the expression is parsed
        >and optimized.

        If the untyped NULL is returned, then the following CREATE VIEW should fail and force the users to do a CAST but currently this executes successfully in Derby.

        IMHO, I really think that the system should avoid these cases by following the conforming way and not allow all parts of <result> to be NULL in the CASE expression. But I'll let the community decide if this jira should be pursue.

        ij> create view v1(c1) as values case when 1=2 then null else null end;
        0 rows inserted/updated/deleted
        ij> select * from v1;
        C1


        NULL

        1 row selected
        ij> describe v1;
        COLUMN_NAME |TYPE_NAME|DEC&|NUM&|COLUM&|COLUMN_DEF|CHAR_OCTE&|IS_NULL&
        ------------------------------------------------------------------------------
        C1 |CHAR |NULL|NULL|1 |NULL |2 |YES

        1 row selected

        Show
        Yip Ng added a comment - >Such a CASE expression could be replaced by an untyped NULL constant, while the expression is parsed >and optimized. If the untyped NULL is returned, then the following CREATE VIEW should fail and force the users to do a CAST but currently this executes successfully in Derby. IMHO, I really think that the system should avoid these cases by following the conforming way and not allow all parts of <result> to be NULL in the CASE expression. But I'll let the community decide if this jira should be pursue. ij> create view v1(c1) as values case when 1=2 then null else null end; 0 rows inserted/updated/deleted ij> select * from v1; C1 NULL 1 row selected ij> describe v1; COLUMN_NAME |TYPE_NAME|DEC&|NUM&|COLUM&|COLUMN_DEF|CHAR_OCTE&|IS_NULL& ------------------------------------------------------------------------------ C1 |CHAR |NULL|NULL|1 |NULL |2 |YES 1 row selected
        Hide
        Christian d'Heureuse added a comment -

        The statement
        CREATE VIEW v1(c1) AS VALUES NULL;
        produces a NullPointerException...

        Show
        Christian d'Heureuse added a comment - The statement CREATE VIEW v1(c1) AS VALUES NULL; produces a NullPointerException...
        Hide
        Yip Ng added a comment -

        Christian wrote:
        >The statement
        > CREATE VIEW v1(c1) AS VALUES NULL;
        >produces a NullPointerException...

        I don't see any existing jira entry for this, so looks like you find a new bug. =)

        Yip wrote:
        >So the implementation is trying to process a conforming SQL language in a nonconforming manner,
        >so it should raise a syntax exception.

        Sorry for the confusion/contradiction here, I wrote this in a haste. What I was trying to convey is:

        The original intent of this jira is to not allow NULLs in ALL parts of <result> in the case expression so that
        it conforms to the SQL:2003 spec, section 6.11 <case expression> Syntax rule 3. And by processing this in
        a conforming manner, a syntax exception should be raised.

        Show
        Yip Ng added a comment - Christian wrote: >The statement > CREATE VIEW v1(c1) AS VALUES NULL; >produces a NullPointerException... I don't see any existing jira entry for this, so looks like you find a new bug. =) Yip wrote: >So the implementation is trying to process a conforming SQL language in a nonconforming manner, >so it should raise a syntax exception. Sorry for the confusion/contradiction here, I wrote this in a haste. What I was trying to convey is: The original intent of this jira is to not allow NULLs in ALL parts of <result> in the case expression so that it conforms to the SQL:2003 spec, section 6.11 <case expression> Syntax rule 3. And by processing this in a conforming manner, a syntax exception should be raised.
        Hide
        Knut Anders Hatlen added a comment -

        Currently, if the type of an untyped NULL in a case expression cannot be inferred, it defaults to CHAR(1). Derby also allows untyped parameters in the case result clauses, but the type inference is different for them. This leads to some confusing behaviour:

        Examples:

        1) CASE WHEN x THEN NULL ELSE NULL END evaluates to a value of type CHAR(1)

        2) CASE WHEN x THEN ? ELSE ? END produces an error:

        ERROR 42X87: At least one result expression (THEN or ELSE) of the 'conditional' expression must not be a '?'.

        3) CASE WHEN x THEN ? END evaluates to a value of type CHAR(1). It gets the type from the implicit ELSE NULL clause. However, even though the metadata says the type is CHAR(1), the parameter accepts longer values, and the returned value will in that case be longer than the metadata says it can be.

        4) CASE WHEN x THEN CAST(? AS CHAR(1)) END evaluates to a value of type CHAR(1). However, unlike (3), when the parameter is given a longer value, that value will be truncated to length 1, and a data truncation warning will be added to the ResultSet.

        I think it would be reasonable to expect cases 1, 2 and 3 to have the same behaviour. Implying the type CHAR(1) when no type is specified feels somewhat arbitrary, so I'm leaning towards raising an error and let the users disambiguate their queries.

        If, however, making 1, 2 and 3 all use type CHAR(1) ends up being the preferred solution, I think the behaviour of 4 (truncate longer values and produce a warning) is less wrong than returning a value that is longer than its metadata permits.

        Show
        Knut Anders Hatlen added a comment - Currently, if the type of an untyped NULL in a case expression cannot be inferred, it defaults to CHAR(1). Derby also allows untyped parameters in the case result clauses, but the type inference is different for them. This leads to some confusing behaviour: Examples: 1) CASE WHEN x THEN NULL ELSE NULL END evaluates to a value of type CHAR(1) 2) CASE WHEN x THEN ? ELSE ? END produces an error: ERROR 42X87: At least one result expression (THEN or ELSE) of the 'conditional' expression must not be a '?'. 3) CASE WHEN x THEN ? END evaluates to a value of type CHAR(1). It gets the type from the implicit ELSE NULL clause. However, even though the metadata says the type is CHAR(1), the parameter accepts longer values, and the returned value will in that case be longer than the metadata says it can be. 4) CASE WHEN x THEN CAST(? AS CHAR(1)) END evaluates to a value of type CHAR(1). However, unlike (3), when the parameter is given a longer value, that value will be truncated to length 1, and a data truncation warning will be added to the ResultSet. I think it would be reasonable to expect cases 1, 2 and 3 to have the same behaviour. Implying the type CHAR(1) when no type is specified feels somewhat arbitrary, so I'm leaning towards raising an error and let the users disambiguate their queries. If, however, making 1, 2 and 3 all use type CHAR(1) ends up being the preferred solution, I think the behaviour of 4 (truncate longer values and produce a warning) is less wrong than returning a value that is longer than its metadata permits.
        Hide
        Knut Anders Hatlen added a comment -

        The attached patch d2002-1a.diff shows how we can forbid CASE expressions with unknown return type. It removes the code that makes these expressions get type CHAR(1) and instead raises the same error condition as is currently raised if all THEN/ELSE expressions are untyped parameters. The error message is changed from

        ERROR 42X87: At least one result expression (THEN or ELSE) of the 'conditional' expression must not be a '?'.

        to

        ERROR 42X87: At least one result expression (THEN or ELSE) of the CASE expression must have a known type.

        The patch also adds test cases to CaseExpressionTest that verify the fix.

        All regression tests passed with the patch.

        Show
        Knut Anders Hatlen added a comment - The attached patch d2002-1a.diff shows how we can forbid CASE expressions with unknown return type. It removes the code that makes these expressions get type CHAR(1) and instead raises the same error condition as is currently raised if all THEN/ELSE expressions are untyped parameters. The error message is changed from ERROR 42X87: At least one result expression (THEN or ELSE) of the 'conditional' expression must not be a '?'. to ERROR 42X87: At least one result expression (THEN or ELSE) of the CASE expression must have a known type. The patch also adds test cases to CaseExpressionTest that verify the fix. All regression tests passed with the patch.
        Hide
        Knut Anders Hatlen added a comment -

        Attached is a first stab at a release note for this issue.

        Show
        Knut Anders Hatlen added a comment - Attached is a first stab at a release note for this issue.
        Hide
        ASF subversion and git services added a comment -

        Commit 1599142 from Knut Anders Hatlen in branch 'code/trunk'
        [ https://svn.apache.org/r1599142 ]

        DERBY-2002: Case expression allows NULL in all parts of <result>

        Require at least one expression with a known type (that is, not NULL or
        a ? parameter) in the THEN and ELSE clauses of a CASE expression.

        Show
        ASF subversion and git services added a comment - Commit 1599142 from Knut Anders Hatlen in branch 'code/trunk' [ https://svn.apache.org/r1599142 ] DERBY-2002 : Case expression allows NULL in all parts of <result> Require at least one expression with a known type (that is, not NULL or a ? parameter) in the THEN and ELSE clauses of a CASE expression.

          People

          • Assignee:
            Knut Anders Hatlen
            Reporter:
            Yip Ng
          • Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved:

              Development