[MDEV-32117] PERSISTENT FOR clause is applied to all tables, and wrong documentation - Jira

Details

Type: Bug
Status: Open (View Workflow)
Priority: Major
Resolution: Unresolved
Affects Version/s: 10.4(EOL), 10.5, 10.6, 10.7(EOL), 10.8(EOL), 10.9(EOL), 10.10(EOL), 10.11, 11.0(EOL), 11.1(EOL), 11.2(EOL), 11.3(EOL)
Fix Version/s: 10.5, 10.6, 10.11
Component/s: OTHER
Labels:
None

Description

If I use "ANALYZE TABLE t1,t2" with two tables, the PERSISTENT FOR clause in the second table t2 also affects the output for the first table t1
If I use "ANALYZE TABLE t1,t2" with two tables, the PERSISTENT FOR clause in the first table t1 also affects the output for the first table t2

CREATE OR REPLACE TABLE t1 (a INT, KEY(a));

CREATE OR REPLACE TABLE t2 (b INT, KEY(b));

ANALYZE TABLE t1, t2 PERSISTENT FOR COLUMNS (b) INDEXES (b);

+---------+---------+----------+-----------------------------------------+

| Table   | Op      | Msg_type | Msg_text                                |

+---------+---------+----------+-----------------------------------------+

| test.t1 | analyze |   status | Engine-independent statistics collected |

| test.t1 | analyze |    error |                        Invalid argument |

| test.t2 | analyze |   status | Engine-independent statistics collected |

| test.t2 | analyze |   status |                                      OK |

+---------+---------+----------+-----------------------------------------+

ANALYZE TABLE t1, t2 PERSISTENT FOR COLUMNS (a) INDEXES (a);

+---------+---------+----------+-----------------------------------------+

| Table   | Op      | Msg_type | Msg_text                                |

+---------+---------+----------+-----------------------------------------+

| test.t1 | analyze |   status | Engine-independent statistics collected |

| test.t1 | analyze |   status |                                      OK |

| test.t2 | analyze |   status | Engine-independent statistics collected |

| test.t2 | analyze |    error |                        Invalid argument |

+---------+---------+----------+-----------------------------------------+

ANALYZE TABLE t1 PERSISTENT FOR COLUMNS (a) INDEXES (a), t2;

+---------+---------+----------+-----------------------------------------+

| Table   | Op      | Msg_type | Msg_text                                |

+---------+---------+----------+-----------------------------------------+

| test.t1 | analyze |   status | Engine-independent statistics collected |

| test.t1 | analyze |   status |                                      OK |

| test.t2 | analyze |   status | Engine-independent statistics collected |

| test.t2 | analyze |    error |                        Invalid argument |

+---------+---------+----------+-----------------------------------------+

ANALYZE TABLE t1 PERSISTENT FOR COLUMNS (b) INDEXES (b), t2;

+---------+---------+----------+-----------------------------------------+

| Table   | Op      | Msg_type | Msg_text                                |

+---------+---------+----------+-----------------------------------------+

| test.t1 | analyze |   status | Engine-independent statistics collected |

| test.t1 | analyze |    error |                        Invalid argument |

| test.t2 | analyze |   status | Engine-independent statistics collected |

| test.t2 | analyze |   status |                                      OK |

+---------+---------+----------+-----------------------------------------+

Another problem is that the documentation at https://mariadb.com/kb/en/analyze-table/ is obviously incorrect:

ANALYZE [NO_WRITE_TO_BINLOG | LOCAL] TABLE tbl_name [,tbl_name ...]

  [PERSISTENT FOR

    { ALL

      | COLUMNS ([col_name [,col_name ...]]) INDEXES ([index_name [,index_name ...]])

It says the PERSISTENT clause is only possible after the table list. However in fact it's accepted after any tbl_name in the list.

Attachments

Issue Links

duplicates

MDEV-32129 analyze table persistent for column (list) inconsistency

Closed

relates to

MDEV-31431 support sql standard <explicit table> expressions

Open

Activity

Alexander Barkov added a comment - 2023-09-08 03:31 - edited

It's wrong that the implementation uses LEX members to store the result of parsing of the PERSISTENT clause:

LEX::with_persistent_for_clause

LEX::column_list

LEX::index_list

Each table should have its own PERSISTENT clause.

Perhaps we need something like this:

class Analyze_table: public Sql_alloc

public:

  TABLE_LIST *m_table;

  List<LEX_STRING>  m_columns;

  List<LEX_STRING>  m_indexes;

  bool m_with_persistent_for_clause;

};

or even better:

class Analyze_table: public Sql_alloc

public:

  Table_ident m_table; // Or a pointer?

  List<LEX_STRING>  m_columns;

  List<LEX_STRING>  m_indexes;

  bool m_with_persistent_for_clause;

};

and collect the entire ANALYZE into a new member in Sql_cmd_analyze_table:

class Sql_cmd_analyze_table

...

  List<Analyze_table> m_tables;

...

};

Alexander Barkov added a comment - 2023-09-08 03:31 - edited It's wrong that the implementation uses LEX members to store the result of parsing of the PERSISTENT clause: LEX::with_persistent_for_clause LEX::column_list LEX::index_list Each table should have its own PERSISTENT clause. Perhaps we need something like this: class Analyze_table: public Sql_alloc { public : TABLE_LIST *m_table; List<LEX_STRING> m_columns; List<LEX_STRING> m_indexes; bool m_with_persistent_for_clause; }; or even better: class Analyze_table: public Sql_alloc { public : Table_ident m_table; // Or a pointer? List<LEX_STRING> m_columns; List<LEX_STRING> m_indexes; bool m_with_persistent_for_clause; }; and collect the entire ANALYZE into a new member in Sql_cmd_analyze_table: class Sql_cmd_analyze_table { ... List<Analyze_table> m_tables; ... };

People

Assignee:: Alexander Barkov

Reporter:: Alexander Barkov

Votes:: 0 Vote for this issue

Watchers:: 3 Start watching this issue

Dates

Created:: 2023-09-07 08:49

Updated:: 2024-11-22 17:11

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.

MariaDB Server