> Restriction is applied to the values of the tuple. The number of tuples
is
> not a value of the tuple.
No, I can't agree. Restriction is a membership test, a function on members:
should this tuple be included in the result set or not? Cardinality of a set
is a second order function on the members of the set, obtainable simply by
examining all the tuples in the set at the same time. There is no a priori
reason not to use cardinality in a membership functions.
> Neither of us is stupid, David. I've boiled this down to something very
> simple. If you look at it algebraically, I think you'll come to the same
> conclusion I have.
AS far as I'm concerned I have already done so, but it seems we reach
different conclusions. To settle that we either need new facts or a higher
authority.
> I wouldn't persist except that you're worth convincing. Andl holds
promise,
> and seeks higher ground than SQL holds. Insofar as possible, if I can I
want
> to help you get it right.
Much appreciated. Really.
> > For this query: calculate the average of that set of numbers after
> > excluding the 2 largest and 2 smallest values. Again, a pure set
> > operation.
> >
> > A reasonable solution would be to use two subqueries with ORDER BY
> > ASC/DESC and LIMIT 2, followed by an aggregation.
>
> Sadly, no. If we're talking about a "pure set operation", and the set is
{1,
> 1, 2}, the "two smallest" is {1, 2} but LIMIT 2 would yield {1, 1}.
No, that isn't a set, it's a multiset. A set has no duplicates.
> Here again, my putative nth() function *does* give the right answer,
simply
> because it's a function of the values, and not of the number of values.
>
> > > There's no debate about the relational operators.
>
> By which I meant: there's no debate about what they do.
>
> > You might be surprised to learn that there is considerable academic
> > uncertainty as to exactly which operators should be included.
>
> There's no uncertainty. Some operators are defined in terms of others.
No
> suprise: even under De Morgan you don't need OR if you have NOT and AND.
The
> redundancy makes the language more expressive.
Not what I meant: that only covers the conjunctive queries. To that you can
successively add negation, recursion, higher order functions and
fixpoint/while. Each of those allows operations that others do not, but
there is disagreement about which should be considered 'relational'. De
Morgan won't help you there.
> LIMIT doesn't belong in this part of the discussion, btw, because it is
not
> defined relationally.
Yes it is. But it does require a second order function.
> > For example, is CTE RECURSIVE relational, or not?
>
> http://wiki.epfl.ch/provenance2011/documents/foundations%20of%20databases-
> abiteboul-1995.pdf
>
> Cf. Chapter 14. Adding recursion changes the language. It adds power; if
> memory serves permits answering second-order queries.
Thank you for the reference -- I didn't have that one. I'm familiar with the
material.
No, recursion (similar to fixpoint/while) makes the language Turing
Complete. Second order functions are not enough.
> > What about LEAD and LAG in the windowing functions? What about string
> > concatenation as an aggregation operator?
>
> AFAIK there's no debate about those, either. They can be expressed in
terms
> of simpler operations, and exist for convenience, such as it is.
This is the same debate as for LIMIT, because they rely on ordering. Indeed
it's possible to construct one from the other with something like this:
SELECT * FROM (
SELECT *, ROW_NUMBER() OVER (ORDER BY name) as row FROM sys.databases
) a WHERE row > 5 and row <= 10
You can't do SQL windowing without some kind of ordering comparison, but the
use of ordering in making a selection does not automatically make the query
non-relationally. The result is still just a set of tuples, no matter how
you choose them.
BTW this is one very useful extension for Sqlite, which is otherwise full
book on the relational hierarchy.
Regards
David M Bennett FACS
Andl - A New Database Language - andl.org
