Thread: feature request ?

Hello

since BOOL expression has three possible values: TRUE,FALSE,NULL
plpgsql IF control structure should have three alternate blocks:
THEN,ELSE,NULL

shouldn't it ?

Sad,

> since BOOL expression has three possible values: TRUE,FALSE,NULL
> plpgsql IF control structure should have three alternate blocks:
> THEN,ELSE,NULL
>
> shouldn't it ?

No, why?

How would you construct a tri-valued IF/THEN?     Doesn't seem too likely to 
me, as well as being different from every other programming language in 
existance ...

-- 
Josh Berkus
Aglio Database Solutions
San Francisco

On Jun 24, 2004, at 2:12 AM, Josh Berkus wrote:

> Sad,
>
>> since BOOL expression has three possible values: TRUE,FALSE,NULL
>> plpgsql IF control structure should have three alternate blocks:
>> THEN,ELSE,NULL
>>
>> shouldn't it ?
>
> No, why?
>
> How would you construct a tri-valued IF/THEN?     Doesn't seem too 
> likely to
> me, as well as being different from every other programming language in
> existance ...

Creating a new control structure to do handle this seems odd. However, 
one could easily have the same effect using a nested if. Using the 
pl/pgsql ELSIF construct, it's pretty straightforward.

IF foo IS NULLTHEN ...
ELSIF fooTHEN ...
ELSE ...
END IF;


Michael Glaesemann
grzm myrealbox com

On Thursday 24 June 2004 09:32, Michael Glaesemann wrote:
> On Jun 24, 2004, at 2:12 AM, Josh Berkus wrote:
> > Sad,
> >
> >> since BOOL expression has three possible values: TRUE,FALSE,NULL
> >> plpgsql IF control structure should have three alternate blocks:
> >> THEN,ELSE,NULL
> >>
> >> shouldn't it ?
> >
> > No, why?
> >
> > How would you construct a tri-valued IF/THEN?     Doesn't seem too
> > likely to
> > me, as well as being different from every other programming language in
> > existance ...
>
> Creating a new control structure to do handle this seems odd. However,
> one could easily have the same effect using a nested if. Using the
> pl/pgsql ELSIF construct, it's pretty straightforward.
>
> IF foo IS NULL
>     THEN ...
> ELSIF foo
>     THEN ...
> ELSE ...
> END IF;

here the foo expression woll be executed twice

On Wednesday 23 June 2004 21:12, you wrote:
> Sad,
>
> > since BOOL expression has three possible values: TRUE,FALSE,NULL
> > plpgsql IF control structure should have three alternate blocks:
> > THEN,ELSE,NULL
> >
> > shouldn't it ?
>
> No, why?
>
> How would you construct a tri-valued IF/THEN?     Doesn't seem too likely
> to me, as well as being different from every other programming language in
> existance ...

Three valued BOOLEAN is already different "from every other programming
language in existance"

Programming languages, perhaps, but clearly not uncommon in SQL ... Informix certainly allows a column to be of type
booleanbut with a value of NULL for given rows (unless precluded by a not-null constraint). Should we question
integers,which can be positive, negative, or -- gasp ! -- NULL ? 

I don't see what your point is. That SQL is wrong ? Or that SQL is not "C" ? Or that SQL is not a "programming
language"? 

"?Que purposa sirve tanto comedia ?  Quien inventan tab miseria ?"

Greg Williamson
DBA
GLobeXplorer LLC

-----Original Message-----
From:    sad [mailto:sad@bankir.ru]
Sent:    Wed 6/23/2004 10:01 PM
To:    pgsql-sql@postgresql.org
Cc:
Subject:    Re: [SQL] feature request ?
On Wednesday 23 June 2004 21:12, you wrote:
> Sad,
>
> > since BOOL expression has three possible values: TRUE,FALSE,NULL
> > plpgsql IF control structure should have three alternate blocks:
> > THEN,ELSE,NULL
> >
> > shouldn't it ?
>
> No, why?
>
> How would you construct a tri-valued IF/THEN?     Doesn't seem too likely
> to me, as well as being different from every other programming language in
> existance ...

Three valued BOOLEAN is already different "from every other programming
language in existance"



---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
              http://www.postgresql.org/docs/faqs/FAQ.html

> I don't see what your point is. That SQL is wrong ? Or that SQL is not "C"
> ? Or that SQL is not a "programming language" ?

Who said wrong ? who said SQL ?

I thougth _WHY_
the IF control structure has exactly two alternate blocks ?
a BOOLEAN expression has exactly two possible values, that's why !

Well
in plpgsql we have tri-valued BOOL

Every programmer asks "how a NULL value treated?"
Now you treat NULLs as false. That's your point, but why ?

On Jun 24, 2004, at 1:49 PM, sad wrote:

> On Thursday 24 June 2004 09:32, Michael Glaesemann wrote:
>> Creating a new control structure to do handle this seems odd. However,
>> one could easily have the same effect using a nested if. Using the
>> pl/pgsql ELSIF construct, it's pretty straightforward.
>>
>> IF foo IS NULL
>>     THEN ...
>> ELSIF foo
>>     THEN ...
>> ELSE ...
>> END IF;
>
> here the foo expression will be executed twice

You're right, in that you couldn't use this in a CASE expression in 
pure SQL, but it would work in a pl/pgsql function, which would execute 
the expression once when it is called. Here's a very simple example:

test=# create or replace function foo_3val(boolean)    returns text    language plpgsql as '    declare        foo
aliasfor $1;    begin        if foo is null            then return ''foo is null'';        elsif foo            then
return''foo is true'';        else            return ''foo is false'';        end if;    end;    ';
 
CREATE FUNCTION
test=# create table foo_vals (foo_id serial unique not null, foo_val 
boolean);
NOTICE:  CREATE TABLE will create implicit sequence 
"foo_vals_foo_id_seq" for "serial" column "foo_vals.foo_id"
NOTICE:  CREATE TABLE / UNIQUE will create implicit index 
"foo_vals_foo_id_key" for table "foo_vals"
CREATE TABLE
test=# insert into foo_vals (foo_val) values (true);
INSERT 5076542 1
test=# insert into foo_vals (foo_val) values (false);
INSERT 5076543 1
test=# insert into foo_vals(foo_id) values(default);
INSERT 5076544 1
test=# select * from foo_vals; foo_id | foo_val
--------+---------      1 | t      2 | f      3 |
(3 rows)

test=# select foo_id, foo_3val(foo_val) from foo_vals; foo_id |   foo_3val
--------+--------------      1 | foo is true      2 | foo is false      3 | foo is null
(3 rows)

Michael Glaesemann
grzm myrealbox com

...IF ELSEIF ELSE
it's all clear
but what about unequality of BOOL type possible value set and IF alternatives
set

On Thu, Jun 24, 2004 at 11:04:15AM +0400, sad wrote:
> Now you treat NULLs as false.

Nope. NULL is neither true, nor false. It's "unknown", or "undefined".

fduch=# SELECT 1 WHERE NULL::boolean;?column?
----------
(0 rows)

fduch=# SELECT 1 WHERE NOT NULL::boolean;?column?
----------
(0 rows)

So if you care, you SHOULD use IS [NOT] NULL, as Michael Glaesemann
suggested. If you don't want expression to be calculated twice, use a
temporary variable.


-- 
Fduch M. Pravking

> > IF foo IS NULL
> > THEN ...
> > ELSIF foo
> > THEN ...
> > ELSE ...
> > END IF;
>
> here the foo expression woll be executed twice

if you can use an immutable or stable function then the overhead would be
minimal as the system knows that it doesn't need to re-evaluate it.

regards
Iain

On Thursday 24 June 2004 14:32, Alexander M. Pravking wrote:
> On Thu, Jun 24, 2004 at 11:04:15AM +0400, sad wrote:
> > Now you treat NULLs as false.
>
> Nope. NULL is neither true, nor false. It's "unknown", or "undefined".
>
> fduch=# SELECT 1 WHERE NULL::boolean;
>  ?column?
> ----------
> (0 rows)

DAMN !! Alex ! read the thread before answering !

On Thu, 24 Jun 2004, sad wrote:

> > I don't see what your point is. That SQL is wrong ? Or that SQL is not "C"
> > ? Or that SQL is not a "programming language" ?
>
> Who said wrong ? who said SQL ?
>
> I thougth _WHY_
> the IF control structure has exactly two alternate blocks ?
> a BOOLEAN expression has exactly two possible values, that's why !
>
> Well
> in plpgsql we have tri-valued BOOL
>
> Every programmer asks "how a NULL value treated?"
> Now you treat NULLs as false. That's your point, but why ?

It doesn't treat NULLs as false, it treats them as not true.

There's a least surprise issue here, most people expect if's then block to
run when it is true and the else block to run when it is not true (which
is false for most systems and false and NULL for this form of tri-valued
logic.)  If you were to add a NULL block you'd have to deal with things
like, if you only have a then and else, do you run the else on NULL or do
you do nothing?  If you do nothing, what if you want the null and else to
be the same, do you add another way to specify that?  If you do the else,
then the else stops making sense since it's sometimes false and sometimes
not true.

>  If you were to add a NULL block you'd have to deal with things
> like, if you only have a then and else, do you run the else on NULL or do
> you do nothing?  If you do nothing, what if you want the null and else to
> be the same, do you add another way to specify that?  If you do the else,
> then the else stops making sense since it's sometimes false and sometimes
> not true.

it is only syntax problem.
really we have more than one way to continue execution if one block is skipped
so your are free to define IF's behavior any way, particularly the way it is
defined now.

two-blocks IF is oviously enough to code ANY algorythm
but the three-blocks IF is more adequate to tri-valued BOOL

On Thu, 24 Jun 2004, sad wrote:

> >  If you were to add a NULL block you'd have to deal with things
> > like, if you only have a then and else, do you run the else on NULL or do
> > you do nothing?  If you do nothing, what if you want the null and else to
> > be the same, do you add another way to specify that?  If you do the else,
> > then the else stops making sense since it's sometimes false and sometimes
> > not true.
>
> it is only syntax problem.
> really we have more than one way to continue execution if one block is skipped
> so your are free to define IF's behavior any way, particularly the way it is
> defined now.

So you want to syntactically allow both ELSE and something like
FALSE/NULL? Or perhaps a different structure from IF entirely?

If you don't then you still run into questions like isIF booleanval THEN ... 1ELSE ... 2NULL ... 2ENDIF
different fromIF booleanval THEN ... 1ELSE ... 2ENDIF
because, if they're different, then lots of currently perfectly correct
programs break.  If they're the same, then ELSE has different meanings
depending on whether NULL is specified, and that's generally bad from an
understanding the language standpoint.

In addition, either adding a FALSE and NULL or just a NULL still involves
looking at the rest of the IF semantics to make sure they make sense.  How
do those interact with ELSIF blocks?

>  then lots of currently perfectly correct
> programs break.  If they're the same, then ELSE has different meanings
> depending on whether NULL is specified, and that's generally bad from an
> understanding the language standpoint.

i've already thougth on this
new control structure needed
but the name of the IF is perfect %-)

> In addition, either adding a FALSE and NULL or just a NULL still involves
> looking at the rest of the IF semantics to make sure they make sense.  How
> do those interact with ELSIF blocks?

that is because we used to two-valued BOOL...
but anyway i see no clear way to generalize ELSEIF.

may i suppose it was wrong to historically define IF as two-blocks control in
plpgsql ?
forget it.

On Thu, 24 Jun 2004, sad wrote:

> >  then lots of currently perfectly correct
> > programs break.  If they're the same, then ELSE has different meanings
> > depending on whether NULL is specified, and that's generally bad from an
> > understanding the language standpoint.
>
> i've already thougth on this
> new control structure needed
> but the name of the IF is perfect %-)

I was thinking that something like Cs switch might work. There's still a
question of the keywords because I don't like reusing case, but maybe
something of the general form:case foo is true is false is null

The general form looks more like:
case <expression> [when <expression>] then <statements> [is (true | false | null)] then <statements> [else]
<statements>endcase
 

With (unthoughtout) semantics likeThe case expression is evaluated once (although the below describes things in terms
ofexpressions of the case expression, it is meant to be indicative of the intent not the actual implementation)Each
when/is/elseclause is evaluated in order as follows: For a when clause, if the value of the case expression is  equal
tothe value of the when expression the statements  are run and the case is ended. For an is clause,  IS TRUE: if case
expressionIS TRUE is true then the   statements are run and the case is ended  IS FALSE: if case expression IS FALSE is
truethen the   statements are run and the case is ended  IS NULL: if case expression IS NULL is true then the
statementsare run and the case is ended For an else clause, run the statements (since no preceding   condition has
succeeded)

This is basically an extension of the syntax and semantics of one
of the case expression.  I don't think the above works keyword-wise
possibly, but it might be a reasonable starting point.

On Jun 24, 2004, at 6:39 PM, sad wrote:

> ...IF ELSEIF ELSE
> it's all clear
> but what about unequality of BOOL type possible value set and IF 
> alternatives
> set

In my opinion the short answer is NULL is here because of the SQL 
standard. The SQL standard does not specify any kind of "IF 
alternative" for 3-valued logic afaik. Why should PostgreSQL go beyond 
what the standard specifies in this hairy area? Three-valued logic is 
something I strive to stay away from to the best of my ability, as it 
is far too complicated for my feeble mind.

Michael Glaesemann
grzm myrealbox com

On Thu, Jun 24, 2004 at 07:34:18 -0700, Stephan Szabo <sszabo@megazone.bigpanda.com> wrote:
> 
> I was thinking that something like Cs switch might work. There's still a
> question of the keywords because I don't like reusing case, but maybe
> something of the general form:
>  case foo
>   is true
>   is false
>   is null

There already is a syntax like this. You can do:
CASE boolean_expression WHEN TRUE THEN whatever WHEN FALSE THEN whatever ELSE whatever
END

Bruno Wolff III wrote:
> 
> There already is a syntax like this. You can do:
> CASE boolean_expression
>   WHEN TRUE THEN whatever
>   WHEN FALSE THEN whatever
>   ELSE whatever
> END
> 

Besides, sad, there's no such thing as a tri-valued boolean.
You either have a boolean(with a true/false value) or a NULL, which is something 
completely different and it's _not_ one of the two _values_ that a boolean 
object takes. I think you're looking at this completely wrong. If in Java you 
receive a Boolean object instance, will you start claiming that that Java has 
tri-valued booleans, and the "if" should account for that ?!

You can think of values in plpgsql as wrapper objects that carry a value and 
have a "is_null" flag; I have no idea how they're implemented in PostgreSQL or 
in any RDMBS in general but this should do it, at least for a naive implementation.

Peace,
-- 
Radu-Adrian Popescu
CSA, DBA, Developer
Aldrapay MD
Aldratech Ltd.
+40213212243

On Thu, 24 Jun 2004, Bruno Wolff III wrote:

> On Thu, Jun 24, 2004 at 07:34:18 -0700,
>   Stephan Szabo <sszabo@megazone.bigpanda.com> wrote:
> >
> > I was thinking that something like Cs switch might work. There's still a
> > question of the keywords because I don't like reusing case, but maybe
> > something of the general form:
> >  case foo
> >   is true
> >   is false
> >   is null
>
> There already is a syntax like this. You can do:
> CASE boolean_expression
>   WHEN TRUE THEN whatever
>   WHEN FALSE THEN whatever
>   ELSE whatever
> END

True, but I think that mostly suffers from similar understandability
problems. :)

On Thu, Jun 24, 2004 at 12:32:59PM -0500, Jaime Casanova wrote:
>  
> Why not disallow the ability of boolean fields to be null?

Why not do it yourself?  That's what the NOT NULL constraint is for.

A

-- 
Andrew Sullivan  | ajs@crankycanuck.ca
I remember when computers were frustrating because they *did* exactly what 
you told them to.  That actually seems sort of quaint now.    --J.D. Baldwin

Very simply, a boolean may have to values: true or false.  It's also 
possible that it's not been set to anything (NULL).

-- 
Until later, Geoffrey                     Registered Linux User #108567
Building secure systems in spite of Microsoft

> Very simply, a boolean may have to values: true or false.  It's also
> possible that it's not been set to anything (NULL).

really ?
what about   (13 < NULL)::BOOL

On Fri, 25 Jun 2004 08:16:47 +0400, sad <sad@bankir.ru> wrote:

> > Very simply, a boolean may have to values: true or false.  It's also
> > possible that it's not been set to anything (NULL).

> really ?
> what about   (13 < NULL)::BOOL

Per the semantics of NULL, 13 is neither greater than nor less than
NULL.  NULL is the *unknown* value; it's impossible to meaningfully
compare it to anything else. Try (NULL = NULL)::boolean. It's NULL,
also.

Since no value, including NULL, is in any way definitively comparable
to NULL -- the unknown value -- comparing to NULL results in ...
unknown.

Otherwise known as NULL.

/rls

--
:wq

On Friday 25 June 2004 09:37, Rosser Schwarz wrote:
> On Fri, 25 Jun 2004 08:16:47 +0400, sad <sad@bankir.ru> wrote:
> > > Very simply, a boolean may have to values: true or false.  It's also
> > > possible that it's not been set to anything (NULL).
> >
> > really ?
> > what about   (13 < NULL)::BOOL
>
> Per the semantics of NULL, 13 is neither greater than nor less than
> NULL.  NULL is the *unknown* value; it's impossible to meaningfully
> compare it to anything else. Try (NULL = NULL)::boolean. It's NULL,
> also.

READ THE THREAD BEFORE ANSWER

sad wrote:
> On Friday 25 June 2004 09:37, Rosser Schwarz wrote:
> 
>>On Fri, 25 Jun 2004 08:16:47 +0400, sad <sad@bankir.ru> wrote:
>>
>>>>Very simply, a boolean may have to values: true or false.  It's also
>>>>possible that it's not been set to anything (NULL).
>>>
>>>really ?
>>>what about   (13 < NULL)::BOOL
>>
>>Per the semantics of NULL, 13 is neither greater than nor less than
>>NULL.  NULL is the *unknown* value; it's impossible to meaningfully
>>compare it to anything else. Try (NULL = NULL)::boolean. It's NULL,
>>also.
> 
> 
> READ THE THREAD BEFORE ANSWER

WHAT MAKES YOU THINK HE HASN'T?
OH, AND WHY ARE WE SHOUTING?

Your main argument seems to be: 1. A boolean value may have 3 states: true/false/null 2. The "if" statement has only
if/then/else/endif3. The "if" statement needs to be expanded to include "elsenull" or 
 
similar.

Now, you can argue for/against special-casing nulls, but all other 
languages cope with defined(), isset() etc. tests.

The key point of argument, and where the problem is with your (13 < 
NULL)::BOOL point is this: - Boolean _values_ can have one of two states: true/false - Boolean _variables_ can have one
ofthree states: true/false/null
 
What you had in your example was an undefined boolean variable, not a 
boolean value with a third state. You can argue that the null in 
question is of type boolean (I don't), but it is explicitly _not_ a value.

Now, your point that:  IF (...)=true
is not the same as  IF NOT((...)=false)
is a good one. Handling of NULLs causes a great deal of pain to 
inexperienced and experienced developers alike. You might be interested 
in the archives at http://www.dbdebunk.com/ which IIRC contains some 
articles arguing against nulls at all in a relational system.

--  Richard Huxton  Archonet Ltd

> sad wrote:
> > On Friday 25 June 2004 09:37, Rosser Schwarz wrote:
> >>On Fri, 25 Jun 2004 08:16:47 +0400, sad <sad@bankir.ru> wrote:
> >>>>Very simply, a boolean may have to values: true or false.  It's also
> >>>>possible that it's not been set to anything (NULL).
> >>>
> >>>really ?
> >>>what about   (13 < NULL)::BOOL
> >>
> >>Per the semantics of NULL, 13 is neither greater than nor less than
> >>NULL.  NULL is the *unknown* value; it's impossible to meaningfully
> >>compare it to anything else. Try (NULL = NULL)::boolean. It's NULL,
> >>also.
> >
> > READ THE THREAD BEFORE ANSWER
>
> WHAT MAKES YOU THINK HE HASN'T?

I had answered to the proposal to PROHIBIT NULL VALUES

> The key point of argument, and where the problem is with your (13 <
> NULL)::BOOL point is this:

IT IS NOT MY PROBLEM !!! it is an EXAMPLE WHY WE CAN NOT PROHIBIT NULLS !!!

sad wrote:
>>sad wrote:
>>
>>>On Friday 25 June 2004 09:37, Rosser Schwarz wrote:
>>>
>>>>On Fri, 25 Jun 2004 08:16:47 +0400, sad <sad@bankir.ru> wrote:
>>>>
>>>>>>Very simply, a boolean may have to values: true or false.  It's also
>>>>>>possible that it's not been set to anything (NULL).
>>>>>
>>>>>really ?
>>>>>what about   (13 < NULL)::BOOL
>>>>
>>>>Per the semantics of NULL, 13 is neither greater than nor less than
>>>>NULL.  NULL is the *unknown* value; it's impossible to meaningfully
>>>>compare it to anything else. Try (NULL = NULL)::boolean. It's NULL,
>>>>also.
>>>
>>>READ THE THREAD BEFORE ANSWER
>>
>>WHAT MAKES YOU THINK HE HASN'T?
> 
> 
> I had answered to the proposal to PROHIBIT NULL VALUES 

Umm - what proposal?

Geoffrey wrote:> Very simply, a boolean may have to values: true or false.  It's also> possible that it's not been set
toanything (NULL).
 

You replied:> really ?> what about   (13 < NULL)::BOOL

Which is an example where a boolean variable is undefined/not set/null.

In reply to you, Rosser Schwarz wrote:> Per the semantics of NULL, 13 is neither greater than nor less than> NULL.
NULLis the *unknown* value; it's impossible to meaningfully> compare it to anything else. Try (NULL = NULL)::boolean.
It'sNULL,> also.>> Since no value, including NULL, is in any way definitively comparable> to NULL -- the unknown value
--comparing to NULL results in ...> unknown.>> Otherwise known as NULL.
 

None of which suggests prohibiting nulls.

> 
>>The key point of argument, and where the problem is with your (13 <
>>NULL)::BOOL point is this:
> 
> 
> IT IS NOT MY PROBLEM !!! it is an EXAMPLE WHY WE CAN NOT PROHIBIT NULLS !!!

Umm - who is suggesting prohibiting nulls? I've re-read the entire 
thread and can't find any such suggestion. Is this one of those 
occasions where the different dialects of English are causing confusion?

--   Richard Huxton  Archonet Ltd

sad wrote:
>>You can think of values in plpgsql as wrapper objects that carry a value
>>and have a "is_null" flag; I have no idea how they're implemented in
>>PostgreSQL or in any RDMBS in general but this should do it, at least for a
>>naive implementation.
> 
> 
> 
> Why should i think on simple object MUCH more complicated than it in nature is 
> ?
> Let we discuss plpgsql here. Leave the Java to Javers.
> 
> 
> 

I _am_ discussing plpgsql here. And I'm also trying to shed some light on this one.

Now, you think of these things as "MUCH more complicated" because they are NOT 
simple objects, and if you think that having an integer value that can be NULL 
is complicated, perhaps you should stick to assembly.
Now, I'm no specialist on this, but it is quite obvious that any data type 
instance in SQL can have values from:
- its natural domain (arrays of characters, numbers, ip addresses, dates, binary 
data and so on)
- NULL

Now think of where else you've seen this. It's quite obvious that
"function foo(int)" in plpgsql is like "void foo(Integer i)" or like "void 
foo(int* i)" and NOT like "void foo(int i)". Now the latter does not put any 
problems to anyone, does it ? You check that the reference is set and then 
proceed to use it's value.

Also you might consider giving it a rest and stop shouting in the forum - it's 
not your back yard you know.

Peace,
-- 
Radu-Adrian Popescu
CSA, DBA, Developer
Aldrapay MD
Aldratech Ltd.
+40213212243

Umm - what proposal?

Umm - who is suggesting prohibiting nulls? I've re-read the entire
thread and can't find any such suggestion. Is this one of those
occasions where the different dialects of English are causing confusion?

--
Richard Huxton
Archonet Ltd