Thread: CASE in where statement. BUG ??
I belive that I have found a bug - or maybe it was done on purpose. Have a look at this query: SELECT integer_field FROM bugtable WHERE (CASE WHEN '' <> '' THEN integer_field = '' ELSE integer_field = 0 END); Result: ERROR: invalid input syntax for integer: "" SELECT text_field FROM bugtable WHERE (CASE WHEN '' <> '' THEN text_field = '' ELSE text_field = '1' END); Result: 1 rows fetched (0,02 sec) Where table structure is: CREATE TABLE "public"."bugtable" ( "integer_field" INTEGER, "text_field" TEXT ) WITH OIDS; I belive that PG reaction should be the same as in the second query -- there shouldn't be any errors. $ psql --version psql (PostgreSQL) 7.4 contains support for command-line editing Windows XP/Cygwin If this is not a bug, please tell me what is wrong. ML
Alexander Litvinov wrote: > This is normaol behavior. > > >>I belive that I have found a bug - or maybe it was done on purpose. Have >>a look at this query: >> >> >>SELECT integer_field FROM bugtable WHERE (CASE WHEN '' <> '' THEN >>integer_field = '' ELSE integer_field = 0 END); >>Result: ERROR: invalid input syntax for integer: "" > > > Here CASE have int type and you are trying to cast '' to int. Error. Well, I see that you have not run this query: In this case the query is trying to make integer_field = '' --> but it shouldn't, becouse "CASE WHEN '' <> ''" is false, so "ELSE integer_field = 0" should be used. > > >> >>SELECT text_field FROM bugtable WHERE (CASE WHEN '' <> '' THEN >>text_field = '' ELSE text_field = '1' END); >>Result: 1 rows fetched (0,02 sec) > > > Here CASE have text type. Everything is fine. Yes, becouse "CASE WHEN '' <> ''" is send false and is using "text_field = 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.
On Friday 19 December 2003 09:02, Marek Lewczuk wrote: > Alexander Litvinov wrote: > > This is normaol behavior. > > > >>I belive that I have found a bug - or maybe it was done on purpose. Have > >>a look at this query: > >> > >> > >>SELECT integer_field FROM bugtable WHERE (CASE WHEN '' <> '' THEN > >>integer_field = '' ELSE integer_field = 0 END); > >>Result: ERROR: invalid input syntax for integer: "" > > > > Here CASE have int type and you are trying to cast '' to int. Error. > > Well, I see that you have not run this query: In this case the query is > trying to make integer_field = '' --> but it shouldn't, becouse "CASE > WHEN '' <> ''" is false, so "ELSE integer_field = 0" should be used. 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
Richard Huxton wrote: > 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. > 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 ?
Csaba Nagy wrote: > 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. Maybe you are right. Thanks. > > Cheers, > Csaba. > > > >
Marek Lewczuk <newsy@lewczuk.com> writes:
> 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 ?
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
This is normaol behavior. > I belive that I have found a bug - or maybe it was done on purpose. Have > a look at this query: > > > SELECT integer_field FROM bugtable WHERE (CASE WHEN '' <> '' THEN > integer_field = '' ELSE integer_field = 0 END); > Result: ERROR: invalid input syntax for integer: "" Here CASE have int type and you are trying to cast '' to int. Error. > > > SELECT text_field FROM bugtable WHERE (CASE WHEN '' <> '' THEN > text_field = '' ELSE text_field = '1' END); > Result: 1 rows fetched (0,02 sec) Here CASE have text type. Everything is fine.
> 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 ? 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.