search

QuesmaOrg / quesma

Programmable database gateway
https://quesma.com
Other
108 stars 6 forks source link

Illegal type Int64 of argument of function ilike #1018

Open b17k0 opened 5 days ago

b17k0 commented 5 days ago

Hello! If you add a filter for the Int64 field in kibana, you will prevent this error:

quesma-1          | Nov 20 14:34:20.926 ERR quesma/quesma/search.go:673 > Q3006: Unspecified database error.  clickhouse: query failed. err: code: 43, message: Illegal type Int64 of argument of function ilike: In scope SELECT account_id, auto_finished, balance_at_end, balance_at_start, category, currency_id, finished_at, started_at, state, uuid FROM default.my_transactions_distributed WHERE ((started_at >= fromUnixTimestamp64Milli(1729458000000)) AND (started_at <= fromUnixTimestamp64Milli(1732113260719))) AND (id ILIKE '%99999999999%') ORDER BY started_at DESC LIMIT 500, query: SELECT "account_id", "category", "currency_id", "finished_at", "id", "started_at", "state", "uuid" FROM default.my_transactions_distributed WHERE (("started_at">=fromUnixTimestamp64Milli(1729458000000) AND "started_at"<=fromUnixTimestamp64Milli(1732113260719)) AND "id" iLIKE '%99999999999%') ORDER BY "started_at" DESC LIMIT 500: code: 43, message: Illegal type Int64 of argument of function ilike: In scope SELECT account_id, auto_finished, balance_at_end, balance_at_start, category, currency_id, finished_at, id, started_at, state, uuid FROM default.my_transactions_distributed WHERE ((started_at >= fromUnixTimestamp64Milli(1729458000000)) AND (started_at <= fromUnixTimestamp64Milli(1732113260719))) AND (id ILIKE '%99999999999%') ORDER BY started_at DESC LIMIT 500 async_id=quesma_async_019349fe-e155-71fb-afe5-d13c397669df request_id=019349fe-e155-709b-9dbc-c03b32c92d7c
quesma-1          | Nov 20 14:34:20.926 ERR quesma/quesma/quesma.go:156 > quesma request failed: Q3006: Unspecified database error.  clickhouse: query failed. err: code: 43, message: Illegal type Int64 of argument of function ilike: In scope SELECT account_id, auto_finished, balance_at_end, balance_at_start, category, currency_id, finished_at, started_at, state, uuid FROM default.my_transactions_distributed WHERE ((started_at >= fromUnixTimestamp64Milli(1729458000000)) AND (started_at <= fromUnixTimestamp64Milli(1732113260719))) AND (id ILIKE '%99999999999%') ORDER BY started_at DESC LIMIT 500, query: SELECT "account_id", "auto_finished", "balance_at_end", "balance_at_start", "category", "currency_id", "finished_at", "id", "started_at", "state", "uuid" FROM default.my_transactions_distributed WHERE (("started_at">=fromUnixTimestamp64Milli(1729458000000) AND "started_at"<=fromUnixTimestamp64Milli(1732113260719)) AND "id" iLIKE '%99999999999%') ORDER BY "started_at" DESC LIMIT 500: code: 43, message: Illegal type Int64 of argument of function ilike: In scope SELECT account_id, auto_finished, balance_at_end, balance_at_start, category, currency_id, finished_at, id, started_at, state, uuid FROM default.my_transactions_distributed WHERE ((started_at >= fromUnixTimestamp64Milli(1729458000000)) AND (started_at <= fromUnixTimestamp64Milli(1732113260719))) AND (id ILIKE '%99999999999%') ORDER BY started_at DESC LIMIT 500 opaque_id=8b7d8918-089b-4044-a511-055925ecce1d;kibana:application:discover:new;application:discover:new path=/my_transactions*/_async_search reason="Unspecified database error." request_id=019349fe-e155-709b-9dbc-c03b32c92d7c

My clickhouse schema:

CREATE TABLE default.my_transactions ON CLUSTER 'my_cluster'
(
    `id` Int64,
    `started_at` DateTime64(9),
    `uuid` UUID,
    `account_id` Int64,
    `state` String,
    `category` String,
    `balance_at_start` Float64,
    `balance_at_end` Float64,
    `finished_at` DateTime64(9),
    `auto_finished` Nullable(Bool),
    `currency_id` FixedString(3)
)
ENGINE = ReplicatedMergeTree
PARTITION BY toYYYYMM(finished_at)
ORDER BY uuid
SETTINGS index_granularity = 8192;

create table default.my_transactions_distributed on cluster 'my_cluster' as default.my_transactions ENGINE = Distributed('my_cluster', 'default', my_transactions, rand());

My schemaOverrides:

schemaOverrides:
            fields:
              timestamp:
                type: alias
                targetColumnName: "finished_at"
              id:
                type: "long"
              started_at:
                type: "date"
              uuid:
                type: "keyword"
              state:
                type: "keyword"
              category:
                type: "keyword"
              balance_at_start:
                type: "float"
              balance_at_end:
                type: "float"
              finished_at:
                type: "date"
              auto_finished:
                type: "boolean"
              currency_id:
                type: "keyword"

In addition to the error itself, it is also strange that for the operator is in the kibana filter, it would be more correct to use id == '99999999999' instead of id ILIKE '%99999999999%'.

avelanarius commented 5 days ago

Some very early investigation into the issue:

An easier (to set up) reproducer is to try total_quantity:2 query on kibana_sample_data_ecommerce (total_quantity is also an Int64) in the example environment (https://github.com/QuesmaOrg/quesma/tree/main/examples/kibana-sample-data)

The total_query:2 query in "Discover" view in Kibana results in the following Elastic query (from "Live tail" of localhost:9999 Quesma debug interface):

{
  "_source": {
    "excludes": []
  },
  "aggs": {
    "0": {
      "date_histogram": {
        "field": "order_date",
        "fixed_interval": "30d",
        "min_doc_count": 1,
        "time_zone": "Europe/Warsaw"
      }
    }
  },
  "fields": [
    {
      "field": "@timestamp",
      "format": "date_time"
    },
    {
      "field": "customer_birth_date",
      "format": "date_time"
    },
    "..."
  ],
  "query": {
    "bool": {
      "filter": [
        {
          "bool": {
            "minimum_should_match": 1,
            "should": [
              {
                "match": {
                  "total_quantity": "2"
                }
              }
            ]
          }
        },
        {
          "range": {
            "order_date": {
              "format": "strict_date_optional_time",
              "gte": "2009-11-20T16:38:05.713Z",
              "lte": "2024-11-20T16:38:05.713Z"
            }
          }
        }
      ],
      "must": [],
      "must_not": [],
      "should": []
    }
  },
  "runtime_mappings": {},
  "script_fields": {},
  "size": 0,
  "stored_fields": [
    "*"
  ],
  "track_total_hits": true
}

and this causes the same exact problem:

Q3006: Unspecified database error.  clickhouse: query failed. err: code: 43, message: Illegal type Int64 of argument of function ilike

The ILIKE comes from this place in Quesma: https://github.com/QuesmaOrg/quesma/blob/6b5c479ab1a60bd0124ab5891bc452cafa042af2/quesma/queryparser/query_parser.go#L591

Confirmed that Elastic handles this query as expected correctly.