Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Recursive CTE UNION field indexing failure #5631

Closed
max-hoffman opened this issue Mar 24, 2023 · 2 comments
Closed

Recursive CTE UNION field indexing failure #5631

max-hoffman opened this issue Mar 24, 2023 · 2 comments
Labels
analyzer bug Something isn't working sql Issue with SQL

Comments

@max-hoffman
Copy link
Contributor

Query:

SELECT COUNT(*) AS `__count` FROM `dcim_rack` WHERE `dcim_rack`.`location_id` IN (
    WITH RECURSIVE __tree(tree_depth, tree_path, tree_ordering, tree_pk) AS (
        SELECT
            0,
            -- Limit to max. 50 levels...
            CAST(CONCAT("", id, "") AS char(1000)),
            CAST(CONCAT("", CONCAT(_name, ""))
                AS char(1000)),
            T.id
        FROM dcim_location T
        WHERE T.parent_id IS NULL

        UNION ALL

        SELECT
            __tree.tree_depth + 1,
            CONCAT(__tree.tree_path, T2.id, ""),
            CONCAT(__tree.tree_ordering, CONCAT(T2._name, "")),
            T2.id
        FROM __tree, dcim_location T2
        WHERE __tree.tree_pk = T2.parent_id
    )
    SELECT U0.`id` FROM `dcim_location` U0 , `__tree` WHERE ((instr(__tree.tree_path, "�7e59b37851044ddb9b3e2b6b51a7dae5�") <> 0) AND (__tree.tree_pk = U0.id)) ORDER BY (`__tree`.tree_ordering) ASC)

error: field "tree_path" is not on schema

Better repro coming soon.

@max-hoffman max-hoffman transferred this issue from dolthub/go-mysql-server Mar 27, 2023
@timsehn timsehn added bug Something isn't working sql Issue with SQL analyzer labels Mar 27, 2023
@max-hoffman
Copy link
Contributor Author

max-hoffman commented Mar 28, 2023

The helper method that collects in-scope table names misses *plan.RecursiveCTE:

diff --git a/sql/analyzer/aliases.go b/sql/analyzer/aliases.go
index 7ff511b8a..be4fdd82c 100644
--- a/sql/analyzer/aliases.go
+++ b/sql/analyzer/aliases.go
@@ -116,7 +116,7 @@ func getTableAliases(n sql.Node, scope *Scope) (TableAliases, error) {
                        rt := getResolvedTable(node.Destination)
                        analysisErr = passAliases.add(rt, rt)
                        return false
-               case *plan.ResolvedTable, *plan.SubqueryAlias, *plan.ValueDerivedTable, *plan.TransformedNamedNode, *plan.RecursiveTable:
+               case *plan.ResolvedTable, *plan.SubqueryAlias, *plan.ValueDerivedTable, *plan.TransformedNamedNode, *plan.RecursiveTable, *plan.RecursiveCte:
                        analysisErr = passAliases.add(node.(sql.Nameable), node.(sql.Nameable))
                        return false

As a result, we are trying to hoist the instr(__tree.tree_path, "�7e59b37851044ddb9b3e2b6b51a7dae5�") <> 0 filter into the parent scope, and fail while reindexing __tree.tree_path.

We need a simple test of the shape select * from xy where x in ( with recursive uv as (...) select u from uv where u = 1) that errors trying to hoist u=1 out of the subexpression.

@PavelSafronov
Copy link
Contributor

Max's update fixes this issue. Resolving.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
analyzer bug Something isn't working sql Issue with SQL
Projects
None yet
Development

No branches or pull requests

3 participants