Andrus

Are Linq-SQL methods commutative ?
Should the following queries return same or different results ?

var query = query.Skip(n).Take(m);

var query = query.Take(m).Skip(n);

Andrus.

Nicholas Paldino [.NET/C# MVP]

Andrus,

Absolutely different results.

Assuming the ordering is the same on each of them (because Skip and Take
make no sense without ordering, LINQ to SQL will create an order for you,
which irritates me to no end, but that's a separate thread), they will
produce different results.

Say your query will produce the ordered set {1, 2, 3}. Let n = 1, m =
2.

The first query:

var query = query.Skip(n).Take(m);

Will return the ordered set {2, 3}, while the second query:

var query = query.Take(m).Skip(n);

Will return the ordered set {2}.

The reason for this is that in the first query, the Skip method skips
one element, then takes the remaining two, while in the second query, the
first two elements are taken, and then the first one is skipped.


--
- Nicholas Paldino [.NET/C# MVP]
- mvp@spam.guard.caspershouse.com


"Andrus" <kobruleht2@hot.ee> wrote in message
news:%234a%23iY2LIHA.5140@TK2MSFTNGP05.phx.gbl...
> Are Linq-SQL methods commutative ?
> Should the following queries return same or different results ?
>
> var query = query.Skip(n).Take(m);
>
> var query = query.Take(m).Skip(n);
>
> Andrus.
>

Jon Skeet [C# MVP]

Andrus <kobruleht2@hot.ee> wrote:
> Are Linq-SQL methods commutative ?
> Should the following queries return same or different results ?
>
> var query = query.Skip(n).Take(m);
>
> var query = query.Take(m).Skip(n);

Different results - the first gives results (zero-based) n to n+m-1.

The second gives results 0 to Min(n-1, m-1).

--
Jon Skeet - <skeet@pobox.com>
http://www.pobox.com/~skeet Blog: http://www.msmvps.com/jon.skeet
World class .NET training in the UK: http://iterativetraining.co.uk

Frans Bouma [C# MVP]

Nicholas Paldino [.NET/C# MVP] wrote:

> Andrus,
>
> Absolutely different results.

Now that's unexpected. In database land, you can't get 2 different
results.

> Assuming the ordering is the same on each of them (because Skip and
> Take make no sense without ordering, LINQ to SQL will create an order
> for you, which irritates me to no end, but that's a separate thread),

Why? SELECT * FROM Table without ordering has no defined ordering, so
using a limit + skip operator on that set results in a set of undefined
rows, you'll never know what rows will be returned.


> they will produce different results.
>
> Say your query will produce the ordered set {1, 2, 3}. Let n = 1,
> m = 2.
>
> The first query:
>
> var query = query.Skip(n).Take(m);
>
> Will return the ordered set {2, 3}, while the second query:
>
> var query = query.Take(m).Skip(n);
>
> Will return the ordered set {2}.
>
> The reason for this is that in the first query, the Skip method
> skips one element, then takes the remaining two, while in the second
> query, the first two elements are taken, and then the first one is
> skipped.

I tried it out to be sure, and indeed Linq to Sql generates two
different queries (which really hurt my eyes but that's another story).
The thing is though that on databases, people will use the take/skip
combination to page through a bigger resultset. However consulting the
manual for both shows no word whatsoever about the order in which these
two statements have to be specified.

I do understand the order, but it's a bit strange as well. For
example, Linq apparently has no problem with an Order by placed in
front of a where, however there IS an order in take/skip which are used
combined as a paging mechanism. Of course, this follows from the specs
of both, but semantically, the intention of what people want to do,
e.g. to page, shouldn't require an order in the statements for paging,
if other elements in the query also don't really require an order (they
do, but that's whiped under the rug)

FB
--
------------------------------------------------------------------------
Lead developer of LLBLGen Pro, the productive O/R mapper for .NET
LLBLGen Pro website: http://www.llblgen.com
My .NET blog: http://weblogs.asp.net/fbouma
Microsoft MVP (C#)
------------------------------------------------------------------------

Jon Skeet [C# MVP]

On Nov 26, 9:04 am, "Frans Bouma [C# MVP]"
<perseus.usenetNOS...@xs4all.nl> wrote:

<snip>

> I tried it out to be sure, and indeed Linq to Sql generates two
> different queries (which really hurt my eyes but that's another story).
> The thing is though that on databases, people will use the take/skip
> combination to page through a bigger resultset. However consulting the
> manual for both shows no word whatsoever about the order in which these
> two statements have to be specified.

I don't see why there'd be any explicit *need* for documentation on
this - it seems pretty obvious to me. If you want to skip some results
and then take a certain number of the rest, you do Skip and then Take.
If you want to take some results and then skip within that (which is a
slightly odd thing to do, admittedly) you do Skip and then Take.

How is it not just intuitive?

> I do understand the order, but it's a bit strange as well. For
> example, Linq apparently has no problem with an Order by placed in
> front of a where, however there IS an order in take/skip which are used
> combined as a paging mechanism.

Filtering and ordering are effectively orthogonal, unless you can
build a filter which depends on the order. Skipping and taking are
clearly *not* orthogonal.

> Of course, this follows from the specs
> of both, but semantically, the intention of what people want to do,
> e.g. to page, shouldn't require an order in the statements for paging,
> if other elements in the query also don't really require an order (they
> do, but that's whiped under the rug)

Elements in the query which interact are affected by the order in
which they're specified.
Elements in the query which don't interact aren't affected by the
order in which they're specified.

That seems pretty sensible to me.

Jon

Andrus

> How is it not just intuitive?

In PostgreSQL order of limit and offset clauses does not matter.

So when moving from PostgreSQL direct SQL to Linq-PostgreSQL this is not
intuitive.

The major issue is that I have no idea how to implement orthogonal Skip()
and Take() effectively (so that minimal possible number of rows are returned
from server) in
Linq-PostgreSQL driver.
In standard SQL Postgres uses LIMIT and OFFSET are orthogonal and there is
no row number function.
http://www.postgresql.org/docs/8.2/i...ies-limit.html

Andrus.

Jon Skeet [C# MVP]

On Nov 26, 11:35 am, "Andrus" <kobrule...@hot.ee> wrote:
> > How is it not just intuitive?
>
> In PostgreSQL order of limit and offset clauses does not matter.

But they're not limit and offset - they're "take the next N results"
and "skip the next N results".

> So when moving from PostgreSQL direct SQL to Linq-PostgreSQL this is not
> intuitive.

Did you read the description of the Skip and Take methods before using
them? If you just assume they mean "offset" and "limit" then you'll
get the wrong results, but if you read the description I believe it
*is* intuitive to use them.

> The major issue is that I have no idea how to implement orthogonal Skip()
> and Take() effectively (so that minimal possible number of rows are returned
> from server) in
> Linq-PostgreSQL driver.
> In standard SQL Postgres uses LIMIT and OFFSET are orthogonal and there is
> no row number function.http://www.postgresql.org/docs/8.2/i...ies-limit.html

You can't and shouldn't implement Skip and Take orthogonally - they're
*meant* to interact. The developer should specify them in the
appropriate order, which is almost always going to be Skip and then
Take.

Skip X, Take Y should translate to
OFFSET X LIMIT Y

Take Y, Skip X should translate to
OFFSET X LIMIT min(Y-X, 0)

What are you trying to achieve which is causing you difficulties?

Jon

Nicholas Paldino [.NET/C# MVP]

Frans,

See inline:

>> Andrus,
>>
>> Absolutely different results.
>
> Now that's unexpected. In database land, you can't get 2 different
> results.

Not really, because to translate what is being done in LINQ to database
land, you would actually perform a query on the result set of the previous
query. The operations are not performed at the same time.

>> Assuming the ordering is the same on each of them (because Skip and
>> Take make no sense without ordering, LINQ to SQL will create an order
>> for you, which irritates me to no end, but that's a separate thread),
>
> Why? SELECT * FROM Table without ordering has no defined ordering, so
> using a limit + skip operator on that set results in a set of undefined
> rows, you'll never know what rows will be returned.

Well, that's not completely true. If you are using a clustered index in
SQL Server, then the ordering of the result set will be on that index,
however, without that index, the order is undefined (basically, however it
is stored in the file).

But I agree with you, in database land, it makes no sense, because you
have to define an ordering. LINQ to SQL gets around this by ordering by all
the columns in the table if another order is not found (I believe, I have to
test that part, but without a specific ordering, it orders on every column
in the table).

This is why I said what I said in parenthesis. I hate the fact that
Take and Skip are exposed, and assume some kind of order for you when it
doesn't make sense to not have ordering. It should be mandatory to provide
an order when using any of these extensions. It's also why I said "assuming
the ordering is the same on each of them", because LINQ to SQL will always
produce a default ordering.

>
>
>> they will produce different results.
>>
>> Say your query will produce the ordered set {1, 2, 3}. Let n = 1,
>> m = 2.
>>
>> The first query:
>>
>> var query = query.Skip(n).Take(m);
>>
>> Will return the ordered set {2, 3}, while the second query:
>>
>> var query = query.Take(m).Skip(n);
>>
>> Will return the ordered set {2}.
>>
>> The reason for this is that in the first query, the Skip method
>> skips one element, then takes the remaining two, while in the second
>> query, the first two elements are taken, and then the first one is
>> skipped.
>
> I tried it out to be sure, and indeed Linq to Sql generates two
> different queries (which really hurt my eyes but that's another story).
> The thing is though that on databases, people will use the take/skip
> combination to page through a bigger resultset. However consulting the
> manual for both shows no word whatsoever about the order in which these
> two statements have to be specified.

Well, there is no order that they HAVE to be specified in. Yes, they
are usually used in conjunction, but it is not a requirement that they be
used in conjunction. They can be specified in any order, which is what the
OP's question was arising from. He wanted to know if the order in which the
operations were applied would affect the outcome, which it definitely will.

> I do understand the order, but it's a bit strange as well. For
> example, Linq apparently has no problem with an Order by placed in
> front of a where, however there IS an order in take/skip which are used
> combined as a paging mechanism. Of course, this follows from the specs
> of both, but semantically, the intention of what people want to do,
> e.g. to page, shouldn't require an order in the statements for paging,
> if other elements in the query also don't really require an order (they
> do, but that's whiped under the rug)

Well, it depends here on what operations we are talking about. You are
making the error of assuming that Take and Skip are both sub parts of a
larger page operation, and assuming that because other elements of the
operation that can be applied (i.e. where) don't require order, these don't.

But that's wrong. Like you said, in db-land, not applying an order
results in an undefined order in the result set. If you are going to page
off that, then the DB is completely within its rights to return a
differently ordered result set between subsequent queries, and your paging
results will simply be wrong. You would be depending on an implementation
detail that the DB would consistently return the undefined order to you.

The reason Take and Skip (should) require an order is because of this
fact. Think of it. The definition of Take is "take the FIRST n elements".
Skip is defined as "skip the NEXT n elements". Because of FIRST and NEXT,
you require order. Without knowing the order, the concepts of FIRST and
NEXT do not exist.


--
- Nicholas Paldino [.NET/C# MVP]
- mvp@spam.guard.caspershouse.com

>
> FB
> --
> ------------------------------------------------------------------------
> Lead developer of LLBLGen Pro, the productive O/R mapper for .NET
> LLBLGen Pro website: http://www.llblgen.com
> My .NET blog: http://weblogs.asp.net/fbouma
> Microsoft MVP (C#)
> ------------------------------------------------------------------------

Frans Bouma [C# MVP]

Jon Skeet [C# MVP] wrote:

> On Nov 26, 9:04 am, "Frans Bouma [C# MVP]"
> <perseus.usenetNOS...@xs4all.nl> wrote:
>
> <snip>
>
> > I tried it out to be sure, and indeed Linq to Sql generates
> > two different queries (which really hurt my eyes but that's another
> > story). The thing is though that on databases, people will use the
> > take/skip combination to page through a bigger resultset. However
> > consulting the manual for both shows no word whatsoever about the
> > order in which these two statements have to be specified.
>
> I don't see why there'd be any explicit need for documentation on
> this - it seems pretty obvious to me. If you want to skip some results
> and then take a certain number of the rest, you do Skip and then Take.
> If you want to take some results and then skip within that (which is a
> slightly odd thing to do, admittedly) you do Skip and then Take.
>
> How is it not just intuitive?

Because skip/take equivalents in SQL work on the same projection
result, while in Linq they don't.

You should look less at specs and more at what users WANT TO DO. Time
and time again you post a reply with how the syntaxis is, how the specs
says how it works etc. etc. All fine, but that's completely not hte
point here: the point is that the user wants to PAGE through a
resultset. In Firebird, Mysql, Postgresql, people have 2 keywords in
the SELECT statement to do that, similar to skip/take, but the order
isn't important, because both work at the same projection result. THe
intention of the USER is to page. Messing the order up has no effect at
compiletime, but at runtime it does.

How do you PREVENT developers to make this error? Exactly, by
specifying a pagenumber and a page size instead of skip/take OR by
specifying that the order doesn't matter.

> > I do understand the order, but it's a bit strange as well.
> > For example, Linq apparently has no problem with an Order by placed
> > in front of a where, however there IS an order in take/skip which
> > are used combined as a paging mechanism.
>
> Filtering and ordering are effectively orthogonal, unless you can
> build a filter which depends on the order. Skipping and taking are
> clearly not orthogonal.

In SQL, order by is done on a projected resultset. In Linq, this isn't
always clear, because you can place an order by in a new projection
scope, and you don't know IF that results in a subquery (projection
scope) or not. My point was that MS should have made the same
limitation to linq as ISO did to SQL: Order By is the last statement in
the query. While order is important for extension methods like
skip/take specified directly, it's not for order by apparently.

> > Of course, this follows from the specs
> > of both, but semantically, the intention of what people want to do,
> > e.g. to page, shouldn't require an order in the statements for
> > paging, if other elements in the query also don't really require an
> > order (they do, but that's whiped under the rug)
>
> Elements in the query which interact are affected by the order in
> which they're specified.
> Elements in the query which don't interact aren't affected by the
> order in which they're specified.
>
> That seems pretty sensible to me.

Yes, because you don't understand what the word 'intention' means when
a developer writes code. You don't seem to grasp the little fact that a
developer who makes a mistake by writing ACB, while the intention was
ABC, should be WARNED with an error or warning that ACB isn't going to
work probably. Like I said: I understand the ordering of skip/take in
the context of the situation where you first want to take and then want
to skip (however I can't find a use case for that). What I don't
understand is that 'skip' and 'take' aren't placed TOGETHER in such a
way that there's no way to make a mistake when one tries to PAGE
through a resultset.

You seem to find it 'sensible' that people can do that. That's what I
find odd. But I disgress, according to the syntax/specs everything
seems logical, because it is in the spec, however precisely that isn't
the point at all here.

FB


--
------------------------------------------------------------------------
Lead developer of LLBLGen Pro, the productive O/R mapper for .NET
LLBLGen Pro website: http://www.llblgen.com
My .NET blog: http://weblogs.asp.net/fbouma
Microsoft MVP (C#)
------------------------------------------------------------------------

Frans Bouma [C# MVP]

Nicholas Paldino [.NET/C# MVP] wrote:

> Frans,
>
> See inline:
>
> > > Andrus,
> > >
> >> Absolutely different results.
> >
> > Now that's unexpected. In database land, you can't get 2 different
> > results.
>
> Not really, because to translate what is being done in LINQ to
> database land, you would actually perform a query on the result set
> of the previous query. The operations are not performed at the same
> time.

That's not defined. There's no ruleset how to translate a Linq query
to SQL, on the contrary, it's often a struggle how to completely grasp
the intention of the linq query with SQL.

So, in the case of take->skip, true, you need multiple projections and
in the case of skip -> take, you need less, perhaps none. However, in
the case of a select statement which specifies BOTH at once, you can't
get 2 different resultsets by specifying them in a different order, see
Firebird's and PostgreSql's SELECT syntax for details.
Firebird:
SELECT FIRST m SKIP n * FROM ...

PostgreSQL:
SELECT * FROM ... LIMIT m OFFSET n

MySql actually does have an order, but it has just 1 clear intention:
you skip n rows and take m. You can swap them, but that doesn't matter
for the statement, you can't specify take m, skip n from that m

MySQL:
SELECT * FROM ... LIMIT n, m


Oh yes, I know... this is SQL and not linq. Let's talk about that for
a second. A developer writing a linq query which is executed on the
database has to understand the query WILL become SQL. The translation
is far from trivial, so the intention of the linq query has to be
represented by the SQL query as much as possible.

The developer isn't helped by fuzzy linq syntaxis, as predicting what
SQL is going to be generated is then even more difficult than it is
now. Take is used for TOP if Skip isn't there.

Is take used for TOP IF skip is there?

No, it's not. Well, not always. If Skip is there and Take is there, it
should be translated into a paging query (paging is skipping rows and
taking the top n rows from what's left). If paging is performed 'TOP'
makes no sense.

So if Take is specified first, and then Skip, the query has to use a
temp table to store the taken rows and then skip the first n rows from
that. Otherwise it can't always be done: TOP can't always be added to a
subquery: if you sort on a column not in the subquery, you can run into
errors if TOP / DISTINCT is used in the subquery.

> > > Assuming the ordering is the same on each of them (because Skip
> > > and Take make no sense without ordering, LINQ to SQL will create
> > > an order for you, which irritates me to no end, but that's a
> > > separate thread),
> >
> > Why? SELECT * FROM Table without ordering has no defined ordering,
> > so using a limit + skip operator on that set results in a set of
> > undefined rows, you'll never know what rows will be returned.
>
> Well, that's not completely true. If you are using a clustered index
> in SQL Server, then the ordering of the result set will be on that
> index, however, without that index, the order is undefined
> (basically, however it is stored in the file).

That's just luck, if I recall correctly, but I have to check BOL for
semantics on this. The SQL standard (not obeyed by anyone in full,
admitted) describes this explicitly. I don't like to hammer on standard
specs that much, but this one thing is essential to understand so
people don't run into problems with unexpected results.

The RDBMS can for example decide to return a part of the rowset before
another part because it has that one already in memory.

> But I agree with you, in database land, it makes no sense, because
> you have to define an ordering. LINQ to SQL gets around this by
> ordering by all the columns in the table if another order is not
> found (I believe, I have to test that part, but without a specific
> ordering, it orders on every column in the table).

I think it orders on every column in the projection. If it would order
on every column in the source of the projection, it can run into issues
with DISTINCT where not every column of the source is in the projection.

> > > they will produce different results.
> > >
> >> Say your query will produce the ordered set {1, 2, 3}. Let n = 1,
> > > m = 2.
> > >
> >> The first query:
> > >
> > > var query = query.Skip(n).Take(m);
> > >
> >> Will return the ordered set {2, 3}, while the second query:
> > >
> > > var query = query.Take(m).Skip(n);
> > >
> >> Will return the ordered set {2}.
> > >
> >> The reason for this is that in the first query, the Skip method
> > > skips one element, then takes the remaining two, while in the
> > > second query, the first two elements are taken, and then the
> > > first one is skipped.
> >
> > I tried it out to be sure, and indeed Linq to Sql generates two
> > different queries (which really hurt my eyes but that's another
> > story). The thing is though that on databases, people will use the
> > take/skip combination to page through a bigger resultset. However
> > consulting the manual for both shows no word whatsoever about the
> > order in which these two statements have to be specified.
>
> Well, there is no order that they HAVE to be specified in. Yes, they
> are usually used in conjunction, but it is not a requirement that
> they be used in conjunction. They can be specified in any order,
> which is what the OP's question was arising from. He wanted to know
> if the order in which the operations were applied would affect the
> outcome, which it definitely will.

Though the intention is to page, i.e. first skip, then take. When
would you first take and then skip? With Skip(n) and Take(m), isn't:
set.Skip(n).Take(m) the same as set.Take(m+n).Skip(n) ?

the net result is that you get a set of rows which is limited to a
size x from a potentially bigger set with size y. In other words:
specifying Take first and then Skip has no real value over specifying
Skip first and then Take. Mind you: the intention is to get a resultset
which has a fixed size and is a subset from a bigger set.

As some databases offer you this in the SELECT statement, one could
argue (and there are no rules for this, so why not!) that Skip and
Take, IF specified both in the same projection scope, are translated
into the parameters for the LIMIT/OFFSET etc. keywords of the SELECT
statement of the projection scope they're specified on.

> > I do understand the order, but it's a bit strange as well. For
> > example, Linq apparently has no problem with an Order by placed in
> > front of a where, however there IS an order in take/skip which are
> > used combined as a paging mechanism. Of course, this follows from
> > the specs of both, but semantically, the intention of what people
> > want to do, e.g. to page, shouldn't require an order in the
> > statements for paging, if other elements in the query also don't
> > really require an order (they do, but that's whiped under the rug)
>
> Well, it depends here on what operations we are talking about. You
> are making the error of assuming that Take and Skip are both sub
> parts of a larger page operation, and assuming that because other
> elements of the operation that can be applied (i.e. where) don't
> require order, these don't.

Why else would you want to use take and skip in the same projection
scope? Their sole purpose is to limit the resultset to a fixed set and
to specify where to start in the overall resultset for the subset to
return.

> But that's wrong. Like you said, in db-land, not applying an order
> results in an undefined order in the result set. If you are going to
> page off that, then the DB is completely within its rights to return
> a differently ordered result set between subsequent queries, and your
> paging results will simply be wrong. You would be depending on an
> implementation detail that the DB would consistently return the
> undefined order to you.

'Order' in my remark above was about skip first/take after that, not
about an ORDER BY added to the query by the linq provider. Sorry about
that confusion.

> The reason Take and Skip (should) require an order is because of this
> fact. Think of it. The definition of Take is "take the FIRST n
> elements". Skip is defined as "skip the NEXT n elements". Because of
> FIRST and NEXT, you require order. Without knowing the order, the
> concepts of FIRST and NEXT do not exist.

Though I've shown above that you can use Take and Skip in any order to
obtain the same rows. The INTENTION of the developer is to get row n
till n+m from a set of rows of size m+k, agreed? Why else would Take
and Skip show up in the same projection scope.

FB

--
------------------------------------------------------------------------
Lead developer of LLBLGen Pro, the productive O/R mapper for .NET
LLBLGen Pro website: http://www.llblgen.com
My .NET blog: http://weblogs.asp.net/fbouma
Microsoft MVP (C#)
------------------------------------------------------------------------