Comment by RedCrowbar
7 years ago
There is a difference. You are right in a sense that scalar expressions are a form of a table expression, but...
This is a valid expression:
SELECT (SELECT count(*) FROM table) + 1
This is not:
SELECT (SELECT * FROM table), 1
In EdgeQL:
SELECT (count(SomeType) + 1)
and
SELECT (SomeType, 1)
are equally valid.
The brackets make it look like they are both subqueries and contained within in the EdgeQL examples. As such, you could write the examples in SQL like this:
And
Both of which are valid in SQL. And you'd get the same result as in the EdgeQL examples.
Indeed, a more relevant second counter example would be:
Not valid... unless `table` only had one row and one column containing a number.
Yes, and that's actually the point being made in the post. You need to rewrite your queries to make SQL happy.
The EdgeQL query has no obvious meaning, unless (,) calculates the cross product. Saying
SELECT Table1, 1
returns each row of Table1 along with 1 and that 1 returns the set {1} means that you have arbitrarily assigned each row of Table1 a row from the set {1}. This gives what you want in the case of a scalar, but what about a non-scalar.
For example, suppose Table1 contains (John, Smith), (Alice, Perkins), (Bob, Best). Then, what should
SELECT Table1, {1,2}
return? If you do not say cross product, you have arbitrarily assigned rows to one another, resulting in meaningless data. If you say cross product, then you have rewritten the sql expression
SELECT * FROM Table1, (VALUES (1), (2))
I don't disagree the SQL syntax is longer and has some unnecessary keywords, but unlike EdgeQL, the query means something particular.
3 replies →
Is this such a common problem that you need a new language to solve it?
2 replies →
> SELECT (SomeType, 1)
This cannot be the case if you call each thing a 'set'. Unless `(a, b)` calculates the cross product, there is no meaningful non-arbitrary way to assign each element of a to an element of b. That would depend on an ordering, which makes it not a set to everyone but a marketing dept somewhere.
`(a, b)` is a cross-product, just like "SELECT * FROM a, b"
They're saying that both of those expressions are valid, not equivalent.
SELECT count(star) FROM table
and
SELECT star FROM table
Are both valid, not equivalent.
Furthermore, listing any kind of set depends on some ordering, be it random.
> SELECT (SELECT * FROM table), 1
What would you expect to returned for by this query?