General error: 1105 unknown error: syntax error at position 5 near 'with'

[droslean]

with recursive `laravel_cte` as ((select *, 1 as `depth`, cast(`id` as char(65535)) as `path` from `bonuses` where `bonuses`.`parent_id` in (413, 414)) union all (select `bonuses`.*, `depth` + 1 as `depth`, concat(`path`, ., `bonuses`.`id`) from `bonuses` inner join `laravel_cte` on `laravel_cte`.`id` = `bonuses`.`parent_id`)) select * from `laravel_cte`

  images:
    vtctld: vitess/lite:v12.0.0-mysql80
    vtgate: vitess/lite:v12.0.0-mysql80
    vttablet: vitess/lite:v12.0.0-mysql80
    vtbackup: vitess/lite:v12.0.0-mysql80
    mysqld:
      mysql80Compatible: vitess/lite:v12.0.0-mysql80

Related to #4099

[GuptaManan100]

The images that you are using correspond to release 12. The parsing support for With was added in #8918 which did not go into release 12. Parsing will be supported release 13 onwards.

Also the query is incorrect, concat(path, ., bonuses.id). There should not be a unquoted . in that expression. MySQL also fails on this -

mysql [localhost:8026] {msandbox} (test) > select concat(`id1`,., `t1`.`id2`) from `t1`;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '., `t1`.`id2`) from `t1`' at line 1

but works correctly when the . is quoted as follows -

mysql [localhost:8026] {msandbox} (test) > select concat(`id1`,'.', `t1`.`id2`) from `t1`;
+-------------------------------+
| concat(`id1`,'.', `t1`.`id2`) |
+-------------------------------+
| 1.2                           |
| 2.3                           |
+-------------------------------+
2 rows in set (0.00 sec)

I checked on main and the corrected query is parsed correctly

with recursive `laravel_cte` as ((select *, 1 as `depth`, cast(`id` as char(65535)) as `path` from `bonuses` where `bonuses`.`parent_id` in (413, 414)) union all (select `bonuses`.*, `depth` + 1 as `depth`, concat(`path`, '.', `bonuses`.`id`) from `bonuses` inner join `laravel_cte` on `laravel_cte`.`id` = `bonuses`.`parent_id`)) select * from `laravel_cte`

[droslean]

@GuptaManan100 Thanks for the response. I will try to use the v13 images. Also, the query can't be wrong because it is being generated by (laravel) eloquent and not manually.

[GuptaManan100]

I am not sure how that occurred, but the query throws a SQL error on vanilla MySQL. Could you try and let me know on which version of MySQL is it working?

[droslean]

It should be MySQL8+

[ahmadmmughal]

@droslean hi 👋🏼 , were you able to resolve this issue? I'm facing the same thing with laravel-adjacency-list package and PlanetscaleDB which uses vitess under the hood.

[droslean]

@droslean hi 👋🏼 , were you able to resolve this issue? I'm facing the same thing with laravel-adjacency-list package and PlanetscaleDB which uses vitess under the hood.

@ahmadmmughal There is no solution. My team stopped using vitess because of this.