Named parameters don't work in sqlc with MySQL

[AndrewRayCode]

What happened?

Version:

sqlc version
v1.15.0

Running sqlc generate on this query:

SELECT *
FROM table
WHERE CASE WHEN TRUE THEN `status` IN ( @HELP::int[] ) ELSE FALSE END;

Fails to parse, with:

queries.sql:72:47: syntax error near "::int[] ) ELSE FALSE END;

Trying to use ANY like the docs say doesn't work, it was unclear if this was postgres or mysql syntax at first, but for some reason the docs seem to hard code postgres syntax, and the playground only supports postres:

SELECT *
FROM table
WHERE CASE WHEN TRUE THEN `status` = ANY(@HELP::int[]) ELSE FALSE END;

Fails to parse, with

queries.sql:72:47: syntax error near "@HELP::int[]) ELSE FALSE END;"

(it's weird it fails to parse on a different place, not the ANY which is invalid mysql syntax

Ok, let's try with arg() instead

SELECT *
FROM table
WHERE CASE WHEN TRUE THEN `status` IN ( sqlc.arg('statuz') ) ELSE FALSE END;

So sqlc generate passes here, except it generates a null string, not a slice

func (q *Queries) DoTheThing(ctx context.Context, statuz sql.NullString)

ok the docs tell us to append ::int[] as a type hint, so

SELECT *
FROM table
WHERE CASE WHEN TRUE THEN `status` IN ( sqlc.arg('statuz')::int[] ) ELSE FALSE END;

Crashes sqlc once again:

queries.sql:72:61: syntax error near "::int[] ) ELSE FALSE END;"

Ok, not sure what's going on, named arguments don't seem supported. Let's try them in a different place:

SELECT *
FROM table
WHERE CASE WHEN sqlc.arg('test') THEN `status` IN ( ? ) ELSE FALSE END;

Now we have three problems:

sqlc generate
unsupported reference type: <nil>

• Broken error message
• But exit status code of sqlc is successful
• Generates what appears to be broken sql: ... WHERE CASE WHEN ? and there is no test argument added to the generated golang code

Ok maybe it's missing a type hint?

SELECT *
FROM table
WHERE CASE WHEN sqlc.arg('test')::bool THEN `status` IN ( ? ) ELSE FALSE END;

Except that crashes the parser again:

queries.sql:72:36: syntax error near "::bool THEN `status` IN ( ? ) ELSE FALSE END;"

Should we consider the mysql as supported in sqlc, or something in beta/experimental mode? It does not appear usable, and the docs don't mention how to use MySQL syntax, unless I'm missing something obvious

[AndrewRayCode]

I see this is at partly a dupe of #695 - where slices aren't supported in MySQL. I'm not sure about the CASE WHEN clause and the additional parse errors

[kyleconroy]

MySQL is fully supported in sqlc. Many of the queries in your bug report aren't valid, hence the compilation failures. First, the shorthand CAST operator (::) isn't supported by MySQL. You'll need to use the full CAST syntax instead. MySQL also does not support array types, which is covered in #695.

Feel free to open up a new issue if you run into other issues.

[Fajar-Islami]

helo @kyleconroy

UPDATE users 
SET 
    first_name = IF(CAST(sqlc.arg(set_first_name) AS boolean) = true, CAST(sqlc.arg(first_name) AS varchar), first_name),
    last_name = IF( CAST(sqlc.arg(set_last_name) AS boolean)  = true, CAST(sqlc.arg(last_name) AS varchar), last_name),
    avatar = IF( CAST(sqlc.arg(set_avatar) AS boolean)  = true, CAST(sqlc.arg(avatar) AS TEXT), avatar),
    updated_at = now()
WHERE id = ? and deleted_at is null;

why i igot error

sqlc/query/users.sql:24:61: syntax error near "boolean) = true, CAST(sqlc.arg(first_name) AS varchar), first_name),"

i use mysql v8

[Fajar-Islami]

I feel that SQLC is only suitable for PostgreSQL.

[Fajar-Islami]

and I also encountered an issue when using @ as a replacement for sqlc.arg in MySQL, where "@" doesn't work as a replacement for sqlc.arg() on MySQL."

[pexarkh]

I feel that SQLC is only suitable for PostgreSQL.

i'm using it with sqlite. sqlc for sqlite is still buggy but already good enough. hope it'll be as good as for postgres soon.

[qazwsxedckll]

-- name: UpdateAuthorName :exec
UPDATE author
SET name = CASE WHEN CAST( sqlc.arg('set_name') AS CHAR )
    THEN sqlc.arg(myname)
    ELSE name
    END;


const updateAuthorName = `-- name: UpdateAuthorName :exec
UPDATE author
SET name = CASE WHEN CAST( sqlc.arg('set_name') AS CHAR )
    THEN ?
    ELSE name
    END
`

not working

[zzhaolei]

-- mysql
CASE WHEN sqlc.arg(is_version) THEN json_extract(extra, '$.version') = sqlc.arg(version) ELSE TRUE END

version is of type int, but I cannot specify it, and sqlc generates the version type as a struct.