search

pingcap / tidb

TiDB is an open-source, cloud-native, distributed, MySQL-Compatible database for elastic scale and real-time analytics. Try AI-powered Chat2Query free at : https://www.pingcap.com/tidb-serverless/
https://pingcap.com
Apache License 2.0
36.81k stars 5.8k forks source link

Unexpected `Can't find column` error for query with expression index #52653

Open bb7133 opened 4 months ago

bb7133 commented 4 months ago

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

SQL statements:

CREATE TABLE `executions_visibility` (
  `namespace_id` char(64) NOT NULL,
  `run_id` char(64) NOT NULL,
  `start_time` datetime(6) NOT NULL,
  `execution_time` datetime(6) NOT NULL,
  `workflow_id` varchar(255) NOT NULL,
  `workflow_type_name` varchar(255) NOT NULL,
  `status` int(11) NOT NULL,
  `close_time` datetime(6) DEFAULT NULL,
  `history_length` bigint(20) DEFAULT NULL,
  `history_size_bytes` bigint(20) DEFAULT NULL,
  `execution_duration` bigint(20) DEFAULT NULL,
  `state_transition_count` bigint(20) DEFAULT NULL,
  `memo` blob DEFAULT NULL,
  `encoding` varchar(64) NOT NULL,
  `task_queue` varchar(255) NOT NULL DEFAULT '',
  `search_attributes` json DEFAULT NULL,
  `parent_workflow_id` varchar(255) DEFAULT NULL,
  `parent_run_id` varchar(255) DEFAULT NULL,
  `TemporalChangeVersion` json GENERATED ALWAYS AS (json_extract(`search_attributes`, _utf8mb4'$.TemporalChangeVersion')) VIRTUAL,
  `BinaryChecksums` json GENERATED ALWAYS AS (json_extract(`search_attributes`, _utf8mb4'$.BinaryChecksums')) VIRTUAL,
  `BatcherUser` varchar(255) GENERATED ALWAYS AS (json_unquote(json_extract(`search_attributes`, _utf8mb4'$.BatcherUser'))) VIRTUAL,
  `TemporalScheduledStartTime` datetime(6) GENERATED ALWAYS AS (convert_tz(regexp_replace(json_unquote(json_extract(`search_attributes`, _utf8mb4'$.TemporalScheduledStartTime')), _utf8mb4'Z|[+-][0-9]{2}:[0-9]{2}$', _utf8mb4''), substr(replace(json_unquote(json_extract(`search_attributes`, _utf8mb4'$.TemporalScheduledStartTime')), _utf8mb4'Z', _utf8mb4'+00:00'), -6, 6), _utf8mb4'+00:00')) VIRTUAL,
  `TemporalScheduledById` varchar(255) GENERATED ALWAYS AS (json_unquote(json_extract(`search_attributes`, _utf8mb4'$.TemporalScheduledById'))) VIRTUAL,
  `TemporalSchedulePaused` tinyint(1) GENERATED ALWAYS AS (json_extract(`search_attributes`, _utf8mb4'$.TemporalSchedulePaused')) VIRTUAL,
  `TemporalNamespaceDivision` varchar(255) GENERATED ALWAYS AS (json_unquote(json_extract(`search_attributes`, _utf8mb4'$.TemporalNamespaceDivision'))) VIRTUAL,
  `BuildIds` json GENERATED ALWAYS AS (json_extract(`search_attributes`, _utf8mb4'$.BuildIds')) VIRTUAL,
  PRIMARY KEY (`namespace_id`,`run_id`) /*T![clustered_index] CLUSTERED */,
  KEY `default_idx` (`namespace_id`,(coalesce(`close_time`, cast(_utf8mb4'9999-12-31 23:59:59' as datetime))),`start_time`,`run_id`),
  KEY `by_execution_time` (`namespace_id`,`execution_time`,(coalesce(`close_time`, cast(_utf8mb4'9999-12-31 23:59:59' as datetime))),`start_time`,`run_id`),
  KEY `by_workflow_id` (`namespace_id`,`workflow_id`,(coalesce(`close_time`, cast(_utf8mb4'9999-12-31 23:59:59' as datetime))),`start_time`,`run_id`),
  KEY `by_workflow_type` (`namespace_id`,`workflow_type_name`,(coalesce(`close_time`, cast(_utf8mb4'9999-12-31 23:59:59' as datetime))),`start_time`,`run_id`),
  KEY `by_status` (`namespace_id`,`status`,(coalesce(`close_time`, cast(_utf8mb4'9999-12-31 23:59:59' as datetime))),`start_time`,`run_id`),
  KEY `by_history_length` (`namespace_id`,`history_length`,(coalesce(`close_time`, cast(_utf8mb4'9999-12-31 23:59:59' as datetime))),`start_time`,`run_id`),
  KEY `by_history_size_bytes` (`namespace_id`,`history_size_bytes`,(coalesce(`close_time`, cast(_utf8mb4'9999-12-31 23:59:59' as datetime))),`start_time`,`run_id`),
  KEY `by_execution_duration` (`namespace_id`,`execution_duration`,(coalesce(`close_time`, cast(_utf8mb4'9999-12-31 23:59:59' as datetime))),`start_time`,`run_id`),
  KEY `by_state_transition_count` (`namespace_id`,`state_transition_count`,(coalesce(`close_time`, cast(_utf8mb4'9999-12-31 23:59:59' as datetime))),`start_time`,`run_id`),
  KEY `by_task_queue` (`namespace_id`,`task_queue`,(coalesce(`close_time`, cast(_utf8mb4'9999-12-31 23:59:59' as datetime))),`start_time`,`run_id`),
  KEY `by_parent_workflow_id` (`namespace_id`,`parent_workflow_id`,(coalesce(`close_time`, cast(_utf8mb4'9999-12-31 23:59:59' as datetime))),`start_time`,`run_id`),
  KEY `by_parent_run_id` (`namespace_id`,`parent_run_id`,(coalesce(`close_time`, cast(_utf8mb4'9999-12-31 23:59:59' as datetime))),`start_time`,`run_id`),
  KEY `by_temporal_change_version` (`namespace_id`,(cast(`TemporalChangeVersion` as char(255) array)),(coalesce(`close_time`, cast(_utf8mb4'9999-12-31 23:59:59' as datetime))),`start_time`,`run_id`),
  KEY `by_binary_checksums` (`namespace_id`,(cast(`BinaryChecksums` as char(255) array)),(coalesce(`close_time`, cast(_utf8mb4'9999-12-31 23:59:59' as datetime))),`start_time`,`run_id`),
  KEY `by_build_ids` (`namespace_id`,(cast(`BuildIds` as char(255) array)),(coalesce(`close_time`, cast(_utf8mb4'9999-12-31 23:59:59' as datetime))),`start_time`,`run_id`),
  KEY `by_batcher_user` (`namespace_id`,`BatcherUser`,(coalesce(`close_time`, cast(_utf8mb4'9999-12-31 23:59:59' as datetime))),`start_time`,`run_id`),
  KEY `by_temporal_scheduled_start_time` (`namespace_id`,`TemporalScheduledStartTime`,(coalesce(`close_time`, cast(_utf8mb4'9999-12-31 23:59:59' as datetime))),`start_time`,`run_id`),
  KEY `by_temporal_scheduled_by_id` (`namespace_id`,`TemporalScheduledById`,(coalesce(`close_time`, cast(_utf8mb4'9999-12-31 23:59:59' as datetime))),`start_time`,`run_id`),
  KEY `by_temporal_schedule_paused` (`namespace_id`,`TemporalSchedulePaused`,(coalesce(`close_time`, cast(_utf8mb4'9999-12-31 23:59:59' as datetime))),`start_time`,`run_id`),
  KEY `by_temporal_namespace_division` (`namespace_id`,`TemporalNamespaceDivision`,(coalesce(`close_time`, cast(_utf8mb4'9999-12-31 23:59:59' as datetime))),`start_time`,`run_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

CREATE TABLE `custom_search_attributes` (
  `namespace_id` char(64) NOT NULL,
  `run_id` char(64) NOT NULL,
  `search_attributes` json DEFAULT NULL,
  `Bool01` tinyint(1) GENERATED ALWAYS AS (json_extract(`search_attributes`, _utf8mb4'$.Bool01')) VIRTUAL,
  `Bool02` tinyint(1) GENERATED ALWAYS AS (json_extract(`search_attributes`, _utf8mb4'$.Bool02')) VIRTUAL,
  `Bool03` tinyint(1) GENERATED ALWAYS AS (json_extract(`search_attributes`, _utf8mb4'$.Bool03')) VIRTUAL,
  `Datetime01` datetime(6) GENERATED ALWAYS AS (convert_tz(regexp_replace(json_unquote(json_extract(`search_attributes`, _utf8mb4'$.Datetime01')), _utf8mb4'Z|[+-][0-9]{2}:[0-9]{2}$', _utf8mb4''), substr(replace(json_unquote(json_extract(`search_attributes`, _utf8mb4'$.Datetime01')), _utf8mb4'Z', _utf8mb4'+00:00'), -6, 6), _utf8mb4'+00:00')) VIRTUAL,
  `Datetime02` datetime(6) GENERATED ALWAYS AS (convert_tz(regexp_replace(json_unquote(json_extract(`search_attributes`, _utf8mb4'$.Datetime02')), _utf8mb4'Z|[+-][0-9]{2}:[0-9]{2}$', _utf8mb4''), substr(replace(json_unquote(json_extract(`search_attributes`, _utf8mb4'$.Datetime02')), _utf8mb4'Z', _utf8mb4'+00:00'), -6, 6), _utf8mb4'+00:00')) VIRTUAL,
  `Datetime03` datetime(6) GENERATED ALWAYS AS (convert_tz(regexp_replace(json_unquote(json_extract(`search_attributes`, _utf8mb4'$.Datetime03')), _utf8mb4'Z|[+-][0-9]{2}:[0-9]{2}$', _utf8mb4''), substr(replace(json_unquote(json_extract(`search_attributes`, _utf8mb4'$.Datetime03')), _utf8mb4'Z', _utf8mb4'+00:00'), -6, 6), _utf8mb4'+00:00')) VIRTUAL,
  `Double01` decimal(20,5) GENERATED ALWAYS AS (json_extract(`search_attributes`, _utf8mb4'$.Double01')) VIRTUAL,
  `Double02` decimal(20,5) GENERATED ALWAYS AS (json_extract(`search_attributes`, _utf8mb4'$.Double02')) VIRTUAL,
  `Double03` decimal(20,5) GENERATED ALWAYS AS (json_extract(`search_attributes`, _utf8mb4'$.Double03')) VIRTUAL,
  `Int01` bigint(20) GENERATED ALWAYS AS (json_extract(`search_attributes`, _utf8mb4'$.Int01')) VIRTUAL,
  `Int02` bigint(20) GENERATED ALWAYS AS (json_extract(`search_attributes`, _utf8mb4'$.Int02')) VIRTUAL,
  `Int03` bigint(20) GENERATED ALWAYS AS (json_extract(`search_attributes`, _utf8mb4'$.Int03')) VIRTUAL,
  `Keyword01` varchar(255) GENERATED ALWAYS AS (json_unquote(json_extract(`search_attributes`, _utf8mb4'$.Keyword01'))) VIRTUAL,
  `Keyword02` varchar(255) GENERATED ALWAYS AS (json_unquote(json_extract(`search_attributes`, _utf8mb4'$.Keyword02'))) VIRTUAL,
  `Keyword03` varchar(255) GENERATED ALWAYS AS (json_unquote(json_extract(`search_attributes`, _utf8mb4'$.Keyword03'))) VIRTUAL,
  `Keyword04` varchar(255) GENERATED ALWAYS AS (json_unquote(json_extract(`search_attributes`, _utf8mb4'$.Keyword04'))) VIRTUAL,
  `Keyword05` varchar(255) GENERATED ALWAYS AS (json_unquote(json_extract(`search_attributes`, _utf8mb4'$.Keyword05'))) VIRTUAL,
  `Keyword06` varchar(255) GENERATED ALWAYS AS (json_unquote(json_extract(`search_attributes`, _utf8mb4'$.Keyword06'))) VIRTUAL,
  `Keyword07` varchar(255) GENERATED ALWAYS AS (json_unquote(json_extract(`search_attributes`, _utf8mb4'$.Keyword07'))) VIRTUAL,
  `Keyword08` varchar(255) GENERATED ALWAYS AS (json_unquote(json_extract(`search_attributes`, _utf8mb4'$.Keyword08'))) VIRTUAL,
  `Keyword09` varchar(255) GENERATED ALWAYS AS (json_unquote(json_extract(`search_attributes`, _utf8mb4'$.Keyword09'))) VIRTUAL,
  `Keyword10` varchar(255) GENERATED ALWAYS AS (json_unquote(json_extract(`search_attributes`, _utf8mb4'$.Keyword10'))) VIRTUAL,
  `Text01` text GENERATED ALWAYS AS (json_unquote(json_extract(`search_attributes`, _utf8mb4'$.Text01'))) STORED,
  `Text02` text GENERATED ALWAYS AS (json_unquote(json_extract(`search_attributes`, _utf8mb4'$.Text02'))) STORED,
  `Text03` text GENERATED ALWAYS AS (json_unquote(json_extract(`search_attributes`, _utf8mb4'$.Text03'))) STORED,
  `KeywordList01` json GENERATED ALWAYS AS (json_extract(`search_attributes`, _utf8mb4'$.KeywordList01')) VIRTUAL,
  `KeywordList02` json GENERATED ALWAYS AS (json_extract(`search_attributes`, _utf8mb4'$.KeywordList02')) VIRTUAL,
  `KeywordList03` json GENERATED ALWAYS AS (json_extract(`search_attributes`, _utf8mb4'$.KeywordList03')) VIRTUAL,
  PRIMARY KEY (`namespace_id`,`run_id`) /*T![clustered_index] CLUSTERED */,
  KEY `by_bool_01` (`namespace_id`,`Bool01`),
  KEY `by_bool_02` (`namespace_id`,`Bool02`),
  KEY `by_bool_03` (`namespace_id`,`Bool03`),
  KEY `by_datetime_01` (`namespace_id`,`Datetime01`),
  KEY `by_datetime_02` (`namespace_id`,`Datetime02`),
  KEY `by_datetime_03` (`namespace_id`,`Datetime03`),
  KEY `by_double_01` (`namespace_id`,`Double01`),
  KEY `by_double_02` (`namespace_id`,`Double02`),
  KEY `by_double_03` (`namespace_id`,`Double03`),
  KEY `by_int_01` (`namespace_id`,`Int01`),
  KEY `by_int_02` (`namespace_id`,`Int02`),
  KEY `by_int_03` (`namespace_id`,`Int03`),
  KEY `by_keyword_01` (`namespace_id`,`Keyword01`),
  KEY `by_keyword_02` (`namespace_id`,`Keyword02`),
  KEY `by_keyword_03` (`namespace_id`,`Keyword03`),
  KEY `by_keyword_04` (`namespace_id`,`Keyword04`),
  KEY `by_keyword_05` (`namespace_id`,`Keyword05`),
  KEY `by_keyword_06` (`namespace_id`,`Keyword06`),
  KEY `by_keyword_07` (`namespace_id`,`Keyword07`),
  KEY `by_keyword_08` (`namespace_id`,`Keyword08`),
  KEY `by_keyword_09` (`namespace_id`,`Keyword09`),
  KEY `by_keyword_10` (`namespace_id`,`Keyword10`),
  KEY `by_text_01` (`Text01`(50)),
  KEY `by_text_02` (`Text02`(50)),
  KEY `by_text_03` (`Text03`(50)),
  KEY `by_keyword_list_01` (`namespace_id`,(cast(`KeywordList01` as char(255) array))),
  KEY `by_keyword_list_02` (`namespace_id`,(cast(`KeywordList02` as char(255) array))),
  KEY `by_keyword_list_03` (`namespace_id`,(cast(`KeywordList03` as char(255) array)))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

SELECT ev.namespace_id, ev.run_id, ev.workflow_type_name, ev.workflow_id, ev.start_time, ev.execution_time, ev.status, ev.close_time, ev.history_length, ev.history_size_bytes, ev.execution_duration, ev.state_transition_count, ev.memo, ev.encoding, ev.task_queue, ev.search_attributes, ev.parent_workflow_id, ev.parent_run_id FROM executions_visibility ev  WHERE namespace_id = "e737babd-1629-4d65-a4a9-349378ca6604" AND (status = 1 and start_time between '2024-04-16 22:05:44.379878' and '2024-04-16 22:05:45.379878' and TemporalNamespaceDivision is null)
 ORDER BY coalesce(close_time, cast('9999-12-31 23:59:59' as datetime)) DESC, start_time DESC, run_id
 LIMIT 1;

2. What did you expect to see? (Required)

No error.

3. What did you see instead (Required)

Sometimes(related to the query plan I believe) we can see the error:

ERROR 1105 (HY000): Can't find column coalesce(test1.executions_visibility.close_time, 9999-12-31 23:59:59) in schema Column: [test1.executions_visibility.namespace_id,test1.executions_visibility.run_id,test1.executions_visibility.start_time,test1.executions_visibility.execution_time,test1.executions_visibility.workflow_id,test1.executions_visibility.workflow_type_name,test1.executions_visibility.status,test1.executions_visibility.close_time,test1.executions_visibility.history_length,test1.executions_visibility.history_size_b

4. What is your TiDB version? (Required)

mysql> select tidb_version()\G
*************************** 1. row ***************************
tidb_version(): Release Version: v8.1.0-alpha-89-g69d7770335
Edition: Community
Git Commit Hash: 69d7770335a7611d05bb2f3a0888c79afb03e92a
Git Branch: master
UTC Build Time: 2024-04-12 18:18:44
GoVersion: go1.21.6
Race Enabled: false
Check Table Before Drop: false
Store: unistore
1 row in set (0.01 sec)
hawkingrei commented 1 month ago

I cannot reproduce it with 

+-----------------------------------------------------------+
| tidb_version()                                            |
+-----------------------------------------------------------+
| Release Version: v8.1.0-alpha-89-g69d7770335              |
| Edition: Community                                        |
| Git Commit Hash: 69d7770335a7611d05bb2f3a0888c79afb03e92a |
| Git Branch: master                                        |
| UTC Build Time: 2024-07-17 10:45:49                       |
| GoVersion: go1.22.5                                       |
| Race Enabled: false                                       |
| Check Table Before Drop: false                            |
| Store: unistore                                           |
+-----------------------------------------------------------+

@bb7133 Can you reproduce it?

bb7133 commented 1 month ago

I cannot reproduce it with

+-----------------------------------------------------------+
| tidb_version()                                            |
+-----------------------------------------------------------+
| Release Version: v8.1.0-alpha-89-g69d7770335              |
| Edition: Community                                        |
| Git Commit Hash: 69d7770335a7611d05bb2f3a0888c79afb03e92a |
| Git Branch: master                                        |
| UTC Build Time: 2024-07-17 10:45:49                       |
| GoVersion: go1.22.5                                       |
| Race Enabled: false                                       |
| Check Table Before Drop: false                            |
| Store: unistore                                           |
+-----------------------------------------------------------+

@bb7133 Can you reproduce it?

I have to say that I am able to reproduce it when trying to run Tempora integration tests: https://github.com/temporalio/temporal.

I didn't find a way to simplify the reproduce steps. Sorry about that.