Skip to content

Different behavior of skip index with alias in latest version #65607

@ardenwick

Description

@ardenwick

Version 24.2.3.70, index used:

CREATE TABLE t
(
    `tenant` String,
    `recordTimestamp` Int64,
    `responseBody` String,
    `colAlias` String ALIAS responseBody || 'something else',
    INDEX ngrams colAlias TYPE ngrambf_v1(3, 2097152, 3, 0) GRANULARITY 1,
)
ENGINE = MergeTree
PARTITION BY (tenant, toDate(recordTimestamp))
ORDER BY recordTimestamp;

INSERT INTO t SELECT * FROM generateRandom('tenant String, recordTimestamp Int64, responseBody String') LIMIT 100;

set send_logs_level='debug';

explain indexes=1 select tenant,recordTimestamp from t where colAlias like '%abcd%';

Expression ((Projection + Before ORDER BY))
  Expression
    ReadFromMergeTree (default.t)
    Indexes:
      MinMax
        Condition: true
        Parts: 98/98
        Granules: 98/98
      Partition
        Condition: true
        Parts: 98/98
        Granules: 98/98
      PrimaryKey
        Condition: true
        Parts: 98/98
        Granules: 98/98
      Skip
        Name: ngrams
        Description: ngrambf_v1 GRANULARITY 1
        Parts: 0/98
        Granules: 0/98

https://fiddle.clickhouse.com/e4b3bc3b-7b1e-4284-9705-5b16a0475138

24.3 and later versions, index not used:
https://fiddle.clickhouse.com/0b617027-0173-43d2-9606-1b09596afa1d

Expression ((Project names + Projection))
  Expression
    ReadFromMergeTree (default.t)
    Indexes:
      MinMax
        Condition: true
        Parts: 89/89
        Granules: 89/89
      Partition
        Condition: true
        Parts: 89/89
        Granules: 89/89
      PrimaryKey
        Condition: true
        Parts: 89/89
        Granules: 89/89

Metadata

Metadata

Assignees

No one assigned

    Labels

    analyzerIssues and pull-requests related to new analyzeranalyzer-importantbugConfirmed user-visible misbehaviour in official release

    Type

    No type

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions