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

Queries with NOT of AND predicates is not using multiple indexes

    XMLWordPrintable

Details

    • Bug
    • Resolution: Fixed
    • Major
    • 5.0.0
    • 4.6.2, 5.0.0
    • query
    •  5.0.0-2509
    • Untriaged
    • No

    Description

      create two indexes on int fields:
      CREATE INDEX `idx1` ON `default`(`k1`)
      CREATE INDEX `idx2` ON `default`(`k0`)

      Document in default containing k1 and k0 integers:

      {
        "k0": 1,
        "k1": 2
      }
      

      Query issued:
      explain SELECT 1 FROM default WHERE not (k0 != 234 AND k1 != 123);

      Result of this query:

      [
        {
          "code": 4000,
          "msg": "No index available on keyspace default that matches your query. Use CREATE INDEX or CREATE PRIMARY INDEX to create an index, or check that your expected index is online.",
          "query_from_user": "explain SELECT 1 FROM  default   WHERE   not (k0 != 234 AND k1 != 123);"
        }
      ]
      

      On translating above not(AND) to OR, following query uses the right unionscan of both indexes:
      explain SELECT 1 FROM default WHERE (k0 = 234 OR k1 = 123);

      This query uses right index: idx2
      explain SELECT 1 FROM default WHERE NOT (k0 < 234 );

      This query uses right index:idx1
      explain SELECT 1 FROM default WHERE NOT (k1 > 123);

      This query issues an error,requiring primary index:
      explain SELECT 1 FROM default WHERE NOT (k0 < 234 AND k1 > 123);

      Same is true if k0 is a boolean field.

      Attachments

        Issue Links

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

          Activity

            People

              ajay.bhullar Ajay Bhullar
              Prerna.Manaktala Prerna Manaktala (Inactive)
              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