Limit and Offset support in HQL

[Patrick Earl]

I'm looking at adding limit and offset to the HQL grammar to support
things like grabbing the top row sorted by date in a subquery. This
is related to the most popular Linq "bug." Other than the limitations
of particular databases, I'm not imagining there will be a great
number of problems implementing this.

The biggest issue I have right now is deciding the syntax. SQL 2008
specifies the following syntax for the limit clause that appears after
the order by clause.

Option A (ANSI SQL)
[ OFFSET integer-literal {ROW | ROWS} ]
[ FETCH { FIRST | NEXT } [integer-literal] {ROW | ROWS} ONLY ]

This is the standard, but quite verbose. Specifying first, next, row,
rows, or only has absolutely no effect, but is required by the
standard.

Option B (PostgreSQL, MySQL, etc.)
[LIMIT integer]
[OFFSET integer]

I'm not even going to propose using something like row_number as a hql syntax.

What are people's thoughts on this?

Patrick Earl

[Richard Birkby]

Is this equivalent to adding Query.SetFirstResult(...) and
Query.SetMaxResults(...) into the HQL grammar?

Richard

[Wenig, Stefan]

Those 2 don't work for subqueries, that's why we need it in the grammar AKAIR.

Sent from my HTC phone

----- Ursprüngliche Nachricht -----
Von: Richard Birkby <rbi...@gmail.com>
Gesendet: Sonntag, 6. Februar 2011 08:32
An: nhibernate-...@googlegroups.com <nhibernate-...@googlegroups.com>
Betreff: Re: [nhibernate-development] Limit and Offset support in HQL

[Richard Brown (gmail)]

Option B, cos:

1. I can't think of a single RDBMS that actually implements the ANSI
standard (SQL Server team are you listening?);
2. The OFFSET x LIMIT y is the most readable (bay far), and is closest to
our internal implementation terminology;
3. I can't think of a single advantage to the more verbose option and it
looks like more work.

-----Original Message-----
From: Wenig, Stefan
Sent: Sunday, February 06, 2011 9:47 AM
To: nhibernate-...@googlegroups.com
Subject: AW: Re: [nhibernate-development] Limit and Offset support in HQL

Those 2 don't work for subqueries, that's why we need it in the grammar
AKAIR.

Sent from my HTC phone

----- Urspr�ngliche Nachricht -----

[Fabio Maulo]

Select SKIPING 50 TAKING 25 .... blah blah

--
Fabio Maulo

[Diego Mijelshon]

I think anything using a (number of rows to skip) and a (max number of rows to fetch) keyword is fine, no need to add noise.

So,

[SKIP | OFFSET] n [TAKE | FETCH | LIMIT] m

...would do in any of the possible combinations.

 

(I _do_ like skip/take a little more than the alternatives because it resembles the OOP side -LINQ- more than the nonstandard SQL side)

    Diego

[Patrick Earl]

Actually, the fetch first syntax will be in the next version of SQL
Server, and it's already in these databases:

IBM DB2, Sybase SQL Anywhere, PostgreSQL, EffiProz and HSQLDB version 2.0

[Patrick Earl]

Based on the feedback so far, it seems like the simpler syntax is more
desired. What are people's preferences around the following two
options?

SKIP x TAKE y
OFFSET x LIMIT y

Seems like Fabio and Diego prefer the first two. I'm also liking the
skip and take a bit more at this point, since it's more descriptive,
despite being less standard from a SQL perspective. Other's thoughts?

Patrick Earl

[Hoang Tang]

I vote for the skip and take version... they all need to be translated anyway why not pick something that is easy to remember

[Stephen Bohlen]

I agree.  HQL is already supposed to be an abstraction over the (underlying) DB query language.  SKIP and TAKE are probably more evocative of what you're declaring as your intent with the statement.

Steve Bohlen
sbo...@gmail.com
http://blog.unhandled-exceptions.com
http://twitter.com/sbohlen

[Fabio Maulo]

Put it at the begin of the query.

It will be clear and, over all, more easy to implement (read it as 'grammar').

Examples:

SKIPING 50 TAKING 25 select e.Name, e.Surname from Employee e ......
SKIPING 50 TAKING 25 from Employee

from Users u where u.Employee in (TAKING 25 from Employee e order by e.Salary desc) 

--
Fabio Maulo

[Stephen Bohlen]

I really like that structure.

+1

Steve Bohlen
sbo...@gmail.com
http://blog.unhandled-exceptions.com
http://twitter.com/sbohlen

[Patrick Earl]

So we have:

Option i: SKIPPING x TAKING y SELECT ... FROM ... WHERE ... ORDER BY ...
Option ii: SKIP x TAKE y SELECT ... FROM ... WHERE ... ORDER BY ...
Option iii: SELECT ... FROM ... WHERE ... ORDER BY ... SKIP x TAKE y

For the first, people would need to get the spelling right on
skipping, unless we just supported both versions (skipping and
skiping).

Patrick

[Michael Teper]

+1 for option ii -- simpler and less ambiguous.

Btw, while on the subject of extensing HQL, should this effort be
coordinated with or at least communicated to the Hibernate team?

-MT

On Feb 6, 10:49 am, Patrick Earl <hyn...@gmail.com> wrote:
> So we have:
>
> Option i: SKIPPING x TAKING y SELECT ... FROM ... WHERE ... ORDER BY ...
> Option ii: SKIP x TAKE y SELECT ... FROM ... WHERE ... ORDER BY ...
> Option iii: SELECT ... FROM ... WHERE ... ORDER BY ... SKIP x TAKE y
>
> For the first, people would need to get the spelling right on
> skipping, unless we just supported both versions (skipping and
> skiping).
>
>         Patrick
>
>
>
> On Sun, Feb 6, 2011 at 11:42 AM, Stephen Bohlen <sboh...@gmail.com> wrote:
> > I really like that structure.
>
> > +1
>
> > Steve Bohlen

> > sboh...@gmail.com
> >http://blog.unhandled-exceptions.com
> >http://twitter.com/sbohlen

>
> > On Sun, Feb 6, 2011 at 1:33 PM, Fabio Maulo <fabioma...@gmail.com> wrote:
>
> >> Put it at the begin of the query.
> >> It will be clear and, over all, more easy to implement (read it as
> >> 'grammar').
> >> Examples:
> >> SKIPING 50 TAKING 25 select e.Name, e.Surname from Employee e ......
> >> SKIPING 50 TAKING 25 from Employee
> >> from Users u where u.Employee in (TAKING 25 from Employee e order by
> >> e.Salary desc)
> >> On Sun, Feb 6, 2011 at 2:23 PM, Patrick Earl <hyn...@gmail.com> wrote:
>
> >>> Based on the feedback so far, it seems like the simpler syntax is more
> >>> desired.  What are people's preferences around the following two
> >>> options?
>
> >>> SKIP x TAKE y
> >>> OFFSET x LIMIT y
>
> >>> Seems like Fabio and Diego prefer the first two.  I'm also liking the
> >>> skip and take a bit more at this point, since it's more descriptive,
> >>> despite being less standard from a SQL perspective.  Other's thoughts?
>
> >>>        Patrick Earl
>
> >> --

> >> Fabio Maulo- Hide quoted text -
>
> - Show quoted text -

[Fabio Maulo]

well... apply the right English spelling even if I would like to use the dialect of my little town in Italy...

SARDA 50 PIGLIA 25 ...

but... I know... HQL have to use English... unfortunately

LOL

--
Fabio Maulo

[Patrick Earl]

I'm going to vote for option ii, since SKIP, TAKE, and SELECT are
similar in English. The syntax doesn't say SELECTING, which would be
equivalent IMHO.

Juts to avoid any complaints about completeness,

Option iv: SELECT ... FROM ... WHERE ... ORDER BY ... SKIPPING x TAKING y

Patrick

[Stephen Bohlen]

As a pretty poor typist, I am sensitive to the misspelling potential in 'skiping' vs. 'skipping'.

Even though suffixing with -ING is better grammar, I'm probably inclined to recommend we go instead with only SKIP and TAKE as these have direct (and thus familiar) allegories to the same ops in LINQ.

Steve Bohlen
sbo...@gmail.com
http://blog.unhandled-exceptions.com
http://twitter.com/sbohlen

[Fabio Maulo]

On Sun, Feb 6, 2011 at 3:56 PM, Michael Teper <mte...@gmail.com> wrote:

+1 for option ii -- simpler and less ambiguous.

Btw, while on the subject of extensing HQL, should this effort be
coordinated with or at least communicated to the Hibernate team?

Sure! in the same way they do with us.

-- 

Fabio Maulo

[Patrick Earl]

+1 for SARDA and PIGLIA lol

[Patrick Earl]

K, I'm going to throw a wrench in the works and add a couple options.

http://en.wikipedia.org/wiki/Select_(SQL)#FETCH_FIRST_clause

So, to recap:

Option i: SKIPPING x TAKING y SELECT ... FROM ... WHERE ... ORDER BY ...
Option ii: SKIP x TAKE y SELECT ... FROM ... WHERE ... ORDER BY ...
Option iii: SELECT ... FROM ... WHERE ... ORDER BY ... SKIP x TAKE y

Option iv: SELECT ... FROM ... WHERE ... ORDER BY ... SKIPPING x TAKING y
Option v: SELECT FIRST x SKIP y ... FROM ... WHERE ... ORDER BY ...
Option vi: SELECT TOP x SKIP y ... FROM ... WHERE ... ORDER BY ...

Patrick Earl

[Michael Teper]

Given the new set of choices, I change my vote from ii to vi. "select
top X" is consistent with SQL and intuitive.

Thanks!
-MT

[Fabio Maulo]

Take care Patric.... TAKE care and SKIP problems.

before give so many options try to have an idea about the work to do in the grammar/parser/tree.

Try to send a mail to Sir. Steve Strong (he spent a lot of days on the ANTLR parser).

--
Fabio Maulo

[Oskar Berggren]

2011/2/6 Patrick Earl <hyn...@gmail.com>:

Both of these seem confusing to me. Take the first 100 elements and
then skip 30, i.e. returning row 31 up to 100, that is return 70 rows?

Option iii seems natural to me - with skip/take close to order by,
since order by is usually vital in this situtation.

As for grammar.... SQL sounds like commands, not a narrative. We order
the database to select some data from rows where this and that, we
also order it to skip some rows and take others. In contract, we do
not tell a story, where we describe how the db is selecting, ordering,
and skipping. So the grammar in option iii seems correct to me.

/Oskar

[Fabio Maulo]

Oskar,

when I talk about 'grammar' I mean the files HQL.g, HqlSqlWalker.g and SqlGenerator.g

--
Fabio Maulo

[Diego Mijelshon]

That _was_ sarcastic, right? I'm feeling Sheldon-y these days.
 
    Diego

[Oskar Berggren]

Consistent with Microsoft SQL Server yes (but version 2005 onwards has
better standard support.) SQL in general have a wider variety of
standard and non-standard methods.

As for intuitive... Is is really intuitive to mash the number of rows
to return together with the column specification? For me it feels more
natural to keep it close to ORDER BY... but then I learned SQL using
Postgresql. :)

2011/2/6 Michael Teper <mte...@gmail.com>:

[Oskar Berggren]

Yeah, I know. But someone earlier said using the -ING form might be
better grammar, which in that case should be english grammar.

/Oskar

2011/2/6 Fabio Maulo <fabio...@gmail.com>:

[Diego Mijelshon]

The problem with V and VI is that they introduce new reserved keywords. How would you select a property named "First"?

It _can_ be disambiguated in the parser, but I doubt it's a good idea.

It would be nice to be able to use the SKIP and TAKE operators in any order, and either before SELECT or after ORDER BY, so client code can use whatever feels more natural:

SKIP 5 SELECT something FROM there WHERE that ORDER BY these TAKE 10

...could be a supported construct.

 

    Diego

[Hoang Tang]

I agree with Diego that it would be nice to be able to use it in any order. Though I think this is more like a requirement anyway... since obviously there is a major different between

from cat skip 4 take 4 order by name  
vs.
from cat order by name skip 4 take 4

[Diego Mijelshon]

Hold on... that's not what I meant.

What I meant is:

- The operators should be usable either _at the end_ or _at the beginning_ of the query, not in the middle

- You should be able to put SKIP before TAKE or vice-versa

- You should be able to put one at the beginning and one at the end

But the semantics are the same in either case.
 
    Diego