
Expressive Power of SQL (2003) [pdf] - mpiedrav
http://homepages.inf.ed.ac.uk/libkin/papers/icdt01.pdf
======
numbsafari
Interesting to note that neither BigQuery, nor Apache Presto (and therefore
AWS Athena), support recursive CTEs.

However, most other DBs do (e.g. Postgres, MS SQL, MySQL 8.0, MariaDB 10.2,
Oracle, etc.).

Which is disappointing, given how powerful BQ and Presto/Athena are for
startups.

~~~
dspillett
Be careful with CTEs with regard to performance, some SQL DBs can optimise by
pushing predicates through to the inner queries, where for some (postgres
being the one example that I know but there are probably others) a CTE
presents an optimisation fence.

~~~
nerdponx
I never understood this. Why can't CTEs be transparently compiled into
subqueries?

~~~
dspillett
There is plenty of discussion about it out there (search for "postgres cte
optimisation fence") including official word.

Note though that simply replacing CTE references with appropriate sub-queries
simply won't work in the recursive case, and in some cases where a CTE is
referred to multiple times the result may be an even less optimal query plan.

------
YeGoblynQueenne
>> However, logicians proved it long time ago that first-order logic, and thus
relational calculus, cannot compare cardinalities of relations, and yet we
have a very simple SQL query doing precisely that.

I'm not sure what the bit about comparing cardinalities refers to. I think it
relates to Trakhtenbrot's theorem, or the Löwenheim–Skolem theorem, but I'd
appreciate it if someone clarified this.

As it is, my intuition is that the result mentioned is for either finite or
infinite relations, or in any case for some broad case of unrestricted
sentences (any formula in any language), whereas there are restricted cases
where it doesn't hold (sort of like satisfiability).

Like I say, if anyone knows what this refers to, please speak up.

____________________

[1]
[https://en.wikipedia.org/wiki/Trakhtenbrot%27s_theorem](https://en.wikipedia.org/wiki/Trakhtenbrot%27s_theorem)

[2]
[https://en.wikipedia.org/wiki/L%C3%B6wenheim%E2%80%93Skolem_...](https://en.wikipedia.org/wiki/L%C3%B6wenheim%E2%80%93Skolem_theorem)

------
domparise
Isn't SQL Turing complete? So imo the question of "how expressive is SQL" is
really just "how expressive can SQL be with reasonably useful performance".

~~~
corpMaverick
From what I can understand (barely) The paper tries to determine if some of
the new(2003) SQL3 extensions were really needed.

From the answers here [https://stackoverflow.com/questions/900055/is-sql-or-
even-ts...](https://stackoverflow.com/questions/900055/is-sql-or-even-tsql-
turing-complete)

It seems that SQL92 was not Turing complete but it became turing complete
later on. Possibly by the same extensions added in SQL3.

~~~
MarkusWinand
The mentioned proof is confirming SQL:2003.

See also:
[https://wiki.postgresql.org/wiki/Cyclic_Tag_System](https://wiki.postgresql.org/wiki/Cyclic_Tag_System)

