Thread: Row IS NULL question
I'm playing around NULL. Docs says that "A row value is considered not null if it has at least one field that is not null." and "SELECT ROW(table.*) IS NULL FROM table; -- detect all-null rows" So, I try: wow=# \d tst Table "public.tst" Column | Type | Modifiers --------+------------------+----------- a | double precision | b | double precision | % echo 'SELECT count(*) FROM tst WHERE a IS NULL AND b IS NULL;' | psql wow SET count ------- 6 (1 row) But ROW() IS NULL doesn't find anything: % echo 'SELECT ROW(tst.*) IS NULL FROM tst;' | psql wow | grep 't' % echo 'SELECT count(*) FROM tst WHERE ROW(tst.*) IS NULL;' | psql wow SET count ------- 0 (1 row) What do I do wrong? Version of postgres - today's HEAD. -- Teodor Sigaev E-mail: teodor@sigaev.ru WWW: http://www.sigaev.ru/
Teodor Sigaev <teodor@sigaev.ru> writes:
> % echo 'SELECT count(*) FROM tst WHERE ROW(tst.*) IS NULL;' | psql wow
> SET
> count
> -------
> 0
> (1 row)
Hm, it turns out that this works:select * from int8_tbl x where row(x.q1,x.q2) is null;
but not this:select * from int8_tbl x where row(x.*) is null;
EXPLAIN tells the tale:
regression=# explain select * from int8_tbl x where row(x.q1,x.q2) is null; QUERY PLAN
-----------------------------------------------------------Seq Scan on int8_tbl x (cost=0.00..1.05 rows=1 width=16)
Filter:((q1 IS NULL) AND (q2 IS NULL))
(2 rows)
regression=# explain select * from int8_tbl x where row(x.*) is null; QUERY PLAN
-----------------------------------------------------------Seq Scan on int8_tbl x (cost=0.00..1.05 rows=1 width=16)
Filter:(x.* IS NULL)
(2 rows)
Apparently what's happening is that gram.y's makeRowNullTest() bursts
the RowExpr apart into individual isnull tests. Now that RowExpr
expansion can change the number of items in the row, it's clearly
premature to do that processing in gram.y --- we should move it to
parse analysis.
Part of the issue is that ExecEvalNullTest simply tests for whether the
presented Datum is null, which I think is impossible for a whole-row Var
coming from a table (but it could happen for a row-returning function's
result, for example). I think that according to the letter of the spec,
an IS [NOT] NULL test should "drill down" into rowtype datums and check
nullness of the individual row fields. Probably the same is true for
array datums.
Moving makeRowNullTest() doesn't seem like a big deal, but changing
ExecEvalNullTest would take some added code. Do we want to tackle that
during beta, or hold off till 8.3? An argument for doing it now is that
we just added nulls-in-arrays in 8.2, and it'd be good if the semantics
of that were right the first time rather than changing later.
regards, tom lane
Please excuse me for jumping in like this... but just for my understanding... Does this have anything to do with ExecEvalWholeRowVar? On Thu, 2006-09-28 at 11:45 -0400, Tom Lane wrote: > Teodor Sigaev <teodor@sigaev.ru> writes: > > % echo 'SELECT count(*) FROM tst WHERE ROW(tst.*) IS NULL;' | psql wow > > SET > > count > > ------- > > 0 > > (1 row) > > Hm, it turns out that this works: > select * from int8_tbl x where row(x.q1,x.q2) is null; > but not this: > select * from int8_tbl x where row(x.*) is null; > > EXPLAIN tells the tale: > > regression=# explain select * from int8_tbl x where row(x.q1,x.q2) is null; > QUERY PLAN > ----------------------------------------------------------- > Seq Scan on int8_tbl x (cost=0.00..1.05 rows=1 width=16) > Filter: ((q1 IS NULL) AND (q2 IS NULL)) > (2 rows) > > regression=# explain select * from int8_tbl x where row(x.*) is null; > QUERY PLAN > ----------------------------------------------------------- > Seq Scan on int8_tbl x (cost=0.00..1.05 rows=1 width=16) > Filter: (x.* IS NULL) > (2 rows) > > Apparently what's happening is that gram.y's makeRowNullTest() bursts > the RowExpr apart into individual isnull tests. Now that RowExpr > expansion can change the number of items in the row, it's clearly > premature to do that processing in gram.y --- we should move it to > parse analysis. > > Part of the issue is that ExecEvalNullTest simply tests for whether the > presented Datum is null, which I think is impossible for a whole-row Var > coming from a table (but it could happen for a row-returning function's > result, for example). I think that according to the letter of the spec, > an IS [NOT] NULL test should "drill down" into rowtype datums and check > nullness of the individual row fields. Probably the same is true for > array datums. > > Moving makeRowNullTest() doesn't seem like a big deal, but changing > ExecEvalNullTest would take some added code. Do we want to tackle that > during beta, or hold off till 8.3? An argument for doing it now is that > we just added nulls-in-arrays in 8.2, and it'd be good if the semantics > of that were right the first time rather than changing later. > > regards, tom lane > > ---------------------------(end of broadcast)--------------------------- > TIP 6: explain analyze is your friend > -- Regards, Gevik Babakhani http://www.postgresql.nl http://www.truesoftware.nl
Gevik Babakhani <pgdev@xs4all.nl> writes:
> Does this have anything to do with ExecEvalWholeRowVar?
Yeah, the construct
>> Seq Scan on int8_tbl x (cost=0.00..1.05 rows=1 width=16)
>> Filter: (x.* IS NULL)
is really ExecEvalNullTest applied to the result of ExecEvalWholeRowVar.
If we simply push makeRowNullTest() to later in the parser, this case
will work as expected, but there is still the issue of IS [NOT] NULL
applied to rowtype values that are not coming from ROW() constructs,
such as the result of a rowtype-returning function. Likewise, null
tests on arrays really would have to be handled in the executor to
work per spec --- we can hardly break them down into scalar isnull
tests at parse time, which is what makeRowNullTest() is trying to do.
regards, tom lane
On Thu, Sep 28, 2006 at 11:45:32AM -0400, Tom Lane wrote: > Teodor Sigaev <teodor@sigaev.ru> writes: > > % echo 'SELECT count(*) FROM tst WHERE ROW(tst.*) IS NULL;' | psql wow > > SET > > count > > ------- > > 0 > > (1 row) > > Hm, it turns out that this works: > select * from int8_tbl x where row(x.q1,x.q2) is null; > but not this: > select * from int8_tbl x where row(x.*) is null; > > EXPLAIN tells the tale: > > regression=# explain select * from int8_tbl x where row(x.q1,x.q2) is null; > QUERY PLAN > ----------------------------------------------------------- > Seq Scan on int8_tbl x (cost=0.00..1.05 rows=1 width=16) > Filter: ((q1 IS NULL) AND (q2 IS NULL)) > (2 rows) > > regression=# explain select * from int8_tbl x where row(x.*) is null; > QUERY PLAN > ----------------------------------------------------------- > Seq Scan on int8_tbl x (cost=0.00..1.05 rows=1 width=16) > Filter: (x.* IS NULL) > (2 rows) > > Apparently what's happening is that gram.y's makeRowNullTest() bursts > the RowExpr apart into individual isnull tests. Now that RowExpr > expansion can change the number of items in the row, it's clearly > premature to do that processing in gram.y --- we should move it to > parse analysis. > > Part of the issue is that ExecEvalNullTest simply tests for whether > the presented Datum is null, which I think is impossible for a > whole-row Var coming from a table (but it could happen for a > row-returning function's result, for example). I think that > according to the letter of the spec, an IS [NOT] NULL test should > "drill down" into rowtype datums and check nullness of the > individual row fields. Probably the same is true for array datums. > > Moving makeRowNullTest() doesn't seem like a big deal, but changing > ExecEvalNullTest would take some added code. Do we want to tackle > that during beta, or hold off till 8.3? An argument for doing it > now is that we just added nulls-in-arrays in 8.2, and it'd be good > if the semantics of that were right the first time rather than > changing later. I think this qualifies as a bug fix and should go in 8.2 :) Cheers, D -- David Fetter <david@fetter.org> http://fetter.org/ phone: +1 415 235 3778 AIM: dfetter666 Skype: davidfetter Remember to vote!
I wrote:
> Moving makeRowNullTest() doesn't seem like a big deal, but changing
> ExecEvalNullTest would take some added code. Do we want to tackle that
> during beta, or hold off till 8.3? An argument for doing it now is that
> we just added nulls-in-arrays in 8.2, and it'd be good if the semantics
> of that were right the first time rather than changing later.
Now that I look more closely, makeRowNullTest() is actually WRONG even
for the cases it handles. SQL99/SQL2003 define <null predicate> thus:
1) Let R be the value of the <row value expression>.
2) If every value in R is the null value, then "R IS NULL" is true; otherwise, it is false.
3) If no value in R is the null value, then "R IS NOT NULL" is true; otherwise, it is false.
makeRowNullTest() is set up to return TRUE for an IS NOT NULL test if
*any* element of R is non null:
regression=# explain select * from int8_tbl x where row(x.q1,x.q2) is not null;
QUERY PLAN
-----------------------------------------------------------Seq Scan on int8_tbl x (cost=0.00..1.05 rows=5 width=16)
Filter:((q1 IS NOT NULL) OR (q2 IS NOT NULL))
(2 rows)
So this is clearly a bug and clearly one of long standing --- we've been
getting this wrong since PG 7.3 :-(
regards, tom lane
Thank you for the explanation. On Thu, 2006-09-28 at 12:06 -0400, Tom Lane wrote: > Gevik Babakhani <pgdev@xs4all.nl> writes: > > Does this have anything to do with ExecEvalWholeRowVar? > > Yeah, the construct > > >> Seq Scan on int8_tbl x (cost=0.00..1.05 rows=1 width=16) > >> Filter: (x.* IS NULL) > > is really ExecEvalNullTest applied to the result of ExecEvalWholeRowVar. > > If we simply push makeRowNullTest() to later in the parser, this case > will work as expected, but there is still the issue of IS [NOT] NULL > applied to rowtype values that are not coming from ROW() constructs, > such as the result of a rowtype-returning function. Likewise, null > tests on arrays really would have to be handled in the executor to > work per spec --- we can hardly break them down into scalar isnull > tests at parse time, which is what makeRowNullTest() is trying to do. > > regards, tom lane > > ---------------------------(end of broadcast)--------------------------- > TIP 2: Don't 'kill -9' the postmaster > -- Regards, Gevik Babakhani http://www.postgresql.nl http://www.truesoftware.nl
> I wrote: > > Moving makeRowNullTest() doesn't seem like a big deal, but changing > > ExecEvalNullTest would take some added code. Do we want to tackle that > > during beta, or hold off till 8.3? An argument for doing it now is that > > we just added nulls-in-arrays in 8.2, and it'd be good if the semantics > > of that were right the first time rather than changing later. > > Now that I look more closely, makeRowNullTest() is actually WRONG even > for the cases it handles. SQL99/SQL2003 define <null predicate> thus: > > 1) Let R be the value of the <row value expression>. > > 2) If every value in R is the null value, then "R IS NULL" is true; > otherwise, it is false. > > 3) If no value in R is the null value, then "R IS NOT NULL" is > true; otherwise, it is false. > > makeRowNullTest() is set up to return TRUE for an IS NOT NULL test if > *any* element of R is non null: > > regression=# explain select * from int8_tbl x where row(x.q1,x.q2) is not null; > > QUERY PLAN > ----------------------------------------------------------- > Seq Scan on int8_tbl x (cost=0.00..1.05 rows=5 width=16) > Filter: ((q1 IS NOT NULL) OR (q2 IS NOT NULL)) > (2 rows) > > So this is clearly a bug and clearly one of long standing --- we've been > getting this wrong since PG 7.3 :-( > > regards, tom lane > > ---------------------------(end of broadcast)--------------------------- > TIP 5: don't forget to increase your free space map settings > Would it be correct to do the null test in ExecEvalNullTest (I would like to learn what happens but somehow I just cannot get the debugger on makeRowNullTest in gram.c) On Thu, 2006-09-28 at 14:38 -0400, Tom Lane wrote: -- Regards, Gevik Babakhani http://www.postgresql.nl http://www.truesoftware.nl