Skip to content
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.

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

Using MYSQL functions ( CONVERT and CAST) result in removing column from struct #1622

Closed
KarimEKhalifa opened this issue May 16, 2022 · 2 comments

Comments

@KarimEKhalifa
Copy link

KarimEKhalifa commented May 16, 2022

Version

1.13.0

What happened?

When creating an insert query for a json column, sqlc does not generate the column name in the golang struct if I try to add CAST(CONVERT( ? USING utf8) AS JSON)

From playing around with MySql and Postgres in the playground, I've noticed that it works fine in Postgres but not when using MySql

Postgres playground:
https://play.sqlc.dev/p/ea800eff2a7beb25bc960f7d839ea96039b5dad0c46f16dddd97f76dc28d3388

MySql playground:
https://play.sqlc.dev/p/38ec6bf15657aa79f4a3dd7c72c559934038a591625869b2dfe59a87498f7b1f

Relevant log output

No response

Database schema

CREATE TABLE product_versions (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    product_id INT NOT NULL,
    product_key VARCHAR(255) NOT NULL UNIQUE,
    version INT NOT NULL,
    type ENUM ('LARGE', 'MEDIUM', 'SMALL') NOT NULL, 
    status ENUM ('ACTIVE', 'DRAFT') NOT NULL,
    attributes JSON,
    created_at TIMESTAMP NOT NULL,
    created_by INT NOT NULL
);

SQL queries

-- name: CreateProductVersion :execresult
INSERT INTO product_versions (
        product_id,
        product_key,
        version,
        type,
        status,
        attributes,
        created_at,
        created_by
    ) VALUES (
        ?, ?, ?, ?, ?, CAST(CONVERT( ? USING utf8) AS JSON), NOW(), ?
);

Configuration

version: "1"
project:
  id: "project"
packages:
  - name: "sqlc"
    path: "./sqlc"
    queries: "./sqlc/query/"
    schema: "./migrations"
    engine: "mysql"
    emit_json_tags: true
    emit_interface: true
    emit_exact_table_names: false

Playground URL

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

What operating system are you using?

macOS

What database engines are you using?

MySQL

What type of code are you generating?

Go

@KarimEKhalifa KarimEKhalifa added bug Something isn't working triage New issues that hasn't been reviewed labels May 16, 2022
@ryanpbrewster
Copy link

ryanpbrewster commented May 28, 2022

FWIW, I'm pretty sure the root cause here is very similar to the one for #1648, namely that convertFuncCastExpr is unimplemented here for mysql.

It seemed a bit tricky to implement because of the TypeName value that needs to be populated in the ast.TypeCast node. The postgres engine implements this as:

	return &ast.TypeCast{
		Arg:      convertNode(n.Arg),
		TypeName: convertTypeName(n.TypeName),
		Location: int(n.Location),
	}

but that info is not as easily accessible in the mysql engine.

You can verify this by setting the SQLCDEBUG=1 environment variable when you run sqlc. If I am correct you should see error logs like

2022/05/28 13:23:17 dolphin.convert: Unknown node type *ast.RowExpr

but for a FuncCastExpr

@kyleconroy kyleconroy added 📚 mysql 🔧 golang 💻 darwin and removed triage New issues that hasn't been reviewed labels Jun 4, 2022
kyleconroy pushed a commit that referenced this issue Jul 30, 2023
What is this

As the title said, this PR wants to add support for CAST function in MySQL.

This PR is based from PR by @ryanpbrewster here (which unfortunately he didn't send here, and only exist in his repository).
Why is this PR created

Currently sqlc unable to infer the correct type from SQL function like MAX, MIN, SUM, etc. For those function, sqlc will return its value as interface{}. This behavior can be seen in this playground.

As workaround, it advised to use CAST function to explicitly tell what is the type for that column, as mentioned in #1574.

Unfortunately, currently sqlc only support CAST function in PostgreSQL and not in MySQL. Thanks to this, right now MySQL users have to parse the interface{} manually, which is not really desirable.
What does this PR do?

    Implement convertFuncCast function for MySQL.
    Add better nil pointer check in some functions that related to convertFuncCast.

I haven't write any test because I'm not sure how and where to put it. However, as far as I know the code that handle ast.TypeCast for PostgreSQL also don't have any test, so I guess it's fine 🤷‍♂️
Related issues

Support CAST ... AS #687, which currently is the oldest MySQL issue that still opened.
Using MYSQL functions ( CONVERT and CAST) result in removing column from struct #1622
Unable to Type Alias #1866
sum in select result in model field type interface{} #1901
MIN() returns an interface{} #1965
@kyleconroy
Copy link
Collaborator

"Fixed" in sqlc 1.21

type CreateProductVersionParams struct {
	ProductID  int32
	ProductKey string
	Version    int32
	Type       ProductVersionsType
	Status     ProductVersionsStatus
	CONVERT    interface{}
	CreatedBy  int32
}

Obviously an all-uppercase field name with an interface{} type isn't ideal here.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

Successfully merging a pull request may close this issue.

3 participants