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

CBAS RQG for Arithmetic functions: Mentioned cbas queries have different result in comparison to SQL.

    XMLWordPrintable

Details

    • Untriaged
    • Unknown

    Description

      sql_query :: SELECT bool_field1 FROM simple_table WHERE ( ( decimal_field1 >= TRUNCATE( 1000 * LOG( 5071 ), 0 ) ) ) AND ( ( NOT ( bool_field1 ) ) ) ORDER BY char_field1
      cbas_query :: SELECT bool_field1 FROM simple_table_db_84091977_simple_table WHERE ( ( decimal_field1 >= TRUNC( 1000 * LOG( 5071 ), 0 ) ) ) AND ( ( NOT ( bool_field1 ) ) ) ORDER BY char_field1

      =========================================================

      sql_query :: SELECT varchar_field1 FROM simple_table WHERE ( ( bool_field1 ) ) AND ( ( decimal_field1 >= TRUNCATE( 1000 * LN( 5071 ), 0 ) ) ) OR ( ( NOT ( bool_field1 ) ) ) AND ( ( decimal_field1 > TRUNCATE( 1000 * ACOS( 5071 ), 0 ) ) ) ORDER BY int_field1
      cbas_query :: SELECT varchar_field1 FROM simple_table_db_84091977_simple_table WHERE ( ( bool_field1 ) ) AND ( ( decimal_field1 >= TRUNC( 1000 * LN( 5071 ), 0 ) ) ) OR ( ( NOT ( bool_field1 ) ) ) AND ( ( decimal_field1 > TRUNC( 1000 * ACOS( 5071 ), 0 ) ) ) ORDER BY int_field1

      =========================================================

      sql_query :: SELECT DISTINCT( primary_key_id ) FROM simple_table WHERE ( ( varchar_field1 <= SUBSTR( "ZBqhMErfuR", 1 ) ) ) AND ( NOT ( ( NOT (decimal_field1 >= TRUNCATE( 1000 * ASIN( 5071 ), 0 )) ) ) ) OR ( NOT ( NOT ( ( bool_field1 = true ) ) ) ) AND ( ( varchar_field1 IN ( "AAzaaKLuoS" , "ABlcYEhyhK" , "ADRnwiUKxf" , "ALPGArgccj" , "APzAeWkBhu" ) ) ) ORDER BY bool_field1
      cbas_query :: SELECT DISTINCT( primary_key_id ) FROM simple_table_db_84091977_simple_table WHERE ( ( varchar_field1 <= SUBSTR( "ZBqhMErfuR", 0 ) ) ) AND ( NOT ( ( NOT (decimal_field1 >= TRUNC( 1000 * ASIN( 5071 ), 0 )) ) ) ) OR ( NOT ( NOT ( ( bool_field1 = true ) ) ) ) AND ( ( varchar_field1 IN [ "AAzaaKLuoS" , "ABlcYEhyhK" , "ADRnwiUKxf" , "ALPGArgccj" , "APzAeWkBhu" ] ) ) ORDER BY bool_field1

      =========================================================

      sql_query :: SELECT * FROM simple_table WHERE ( ( int_field1 >= TRUNCATE( 1000 * ASIN( 47941685 ), 0 ) ) ) OR ( ( varchar_field1 < RTRIM( "ZBqhMErfuR" ) ) ) ORDER BY bool_field1
      cbas_query :: SELECT * FROM simple_table_db_84091977_simple_table WHERE ( ( int_field1 >= TRUNC( 1000 * ASIN( 47941685 ), 0 ) ) ) OR ( ( varchar_field1 < RTRIM( "ZBqhMErfuR" ) ) ) ORDER BY bool_field1

      =========================================================

      sql_query :: SELECT DISTINCT( char_field1 ) FROM simple_table WHERE ( ( primary_key_id < SUBSTR( "549", 1 ) ) ) AND ( ( NOT (decimal_field1 < TRUNCATE( 1000 * ACOS( 5071 ), 0 )) ) ) OR ( ( bool_field1 ) ) AND ( ( char_field1 LIKE '%N%' ) ) ORDER BY bool_field1
      cbas_query :: SELECT DISTINCT( char_field1 ) FROM simple_table_db_84091977_simple_table WHERE ( ( primary_key_id < SUBSTR( "549", 0 ) ) ) AND ( ( NOT (decimal_field1 < TRUNC( 1000 * ACOS( 5071 ), 0 )) ) ) OR ( ( bool_field1 ) ) AND ( ( char_field1 LIKE '%N%' ) ) ORDER BY bool_field1

      =========================================================

      sql_query :: SELECT * FROM simple_table WHERE ( ( NOT ( varchar_field1 > LTRIM( "ZBqhMErfuR" ) ) ) ) AND ( ( bool_field1 = true ) ) OR ( ( decimal_field1 != TRUNCATE( 1000 * ACOS( 5071 ), 0 ) ) ) AND ( ( ( char_field1 IN ( "A" , "B" , "C" , "D" , "E" ) ) AND ( primary_key_id NOT LIKE "549" ) ) ) ORDER BY char_field1
      cbas_query :: SELECT * FROM simple_table_db_84091977_simple_table WHERE ( ( NOT ( varchar_field1 > LTRIM( "ZBqhMErfuR" ) ) ) ) AND ( ( bool_field1 = true ) ) OR ( ( decimal_field1 != TRUNC( 1000 * ACOS( 5071 ), 0 ) ) ) AND ( ( ( char_field1 IN [ "A" , "B" , "C" , "D" , "E" ] ) AND ( primary_key_id NOT LIKE "549" ) ) ) ORDER BY char_field1

      =========================================================

      sql_query :: SELECT DISTINCT( bool_field1 ) FROM simple_table WHERE ( ( bool_field1 ) ) AND ( ( NOT (NOT (decimal_field1 >= TRUNCATE( 1000 * ASIN( 5071 ), 0 ))) ) ) OR ( ( char_field1 IN ( "A" , "B" , "C" , "D" , "E" ) ) ) AND ( ( bool_field1 != true ) ) ORDER BY decimal_field1
      cbas_query :: SELECT DISTINCT( bool_field1 ) FROM simple_table_db_84091977_simple_table WHERE ( ( bool_field1 ) ) AND ( ( NOT (NOT (decimal_field1 >= TRUNC( 1000 * ASIN( 5071 ), 0 ))) ) ) OR ( ( char_field1 IN [ "A" , "B" , "C" , "D" , "E" ] ) ) AND ( ( bool_field1 != true ) ) ORDER BY decimal_field1

      =========================================================

      sql_query :: SELECT * FROM simple_table WHERE ( ( ( varchar_field1 NOT BETWEEN "AAzaaKLuoS" and "zwDIxgKAOl" ) ) OR ( ( ( char_field1 IN ( "A" , "B" , "C" , "D" , "E" ) ) ) AND ( ( int_field1 != TRUNCATE( 1000 * ASIN( 47941685 ), 0 ) ) ) ) ) AND ( NOT ( NOT ( ( bool_field1 ) ) ) ) ORDER BY decimal_field1
      cbas_query :: SELECT * FROM simple_table_db_84091977_simple_table WHERE ( ( ( varchar_field1 NOT BETWEEN "AAzaaKLuoS" and "zwDIxgKAOl" ) ) OR ( ( ( char_field1 IN [ "A" , "B" , "C" , "D" , "E" ] ) ) AND ( ( int_field1 != TRUNC( 1000 * ASIN( 47941685 ), 0 ) ) ) ) ) AND ( NOT ( NOT ( ( bool_field1 ) ) ) ) ORDER BY decimal_field1

      =========================================================

      sql_query :: SELECT * FROM simple_table WHERE NOT ( NOT ( ( ( NOT ( bool_field1 ) ) ) AND ( NOT ( ( decimal_field1 > TRUNCATE( 1000 * LOG( 5071 ), 0 ) ) ) ) ) ) ORDER BY bool_field1
      cbas_query :: SELECT * FROM simple_table_db_84091977_simple_table WHERE NOT ( NOT ( ( ( NOT ( bool_field1 ) ) ) AND ( NOT ( ( decimal_field1 > TRUNC( 1000 * LOG( 5071 ), 0 ) ) ) ) ) ) ORDER BY bool_field1

      =========================================================

      sql_query :: SELECT DISTINCT( bool_field1 ) FROM simple_table WHERE NOT ( ( decimal_field1 <= TRUNCATE( 1000 * ACOS( 5071 ), 0 ) ) ) ORDER BY int_field1
      cbas_query :: SELECT DISTINCT( bool_field1 ) FROM simple_table_db_84091977_simple_table WHERE NOT ( ( decimal_field1 <= TRUNC( 1000 * ACOS( 5071 ), 0 ) ) ) ORDER BY int_field1

      =========================================================

      sql_query :: SELECT bool_field1 FROM simple_table WHERE ( NOT ( NOT ( NOT ( ( primary_key_id >= REPLACE( "549" , "d" , "c" ) ) ) ) ) ) AND ( ( decimal_field1 < TRUNCATE( 1000 * LOG( 5071 ), 0 ) ) ) ORDER BY int_field1
      cbas_query :: SELECT bool_field1 FROM simple_table_db_84091977_simple_table WHERE ( NOT ( NOT ( NOT ( ( primary_key_id >= REPLACE( "549" , "d" , "c" ) ) ) ) ) ) AND ( ( decimal_field1 < TRUNC( 1000 * LOG( 5071 ), 0 ) ) ) ORDER BY int_field1

      Attachments

        1. backup.zip
          69 kB
        2. data.json
          291 kB
        No reviews matched the request. Check your Options in the drop-down menu of this sections header.

        Activity

          ritesh.agarwal Ritesh Agarwal created issue -
          ritesh.agarwal Ritesh Agarwal made changes -
          Field Original Value New Value
          Summary CBAS RQG for Arithmetic: Mentioned cbas queries have different result in comparison to SQL. CBAS RQG for Arithmetic functions: Mentioned cbas queries have different result in comparison to SQL.
          till Till Westmann made changes -
          Assignee Till Westmann [ till ] Dmitry Lychagin [ dmitry.lychagin ]
          till Till Westmann made changes -
          Rank Ranked higher

          Ritesh Agarwal Could we get the data + expected results for N1QL?

          till Till Westmann added a comment - Ritesh Agarwal Could we get the data + expected results for N1QL?
          till Till Westmann made changes -
          Assignee Dmitry Lychagin [ dmitry.lychagin ] Ritesh Agarwal [ ritesh.agarwal ]
          till Till Westmann made changes -
          Labels functional-test functional-test triaged
          ritesh.agarwal Ritesh Agarwal made changes -
          Attachment data.json [ 50180 ]

          The attached JSON is quite difficult to consume. To get it back into an instance it seems that we'll need to 

          1. create a new bucket
          2. extract the JSON objects and the dataset name from the file
          3. invent new keys for each object
          4. load the objects into KV
          5. create and populate the shadow datasets.

          It would be a lot easier, if you could attach a zipped cbbbackup of the KV bucket and the Analytics DDL to create and populate the shadow datasets.
          Is that feasible?

          till Till Westmann added a comment - The attached JSON is quite difficult to consume. To get it back into an instance it seems that we'll need to  create a new bucket extract the JSON objects and the dataset name from the file invent new keys for each object load the objects into KV create and populate the shadow datasets. It would be a lot easier, if you could attach a zipped cbbbackup of the KV bucket and the Analytics DDL to create and populate the shadow datasets. Is that feasible?

          Till Westmann: Sure Till, I will give you this data. Meanwhile, let me figure out if there is some way I can give you a script that will generate and upload the same document types in CB buckets.

          ritesh.agarwal Ritesh Agarwal added a comment - Till Westmann : Sure Till, I will give you this data. Meanwhile, let me figure out if there is some way I can give you a script that will generate and upload the same document types in CB buckets.
          ritesh.agarwal Ritesh Agarwal made changes -
          Attachment backup.zip [ 50250 ]

          Attaching the data backup so that you can upload the same data into CB buckets. For analytics DDL, simply ingested everything without any filters.

          Another observation is that I compared the results with N1QL for few queries mentioned here and observed that the results are same. The reason for result mismatch with SQL is that for queries mentioned here they use ASIN( 47941685 ), ACOS( 47941685 ), ATAN( 47941685 ) here which return Null in SQL and NaN in CBAS/N1QL. Now when the comparison of a document field is done with NaN in cbas/n1ql they return True but false in SQL.

          ritesh.agarwal Ritesh Agarwal added a comment - Attaching the data backup so that you can upload the same data into CB buckets. For analytics DDL, simply ingested everything without any filters. Another observation is that I compared the results with N1QL for few queries mentioned here and observed that the results are same. The reason for result mismatch with SQL is that for queries mentioned here they use ASIN( 47941685 ), ACOS( 47941685 ), ATAN( 47941685 ) here which return Null in SQL and NaN in CBAS/N1QL. Now when the comparison of a document field is done with NaN in cbas/n1ql they return True but false in SQL.
          ritesh.agarwal Ritesh Agarwal made changes -
          Assignee Ritesh Agarwal [ ritesh.agarwal ] Dmitry Lychagin [ dmitry.lychagin ]

          Ritesh Agarwal So right now N1QL and SQL++ are aligned - that's a good thing.
          We could decide to change both languages to follow the SQL way and to return NULL, but that would be a different discussion. If this is the only issue, we should defer this discussion to the next release.

          till Till Westmann added a comment - Ritesh Agarwal So right now N1QL and SQL++ are aligned - that's a good thing. We could decide to change both languages to follow the SQL way and to return NULL, but that would be a different discussion. If this is the only issue, we should defer this discussion to the next release.
          till Till Westmann made changes -
          Assignee Dmitry Lychagin [ dmitry.lychagin ] Ritesh Agarwal [ ritesh.agarwal ]

          Yes, we can defer this for Alice.

          ritesh.agarwal Ritesh Agarwal added a comment - Yes, we can defer this for Alice.
          till Till Westmann made changes -
          Fix Version/s Alice [ 15048 ]
          Fix Version/s vulcan [ 14610 ]
          till Till Westmann made changes -
          Assignee Ritesh Agarwal [ ritesh.agarwal ] Till Westmann [ till ]
          till Till Westmann made changes -
          Labels functional-test triaged functional-test n1ql triaged
          till Till Westmann made changes -
          Labels functional-test n1ql triaged functional-test n1ql
          till Till Westmann made changes -
          Labels functional-test n1ql functional-test n1ql triaged
          till Till Westmann made changes -
          Rank Ranked higher
          till Till Westmann made changes -
          Rank Ranked higher
          till Till Westmann made changes -
          Priority Major [ 3 ] Critical [ 2 ]

          Ritesh Agarwal I think that we should validate that a) Query and Analytics return the same result and b) run the comparison test accordingly (i.e. not based on the SQL result).

          till Till Westmann added a comment - Ritesh Agarwal I think that we should validate that a) Query and Analytics return the same result and b) run the comparison test accordingly (i.e. not based on the SQL result).
          till Till Westmann made changes -
          Assignee Till Westmann [ till ] Ritesh Agarwal [ ritesh.agarwal ]
          till Till Westmann made changes -
          Rank Ranked higher
          till Till Westmann made changes -
          Rank Ranked higher
          till Till Westmann made changes -
          Epic Link MB-24368 [ 68166 ]
          till Till Westmann made changes -
          Assignee Ritesh Agarwal [ ritesh.agarwal ] Till Westmann [ till ]
          till Till Westmann made changes -
          Assignee Till Westmann [ till ] Ritesh Agarwal [ ritesh.agarwal ]

          Currently (build 6.0.0-1355)

          select value [ASIN(47941685), ACOS(47941685), ATAN(47941685)]
          

          returns

          [ "NaN", "NaN", 1.5707963059362222 ]
          

          for both the Query and Analytics services. This should be the expected result for both services.

          Do you agree, Keshav Murthy?

          till Till Westmann added a comment - Currently (build 6.0.0-1355) select value [ASIN(47941685), ACOS(47941685), ATAN(47941685)] returns [ "NaN", "NaN", 1.5707963059362222 ] for both the Query and Analytics services. This should be the expected result for both services. Do you agree, Keshav Murthy ?

          Yes.  I agree.

          keshav Keshav Murthy added a comment - Yes.  I agree.

          The current result is the expected result for the Query and Analytics services.

          till Till Westmann added a comment - The current result is the expected result for the Query and Analytics services.
          till Till Westmann made changes -
          Resolution Won't Fix [ 2 ]
          Status Open [ 1 ] Resolved [ 5 ]

          Bulk closing defects with resolution = user error, cannot reproduce, duplicate etc.

          mihir.kamdar Mihir Kamdar (Inactive) added a comment - Bulk closing defects with resolution = user error, cannot reproduce, duplicate etc.
          mihir.kamdar Mihir Kamdar (Inactive) made changes -
          Status Resolved [ 5 ] Closed [ 6 ]
          till Till Westmann made changes -
          Epic Link MB-24368 [ 68166 ] MB-54237 [ 200522 ]
          Workaround Move 6.0.x changes to their own Epic.

          People

            ritesh.agarwal Ritesh Agarwal
            ritesh.agarwal Ritesh Agarwal
            Votes:
            0 Vote for this issue
            Watchers:
            4 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Gerrit Reviews

                There are no open Gerrit changes

                PagerDuty