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

Query: Different results for different indexes in use.

    XMLWordPrintable

Details

    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

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

          Activity

            Evgeny Makarenko Is this a regression? Can you please figure it out?

            raju Raju Suravarjjala added a comment - Evgeny Makarenko Is this a regression? Can you please figure it out?
            Sitaram.Vemulapalli Sitaram Vemulapalli added a comment - - 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 - - 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

            Enterprise Edition 7.0.0 build 1075:  Reproducible

            Enterprise Edition 6.5.0 build 4405 (Beta 2, I think): Also reproducible.

            keshav Keshav Murthy added a comment - Enterprise Edition 7.0.0 build 1075:  Reproducible Enterprise Edition 6.5.0 build 4405 (Beta 2, I think): Also reproducible.
            Sitaram.Vemulapalli Sitaram Vemulapalli added a comment - - edited

            No reversion, It will cause performance regression. My guess the use case described only one might be issue, please fix that.
            If any uses cases found later we address at that time.

            Sitaram.Vemulapalli Sitaram Vemulapalli added a comment - - edited No reversion, It will cause performance regression. My guess the use case described only one might be issue, please fix that. If any uses cases found later we address at that time.
            Sitaram.Vemulapalli Sitaram Vemulapalli added a comment - - edited

            Both plans are right respective releases. In MH (MB-30422) there is full range of key i.e all values, not in pre-MH

            i.e

            {"inclusive":0}

            low, high nil

            range is equal only when

            func (this *Range2) EqualRange() bool {
                    return (this.Inclusion == datastore.BOTH) && (this.Low != nil && this.High != nil && (this.Low == this.High || this.Low.EquivalentTo(this.High)))
            }
            

            MIN optimization can be done only when MIN expression is equal to index key and left side of index keys have equal ranges, then MIN can return first VALUE (i.e NOT MISSING, NOT NULL). Otherwise it needs to follow regular MIN calculation (NO special optimization).

            Sitaram.Vemulapalli Sitaram Vemulapalli added a comment - - edited Both plans are right respective releases. In MH ( MB-30422 ) there is full range of key i.e all values, not in pre-MH i.e {"inclusive":0} low, high nil range is equal only when func ( this *Range2) EqualRange() bool { return ( this .Inclusion == datastore.BOTH) && ( this .Low != nil && this .High != nil && ( this .Low == this .High || this .Low.EquivalentTo( this .High))) } MIN optimization can be done only when MIN expression is equal to index key and left side of index keys have equal ranges, then MIN can return first VALUE (i.e NOT MISSING, NOT NULL). Otherwise it needs to follow regular MIN calculation (NO special optimization).
            jeelan.poola Jeelan Poola added a comment - - edited

            This issue got exposed from build 6.5.0-4156 onwards (when the fix for MB-29605 was merged). It was kind of hidden until then. Nonetheless, this is serious issue and must be fixed in MH. GSI patches are under review.

            jeelan.poola Jeelan Poola added a comment - - edited This issue got exposed from build 6.5.0-4156 onwards (when the fix for MB-29605 was merged). It was kind of hidden until then. Nonetheless, this is serious issue and must be fixed in MH. GSI patches are under review.

            Build couchbase-server-6.5.0-4924 contains indexing commit 7d0d066 with commit message:
            MB-37078: [Backport to mad-hatter] Fix equality check for nil in fast count

            build-team Couchbase Build Team added a comment - Build couchbase-server-6.5.0-4924 contains indexing commit 7d0d066 with commit message: MB-37078 : [Backport to mad-hatter] Fix equality check for nil in fast count

            Build couchbase-server-6.5.0-4924 contains indexing commit f1cd70d with commit message:
            MB-37078: [Backport to mad-hatter] Add functional tests for min/max aggregate optimisations

            build-team Couchbase Build Team added a comment - Build couchbase-server-6.5.0-4924 contains indexing commit f1cd70d with commit message: MB-37078 : [Backport to mad-hatter] Add functional tests for min/max aggregate optimisations

            Build couchbase-server-6.5.0-4924 contains indexing commit d937caa with commit message:
            MB-37078: [Backport to mad-hatter] Fix equality filter check for nil values of high and low

            build-team Couchbase Build Team added a comment - Build couchbase-server-6.5.0-4924 contains indexing commit d937caa with commit message: MB-37078 : [Backport to mad-hatter] Fix equality filter check for nil values of high and low

            Build couchbase-server-7.0.0-1110 contains indexing commit e1ad363 with commit message:
            MB-37078: Add functional tests for min/max aggregate optimisations

            build-team Couchbase Build Team added a comment - Build couchbase-server-7.0.0-1110 contains indexing commit e1ad363 with commit message: MB-37078 : Add functional tests for min/max aggregate optimisations

            Build couchbase-server-7.0.0-1110 contains indexing commit 08e81e9 with commit message:
            MB-37078 Fix equality check for nil in fast count

            build-team Couchbase Build Team added a comment - Build couchbase-server-7.0.0-1110 contains indexing commit 08e81e9 with commit message: MB-37078 Fix equality check for nil in fast count

            Build couchbase-server-7.0.0-1110 contains indexing commit 88c256b with commit message:
            MB-37078: Fix equality filter check for nil values of high and low

            build-team Couchbase Build Team added a comment - Build couchbase-server-7.0.0-1110 contains indexing commit 88c256b with commit message: MB-37078 : Fix equality filter check for nil values of high and low

            verified for 6.5.0-4924

            evgeny.makarenko Evgeny Makarenko (Inactive) added a comment - verified for 6.5.0-4924

            People

              amit.kulkarni Amit Kulkarni
              evgeny.makarenko Evgeny Makarenko (Inactive)
              Votes:
              0 Vote for this issue
              Watchers:
              7 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                PagerDuty