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

[Backport 6.0.4] - Query: Different results for different indexes in use.

    XMLWordPrintable

Details

    • Bug
    • Status: Closed
    • Critical
    • Resolution: Fixed
    • 6.0.4
    • 6.0.4
    • secondary-index
    • None

    Description

      Build 6.5.0-4890

      query 

      SELECT MIN(d.age) from default d USE INDEX (`travel_history_name_age`) UNNEST d.`travel_history` AS t where t = 'India'

      returns 45

      query

      SELECT MIN(d.age) from default d USE INDEX (`#primary`) UNNEST d.`travel_history` AS t where t = 'India'

      returns 28

      Indexes definitions:

      CREATE PRIMARY INDEX `#primary` ON `default`
      		 
       
      		 
      CREATE INDEX `name_age_travel_history` ON `default`(`name`,`age`,(all (array `t` for `t` in `travel_history` end)))
      		 
       
      		 
      CREATE INDEX `name_travel_history_age` ON `default`(`name`,(all (array `t` for `t` in `travel_history` end)),`age`)
      		 
       
      		 
      CREATE INDEX `travel_history_name_age` ON `default`((all (array `t` for `t` in `travel_history` end)),`name`,`age`)

      default bucket data is attached

      logs are attached

      Attachments

        1. aa.sql
          10 kB
        2. default_data.json
          111 kB
        3. ExplainAlice.txt
          2 kB
        4. ExplainMH.txt
          3 kB

        Issue Links

          Clones

          Bug - A problem which impairs or prevents the functions of the product. MB-37078 Query: Different results for different indexes in use.

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed
          No reviews matched the request. Check your Options in the drop-down menu of this sections header.

          Activity

            Ascending order - Click to sort in descending order
            wayne Wayne Siu added a comment -

            Comment from Sitaram in MB-37078, is it an issue in Alice?

            Sitaram.VemulapalliSitaram Vemulapalli added a comment - 01/Dec/19 9:42 PM - edited
            		 
            Plan looks right. Index aggregates giving wrong results. made aa.sql
            		 
            SELECT MIN(d.age) from default d USE INDEX (`travel_history_name_age`) UNNEST d.`travel_history` AS t where t = 'India';
            		 
             
            		 
            2nd Range is full span. If add d.name IS NOT MISSING it gives right results.
            		 
            Scan seems fine. it look like this is side affect of MIN optimization. If i change age DESC it gives right results.
            		 
             
            		 
            The following gives right results.
            		 
            SELECT d.age from default d USE INDEX (`travel_history_name_age`) UNNEST d.`travel_history` AS t where t = 'India' order by d.age limit 1;
            		 
             
            		 
            *It gives right result in 6.0.3*

            wayne Wayne Siu added a comment - Comment from Sitaram in MB-37078 , is it an issue in Alice? Sitaram.VemulapalliSitaram Vemulapalli added a comment - 01/Dec/19 9:42 PM - edited Plan looks right. Index aggregates giving wrong results. made aa.sql SELECT MIN(d.age) from default d USE INDEX (`travel_history_name_age`) UNNEST d.`travel_history` AS t where t = 'India';   2nd Range is full span. If add d.name IS NOT MISSING it gives right results. Scan seems fine. it look like this is side affect of MIN optimization. If i change age DESC it gives right results.   The following gives right results. SELECT d.age from default d USE INDEX (`travel_history_name_age`) UNNEST d.`travel_history` AS t where t = 'India' order by d.age limit 1;   *It gives right result in 6.0.3*
            Sitaram.Vemulapalli Sitaram Vemulapalli added a comment -

            Wayne Siu Alice is fine because this is due to MB-29605 which done in MH

            Sitaram.Vemulapalli Sitaram Vemulapalli added a comment - Wayne Siu Alice is fine because this is due to MB-29605 which done in MH
            amit.kulkarni Amit Kulkarni added a comment - - edited

            Wayne Siu,

            Although we haven't encountered any issue in Alice till now, the changeset http://review.couchbase.org/c/120130/ makes the optimisation we have done in 6.0.3 (MB-34698) more robust. So, the fix will be required in 6.0.4.

            amit.kulkarni Amit Kulkarni added a comment - - edited Wayne Siu , Although we haven't encountered any issue in Alice till now, the changeset http://review.couchbase.org/c/120130/ makes the optimisation we have done in 6.0.3 ( MB-34698 ) more robust. So, the fix will be required in 6.0.4.
            build-team Couchbase Build Team added a comment -

            Build couchbase-server-6.0.4-3061 contains indexing commit dde33ac with commit message:
            MB-37274: [Backport to Alice] Fix equality check for nil in fast count

            build-team Couchbase Build Team added a comment - Build couchbase-server-6.0.4-3061 contains indexing commit dde33ac with commit message: MB-37274 : [Backport to Alice] Fix equality check for nil in fast count
            build-team Couchbase Build Team added a comment -

            Build couchbase-server-6.0.4-3062 contains indexing commit 542f4a2 with commit message:
            MB-37274: [Backport to Alice] Fix equality filter check for nil values of high and low

            build-team Couchbase Build Team added a comment - Build couchbase-server-6.0.4-3062 contains indexing commit 542f4a2 with commit message: MB-37274 : [Backport to Alice] Fix equality filter check for nil values of high and low
            ajay.bhullar Ajay Bhullar added a comment -

            verified in 6.0.4-3066 with the cases mentioned in the comment referenced in the original bug, everything looks fine

            ajay.bhullar Ajay Bhullar added a comment - verified in 6.0.4-3066 with the cases mentioned in the comment referenced in the original bug, everything looks fine

            People

              ajay.bhullar Ajay Bhullar
              amit.kulkarni Amit Kulkarni
              Votes:
              0 Vote for this issue
              Watchers:
              7 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Gerrit Reviews

                  There are no open Gerrit changes

                  PagerDuty