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 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?

          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.

          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 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.

          Yes, we can defer this for Alice.

          ritesh.agarwal Ritesh Agarwal added a comment - Yes, we can defer this for Alice.

          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).

          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.

          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.

          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