Firebird

At 02:58 PM 7/30/2001 +0200, Michael Weissenbacher wrote:

>it would be really cool to have a LAST(n) option as counterpart to FIRST(n).
>i'd love to run some tests on this new stuff...

Aside from being symmetric, what does LAST(n) do for you?  Does anyone have
a suggestion of how to implement it?  I suppose we could have a ring buffer
of size n, and send its contents when we get EOS.


Regards,

Ann
 http://www.ibphoenix.com
We have answers.

Michael,

> From: Michael Weissenbacher [mailto:MWeissenbacher@...]
> Sent: Monday, July 30, 2001 8:58 AM

...

> finally something is going on regarding limit!

You know, you were more than welcome to make that contribution yourself
--  you didn't have to wait on anybody else.


> there is something regarding the syntax that i would like to 
> see. i think
> the index should be 0-based

Unfortunately, SQL (in general) is 1 based!

, and the () should be optional. 
> the parameter
> order should be reversed (first starting index, second number 
> of records to
> retrieve). that's because the only other database i know that supports
> limit, mySQL, starts it's index from 0 and doesn't use () and 
> the parameters
> are in reverse order. it would be a LOT easyier to convert 
> from mySQL to
> interbase if these things don't differ. some examples:
> select * from table LIMIT 5,10;  # Retrieve rows 6-15
> select * from table LIMIT 5;     # Retrieve first 5 rows
> select * from table LIMIT 0,10;     # Retrieve rows 1-10
 
Well, here we have an example (amonst the many to choose from) where
each SQL engine is different -- the syntax which John implements is 90%+
compatible with PostgreSQL.  

And since a conversion from MySQL would involve a fair number of SQL
changes anyways, I'm of the opinion that having to make this additional
change for LIMIT would not be a burden.

(I'm must admit that I personally find the syntax a little backward but
I think I can adapt -- afterall we are taking about SQL, who ever said
it was logical!)

> it would be really cool to have a LAST(n) option as 
> counterpart to FIRST(n).
> i'd love to run some tests on this new stuff...

That's available right now -- just use TOP and reverse your sort order.

Seriously, as Ann suggested implementing LAST seems like a lot of
(needless) work.


Sean

i think, in "real" problems there is not much use for a LAST(n) option, but
still it would be nice.
i think if the statement contains a order by clause the direction of the
order could be reversed, then the FIRST(n) retrieved. i also like the idea
with the ring buffer...
i'm wondering why john mentioned that there is engine code that supports it,
is there?

regards,
michael weissenbacher

-----Original Message-----
From: Ann W. Harrison [mailto:aharrison@...]
Sent: Monday, July 30, 2001 4:25 PM
To: firebird-devel@...
Cc: 'bellardo@...'
Subject: RE: [Firebird-devel] LIMIT


At 02:58 PM 7/30/2001 +0200, Michael Weissenbacher wrote:

>it would be really cool to have a LAST(n) option as counterpart to
FIRST(n).
>i'd love to run some tests on this new stuff...

Aside from being symmetric, what does LAST(n) do for you?  Does anyone have
a suggestion of how to implement it?  I suppose we could have a ring buffer
of size n, and send its contents when we get EOS.


Regards,

Ann
 http://www.ibphoenix.com
We have answers.


_______________________________________________
Firebird-devel mailing list
Firebird-devel@...
http://lists.sourceforge.net/lists/listinfo/firebird-devel

> You know, you were more than welcome to make that contribution yourself
> --  you didn't have to wait on anybody else.
i just joined the mailing list two days ago and was happy seeing there is so
much discussion being done ;). but unfortunately i'm more a user because i'm
interfacing to firebird via interclient, and i'm programming java all the
time so i don't think i could do much good working around in firebird's c
code ;). i just want information about the development and maybe i can help
by testing new features or discussion about syntax!

> Unfortunately, SQL (in general) is 1 based!
that's a good point

> Well, here we have an example (amonst the many to choose from) where
> each SQL engine is different -- the syntax which John implements is 90%+
> compatible with PostgreSQL.  
well, again, limit is not standard sql that's why there are different
implementations. i just thougt it would be good to do it like in mySQL.

> That's available right now -- just use TOP and reverse your sort order.
that's right, so if it can't be done easily let it be

thanks for reading
michael weissenbacher

If you do have some time and are using java, you could try out the jca/jdbc
driver we have been working on (in cvs under client-java).  I'm interested
to know how much of it works in (semi-) real world situations.  You can
deploy it as an (xa capable) jca resource adapter in jboss, or as a jdbc 1
driver in normal java programs.  If you are interested and need more
instructions, let me know.

thanks
david jencks

On 2001.07.30 11:03:39 -0400 Michael Weissenbacher wrote:
> > You know, you were more than welcome to make that contribution yourself
> > --  you didn't have to wait on anybody else.
> i just joined the mailing list two days ago and was happy seeing there is
> so
> much discussion being done ;). but unfortunately i'm more a user because
> i'm
> interfacing to firebird via interclient, and i'm programming java all the
> time so i don't think i could do much good working around in firebird's c
> code ;). i just want information about the development and maybe i can
> help
> by testing new features or discussion about syntax!
> 
> > Unfortunately, SQL (in general) is 1 based!
> that's a good point
> 
> > Well, here we have an example (amonst the many to choose from) where
> > each SQL engine is different -- the syntax which John implements is
> 90%+
> > compatible with PostgreSQL.  
> well, again, limit is not standard sql that's why there are different
> implementations. i just thougt it would be good to do it like in mySQL.
> 
> > That's available right now -- just use TOP and reverse your sort order.
> that's right, so if it can't be done easily let it be
> 
> thanks for reading
> michael weissenbacher
> 
> 
> _______________________________________________
> Firebird-devel mailing list
> Firebird-devel@...
> http://lists.sourceforge.net/lists/listinfo/firebird-devel
> 
>

Michael,

> -----Original Message-----
> From: Michael Weissenbacher [mailto:MWeissenbacher@...]
> Sent: Monday, July 30, 2001 11:04 AM

...

> > You know, you were more than welcome to make that 
> contribution yourself
> > --  you didn't have to wait on anybody else.
> i just joined the mailing list two days ago and was happy 
> seeing there is so
> much discussion being done ;). 

I apologize for the tone of my posting -- when I read your message I
thought that you had been a long time user and list subscriber.  I
didn't mean to 'flame' a 'newby'.


> but unfortunately i'm more a 
> user because i'm
> interfacing to firebird via interclient, and i'm programming 
> java all the
> time so i don't think i could do much good working around in 
> firebird's c
> code ;). i just want information about the development and 
> maybe i can help
> by testing new features or discussion about syntax!

All help and comments are appreciated!


> > Well, here we have an example (amonst the many to choose from) where
> > each SQL engine is different -- the syntax which John 
> implements is 90%+
> > compatible with PostgreSQL.  
> well, again, limit is not standard sql that's why there are different
> implementations. i just thougt it would be good to do it like 
> in mySQL.

I appreciate where your coming from, however, of the two engines
(PstgreSQL vs. mySQL) mySQL is the least SQL compliant so, I think it's
better to stay in the "mainstream".

> 
> > That's available right now -- just use TOP and reverse your 
> sort order.
> that's right, so if it can't be done easily let it be
 
It was just a suggestion, let's wait for John B. to comment about the
possible implementation of LAST.


Sean

Hi,

On Monday, July 30, 2001, at 07:46 AM, Michael Weissenbacher wrote:

> i think, in "real" problems there is not much use for a LAST(n) option, 
> but
> still it would be nice.

I agree.  I don't think there is much need for a LAST(n) that wouldn't 
be satisfied with FIRST(n) and a reverse order sort.  Especially since 
LAST(n) would take a fair amount of work to implement.

A select count(*) from xxx followed by a select * from xxx limit (ALL, 
N - count) would also work.

> i think if the statement contains a order by clause the direction of the
> order could be reversed, then the FIRST(n) retrieved. i also like the 
> idea
> with the ring buffer...

Hmm.  If you wanted the last, say, 1,000,000 records the ring buffer 
idea seems like a resource (and maybe performance) hog.

> i'm wondering why john mentioned that there is engine code that 
> supports it,
> is there?

The code I said already exists would be to handle

>  LIMIT(ALL, N)

which is different than LAST(N).  The 'ALL' would tell the engine to 
return all records starting with record N.  This syntax could be used to 
implement LAST(N) *if* you know how many records are in the result set.  
But it does not implement LAST(N).  You need some buffer or other logic 
to correctly implement LAST.  I think right now our time is best spent 
in testing FB so we can release it.

> [.....]

-John

Hi,

On Monday, July 30, 2001, at 05:58 AM, Michael Weissenbacher wrote:

> finally something is going on regarding limit!
> there is something regarding the syntax that i would like to see. i 
> think
> the index should be 0-based,

As mentioned in a previous post by Sean L. 1 based is more SQL-ish.  
There was a thread in firebird-devel with the subject "First & limit" 
that talked about the 0 vs 1 based issue.  You may want to look in the 
archives.

> and the () should be optional

I don't see any problem with that.  Lets see what other think.

> the parameter
> order should be reversed (first starting index, second number of 
> records to
> retrieve).

I agree that the order seems odd.  The reason I put them in 
LIMIT(length, start) order is the LIMIT(length) syntax.  I figured it 
would be easier to remember to add a parameter to LIMIT(length) to get 
LIMIT(length, start), than to remember a different syntax.  Although the 
LIMIT(start, length) is more natural to us humans :-)  Anyone else have 
thoughts here?

> that's because the only other database i know that supports
> limit, mySQL, starts it's index from 0 and doesn't use () and the 
> parameters
> are in reverse order. it would be a LOT easyier to convert from mySQL to
> interbase if these things don't differ. some examples:
> select * from table LIMIT 5,10;  # Retrieve rows 6-15
> select * from table LIMIT 5;     # Retrieve first 5 rows
> select * from table LIMIT 0,10;     # Retrieve rows 1-10
>

The two open source rdbms that people are converting from are PostgreSQL 
and mySQL.  It was mentioned that PostgreSQL does things different than 
mySQL (1 based, etc.).  If we chose to do it one way the conversion 
process from the other will be effected.  Anyone know how (if) Oracle 
does it?  Generally I don't think "the other rdbms does it this way so 
we should" is a good argument, unless all (or most all) other rdbms do 
it that way.

> it would be really cool to have a LAST(n) option as counterpart to 
> FIRST(n).
> i'd love to run some tests on this new stuff...

This is being discussed in other responses.

-John

On Monday, July 30, 2001, at 09:53 AM, John Bellardo wrote:
> Hi,
>
> On Monday, July 30, 2001, at 05:58 AM, Michael Weissenbacher wrote:
>
>> finally something is going on regarding limit!
>> there is something regarding the syntax that i would like to see.
>> [....]

Another change that was mentioned previously is to move the FIRST N 
clause from after the join list to after the SELECT keyword.  The syntax 
would be:

SELECT FIRST 10 * FROM ... WHERE ...

as opposed to

SELECT * FROM ... FIRST 10 WHERE ...

the LIMIT statement would remain in the same place:

SELECT * FROM ... LIMIT (1, 2) WHERE ...

Thoughts?

-John

On Monday 30 July 2001 11:00, John Bellardo wrote:
> SELECT FIRST 10 * FROM ... WHERE ...
>
> as opposed to
>
> SELECT * FROM ... FIRST 10 WHERE ...
>
> the LIMIT statement would remain in the same place:
>
> SELECT * FROM ... LIMIT (1, 2) WHERE ...
>
> Thoughts?

I personally find this a more sensible location and for what its worth this 
is where the clause is located in Sybase SQL Anywhere.  Their syntax is:

	SELECT [ALL | DISTINCT] [FIRST n | TOP n] select-list ...

Wouldn't hurt to check some other databases like Oracle, DB2, ... and just 
see if there is anything approximating consensus between them.

Any idea if this is part of any future SQL standard either?

-- 
Brad Pepers
brad@...

At 10:00 AM 7/30/2001 -0700, John Bellardo wrote:

>Another change that was mentioned previously is to move the FIRST N clause 
>from after the join list to after the SELECT keyword.  The syntax would be:
>
>SELECT FIRST 10 * FROM ... WHERE ...
>
>SELECT * FROM ... LIMIT (1, 2) WHERE ...

Those sound fine to me.  I'd make the parentheses optional
on both clauses, but I have a tendency toward looseness in
syntax that my spouse deplores.


Cheers,

Ann

John,

> From: John Bellardo [mailto:bellardo@...]
> Sent: Monday, July 30, 2001 1:01 PM

> 
> the LIMIT statement would remain in the same place:
> 
> SELECT * FROM ... LIMIT (1, 2) WHERE ...
> 

FYI  -- The PostgreSQL syntax calls for the LIMIT to be at the end of
the SELECT... FROM... WHERE ... ORDER BY... statement.

Personally, I think that they all (TOP/FIRST/LIMIT) should be at the
front of the statement just after the SELECT.  

Why wouldn't TOP/FIRST be placed exactly the same as LIMIT???  

Just wanting to make it deliberately confusing?  (Well, maybe after all
where talking about SQL right!)

It doesn't matter to me, as long as we are consistent!


Sean

At 01:35 PM 7/30/2001 -0400, Leyne, Sean wrote:


>Why wouldn't TOP/FIRST be placed exactly the same as LIMIT???
>...
>It doesn't matter to me, as long as we are consistent!

A reasonable alternative to consistency is flexibility - take
either clause in either place...  but see previous comment on
syntax.


Regards,

Ann
 http://www.ibphoenix.com
We have answers.

On Monday, July 30, 2001, at 10:50 AM, Ann W. Harrison wrote:

> At 01:35 PM 7/30/2001 -0400, Leyne, Sean wrote:
>
>
>> Why wouldn't TOP/FIRST be placed exactly the same as LIMIT???
>> ...
>> It doesn't matter to me, as long as we are consistent!
>
> A reasonable alternative to consistency is flexibility - take
> either clause in either place...  but see previous comment on
> syntax.

Now you are just trying to make my life difficult ;-)  One of the 
benefits of having them both in the same place is error handling.  For 
the following query:

SELECT FIRST 5 * FROM MY_TABLE LIMIT (4, 5)

We would have to add extra error checking (not too hard) and figure out 
how to deal with it.  If they were both in one place the existing parser 
would handle it.

The SELECT FIRST X/LIMIT (X, Y) * FROM ... syntax seems to flow better.  
It also avoids the confusion of the FIRST/LIMIT clause being associated 
with a table in the join.  Example:  SELECT * FROM TABLE1 FIRST 5, 
TABLE2 LIMIT (4, 6).

But when:

SELECT FIRST 5 COUNT(*) FROM TABLE1

returns 5, it may be a little confusing.  The user might have expected 
to get the first 5 rows of the result.  The first row in the result 
being the count of rows in TABLE1, and the rest don't exist.

-John

In SQL Server :

<query specification> ::= 
SELECT [ ALL | DISTINCT ]
[ {TOP integer | TOP integer PERCENT} [ WITH TIES] ] 
<select_list>
[ INTO new_table ]
[ FROM {<table_source>} [,...n] ]
[ WHERE <search_condition> ]
[ GROUP BY [ALL] group_by_expression [,...n] 
[ WITH { CUBE | ROLLUP } ]
]
[ HAVING <search_condition> ]

Sybase up to 11.9.2 does not have TOP/FIRST/LIMIT commands.

At 14:11 30/07/2001, you wrote:
>Hi,
>
>On Monday, July 30, 2001, at 05:58 AM, Michael Weissenbacher wrote:
>
>> finally something is going on regarding limit!
>> there is something regarding the syntax that i would like to see. i 
>> think
>> the index should be 0-based,
>
>As mentioned in a previous post by Sean L. 1 based is more SQL-ish.  
>There was a thread in firebird-devel with the subject "First & limit" 
>that talked about the 0 vs 1 based issue.  You may want to look in the 
>archives.
>
>> and the () should be optional
>
>I don't see any problem with that.  Lets see what other think.
>
>> the parameter
>> order should be reversed (first starting index, second number of 
>> records to
>> retrieve).
>
>I agree that the order seems odd.  The reason I put them in 
>LIMIT(length, start) order is the LIMIT(length) syntax.  I figured it 
>would be easier to remember to add a parameter to LIMIT(length) to get 
>LIMIT(length, start), than to remember a different syntax.  Although the 
>LIMIT(start, length) is more natural to us humans :-)  Anyone else have 
>thoughts here?
>
>> that's because the only other database i know that supports
>> limit, mySQL, starts it's index from 0 and doesn't use () and the 
>> parameters
>> are in reverse order. it would be a LOT easyier to convert from mySQL to
>> interbase if these things don't differ. some examples:
>> select * from table LIMIT 5,10;  # Retrieve rows 6-15
>> select * from table LIMIT 5;     # Retrieve first 5 rows
>> select * from table LIMIT 0,10;     # Retrieve rows 1-10
>>
>
>The two open source rdbms that people are converting from are PostgreSQL 
>and mySQL.  It was mentioned that PostgreSQL does things different than 
>mySQL (1 based, etc.).  If we chose to do it one way the conversion 
>process from the other will be effected.  Anyone know how (if) Oracle 
>does it?  Generally I don't think "the other rdbms does it this way so 
>we should" is a good argument, unless all (or most all) other rdbms do 
>it that way.
>
>> it would be really cool to have a LAST(n) option as counterpart to 
>> FIRST(n).
>> i'd love to run some tests on this new stuff...
>
>This is being discussed in other responses.
>
>-John

--
Auf Wiedersehn

        Juarez Rudsatz

Leyne, Sean wrote:

>John,
>
>>From: John Bellardo [mailto:bellardo@...]
>>Sent: Monday, July 30, 2001 1:01 PM
>>
>
>>the LIMIT statement would remain in the same place:
>>
>>SELECT * FROM ... LIMIT (1, 2) WHERE ...
>>
>
>FYI  -- The PostgreSQL syntax calls for the LIMIT to be at the end of
>the SELECT... FROM... WHERE ... ORDER BY... statement.
>
>Personally, I think that they all (TOP/FIRST/LIMIT) should be at the
>front of the statement just after the SELECT.  
>
>Why wouldn't TOP/FIRST be placed exactly the same as LIMIT???  
>
>Just wanting to make it deliberately confusing?  (Well, maybe after all
>where talking about SQL right!)
>
>It doesn't matter to me, as long as we are consistent!
>
I agree, I prefer that there be only one way to do a thing, multiple 
syntaxes make it confusing.

While we are on this it seems to me that limit(x,y) is itself confusing, 
since both the x and y seem to be from what Im hearing work differently 
in other implementations.  I know it's late but what about not having a 
limit with two parameters but a seperate clause to describe the skip 
component at least that way there would be no confusion when looking at 
limit:   

select * from table skip (20) limit (50) ;

first(50) then becomes an alias of limit(50).

The postgres syntax used a fancier keywork for skip, but they keyword 
was only an option on their limit clause - but Im not in a position to 
look up the previous post from sean about it.

Mainly Im suggesting this becuase limit(x,y) is obviously going to 
result in some confusionif it means limit(start, endpos) or limit(start, 
length) or limit(length, skipnum)  - (much like most substring commands 
result in similar confusion).  And having a seperate clause for each 
makes it clear exactly what is being asked for.


Cheers

Mark

> -----Original Message-----
> From: firebird-devel-admin@...
> [mailto:firebird-devel-admin@... Behalf Of Mark
> O'Donohue
> Sent: Lunes 30 de Julio de 2001 21:12
>
> Mainly Im suggesting this becuase limit(x,y) is obviously going to
> result in some confusionif it means limit(start, endpos) or limit(start,
> length) or limit(length, skipnum)

How many more keywords would users tolerate?
:-)


  - (much like most substring commands
> result in similar confusion).

Mark, please explain how this syntax is confusing:
select substring(field FROM startpos FOR length) from tbl

The SQL committee is a great friend of verbosity and I adhered to the
standard syntax.


> And having a seperate clause for each
> makes it clear exactly what is being asked for.

I basically agree, because anybody may forget how the thing works given that
there are so many options among current SQL-based servers.

C.

I agree- I like skip n limit m better than limit n, m

david jencks

On 2001.07.30 21:12:22 -0400 Mark O'Donohue wrote:
> Leyne, Sean wrote:
> 
> >John,
> >
> >>From: John Bellardo [mailto:bellardo@...]
> >>Sent: Monday, July 30, 2001 1:01 PM
> >>
> >
> >>the LIMIT statement would remain in the same place:
> >>
> >>SELECT * FROM ... LIMIT (1, 2) WHERE ...
> >>
> >
> >FYI  -- The PostgreSQL syntax calls for the LIMIT to be at the end of
> >the SELECT... FROM... WHERE ... ORDER BY... statement.
> >
> >Personally, I think that they all (TOP/FIRST/LIMIT) should be at the
> >front of the statement just after the SELECT.  
> >
> >Why wouldn't TOP/FIRST be placed exactly the same as LIMIT???  
> >
> >Just wanting to make it deliberately confusing?  (Well, maybe after all
> >where talking about SQL right!)
> >
> >It doesn't matter to me, as long as we are consistent!
> >
> I agree, I prefer that there be only one way to do a thing, multiple 
> syntaxes make it confusing.
> 
> While we are on this it seems to me that limit(x,y) is itself confusing, 
> since both the x and y seem to be from what Im hearing work differently 
> in other implementations.  I know it's late but what about not having a 
> limit with two parameters but a seperate clause to describe the skip 
> component at least that way there would be no confusion when looking at 
> limit:   
> 
> select * from table skip (20) limit (50) ;
> 
> first(50) then becomes an alias of limit(50).
> 
> The postgres syntax used a fancier keywork for skip, but they keyword 
> was only an option on their limit clause - but Im not in a position to 
> look up the previous post from sean about it.
> 
> Mainly Im suggesting this becuase limit(x,y) is obviously going to 
> result in some confusionif it means limit(start, endpos) or limit(start, 
> length) or limit(length, skipnum)  - (much like most substring commands 
> result in similar confusion).  And having a seperate clause for each 
> makes it clear exactly what is being asked for.
> 
> 
> Cheers
> 
> Mark
> 
> 
> 
> 
> _______________________________________________
> Firebird-devel mailing list
> Firebird-devel@...
> http://lists.sourceforge.net/lists/listinfo/firebird-devel
> 
>

Claudio Valderrama C. wrote:

>>-----Original Message-----
>>From: firebird-devel-admin@...
>>[mailto:firebird-devel-admin@... Behalf Of Mark
>>O'Donohue
>>Sent: Lunes 30 de Julio de 2001 21:12
>>
>>Mainly Im suggesting this becuase limit(x,y) is obviously going to
>>result in some confusionif it means limit(start, endpos) or limit(start,
>>length) or limit(length, skipnum)
>>
>
>How many more keywords would users tolerate?
>:-)
>
>
>  - (much like most substring commands
>
>>result in similar confusion).
>>
>
>Mark, please explain how this syntax is confusing:
>select substring(field FROM startpos FOR length) from tbl
>
Sorry claudio, I was talking substring in general, not just sql.  
Substrings can be either substring(startPos, len) or substring(startPos, 
endPos) (The later being rarer nowadays) but seeing substring(12,14) 
doesn't tell you which one is being used.  With position only parameters 
it's not possible to give any context to what the parameter means.  I 
see in the above (now remember I've never used substring in sql 
statements, probably because Im a java programmer) but I agree that the 
syntax above is clear.

So would you suggest:

limit from 30 for 200 ?

(Actually offset was the token in postgres (as I look back over old 
posts from Sean))

so from postgres post from Sean,

LIMIT Clause 
> >
> >    LIMIT { count | ALL } [ { OFFSET | , } start ]
> >    OFFSET start




LIMIT 200 OFFSET 30 -- is the postgresql way

but I think Seans reading of it was that OFFSET cannot occur by itself so

select * from table
offset 30

was illegal.  can anyone confirm that?  

OFFSET is however a bit obscure, SKIP in my opinion is better, but that 
probably makes OFFSET a perfect candidate for a SQL standard :-).

>
>The SQL committee is a great friend of verbosity and I adhered to the
>standard syntax.
>
Clarity is fine, verbosity for its own sake is a pain, (just ask vax/vms 
dcl programmer why they all have rsi :-).


On another point, since you checked out the fb2 tree, Claudio, the fb2 
tree is not entirely complete, I still need to checkin a few new files, 
(configure.in being one of them).  I know this because I've now tried to 
check it out at work and it's not all there yet.


Cheers

Mark

Markm et al,

My vote is for the PostgreSQL syntax.  To needlessly develop our own
syntax doesn't make sense, when one which supports our needs already
exists.  It's not a 'perfect' world so let's be partical about this --
heck if you ask 10 developers you'll probably get 13 suggested syntaxes.

So with John current implementation the syntax sould be:

LIMIT count, start_pos

Ultimately it should be extended allow for the complete syntax.

The fact that we could support the syntax both after the SELECT and at
the end of the statement, is a "good thing" however.


Sean

> >The two open source rdbms that people are converting from are PostgreSQL
> >and mySQL.  It was mentioned that PostgreSQL does things different than
> >mySQL (1 based, etc.).  If we chose to do it one way the conversion
> >process from the other will be effected.  Anyone know how (if) Oracle
> >does it?

Yes, Oracle does. But Oracle not use LIMIT, just ROWNO pseudocolumn. So,
mSQL use pseudocolumn to crop resultset.

Hi,
	There has been much discussion on the LIMIT syntax.  Let me see if 
I can summarize it.  The way I see it there are two different issues, 
the keyword syntax and the position the keyword(s) are located in the 
SQL statement.

For keyword syntax we have 2 options:

#1)  FIRST X   and    LIMIT [(] X, Y [)]
    With the option of using 'ALL' instead of X in LIMIT.

#2)  FIRST X   and    FIRST X OFFSET Y   and    OFFSET Y
     Where OFFSET Y has the same functionality as LIMIT ALL, Y.  We could 
also use the keyword SKIP instead of OFFSET.


The positions of the keywords can be:

#1)  after the optional [ ALL | DISTINCT ] clause.

#2)  after the join list (like it is now).

#3)  all of the above.


I'd like to do an informal poll to figure out which options people 
prefer.  Please send me a private email (no need to add more traffic to 
the list) over the next few days and tell me your opinion.  This poll 
shouldn't stop discussion on the merits of the different options, but I 
am hoping to get the final syntax implemented by the weekend.

Thanks.

-John

Hi, Sean

At first, I happy that FB support limitation at all.

But I think (if it not too later) that right syntax in mySQL

SELECT *
FROM <TABLE>
WHERE <COND>
ORDER BY <COL>
LIMIT (FROM,COUNT)

Why:
- It's true way to expancion of FB. Postgress Projects usualy no need's to
change RDBMS. If it happen, there so changes in logic (triggers, SP), so
change limitation syntax ... simply. Successful mySQL project often need
RDBMS evolution - "friendly" syntax in FB it's good "commercial" motion
(It's seems that good idea to add in FB GET_LASTID() - function :)))) for
more compatibility)
- 4 developers adding limitation to end of SQL give more rulez:
    - clearly view query (especcialy when I write long query in one code
line my $sql = 'SELECT blah-blah...'(it's not good but quick));
    - it's easy to make query "on fly"
    - easy to reuse query (copy-paste-change)

> SELECT FIRST 5 COUNT(*) FROM TABLE1
>
> returns 5, it may be a little confusing.

But it's not right - LIMIT usual limiting result set, not aggregates, so

SELECT FIRST 5 COUNT(*) FROM TABLE1

Returns
F1
____
<count>

But

SELECT FIRST 0 COUNT(*) FROM TABLE1

Returns
F1
____
null

> -----Original Message-----
> From: Maximenko Alexander [mailto:alex@...]
> Sent: Wednesday, August 15, 2001 8:27 AM

> At first, I happy that FB support limitation at all.

Glad to hear it.


<comments advocating the mySQL LIMIT syntax SELECT...ORDER BY ... LIMIT(
FROM, COUNT)>

The fact is that anyone migrating from mySQL (the sooner the better as
far as I'm concerned) needs to make a number of changes in
design/implementation as well as changes in SQL statements (Hey, FB
support sub-selects and joins!).  Accordingly, to also expect a change
in the syntax for LIMIT should not be a big problem.

The bottom line is that until a 'standard' is developed for implementing
LIMIT, we will never please everybody -- so we're dammed if we do and
dammed if we don't.


Sean

> -----Original Message-----
> From: Maximenko Alexander [mailto:alex@...]
> Sent: Wednesday, August 15, 2001 8:31 AM

<snip>

> But
> 
> SELECT FIRST 0 COUNT(*) FROM TABLE1
> 
> Returns
> F1
> ____
> null

It also could be argued that no result set was requested/required and as
such the result seems to be correct.

I think that this could be considered is an invalid syntax "FIRST 0",
which should be rejected. Equally, the engine could add a
straightforward check if value < 1 then value = 1, and thus allow the
syntax -- "no crime no foul".


Sean

""Leyne, Sean"" <sleyne@...> wrote in message >

> The bottom line is that until a 'standard' is developed for implementing
> LIMIT, we will never please everybody -- so we're dammed if we do and
> dammed if we don't.

Yes, you are right )))

And, about mySQL migration...you are right too, i forgot than mySQL have a
lot of function, which not implemented in FB, and probably imposible to
implement by UDF... And mySQL supports REPLACE ...

But usualy mySQL projects so simply, so it migration easy (at any LIMIT
syntax ;-))) )...

But about FIRST 0 - if think it's right syntax - but empty result set. It
must not cause syntax error...

> -----Original Message-----
> From: firebird-devel-admin@...
> [mailto:firebird-devel-admin@... Behalf Of
> Maximenko Alexander
>
> And, about mySQL migration...you are right too, i forgot than mySQL have a
> lot of function, which not implemented in FB, and probably imposible to
> implement by UDF...

Can you give us of some of those, the ones that you consider most useful?
Why you say they can't be put in a UDF, are they aggregate functions like
AVG, for example?


> And mySQL supports REPLACE ...

What's replace? If you mean REPLACE TABLE, we have RECREATE TABLE. If you
mean something like

if cannot insert record because it exists
then update record

in a single command, I think it was on Jim's list a year ago.

C.

> Can you give us of some of those, the ones that you consider most useful?
Ok, I try ... for Mon or Thu, ok?

> Why you say they can't be put in a UDF, are they aggregate functions like
> AVG, for example?
>
Yes AVG, or another table based function.

And for sample GET_LASTID() - it's funny function that return (in term of
FB) value for last changed generator (of course, generator changed in
_current_ connection). Very seldom mySQL-based scripts ignore this
feature )))

So mySQL supports "blank" select (without FROM)...
>
> > And mySQL supports REPLACE ...
>
> What's replace? If you mean REPLACE TABLE, we have RECREATE TABLE.

> If you
> mean something like if cannot insert record because it exists
> then update record in a single command, I think it was on Jim's list a
year ago.
>
Yes, REPLACE similar INSERT syntax and used for replace or insert record.

Because mySQL doesn't  support UPDATE one table to another (FB support, but
it work very slow ;((( ), so mySQL developers often use REPLACE for update
;-)))

Hi,

> And for sample GET_LASTID() - it's funny function that return (in
term of
> FB) value for last changed generator (of course, generator changed
in
> _current_ connection). Very seldom mySQL-based scripts ignore this
> feature )))

This function doesnt make sense for Firebird, because there arent
auto-increment fields.

In mySQL you have
    create table foo (
        bar auto_increment,
        v integer,

        primary key (bar)
    );

and after
    insert into foo (v) values (1);

you'll need mysql_insert_id() because, there is no other way to find
out
the value of "bar".

In Firebird you would write:

    id = gen_id( MYGEN, 1 );
    insert into foo (bar, v) values (:id, 1);

So you know the value of the primary key already.

Tord

Hi, Tord.

I  say "in term of FB". Sorry for bad Enlish.

Of course my SQL use autoincrement, but FB can emulate autoincrement by
TRIGGER.

But every 4 weeks somebody asks at epsilon.public.interbase, how 2 get ID of
inserted record on client.

As you say at IB/FB we must get new generator ID _before_ INSERT (select
GEN_ID(...) from RDB$DATABASE), at mySQL we must get ID _after_ INSERT
(select GET_LASTID()).

Of course for developers most acceptable second choice, but ... imagine:
when you insert record, trigger cause INSERT at another TABLE, and that must
return GET_LASTID ? )))

Hi,

> Of course my SQL use autoincrement, but FB can emulate autoincrement
by
> TRIGGER.

Since I dont emulate autoincrement with trigger, I couldnt see your
concern at first.

Sorry I have no solution for your problem.


> Of course for developers most acceptable second choice, but ...
imagine:
> when you insert record, trigger cause INSERT at another TABLE, and
that must
> return GET_LASTID ? )))

I cant even think of a real world example for this one =:-)


Tord

Hello, Alexander!

Maximenko Alexander wrote:

> And for sample GET_LASTID() - it's funny function that return (in term of
> FB) value for last changed generator (of course, generator changed in
> _current_ connection). Very seldom mySQL-based scripts ignore this
> feature )))

So you want generators to be in user context? As I understand get_lastid()
remembers last returned to user autoincrement value.
In IB terms implementation could be as TIP for SHAPSHOT - by holding in
memory generator pages for each connection. I don't think that
this must be implemented at all. Cool little feature that needs a
lot of work in engine (new bugs)?

-- 
Dmitry Kuzmenko, Epsylon Technologies.

> -----Original Message-----
> From: firebird-devel-admin@...
> [mailto:firebird-devel-admin@... Behalf Of Tord
> Hammer
>
> > Of course for developers most acceptable second choice, but ...
> imagine:
> > when you insert record, trigger cause INSERT at another TABLE, and
> that must
> > return GET_LASTID ? )))
>
> I cant even think of a real world example for this one =:-)

I think this is the way MsSql works: once you have inserted a record in a
table that includes an autoinc field, you can retrieve that value
immediately with an API call. Indeed, several ASP/ADO applications I've seen
work this way with MsSql but they simply seem to refresh the record once it
has been inserted. Maybe ADO does the API magic internally. From MsSql
books:

@@IDENTITY and SCOPE_IDENTITY will return the last identity value generated
in any table in the current session. However, SCOPE_IDENTITY returns the
value only within the current scope; @@IDENTITY is not limited to a specific
scope.
IDENT_CURRENT is not limited by scope and session; it is limited to a
specified table. IDENT_CURRENT returns the identity value generated for a
specific table in any session and any scope.
The @@IDENTITY value does not revert to a previous setting if the INSERT or
SELECT INTO statement or bulk copy fails, or if the transaction is rolled
back.

Isn't it the same thing that some neophytes complain against FB? Some people
want generators to rollback their change if the transaction is rolled back,
until someone has to force them to think in terms on multi-user
applications.

C.

On Friday 17 August 2001 22:19, Claudio Valderrama C. wrote:
> > -----Original Message-----
> Isn't it the same thing that some neophytes complain against FB? Some
> people want generators to rollback their change if the transaction is
> rolled back, until someone has to force them to think in terms on
> multi-user
> applications.

I'm not a neophyte and just want to clarify that there *are* valid reason to 
have autogenerated ids work this way.  No one is going to force me to think 
otherwise though if any real arguments were offered I might be convinced (but 
I've heard them before and so far I find them unconvincing).  It seems to be 
a matter of short sightedness.  If you think multi-user has to be done a 
certain way, then you end up not wanting generating ids to lock but this 
misses that there *are* other ways of doing things where it does work just 
fine and provides some benefits.

Other fairly often used databases do include the id generating as part of the 
transaction and restore it on a rollback and they are certainly used for 
multi-user applications!

To my mind the best case would be that generating an id can be done in a 
function call like Firebird does but that it will be part of the transaction 
and either get commited or rolled back along with anything else in the 
transaction.  This way if you want it your way, you just start a transaction, 
get an id, and commit.  Now it works the way you want.  If I want it to be 
part of a larger transaction, then its up to me.

Anyway - I've heard a lot of "this is the way to do it and only an idiot 
would do it another way" and am sick of it and want to say that others with 
many years of experience are doing it this way so its not a rule carved in 
stone!

-- 
Brad Pepers
brad@...

Brad,

Is there some way to attach id generation to transactions without
serializing access to the generator?  If not, please explain in more
specific detail how allowing generated id rollback is appropriate in
multi-user environments.

thanks
david jencks

On 2001.08.18 02:40:32 -0400 Brad Pepers wrote:
> On Friday 17 August 2001 22:19, Claudio Valderrama C. wrote:
> > > -----Original Message-----
> > Isn't it the same thing that some neophytes complain against FB? Some
> > people want generators to rollback their change if the transaction is
> > rolled back, until someone has to force them to think in terms on
> > multi-user
> > applications.
> 
> I'm not a neophyte and just want to clarify that there *are* valid reason
> to 
> have autogenerated ids work this way.  No one is going to force me to
> think 
> otherwise though if any real arguments were offered I might be convinced
> (but 
> I've heard them before and so far I find them unconvincing).  It seems to
> be 
> a matter of short sightedness.  If you think multi-user has to be done a 
> certain way, then you end up not wanting generating ids to lock but this 
> misses that there *are* other ways of doing things where it does work
> just 
> fine and provides some benefits.
> 
> Other fairly often used databases do include the id generating as part of
> the 
> transaction and restore it on a rollback and they are certainly used for 
> multi-user applications!
> 
> To my mind the best case would be that generating an id can be done in a 
> function call like Firebird does but that it will be part of the
> transaction 
> and either get commited or rolled back along with anything else in the 
> transaction.  This way if you want it your way, you just start a
> transaction, 
> get an id, and commit.  Now it works the way you want.  If I want it to
> be 
> part of a larger transaction, then its up to me.
> 
> Anyway - I've heard a lot of "this is the way to do it and only an idiot 
> would do it another way" and am sick of it and want to say that others
> with 
> many years of experience are doing it this way so its not a rule carved
> in 
> stone!
> 
> -- 
> Brad Pepers
> brad@...
> 
> _______________________________________________
> Firebird-devel mailing list
> Firebird-devel@...
> http://lists.sourceforge.net/lists/listinfo/firebird-devel
> 
>

> So you want generators to be in user context? As I understand get_lastid()
> remembers last returned to user autoincrement value.
> In IB terms implementation could be as TIP for SHAPSHOT - by holding in
> memory generator pages for each connection. I don't think that
> this must be implemented at all. Cool little feature that needs a
> lot of work in engine (new bugs)?

Dmitry, GET_LASTID() was a joke. This thing can make easy migration
web-projects from mySQL to FB, but ... not so nessesary.

Could you give an example of another often used
database that does this?

I've looked around (a very little bit) and seen a couple
which handle it the same way as FB (postgresql for example)



-----Original Message-----
Other fairly often used databases do include the id generating as part of
the
transaction and restore it on a rollback and they are certainly used for
multi-user applications!

At 03:11 PM 8/17/2001 +0400, Maximenko Alexander wrote:

>Of course my SQL use autoincrement, but FB can emulate autoincrement by
>TRIGGER.  But every 4 weeks somebody asks at epsilon.public.interbase, how
>2 get ID of inserted record on client.
>
>As you say at IB/FB we must get new generator ID _before_ INSERT (select
>GEN_ID(...) from RDB$DATABASE), at mySQL we must get ID _after_ INSERT
>(select GET_LASTID()).

A reasonable way to handle this - already implemented in the engine -
is INSERT ... RETURNING <values>.  Oracle has something similar.  The
new code would be entirely in DSQL which would need to parse the new
clause on insert and add a second message in which to return the values.




Regards,

Ann
 http://www.ibphoenix.com
We have answers.

>On Friday 17 August 2001 22:19, Claudio Valderrama C. wrote:
> >
> > Isn't it the same thing that some neophytes complain against FB? Some
> > people want generators to rollback their change if the transaction is
> > rolled back, until someone has to force them to think in terms on
> > multi-user applications.

At 12:40 AM 8/18/2001 -0600, Brad Pepers wrote:

>I'm not a neophyte and just want to clarify that there *are* valid reason to
>have autogenerated ids work this way.  No one is going to force me to think
>otherwise though if any real arguments were offered I might be convinced (but
>I've heard them before and so far I find them unconvincing).  It seems to be
>a matter of short sightedness.

OK, I'll bite.  The goal of generators was to create a very low overhead
mechanism for creating unique identifiers.  The mechanism is very simple.
The RDB$GENERATORS table holds a generator id which is the offset into
an array of 32 or 64 bit integers - depending on the version of InterBase.
The array is stored on pages in the database with the page type of pag_ids.
To create a new value for a generator Firebird increments the array element
at the offset specified for that generator by the amount specified in the
gen_id function.  The new value is stored on the page in cache.  When a
transaction that has created a new generator value commits, the generator
page is written out.

I can imagine two alternatives that would not leave gaps in the number
space.  One is to lock the generator when it is updated and release it
when the transaction that updated it commits or rolls back.  That could
be done by adding a new lock type to the lock manager for locking generators.
Locking generators would not add significantly to the processing overhead
for the database but would reduce concurrency quite severely.  First, all
transactions that use a single generator would be serialized.  Second,
transactions that use more than one generator would be at risk of deadlocking
on the generators.

The second alternative that occurs to me is to keep an internal accounting
of the generator values acquired by a transaction and make the value
available again if the transaction rolls back.  That algorithm is 
non-blocking -
I think - but complicates the internal accounting considerably.  In addition
to its "next" value, each generator would have an array of values available
for reuse.  When the server shuts down, those arrays must be preserved, so they
must be stored on disk.  More seriously, the arrays must be preserved if 
someone
trips over the server's power cord.  As you know, Firebird does not recover
from a log, but keeps the database consistent so it requires no recovery 
process.
I don't see how to preserve the generator values that become available when
the running transactions fail because the server crashed.

I have no doubt that systems built on different principles could have
different behaviors, but I welcome any ideas that would allow Firebird to
provide generator values that match users' expectations.



Regards,

Ann
 http://www.ibphoenix.com
We have answers.

On August 27, 2001 12:49 pm, Ann W. Harrison wrote:
> >On Friday 17 August 2001 22:19, Claudio Valderrama C. wrote:
> > > Isn't it the same thing that some neophytes complain against FB? Some
> > > people want generators to rollback their change if the transaction is
> > > rolled back, until someone has to force them to think in terms on
> > > multi-user applications.
>
> At 12:40 AM 8/18/2001 -0600, Brad Pepers wrote:
> >I'm not a neophyte and just want to clarify that there *are* valid reason
> > to have autogenerated ids work this way.  No one is going to force me to
> > think otherwise though if any real arguments were offered I might be
> > convinced (but I've heard them before and so far I find them
> > unconvincing).  It seems to be a matter of short sightedness.
>
> OK, I'll bite.  The goal of generators was to create a very low overhead
> mechanism for creating unique identifiers.  The mechanism is very simple.
> The RDB$GENERATORS table holds a generator id which is the offset into
> an array of 32 or 64 bit integers - depending on the version of InterBase.
> The array is stored on pages in the database with the page type of pag_ids.
> To create a new value for a generator Firebird increments the array element
> at the offset specified for that generator by the amount specified in the
> gen_id function.  The new value is stored on the page in cache.  When a
> transaction that has created a new generator value commits, the generator
> page is written out.

I can see the goal of the current generator design and understand the 
benefits it has (simplicity, speed, and lack of deadlocks).  Its only real 
drawback is the consequences of a rollback and missing numbers in the 
sequence.  But sometimes the missing numbers are crucial and can't be allowed 
in which case an alternative way of getting numbers is needed.  There is a 
paper I read on how to do this using generators on IB/FB but in my opinion 
this is a very complicated way of doing things and its better to recongnize 
that generators just aren't the tool in this case.

> I can imagine two alternatives that would not leave gaps in the number
> space.  One is to lock the generator when it is updated and release it
> when the transaction that updated it commits or rolls back.  That could
> be done by adding a new lock type to the lock manager for locking
> generators. Locking generators would not add significantly to the
> processing overhead for the database but would reduce concurrency quite
> severely.  First, all transactions that use a single generator would be
> serialized.  Second, transactions that use more than one generator would be
> at risk of deadlocking on the generators.

The locking is the alternative I wouldn't mind seeing.  But as you say there 
is the serialization problem and the risk of deadlocks.  The serialization is 
not a problem in some designs and the deadlock can be worked around like any 
deadlock by maintaining a strict ordering to resource allocation.  The gain 
from using locks is that you could either include the id generation inside 
your transaction getting the benefit of consecutive numbers or you could 
generate the ids in a separate transaction that you then commit before using 
the numbers which would remove the serialization issues.

> The second alternative that occurs to me is to keep an internal accounting
> of the generator values acquired by a transaction and make the value
> available again if the transaction rolls back.  That algorithm is
> non-blocking -
> I think - but complicates the internal accounting considerably.  In
> addition to its "next" value, each generator would have an array of values
> available for reuse.  When the server shuts down, those arrays must be
> preserved, so they must be stored on disk.  More seriously, the arrays must
> be preserved if someone
> trips over the server's power cord.  As you know, Firebird does not recover
> from a log, but keeps the database consistent so it requires no recovery
> process.
> I don't see how to preserve the generator values that become available when
> the running transactions fail because the server crashed.

This would just be far too complicated.

> I have no doubt that systems built on different principles could have
> different behaviors, but I welcome any ideas that would allow Firebird to
> provide generator values that match users' expectations.

Its really not too bad right now since you can always just use an id table 
and a bit of SQL code to generate ids yourself.  This has the benefits of 
being portable to any database and giving sequential numbers.  Of course it 
has the problems of deadlocks and serialization you mentioned but depending 
on your application, these might not be issues worth worrying about.

My main problem is that anytime someone mentions wanting sequential numbers 
they are told they are doing something wrong and they should use generators 
and triggers instead which I think is not always the best solution (for one 
thing its completely IB/FB specific which is something I've tried to stay 
away from).

Another thought on this is that it would be nice to have the fairly standard 
feature I've seen on many databases which is an automatic id domain for 
primary key columns.  This is something I think just about every database 
I've used recently supported but not IB/FB.  Again the suggestion on the list 
is to use before insert triggers and such which is all fine but why not just 
natively support such a common feature?  The tricky bit is just how to get 
back the number that was assigned automatically...

-- 
Brad Pepers
brad@...

> > >On Friday 17 August 2001 22:19, Claudio Valderrama C. wrote:
> > > > ...people want generators to rollback their change if the 
> transaction is
> > > > rolled back...
> >
> > At 12:40 AM 8/18/2001 -0600, Brad Pepers wrote:
> > > ...there *are* valid reason
> > > to have autogenerated ids work this way.
>
> >On August 27, 2001 12:49 pm, Ann W. Harrison wrote:
> > OK, I'll bite.  The goal of generators ...

At 01:06 AM 8/28/2001 -0600, Brad Pepers wrote:

>I can see the goal of the current generator design and understand the
>benefits ... sometimes the missing numbers are crucial

I wrote:

> > .. lock the generator when it is updated and release it
> > when the transaction that updated it commits or rolls back...

Brad wrote

>Serialization is
>not a problem in some designs and the deadlock can be worked around like any
>deadlock by maintaining a strict ordering to resource allocation.

That's an interesting idea.  What would you think of having an option
on generator declaration that caused the generator to be locked by
the transaction that updates it?  Generators declared that way would
serialize updates and could be involved in deadlocks, but they wouldn't
lose numbers.  Generators without the option would work as they now do.

>... an automatic id domain for
>primary key columns.  The suggestion on the list
>is to use before insert triggers ...  The tricky bit is just how to get
>back the number that was assigned automatically...

I'm not sure that an automatic id domain solves the problem of getting
the number back.  As I've mentioned here several times,
INSERT ... RETURNING <values> seems like a better solution.



Regards,

Ann