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

N1QL returns duplicate results

    XMLWordPrintable

Details

    • Bug
    • Status: Closed
    • Major
    • Resolution: Done
    • 5.1.0
    • None
    • query
    • Untriaged
    • Windows 64-bit
    • Unknown

    Description

      N1QL returns duplicate results for one entry

      example query:

       SELECT `Extent1`.* FROM `binyat` as `Extent1` 
       WHERE ((`Extent1`.`docType` = 'StockRevisionLine') 
       AND (`Extent1`.`stockRevisionId` = '15b4ef08-1bfb-4294-b154-d15535a1ce10') 
       AND (`Extent1`.`id` = '0911ad98-a24b-402b-bd0a-ad3e03e0075e')) 
      


      and its results:

      [ 
       { 
        "date": "2018-04-12T14:30:52.2843225+05:00", 
        "docType": "StockRevisionLine", 
        "id": "0911ad98-a24b-402b-bd0a-ad3e03e0075e", 
        ... 
       }, 
       { 
        "date": "2018-04-12T14:30:52.2843225+05:00", 
        "docType": "StockRevisionLine", 
        "id": "0911ad98-a24b-402b-bd0a-ad3e03e0075e", 
        ... 
       } 

       

       

      If I query by key, I get only one result.

      After I deleted this document, N1QL still returns one result, but querying by key returns no result.

      This started to happen on several documents.

      I think this is some how related to replication, where conflicted and deleted documents still kept somewhere, and GSI scans them also.

      Please let me know if additional information is needed to investigate this issue.

      Attachments

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

        Activity

          The indexes are maintained asynchronously.  You'll have to use the right consistency levels on the indexing.

          See: https://developer.couchbase.com/documentation/server/current/architecture/querying-data-with-n1ql.html

          keshav Keshav Murthy added a comment - The indexes are maintained asynchronously.  You'll have to use the right consistency levels on the indexing. See:  https://developer.couchbase.com/documentation/server/current/architecture/querying-data-with-n1ql.html

          Changing "Scan Consistency" does not change anything, I still have duplicate results for one document.

          PS: Several days passed since I noticed this issue, there was enough time for index to run.

          mgochmuradov merdan gochmuradov added a comment - Changing " Scan Consistency " does not change anything, I still have duplicate results for one document. PS: Several days passed since I noticed this issue, there was enough time for index to run.

          please reopen this issue, because scan consistency is not the case.

          mgochmuradov merdan gochmuradov added a comment - please reopen this issue, because scan consistency is not the case.

          merdan gochmuradov:  Please run the following query and give the results and the query plan here.

          Also, please run the query with the profiling ON and provide the plan with the profile as well. Thanks.

           

           SELECT META(Extent1).id,  `Extent1`.* FROM `binyat` as `Extent1`
          WHERE ((`Extent1`.`docType` = 'StockRevisionLine')
          AND (`Extent1`.`stockRevisionId` = '15b4ef08-1bfb-4294-b154-d15535a1ce10')
          AND (`Extent1`.`id` = '0911ad98-a24b-402b-bd0a-ad3e03e0075e')) 
          keshav Keshav Murthy added a comment - merdan gochmuradov :  Please run the following query and give the results and the query plan here. Also, please run the query with the profiling ON and provide the plan with the profile as well. Thanks.    SELECT META(Extent1).id,  `Extent1`.* FROM `binyat` as `Extent1` WHERE ((`Extent1`.`docType` = 'StockRevisionLine') AND (`Extent1`.`stockRevisionId` = '15b4ef08-1bfb-4294-b154-d15535a1ce10') AND (`Extent1`.`id` = '0911ad98-a24b-402b-bd0a-ad3e03e0075e')) 

          Oo, it seeams that Sync Gateway creates duplicate files.

           

          // SELECT META(Extent1).id returns:
          [
            {
              "id": "0911ad98-a24b-402b-bd0a-ad3e03e0075e"
            },
            {
              "id": "_sync:rb:+MXU55nujQK/fiJ/50IqJAAH0jU169HKATkpl/R0OhM="
            }
          ]
          

           

          mgochmuradov merdan gochmuradov added a comment - Oo, it seeams that Sync Gateway creates duplicate files.   // SELECT META(Extent1).id returns: [   {     "id" : "0911ad98-a24b-402b-bd0a-ad3e03e0075e"   },   {     "id" : "_sync:rb:+MXU55nujQK/fiJ/50IqJAAH0jU169HKATkpl/R0OhM="   } ]  

          Bulk closing invalid, duplicate and won't fix bugs

          raju Raju Suravarjjala added a comment - Bulk closing invalid, duplicate and won't fix bugs

          People

            keshav Keshav Murthy
            mgochmuradov merdan gochmuradov
            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