case expression returns interface{}

[araufdogan]

Version

1.23.0

What happened?

When I run sqlc generate, it generates interface{} for my custom select fields. I couldn't find how to define type for this fields. Is there any way to do it?

Relevant log output

No response

Database schema

CREATE TABLE test_table (
  id int unsigned NOT NULL AUTO_INCREMENT,
  val1 int NOT NULL,
  val2 int NOT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

SQL queries

-- name: GetData :many
SELECT test_table.*, 
CASE
WHEN test_table.val1 = 100 THEN test_table.val1 ELSE test_table.val2
END AS final_val
from test_table;

Configuration

{
  "version": "2",
  "sql": [{
    "schema": "schema.sql",
    "queries": "query.sql",
    "engine": "mysql",
    "gen": {
      "go": {
        "out": "db"
      }
    }
  }]
}

Playground URL

https://play.sqlc.dev/p/ded46511e671b26f426512a2248d30b6d7707ca404b55d163d0c1705e744791c

What operating system are you using?

macOS

What database engines are you using?

MySQL

What type of code are you generating?

Go

[andrewmbenton]

This is a case where sqlc's built-in type inference isn't good enough. A forthcoming MySQL database-backed analyzer (like the one we have for PostgreSQL) would presumably figure out the type of final_val, but until then you may be able to work around using the CAST() function. Here's a playground link to demo: https://play.sqlc.dev/p/e967e16a7f20b287f171e0ba43d616a3290276b5a33d064489ab94d71bfd6908

I can't see a way to get MySQL to CAST() to a regular INT, but this convinces sqlc to return an int64 at least:

-- name: GetData :many
SELECT test_table.*, 
CAST(
  CASE
    WHEN test_table.val1 = 100 THEN test_table.val1 ELSE test_table.val2
  END
AS SIGNED) AS final_val
from test_table;

[andrewmbenton]

Re-opening since this is a real issue that I'd like to see closed with https://github.com/sqlc-dev/sqlc/issues/2902.

[BonnieMilianB]

adding to this, if I use INT or BIGINT I received this error

only SIGNED and UNSIGNED worked.

And if I don't do the CAST, the fields are set as interface type, and even casting manually to int64 the data returned is wrong, it didn't worked the interface type.