Can't find column with constant expression

[hawkingrei]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

SELECT 

            (SELECT 
                    100.00 
                FROM
                    (SELECT 
                    '2024-09-15' AS DATE ) newTb
                WHERE
                    T0.DATE = DATE_ADD(newTb.DATE, INTERVAL 1 MONTH)) AS 'PROFIT'
    FROM
        (SELECT 
        '2024-09-15' AS DATE
        ) T0
    GROUP BY T0.DATE 
     WITH ROLLUP
    ;

2. What did you expect to see? (Required)

+--------+
| PROFIT |
+--------+
| <null> |
| <null> |
+--------+

3. What did you see instead (Required)

(1105, "Can't find column Column#3 in schema Column: [] Unique key: []")

4. What is your TiDB version? (Required)

+-----------------------------------------------------------+
| tidb_version() |
+-----------------------------------------------------------+
| Release Version: v8.4.0-alpha-187-g3ae6470ea7 |
| Edition: Community |
| Git Commit Hash: 3ae6470 |
| Git Branch: HEAD |
| UTC Build Time: 2024-09-18 02:31:43 |
| GoVersion: go1.21.10 |
| Race Enabled: false |
| Check Table Before Drop: false |
| Store: tikv |
+-----------------------------------------------------------+

[hawkingrei]

if we continue to simple the SQL like this.

 SELECT  (SELECT 100.00 FROM (SELECT '2024-09-15' AS DATE ) newTb ) AS 'PROFIT'     FROM         (SELECT         '2024-09-15' AS DATE         ) T0     GROUP BY T0.DATE      WITH ROLLUP     ;

In TiDB

(1815, "Internal : Can't find a proper physical plan for this query")

In MySQL

+--------+
| PROFIT |
+--------+
| 100.00 |
| 100.00 |
+--------+

[hawkingrei]

This is normal.

SELECT  (SELECT 100.00 FROM (SELECT '2024-09-15' AS DATE ) newTb ) AS 'PROFIT'     FROM         (SELECT         '2024-09-15' AS DATE         ) T0 GROUP BY T0.DATE

[hawkingrei]

if we continue to simple the SQL like this.

 SELECT  (SELECT 100.00 FROM (SELECT '2024-09-15' AS DATE ) newTb ) AS 'PROFIT'     FROM         (SELECT         '2024-09-15' AS DATE         ) T0     GROUP BY T0.DATE      WITH ROLLUP     ;

In TiDB

(1815, "Internal : Can't find a proper physical plan for this query")

In MySQL

+--------+
| PROFIT |
+--------+
| 100.00 |
| 100.00 |
+--------+

it has been fixed by #54536

[AilinKid]

the original issue:
when we in the logical optimization for scalarSubq inside, we applies the predicates push down, here is for EQ(column#3, '2024-09-15'), the second arg is already computed from date_add('2024-09-15', interval 1 month), when this predicate is pushed down to basic datasource operator for the scalarSubq which is tableDual here, we find this predicate couldn't be proceed any further, so we leave it in-place where it arrives which is shown as the code below:

// PredicatePushDown implements LogicalPlan.<1st> interface.
func (p *BaseLogicalPlan) PredicatePushDown(predicates []expression.Expression, opt *optimizetrace.LogicalOptimizeOp) ([]expression.Expression, base.LogicalPlan) {
	if len(p.children) == 0 {
		return predicates, p.self
	}
	child := p.children[0]
	rest, newChild := child.PredicatePushDown(predicates, opt)
	** utilfuncp.AddSelection(p.self, newChild, rest, 0, opt) ** this line
	return nil, p.self
}

the newChild is as what it was, still a tableDual, the predicate is left as selection above it, while, we couldn't resolve this function because tableDual couldn't provide what the predicate required --- the column#3

we periodically find there some admission control problem with the projection's canbePushed conditions' check. After we fixing, the logical tree currently is

xxx
 +---selection[EQ(column#3, date_add(column#6, '2024-09-15', interval 1 month))]
      +---- projection['2024-09-15' ---> column#6]
            +---- tableDual

so the output is still the same, we couldn't resolve column#3 as well, but how did the column#3 is from, from the inner subq'
s perspective, T0.DATE should be a correlated column right?

after some deep insight with the subq build process, i find that the original selection build can output correlated column, while the replacingFunction replace this correlated column with new-generated grouping column, this is not right, because expand related rewrite only can be access from outer and its parent scope, why happened inside here? at last, i find that we forgot to clean the currentBlockExpand info when we stepping into another qb's building process.

we should set it to nil, otherwise, the inner qb will use the outer scope's expand meta to replacing itself functions.