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

[KarimEKhalifa]

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

[ryanpbrewster]

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]

"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.