Обсуждение: Re: SQL compliance, was Re: [HACKERS] follow-up on PC Week Labsbenchmark results

>> You had inquired earlier about "when we would support complete SQL92"
>> (give or take a few words). What areas of entry level SQL92 are we
>> missing in your opinion (or should we wait for the article)?
> Well, what I look for on the language side is complete SQL-92 entry level
> compliance, plus common language extensions like outer joins, cast, case,
> cube, rollup, a datetime data type, add table constraint and alter table.
> Also, I look for a stored procedure language. Basically, parity with the
> commercial databases. :)

I've since seen the article in the latest issue of PCWeek. The article
was not at all clear on the *specific* features which would disqualify
Postgres from having SQL92 entry level compliance (for most commercial
RDBMSes this is the only level they attain), and I was amused to note
that although InterBase was lauded for SQL92 compliance, the author
did encourage them to consider supporting the SQL92 comment delimiter
("--") in their next release :))

Since InterBase has not been released as Open Source, and since we
will have a 7.0 release *before* Inprise does try the Open Source
thing, it would be nice to have those things happen before annointing
it as the "one true Open Source RDBMS" (tm). But frankly PCWeek has
been far more aggressively clueless in the past, and all in all they
are coming much closer to a balanced view of the world over the last
few months.

It's nice seeing Postgres mentioned at all (though in this article
none of the titles or subtitles mentioned us; you had to look at the
content), and we've still got a long way to go to completely overcome
the FUD-based criticisms of Open Source which were more clearly
apparent in PCWeek until very recently.
                    - Thomas

-- 
Thomas Lockhart                lockhart@alumni.caltech.edu
South Pasadena, California

Thomas Lockhart wrote:
> 
> I've since seen the article in the latest issue of PCWeek. The article
> was not at all clear on the *specific* features which would disqualify
> Postgres from having SQL92 entry level compliance (for most commercial
> RDBMSes this is the only level they attain), and I was amused to note
> that although InterBase was lauded for SQL92 compliance, the author
> did encourage them to consider supporting the SQL92 comment delimiter
> ("--") in their next release :))

Why does PostgreSQL _not_ support the -- comment delimiter ?

Is there something complicated to supporting it in parser ?

IMNSHO it would require only a few lines in gram.y

Does supporting user-defined operators interfere ?

I assume we could comfortably disallow -- as a possible operator (one 
can't input it from interactive psql anyway)

--------------
Hannu

Hannu Krosing <hannu@tm.ee> writes:
> Thomas Lockhart wrote:
>> ... although InterBase was lauded for SQL92 compliance, the author
>> did encourage them to consider supporting the SQL92 comment delimiter
>> ("--") in their next release :))

> Why does PostgreSQL _not_ support the -- comment delimiter ?

Better read it again, Hannu ... wasn't us that was being spoken of ...
        regards, tom lane

I believe it is Interbase that does not support the -- comment.

> Thomas Lockhart wrote:
> > 
> > I've since seen the article in the latest issue of PCWeek. The article
> > was not at all clear on the *specific* features which would disqualify
> > Postgres from having SQL92 entry level compliance (for most commercial
> > RDBMSes this is the only level they attain), and I was amused to note
> > that although InterBase was lauded for SQL92 compliance, the author
> > did encourage them to consider supporting the SQL92 comment delimiter
> > ("--") in their next release :))
> 
> Why does PostgreSQL _not_ support the -- comment delimiter ?
> 
> Is there something complicated to supporting it in parser ?
> 
> IMNSHO it would require only a few lines in gram.y
> 
> Does supporting user-defined operators interfere ?
> 
> I assume we could comfortably disallow -- as a possible operator (one 
> can't input it from interactive psql anyway)
> 
> --------------
> Hannu
> 
> ************
> 


--  Bruce Momjian                        |  http://www.op.net/~candle pgman@candle.pha.pa.us               |  (610)
853-3000+  If your life is a hard drive,     |  830 Blythe Avenue +  Christ can be your backup.        |  Drexel Hill,
Pennsylvania19026
 

Tom Lane wrote:
> 
> Hannu Krosing <hannu@tm.ee> writes:
> > Thomas Lockhart wrote:
> >> ... although InterBase was lauded for SQL92 compliance, the author
> >> did encourage them to consider supporting the SQL92 comment delimiter
> >> ("--") in their next release :))
> 
> > Why does PostgreSQL _not_ support the -- comment delimiter ?
> 
> Better read it again, Hannu ... wasn't us that was being spoken of ...

I got the impression from the paragraph that followed that we don't

and the first query I tried bounced from commandline

[hannu@hu hannu]$ psql -c "select count(*) from t1 -- what"
ERROR:  parser: parse error at or near "-"

but worked interactively:

[hannu@hu TeleHansaPlus]$ psql
Welcome to the POSTGRESQL interactive sql monitor: Please read the file COPYRIGHT for copyright terms of POSTGRESQL
[PostgreSQL 6.5.2 on i586-pc-linux-gnu, compiled by gcc egcs-2.91.66]
  type \? for help on slash commands  type \q to quit  type \g or terminate with semicolon to execute queryYou are
currentlyconnected to the database: hannu
 

hannu=> select count(*) from t1 -- what
hannu-> ;
count
-----   3
(1 row)

and failed also when used from python

[hannu@hu TeleHansaPlus]$ python
Python 1.5.2 (#1, Apr 18 1999, 16:03:16)  [GCC pgcc-2.91.60 19981201
(egcs-1.1.1  on linux2
Copyright 1991-1995 Stichting Mathematisch Centrum, Amsterdam
>>> import pg
>>> con=pg.connect('hannu')
>>> con.query("select count(*) from t1 -- what")
Traceback (innermost last): File "<stdin>", line 1, in ?
pg.error: ERROR:  parser: parse error at or near "-"


So assumed it was handled in psql when in interactive mode.

------------
Hannu

> Tom Lane wrote:
> > 
> > Hannu Krosing <hannu@tm.ee> writes:
> > > Thomas Lockhart wrote:
> > >> ... although InterBase was lauded for SQL92 compliance, the author
> > >> did encourage them to consider supporting the SQL92 comment delimiter
> > >> ("--") in their next release :))
> > 
> > > Why does PostgreSQL _not_ support the -- comment delimiter ?
> > 
> > Better read it again, Hannu ... wasn't us that was being spoken of ...
> 
> I got the impression from the paragraph that followed that we don't
> 
> and the first query I tried bounced from commandline
> 
> [hannu@hu hannu]$ psql -c "select count(*) from t1 -- what"
> ERROR:  parser: parse error at or near "-"

Worked here:
$ sql -c "select count(*) from t1 -- what" testERROR:  Relation 't1' does not exist


--  Bruce Momjian                        |  http://www.op.net/~candle pgman@candle.pha.pa.us               |  (610)
853-3000+  If your life is a hard drive,     |  830 Blythe Avenue +  Christ can be your backup.        |  Drexel Hill,
Pennsylvania19026
 

> I got the impression from the paragraph that followed that we don't
> and the first query I tried bounced from commandline
> So assumed it was handled in psql when in interactive mode.

Yuck. They *were* talking about InterBase, but you're right!

Didn't realize that scan.l had lost (or never did have) the right
stuff. Will be fixed before we're out of beta...
                   - Tom

-- 
Thomas Lockhart                lockhart@alumni.caltech.edu
South Pasadena, California

> > I got the impression from the paragraph that followed that we don't
> > and the first query I tried bounced from commandline
> > So assumed it was handled in psql when in interactive mode.
> 
> Yuck. They *were* talking about InterBase, but you're right!
> 
> Didn't realize that scan.l had lost (or never did have) the right
> stuff. Will be fixed before we're out of beta...

Can you give me a failure condition for the TODO list?  I can't see the
bug here.

--  Bruce Momjian                        |  http://www.op.net/~candle pgman@candle.pha.pa.us               |  (610)
853-3000+  If your life is a hard drive,     |  830 Blythe Avenue +  Christ can be your backup.        |  Drexel Hill,
Pennsylvania19026
 

> Can you give me a failure condition for the TODO list?  I can't see the
> bug here.

Well, now that I got off my duff and tried your little test with my
current sources, I get your result. Hannu??
                 - Thomas

-- 
Thomas Lockhart                lockhart@alumni.caltech.edu
South Pasadena, California

Thomas Lockhart <lockhart@alumni.caltech.edu> writes:
> Yuck. They *were* talking about InterBase, but you're right!

> Didn't realize that scan.l had lost (or never did have) the right
> stuff. Will be fixed before we're out of beta...

I've griped about these boundary conditions before, actually ---
although scan.l does the right thing most of the time with comments,
it has problems if a -- comment is terminated with \r instead of \n
(hence gripes from Windows users), and it also has problems if a --
comment is not terminated with \n before the end of the buffer.

There are some other cases where \r is not taken as equivalent
to \n, also.

Am testing a fix now.
        regards, tom lane

On 2000-02-17, Thomas Lockhart mentioned:

> I've since seen the article in the latest issue of PCWeek. The article
> was not at all clear on the *specific* features which would disqualify
> Postgres from having SQL92 entry level compliance

I dug through the standard to come up with a list. I probably missed some
things, but they would be more of a lexical nature. I think I covered all
language constructs (which is what people look at anyway). Some of these
things I never used, so I merely tested them by looking at the current
documentation and/or entering a simple example query. Also, this list
doesn't care whether an implemented feature contains bugs that would
actually disqualify it from complete compliance.


* TIME and TIMESTAMP WITH TIMEZONE missing  [6.1]

* Things such as SELECT MAX(ALL x) FROM y; don't work.  [6.5]
{This seems to be an easy grammar fix.}

* LIKE with ESCAPE clause missing  [8.5]
{Is on TODO.}

* SOME / ANY doesn't seem to exist  [8.7]

* Grant privileges have several deficiencies  [10.3, 11.36]

* Schemas  [11.1, 11.2]

* CREATE VIEW name (x, y, z) doesn't work  [11.19]

* There's a WITH CHECK OPTION clause for CREATE VIEW  [11.19]

* no OPEN statement  [13.2]

* FETCH syntax has a few issues  [13.3]

* SELECT x INTO a, b, c table  [13.5]

* DELETE WHERE CURRENT OF  [13.6]

* INSERT INTO table DEFAULT VALUES  [13.8]
{Looks like a grammar fix as well.}

* UPDATE WHERE CURRENT OF  [13.9]

* no SQLSTATE, SQLCODE  [22.1, 22.2]
{Not sure about that one, since the sections don't contain leveling
information.}

* default transaction isolation level is SERIALIZABLE
{Why isn't ours?}

* no autocommit in SQL

* modules?  [12]

* Some type conversion problems. For example a DECIMAL field should not
dump out as NUMERIC, and a FLOAT(x) field should be stored as such.

[* Haven't looked at Embedded SQL.]


That's it. :)

-- 
Peter Eisentraut                  Sernanders väg 10:115
peter_e@gmx.net                   75262 Uppsala
http://yi.org/peter-e/            Sweden

Peter Eisentraut <peter_e@gmx.net> writes:
> * Things such as SELECT MAX(ALL x) FROM y; don't work.  [6.5]
> {This seems to be an easy grammar fix.}

Yes, and since ALL is already a reserved word, it wouldn't break
anything to accept it.  I'll try to take care of that today.
None of the other stuff is quite as easy to fix :-(


> * INSERT INTO table DEFAULT VALUES  [13.8]
> {Looks like a grammar fix as well.}

Huh?  We do have DEFAULT VALUES --- what is wrong exactly?

What we don't seem to have is full <table value constructor> per 7.2;
we only allow VALUES ... in INSERT, whereas SQL allows it in other
constructs where a sub-SELECT would be legal, and we don't accept
multiple rows in VALUES.  For example, you should be able to write
INSERT INTO t VALUES (1,2,3), (4,5,6), (7,8,9), ...

but we don't accept that now.  The spec also shows several examples like
     CONSTRAINT DOMAIN_CONSTRAINTS_CHECK_DEFERRABLE       CHECK ( ( IS_DEFERRABLE, INITIALLY_DEFERRED ) IN
( VALUES ( 'NO',  'NO' ),                        ( 'YES', 'NO' ),                        ( 'YES', 'YES' ) ) )
 


Thanks for digging through the spec ... I bet that was tedious ...
        regards, tom lane

On 2000-02-19, Tom Lane mentioned:

> > * INSERT INTO table DEFAULT VALUES  [13.8]
> > {Looks like a grammar fix as well.}
> 
> Huh?  We do have DEFAULT VALUES --- what is wrong exactly?

Not documented. ;)

-- 
Peter Eisentraut                  Sernanders väg 10:115
peter_e@gmx.net                   75262 Uppsala
http://yi.org/peter-e/            Sweden

On 2000-02-19, Tom Lane mentioned:

> What we don't seem to have is full <table value constructor> per 7.2;
> we only allow VALUES ... in INSERT, whereas SQL allows it in other
> constructs where a sub-SELECT would be legal,

Not required by Intermediate Level.

> and we don't accept
> multiple rows in VALUES.  For example, you should be able to write
> 
>     INSERT INTO t VALUES (1,2,3), (4,5,6), (7,8,9), ...
> 
> but we don't accept that now.

Not required either.

-- 
Peter Eisentraut                  Sernanders väg 10:115
peter_e@gmx.net                   75262 Uppsala
http://yi.org/peter-e/            Sweden

Thomas Lockhart wrote:
> 
> > Can you give me a failure condition for the TODO list?  I can't see the
> > bug here.
> 
> Well, now that I got off my duff and tried your little test with my
> current sources, I get your result. Hannu??

My tests were with 6.5.3 which has even more yuckies in it :

[hannu@hu hannu]$ psql -c "select -- what ?                    
> count(*) from t1;"
ERROR:  attribute 'what' not found
[hannu@hu hannu]$ psql -c "select -- what  
> count(*) from t1;"
ERROR:  parser: parse error at or near "count"
[hannu@hu hannu]$ psql -c "select count(*) -- what
> from t1;"
count
-----   3
(1 row)


But they all work from psql

hannu=> select -- what ?
hannu-> count(*) from t1;
count
-----   3
(1 row)

hannu=> select count(*) -- what ?
hannu-> from t1;
count
-----   3
(1 row)


Could you try them on current.

-------------
Hannu

Thomas Lockhart wrote:
> 
> > Can you give me a failure condition for the TODO list?  I can't see the
> > bug here.
> 
> Well, now that I got off my duff and tried your little test with my
> current sources, I get your result. Hannu??

Btw, how did you try them ?

Could it be that psql is now stripping comments even in non-interactive 
mode (when using  -c or redirected stdin)?

Could you test with some other frontend (python, perl, tcl, C) ?

------------
Hannu

Peter Eisentraut <peter_e@gmx.net> writes:
> On 2000-02-19, Tom Lane mentioned:
>> What we don't seem to have is full <table value constructor> per 7.2;
>> we only allow VALUES ... in INSERT, whereas SQL allows it in other
>> constructs where a sub-SELECT would be legal,

> Not required by Intermediate Level.

No, but it's useful enough that we should have it...
        regards, tom lane

Hannu Krosing <hannu@tm.ee> writes:
> Could you test with some other frontend (python, perl, tcl, C) ?

Yup, psql is untrustworthy as a means of testing the backend's comment
handling ;-).

I committed lexer changes on Friday evening that I believe fix all of
the backend's problems with \r versus \n.  The issue with unterminated
-- comments, which was Hannu's original complaint, was fixed awhile ago;
but we still had problems with comments terminated with \r instead of
\n, as well as some non-SQL-compliant behavior for -- comments between
the segments of a multiline literal, etc etc.

While fixing this I realized that there are some fundamental
discrepancies between the way the backend recognizes comments and the
way that psql does.  These arise from the fact that the comment
introducer sequences /* and -- are also legal as parts of operator
names, and since the backend is based on lex which uses greedy longest-
available-match rules, you get things like this:

select *-- 123
ERROR:  Can't find left op '*--' for type 23

(Parsing '*--' as an operator name wins over parsing just '*' as an
operator name, so that '--' would be recognized on the next call.)
More subtly,

select /**/- 22
ERROR:  parser: parse error at or near ""

which is the backend's rather lame excuse for an "unterminated comment"
error.  What happens here is that the sequence /**/- is bit off as a
single lexer token, then tested in this order to see if it is(a) a complete "/* ... */" comment (nope),(b) the start of
acomment, "/* anything" (yup), or(c) an operator (which would succeed if it got the chance).
 
There does not seem to be any way to persuade lex to stop at the "*/"
if it has a chance to recognize a longer token by applying the operator
rule.

Both of these problems are easily avoided by inserting some whitespace,
but I wonder whether we ought to try to fix them for real.  One way
that this could be done would be to alter the lexer rules so that
operators are lexed a single character at a time, which'd eliminate
lex's tendency to recognize a long operator name in place of a comment.
Then we'd need a post-pass to recombine adjacent operator characters into
a single token.  (This would forever prevent anyone from using operator
names that include '--' or '/*', but I'm not sure that's a bad thing.)
The post-pass would also be a mighty convenient place to fix the NOT NULL
problem that's giving us trouble in another thread: the post-pass would
need one-token lookahead anyway, so it could very easily convert NOT
followed by NULL into a single special token.

Meanwhile, psql is using some ad-hoc code to recognize comments,
rather than a lexer, and it thinks both of these sequences are indeed
comments.  I also find that it strips out the -- flavor of comment,
but sends the /* */ flavor on through, which is just plain inconsistent.
I suggest we change psql to not strip -- comments either.  The only
reason for psql to be in the comment-recognition business at all is
so that it can determine whether a semicolon is end-of-query or just
a character in a comment.

Another thing I'd like to fix here is to get the backend to produce
a more useful error message than 'parse error at or near ""' when it's
presented with an unterminated comment or unterminated literal.
The flex manual recommends coding like
    <quote><<EOF>>   {             error( "unterminated quote" );             yyterminate();             }

but <<EOF>> is a flex-ism not supported by regular lex.  We already
tell people they have to use flex (though I'm not sure that's *really*
necessary at present); do we want to set that requirement in stone?
Or does anyone know another way to get this effect?
        regards, tom lane

On 2000-02-20, Tom Lane mentioned:

> select *-- 123
> ERROR:  Can't find left op '*--' for type 23

> select /**/- 22
> ERROR:  parser: parse error at or near ""

I believe that these things (certainly the first one) could be fixed by
making the {operator} rule in scan.l rescanning yytext for "--" or "/*"
(using string functions) and if found putting part of the token back in
the input stream using yyless().

> Meanwhile, psql is using some ad-hoc code to recognize comments,
> rather than a lexer, and it thinks both of these sequences are indeed
> comments.

Incidentally, it's right. ;)

> I suggest we change psql to not strip -- comments either.

That sounds reasonable, although we had a painful discussion about this
last fall, I recall, that ended with me leaving it like that. If someone
wants to bother, be my guest. One of these days, psql should get a lexer
to fix some other parsing problems as well.

> but <<EOF>> is a flex-ism not supported by regular lex.

Exclusive start conditions are not supported by regular lex either. We
lose. Sometimes I think we're actually doing people a favour by requiring
flex, because then they don't have to deal with incarnations like Sun's.

If you want to catch unbalanced quotes at the end of input, I could
imagine that some grand unified evilness via yywrap setting some global
flag or two might get the job done.


-- 
Peter Eisentraut                  Sernanders väg 10:115
peter_e@gmx.net                   75262 Uppsala
http://yi.org/peter-e/            Sweden

Peter Eisentraut <peter_e@gmx.net> writes:
> On 2000-02-20, Tom Lane mentioned:
>> select *-- 123
>> ERROR:  Can't find left op '*--' for type 23

> I believe that these things (certainly the first one) could be fixed by
> making the {operator} rule in scan.l rescanning yytext for "--" or "/*"
> (using string functions) and if found putting part of the token back in
> the input stream using yyless().

I think you are right, that would work.  Is yyless flex-specific or a
generic lex feature?

The intermediate-lookahead-buffer solution might still be better, if it
lets us solve more problems than just this one.  I'm inclined to not
do anything until Thomas decides what he wants to do about the NOT NULL
business.

>> but <<EOF>> is a flex-ism not supported by regular lex.

> Exclusive start conditions are not supported by regular lex either.

Oooh, somehow I managed to completely miss that statement in the flex
manual, but you are right.  Hmm.  I think that shoots a gaping hole in
my desire to have scan.l work with plain lex.  Offhand I don't see a
good way to avoid using exclusive start conditions for multi-section
literals.

> If you want to catch unbalanced quotes at the end of input, I could
> imagine that some grand unified evilness via yywrap setting some global
> flag or two might get the job done.

Right at the moment I'm thinking we might as well use <<EOF>>, which
is after all the recommended way of doing it.
        regards, tom lane

> > I've since seen the article in the latest issue of PCWeek. The article
> > was not at all clear on the *specific* features which would disqualify
> > Postgres from having SQL92 entry level compliance
> I dug through the standard to come up with a list [ of missing features ].
> * TIME and TIMESTAMP WITH TIMEZONE missing  [6.1]

TIMESTAMP WITH TIME ZONE is already available (and was for v6.5.x
too). I'll add syntax to allow TIME WITH TIME ZONE for v7.0.

> * SOME / ANY doesn't seem to exist  [8.7]
> 
> * Grant privileges have several deficiencies  [10.3, 11.36]
> 
> * Schemas  [11.1, 11.2]
> 
> * CREATE VIEW name (x, y, z) doesn't work  [11.19]
> 
> * There's a WITH CHECK OPTION clause for CREATE VIEW  [11.19]
> 
> * no OPEN statement  [13.2]
> 
> * FETCH syntax has a few issues  [13.3]
> 
> * SELECT x INTO a, b, c table  [13.5]
> 
> * DELETE WHERE CURRENT OF  [13.6]
> 
> * INSERT INTO table DEFAULT VALUES  [13.8]
> {Looks like a grammar fix as well.}
> 
> * UPDATE WHERE CURRENT OF  [13.9]
> 
> * no SQLSTATE, SQLCODE  [22.1, 22.2]
> {Not sure about that one, since the sections don't contain leveling
> information.}
> 
> * default transaction isolation level is SERIALIZABLE
> {Why isn't ours?}
> 
> * no autocommit in SQL
> 
> * modules?  [12]
> 
> * Some type conversion problems. For example a DECIMAL field should not
> dump out as NUMERIC, and a FLOAT(x) field should be stored as such.
> 
> [* Haven't looked at Embedded SQL.]
> 
> That's it. :)
> 
> --
> Peter Eisentraut                  Sernanders väg 10:115
> peter_e@gmx.net                   75262 Uppsala
> http://yi.org/peter-e/            Sweden

-- 
Thomas Lockhart                lockhart@alumni.caltech.edu
South Pasadena, California