[BUG]: Custom select field type coercion does not work with `count(1)`

[andrewcohen]

What version of drizzle-orm are you using?

0.23.8

Describe the Bug

On postgresql given the following query, the data type of fail is returned as a string although the inferred typescript type is inferred as a number.

let x = await db
    .select({
      ok: sql<number>`1`,
      fail: sql<number>`count(1)`,
    })
    .from(sql`generate_series(0,1,1)`);

// Returns: 
// Query: select 1, count(1) from generate_series(0,1,1)
// [ { ok: 1, fail: '2' } ]

// Inferred type of x
let x: {
    ok: number;
    fail: number;
}[]

I have not tested this on other database engines.

[dankochetov]

It is inferred as a number because you specified <number> as the sql generic. It doesn't change the actual value returned from the DB, it specifies its expected type.

[andrewcohen]

Makes sense. Given that postgres uses bigint for the count result, what would be the appropriate type to use for the generic? Is this a cast that drizzle is expected to handle or somewhere else?

select pg_typeof(1) as ok, pg_typeof(count(1)) as fail from generate_series(0,1,1);
   
-[ RECORD 1 ]-
ok   | integer
fail | bigint

[relsunkaev]

It seems select(sql`count(...)`) returns a string in all cases for some reason.

[Angelelz]

We now have the count() function that returns a number in all cases.