Kevin Smedley wrote:

I can't see a reason to define an error state into existence.
Asking for zero rows is not an error under any circumstances.

It's the wording of the error message that's the bug. It should read
"Invalid fetch first n rows value. It must be a non-negative integer
constant."

--
Roy

UK Ingres User Association Conference 2010 will be on Tuesday June 8 2010
Go to http://www.iua.org.uk/join to get on the mailing list.

Damien Owen wrote:

Hell, I'm way past being offended by SQL; it's a question of what FIRST
means if there is no specified ordering. Ingres allows FIRST without
ORDER BY, so it just means SELECT RANDOMLY CHOSEN n...

Try explaining to your boss why MODIFYing a table breaks your
application; or why partitioning breaks it, or why running optimizedb
breaks it, or a new release of Ingres,...

--
Roy

UK Ingres User Association Conference 2010 will be on Tuesday June 8 2010
Go to http://www.iua.org.uk/join to get on the mailing list.

In my copy of the standard it does say that including a <fetch first
clause> is possibly non-deterministic.
If the <fetch first row count> is omitted the default value is 1, the
<fetch first row count> must be greater than 0 and a value of less
than 1 should raise an exception.

I know, just because it's in the standard doesn't make it right.

As a side note, there are some SQL implementations that do not return
a relations and support the <fetch first clause> syntax; so a <fetch
first clause> with a <fetch first row count> of 0 returns nothing.

In practical terms, implementing:
SELECT RANDOMLY CHOSEN n ...
Is harder to do and more inefficient. (and QA would have to grapple with
assuring randomness)
What I think we have might be better named:
SELECT ARBITRARILY CHOSEN n ROWS ...
The extra keyword would help some of the syntax problems.

FIRST is probably not the best keyword; FIRST can imply positional
significance but could also leave you looking for the FIRST n in your
backups. I would have preferred LIMIT TO n ROWS and after the FROM
clause but that might biased from RdB days.


-----Original Message-----
From: info-ingres-bounces (AT) kettleriver...ting (DOT) com
[mailto:info-ingres-bounces (AT) kettleriverconsulting (DOT) com] On Behalf Of Roy
Hann
Sent: 28 April 2010 10:36
To: info-ingres (AT) kettleriverconsulting (DOT) com
Subject: Re: [Info-Ingres] select first 0

Damien Owen wrote:

Hell, I'm way past being offended by SQL; it's a question of what FIRST
means if there is no specified ordering. Ingres allows FIRST without
ORDER BY, so it just means SELECT RANDOMLY CHOSEN n...

Try explaining to your boss why MODIFYing a table breaks your
application; or why partitioning breaks it, or why running optimizedb
breaks it, or a new release of Ingres,...

--
Roy

UK Ingres User Association Conference 2010 will be on Tuesday June 8
2010
Go to http://www.iua.org.uk/join to get on the mailing list.


_______________________________________________
Info-Ingres mailing list
Info-Ingres (AT) kettleriverconsulting (DOT) com
http://ext-cando.kettleriverconsulti...fo/info-ingres

withdefault wrote:

Arrrgh! F***ing boneheads. That isn't just unnecessary, it's plain,
flat-out wrong to want that behaviour.

This annoys me more than usual because as much as I hate SQL I *do*
think a standard, however imperfect, is infinitely better than no
standard at all. But something like this just demands to be defied.
There is plenty of other silly stuff in the standard that Ingres
ignores.

I say Ingres should return an empty table (unless it is installed with
strict SQL92 compliance turned on.)

Clearly.

Yeah, well that's just heading off into Looney Land. There is no point
trying to defend that to anyone, "relational purist" or otherwise.

--
Roy

UK Ingres User Association Conference 2010 will be on Tuesday June 8 2010
Go to http://www.iua.org.uk/join to get on the mailing list.

Ian Kirkham wrote:

You are quite right about that, and it is in keeping with the
boiler-plate code reminding us that session temporary tables PRESERVE
ROWS WITH NORECOVERY! :-)

Me too!! But after the ORDER BY clause.

--
Roy

UK Ingres User Association Conference 2010 will be on Tuesday June 8 2010
Go to http://www.iua.org.uk/join to get on the mailing list.

On Apr 28, 11:30*am, Roy Hann <specia... (AT) processed (DOT) almost.meat> wrote:
The <fetch first clause> syntax of [OFFSET m] [FETCH FIRST|NEXT n ROWS|
ROW ONLY] is available too which can succeed the "optional" ORDER BY
clause.

Roy Hann wrote:
Courtesy of Microsoft:

[22] varley.FreeTDS.1> select top 0 type from systypes
[22] varley.FreeTDS.2> go
type
----

(0 rows affected)

--jkl