Uploaded image for project: 'Couchbase Server'
  1. Couchbase Server
  2. MB-28865

Query throws an error if case expression is not kept in braces inside where clause.

    XMLWordPrintable

Details

    • Untriaged
    • No

    Description

      Query:

      SELECT join_mo,name,CASE WHEN join_mo < 3 OR join_mo > 11 THEN 'winter' ELSE 'other' END AS period FROM default_shadow 
      WHERE CASE WHEN join_mo < 3 OR join_mo > 11 THEN 'winter' ELSE 'other' END LIKE 'win%'

      Error:

      [
        {
          "code": 1,
          "msg": "Conversion = '''",
          "query_from_user": "SELECT join_mo,name,CASE WHEN join_mo < 3 OR join_mo > 11 THEN 'winter' ELSE 'other' END AS period FROM default_shadow \nWHERE CASE WHEN join_mo < 3 OR join_mo > 11 THEN 'winter' ELSE 'other' END LIKE 'win%'"
        }
      ]
      

      But on simply putting the case expression in where in braces, query works:

      SELECT join_mo,name,CASE WHEN join_mo < 3 OR join_mo > 11 THEN 'winter' ELSE 'other' END AS period FROM default_shadow 
      WHERE (CASE WHEN join_mo < 3 OR join_mo > 11 THEN 'winter' ELSE 'other' END) LIKE 'win%'

      Query works fine in N1QL both ways.

      Attachments

        No reviews matched the request. Check your Options in the drop-down menu of this sections header.

        Activity

          dmitry.lychagin Dmitry Lychagin (Inactive) added a comment - Open source tracking issue: https://issues.apache.org/jira/browse/ASTERIXDB-2356  

          Tried below

          SELECT
          CASE WHEN get_year(current_date()) > 0 THEN "abc" ELSE "def" END LIKE "a%" as v1
          

          and the query in Ticket description gives no error

          tanzeem.ahmed Tanzeem Ahmed (Inactive) added a comment - Tried below SELECT CASE WHEN get_year(current_date()) > 0 THEN "abc" ELSE "def" END LIKE "a%" as v1 and the query in Ticket description gives no error

          People

            dmitry.lychagin Dmitry Lychagin (Inactive)
            ritesh.agarwal Ritesh Agarwal
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Gerrit Reviews

                There are no open Gerrit changes

                PagerDuty