phpunit test has errors - "1 recursive reference in subquery"

[Kenny417]

Error generated by phpunit when I run my test:

Illuminate\Database\QueryException: SQLSTATE[HY000]: General error: 1 recursive reference in a subquery: cte (SQL: with recursive "cte" as (select * from (select * from "comments" where "commentable_id" = 1) union all select * from (select "comments".* from "comments" inner join "cte" on "cte"."id" = "comments"."commentable_id")) select * from "cte")

I have tested this with query builder and Eloquent. My example with eloquent is slimmed down to match the example in your documentation. Both functions give the same error in my test but it works fine when I test my endpoint in Postman or through the web browser.

commentable_id is the id of the parent.

trait Comments
{
    function getComments($id)
    {
        $query = DB::table('comments')
            ->where('commentable_id', $id)
            ->unionAll(
            DB::table('comments')
                ->select('comments.*')
                ->join('cte', 'cte.id', '=', 'comments.commentable_id')
            );

        return DB::table('cte')
            ->withRecursiveExpression('cte', $query)
            ->join('users','cte.author_id','=','users.id')
            ->select('cte.*', 'users.id as user_id', 'users.first_name as first_name', 'users.last_name as last_name')
            ->orderBy('created_at', 'DESC')
            ->get();
    }

    function getCommentsWithEloquent($id)
    {
        $query = Comment::where('commentable_id', $id)
        ->unionAll(
            Comment::select('comments.*')
                ->join('cte', 'cte.id', '=', 'comments.commentable_id')
        );

        return Comment::from('cte')
            ->withRecursiveExpression('cte', $query)
            ->get();
    }

}

Formatted MySQL queries:

/***********
This query runs when the page is viewed in a web browser
it works fine. 
**********/

with recursive `cte` as (
    (select * from `comments` where `commentable_id` = '37') 
    union all 
    (select `comments`.* from `comments` inner join `cte` on `cte`.`id` = `comments`.`commentable_id`)
) 
select `cte`.*, 
       `users`.`id` as `user_id`, 
       `users`.`first_name` as `first_name`, 
       `users`.`last_name` as `last_name` 
from `cte` inner join `users` on `cte`.`author_id` = `users`.`id` 
order by `created_at` desc;
    
/***********
composer test generates the next query and gives error
Illuminate\Database\QueryException: SQLSTATE[HY000]: General error: 1 recursive reference in a subquery: cte 
**********/

with recursive `cte` as (
    select * from (
        select * 
        from `comments` 
        where `commentable_id` = 1
	)
	union all 
    select * from (
		select `comments`.* 
        from `comments` 
        inner join `cte` on `cte`.`id` = `comments`.`commentable_id`
	)
) 
select `cte`.*, 
	   `users`.`id` as `user_id`, 
	   `users`.`first_name` as `first_name`, 
	   `users`.`last_name` as `last_name` 
from `cte` 
inner join `users` on `cte`.`author_id` = `users`.`id` 
order by `created_at` desc;

When I run the mysql code from the phpunit error message in MySQL Workbench it gives me this error:

Error Code: 1248. Every derived table must have its own alias

When I give the derived tables an alias I get this error:

Error Code: 3577. In recursive query block of Recursive Common Table Expression 'cte', the recursive table must be referenced only once, and not in any subquery

I can run the MySQL code that the browser and Postman are using and it works as expected. When I remove the extra SELECT * FROM code from the statement in the phpunit error and run it in Workench, it works fine.

Do you know why it generates different MySQL code in the test? This package works perfectly when I visit the site in my browser.

[staudenmeir]

The queries are different because your tests are running on an SQLite database.

Laravel uses this nested syntax to support complex UNION queries, but it doesn't work with recursive CTEs.

I see two possible solutions:

• You run your tests on MySQL. (It's strongly recommended to use the database from the production environment. Otherwise, successful tests don't always mean that your application will actually work.)
• The package could compile different UNION queries for SQLite when recursive CTEs are involved.

[staudenmeir]

I've released a new version that fixes this.