PDO Sqlite: parser stack overflow

[mvorisek]

UPDATE - sqlite issue - https://sqlite.org/forum/forumpost/8652dfdbbe

UPDATE2 - fixed as of Sqlite 3.46.0 - sqlite/sqlite@7659ce22c5

https://pastebin.com/MQKYuSYG simplest repro
https://github.com/sqlite/sqlite/blob/version-3.44.0/src/parse.y#L1090 expr parenthesis consuming stack
https://github.com/sqlite/sqlite/blob/version-3.44.0/src/parse.y#L917 where kw consuming stack

Description

Given the following DB structure:

CREATE TABLE `country` (
  `id` INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
  `name` VARCHAR(255) DEFAULT NULL COLLATE NOCASE,
  `code` VARCHAR(255) DEFAULT NULL COLLATE NOCASE,
  `is_eu` BOOLEAN DEFAULT NULL
);

insert into `country` (`name`, `code`, `is_eu`)
values
  ('Canada', 'CA', 0);
insert into `country` (`name`, `code`, `is_eu`)
values
  ('Latvia', 'LV', 0);
insert into `country` (`name`, `code`, `is_eu`)
values
  ('Japan', 'JP', 0);
insert into `country` (`name`, `code`, `is_eu`)
values
  ('Lithuania', 'LT', 1);
insert into `country` (`name`, `code`, `is_eu`)
values
  ('Russia', 'RU', 0);
insert into `country` (`name`, `code`, `is_eu`)
values
  ('France', 'FR', 0);
insert into `country` (`name`, `code`, `is_eu`)
values
  ('Brazil', 'BR', 0);


CREATE TABLE `user` (
  `id` INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
  `name` VARCHAR(255) DEFAULT NULL COLLATE NOCASE,
  `surname` VARCHAR(255) DEFAULT NULL COLLATE NOCASE,
  `is_vip` BOOLEAN DEFAULT NULL,
  `country_id` INTEGER UNSIGNED DEFAULT NULL
);

insert into `user` (
  `name`, `surname`, `is_vip`, `country_id`
)
values
  ('John', 'Smith', 0, 1);
insert into `user` (
  `name`, `surname`, `is_vip`, `country_id`
)
values
  ('Jane', 'Doe', 0, 2);
insert into `user` (
  `name`, `surname`, `is_vip`, `country_id`
)
values
  ('Alain', 'Prost', 0, 6);
insert into `user` (
  `name`, `surname`, `is_vip`, `country_id`
)
values
  ('Aerton', 'Senna', 0, 7);
insert into `user` (
  `name`, `surname`, `is_vip`, `country_id`
)
values
  ('Rubens', 'Barichello', 0, 7);


CREATE TABLE `ticket` (
  `id` INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
  `number` VARCHAR(255) DEFAULT NULL COLLATE NOCASE,
  `venue` VARCHAR(255) DEFAULT NULL COLLATE NOCASE,
  `is_vip` BOOLEAN DEFAULT NULL,
  `user` INTEGER UNSIGNED DEFAULT NULL
);

insert into `ticket` (
  `number`, `venue`, `is_vip`, `user`
)
values
  ('001', 'Best Stadium', 0, 1);
insert into `ticket` (
  `number`, `venue`, `is_vip`, `user`
)
values
  ('002', 'Best Stadium', 0, 2);
insert into `ticket` (
  `number`, `venue`, `is_vip`, `user`
)
values
  ('003', 'Best Stadium', 0, 2);
insert into `ticket` (
  `number`, `venue`, `is_vip`, `user`
)
values
  ('004', 'Best Stadium', 0, 4);
insert into `ticket` (
  `number`, `venue`, `is_vip`, `user`
)
values
  ('005', 'Best Stadium', 0, 5);

PDO Sqlite fails to execute the following query:

select
  count(*)
from
  `user`
where
  (
    (
      select
        exists (
          select
            *
          from
            `ticket` `_T_c1c694bd849d`
          where
            (
              `user` = `user`.`id`
              and (
                select
                  exists (
                    select
                      *
                    from
                      `user` `_T_u_d09419503c1c`
                    where
                      (
                        `id` = `_T_c1c694bd849d`.`user`
                        and (
                          select
                            exists (
                              select
                                *
                              from
                                `country` `_T_u_c_a91b1c284cd4`
                              where
                                (
                                  `id` = `_T_u_d09419503c1c`.`country_id`
                                  and (
                                    select
                                      count(*)
                                    from
                                      `user` `_T_u_c_U_06e2ba85c546`
                                    where
                                      `country_id` = `_T_u_c_a91b1c284cd4`.`id`
                                  ) > 1
                                )
                            )
                        ) = 1
                      )
                  )
              ) = 1
            )
        )
    ) = 1
    and (
      select
        exists (
          select
            *
          from
            `ticket` `_T_c1c694bd849d`
          where
            (
              `user` = `user`.`id`
              and (
                select
                  exists (
                    select
                      *
                    from
                      `user` `_T_u_d09419503c1c`
                    where
                      (
                        `id` = `_T_c1c694bd849d`.`user`
                        and (
                          select
                            exists (
                              select
                                *
                              from
                                `country` `_T_u_c_a91b1c284cd4`
                              where
                                (
                                  `id` = `_T_u_d09419503c1c`.`country_id`
                                  and (
                                    select
                                      count(*)
                                    from
                                      `user` `_T_u_c_U_06e2ba85c546`
                                    where
                                      `country_id` = `_T_u_c_a91b1c284cd4`.`id`
                                  ) > 1
                                )
                            )
                        ) = 1
                      )
                  )
              ) = 1
            )
        )
    ) = 1
    and (
      select
        exists (
          select
            *
          from
            `ticket` `_T_c1c694bd849d`
          where
            (
              `user` = `user`.`id`
              and (
                select
                  exists (
                    select
                      *
                    from
                      `user` `_T_u_d09419503c1c`
                    where
                      (
                        `id` = `_T_c1c694bd849d`.`user`
                        and (
                          select
                            exists (
                              select
                                *
                              from
                                `country` `_T_u_c_a91b1c284cd4`
                              where
                                (
                                  `id` = `_T_u_d09419503c1c`.`country_id`
                                  and (
                                    select
                                      count(*)
                                    from
                                      `user` `_T_u_c_U_06e2ba85c546`
                                    where
                                      `country_id` = `_T_u_c_a91b1c284cd4`.`id`
                                  ) >= 2
                                )
                            )
                        ) = 1
                      )
                  )
              ) = 1
            )
        )
    ) = 1
    and (
      select
        exists (
          select
            *
          from
            `ticket` `_T_c1c694bd849d`
          where
            (
              `user` = `user`.`id`
              and (
                select
                  exists (
                    select
                      *
                    from
                      `user` `_T_u_d09419503c1c`
                    where
                      (
                        `id` = `_T_c1c694bd849d`.`user`
                        and (
                          select
                            exists (
                              select
                                *
                              from
                                `country` `_T_u_c_a91b1c284cd4`
                              where
                                (
                                  `id` = `_T_u_d09419503c1c`.`country_id`
                                  and (
                                    select
                                      exists (
                                        select
                                          *
                                        from
                                          `user` `_T_u_c_U_06e2ba85c546`
                                        where
                                          (
                                            `country_id` = `_T_u_c_a91b1c284cd4`.`id`
                                            and (
                                              select
                                                exists (
                                                  select
                                                    *
                                                  from
                                                    `country` `_T_u_c_U_c_f53146a9f663`
                                                  where
                                                    (
                                                      `id` = `_T_u_c_U_06e2ba85c546`.`country_id`
                                                      and (
                                                        select
                                                          count(*)
                                                        from
                                                          `user` `_T_u_c_U_c_U_0cfa13a09292`
                                                        where
                                                          `country_id` = `_T_u_c_U_c_f53146a9f663`.`id`
                                                      ) > 1
                                                    )
                                                )
                                            ) = 1
                                          )
                                      )
                                  ) = 1
                                )
                            )
                        ) = 1
                      )
                  )
              ) = 1
            )
        )
    ) = 1
    and (
      select
        exists (
          select
            *
          from
            `ticket` `_T_c1c694bd849d`
          where
            (
              `user` = `user`.`id`
              and (
                select
                  exists (
                    select
                      *
                    from
                      `user` `_T_u_d09419503c1c`
                    where
                      (
                        `id` = `_T_c1c694bd849d`.`user`
                        and (
                          select
                            exists (
                              select
                                *
                              from
                                `country` `_T_u_c_a91b1c284cd4`
                              where
                                (
                                  `id` = `_T_u_d09419503c1c`.`country_id`
                                  and (
                                    select
                                      exists (
                                        select
                                          *
                                        from
                                          `user` `_T_u_c_U_06e2ba85c546`
                                        where
                                          (
                                            `country_id` = `_T_u_c_a91b1c284cd4`.`id`
                                            and (
                                              select
                                                exists (
                                                  select
                                                    *
                                                  from
                                                    `country` `_T_u_c_U_c_f53146a9f663`
                                                  where
                                                    (
                                                      `id` = `_T_u_c_U_06e2ba85c546`.`country_id`
                                                      and (
                                                        select
                                                          exists (
                                                            select
                                                              *
                                                            from
                                                              `user` `_T_u_c_U_c_U_0cfa13a09292`
                                                            where
                                                              (
                                                                `country_id` = `_T_u_c_U_c_f53146a9f663`.`id`
                                                                and `name` is not null
                                                              )
                                                          )
                                                      ) = 1
                                                    )
                                                )
                                            ) = 1
                                          )
                                      )
                                  ) = 1
                                )
                            )
                        ) = 1
                      )
                  )
              ) = 1
            )
        )
    ) = 1
  );

It may seems to be complex query, but it comes from ORM and it should be far from the Sqlite limits

(according to the Sqlite doc the default expr depth limit is 1000 - https://www.sqlite.org/limits.html#max_expr_depth)

PHP thows: PDOException: SQLSTATE[HY000]: General error: 1 parser stack overflow

The query is failing with Sqlite only, with MySQL or MSSQL it can complete (but of course the SQL needs different identifier/literal escapes)

PHP Version

any

Operating System

any

[cmb69]

It may seems to be complex query, […]

I've rarely seen a smaller query. ;)

[…] but it comes from ORM and it should be far from the Sqlite limits

No. This query is actually hitting the YYSTACKDEPTH limit of 100 (maybe SQLITE_MAX_EXPR_DEPTH would need to be increased as well). Doing so lets the query run (with pdo_sqlite and sqlite3). So this is nothing we can fix (except for the Windows builds, but I don't think it would make sense to deviate from the defaults in this case).