matrixone [Bug]: `result_scan` return different result with raw sql

Is there an existing issue for the same bug?

[X] I have checked the existing issues.

Branch Name

1.1-dev

Commit ID

v1.1.2-c66ab12ea-2024-03-27

Other Environment Information

- Hardware parameters:
- OS type:
- Others:

Actual Behavior

Run SQL explain select * from mo_catalog.mo_user; in SQL editor, and get the result with result_scan:

2024/03/27 02:12:13.155406 +0000 DEBUG [email protected]/finisher_api.go:610 trace {"elapsed": "65.721634ms", "rows": 0, "sql": "/* cloud_user */explain select * from mo_catalog.mo_user;"}

mysql> select statement from system.statement_info where statement_id = '018e7dad-e8a0-71fb-9e91-aa5a161c847c' and request_at > '2024-03-27 02:12:00';
+------------------------------------------+
| statement                                |
+------------------------------------------+
| explain select * from mo_catalog.mo_user |
+------------------------------------------+
1 row in set (0.14 sec)

mysql> select * from result_scan('018e7dad-e8a0-71fb-9e91-aa5a161c847c') as t limit 0,1000;
+---------------------+
| connection_id()     |
+---------------------+
| 8386658438904696839 |
|                   0 |
+---------------------+
2 rows in set (0.14 sec)

Expected Behavior

result_scan get the correct result

Steps to Reproduce

Cannot reproduce persistently, but get another case:

mysql> /* cloud_user */explain select * from mo_catalog.mo_user;
+----------------------------------------+
| QUERY PLAN                             |
+----------------------------------------+
| Project                                |
|   ->  Table Scan on mo_catalog.mo_user |
+----------------------------------------+
2 rows in set (0.12 sec)

mysql> /* cloud_nonuser */ select database() as db,last_query_id() as query_id;
+----------+--------------------------------------+
| db       | query_id                             |
+----------+--------------------------------------+
| tpch_sf1 | 018e7e8c-fe94-7aff-9707-8d175bcfc07b |
+----------+--------------------------------------+
1 row in set (0.05 sec)

mysql> select * from result_scan('018e7e8c-fe94-7aff-9707-8d175bcfc07b') as t limit 0,1000;
+---------------------+
| connection_id()     |
+---------------------+
| 8386658438904696839 |
|                   0 |
+---------------------+
2 rows in set (0.26 sec)

mysql> select * from system.statement_info where statement_id = '018e7e8c-fe94-7aff-9707-8d175bcfc07b' and request_at >= '2024-03-27 06:10:00';



### Additional information

Cloud dev
Same as https://github.com/matrixorigin/matrixone/issues/9145

Mar 27 '24 06:03 DanielZhangQD

在定位中。

Apr 01 '24 10:04 daviszhen

confirm，closed main commit:652cc255d9075ad1cf683f88bcd66d132fe98dcc mysql> /* cloud_user */explain select * from mo_catalog.mo_user; +----------------------------------------+ | QUERY PLAN | +----------------------------------------+ | Project | | -> Table Scan on mo_catalog.mo_user | +----------------------------------------+ 2 rows in set (0.17 sec)

mysql> /* cloud_nonuser */ select database() as db,last_query_id() as query_id; +------+--------------------------------------+ | db | query_id | +------+--------------------------------------+ | db | 018f0580-e585-7323-b2a7-d9d4074a7206 | +------+--------------------------------------+ 1 row in set (0.00 sec)

mysql> select * from result_scan('018f0580-e585-7323-b2a7-d9d4074a7206') as t limit 0,1000; +----------------------------------------+ | QUERY PLAN | +----------------------------------------+ | Project | | -> Table Scan on mo_catalog.mo_user | +----------------------------------------+ 2 rows in set (0.01 sec)

mysql> /* cloud_user */explain select * from insert_ignore_03; +-----------------------------------------+ | QUERY PLAN | +-----------------------------------------+ | Project | | -> Table Scan on db.insert_ignore_03 | +-----------------------------------------+ 2 rows in set (0.10 sec)

mysql> /* cloud_nonuser */ select database() as db,last_query_id() as query_id; +------+--------------------------------------+ | db | query_id | +------+--------------------------------------+ | db | 018f0582-45aa-7b10-9909-9358f777f641 | +------+--------------------------------------+ 1 row in set (0.00 sec)

mysql> select * from result_scan('018f0582-45aa-7b10-9909-9358f777f641') as t limit 0,1000; +-----------------------------------------+ | QUERY PLAN | +-----------------------------------------+ | Project | | -> Table Scan on db.insert_ignore_03 | +-----------------------------------------+ 2 rows in set (0.01 sec)

Apr 22 '24 11:04 heni02

explain analyze has the similar issue:

mysql> /* cloud_nonuser */select connection_id();/* cloud_nonuser */ use `mo_sample_data_tpch_sf1`;/* cloud_user */explain analyze select * from mo_catalog.mo_user;/* cloud_nonuser */ select database() as db,last_query_id() as query_id;
+-----------------+
| connection_id() |
+-----------------+
|        18087682 |
+-----------------+
1 row in set (0.03 sec)

Query OK, 0 rows affected (0.03 sec)

+------------------------------------------------------------------------------------------------------------------------------------+
| QUERY PLAN                                                                                                                         |
+------------------------------------------------------------------------------------------------------------------------------------+
| Project                                                                                                                            |
|   Analyze: timeConsumed=0ms waitTime=0ms inputRows=1 outputRows=1 InputSize=193bytes OutputSize=193bytes MemorySize=193bytes       |
|   ->  Table Scan on mo_catalog.mo_user                                                                                             |
|         Analyze: timeConsumed=0ms waitTime=0ms inputRows=1 outputRows=1 InputSize=193bytes OutputSize=193bytes MemorySize=386bytes |
+------------------------------------------------------------------------------------------------------------------------------------+
4 rows in set (0.15 sec)

+-------------------------+--------------------------------------+
| db                      | query_id                             |
+-------------------------+--------------------------------------+
| mo_sample_data_tpch_sf1 | 018f28ec-a00d-7002-9c51-9f901f4ec65a |
+-------------------------+--------------------------------------+
1 row in set (0.03 sec)
mysql> select * from result_scan('018f28ec-a00d-7002-9c51-9f901f4ec65a') as t limit 0,1000;
+---------------------+
| connection_id()     |
+---------------------+
| 8386658438904696839 |
|                   0 |
|                   0 |
|          4294967295 |
+---------------------+
4 rows in set (0.30 sec)

mysql>
Display all 768 possibilities? (y or n)
mysql> select statement from system.statement_info where statement_id = '018f28ec-a00d-7002-9c51-9f901f4ec65a' and request_at > '2024-04-28 08:12:00';
+--------------------------------------------------+
| statement                                        |
+--------------------------------------------------+
| explain analyze select * from mo_catalog.mo_user |
+--------------------------------------------------+
1 row in set (0.15 sec)

Apr 29 '24 08:04 DanielZhangQD

未投入

May 05 '24 12:05 daviszhen

未投入

May 09 '24 10:05 daviszhen

未投入

May 09 '24 10:05 daviszhen

未投入

May 14 '24 12:05 daviszhen

未投入

May 17 '24 12:05 daviszhen

改掉了。


mysql> /* cloud_nonuser */select connection_id();/* cloud_nonuser */ use `mo_sample_data_tpch_sf1`;/* cloud_user */explain analyze select * from mo_catalog.mo_user;/* cloud_nonuser */ select database() as db,last_query_id() as query_id;
+-----------------+
| connection_id() |
+-----------------+
|           10779 |
+-----------------+
1 row in set (0.00 sec)

ERROR 1049 (HY000): invalid database mo_sample_data_tpch_sf1
+------------------------------------------------------------------------------------------------------------------------------------+
| QUERY PLAN                                                                                                                         |
+------------------------------------------------------------------------------------------------------------------------------------+
| Project                                                                                                                            |
|   Analyze: timeConsumed=0ms waitTime=0ms inputRows=2 outputRows=2 InputSize=386bytes OutputSize=386bytes MemorySize=0bytes         |
|   ->  Table Scan on mo_catalog.mo_user                                                                                             |
|         Analyze: timeConsumed=0ms waitTime=0ms inputRows=2 outputRows=2 InputSize=386bytes OutputSize=386bytes MemorySize=386bytes |
+------------------------------------------------------------------------------------------------------------------------------------+
4 rows in set (0.01 sec)

+------+--------------------------------------+
| db   | query_id                             |
+------+--------------------------------------+
|      | 018f9609-9215-7712-ac4c-ff391ccce777 |
+------+--------------------------------------+
1 row in set (0.01 sec)

mysql> select * from meta_scan('018f9609-9215-7712-ac4c-ff391ccce777') as t \G
*************************** 1. row ***************************
    query_id: 018f9609-9215-7712-ac4c-ff391ccce777
   statement: explain analyze select * from mo_catalog.mo_user
  account_id: 0
     role_id: 0
 result_path: SHARED:/query_result/sys_018f9609-9215-7712-ac4c-ff391ccce777_1.blk
 create_time: 2024-05-20 20:46:00
 result_size: 0.0003681182861328125
      tables: mo_user
     user_id: 1
expired_time: 2024-05-21 20:46:00
   ColumnMap: QUERY PLAN -> QUERY PLAN
1 row in set (0.00 sec)

mysql> select * from result_scan('018f9609-9215-7712-ac4c-ff391ccce777') as t;
+------------------------------------------------------------------------------------------------------------------------------------+
| QUERY PLAN                                                                                                                         |
+------------------------------------------------------------------------------------------------------------------------------------+
| Project                                                                                                                            |
|   Analyze: timeConsumed=0ms waitTime=0ms inputRows=2 outputRows=2 InputSize=386bytes OutputSize=386bytes MemorySize=0bytes         |
|   ->  Table Scan on mo_catalog.mo_user                                                                                             |
|         Analyze: timeConsumed=0ms waitTime=0ms inputRows=2 outputRows=2 InputSize=386bytes OutputSize=386bytes MemorySize=386bytes |
+------------------------------------------------------------------------------------------------------------------------------------+
4 rows in set (0.01 sec)

May 20 '24 12:05 daviszhen

testing

May 27 '24 06:05 aressu1985

confirm,closed main commit:406d304aac24192bd806ac9d5809dff775b6eeb4 mysql> /* cloud_nonuser /select connection_id();/ cloud_nonuser / use mo_sample_data_tpch_sf1;/ cloud_user /explain analyze select * from mo_catalog.mo_user;/ cloud_nonuser */ select database() as db,last_query_id() as query_id; +-----------------+ | connection_id() | +-----------------+ | 881 | +-----------------+ 1 row in set (0.00 sec)

ERROR 1049 (HY000): invalid database mo_sample_data_tpch_sf1 +--------------------------------------------------------------------------------------------------------------------------------------------------+ | QUERY PLAN | +--------------------------------------------------------------------------------------------------------------------------------------------------+ | Project | | Analyze: timeConsumed=0ms waitTime=1ms inputRows=2 outputRows=2 InputSize=386bytes OutputSize=386bytes MemorySize=0bytes | | -> Table Scan on mo_catalog.mo_user | | Analyze: timeConsumed=1ms waitTime=0ms inputBlocks=1 inputRows=2 outputRows=2 InputSize=386bytes OutputSize=386bytes MemorySize=386bytes | +--------------------------------------------------------------------------------------------------------------------------------------------------+ 4 rows in set (0.00 sec)

+------+--------------------------------------+ | db | query_id | +------+--------------------------------------+ | | 018fcdb6-400d-7b46-abf6-d11552d7e33e | +------+--------------------------------------+ 1 row in set (0.00 sec)

1.2-dev commit:6b9f5de45d139a4480182dd0c9ebc502d9700e29 mysql> /* cloud_nonuser /select connection_id();/ cloud_nonuser / use mo_sample_data_tpch_sf1;/ cloud_user /explain analyze select * from mo_catalog.mo_user;/ cloud_nonuser */ select database() as db,last_query_id() as query_id; +-----------------+ | connection_id() | +-----------------+ | 72 | +-----------------+ 1 row in set (0.00 sec)

ERROR 1049 (HY000): invalid database mo_sample_data_tpch_sf1 +------------------------------------------------------------------------------------------------------------------------------------+ | QUERY PLAN | +------------------------------------------------------------------------------------------------------------------------------------+ | Project | | Analyze: timeConsumed=0ms waitTime=1ms inputRows=2 outputRows=2 InputSize=386bytes OutputSize=386bytes MemorySize=0bytes | | -> Table Scan on mo_catalog.mo_user | | Analyze: timeConsumed=1ms waitTime=0ms inputRows=2 outputRows=2 InputSize=386bytes OutputSize=386bytes MemorySize=386bytes | +------------------------------------------------------------------------------------------------------------------------------------+ 4 rows in set (0.00 sec)

+------+--------------------------------------+ | db | query_id | +------+--------------------------------------+ | | 018fcdec-0fbb-7789-8d1f-93ba5c295bbd | +------+--------------------------------------+ 1 row in set (0.00 sec)

May 31 '24 09:05 heni02