I have e.g. the following string column containing either numbers or empty strings:

CREATE TABLE tst (s string);

INSERT INTO tst VALUES ('1234'), (''), (‘4567');

I’d like to cast ’s' to an INT column. The following query works:

sql>SELECT CAST(s AS INT) FROM (SELECT CASE WHEN s='' THEN NULL ELSE s END AS s FROM tst) AS tmp;

+------+
| L4 |
+======+
| 1234 |
| null |
| 4567 |
+------+
3 tuples

But the following query doesn’t:

sql>SELECT CASE WHEN s='' THEN NULL ELSE CAST(s AS INT) END FROM tst;

conversion of string '' to type int failed.

sql>SELECT CASE WHEN s='' THEN CAST(NULL as INT) ELSE CAST(s AS INT) END FROM tst;

conversion of string '' to type int failed.

This is probably because batcalc.int is applied on the whole “s” column as shown by the query plan:

sql>EXPLAIN SELECT CASE WHEN s='' THEN NULL ELSE CAST(s AS INT) END FROM tst;

+--------------------------------------------------------------------------------------------------------------------+

| mal |

+====================================================================================================================+

…

| C_7:bat[:oid] := sql.tid(X_6:int, "sys":str, "tst":str); |

| X_10:bat[:str] := sql.bind(X_6:int, "sys":str, "tst":str, "s":str, 0:int); |

| X_19:bat[:str] := algebra.projection(C_7:bat[:oid], X_10:bat[:str]); |

| X_21:bat[:bit] := batcalc.==(X_19:bat[:str], "":str); |

| X_24:bat[:bit] := batcalc.isnil(X_21:bat[:bit]); |

| X_30:bat[:bit] := batcalc.ifthenelse(X_24:bat[:bit], false:bit, X_21:bat[:bit]); |

| X_35:bat[:int] := batcalc.int(X_19:bat[:str]); |

| X_36:bat[:int] := batcalc.ifthenelse(X_30:bat[:bit], nil:int, X_35:bat[:int]); |

…

Is my query wrong or the plan?