[MDEV-16629] "Table Does Not Exist" Error from Recursive CTE Query Inside Function - Jira

Fred Hornsey created issue - 2018-06-30 02:59

Fred Hornsey made changes - 2018-06-30 04:22

Field	Original Value	New Value
Description	I'm trying to wrap a recursive cte query inside a function. The query works outside the function and the function statement itself is accepted without error. When the function is used however it reports that the table I'm trying to query inside the function does not exist. A "use" statement is used before the function definition and specifying the database.table combination does not change the result. Here is the exact error if that is helpful: {{ERROR 1146 (42S02) at line 12: Table 'ck2.Characters' doesn't exist}} I've attached the sql file I use to create the database and a separate sql file that contains the function and a attempt at using it.	I'm trying to wrap a recursive cte query inside a function. The query works outside the function and the function statement itself is accepted without error. When the function is used however it reports that the table I'm trying to query inside the function does not exist. A "use" statement is used before the function definition and specifying the database.table combination does not change the result. Here is the exact error if that is helpful: {{ERROR 1146 (42S02) at line 12: Table 'ck2.Characters' doesn't exist}} I've attached the sql file I use to create the database and a separate sql file that contains the function and a attempt at using it. {{use ck2; create or replace function count_descendants(cid int unsigned) returns int unsigned return ( with recursive Descendants as ( select * from Characters where id = cid union where d.id = c.mother_id or d.id = c.real_father_id ) select count(distinct(id)) from Descendants ); select count_descendants(91402); drop function count_descendants; }}

Fred Hornsey made changes - 2018-06-30 04:24

Description

I'm trying to wrap a recursive cte query inside a function. The query works outside the function and the function statement itself is accepted without error. When the function is used however it reports that the table I'm trying to query inside the function does not exist. A "use" statement is used before the function definition and specifying the database.table combination does not change the result.

Here is the exact error if that is helpful:

{{ERROR 1146 (42S02) at line 12: Table 'ck2.Characters' doesn't exist}}

I've attached the sql file I use to create the database and a separate sql file that contains the function and a attempt at using it.

{{use ck2;

create or replace function count_descendants(cid int unsigned) returns int unsigned return (
    with recursive Descendants as (
        select * from Characters where id = cid
        union

            where d.id = c.mother_id or d.id = c.real_father_id
    ) select count(distinct(id)) from Descendants
    );

select count_descendants(91402);

drop function count_descendants;
}}

I'm trying to wrap a recursive cte query inside a function. The query works outside the function and the function statement itself is accepted without error. When the function is used however it reports that the table I'm trying to query inside the function does not exist. A "use" statement is used before the function definition and specifying the database.table combination does not change the result.

Here is the exact error if that is helpful:

{{ERROR 1146 (42S02) at line 12: Table 'ck2.Characters' doesn't exist}}

I've attached the sql file I use to create the database and a separate sql file that contains the function and a attempt at using it.

Fred Hornsey made changes - 2018-06-30 04:38

Description

I'm trying to wrap a recursive cte query inside a function. The query works outside the function and the function statement itself is accepted without error. When the function is used however it reports that the table I'm trying to query inside the function does not exist. A "use" statement is used before the function definition and specifying the database.table combination does not change the result.

Here is the exact error if that is helpful:

{{ERROR 1146 (42S02) at line 12: Table 'ck2.Characters' doesn't exist}}

I've attached the sql file I use to create the database and a separate sql file that contains the function and a attempt at using it.

I'm trying to wrap a recursive cte query inside a function. The query works outside the function and the function statement itself is accepted without error. When the function is used however it reports that the table I'm trying to query inside the function does not exist. A "use" statement is used before the function definition and specifying the database.table combination does not change the result.

Here is the exact error if that is helpful:

{{ERROR 1146 (42S02) at line 33: Table 'ck2.Characters' doesn't exist}}

I've attached the full SQL, but this is a isolated example that has the same problem:
{{
create or replace database ck2;
use ck2;

create table Characters (
    id int unsigned key,

    mother_id int unsigned check (mother_id != id),
    foreign key (mother_id)
        references Characters(id)
        on delete cascade,

    real_father_id int unsigned check(real_father_id != id),
    foreign key (real_father_id)
        references Characters(id)
        on delete cascade
);

insert into Characters values
    (0, null, null),
    (1, 0, null),
    (2, 1, null)
;

create or replace function count_descendants(cid int unsigned) returns int unsigned return (
    with recursive Descendants as (
        select * from Characters where id = cid
        union
        select c.* from Characters as c, Descendants as d
            where d.id = c.mother_id or d.id = c.real_father_id
    ) select count(distinct(id)) from Descendants
    );

select count_descendants(0);
}}

Fred Hornsey made changes - 2018-06-30 04:39

Description

I'm trying to wrap a recursive cte query inside a function. The query works outside the function and the function statement itself is accepted without error. When the function is used however it reports that the table I'm trying to query inside the function does not exist. A "use" statement is used before the function definition and specifying the database.table combination does not change the result.

Here is the exact error if that is helpful:

{{ERROR 1146 (42S02) at line 33: Table 'ck2.Characters' doesn't exist}}

I've attached the full SQL, but this is a isolated example that has the same problem:
{{
create or replace database ck2;
use ck2;

create table Characters (
    id int unsigned key,

    mother_id int unsigned check (mother_id != id),
    foreign key (mother_id)
        references Characters(id)
        on delete cascade,

    real_father_id int unsigned check(real_father_id != id),
    foreign key (real_father_id)
        references Characters(id)
        on delete cascade
);

insert into Characters values
    (0, null, null),
    (1, 0, null),
    (2, 1, null)
;

create or replace function count_descendants(cid int unsigned) returns int unsigned return (
    with recursive Descendants as (
        select * from Characters where id = cid
        union
        select c.* from Characters as c, Descendants as d
            where d.id = c.mother_id or d.id = c.real_father_id
    ) select count(distinct(id)) from Descendants
    );

select count_descendants(0);
}}

I'm trying to wrap a recursive cte query inside a function. The query works outside the function and the function statement itself is accepted without error. When the function is used however it reports that the table I'm trying to query inside the function does not exist. A "use" statement is used before the function definition and specifying the database.table combination does not change the result.

Here is the exact error if that is helpful:

{{ERROR 1146 (42S02) at line 33: Table 'ck2.Characters' doesn't exist}}

I've attached the full SQL, but this is a isolated example that has the same problem:
{{create or replace database ck2;
use ck2;

create table Characters (
    id int unsigned key,

    mother_id int unsigned check (mother_id != id),
    foreign key (mother_id)
        references Characters(id)
        on delete cascade,

    real_father_id int unsigned check(real_father_id != id),
    foreign key (real_father_id)
        references Characters(id)
        on delete cascade
);

insert into Characters values
    (0, null, null),
    (1, 0, null),
    (2, 1, null)
;

create or replace function count_descendants(cid int unsigned) returns int unsigned return (
    with recursive Descendants as (
        select * from Characters where id = cid
        union
        select c.* from Characters as c, Descendants as d
            where d.id = c.mother_id or d.id = c.real_father_id
    ) select count(distinct(id)) from Descendants
    );

select count_descendants(0);}}

Fred Hornsey made changes - 2018-06-30 04:54

Description

I'm trying to wrap a recursive cte query inside a function. The query works outside the function and the function statement itself is accepted without error. When the function is used however it reports that the table I'm trying to query inside the function does not exist. A "use" statement is used before the function definition and specifying the database.table combination does not change the result.

Here is the exact error if that is helpful:

{{ERROR 1146 (42S02) at line 33: Table 'ck2.Characters' doesn't exist}}

I've attached the full SQL, but this is a isolated example that has the same problem:
{{create or replace database ck2;
use ck2;

create table Characters (
    id int unsigned key,

    mother_id int unsigned check (mother_id != id),
    foreign key (mother_id)
        references Characters(id)
        on delete cascade,

    real_father_id int unsigned check(real_father_id != id),
    foreign key (real_father_id)
        references Characters(id)
        on delete cascade
);

insert into Characters values
    (0, null, null),
    (1, 0, null),
    (2, 1, null)
;

create or replace function count_descendants(cid int unsigned) returns int unsigned return (
    with recursive Descendants as (
        select * from Characters where id = cid
        union
        select c.* from Characters as c, Descendants as d
            where d.id = c.mother_id or d.id = c.real_father_id
    ) select count(distinct(id)) from Descendants
    );

select count_descendants(0);}}

I'm trying to wrap a recursive cte query inside a function. The query works outside the function and the function statement itself is accepted without error. When the function is used however it reports that the table I'm trying to query inside the function does not exist. A "use" statement is used before the function definition and specifying the database.table combination does not change the result.

Here is the exact error if that is helpful:

{{ERROR 1146 (42S02) at line 33: Table 'ck2.Characters' doesn't exist}}

I've attached the full SQL, but this is a isolated example that has the same problem:

{code:sql}
create or replace database ck2;
use ck2;

create table Characters (
    id int unsigned key,

    mother_id int unsigned check (mother_id != id),
    foreign key (mother_id)
        references Characters(id)
        on delete cascade,

    real_father_id int unsigned check(real_father_id != id),
    foreign key (real_father_id)
        references Characters(id)
        on delete cascade
);

insert into Characters values
    (0, null, null),
    (1, 0, null),
    (2, 1, null)
;

create or replace function count_descendants(cid int unsigned) returns int unsigned return (
    with recursive Descendants as (
        select * from Characters where id = cid
        union
        select c.* from Characters as c, Descendants as d
            where d.id = c.mother_id or d.id = c.real_father_id
    ) select count(distinct(id)) from Descendants
    );

select count_descendants(0);
{code}

Alice Sherepa made changes - 2018-07-01 22:05

Assignee

Alice Sherepa [ alice ]

Alice Sherepa added a comment - 2018-07-02 12:54

Thanks for the report! Reproduced on 10.2 commit 8639e288086247ce39917f4cb55191c8bb5b5a8c, 10.3

CREATE or replace TABLE t1 (id int);

INSERT INTO t1 VALUES (0), (1),(2);

WITH recursive cte AS (SELECT id from t1 union select 3 from cte) SELECT count(id) FROM cte;

CREATE OR REPLACE FUNCTION func() RETURNS int RETURN (

WITH recursive cte AS (SELECT id from t1 union select 3 from cte) SELECT count(id) FROM cte);

SELECT func();

CREATE or replace TABLE t1 (id int);

INSERT INTO t1 VALUES (0), (1),(2);

WITH recursive cte AS (SELECT id from t1 union select 3 from cte) SELECT count(id) FROM cte;

count(id)

CREATE OR REPLACE FUNCTION func() RETURNS int RETURN (

WITH recursive cte AS (SELECT id from t1 union select 3 from cte) SELECT count(id) FROM cte);

SELECT func();

main.1_my                                [ fail ]

        Test ended at 2018-07-02 14:51:10

CURRENT_TEST: main.1_my

mysqltest: At line 9: query 'SELECT func()' failed: 1146: Table 'test.t1' doesn't exist

Alice Sherepa added a comment - 2018-07-02 12:54 Thanks for the report! Reproduced on 10.2 commit 8639e288086247ce39917f4cb55191c8bb5b5a8c, 10.3 CREATE or replace TABLE t1 (id int ); INSERT INTO t1 VALUES (0), (1),(2); WITH recursive cte AS ( SELECT id from t1 union select 3 from cte) SELECT count (id) FROM cte; CREATE OR REPLACE FUNCTION func() RETURNS int RETURN ( WITH recursive cte AS ( SELECT id from t1 union select 3 from cte) SELECT count (id) FROM cte); SELECT func(); CREATE or replace TABLE t1 (id int); INSERT INTO t1 VALUES (0), (1),(2); WITH recursive cte AS (SELECT id from t1 union select 3 from cte) SELECT count(id) FROM cte; count(id) 4 CREATE OR REPLACE FUNCTION func() RETURNS int RETURN ( WITH recursive cte AS (SELECT id from t1 union select 3 from cte) SELECT count(id) FROM cte); SELECT func(); main.1_my [ fail ] Test ended at 2018-07-02 14:51:10 CURRENT_TEST: main.1_my mysqltest: At line 9: query 'SELECT func()' failed: 1146: Table 'test.t1' doesn't exist

Alice Sherepa made changes - 2018-07-02 12:55

Link

This issue relates to ~~MDEV-16661~~ [ ~~MDEV-16661~~ ]

Alice Sherepa made changes - 2018-07-02 12:55

Fix Version/s

10.2 [ 14601 ]

Alice Sherepa made changes - 2018-07-02 12:56

Status

Open [ 1 ]

Confirmed [ 10101 ]

Alice Sherepa made changes - 2018-07-02 12:56

Assignee

Alice Sherepa [ alice ]

Igor Babaev [ igor ]

Igor Babaev (Inactive) made changes - 2018-07-02 21:41

Status

Confirmed [ 10101 ]

In Progress [ 3 ]

Igor Babaev (Inactive) added a comment - 2018-07-21 16:35

A fix for this bug was pushed into 10.2

Igor Babaev (Inactive) added a comment - 2018-07-21 16:35 A fix for this bug was pushed into 10.2

Igor Babaev (Inactive) made changes - 2018-07-21 16:35

Fix Version/s		10.2.17 [ 23111 ]
Fix Version/s	10.2 [ 14601 ]
Resolution		Fixed [ 1 ]
Status	In Progress [ 3 ]	Closed [ 6 ]

Jon Kilgallon made changes - 2021-04-20 14:12

Link

This issue relates to ~~MDEV-25464~~ [ ~~MDEV-25464~~ ]

Sergei Golubchik made changes - 2021-12-06 21:47

Workflow

MariaDB v3 [ 88145 ]

MariaDB v4 [ 154594 ]

MariaDB Server

"Table Does Not Exist" Error from Recursive CTE Query Inside Function

Details

Description

Attachments

Attachments

Issue Links

Activity

People

Dates

Git Integration