[MCOL-1170] ANALYZE doesn't work in ColumnStore Created: 2018-01-18  Updated: 2023-02-06  Resolved: 2023-01-17

Status: Closed
Project: MariaDB ColumnStore
Component/s: None
Affects Version/s: None
Fix Version/s: 22.08.8

Type: Bug Priority: Major
Reporter: Andrew Hutchings (Inactive) Assignee: David Hall (Inactive)
Resolution: Fixed Votes: 1
Labels: None

Epic Link: ColumnStore Compatibility Improvements
Sprint: 2022-22, 2022-23
Assigned for Review: Denis Khalikov Denis Khalikov
Assigned for Testing: Daniel Lee Daniel Lee (Inactive)

 Description   

The following query fails in ColumnStore whether or not the table is a ColumnStore table:

analyze select * from td where a=1;
 
 
 
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'analyze select * from td where a=1' at line 1



 Comments   
Comment by Elena Stepanova [ 2019-12-25 ]

For Columnstore as a MariaDB plugin, it returns ER_INTERNAL_ERROR instead:

f93bfb9288d020b190f5c73a31223fff6439687d

 
MariaDB [db]> create table t1 (a int, b int) engine=Columnstore;
 
Query OK, 0 rows affected (0.452 sec)
 
 
 
MariaDB [db]> insert into t1 values (1,10),(2,20);
 
Query OK, 2 rows affected (0.248 sec)
 
Records: 2  Duplicates: 0  Warnings: 0
 
 
 
MariaDB [db]> analyze select * from t1 where a =1;
 
ERROR 1815 (HY000): Internal error: Unknown error

Comment by Todd Stoffel (Inactive) [ 2021-01-16 ]

As of 5.5.1, this throws an 1815 error on every other run:

MariaDB [test]> analyze select * from t1 where c1 = 1;
 
+------+---------------+-------+------+---------------+------+---------+------+------+--------+----------+------------+-------+
 
| id   | select_type   | table | type | possible_keys | key  | key_len | ref  | rows | r_rows | filtered | r_filtered | Extra |
 
+------+---------------+-------+------+---------------+------+---------+------+------+--------+----------+------------+-------+
 
|    1 | PUSHED SELECT | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | NULL   |     NULL |       NULL | NULL  |
 
+------+---------------+-------+------+---------------+------+---------+------+------+--------+----------+------------+-------+
 
1 row in set (0.009 sec)
 
 
 
MariaDB [test]> analyze select * from t1 where c1 = 1;
 
ERROR 1815 (HY000): Internal error: Unknown error
 
 
 
MariaDB [test]> analyze select * from t1 where c1 = 1;
 
+------+---------------+-------+------+---------------+------+---------+------+------+--------+----------+------------+-------+
 
| id   | select_type   | table | type | possible_keys | key  | key_len | ref  | rows | r_rows | filtered | r_filtered | Extra |
 
+------+---------------+-------+------+---------------+------+---------+------+------+--------+----------+------------+-------+
 
|    1 | PUSHED SELECT | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | NULL   |     NULL |       NULL | NULL  |
 
+------+---------------+-------+------+---------------+------+---------+------+------+--------+----------+------------+-------+
 
1 row in set (0.010 sec)
 
 
 
MariaDB [test]> analyze select * from t1 where c1 = 1;
 
ERROR 1815 (HY000): Internal error: Unknown error
 
 
 
MariaDB [test]> analyze select * from t1 where c1 = 1;
 
+------+---------------+-------+------+---------------+------+---------+------+------+--------+----------+------------+-------+
 
| id   | select_type   | table | type | possible_keys | key  | key_len | ref  | rows | r_rows | filtered | r_filtered | Extra |
 
+------+---------------+-------+------+---------------+------+---------+------+------+--------+----------+------------+-------+
 
|    1 | PUSHED SELECT | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | NULL   |     NULL |       NULL | NULL  |
 
+------+---------------+-------+------+---------------+------+---------+------+------+--------+----------+------------+-------+
 
1 row in set (0.010 sec)

Comment by David Hall (Inactive) [ 2022-10-13 ]

Analyze needs to be completed differently than a normal query. In server, when an ANALYZE is seen, it calls init_scan() immediatly followed by end_scan(). This leaves the sqlfrontendsession (ExeMgr) in a state where it expects to return rows. This patch fixes end_scan to clean this up via reads and writes to get everything back in synch.

ANALYZE should display the number of rows to be displayed if the query were run normally. We have that information available, but no way to return it. A modification to server side to ask for that in the handler is required.

This patch also includes a beautification of sqlfrontsessionthread.cpp since it looked bad. The important change is at line 774
if (!swallowRows)
which short circuits the actual return of data

Comment by Daniel Lee (Inactive) [ 2022-10-24 ]

Is this ready for QA? I don't see the PR being merged yet. I tested the latest build from Drone and the issue still exists.

Comment by Daniel Lee (Inactive) [ 2022-10-24 ]

In a new MariaDB session, the first analyze query did not error out, but from the 2nd one and on.

Comment by Daniel Lee (Inactive) [ 2023-01-17 ]

Build verified: 23.02

engine: 35c8359d90cefef25794bdc908aac9afc94b22a3
server: 689041a5344614e8e3416659b71a6754bb65e490
buildNo: 6492

No error returned for repeated executions.

Generated at Thu Feb 08 02:26:44 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.