I would like for you to also consider adding the following to gram.y for
version 6.5:

| NULL_P '=' a_expr
                                {       $$ = makeA_Expr(ISNULL, NULL, $3,
NULL); }

I know there was some discussion about this earlier including comments
against this.  Access 97 is now generating the following statement and
error:

SQLDriverConnect(out)='DSN=PostgreSQL;DATABASE=mp;SERVER=192.168.97.2;PORT=5
432;UID=kari;PWD=;READONLY=0;PROTOCOL=6.4;FAKEOIDINDEX=0;SHOWOIDCOLUMN=0;ROW
VERSIONING=0;SHOWSYSTEMTABLES=0;CONNSETTINGS='
conn=154616224, 
query='SELECT "RentalOrders"."rentalorderlinesid" FROM "rentalorderlines"
"RentalOrders" WHERE ( NULL = "rentalorderid" ) '
ERROR from backend during send_query: 'ERROR:  parser: parse error at or
near "="'


The above code changed allows Access 97 to work correctly.  I would be happy
to consider any other possible alternatives.

Thanks, Michael


	-----Original Message-----
	From:	Bruce Momjian [SMTP:maillist@candle.pha.pa.us]
	Sent:	Saturday, March 13, 1999 10:14 PM
	To:	Michael Davis
	Cc:	hackers@postgreSQL.org
	Subject:	Re: [HACKERS] parser enhancement request for 6.5

	Applied.


	[Charset iso-8859-1 unsupported, filtering to ASCII...]
	> I have a problem with Access97 not working properly when entering
new
	> records using a sub form, i.e. entering a new order/orderlines or
master and
	> detail tables.  The problem is caused by a SQL statement that
Access97 makes
	> involving NULL.  The syntax that fails is "column_name" = NULL.
The
	> following attachment was provided by -Jose'-.  It contains a very
small
	> enhancement to gram.y that will allow Access97 to work properly
with sub
	> forms.  Can this enhancement be added to release 6.5?
	> 
	>  <<gram.patch>> 
	> Thanks, Michael
	> 

	[Attachment, skipping...]


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

Tue, 16 Mar 1999 17:55:04 -0600
Michael Davis <michael.davis@prevuenet.com>
RE: [HACKERS] parser enhancement request for 6.5
> I would like for you to also consider adding the following to gram.y 
> for version 6.5:

I had the same problem (patch not complete, working on more complete
changes, screwed up now that I've got to resolve changes) for this set
of patches as I did for the int8 stuff.

Your suggested feature should have been in the original patch, and I
have patches on my machine which would have done it correctly. btw,
there is a fundamental shift/reduce conflict trying for "where NULL =
value", though "where value = NULL" seems to be OK. This is *such* a
kludge! Thanks to M$...

Wonder what else I'll find as I wade through 1000 e-mails? :/

                     - Tom

Sun, 21 Mar 1999 15:06:24 +0000
Thomas Lockhart <lockhart@alumni.caltech.edu>
Re: [HACKERS] parser enhancement request for 6.5
Hello!

I tried to create a simple function, to "variable value validate" :)
Here:
text *default_text(text* input) {
	char *ret;
	char def[20];
	if (input) ret=input;
	strcpy((def+4),"Default");
	(*((int4*)def)) = strlen(def+4)+4;
	ret=def;
	elog(NOTICE,"Here:%i", (int4)(*def))
}
This retunrs with the text "Default", if input value IS NULL, and the
with original value if not.
So try it with postgres:
tron=> create table test (v text);
tron=> insert into test values(NULL);
tron=> insert into test values('1');
CREATE INSERT INSERT
tron=> select default_text(v) from test;
NOTICE:  Here: 11
NOTICE:  Here: 5
?column?
--------

       1
I don't seek this in the source, but i think, all function, who take a NULL
value as parameter can't return with a NOT NULL value.
But why? Ooops... And can i check about an int4 if IS NULL ?
??
--
 //  NeKo@KorNeL.szif.hu // http://lsc.kva.hu/  //


Fri, 26 Mar 1999 20:37:48 +0100 (NFT)
"Vazsonyi Peter[ke]" <neko@kornel.szif.hu>
NULL handling question
> I don't seek this in the source, but i think, all function, who take a 
> NULL value as parameter can't return with a NOT NULL value.
> But why?

Postgres assumes that a NULL input will give a NULL output, and never
calls your routine at all. Since NULL means "don't know", there is a
strong argument that this is correct behavior.

> And can i check about an int4 if IS NULL ?

Not as cleanly as the pass-by-reference data types. I vaguely recall
that the input and output routines can look for a second or third
argument, one of which is a NULL indicator. But that mechanism is not
generally usable in other contexts afaik.

                     - Tom

Mon, 29 Mar 1999 15:47:15 +0000
Thomas Lockhart <lockhart@alumni.caltech.edu>
Re: [HACKERS] NULL handling question
Thus spake Thomas Lockhart
> > I don't seek this in the source, but i think, all function, who take a 
> > NULL value as parameter can't return with a NOT NULL value.
> > But why?
> 
> Postgres assumes that a NULL input will give a NULL output, and never
> calls your routine at all. Since NULL means "don't know", there is a

Actually, the problem is that it does call the function.  After it
returns it throws away the result and so the effect is that the function
never gets called but in the meantime, the function has to deal with
NULL inputs for nothing.  This has been hanging around since the last
release.  I looked at the dispatch code but it wasn't very clear where
we have to put the test to do this correctly.  Maybe we can get it cleaned
up before release this time.


> strong argument that this is correct behavior.

I agree but recently I said that there was no stored procedures in PostgreSQL
and someone corrected me pointing out that functions with no return were
in effect stored procedures.  Do the same arguments apply?  If a procedure
is passed a NULL argument, should the side effects be bypassed?

-- 
D'Arcy J.M. Cain <darcy@{druid|vex}.net>   |  Democracy is three wolves
http://www.druid.net/darcy/                |  and a sheep voting on
+1 416 424 2871     (DoD#0082)    (eNTP)   |  what's for dinner.

Mon, 29 Mar 1999 12:27:05 -0500 (EST)
"D'Arcy" "J.M." Cain <darcy@druid.net>
Re: [HACKERS] NULL handling question
On Mon, 29 Mar 1999, Thomas Lockhart wrote:
> Postgres assumes that a NULL input will give a NULL output,

But why? That is not true in all case, i mean so like: "FALSE && dont'know"
is always FALSE.

> and never calls your routine at all.

But! I see the output of elogs in function.
I don't sure about 6.5, i test it not for a long time. The 6.4.x calls my
functions always (with one or more NULL parameters).

Then if the return value has "pass-by-reference" type, can i give a NULL or
a NOT NULL value. I don't now realy, but i think it's posible to give NULL
indicator with int4, bool, etc like type results.

I mean this feature is necessary... Not? ;)
Any opinion?

So thans for all.

--
 NeKo@(kva.hu|Kornel.szif.hu) the servant of Crash
 hu:http://lsc.kva.hu  en:-- (sorry, my english is...)


Mon, 29 Mar 1999 18:32:43 +0100 (NFT)
"Vazsonyi Peter[ke]" <neko@kornel.szif.hu>
Re: [HACKERS] NULL handling question
> > Postgres assumes that a NULL input will give a NULL output,
> But why? That is not true in all case, i mean so like: "FALSE && 
> dont'know" is always FALSE.

Your example shows a flaw in the Postgres premise on this topic,
perhaps.

> > and never calls your routine at all.
> But! I see the output of elogs in function.
> The 6.4.x calls my
> functions always (with one or more NULL parameters).

It's been discussed before, and as you and others note it seems the
behavior has changed so that functions are called even with NULL
input. But the job wasn't finished since the results are ignored.

                    - Tom

Mon, 29 Mar 1999 18:09:28 +0000
Thomas Lockhart <lockhart@alumni.caltech.edu>
Re: [HACKERS] NULL handling question

Added to 6.5 beta.


> I would like for you to also consider adding the following to gram.y for
> version 6.5:
> 
> | NULL_P '=' a_expr
>                                 {       $$ = makeA_Expr(ISNULL, NULL, $3,
> NULL); }
> 
> I know there was some discussion about this earlier including comments
> against this.  Access 97 is now generating the following statement and
> error:
> 
> SQLDriverConnect(out)='DSN=PostgreSQL;DATABASE=mp;SERVER=192.168.97.2;PORT=5
> 432;UID=kari;PWD=;READONLY=0;PROTOCOL=6.4;FAKEOIDINDEX=0;SHOWOIDCOLUMN=0;ROW
> VERSIONING=0;SHOWSYSTEMTABLES=0;CONNSETTINGS='
> conn=154616224, 
> query='SELECT "RentalOrders"."rentalorderlinesid" FROM "rentalorderlines"
> "RentalOrders" WHERE ( NULL = "rentalorderid" ) '
> ERROR from backend during send_query: 'ERROR:  parser: parse error at or
> near "="'
> 
> 
> The above code changed allows Access 97 to work correctly.  I would be happy
> to consider any other possible alternatives.
> 
> Thanks, Michael
> 
> 
> 	-----Original Message-----
> 	From:	Bruce Momjian [SMTP:maillist@candle.pha.pa.us]
> 	Sent:	Saturday, March 13, 1999 10:14 PM
> 	To:	Michael Davis
> 	Cc:	hackers@postgreSQL.org
> 	Subject:	Re: [HACKERS] parser enhancement request for 6.5
> 
> 	Applied.
> 
> 
> 	[Charset iso-8859-1 unsupported, filtering to ASCII...]
> 	> I have a problem with Access97 not working properly when entering
> new
> 	> records using a sub form, i.e. entering a new order/orderlines or
> master and
> 	> detail tables.  The problem is caused by a SQL statement that
> Access97 makes
> 	> involving NULL.  The syntax that fails is "column_name" = NULL.
> The
> 	> following attachment was provided by -Jose'-.  It contains a very
> small
> 	> enhancement to gram.y that will allow Access97 to work properly
> with sub
> 	> forms.  Can this enhancement be added to release 6.5?
> 	> 
> 	>  <<gram.patch>> 
> 	> Thanks, Michael
> 	> 
> 
> 	[Attachment, skipping...]
> 
> 
> 	-- 
> 	  Bruce Momjian                        |  http://www.op.net/~candle
> 	  maillist@candle.pha.pa.us            |  (610) 853-3000
> 	  +  If your life is a hard drive,     |  830 Blythe Avenue
> 	  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania
> 19026
> 


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

Sun, 9 May 1999 20:54:27 -0400 (EDT)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] parser enhancement request for 6.5
> > I would like for you to also consider adding the following to gram.y for
> > version 6.5:
> > | NULL_P '=' a_expr
> >     {       $$ = makeA_Expr(ISNULL, NULL, $3, NULL); }
> > I know there was some discussion about this earlier including comments
> > against this.  Access 97 is now generating the following statement and
> > error...

I'm not certain that this patch should survive. There are at least two
other places in the parser which should be modified for symmetry (the
"b_expr" and the default expressions) and I recall that these lead to
more shift/reduce conflicts. Remember that shift/reduce conflicts
indicate that some portion of the parser logic can *never* be reached,
which means that some feature (perhaps the new one, or perhaps an
existing one) is disabled.

There is currently a single shift/reduce conflict in  gram.y, and I'm
suprised to find that it is *not* due to the "NULL_P '=' a_expr" line.
I'm planning on touching gram.y to hunt down the shift/reduce conflict
(from previous work I think it in Stefan's "parens around selects"
mods), and I'll look at the NULL_P issue again also.

I'll reiterate something which everyone probably knows: "where NULL =
expr" is *not* standard SQL92, and any company selling products which
implement this rather than the standard "where expr is NULL" should
make your "don't buy" list, rather than your "only buy" list, which is
what they are trying to force you to do :(

                            - Tom

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

Mon, 10 May 1999 14:53:20 +0000
Thomas Lockhart <lockhart@alumni.caltech.edu>
Re: [HACKERS] parser enhancement request for 6.5
Thomas Lockhart wrote:

> There is currently a single shift/reduce conflict in  gram.y, and I'm
> suprised to find that it is *not* due to the "NULL_P '=' a_expr" line.
> I'm planning on touching gram.y to hunt down the shift/reduce conflict
> (from previous work I think it in Stefan's "parens around selects"
> mods), and I'll look at the NULL_P issue again also.

    No - not the parens.

    Looking  at  the  y.output  (produced with -v) I see that the
    conflict is at state 266  when  in  the  SelectStmt  the  FOR
    keyword  of FOR UPDATE has been seen.  The SelectStmt is also
    used in CursorStmt.

    The  rule  cursor_clause  in   CursorStmt   results   in   an
    elog(ERROR)   telling   that   cursors  for  update  are  not
    supported. But in fact a

        DECLARE x1 CURSOR FOR SELECT * FROM x FOR UPDATE OF x;

    doesn't  throw  an  error.  So   it   is   the   CursorStmt's
    cursor_clause  that  is  currently unreachable in the parser.
    Instead the SelectStmt's for_update_clause has already  eaten
    up the FOR UPDATE.


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#======================================== jwieck@debis.com (Jan Wieck) #



Mon, 10 May 1999 18:28:49 +0200 (MET DST)
jwieck@debis.com (Jan Wieck)
Re: [HACKERS] parser enhancement request for 6.5