bug: join unable to get field named

[zhyass]

Search before asking

• I had searched in the issues and found no similar issues.

Version

main

What's Wrong?

mysql> create table t(a int);
Query OK, 0 rows affected (0.07 sec)

mysql> insert into t values(1);
+-------------------------+
| number of rows inserted |
+-------------------------+
|                       1 |
+-------------------------+
1 row in set (0.05 sec)
Read 1 rows, 5.00 B in 0.033 sec., 30.15 rows/sec., 150.75 B/sec.

mysql> insert into t values(2);
+-------------------------+
| number of rows inserted |
+-------------------------+
|                       1 |
+-------------------------+
1 row in set (0.06 sec)
Read 1 rows, 5.00 B in 0.041 sec., 24.63 rows/sec., 123.16 B/sec.

mysql> with tt as (select sum(a) as a from t) select t._row_id, t.a/tt.a from t, tt;
ERROR 1105 (HY000): BadArguments. Code: 1006, Text = Unable to get field named "3". Valid fields: ["0", "2"].

mysql> with tt as (select sum(a) as a from t) select t._row_id, t.a/tt.a from tt,t;
+----------------------+--------------------+
| _row_id              | a / a              |
+----------------------+--------------------+
| 18446735277616529408 | 0.3333333333333333 |
| 18446739675663040512 | 0.6666666666666666 |
+----------------------+--------------------+
2 rows in set (0.14 sec)
Read 4 rows, 20.00 B in 0.052 sec., 77.03 rows/sec., 385.16 B/sec.

How to Reproduce?

No response

Are you willing to submit PR?

• Yes I am willing to submit a PR!

[sundy-li]

get_table_index will fetch the table by rev order.

t._row_id should bind to the left table t of join instead of the t in CTE.

 pub fn get_table_index(
        &self,
        database_name: Option<&str>,
        table_name: &str,
    ) -> Option<IndexType> {
        // Use `rev` is because a table may be queried multiple times in join clause,
        // and the virtual columns should add to the table newly added.
        self.tables
            .iter()
            .rev()
            .find(|table| match database_name {
                Some(database_name) => {
                    table.database == database_name && table.name == table_name
                        || table.alias_name == Some(table_name.to_string())
                }
                None => {
                    table.name == table_name || table.alias_name == Some(table_name.to_string())
                }
            })
            .map(|table| table.index)
    }