Thread: BUG #5732: parsing of: "WHERE mycol=123AND ..."
The following bug has been logged online: Bug reference: 5732 Logged by: Josh Kupershmidt Email address: schmiddy@gmail.com PostgreSQL version: 8.3 and HEAD Operating system: Linux and OS X Description: parsing of: "WHERE mycol=123AND ..." Details: I noticed that Postgres in many cases will happily tokenize WHERE clauses having no space between a condition and "AND" or "OR". For example: CREATE TABLE mytab (mycol int); INSERT INTO mytab (mycol) VALUES (1), (2); SELECT * FROM mytab WHERE mycol = 1AND true; SELECT * FROM mytab WHERE mycol = 2OR true; although some cases produce an error, as I would expect, such as: SELECT * FROM mytab WHERE mycol = 2::intOR true; I think it would be more consistent to raise syntax errors in all these cases. Josh
"Josh Kupershmidt" <schmiddy@gmail.com> writes:
> I noticed that Postgres in many cases will happily tokenize WHERE clauses
> having no space between a condition and "AND" or "OR".
This has nothing to do with AND or OR. Any situation where you have
some digits followed by something that can't be part of a number will
be lexed as two separate tokens. I'm not sure we could change that,
because it's a pretty basic aspect of a flex lexer; and even if we
could I'm not eager to face the wrath of all the users whose queries
would break. Leaving out "unnecessary" spaces is a pretty common habit.
regards, tom lane
On Thu, 2010-10-28 at 23:46 +0000, Josh Kupershmidt wrote:
> SELECT * FROM mytab WHERE mycol = 2OR true;
Is that inconsistent with the standard?
Other languages seem to allow similar things, such as ruby and perl. For
instance, in ruby:
puts 1if(true)
seems to be acceptable.
> although some cases produce an error, as I would expect, such as:
> SELECT * FROM mytab WHERE mycol = 2::intOR true;
That's not the same. In that example, there's no hope of distinguishing
the identifier "int" from the keyword "OR".
> I think it would be more consistent to raise syntax errors in all these
> cases.
I don't really see a "bug" here. Is this causing you some kind of
problem?
Regards,
Jeff Davis
Jeff Davis <pgsql@j-davis.com> writes:
> On Thu, 2010-10-28 at 23:46 +0000, Josh Kupershmidt wrote:
>> SELECT * FROM mytab WHERE mycol = 2OR true;
> Is that inconsistent with the standard?
I was just looking at that. The spec lumps both <unsigned numeric
literal> and <keyword> under <nondelimiter token>, and says that there
must be a <separator> (ie, whitespace or comment) between adjacent
<nondelimiter token>s. However, I would tend to read that as
instructing users how to write portable SQL, not as instructing
implementations that they must throw an error when they find two tokens
that aren't separated by whitespace. The actual behavior of an
implementation in such a case could be regarded as a spec extension.
I experimented a bit with mysql's behavior, and it seems that (at least
in 5.1.51) what they do is treat "1and" or "2or" as if it were an
identifier. They're definitely not throwing an error, at least not on
that token --- they will of course spit up later if the remainder of
the input is inconsistent with the assumption that that part is an
identifier. I don't have any other SQL DBMSes handy to experiment with,
but I wouldn't be surprised to find multiple behaviors out there.
regards, tom lane
On Thu, Oct 28, 2010 at 8:01 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > "Josh Kupershmidt" <schmiddy@gmail.com> writes: >> I noticed that Postgres in many cases will happily tokenize WHERE clauses >> having no space between a condition and "AND" or "OR". > > This has nothing to do with AND or OR. =A0Any situation where you have > some digits followed by something that can't be part of a number will > be lexed as two separate tokens. Yeah, I hadn't tried to pinpoint how widespread this feature/bug is in the syntax. Though note, you can see this with e.g. text columns as well, such as in: SELECT * FROM mytab WHERE mycol =3D 'abc def'AND true; Josh
On Thu, Oct 28, 2010 at 8:03 PM, Jeff Davis <pgsql@j-davis.com> wrote: > I don't really see a "bug" here. Is this causing you some kind of > problem? I happened to notice it while fixing up some code using multi-line strings which had forgotten to put spaces in the SQL across lines. I was just surprised Postgres didn't throw an error. The only mild concern I have is if this could possibly lead to ambiguous parsing in some situations, though I've played with some examples and I haven't seen any yet. It would be nice to have this behavior documented somewhere though. Josh
Josh Kupershmidt <schmiddy@gmail.com> writes:
> The only mild concern I have is if this could possibly lead to
> ambiguous parsing in some situations, though I've played with some
> examples and I haven't seen any yet. It would be nice to have this
> behavior documented somewhere though.
The fine manual currently says (at the head of section 4.1):
A token can be a key word, an identifier, a quoted identifier, a literal
(or constant), or a special character symbol. Tokens are normally
separated by whitespace (space, tab, newline), but need not be if there
is no ambiguity (which is generally only the case if a special character
is adjacent to some other token type).
The parenthetical remark at the end fails to point out the special case
of number-followed-by-identifier-that-doesn't-look-like-an-exponent.
But I'm not sure that it's reasonable to try to shoehorn in a mention
of the case. Might be a good idea to change "generally" to "usually",
though, since "generally" might be read as implying that that's the
exact and only rule.
regards, tom lane
On Thu, Oct 28, 2010 at 5:20 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > I experimented a bit with mysql's behavior, and it seems that (at least > in 5.1.51) what they do is treat "1and" or "2or" as if it were an > identifier. =A0They're definitely not throwing an error, at least not on > I guess the eleant question is what the lexical elements section of the standard says about identifiers. It pretty clearly declares that they can't start with digits: <identifier body> ::=3D <identifier start> [ <identifier part>... ] <identifier part> ::=3D <identifier start> | <identifier extend> <identifier start> ::=3D !! See the Syntax Rules <identifier extend> ::=3D !! See the Syntax Rules 1) An <identifier start> is any character in the Unicode General Category classes =93Lu=94, =93Ll=94, =93Lt=94, =93Lm=94, =93Lo=94, or =93Nl= =94. NOTE 70 =97 The Unicode General Category classes =93Lu=94, =93Ll=94, =93Lt= =94, =93Lm=94, =93Lo=94, and =93Nl=94 are assigned to Unicode characters that are, respectively, upper-case letters, lower-case letters, title-case letters, modifier letters, other letters, and letter numbers. 2) An <identifier extend> is U+00B7, =93Middle Dot=94, or any character in the Unicode General Category classes =93Mn=94, =93Mc=94, =93Nd=94, =93Pc=94= , or =93Cf=94. NOTE 71 =97 The Unicode General Category classes =93Mn=94, =93Mc=94, =93Nd= =94, =93Pc=94, and =93Cf=94 are assigned to Unicode characters that are, respectively, nonspacing marks, spacing combining marks, decimal numbers, connector punctuations, and formatting codes. --=20 greg
Greg Stark <gsstark@mit.edu> writes:
> On Thu, Oct 28, 2010 at 5:20 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> I experimented a bit with mysql's behavior, and it seems that (at least
>> in 5.1.51) what they do is treat "1and" or "2or" as if it were an
>> identifier. They're definitely not throwing an error, at least not on
> I guess the eleant question is what the lexical elements section of
> the standard says about identifiers. It pretty clearly declares that
> they can't start with digits:
Yeah. The key point IMO is that this *input* is not spec-compliant.
So implementations can either throw an error, or define their own
spec extension as to how to interpret it. I find mysql's behavior
interesting mostly because it shows that throwing an error isn't
necessarily common practice. Anybody want to try Oracle, DB2, etc?
regards, tom lane
MS SQL server 2008 has no problem with this: select * from client where CLIENT_ID = 12AND SNAME='Smith' Returns the expected row. PostgreSQL 9.0 has no problem with it either, again throwing no error and returning the expected result. Regards, Gary. On 30/10/2010 7:23 PM, Tom Lane wrote: > Greg Stark<gsstark@mit.edu> writes: >> On Thu, Oct 28, 2010 at 5:20 PM, Tom Lane<tgl@sss.pgh.pa.us> wrote: >>> I experimented a bit with mysql's behavior, and it seems that (at least >>> in 5.1.51) what they do is treat "1and" or "2or" as if it were an >>> identifier. They're definitely not throwing an error, at least not on >> I guess the eleant question is what the lexical elements section of >> the standard says about identifiers. It pretty clearly declares that >> they can't start with digits: > Yeah. The key point IMO is that this *input* is not spec-compliant. > So implementations can either throw an error, or define their own > spec extension as to how to interpret it. I find mysql's behavior > interesting mostly because it shows that throwing an error isn't > necessarily common practice. Anybody want to try Oracle, DB2, etc? > > regards, tom lane >
On Sat, Oct 30, 2010 at 11:23 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> Anybody want to try Oracle, DB2, etc?
Oracle seems to behave like us:
SQL> select * from (select 1 as x from dual) where 1=1and x=1;
X
----------
1
--
greg