From postgresql@ultimeth.com Mon Feb  14 23:55:01 2011
Received: from maia.hub.org (maia-3.hub.org [200.46.204.243])
	by mail.postgresql.org (Postfix) with ESMTP id 68BF11336971
	for <pgsql-sql-postgresql.org@mail.postgresql.org>;
	Mon, 14 Feb 2011 19:55:01 -0400 (AST)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.243]) (amavisd-maia,
	port 10024) with ESMTP id 68424-04 for
	<pgsql-sql-postgresql.org@mail.postgresql.org>;
	Mon, 14 Feb 2011 23:54:53 +0000 (UTC)
X-Greylist: delayed 00:25:22.308979 by SQLgrey-1.7.6
Received: from mail.mailpen.net (mail.mailpen.net [209.59.217.159])
	by mail.postgresql.org (Postfix) with ESMTP id 8DBAC1337B79
	for <pgsql-sql@postgresql.org>; Mon, 14 Feb 2011 19:54:53 -0400 (AST)
Received: from [172.20.1.171] (pool-98-117-107-200.sttlwa.fios.verizon.net
	[98.117.107.200])
	(using TLSv1 with cipher DHE-RSA-AES256-SHA (256/256 bits))
	(No client certificate requested)
	by mail.mailpen.net (Postfix) with ESMTP id 55A6E114149;
	Mon, 14 Feb 2011 15:29:30 -0800 (PST)
Message-ID: <4D59BAD5.4090306@ultimeth.com>
Date: Mon, 14 Feb 2011 15:29:25 -0800
From: "Dean Gibson (DB Administrator)" <postgresql@ultimeth.com>
Organization: UltiMeth Systems
User-Agent: Thunderbird 2.0.0.24 (Windows/20100228)
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Subject: SQL gotcha
Content-Type: multipart/alternative;
	boundary="------------040705040503010304000200"
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-1.899 tagged_above=-5 required=5 tests=BAYES_00=-1.9,
	HTML_MESSAGE=0.001
X-Spam-Level: 
X-Archive-Number: 201102/62
X-Sequence-Number: 35138

This is a multi-part message in MIME format.
--------------040705040503010304000200
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit

As you all know, when you specify an alias for a table or column name, 
the keyword "AS" is usually optional.  I personally like to always 
include the "AS" keyword, if for no other reason than readability, and I 
presume that always using "AS" might catch some syntactic errors as well.

However, recently I ran across a statement where the optionality of the 
"AS" keyword caused me some grief:

    SELECT ... FROM aaa LEFFT JOIN bbb WHERE ...

Note the (unintentional) misspelling of the keyword "LEFT".  PostgreSQL 
took "LEFFT" to be an alias for table "aaa" and silently accepted the 
statement;  naturally I did not get the results I expected !!

If PostgreSQL had an option to always require the "AS", that would have 
caught this error.  Is there any interest in PostgreSQL having any 
"pedantic"-type options, like many C/C++ compilers do?

-- 
Mail to my list address MUST be sent via the mailing list.
All other mail to my list address will bounce.


--------------040705040503010304000200
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 8bit

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
</head>
<body bgcolor="#ffffff" text="#000000">
As you all know, when you specify an alias for a table or column name,
the keyword "AS" is usually optional.  I personally like to always
include the "AS" keyword, if for no other reason than readability, and
I presume that always using "AS" might catch some syntactic errors as
well.<br>
<br>
However, recently I ran across a statement where the optionality of the
"AS" keyword caused me some grief:<br>
<blockquote>SELECT ... FROM aaa LEFFT JOIN bbb WHERE ...<br>
</blockquote>
Note the (unintentional) misspelling of the keyword "LEFT".  PostgreSQL
took "LEFFT" to be an alias for table "aaa" and silently accepted the
statement;  naturally I did not get the results I expected !!<br>
<br>
If PostgreSQL had an option to always require the "AS", that would have
caught this error.  Is there any interest in PostgreSQL having any
"pedantic"-type options, like many C/C++ compilers do?<br>
<br>
<pre class="moz-signature" cols="72">-- 
Mail to my list address MUST be sent via the mailing list.
All other mail to my list address will bounce.</pre>
</body>
</html>

--------------040705040503010304000200--



From achill@matrix.gatewaynet.com Tue Feb  15 07:41:24 2011
Received: from maia.hub.org (maia-2.hub.org [200.46.204.251])
	by mail.postgresql.org (Postfix) with ESMTP id 37E9C133740B
	for <pgsql-sql-postgresql.org@mail.postgresql.org>;
	Tue, 15 Feb 2011 03:41:24 -0400 (AST)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia,
	port 10024) with ESMTP id 53298-03 for
	<pgsql-sql-postgresql.org@mail.postgresql.org>;
	Tue, 15 Feb 2011 07:41:16 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from smadev.internal.net (host3.dynacom.ondsl.gr [62.103.35.211])
	by mail.postgresql.org (Postfix) with ESMTP id 4EEAF133655C
	for <pgsql-sql@postgresql.org>; Tue, 15 Feb 2011 03:41:15 -0400 (AST)
Received: from smadev.internal.net (localhost.internal.net [127.0.0.1])
	by smadev.internal.net (8.14.2/8.14.2) with ESMTP id p1F7fD1K081990;
	Tue, 15 Feb 2011 09:41:13 +0200 (EET)
	(envelope-from achill@matrix.gatewaynet.com)
Received: from localhost (localhost [[UNIX: localhost]])
	by smadev.internal.net (8.14.2/8.14.2/Submit) id p1F7fCG9081989;
	Tue, 15 Feb 2011 09:41:12 +0200 (EET)
	(envelope-from achill@matrix.gatewaynet.com)
From: Achilleas Mantzios <achill@matrix.gatewaynet.com>
Organization: Dynacom Tankers Mgmt
To: pgsql-sql@postgresql.org
Subject: Re: SQL gotcha
Date: Tue, 15 Feb 2011 09:41:11 +0200
User-Agent: KMail/1.9.7
Cc: "Dean Gibson (DB Administrator)" <postgresql@ultimeth.com>
References: <4D59BAD5.4090306@ultimeth.com>
In-Reply-To: <4D59BAD5.4090306@ultimeth.com>
MIME-Version: 1.0
Content-Type: text/plain;
  charset="utf-8"
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline
Message-Id: <201102150941.12331.achill@matrix.gatewaynet.com>
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-0.451 tagged_above=-5 required=5 tests=BAYES_00=-1.9,
	RCVD_IN_BRBL_LASTEXT=1.449
X-Spam-Level: 
X-Archive-Number: 201102/63
X-Sequence-Number: 35139

=CE=A3=CF=84=CE=B9=CF=82 Tuesday 15 February 2011 01:29:25 =CE=BF/=CE=B7 De=
an Gibson (DB Administrator) =CE=AD=CE=B3=CF=81=CE=B1=CF=88=CE=B5:
> As you all know, when you specify an alias for a table or column name,=20
> the keyword "AS" is usually optional.  I personally like to always=20
> include the "AS" keyword, if for no other reason than readability, and I=
=20
> presume that always using "AS" might catch some syntactic errors as well.
>=20
> However, recently I ran across a statement where the optionality of the=20
> "AS" keyword caused me some grief:
>=20
>     SELECT ... FROM aaa LEFFT JOIN bbb WHERE ...
>=20
> Note the (unintentional) misspelling of the keyword "LEFT".  PostgreSQL=20
> took "LEFFT" to be an alias for table "aaa" and silently accepted the=20
> statement;  naturally I did not get the results I expected !!
>=20
> If PostgreSQL had an option to always require the "AS", that would have=20
> caught this error.  Is there any interest in PostgreSQL having any=20
> "pedantic"-type options, like many C/C++ compilers do?
>=20

Actually PostgreSQL seems to endorse the usage of "AS" more than the SQL st=
andard requires.
http://www.postgresql.org/docs/8.3/interactive/sql-select.html

"The AS Key Word

In the SQL standard, the optional key word AS is just noise and can be omit=
ted without affecting the meaning.=20
The PostgreSQL parser requires this key word when renaming output columns b=
ecause the type extensibility features=20
lead to parsing ambiguities without it. AS is optional in FROM items, howev=
er. "

=2D-=20
Achilleas Mantzios