parser enhancement request for 6.5

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?

I recall discussing this earlier, and after the obligatory objection
that this is non-standard (a la everything M$) it should be OK to do it.
I could have sworn that I'd made this fix, but can't see it in current
sources and can't see any evidence that it ever was :(

Will enable this for v6.5, with the proviso that it is fair game for
removal if it ever conflicts with other SQL parsing needs.

- Tom

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

Very cool!

-----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

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

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

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/ //

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

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.

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...)

I would like to have a C function and/or stored procedure that can accept a
null parameter value and return a non-null value.

Thanks, Michael

-----Original Message-----
From: D'Arcy" "J.M." Cain [SMTP:darcy@druid.net]
Sent: Monday, March 29, 1999 10:27 AM
To: lockhart@alumni.caltech.edu
Cc: neko@kornel.szif.hu; hackers@postgreSQL.org
Subject: 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.

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

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

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

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) #