Alexander Litvinov wrote:
trying to make integer_field = '' --> but it shouldn't, becouse "CASE
WHEN '' <> ''" is false, so "ELSE integer_field = 0" should be used.

= 1".

Please execute this insert:
INSERT INTO bugtable (integer_field, text_field) VALUES (0, '1');

Then, executing both selects should return "1 rows fetched (0,02 sec)"...

Check it out.



---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Richard Huxton wrote:
Well, I must say that it is wird action... Why to check both elements of
CASE expression, if we know for sure that only one will be executed ?



---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Csaba Nagy wrote:
Maybe you are right. Thanks.




---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

On Friday 19 December 2003 09:02, Marek Lewczuk wrote:
But until it's evaluated ''<>'' it doesn't know it is false. When building the
CASE expression, it's trying to map types to each elements and notes that ''
is not an int. Only after this stage will it actually try to evaluate the
expression.

--
Richard Huxton
Archonet Ltd

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo (AT) postgresql (DOT) org so that your
message can get through to the mailing list cleanly

Marek Lewczuk <newsy (AT) lewczuk (DOT) com> writes:
It cannot discover that the WHEN clause is constant-false until after it
has performed syntactic analysis --- which includes assigning datatypes
to all elements of the expression.

In theory perhaps we could do syntactic analysis of the WHEN part, then
stop and do constant-folding to see if we could prove that the WHEN is
always false before we move on to syntactic analysis of the THEN part.
In practice, no one does it that way --- it would arguably be illegal to
do so according to the SQL spec, which draws a very clear distinction
between parse-time checking and execution-time activity. Early constant
folding would have unpleasant properties for user-defined functions,
too. You might find that a change you make in one of your functions
doesn't show up in the behavior of some existing rule, because the call
to that function in the rule got constant-folded before the rule was
stored.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

This is normaol behavior.

Here CASE have int type and you are trying to cast '' to int. Error.

Here CASE have text type. Everything is fine.


---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Because that's a syntax check, which does NOT evaluate any of the
expressions, only the types of them. Even if it will not be executed
ever, it is still wrong, and it should generate an error, because it
means a mistake on your side which potentially could leave to results
you didn't expect if executed as is.
If it would not generate error in these cases, you would scream when you
by mistake write a wrong query and it doesn't give you the right errors.
Say thanks to the parser that it catches your errors and doesn't let you
do detective work to find out why your queries are not working as you
expect them to work.

Cheers,
Csaba.



---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo (AT) postgresql (DOT) org