All Products
Search
Document Center

PolarDB for MySQL:Check whether the IMCI is valid for all columns in the SQL statement

Last Updated:Nov 21, 2022

The dbms_imci.check_columnar_index() stored procedure can parse the SQL statement you enter, obtain all the columns used in the SQL statement, and check whether the IMCI is valid for these columns.

  • If the columns for which the IMCI is invalid are involved in a SQL statement, this stored procedure returns the database names, table names, and names of such columns.

  • If the IMCI is valid for all columns in the SQL statement, this stored procedure returns an empty result set.

Prerequisites

  • The PolarDB cluster runs PolarDB for MySQL 8.0.1 and the revision version is 8.0.1.1.30 or later.

  • You have the read (SELECT) permissions on the specified table.

Syntax

dbms_imci.check_columnar_index('<query_string>');

Parameters

Parameter

Description

query_string

The SQL statement to be parsed.

Note
  • The SQL statement must be a valid SELECT statement. Other DML statements such as INSERT, UPDATE, or DELETE are not supported. If the SELECT statement contains errors (for example, a nonexistent column is involved), error messages are returned.

  • query_string must be a string and cannot be a variable value or a query result.

Precautions

  • The stored procedure is case-sensitive.

  • When you use this stored procedure to parse an SQL statement and add the database name before the table name in the SQL statement, the database name is used. If no database name is added before the table name in the SQL statement, you must execute the use db_name statement to switch to the database which contains the table in the SQL statement and then execute the stored procedure.

  • Although no length limit is set for SQL statements, the length of SQL statements that are sent from the client to the database is limited by the thread_stack parameter for parsing SQL statements and parameters such as max_allowed_packet.

  • If the IMCI does not support the data types of the columns in the SQL statement, an error message is returned when the stored procedure is called.

  • The SELECT statement can be the parameterized form of a prepared statement. However, the PREPARE... FROM keyword cannot be added.

  • If the SQL statement contains characters that need to be escaped, such as 'or ", you must escape the characters based on MySQL syntax. Examples:

    • Replace 'in the SQL statement with ''.

      call dbms_imci.check_columnar_index('select t1.a from t1 where t1.b = ''some_string''');
    • Replace "in the SQL statement with "".

      call dbms_imci.check_columnar_index("select t1.a from t1 where t1.b = ""some_string""");

    For more escape rules, see MySQL documentation.

Examples

The t1, t2, and t3 tables are used in the following examples. Call the stored procedure to check whether the IMCI is valid for all columns in the SQL statement.

  1. Execute the following statement to switch to the test database:

    use test;
  2. Execute the following statements to create the t1, t2, and t3 tables. The IMCI is valid for all columns in t3 table.

    create table t1 (a int, b int) engine = innodb;
    create table t2 (a int, b int) engine = innodb;
    create table t3 (a int, b int) engine = innodb comment 'columnar=1';
  3. Call the stored procedure to check whether the IMCI is valid for all columns in the SQL statement.

    • The SQL statement uses columns in the t1 and t2 tables.

      call dbms_imci.check_columnar_index('select count(t1.a) from t1 inner join t2 on t1.a = t2.a group by t1.b');

      Sample result:

      +--------------+------------+-------------+
      | TABLE_SCHEMA | TABLE_NAME | COLUMN_NAME |
      +--------------+------------+-------------+
      | test         | t1         | a           |
      | test         | t1         | b           |
      | test         | t2         | a           |
      +--------------+------------+-------------+
      3 rows in set (0.01 sec)

      The SQL statement uses the t1.a, t1.b, and t2.a columns, and the IMCI is invalid for the three columns. Therefore, this stored procedure returns the database names, table names, and names of the three columns.

    • The columns in the SQL statement belong to the t3 table.

      call dbms_imci.check_columnar_index('select a, b from t3');

      Sample result:

      Empty set (0.00 sec)

      The IMCI is valid for all columns in the t3 table. Therefore, this stored procedure returns an empty result set.