Compatibility issues with lower versions of TiDB after adding parentheses to SubqueryExpr in tidb parser's RestoreWithDefaultDB

Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.

Original topic: tidb parser RestoreWithDefaultDB SubqueryExpr 加括号后,tidb低版本不兼容问题

| username: lazzyfu

Problem Description

When using the RestoreWithDefaultDB method to restore SQL, subqueries automatically add parentheses, causing syntax errors when TiDB executes the SQL. The affected versions are: TiDB V5.04 and below.

TiDB Parser Version

github.com/pingcap/tidb/parser v0.0.0-20230530104642-635a4362235e

Table Structure

CREATE TABLE `a` (
    `ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Primary Key',
    PRIMARY KEY (`ID`)
) ENGINE = InnoDB AUTO_INCREMENT = 3 DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

CREATE TABLE `b` (
    `ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Primary Key',
    PRIMARY KEY (`ID`)
) ENGINE = InnoDB AUTO_INCREMENT = 3 DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

CREATE TABLE `c` (
    `ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Primary Key',
    PRIMARY KEY (`ID`)
) ENGINE = InnoDB AUTO_INCREMENT = 3 DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

Original SQL Statement

SELECT
    *
FROM
    (
        SELECT
            prov.id
        FROM
            (
                SELECT
                    id
                FROM
                    `a`
            ) AS `prov`
            JOIN (
                SELECT
                    id
                FROM
                    `b`
            ) AS `mon`
            LEFT JOIN (
                SELECT
                    id
                FROM
                    `c` AS `d`
            ) AS `amt` ON `prov`.`id` = `amt`.`id`
            AND `mon`.`id` = `amt`.`id`
    ) AS `r`;

Parsing the Original SQL into stmt and then Restoring SQL using utilparser.RestoreWithDefaultDB

SELECT
    *
FROM
    (
        SELECT
            prov.id
        FROM
            (
                (
                    SELECT
                        id
                    FROM
                        a
                ) AS prov
                JOIN (
                    SELECT
                        id
                    FROM
                        b
                ) AS mon
            )
            LEFT JOIN (
                SELECT
                    id
                FROM
                    c AS d
            ) AS amt ON prov.id = amt.id
            AND mon.id = amt.id
    ) AS r

It can be observed that the restored SQL automatically adds parentheses, causing errors in lower versions of TiDB.

Since the company currently has multiple TiDB clusters with different versions, how can this be resolved? How to handle the SubqueryExpr part when using the utilparser.RestoreWithDefaultDB method?

| username: mono | Original post link

This is also considered normal. The official documentation clearly states that downgrades and version rollbacks are not supported. Generally, it is upward compatible but not downward compatible.

| username: Kongdom | Original post link

It seems that we can only detect the database version in the program and then handle it with branching. We are currently compatible with MySQL and TiDB, and we determine compatibility within the program.

| username: redgame | Original post link

It is recommended to modify the query statement.