Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commita1ee066

Browse files
committed
Provide tunable knob for x = NULL -> x IS NULL transformation, default to off.
1 parentfd5e959 commita1ee066

File tree

7 files changed

+101
-51
lines changed

7 files changed

+101
-51
lines changed

‎doc/src/sgml/func.sgml

Lines changed: 16 additions & 8 deletions
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,4 @@
1-
<!-- $Header: /cvsroot/pgsql/doc/src/sgml/func.sgml,v 1.71 2001/09/10 02:46:18 ishii Exp $ -->
1+
<!-- $Header: /cvsroot/pgsql/doc/src/sgml/func.sgml,v 1.72 2001/09/20 14:20:26 petere Exp $ -->
22

33
<chapter id="functions">
44
<title>Functions and Operators</title>
@@ -266,16 +266,24 @@
266266
Do <emphasis>not</emphasis> use
267267
<literal><replaceable>expression</replaceable> = NULL</literal>
268268
because NULL is not <quote>equal to</quote> NULL. (NULL represents
269-
an unknown value, so it is not known whether two unknown values are
270-
equal.) <productname>Postgres</productname> presently converts
271-
<literal>x = NULL</literal> clauses to <literal>x IS NULL</literal> to
272-
allow some broken client applications (such as
273-
<productname>Microsoft Access</productname>) to work, but this may
274-
be discontinued in a future release.
269+
an unknown value, and it is not known whether two unknown values are
270+
equal.)
275271
</para>
276272

277273
<para>
278-
Boolean values can be tested using the constructs
274+
Some applications may (incorrectly) require that
275+
<literal><replaceable>expression</replaceable> = NULL</literal>
276+
returns true if <replaceable>expression</replaceable> evaluates to
277+
the NULL value. To support these applications, the run-time option
278+
<varname>transform_null_equals</varname> can be turned on (e.g.,
279+
<literal>SET transform_null_equals TO ON;</literal>).
280+
<productname>PostgreSQL</productname> would then convert <literal>x
281+
= NULL</literal> clauses to <literal>x IS NULL</literal>. This was
282+
the default behavior in releases 6.5 through 7.1.
283+
</para>
284+
285+
<para>
286+
Boolean values can also be tested using the constructs
279287
<synopsis>
280288
<replaceable>expression</replaceable> IS TRUE
281289
<replaceable>expression</replaceable> IS NOT TRUE

‎doc/src/sgml/runtime.sgml

Lines changed: 44 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$Header: /cvsroot/pgsql/doc/src/sgml/runtime.sgml,v 1.80 2001/09/16 16:11:09 petere Exp $
2+
$Header: /cvsroot/pgsql/doc/src/sgml/runtime.sgml,v 1.81 2001/09/20 14:20:27 petere Exp $
33
-->
44

55
<Chapter Id="runtime">
@@ -1201,6 +1201,49 @@ dynamic_library_path = '/usr/local/lib/postgresql:/home/my_project/lib:$libdir'
12011201
</listitem>
12021202
</varlistentry>
12031203

1204+
<varlistentry>
1205+
<term><varname>TRANSFORM_NULL_EQUALS</varname> (<type>boolean</type>)</term>
1206+
<listitem>
1207+
<para>
1208+
When turned on, expressions of the form
1209+
<literal><replaceable>expr</> = NULL</literal> (or
1210+
<literal>NULL = <replaceable>expr</></literal>) are treated as
1211+
<literal><replaceable>expr</> IS NULL</literal>, that is, they
1212+
return true if <replaceable>expr</> evaluates to the NULL
1213+
value, and false otherwise. The correct behavior of
1214+
<literal><replaceable>expr</> = NULL</literal> is to always
1215+
return NULL (unknown). Therefore this option defaults to off.
1216+
</para>
1217+
1218+
<para>
1219+
However, filtered forms in <productname>Microsoft
1220+
Access</productname> generate queries that appear to use
1221+
<literal><replaceable>expr</> = NULL</literal> to test for
1222+
NULLs, so if you use that interface to access the database you
1223+
might want to turn this option on. Since expressions of the
1224+
form <literal><replaceable>expr</> = NULL</literal> always
1225+
return NULL (using the correct interpretation) they are not
1226+
very useful and do not appear often in normal applications, so
1227+
this option does little harm in practice. But new users are
1228+
frequently confused about the semantics of expressions
1229+
involving NULL, so we do not turn this option on by default.
1230+
</para>
1231+
1232+
<para>
1233+
Note that this option only affects the literal <literal>=</>
1234+
operator, not other comparison operators or other expressions
1235+
that are computationally equivalent to some expression
1236+
involving the equals operator (such as <literal>IN</literal>).
1237+
Thus, this option is not a general fix for bad programming.
1238+
</para>
1239+
1240+
<para>
1241+
Refer to the <citetitle>User's Guide</citetitle> for related
1242+
information.
1243+
</para>
1244+
</listitem>
1245+
</varlistentry>
1246+
12041247
<varlistentry>
12051248
<term><varname>PORT</varname> (<type>integer</type>)</term>
12061249
<listitem>

‎src/backend/parser/gram.y

Lines changed: 3 additions & 26 deletions
Original file line numberDiff line numberDiff line change
@@ -11,7 +11,7 @@
1111
*
1212
*
1313
* IDENTIFICATION
14-
* $Header: /cvsroot/pgsql/src/backend/parser/gram.y,v 2.251 2001/09/18 01:59:06 tgl Exp $
14+
* $Header: /cvsroot/pgsql/src/backend/parser/gram.y,v 2.252 2001/09/20 14:20:27 petere Exp $
1515
*
1616
* HISTORY
1717
* AUTHORDATEMAJOR EVENT
@@ -89,7 +89,6 @@ static void insertSelectOptions(SelectStmt *stmt,
8989
List *sortClause, List *forUpdate,
9090
Node *limitOffset, Node *limitCount);
9191
static Node *makeSetOp(SetOperation op,bool all, Node *larg, Node *rarg);
92-
staticboolexprIsNullConstant(Node *arg);
9392
static Node *doNegate(Node *n);
9493
staticvoiddoNegateFloat(Value *v);
9594

@@ -4465,29 +4464,7 @@ a_expr: c_expr
44654464
|a_expr'>'a_expr
44664465
{$$ = makeA_Expr(OP,">",$1,$3); }
44674466
|a_expr'='a_expr
4468-
{
4469-
/*
4470-
* Special-case "foo = NULL" and "NULL = foo" for
4471-
* compatibility with standards-broken products
4472-
* (like Microsoft's). Turn these into IS NULL exprs.
4473-
*/
4474-
if (exprIsNullConstant($3))
4475-
{
4476-
NullTest *n = makeNode(NullTest);
4477-
n->arg =$1;
4478-
n->nulltesttype = IS_NULL;
4479-
$$ = (Node *)n;
4480-
}
4481-
elseif (exprIsNullConstant($1))
4482-
{
4483-
NullTest *n = makeNode(NullTest);
4484-
n->arg =$3;
4485-
n->nulltesttype = IS_NULL;
4486-
$$ = (Node *)n;
4487-
}
4488-
else
4489-
$$ = makeA_Expr(OP,"=",$1,$3);
4490-
}
4467+
{$$ = makeA_Expr(OP,"=",$1,$3); }
44914468

44924469
|a_exprOpa_expr
44934470
{$$ = makeA_Expr(OP,$2,$1,$3); }
@@ -6137,7 +6114,7 @@ Oid param_type(int t)
61376114
/*
61386115
* Test whether an a_expr is a plain NULL constant or not.
61396116
*/
6140-
staticbool
6117+
bool
61416118
exprIsNullConstant(Node *arg)
61426119
{
61436120
if (arg &&IsA(arg, A_Const))

‎src/backend/parser/parse_expr.c

Lines changed: 32 additions & 10 deletions
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $Header: /cvsroot/pgsql/src/backend/parser/parse_expr.c,v 1.99 2001/08/09 18:28:17 petere Exp $
11+
* $Header: /cvsroot/pgsql/src/backend/parser/parse_expr.c,v 1.100 2001/09/20 14:20:27 petere Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -34,9 +34,10 @@
3434

3535

3636
intmax_expr_depth=DEFAULT_MAX_EXPR_DEPTH;
37-
3837
staticintexpr_depth_counter=0;
3938

39+
boolTransform_null_equals= false;
40+
4041
staticNode*parser_typecast_constant(Value*expr,TypeName*typename);
4142
staticNode*parser_typecast_expression(ParseState*pstate,
4243
Node*expr,TypeName*typename);
@@ -157,14 +158,35 @@ transformExpr(ParseState *pstate, Node *expr, int precedence)
157158
{
158159
caseOP:
159160
{
160-
Node*lexpr=transformExpr(pstate,
161-
a->lexpr,
162-
precedence);
163-
Node*rexpr=transformExpr(pstate,
164-
a->rexpr,
165-
precedence);
166-
167-
result= (Node*)make_op(a->opname,lexpr,rexpr);
161+
/*
162+
* Special-case "foo = NULL" and "NULL = foo" for
163+
* compatibility with standards-broken products
164+
* (like Microsoft's). Turn these into IS NULL exprs.
165+
*/
166+
if (Transform_null_equals&&strcmp(a->opname,"=")==0
167+
&& (exprIsNullConstant(a->lexpr)||exprIsNullConstant(a->rexpr)))
168+
{
169+
NullTest*n=makeNode(NullTest);
170+
n->nulltesttype=IS_NULL;
171+
172+
if (exprIsNullConstant(a->lexpr))
173+
n->arg=a->rexpr;
174+
else
175+
n->arg=a->lexpr;
176+
177+
result=transformExpr(pstate,n,precedence);
178+
}
179+
else
180+
{
181+
Node*lexpr=transformExpr(pstate,
182+
a->lexpr,
183+
precedence);
184+
Node*rexpr=transformExpr(pstate,
185+
a->rexpr,
186+
precedence);
187+
188+
result= (Node*)make_op(a->opname,lexpr,rexpr);
189+
}
168190
}
169191
break;
170192
caseAND:

‎src/backend/utils/misc/guc.c

Lines changed: 2 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -4,7 +4,7 @@
44
* Support for grand unified configuration scheme, including SET
55
* command, configuration file, and command line options.
66
*
7-
* $Header: /cvsroot/pgsql/src/backend/utils/misc/guc.c,v 1.48 2001/09/12 14:06:37 petere Exp $
7+
* $Header: /cvsroot/pgsql/src/backend/utils/misc/guc.c,v 1.49 2001/09/20 14:20:27 petere Exp $
88
*
99
* Copyright 2000 by PostgreSQL Global Development Group
1010
* Written by Peter Eisentraut <peter_e@gmx.net>.
@@ -247,12 +247,10 @@ static struct config_bool
247247
{"show_source_port",PGC_SIGHUP,&ShowPortNumber, false,NULL},
248248

249249
{"sql_inheritance",PGC_USERSET,&SQL_inheritance, true,NULL},
250-
251250
{"australian_timezones",PGC_USERSET,&Australian_timezones, false,ClearDateCache},
252-
253251
{"fixbtree",PGC_POSTMASTER,&FixBTree, true,NULL},
254-
255252
{"password_encryption",PGC_USERSET,&Password_encryption, false,NULL},
253+
{"transform_null_equals",PGC_USERSET,&Transform_null_equals, false,NULL},
256254

257255
{NULL,0,NULL, false,NULL}
258256
};

‎src/include/parser/gramparse.h

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -7,7 +7,7 @@
77
* Portions Copyright (c) 1996-2001, PostgreSQL Global Development Group
88
* Portions Copyright (c) 1994, Regents of the University of California
99
*
10-
* $Id: gramparse.h,v 1.15 2001/02/09 03:26:27 tgl Exp $
10+
* $Id: gramparse.h,v 1.16 2001/09/20 14:20:28 petere Exp $
1111
*
1212
*-------------------------------------------------------------------------
1313
*/
@@ -29,5 +29,6 @@ extern Oidparam_type(int t);
2929
externintyyparse(void);
3030
externchar*xlateSqlFunc(char*name);
3131
externchar*xlateSqlType(char*name);
32+
boolexprIsNullConstant(Node*arg);
3233

3334
#endif/* GRAMPARSE_H */

‎src/include/parser/parse_expr.h

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -7,7 +7,7 @@
77
* Portions Copyright (c) 1996-2001, PostgreSQL Global Development Group
88
* Portions Copyright (c) 1994, Regents of the University of California
99
*
10-
* $Id: parse_expr.h,v 1.21 2001/01/24 19:43:27 momjian Exp $
10+
* $Id: parse_expr.h,v 1.22 2001/09/20 14:20:28 petere Exp $
1111
*
1212
*-------------------------------------------------------------------------
1313
*/
@@ -20,6 +20,7 @@
2020
#defineEXPR_RELATION_FIRST 2
2121

2222
externintmax_expr_depth;
23+
externboolTransform_null_equals;
2324

2425
externNode*transformExpr(ParseState*pstate,Node*expr,intprecedence);
2526
externOidexprType(Node*expr);

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp