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

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

On 24 Apr 1998, Bruce Stephens wrote:

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

    Patch?

> Floating point literals: PostgreSQL requires that positive floating
> point constants start with a digit, but the script has ".10" and
> things.  Same here, I altered the lexical spec for floats, but it's
> possible there was a reason for it being the way it was.

    Patch?

> View syntax: The script has "CREATE VIEW foo (a, b, c) AS SELECT ..."
> which doesn't seem to be acceptable to PostgreSQL.  I rephrased these
> as "CREATE VIEW foo AS SELECT blah AS a, ..." and so on.
>
> Commands separated by "go", not ";".  Don't know whether this would be
> easy or hard to do, or whether it's important.  Global substitution
> for this.
>
> Some types, like "tinyint" aren't available, so I just substituted
> "int".
>
> Some of the views are only creatable as the PostgreSQL superuser.
> (This is on the TODO list, I think.)
> -----
>
> I think that was it.  Presumably the developers will be making some
> effort to get this to work (at least most of it: "go" vs ";" is a bit
> irrelevant, but NULL is important, IMHO); it's surely slightly
> embarrassing to recommend a book which has an example that won't run!
> --
> Official WWW Site: http://www.postgresql.org
> Online Docs & FAQ: http://www.postgresql.org/docs
> Searchable Lists: http://www.postgresql.org/mhonarc
>

Marc G. Fournier
Systems Administrator @ hub.org
primary: scrappy@hub.org           secondary: scrappy@{freebsd|postgresql}.org

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&B=5>