The maximum number of columns without aliases in prepared statements

Translated
1

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

Original topic: 关于预处理语句不设置别名的列的最大数量

| username: Qiuchi

【TiDB Version】6.5

When using MyBatis, some parameters in the XML mapper did not specify column aliases, resulting in a Duplicate column name "NULL" error. This is due to differences in behavior between TiDB and MySQL. I hope to understand the related restrictions of the two databases and whether it is possible to bypass this error without adding aliases to the columns.

MySQL does not allow preparing the following statement, but TiDB does:

prepare stmt from
    '
    select tmp.*
    from (
        select ?, ?
        from dual
        ) tmp
    ';

However, TiDB does not allow preparing the following statement or using more than two ? placeholders:

# Compared to the first one, only the "from dual" is removed, and if "from dual" is not written on a new line, it cannot be prepared either
prepare stmt from
    '
    select tmp.*
    from (
        select ?, ?
        ) tmp
    ';

# Declaring three unnamed placeholders
prepare stmt from
    '
    select tmp.*
    from (
        select ?, ?, ?
        ) tmp
    ';

Both databases return the error message Duplicate column name ‘?’ when reporting errors.

When using Oracle previously, since the bind of prepared statements was provided through :n placeholders, it did not seem to be restricted by not setting aliases for parameters. The following prepared statement can be compiled and executed normally:

declare
    sql_stmt varchar2(200);
begin
    sql_stmt := '
    select tmp.*
    from (
        select :1, :2, :3
        from dual
        ) tmp
    ';
    execute immediate sql_stmt using 'value1', 'value2';
end;
2
| username: tidb菜鸟一只 | Original post link

MySQL supports the PREPARE statement and also supports multiple placeholders. PREPARE stmt FROM ‘SELECT * FROM tt WHERE id = ? AND name = ?’;

3
| username: Qiuchi | Original post link

Bro, take a closer look at the issue… or try that SQL.

4
| username: tidb菜鸟一只 | Original post link

I understand your point. The difference lies in how MySQL and TiDB handle the use of the prepare syntax with the dual virtual table. In MySQL, the alias for each placeholder column in the dual table is the same, whereas in TiDB, similar to Oracle, the alias for each placeholder column in the dual table is different. Therefore,

PREPARE stmt FROM
    '
    select tmp.*
    from (
        select ?, ?
        from dual
        ) tmp
    ';

can be executed in TiDB and Oracle, but not in MySQL.

prepare stmt from
    '
    select tmp.*
    from (
        select ?, ?
        ) tmp
    ';

cannot be executed in either TiDB or MySQL.

This suggests that TiDB has likely done some secondary development for the dual virtual table.

5
| username: Qiuchi | Original post link

However, if there are three placeholders, TiDB won’t work either… Also, is it true that select 1; and select 1 from dual; are actually different in MySQL? I used to think the former was just a shorthand.