From: Bruce Momjian <pgman@candle.pha.pa.us>
Message-Id: <200208260031.g7Q0VCC24924@candle.pha.pa.us>
Subject: Re: LIMIT 1 FOR UPDATE or FOR UPDATE LIMIT 1?
In-Reply-To: <20020405160833.A917@ford.rockstorm.se>
To: magnus.enbom@rockstorm.se
Date: Sun, 25 Aug 2002 20:31:12 -0400 (EDT)
Cc: pgsql-sql@postgresql.org
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary=ELM1030321872-22850-0_
Content-Transfer-Encoding: 7bit

--ELM1030321872-22850-0_
Content-Transfer-Encoding: 7bit
Content-Type: text/plain; charset=US-ASCII


I found this email from April.  It properly points out that our
LIMIT/FOR UPDATE ordering doesn't match MySQL's, and MySQL's looks more
correct, specifically that the FOR UPDATE is after the LIMIT.  Our
grammar is:

       | select_clause sort_clause opt_for_update_clause opt_select_limit

How do we want to deal with this?  I tried allowing both orderings with
the attached patch but got:

	bison -y -d  gram.y
	conflicts:  4 shift/reduce, 5 reduce/reduce

---------------------------------------------------------------------------

Magnus Enbom wrote:
> Hi,
> 
> I've just been hit by a small but annoying difference between postgres(7.2)
> and mysql(4.x).
> In postgresql you do:
> 
> SELECT * FROM table FOR UPDATE LIMIT 1;
> 
> and in mysql you do:
> 
> SELECT * FROM table LIMIT 1 FOR UPDATE;
> 
> Is it possible for postgres to accept the mysql syntax as well?
> It's not that many databases that implement LIMIT, so it would be nice if the
> ones that do have the same syntax(or can accept each others variants).
> 
> -- Magnus
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
> 

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

--ELM1030321872-22850-0_
Content-Transfer-Encoding: 7bit
Content-Type: text/plain
Content-Disposition: inline; filename="/bjm/diff"

Index: gram.y
===================================================================
RCS file: /cvsroot/pgsql-server/src/backend/parser/gram.y,v
retrieving revision 2.360
diff -c -r2.360 gram.y
*** gram.y	19 Aug 2002 15:08:47 -0000	2.360
--- gram.y	26 Aug 2002 00:29:24 -0000
***************
*** 4114,4123 ****
--- 4114,4135 ----
  										nth(0, $4), nth(1, $4));
  					$$ = $1;
  				}
+ 			| select_clause sort_clause opt_select_limit opt_for_update_clause
+ 				{
+ 					insertSelectOptions((SelectStmt *) $1, $2, $4,
+ 										nth(0, $3), nth(1, $3));
+ 					$$ = $1;
+ 				}
  			| select_clause for_update_clause opt_select_limit
  				{
  					insertSelectOptions((SelectStmt *) $1, NIL, $2,
  										nth(0, $3), nth(1, $3));
+ 					$$ = $1;
+ 				}
+ 			| select_clause opt_select_limit for_update_clause
+ 				{
+ 					insertSelectOptions((SelectStmt *) $1, NIL, $3,
+ 										nth(0, $2), nth(1, $2));
  					$$ = $1;
  				}
  			| select_clause select_limit

--ELM1030321872-22850-0_--