yugabyte/yugabyte-db

[YCQL] Inaccuracy in Explain query involving Jsonb

Open

#4529 aperta il 20 mag 2020

Vedi su GitHub
 (0 commenti) (0 reazioni) (0 assegnatari)C (1003 fork)batch import
area/ycqlgood first issuekind/enhancementpriority/medium

Metriche repository

Star
 (8229 star)
Metriche merge PR
 (Merge medio 17g 21h) (92 PR mergiate in 30 g)

Descrizione

Jira Link: DB-4671

ycqlsh:mykeyspace> describe test4;

CREATE TABLE mykeyspace.test4 (
    id bigint PRIMARY KEY,
    detail jsonb
) WITH default_time_to_live = 0
    AND transactions = {'enabled': 'true'};
CREATE INDEX index_test4_by_detail_hello_array ON mykeyspace.test4 (detail->'0'->>'hello', id)
    WITH transactions = {'enabled': 'true'};
CREATE INDEX index_test4_by_detail_foo_array ON mykeyspace.test4 (detail->'0'->>'foo', id)
    WITH transactions = {'enabled': 'true'};

ycqlsh:mykeyspace> explain SELECT id FROM mykeyspace.test4 WHERE (detail->0->>'hello') = 'world';

 QUERY PLAN
----------------------------------------------------------------------------------------
 Index Only Scan using mykeyspace.index_test4_by_detail_hello_array on mykeyspace.test4
   Key Conditions: (detail->'0'->>'hello' = 'world')

Note that the query was for detail->0->>'hello'. But the explain plan shows detail->'0'->>'hello'.

  • ->0-> would denote indexing into an array in the json
  • ->'0'-> would denote selecting a sub-field "0" in the json

Guida contributor