Inconsistent data returned with index

Java SDK
, ,
22

@Kevin.Cherkauer this is the output you asked for. thank you.
idxx_username_arts_createdAt.json.zip (1.3 KB)

23

@icy44 index definitions looks to fine for me.

EXPLAIN you posted is not correct particularly “covers”. Particularly covers array must have all index keys. If not it will be issue and result in wrong results. I have never seen this. Not sure what went wrong. Only way is restart query service and check again

It should some thing like this (I used default bucket)

 explain select count(1) totalCount from default where type = "art" and username = "hellokitty";
{
    "requestID": "5011071d-4f12-443c-bfa1-629d834a4525",
    "signature": "json",
    "results": [
    {
        "plan": {
            "#operator": "Sequence",
            "~children": [
                {
                    "#operator": "IndexScan3",
                    "covers": [
                        "cover ((`default`.`username`))",
                        "cover (((`default`.`meta`).`createdAt`))",
                        "cover ((-str_to_millis(((`default`.`values`).`published_a t`))))",
                        "cover (ifmissingornull(((`default`.`values`).`published`), false))",
                        "cover (ifmissingornull(((`default`.`values`).`deleted_at`), 0))",
                        "cover (ifmissingornull(((`default`.`values`).`suspended`), false))",
                        "cover ((meta(`default`).`id`))",
                        "cover (count(1))"
                    ],
                    "filter_covers": {
                        "cover ((`default`.`type`))": "art"
                    },
                    "index": "idxx_username_arts_createdAt",
                    "index_group_aggs": {
                        "aggregates": [
                            {
                                "aggregate": "COUNT",
                                "expr": "1",
                                "id": 7,
                                "keypos": -1
                            }
                        ]
                    },
                    "index_id": "53254c1c3c448b64",
                    "index_partition_by": "[`username`]",
                    "index_projection": {
                        "entry_keys": [
                            7
                        ]
                    },
                    "keyspace": "default",
                    "namespace": "default",
                    "spans": [
                        {
                            "exact": true,
                            "range": [
                                {
                                    "high": "\"hellokitty\"",
                                    "inclusion": 3,
                                    "index_key": "`username`",
                                    "low": "\"hellokitty\""
                                }
                            ]
                        }
                    ],
                    "using": "gsi"
                },
                {
                    "#operator": "Parallel",
                    "~child": {
                        "#operator": "Sequence",
                        "~children": [
                            {
                                "#operator": "InitialProject",
                                "result_terms": [
                                    {
                                        "as": "totalCount",
                                        "expr": "cover (count(1))"
                                    }
                                ]
                            }
                        ]
                    }
                }
            ]
        },
        "text": "select count(1) totalCount from default where type = \"art\" and username = \"hellokitty\";"
    }
    ],
    "status": "success",
    "metrics": {
        "elapsedTime": "4.629256ms",
        "executionTime": "4.544567ms",
        "resultCount": 1,
        "resultSize": 3095,
        "serviceLoad": 2
    }
}
24

@icy44 Thank you for the getIndexStatus output. Not sure if this is significant but I notice the CREATE INDEX statement in there differs from the one you posted earlier – this may explain the issue @vsr1 detected of an apparently non-covering index being used as if it were covering. The CREATE INDEX according to getIndexStatus is

CREATE INDEX `idxx_username_arts_createdAt`
ON `products`(
  `username`,
  (`meta`.`createdAt`) DESC,
  (-str_to_millis((`values`.`published_at`))),
  ifmissingornull((`values`.`published`), false),
  ifmissingornull((`values`.`deleted_at`), 0),
  ifmissingornull((`values`.`suspended`), false))
PARTITION BY hash(`username`)
WHERE (`type` = \"art\")
WITH {
  \"defer_build\":true,
  \"nodes\":[ \"10.10.30.43:8091\",\"10.10.30.42:8091\" ],
  \"num_replica\":1,
  \"num_partition\":8
}

The output indicates the partitions are distributed

node 10.10.30.42:8091

  • replicaId 0 paritions [1, 2, 3, 6, 7, 8]
  • replicaId 1 partitions [4, 5]

node 10.10.30.43:8091

  • replicaId 0 partitions [4, 5]
  • replicaId 1 partitions [1, 2, 3, 6, 7, 8]

so that looks fine.

25

@vsr1 @Kevin.Cherkauer thanks for your help and information. we re-created the index with same setup, that fix the issue. :slight_smile:

1 Like
26

@icy44 Glad you were able to resolve the issue. It sure was an unusual one that I have not seen before either. Perhaps a random glitch of disk corruption.