Skip to content

case expression returns interface{} #2937

New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom
Open
araufdogan opened this issue Nov 3, 2023 · 3 comments
Open

case expression returns interface{} #2937

araufdogan opened this issue Nov 3, 2023 · 3 comments
Labels
analyzer 📚 mysql bug Something isn't working

Comments

@araufdogan
Copy link

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
@araufdogan araufdogan added bug Something isn't working triage New issues that hasn't been reviewed labels Nov 3, 2023
@andrewmbenton andrewmbenton added 📚 mysql analyzer and removed triage New issues that hasn't been reviewed labels Nov 3, 2023
@andrewmbenton
Copy link
Collaborator

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
Copy link
Collaborator

Re-opening since this is a real issue that I'd like to see closed with #2902.

@andrewmbenton andrewmbenton reopened this Nov 3, 2023
@BonnieMilianB
Copy link

BonnieMilianB commented Jan 17, 2024 •
edited
Loading

adding to this, if I use INT or BIGINT I received this error
Screenshot 2024-01-17 at 2 43 12 p m

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.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
analyzer 📚 mysql bug Something isn't working
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
3 participants
@andrewmbenton @araufdogan @BonnieMilianB