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

Issues on Index Selection using IN clause

    XMLWordPrintable

Details

    • Improvement
    • Status: Closed
    • Major
    • Resolution: Fixed
    • 5.1.2
    • 6.5.0, 6.0.1
    • query

    Description

      Given this index definition:

      CREATE INDEX indexIn on bucket(field1) WHERE field1 IN ["A","B","C"];
      

      The following queries should be using this index and worked properly:

      SELECT * from bucket where field1 IN ["A"]
      SELECT * from bucket where field1 = "A"
      SELECT * from bucket where field1 IN ["B"] 
      SELECT * from bucket where field1 = "B"
      SELECT * from bucket where field1 IN ["C"]
      SELECT * from bucket where field1 = "C"
      SELECT * from bucket where field1 IN ["A","B"]
      -- All tuples combination
      SELECT * from bucket WHERE field1 IN ["A","B","C"];
      SELECT * from bucket WHERE field1 IN ["A","C","B"];
      -- All triples combination
      

      After several combined tests the only combination that worked was the exact IN clause and exact value:

      SELECT * from bucket WHERE field1 IN ["A","B","C"];
      SELECT * from bucket WHERE field = "A"; -- OR B OR C
      

      For the rest of the combinations the following error was raised:

      [
        {
          "code": 4000,
          "msg": "No index available on keyspace oysho_stg_copy 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": "SELECT * from oysho_stg_copy WHERE field1 IN [\"B\",\"C\",\"A\"];"
        }
      ]
      

      The following issue was discussed with the N1QL team and the answer was to avoid using complex terms (IN, OR, etc.)on indexes to avoid indexing mismatch. In case of using, Query's IN clausule has to be exact to the index's one.

      However, IN clause is allowed on CREATE INDEX sintax and doens't works as expected (manage a range of values).

      On the other hand, equal operator works on single equal values, so query terms doesn't have to be exact to the Index's IN clause.

      None of this limitations are documented on doc site and N1QL books.

      After discussing this issue with the N1QL and the tiger team the following actions were detected:

      1. File this MB to fix the IN clause behaviour to expected
      2. Link to current customer CBSE
      3. File a DOC Issue to

      Attachments

        Issue Links

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

          Activity

            Build couchbase-server-6.5.0-1836 contains query commit e0ce3bf with commit message:
            MB-32306. Handle Index Sargable when both are IN clauses

            build-team Couchbase Build Team added a comment - Build couchbase-server-6.5.0-1836 contains query commit e0ce3bf with commit message: MB-32306 . Handle Index Sargable when both are IN clauses
            Sitaram.Vemulapalli Sitaram Vemulapalli added a comment - - edited

            In addition to above use case the following will not use index, because "D" is not part of the index.

            SELECT * from bucket WHERE field1 IN ["A","C","B", "D"];

            If customer needs this 5.5.3 or 6.0.1. Please get it triaged through maintenance release committee.

            Sitaram.Vemulapalli Sitaram Vemulapalli added a comment - - edited In addition to above use case the following will not use index, because "D" is not part of the index. SELECT * from bucket WHERE field1 IN ["A","C","B", "D"] ; If customer needs this 5.5.3 or 6.0.1. Please get it triaged through maintenance release committee.

            Build couchbase-server-6.5.0-1844 contains query commit 160fe3d with commit message:
            MB-32306. Handle Index Sargable when both are IN clauses

            build-team Couchbase Build Team added a comment - Build couchbase-server-6.5.0-1844 contains query commit 160fe3d with commit message: MB-32306 . Handle Index Sargable when both are IN clauses

            Reopening to add the fix to 6.0.1 as well.

            keshav Keshav Murthy added a comment - Reopening to add the fix to 6.0.1 as well.

            Build couchbase-server-6.0.1-2001 contains query commit a53ce41 with commit message:
            MB-32306. Handle Index Sargable when both are IN clauses

            build-team Couchbase Build Team added a comment - Build couchbase-server-6.0.1-2001 contains query commit a53ce41 with commit message: MB-32306 . Handle Index Sargable when both are IN clauses
            mihir.kamdar Mihir Kamdar (Inactive) added a comment - - edited

            Ajay Bhullar can you pls validate this on 6.0.1 and add a test for it?

            mihir.kamdar Mihir Kamdar (Inactive) added a comment - - edited Ajay Bhullar can you pls validate this on 6.0.1 and add a test for it?

            verified in 6.0.1-2010 will be automating the test case today. Have tried all the queries mentioned in the description. 

            ajay.bhullar Ajay Bhullar added a comment - verified in 6.0.1-2010 will be automating the test case today. Have tried all the queries mentioned in the description. 

            People

              ajay.bhullar Ajay Bhullar
              Raymundo.Flores Raymundo Flores (Inactive)
              Votes:
              0 Vote for this issue
              Watchers:
              12 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                PagerDuty