Re: [QUESTIONS] Practical SQL Handbook - demo script for postgreSQL

The Hermit Hacker <scrappy@hub.org> writes:

Moved to pgsql-hackers@postgresql.org (where the developers hang out)

The NULL contraint: PostgreSQL only allows NOT NULL (NULL being the
default). I altered the backend grammar for this one.

Patch?

OK. The patch to gram.y is almost certainly wrong: it's just a hack
to get NULL acceptable---it should surely go in the same place as the
check for NOT NULL.

The floating point literal change is probably right, but it may break
things (it may well cause more things to be regarded as floats than
should be). Again, somebody who knows about this stuff definitely
needs to check.

I hope this helps all the same.

*** /mnt/1gig2/postgres/make/pgsql/src/backend/parser/gram.y	Fri Apr 17 05:12:56 1998
--- gram.y	Mon Apr 20 22:59:01 1998
***************
*** 735,740 ****
--- 735,741 ----
  		;

ColQualifier: ColQualList { $$ = $1; }
+ | NULL_P { $$ = NULL; }
| /*EMPTY*/ { $$ = NULL; }
;

*** /mnt/1gig2/postgres/make/pgsql/src/backend/parser/scan.l	Wed Apr  8 07:35:00 1998
--- scan.l	Mon Apr 20 23:22:16 1998
***************
*** 153,159 ****
  xmstop			-

integer -?{digit}+
! real -?{digit}+\.{digit}+([Ee][-+]?{digit}+)?

param \${integer}

--- 153,159 ----
  xmstop			-

integer -?{digit}+
! real -?{digit}*\.{digit}+([Ee][-+]?{digit}+)?

param \${integer}

The NULL contraint: PostgreSQL only allows NOT NULL (NULL being
the default). I altered the backend grammar for this one.

Patch?

OK. The patch to gram.y is almost certainly wrong: it's just a hack
to get NULL acceptable---it should surely go in the same place as the
check for NOT NULL.

Yes, and no. Putting the grammar where you did disallows any other
clauses, such as DEFAULT or CONSTRAINT, in the declaration. Trying to
put it in the proper place results in shift/reduce conflicts, since it
is ambiguous with other allowed syntax.

btw, afaik this is not SQL92 anyway...

The floating point literal change is probably right, but it may break
things (it may well cause more things to be regarded as floats than
should be). Again, somebody who knows about this stuff definitely
needs to check.

I hope this helps all the same.

Yes it does! I've got a more general floating patch to apply, but would
not have done it without your prompting. Discussion and proposals are
how we progress. Good work.

Don't know how or if we want to proceed with a bare "NULL" clause.
Should we bother with a special case of _only_ NULL in a declaration, as
in Bruce's patch?

- Tom

"Thomas G. Lockhart" <lockhart@alumni.caltech.edu> writes:

Don't know how or if we want to proceed with a bare "NULL" clause.
Should we bother with a special case of _only_ NULL in a
declaration, as in Bruce's patch?

My patch is clearly wrong. The NULL should be parallel to NOT NULL,
and ought just to be ignored (since NULL is the default). I think
it's worth doing (as the book says, NULL may not be the default on
your system, and anyway, it's always better to specify just for
clarity).

I think explicitly specifying NULL is probably good practice, so it
should be supported.

Don't know how or if we want to proceed with a bare "NULL" clause.
Should we bother with a special case of _only_ NULL in a
declaration, as in Bruce's patch?

My patch is clearly wrong. The NULL should be parallel to NOT NULL,
and ought just to be ignored (since NULL is the default). I think
it's worth doing (as the book says, NULL may not be the default on
your system, and anyway, it's always better to specify just for
clarity).
I think explicitly specifying NULL is probably good practice, so it
should be supported.

Maybe (SQL92 is full of inconsistant/non-symmetric features), but you
will need to figure out how to do it without shift/reduce conflicts in
the grammar. The fact that they are there means that either it is
impossible to unambiguously parse the allowed syntax, or that the
grammar definition in the yacc language needs to be restructured a bit.
It isn't obvious to me how to restructure for this case; I've fixed this
kind of problem in other parts of the grammar and the tricks I used
there don't look usable here.

I know it isn't helpful to always fall back on "big philosophy" when you
are proposing a small fix/improvement, but we should think about how
much clutter we want to put in to the grammar. The "bare NULL" is
apparently _not_ SQL92 (it does not appear in the BNF definitions in my
SQL book by Date).

I'd like us to think about limiting the extensions to SQL92 in favor of
extending the grammar toward Postgres' OR features. Just a thought...

- Tom

Don't know how or if we want to proceed with a bare "NULL" clause.
Should we bother with a special case of _only_ NULL in a
declaration, as in Bruce's patch?

My patch is clearly wrong. The NULL should be parallel to NOT NULL,
and ought just to be ignored (since NULL is the default). I think
it's worth doing (as the book says, NULL may not be the default on
your system, and anyway, it's always better to specify just for
clarity).
I think explicitly specifying NULL is probably good practice, so it
should be supported.

Maybe (SQL92 is full of inconsistant/non-symmetric features), but you
will need to figure out how to do it without shift/reduce conflicts in
the grammar. The fact that they are there means that either it is
impossible to unambiguously parse the allowed syntax, or that the
grammar definition in the yacc language needs to be restructured a bit.
It isn't obvious to me how to restructure for this case; I've fixed this
kind of problem in other parts of the grammar and the tricks I used
there don't look usable here.

I know it isn't helpful to always fall back on "big philosophy" when you
are proposing a small fix/improvement, but we should think about how
much clutter we want to put in to the grammar. The "bare NULL" is
apparently _not_ SQL92 (it does not appear in the BNF definitions in my
SQL book by Date).

I'd like us to think about limiting the extensions to SQL92 in favor of
extending the grammar toward Postgres' OR features. Just a thought...

- Tom

I strongly agree. Particularly about not whacking at the grammar. Even
"standard" SQL is quite confusing when writing queries. What is being asked
for is not part of the standard, and more importantly does not add any
capability to the system. Any extensions need to be vary carefully thought
out, and even then avoided unless there is a _compelling_ reason for them.

The test I try to use is "could I explain this feature over the phone and
and provide a consistant 'story' about why it works the way it does"? So
that the listener can cope with all the exceptions, limitations, side
effects, errors etc just by relying on the theory from the explanation?

Generally if a proposed extension fails this test it turns out to be either
unimportant, or conceptually flawed.

Remember, the standard already has enough ad-hack semantics and syntactic
sugar, we certainly don't need to add more.

-dg

David Gould dg@illustra.com 510.628.3783 or 510.305.9468
Informix Software (No, really) 300 Lakeside Drive Oakland, CA 94612
"(Windows NT) version 5.0 will build on a proven system architecture
and incorporate tens of thousands of bug fixes from version 4.0."
-- <http://www.microsoft.com/y2k.asp?A=7&amp;B=5&gt;