sqlite - Prepared statement in WITH clause causes infinite loop

Question

I have PHP script that prints numbers in range from 2 to specified end using SQLite prepared statement with a WITH clause. When I run it, the program never exits and outputs also numbers after 5 (specified as parameter). When I run similar program that has the end fixed (constant in SQL code) instead of prepared statement, it outputs the expected numbers.

<?php

// Version with prepared statement

$db = new PDO("sqlite::memory:");
$s = $db->prepare("WITH RECURSIVE range(i) AS (
  SELECT 2
  UNION
  SELECT i + 1 FROM range WHERE i < :to
) select * from range
");
$s->execute(array(":to" => 5));

foreach ($s as $value)
    echo $value[0] . "\n";

---

<?php

// Version with constant -- working

$db = new PDO("sqlite::memory:");
$s = $db->prepare("WITH RECURSIVE range(i) AS (
  SELECT 2
  UNION
  SELECT i + 1 FROM range WHERE i < 5
) select * from range
");
$s->execute(array());

foreach ($s as $value)
    echo $value[0] . "\n";

Answer 1

LIMIT can be used to limit number of rows. This does not work for every case but it can be when we know number of rows.

<?php

// Version with prepared statement and LIMIT

$db = new PDO("sqlite::memory:");
$s = $db->prepare("WITH RECURSIVE range(i) AS (
  SELECT 2
  UNION
  SELECT i + 1 FROM range WHERE i < :to
) select i from range limit :to - 1
");
$s->execute(array(":to" => 5));

foreach ($s as $value)
    echo $value[0] . "\n";