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

SQL++ Alignment: UNION ALL is not working for all the query types. Keyword error UNION.

    XMLWordPrintable

    Details

    • Triage:
      Untriaged
    • Epic Link:
    • Is this a Regression?:
      Unknown
    • Sprint:
      CX Sprint 96, CX Sprint 97, CX Sprint 116, CX Sprint 117

      Description

      Analytics Query:

      (SELECT * FROM  simple_table_db_83597457_simple_table_shadow   WHERE  ((((decimal_field1 > 4912) OR (decimal_field1 = 4912)) OR (int_field1 = 52000873) OR char_field1 < "N")) OR (bool_field1 != false)    ORDER BY   int_field1  DESC)  
      UNION ALL 
      (SELECT * FROM  simple_table_db_83597457_simple_table_shadow   WHERE  ((primary_key_id NOT BETWEEN "1" and "999" AND decimal_field1 >= 4912)) AND ((bool_field1 OR (int_field1  IN [  44752 , 55191 , 56574 , 57419 , 544073  ]) OR (int_field1 IS NULL))) OR ((int_field1 < 52000873) AND (int_field1 > 52000873)) AND ((bool_field1 = false AND decimal_field1 IS NOT NULL))    ORDER BY   int_field1 , decimal_field1 ,  varchar_field1 , char_field1 )
      

      When I run the 2 queries separately I am getting the results. The first query generated some data and the second query returns empty data "results": [].

      Error:

      [
        {
          "code": 1,
          "msg": "Syntax error: In line 2 >>UNION ALL << Encountered \"UNION\" at column 1. ",
          "query_from_user": "(SELECT * FROM  simple_table_db_83597457_simple_table_shadow   WHERE  ((((decimal_field1 > 4912) OR (decimal_field1 = 4912)) OR (int_field1 = 52000873) OR char_field1 < \"N\")) OR (bool_field1 != false)    ORDER BY   int_field1  DESC)  \nUNION ALL \n(SELECT * FROM  simple_table_db_83597457_simple_table_shadow   WHERE  ((primary_key_id NOT BETWEEN \"1\" and \"999\" AND decimal_field1 >= 4912)) AND ((bool_field1 OR (int_field1  IN [  44752 , 55191 , 56574 , 57419 , 544073  ]) OR (int_field1 IS NULL))) OR ((int_field1 < 52000873) AND (int_field1 > 52000873)) AND ((bool_field1 = false AND decimal_field1 IS NOT NULL))    ORDER BY   int_field1 , decimal_field1 ,  varchar_field1 , char_field1 )"
        }
      ]
      

      The same query works fine on SQL.

        Attachments

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

          Activity

          Hide
          ritesh.agarwal Ritesh Agarwal added a comment - - edited

          Failing CBAS Query-1:

          (SELECT * FROM  simple_table_db_83597457_simple_table_shadow   WHERE  NOT (primary_key_id IS NULL)    ORDER BY   decimal_field1 )
          UNION ALL 
          (SELECT * FROM  simple_table_db_83597457_simple_table_shadow   WHERE  ((bool_field1 = true AND decimal_field1 IS NOT NULL)) AND ((bool_field1 != false OR decimal_field1 != 4912))    ORDER BY   int_field1 ,  primary_key_id , varchar_field1 , char_field1 )
          

          But if i remove Order By from the first query then it works:

          SELECT * FROM  simple_table_db_83597457_simple_table_shadow   WHERE  NOT (primary_key_id IS NULL)
          UNION ALL 
          SELECT * FROM  simple_table_db_83597457_simple_table_shadow   WHERE  ((bool_field1 = true AND decimal_field1 IS NOT NULL)) AND ((bool_field1 != false OR decimal_field1 != 4912))    ORDER BY   int_field1 ,  primary_key_id , varchar_field1 , char_field1
          

          Show
          ritesh.agarwal Ritesh Agarwal added a comment - - edited Failing CBAS Query-1: (SELECT * FROM simple_table_db_83597457_simple_table_shadow WHERE NOT (primary_key_id IS NULL) ORDER BY decimal_field1 ) UNION ALL (SELECT * FROM simple_table_db_83597457_simple_table_shadow WHERE ((bool_field1 = true AND decimal_field1 IS NOT NULL)) AND ((bool_field1 != false OR decimal_field1 != 4912)) ORDER BY int_field1 , primary_key_id , varchar_field1 , char_field1 ) But if i remove Order By from the first query then it works: SELECT * FROM simple_table_db_83597457_simple_table_shadow WHERE NOT (primary_key_id IS NULL) UNION ALL SELECT * FROM simple_table_db_83597457_simple_table_shadow WHERE ((bool_field1 = true AND decimal_field1 IS NOT NULL)) AND ((bool_field1 != false OR decimal_field1 != 4912)) ORDER BY int_field1 , primary_key_id , varchar_field1 , char_field1
          Hide
          till Till Westmann added a comment -

          It seems the replacing the first SelectBlock in the SelectSetOperation production:

          SelectSetOperation	::=	SelectBlock ( ( <UNION> | <INTERSECT> | <EXCEPT> ) ( <ALL> )? ( SelectBlock | Subquery ) )*
          

          with ( SelectBlock | Subquery ) would resolve the incompatibility.
          This is not critical for Vulcan, can be release-noted.

          Show
          till Till Westmann added a comment - It seems the replacing the first SelectBlock in the SelectSetOperation production: SelectSetOperation ::= SelectBlock ( ( <UNION> | <INTERSECT> | <EXCEPT> ) ( <ALL> )? ( SelectBlock | Subquery ) )* with ( SelectBlock | Subquery ) would resolve the incompatibility. This is not critical for Vulcan, can be release-noted.
          Hide
          till Till Westmann added a comment -

          There is a workaround not using parentheses around the first subquery.

          Deferring to Alice.

          Show
          till Till Westmann added a comment - There is a workaround not using parentheses around the first subquery. Deferring to Alice.
          Hide
          till Till Westmann added a comment -

          Description for release notes:

          Summary: Known Issue A query of the form

          (SELECT ... FROM ... WHERE ...)
          UNION ALL
          (SELECT ... FROM ... WHERE ...)

          is not parsed correctly.

          Workaround: Remove the parentheses around the first SFW block

          SELECT ... FROM ... WHERE ...
          UNION ALL
          (SELECT ... FROM ... WHERE ...)

          Show
          till Till Westmann added a comment - Description for release notes: Summary: Known Issue A query of the form (SELECT ... FROM ... WHERE ...) UNION ALL (SELECT ... FROM ... WHERE ...) is not parsed correctly. Workaround : Remove the parentheses around the first SFW block SELECT ... FROM ... WHERE ... UNION ALL (SELECT ... FROM ... WHERE ...)
          Hide
          till Till Westmann added a comment -

          Not a release note for Alice, moving to Mad-Hatter.

          Show
          till Till Westmann added a comment - Not a release note for Alice, moving to Mad-Hatter.

            People

            • Assignee:
              till Till Westmann
              Reporter:
              ritesh.agarwal Ritesh Agarwal
            • Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

              • Created:
                Updated:

                Gerrit Reviews

                There are no open Gerrit changes

                  PagerDuty

                  Error rendering 'com.pagerduty.jira-server-plugin:PagerDuty'. Please contact your Jira administrators.