Min/Max on Datetimes result in interface{} as type

[Neokil]

Version

Other

What happened?

Hint: I am using v1.13.0 (installed via brew) but that is not available in the form.

I currently have a query that looks like this:

SELECT COUNT(*) as NumOfActivities,
        MIN(event_time) as MinDate, 
        MAX(event_time) as MaxDate 
FROM activities 
WHERE account_id = $1

which creates a struct like this:

type GetActivityMetaDataForAccountRow struct {
	Numofactivities int64
	Mindate         interface{}
	Maxdate         interface{}
}

but I would expect

type GetActivityMetaDataForAccountRow struct {
	Numofactivities int64
	Mindate         time.Time
	Maxdate         time.Time
}

so it looks like after putting a date through an aggregate-function sqlc can no longer determine its type? but it seems only to be the case for datetimes as far as I can see it.

Relevant log output

No response

Database schema

CREATE TABLE activities (
    account_id BIGINT NOT NULL,
    event_time TIMESTAMP WITH TIME ZONE NOT NULL, 
);

SQL queries

SELECT COUNT(*) as NumOfActivities,
        MIN(event_time) as MinDate, 
        MAX(event_time) as MaxDate 
FROM activities 
WHERE account_id = $1

Configuration

No response

Playground URL

No response

What operating system are you using?

macOS

What database engines are you using?

PostgreSQL

What type of code are you generating?

Go

[jose-zenledger]

Yeah I am experiencing this too. So then I changed it to cast the min or max so like:
MIN(event_time)::timestamp as MinDate. However it fails when it returns null.

I then thought maybe I could use nulltime as a custom type but and I can't figure out how to use a custom type for custom queries like this.

[Codelax]

I have this problem too. I would like to get *time.Time but I cannot get it when overriding type on aggregate.

[kdubovikov]

This problem reproduces not only for timestamp fields, but for int fields as well.

with t1 as (
	select
		id,
		min(col) as min_col
	from
		table1
	group by
		id
)
select
	t1.min_col
from
	t2
left outer join t1 on t2.id = t1.id

May be related to #1334

UPDATE I just built sqlc from the git to include all latest commits and this query still results into incorrect struct. min_col ends up being interface{}

UPDATE 2
Checked that this simple query also does not work

select
		id,
		min(col) as min_col -- col is an int column
from
		table1
group by
		id

[kdubovikov]

I think the problem lies in that definitions of min function in pg_catalog do not use Function.ReturnTypeIsNull field in any way. Here is the example: https://github.com/kyleconroy/sqlc/blob/996a73a27144a625080326f7340a11f837bd94fe/internal/engine/postgresql/pg_catalog.go#L14286

But here this field is the only thing considered when we are inferring if the argument of the function is nullable or not https://github.com/kyleconroy/sqlc/blob/996a73a27144a625080326f7340a11f837bd94fe/internal/compiler/output_columns.go#L219

To solve this, I think we need to make so that for some built-in functions the output type should be inferred from their argument.

UPDATE
Ok, so I found how we can fix this.
https://github.com/kyleconroy/sqlc/blob/996a73a27144a625080326f7340a11f837bd94fe/internal/compiler/output_columns.go#L208

Here, we can parse function parameters using outputColumnRefs and check if the argument type is nullable. Then, we can pass this info to Column structure. Minimalistic implementation that will work only for min looks like this (pardon for horrible code):

		case *ast.FuncCall:
			rel := n.Func
			name := rel.Name
			fmt.Printf("FUNC CALL %s\n", name)
			if res.Name != nil {
				name = *res.Name
			}
			fun, err := qc.catalog.ResolveFuncCall(n)

			if err == nil {
				// CHANGE HERE
				if len(fun.Args) == 1 && fun.Name == "min" {
					ref := n.Args.Items[0].(*ast.ColumnRef)
					columns, err := outputColumnRefs(res, tables, ref)
					if err != nil {
						panic(err)
					}
					fmt.Println(columns[0].Name)
					fmt.Println(columns[0].NotNull)
					fmt.Println(columns[0].Table.Name)

					cols = append(cols, &Column{
						Name:       name,
						DataType:   columns[0].DataType,
						NotNull:    columns[0].NotNull,
						IsFuncCall: true,
					})
				// END CHANGE
				} else {
					cols = append(cols, &Column{
						Name:       name,
						DataType:   dataType(fun.ReturnType),
						NotNull:    !fun.ReturnTypeNullable,
						IsFuncCall: true,
					})
				}
			} else {
				cols = append(cols, &Column{
					Name:       name,
					DataType:   "any",
					IsFuncCall: true,
				})
			}

The code above solves issue for group by queries using min function only.

The problem is I am unsure for which functions we should apply such type inference and for which not. That will be dependent on the database and likely should be implemented somewhere at the catalog level. Also, aggregation functions may have array parameters, so those cases should be handled correctly too: https://www.postgresql.org/docs/9.5/functions-aggregate.html

@kyleconroy , do you have an idea on how this issue should be handled? From the postgres doc it looks like all aggregation functions can return null, so maybe just mark all aggregation functions in catalogs for postgres, dolphin and sqlite? By that I mean to fill ReturnTypeNullable = true for every aggregation function. That would mean modifying sql-pc-gen somehow. And making manual edits for dolphin and sqlite catalogs.
For sql-pc-gen, I think this can be done by setting ReturnTypeNullable = true for all functions with prokind = 'a'. That is, for all aggregation functions.

[ryan-berger]

I'm not the maintainer, but I can take a look at this soon because I hit this issue in our production project. I believe a refactor of this portion of the codebase just happened, so the preferred solution may change.

I was able to get around this for the time being by using:

COALESCE(extract(epoc from ....)), 0)

And then just using time.Unix(myIntColumn, 0) and using t.IsZero() to check for null times. Not ideal, but it will work until this issue gets closed.

[kdubovikov]

@ryan-berger I think I have solved the issue in the linked pull request, but I need a bit more help from maintainers to fix tests

[ryan-berger]

@kdubovikov You are correct, it would just be sqlc-pg-gen that needs to change, and that the refactor didn't change much if Kyle goes with your other solution.

I was thinking about the sqlc-pg-gen a bit more:

For sql-pc-gen, I think this can be done by setting ReturnTypeNullable = true for all functions with prokind = 'a'. That is, for all aggregation functions.

And I realized that COUNT has a prokind of a, but cannot return a null value it will return 0 instead.

The Postgres docs say:

It should be noted that except for count, these functions return a null value when no rows are selected.

Looking at pg_catalog, I can't find any difference between MIN and COUNT that we could use to determine this, so we likely just need to make it a special case in the sql-pg-gen. I don't really like the idea of this though because it would be nice if we could determine nullability of the return type without manually annotating pg_catalog functions.

@kyleconroy any thoughts on this?

[kyleconroy]

A few thoughts. We should manually tag the aggregate functions that can return null. I honestly do do not know that min and max would return null.

As for type inference, it's a very large project to take on.

[jose-zenledger]

A few thoughts. We should manually tag the aggregate functions that can return null. I honestly do do not know that min and max would return null.

As for type inference, it's a very large project to take on.

I think it returns null if there are no rows in the set so:

SELECT MAX(amount) FROM sales WHERE product = 'something_that_does_not_exist';