Hi All,



Here's some fun...select first 0 * from iitables;



Returns all the rows in iitables...Why are my users so inventive? They
were expecting just the heading row, sort of equivalent to select * from
iitables where 1=0.



According to the manual select first numrows, numrows must be a positive
integer. So zero isn't a positive integer...which is cool, but it should
generate an error I would have thought.



I'm starting to wonder when they'll ask for select first -1 * from
iitables to return the last row.

BTW. If you do that select you get the somewhat confusing error message:

E_US0837 line 1, FROM clause is mandatory when a column ('first') has
been specified in the target list.



Martin Bowes

The parameter to FIRST must be literal number and not an expression and
hence FIRST is interpreted as a column and then, so is FROM due to the
expression context of the * - hence you have no from list.



________________________________

From: info-ingres-bounces (AT) kettleriver...ting (DOT) com
[mailto:info-ingres-bounces (AT) kettleriverconsulting (DOT) com] On Behalf Of
Martin Bowes
Sent: 27 April 2010 09:15
To: Ingres and related product discussion forum
Subject: [Info-Ingres] select first 0



Hi All,



Here's some fun...select first 0 * from iitables;



Returns all the rows in iitables...Why are my users so inventive? They
were expecting just the heading row, sort of equivalent to select * from
iitables where 1=0.



According to the manual select first numrows, numrows must be a positive
integer. So zero isn't a positive integer...which is cool, but it should
generate an error I would have thought.



I'm starting to wonder when they'll ask for select first -1 * from
iitables to return the last row.

BTW. If you do that select you get the somewhat confusing error message:

E_US0837 line 1, FROM clause is mandatory when a column ('first') has
been specified in the target list.



Martin Bowes

I somehow expected this behavior.

Kim Ginnerup

Fra: info-ingres-bounces (AT) kettleriver...ting (DOT) com [mailto:info-ingres-bounces (AT) kettleriverconsulting (DOT) com] På vegne af Martin Bowes
Sendt: 27. april 2010 10:15
Til: Ingres and related product discussion forum
Emne: [Info-Ingres] select first 0

Hi All,

Here's some fun...select first 0 * from iitables;

Returns all the rows in iitables...Why are my users so inventive? They wereexpecting just the heading row, sort of equivalent to select * from iitables where 1=0.

According to the manual select first numrows, numrows must be a positive integer. So zero isn't a positive integer...which is cool, but it should generate an error I would have thought.

I'm starting to wonder when they'll ask for select first -1 * from iitablesto return the last row.
BTW. If you do that select you get the somewhat confusing error message:
E_US0837 line 1, FROM clause is mandatory when a column ('first') has been specified in the target list.

Martin Bowes

In the 'select first 0 *' query the parse is handled by psf exactly as
for the case 'select first n' where n is > 0, and the first_n node is
created with 0 as its value. However later on opf quietly ignores
first_n values of zero or less, so the effect for the user is just as if
the query had been 'select * from...'.



In the 'select first -1 *' query the lexical analyser sees the '-' and
thinks 'arithmetic expression', so passes 'first' up to the parser as a
NAME token rather than the reserved FIRST token. So the parser is going
along the lines of 'select <columname1> - 1 * <columname2>', where
columname1 is 'first' and columname2 is 'from'. Having eaten up the
'from' as a columname it complains about not finding an SQL 'from',
hence the error message.



It wouldn't be hard to change the behaviour of select first 0 to return
an error message.





From: info-ingres-bounces (AT) kettleriver...ting (DOT) com
[mailto:info-ingres-bounces (AT) kettleriverconsulting (DOT) com] On Behalf Of
Martin Bowes
Sent: 27 April 2010 09:15
To: Ingres and related product discussion forum
Subject: [Info-Ingres] select first 0



Hi All,



Here's some fun...select first 0 * from iitables;



Returns all the rows in iitables...Why are my users so inventive? They
were expecting just the heading row, sort of equivalent to select * from
iitables where 1=0.



According to the manual select first numrows, numrows must be a positive
integer. So zero isn't a positive integer...which is cool, but it should
generate an error I would have thought.



I'm starting to wonder when they'll ask for select first -1 * from
iitables to return the last row.

BTW. If you do that select you get the somewhat confusing error message:

E_US0837 line 1, FROM clause is mandatory when a column ('first') has
been specified in the target list.



Martin Bowes

Kevin Smedley wrote:

I don't feel that SELECT FIRST 0... is an error. It should do what
Marty's programmer expected.

What do other DBMSs do? (I don't have anything else running at the
moment.)

--
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.

Other DBMSs vary as to whether they produce an empty result or disallow
but as far as I am aware the actual syntax used with FIRST following the
SELECT is unique to Ingres and the syntax is poor in that it can so
easily lead to odd restrictions and confusing error messages due to the
juxtaposition with the target list.
Regards,
Ian

-----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: 27 April 2010 18:43
To: info-ingres (AT) kettleriverconsulting (DOT) com
Subject: Re: [Info-Ingres] select first 0

Kevin Smedley wrote:

I don't feel that SELECT FIRST 0... is an error. It should do what
Marty's programmer expected.

What do other DBMSs do? (I don't have anything else running at the
moment.)

--
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

Ian Kirkham wrote:

The whole idea of ordering in a set (or bag) is stupid. If SELECT FIRST
is going to be allowed at all, it should be allowed only when an ORDER
BY is also specified (and even then it should count distinct sort key
values, not rows, IMO).

But given that Ingres allows it, asking for the set of zero rows is the
only case other than "all rows" that actually makes any mathematical
sense.

--
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.

Given a choice I'd rather it returned the data header and no rows. It
makes more sense that way.



Should I raise an issue with tech support?



Marty



From: Kevin Smedley [mailto:Kevin.Smedley (AT) ingres (DOT) com]
Sent: 27 April 2010 15:12
To: Ingres and related product discussion forum
Subject: Re: [Info-Ingres] select first 0



In the 'select first 0 *' query the parse is handled by psf exactly as
for the case 'select first n' where n is > 0, and the first_n node is
created with 0 as its value. However later on opf quietly ignores
first_n values of zero or less, so the effect for the user is just as if
the query had been 'select * from...'.



In the 'select first -1 *' query the lexical analyser sees the '-' and
thinks 'arithmetic expression', so passes 'first' up to the parser as a
NAME token rather than the reserved FIRST token. So the parser is going
along the lines of 'select <columname1> - 1 * <columname2>', where
columname1 is 'first' and columname2 is 'from'. Having eaten up the
'from' as a columname it complains about not finding an SQL 'from',
hence the error message.



It wouldn't be hard to change the behaviour of select first 0 to return
an error message.





From: info-ingres-bounces (AT) kettleriver...ting (DOT) com
[mailto:info-ingres-bounces (AT) kettleriverconsulting (DOT) com] On Behalf Of
Martin Bowes
Sent: 27 April 2010 09:15
To: Ingres and related product discussion forum
Subject: [Info-Ingres] select first 0



Hi All,



Here's some fun...select first 0 * from iitables;



Returns all the rows in iitables...Why are my users so inventive? They
were expecting just the heading row, sort of equivalent to select * from
iitables where 1=0.



According to the manual select first numrows, numrows must be a positive
integer. So zero isn't a positive integer...which is cool, but it should
generate an error I would have thought.



I'm starting to wonder when they'll ask for select first -1 * from
iitables to return the last row.

BTW. If you do that select you get the somewhat confusing error message:

E_US0837 line 1, FROM clause is mandatory when a column ('first') has
been specified in the target list.



Martin Bowes

Personally I agree that it would be nicer not to return an error, and a
quick look at the code suggests it would be a small change to just
return the column names and (0 rows) output.

If I've read the code correctly it does seem as if Ingres will return
the expected error if the 'n' of 'first n' is a variable (via a
database procedure seems to be one option) that evaluates to 0 or less.
In such a case the error is:

E_US0874 Invalid fetch first n rows value. It must be a positive
integer.
(Wed Apr 28 08:19:10 2010)

If this is the case, then that might make the case for not returning an
error for the 'select first 0' where 0 is a constant less persuasive.


-----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: 27 April 2010 18:43
To: info-ingres (AT) kettleriverconsulting (DOT) com
Subject: Re: [Info-Ingres] select first 0

Kevin Smedley wrote:

I don't feel that SELECT FIRST 0... is an error. It should do what
Marty's programmer expected.

What do other DBMSs do? (I don't have anything else running at the
moment.)

--
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

Ian Kirkham wrote:

-----Original Message-----
From: Roy Hann
Sent: 28 April 2010 08:05
Subject: Re: [Info-Ingres] select first 0

The whole idea of ordering in a set (or bag) is stupid. If SELECT FIRST
is going to be allowed at all, it should be allowed only when an ORDER
BY is also specified (and even then it should count distinct sort key
values, not rows, IMO).

But given that Ingres allows it, asking for the set of zero rows is the
only case other than "all rows" that actually makes any mathematical
sense.

--
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.

_______________________________________________

I agree that the idea of ordering a set goes against set theory and the pure relational model that Ingres is an implementation of (or at least based on) but, that doesn't mean that it wont have a practical use in the real world. Admittedly if you're using it you might not be creating the most elegant solution but, sometimes you just have to get the job done... even if it offends the relational purists. Try explaining to your boss that the client is pissed off because you wont implement a feature they want because doing so violates set theory.

Damien Owen
Sandyx Systems



-------------------------------------------------------------------------------------------
This email and the files transmitted with it are meant solely for the use of the individual addressee named above.
They may contain confidential and/or legally privileged information. If you are not the addressee or responsible for
delivery of the message to the addressee, please delete it from your system and contact the sender right away.
Sandyx Systems Ltd has taken steps to ensure that this email and any attachments are virus-free, but it remains
your responsibility to confirm and ensure this.