search

rmculpepper / sql

Embedding of some of SQL into Racket
33 stars 5 forks source link

Casting to some of the integer types generates invalid queries #17

Open Bogdanp opened 5 years ago

Bogdanp commented 5 years ago

I use a bunch of custom domains in my application for validation and, because those domains get assigned custom type ids which this library doesn't (I think) support, I end up having to cast various columns. I've noticed that casting to int4 and int8 and text works just fine

> (sql-ast->string (scalar-expr-qq (select (cast 1 int4))))
"(SELECT CAST(1 AS int4))"

> (sql-ast->string (scalar-expr-qq (select (cast 1 int8))))
"(SELECT CAST(1 AS int8))"

> (sql-ast->string (scalar-expr-qq (select (cast 1 text))))
"(SELECT CAST(1 AS text))"

but casting to integer produces invalid sql:

(sql-ast->string (scalar-expr-qq (select (cast 1 integer))))
"(SELECT CAST(1 AS \"INTEGER\"))"

Note the quotes around INTEGER. In Postgres, executing this query produces an error:

ERROR:  42704: type "integer" does not exist
LINE 1: select cast(1 as "integer");
                         ^
LOCATION:  typenameType, parse_type.c:257
Time: 0.454 ms

Casting to smallint, bigint, decimal or numeric exhibits the same behavior.