bug: information_schema.columns doesn't work #10271
Labels
C-bug
Category: something isn't working
Comments
|
cc @TCeason |
|
I can query in my local 'root'@mysqldb 08:38:52 [information_schema]> select * from information_schema.columns WHERE table_name= 'fct_server' limit 5;
Empty set (0.21 sec)
Read 247 rows, 28.60 KiB in 0.158 sec., 1.56 thousand rows/sec., 181.06 KiB/sec.
'root'@mysqldb 08:40:06 [information_schema]> select version();
+-------------------------------------------------------------------------------------+
| version() |
+-------------------------------------------------------------------------------------+
| 8.0.26-v0.8.145-nightly-46491e0(rust-1.68.0-nightly-2023-02-23T07:20:55.219302423Z) |
+-------------------------------------------------------------------------------------+
1 row in set (0.03 sec)
'root'@mysqldb 08:40:12 [information_schema]> desc information_schema.columns;
+--------------------------+------------------+------+---------+-------+
| Field | Type | Null | Default | Extra |
+--------------------------+------------------+------+---------+-------+
| table_catalog | VARCHAR | NO | "" | |
| table_schema | VARCHAR | NO | "" | |
| table_name | VARCHAR | NO | "" | |
| column_name | VARCHAR | NO | "" | |
| ordinal_position | TINYINT UNSIGNED | NO | 0 | |
| column_default | NULL | NO | NULL | |
| column_comment | NULL | NO | NULL | |
| column_key | NULL | NO | NULL | |
| is_nullable | VARCHAR | NO | "" | |
| data_type | VARCHAR | NO | "" | |
| column_type | VARCHAR | NO | "" | |
| character_maximum_length | NULL | NO | NULL | |
| character_octet_length | NULL | NO | NULL | |
| numeric_precision | NULL | NO | NULL | |
| numeric_precision_radix | NULL | NO | NULL | |
| numeric_scale | NULL | NO | NULL | |
| datetime_precision | NULL | NO | NULL | |
| character_set_catalog | NULL | NO | NULL | |
| character_set_schema | NULL | NO | NULL | |
| character_set_name | NULL | NO | NULL | |
| collation_catalog | NULL | NO | NULL | |
| collation_schema | NULL | NO | NULL | |
| collation_name | NULL | NO | NULL | |
| domain_catalog | NULL | NO | NULL | |
| domain_schema | NULL | NO | NULL | |
| domain_name | NULL | NO | NULL | |
| extra | NULL | NO | NULL | |
+--------------------------+------------------+------+---------+-------+
27 rows in set (0.07 sec)
Read 0 rows, 0.00 B in 0.022 sec., 0 rows/sec., 0.00 B/sec.
Please provide result of |
|
And also provide result of 'root'@mysqldb 08:43:58 [test]> show create table information_schema.columns;
+---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| columns | CREATE TABLE `columns` (
) ENGINE=VIEW QUERY='SELECT
database AS table_catalog,
database AS table_schema,
table AS table_name,
name AS column_name,
1 AS ordinal_position,
NULL AS column_default,
NULL AS column_comment,
NULL AS column_key,
is_nullable AS is_nullable,
data_type AS data_type,
data_type AS column_type,
NULL AS character_maximum_length,
NULL AS character_octet_length,
NULL AS numeric_precision,
NULL AS numeric_precision_radix,
NULL AS numeric_scale,
NULL AS datetime_precision,
NULL AS character_set_catalog,
NULL AS character_set_schema,
NULL AS character_set_name,
NULL AS collation_catalog,
NULL AS collation_schema,
NULL AS collation_name,
NULL AS domain_catalog,
NULL AS domain_schema,
NULL AS domain_name,
NULL AS extra
FROM system.columns;' |
+---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.06 sec)
Read 0 rows, 0.00 B in 0.019 sec., 0 rows/sec., 0.00 B/sec.
'root'@mysqldb 08:44:42 [test]> show settings;
+------------------------------------+-------------+-------------+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+
| name | value | default | level | description | type |
+------------------------------------+-------------+-------------+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+
| collation | binary | binary | SESSION | Char collation, support "binary" "utf8" default value: binary | String |
| enable_async_insert | 0 | 0 | SESSION | Whether the client open async insert mode, default value: 0. | UInt64 |
| enable_bushy_join | 0 | 0 | SESSION | Enable generating bushy join plan in optimizer | UInt64 |
| enable_cbo | 1 | 1 | SESSION | If enable cost based optimization, default value: 1. | UInt64 |
| enable_distributed_eval_index | 1 | 1 | SESSION | If enable distributed eval index, default value: 1 | UInt64 |
| enable_new_processor_framework | 1 | 1 | SESSION | Enable new processor framework if value != 0, default value: 1. | UInt64 |
| enable_planner_v2 | 1 | 1 | SESSION | Enable planner v2 by setting this variable to 1, default value: 1. | UInt64 |
| enable_query_result_cache | 0 | 0 | SESSION | Enable the cache result of each query. It's disabled by default. | UInt64 |
| flight_client_timeout | 60 | 60 | SESSION | Max duration the flight client request is allowed to take in seconds. By default, it is 60 seconds. | UInt64 |
| group_by_two_level_threshold | 10000 | 10000 | SESSION | The threshold of keys to open two-level aggregation, default value: 10000. | UInt64 |
| input_read_buffer_size | 1048576 | 1048576 | SESSION | The size of buffer in bytes for input with format. By default, it is 1MB. | UInt64 |
| load_file_metadata_expire_hours | 168 | 168 | SESSION | How many hours will the COPY file metadata expired in the metasrv, default value: 24*7=7days | UInt64 |
| max_block_size | 65536 | 65536 | SESSION | Maximum block size for reading, default value: 65536. | UInt64 |
| max_execute_time | 0 | 0 | SESSION | The maximum query execution time. it means no limit if the value is zero. default value: 0. | UInt64 |
| max_inlist_to_or | 3 | 3 | SESSION | Max size in inlist expression that will convert to or combinator, default value: 3. | UInt64 |
| max_memory_usage | 13201388339 | 13201388339 | SESSION | The maximum memory usage for processing single query, in bytes. By default the value is determined automatically. | UInt64 |
| max_result_cache_bytes | 1048576 | 1048576 | SESSION | The maximum bytes of the result cache for one query, default: 1048576 bytes (1MB). | UInt64 |
| max_result_rows | 0 | 0 | SESSION | Auto limit max result rows if user not specify the limit, default is 0 means no limit | UInt64 |
| max_storage_io_requests | 8 | 8 | SESSION | The maximum number of concurrent IO requests. By default the value is determined automatically. | UInt64 |
| max_threads | 8 | 8 | SESSION | The maximum number of threads to execute the request. By default the value is determined automatically. | UInt64 |
| parquet_uncompressed_buffer_size | 2097152 | 2097152 | SESSION | Parquet decompresses buffer size. default: 2MB | UInt64 |
| prefer_broadcast_join | 1 | 1 | SESSION | If enable broadcast join, default value: 1 | UInt64 |
| quoted_ident_case_sensitive | 1 | 1 | SESSION | Case sensitivity of quoted identifiers, default value: 1 (aka case-sensitive). | UInt64 |
| result_cache_ttl | 300 | 300 | SESSION | Time-to-live of query result cache, default: 300 seconds (5 minutes). | UInt64 |
| retention_period | 12 | 12 | SESSION | The retention_period in hours. By default the value is 12 hours. | UInt64 |
| sandbox_tenant | | | SESSION | Inject a custom sandbox_tenant into this session, it's only for testing purpose and take effect when the internal_enable_sandbox_tenant is on | String |
| sql_dialect | PostgreSQL | PostgreSQL | SESSION | SQL dialect, support "PostgreSQL" "MySQL" and "Hive", default value: "PostgreSQL". | String |
| storage_fetch_part_num | 2 | 2 | SESSION | The max number of part each read cycle. | UInt64 |
| storage_io_max_page_bytes_for_read | 524288 | 524288 | SESSION | The maximum bytes of one IO request to read. Default the value is 512KB | UInt64 |
| storage_io_min_bytes_for_seek | 48 | 48 | SESSION | If the distance between two IO ranges to be read in one file is less than storage_io_min_bytes_for_seek, then Databend sequentially reads a range of file that contains both ranges, thus avoiding extra seek. Default value is 48Bytes | UInt64 |
| storage_read_buffer_size | 1048576 | 1048576 | SESSION | The size of buffer in bytes for buffered reader of dal. By default, it is 1MB. | UInt64 |
| timezone | UTC | UTC | SESSION | Timezone, default value: "UTC". | String |
| tolerate_inconsistent_result_cache | 0 | 0 | SESSION | Tolerate inconsistent result cache. It's disabled by default. | UInt64 |
| unquoted_ident_case_sensitive | 0 | 0 | SESSION | Case sensitivity of unquoted identifiers, default value: 0 (aka case-insensitive). | UInt64 |
| wait_for_async_insert | 1 | 1 | SESSION | Whether the client wait for the reply of async insert, default value: 1. | UInt64 |
| wait_for_async_insert_timeout | 100 | 100 | SESSION | The timeout in seconds for waiting for processing of async insert, default value: 100. | UInt64 |
+------------------------------------+-------------+-------------+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+
36 rows in set (0.09 sec)
Read 36 rows, 5.85 KiB in 0.018 sec., 1.97 thousand rows/sec., 321.08 KiB/sec.
|
|
One difference I see is the version()s we're using: It's also interesting but unrelated that the default dialect is Postgres rather than MySql. |
|
It's ok on my local with commit version 425cad1. And if query with a not exists column, databend will have some other error hints like this example: 'root'@mysqldb 07:58:37 [trc]> select version();
+------------------------------------------------------------------------------------+
| version() |
+------------------------------------------------------------------------------------+
| 8.0.26-v0.9.56-nightly-425cad1(rust-1.68.0-nightly-2023-03-01T23:49:50.953368995Z) |
+------------------------------------------------------------------------------------+
1 row in set (0.05 sec)
'root'@mysqldb 07:58:43 [trc]> select * from information_schema.columns WHERE table_name = 'fct_server' limit 5;
Empty set (0.23 sec)
Read 253 rows, 29.27 KiB in 0.175 sec., 1.44 thousand rows/sec., 166.86 KiB/sec.
'root'@mysqldb 07:58:47 [trc]> select * from information_schema.columns WHERE table_namecc = 'fct_server' limit 5;
ERROR 1105 (HY000): Code: 1065, displayText = error:
--> SQL:1:48
|
1 | select * from information_schema.columns WHERE table_namecc = 'fct_server' limit 5
| ^^^^^^^^^^^^ column doesn't exist
.
So if you can still reproduce please also provide the query log. |
|
By the way, information_schema.columns is a view of system.columns. 'root'@mysqldb 08:04:28 [system]> select * from system.columns limit 1;
+------+----------+---------------------+--------+-----------+--------------+--------------------+-------------+---------+
| name | database | table | type | data_type | default_kind | default_expression | is_nullable | comment |
+------+----------+---------------------+--------+-----------+--------------+--------------------+-------------+---------+
| name | system | malloc_stats_totals | String | VARCHAR | | | NO | |
+------+----------+---------------------+--------+-----------+--------------+--------------------+-------------+---------+ |
|
Do you have any view in the catalog? You can drop the view and recreate them. |
|
One thing I've noticed is that if I @sundy-li Good idea, you lead me to the fix! Creating a view that doesn't work is perfectly valid, it doesn't error until you try to SELECT from it. When I delete my views that are bad like that, everything is good. Here is an example of one of my bad views: I ran |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Search before asking
Version
v0.9.55-nightly (also tried 0.9.56)
What's Wrong?
#4659 doesn't work anymore, at least the columns table
How to Reproduce?
Are you willing to submit PR?
The text was updated successfully, but these errors were encountered: