CASE expression and integer typing

[suhabe]

The following go-jet code is giving the error below on Postgres:

	var dest[]struct {
		NumPassed int64
	}
	
	err := SELECT(SUM(CASE().WHEN(Benchmark.Passed.IS_TRUE()).
				THEN(Int(1)).
				ELSE(Int(0))).AS("num_passed")).
		FROM(Benchmark).
		Query(tx, &dest)

pq: function sum(text) does not exist

It seems like its interpreting the CASE expression as a string when it should interpret it as Int. When I print out the SQL statement using DebugSql() and copy/paste it directly into Postgres's client, it works correctly. But through go-jet, I get the error above.

 db=# select SUM((CASE WHEN benchmark.passed THEN 1 ELSE 2 END)) AS "num_passed" from benchmark;

 num_passed 
------------
         12

(1 row)

[go-jet]

.DebugSql() is not the statement executed with Query(tx, &dest). It's parameterized statement returned with .Sql(). In parameterized statement ints are replaced with placeholder, and in situations like this, postgres interpreter does not have enough information to deduce the type of the CASE expression. Solution would be to cast CASE expression to integer:

SUM(
    CAST(CASE .....).AS_INTEGER(),
)

[suhabe]

That worked! Thank you very much.

[go-jet]

With the Release 2.7.0, expression casting can be avoided by using one of the bit specific integer literal constructor - Int8, Uint8, Int16 etc...
These literal constructor will automatically add a type cast for postgres queries.

err := SELECT(SUM(CASE().WHEN(Benchmark.Passed.IS_TRUE()).
			THEN(Int32(1)).
			ELSE(Int32(0))).AS("num_passed")).
	FROM(Benchmark).
	Query(tx, &dest)