Support for CTEs in sql exec

[anthonyblond]

Currently lib/db/sql.php determines whether to return the results of a query or the number of rows affected by a query by using a regex on it:

if (preg_match('/(?:^[\s\(]*'.
			'(?:EXPLAIN|SELECT|PRAGMA|SHOW)|RETURNING)\b/is',$cmd) ||
			(preg_match('/^\s*(?:CALL|EXEC)\b/is',$cmd) &&
				$query->columnCount())) {
			$result=$query->fetchall(\PDO::FETCH_ASSOC);
...
} else {
$this->rows=$result=$query->rowcount();
...

This means if the query starts with a CTE, i.e.

WITH q1 AS (
...some query...
)
SELECT *
FROM q1
...

then the result won't be returned, since it didn't start with a select.

It can be tricked by a commented line -- RETURNING somewhere in the query, but that's not an ideal solution :)

Not sure what to suggest to resolve this. Can't just check if it starts as WITH, since the last part may end up being an update instead of a select and the behaviour would be incorrect.

(I'm using postgresql, but I assume its similar for other databases)