Hive
  1. Hive
  2. HIVE-3183

case expression should allow different types per ISO-SQL 2011

    Details

    • Type: Bug Bug
    • Status: Open
    • Priority: Major Major
    • Resolution: Unresolved
    • Affects Version/s: 0.8.0
    • Fix Version/s: None
    • Component/s: SQL
    • Labels:
      None

      Description

      The ISO-SQL standard specification for CASE allows the specification to include different types in the WHEN and ELSE blocks including this example which mixes smallint and integer types

      select case when vsint.csint is not null then vsint.csint else 1 end from cert.vsint vsint

      The Apache Hive docs do not state how it deviates from the standard or any given restrictions so unsure if this is a bug vs an enhancement. Many SQL applications mix so this seems to be a restrictive implementation if this is by design.

      Argument type mismatch '1': The expression after ELSE should have the same type as those after THEN: "smallint" is expected but "int" is found

      1. udf_when_type_wrong3.q.out
        0.4 kB
        Xiu
      2. udf_when_type_wrong2.q.out
        0.4 kB
        Xiu
      3. Hive-3183.patch.txt
        1 kB
        Xiu

        Activity

        Hide
        Xiu added a comment -

        This patch removes the restriction on 'when' clause. So some negative testcases become positive, namely:

        udf_when_type_wrong2.q
        udf_when_type_wrong3.q

        They should be moved from 'ql/src/test/queries/clientnegative/' to 'ql/src/test/queries/clientpositive/', and be renamed to reflect its positive nature.

        Also in ql/src/test/results/clientpositive/

        udf_when_type_wrong2.q.out
        udf_when_type_wrong3.q.out

        need to be added.

        Show
        Xiu added a comment - This patch removes the restriction on 'when' clause. So some negative testcases become positive, namely: udf_when_type_wrong2.q udf_when_type_wrong3.q They should be moved from 'ql/src/test/queries/clientnegative/' to 'ql/src/test/queries/clientpositive/', and be renamed to reflect its positive nature. Also in ql/src/test/results/clientpositive/ udf_when_type_wrong2.q.out udf_when_type_wrong3.q.out need to be added.
        Hide
        Ashutosh Chauhan added a comment -

        Would you like to format this as a patch and submit for review?

        Show
        Ashutosh Chauhan added a comment - Would you like to format this as a patch and submit for review?
        Hide
        N Campbell added a comment -

        I am not planning to commit code changes to Apache. I'll leave that to
        Cloudera, Hortonworks and others who are incrementally improving Hive-QL

        The intent is to enable others in the public domain to understand the bugs
        and opportunities that exist re Hive-QL types and SQL support distinct from
        working within the the supplied grammar or hand writing work arounds etc

        Show
        N Campbell added a comment - I am not planning to commit code changes to Apache. I'll leave that to Cloudera, Hortonworks and others who are incrementally improving Hive-QL The intent is to enable others in the public domain to understand the bugs and opportunities that exist re Hive-QL types and SQL support distinct from working within the the supplied grammar or hand writing work arounds etc
        Hide
        Szehon Ho added a comment -

        Seems like this is already resolved by HIVE-5825. Tried to the example in the JIRA, and issue no longer occurs.

        Show
        Szehon Ho added a comment - Seems like this is already resolved by HIVE-5825 . Tried to the example in the JIRA, and issue no longer occurs.
        Hide
        Szehon Ho added a comment -

        I guess we can resolve this one as duplicate, unless there is something of this JIRA not captured by the other?

        Show
        Szehon Ho added a comment - I guess we can resolve this one as duplicate, unless there is something of this JIRA not captured by the other?

          People

          • Assignee:
            Unassigned
            Reporter:
            N Campbell
          • Votes:
            0 Vote for this issue
            Watchers:
            5 Start watching this issue

            Dates

            • Created:
              Updated:

              Development