Date: Mon, 26 Oct 1998 17:22:25 +0100
From: Sferacarta Software <sferac@bo.nettuno.it>
Reply-To: Sferacarta Software <sferac@bo.nettuno.it>
Organization: Sferacarta Software
Priority: Normal
Message-ID: <6723.981026@bo.nettuno.it>
To: Byron Nikolaidis <byronn@insightdist.com>
CC: pgsql-hackers@postgresql.org
Subject: Re[2]: [HACKERS] Re: [INTERFACES] Odbc parser error
References: <362EE91B.879374AA@sferacarta.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

Hi all,

JS> Yes, I would to suggest a way to solve this problem because in Access we
JS> can't link two tables
JS> by a numeric field, for example:

JS> I have 2 tables:

JS> ORDER_MASTER:           ORDER_DETAIL:
JS> --------------          --------------
JS> numero  SERIAL  <--->   numero  SERIAL
JS> ...                     ...

JS> When I try to create a new ORDER, the connection goes down with message:

JS> # conn=75511800, query='SELECT "order_detail"."numero" FROM
JS> "order_detail" WHERE ("numero" =  NULL )'
JS> # ERROR from backend during send_query: 'ERROR:  parser: parse error at
JS> or near "null"'


JS> I know this is not standard but Access understand both syntaxes (i.e.:

JS> SELECT * FROM table WHERE field IS NULL;      =  SELECT * FROM table
JS> WHERE field = NULL;
JS> SELECT * FROM table WHERE field IS NOT NULL;  =  SELECT * FROM table
JS> WHERE field <> NULL;

JS> Why not to get PostgreSQL to understand it also ?

Done. I modify the pgsql/src/backend/gram.y at line 3318 I inserted
the following two lines:

<DELETED>
3318            | a_expr '=' NULL_P
3319                            {       $$ = makeA_Expr(ISNULL, NULL,$1, NULL);

3320            | a_expr '=' a_expr
3321                            {       $$ = makeA_Expr(OP, "=", $1, $3); }
<DELETED>

now SELECT * FROM table WHERE field IS NULL;
is the same as: SELECT * FROM table WHERE field = NULL;

How about to add it to official release ?
-------------------------------------------------------------------

I think I found a bug using IS NULL operator:

hygea=> \d comuni

Table    = comuni
+----------------------------------+----------------------------------+-------+
|              Field               |              Type                | Length|
+----------------------------------+----------------------------------+-------+
| istat                            | char() not null                  |     6 |
| nome                             | char()                           |    50 |
| provincia                        | char()                           |     2 |
| codice_fiscale                   | char()                           |     4 |
| cap                              | char()                           |     5 |
| regione                          | char()                           |     3 |
| distretto                        | char()                           |     4 |
+----------------------------------+----------------------------------+-------+
hygea=> select * from comuni where COMUNI is null;
                                   ^^^^^^------------------COMUNI is
                                   not a field but the table name.
                                   
istat|nome|provincia|codice_fiscale|cap|regione|distretto
-----+----+---------+--------------+---+-------+---------
(0 rows)


Jose'