sqlc Forgets the Column Name When Functions Applied on Both Sides

[mohammed90]

Correct Behavior

The following query:

-- name: selectUserByUsername :one
SELECT
	*
FROM
	users
WHERE 
	LOWER(username) = $1 AND age = $2
LIMIT 1;

Generates this Go code:

type selectUserByUsernameParams struct {
	Username string
	Age      sql.NullInt32
}

Unexpected Behavior

Note how here I applied the LOWER() function on both sides of the comparison:

-- name: selectUserByUsername :one
SELECT
	*
FROM
	users
WHERE 
	LOWER(username) = LOWER($1) AND age = $2
LIMIT 1;

The generated code ends up having Lower as the parameter name rather than Username, which is the column name:

type selectUserByUsernameParams struct {
	Lower string
	Age   sql.NullInt32
}

The AND age = $2 is just there to force the generation of the struct. Eliminating the AND age = $2 will still name the argument/parameter lower rather than username. I think the struct makes it more obvious to catch.

Playground

https://play.sqlc.dev/p/3d035e555d980f57e8a93672f9a1aec4a25fbcce02edff8946a528f3fc989b92

[kevinburke1]

Dupe of https://github.com/kyleconroy/sqlc/issues/229, I think.

[mohammed90]

Kinda but not exactly. It's expected if it were part of the selected columns, because SQL engines end up naming the column with that. It's unexpected when it's one of the comparison parameters of WHERE. It generates the correct field name if I omit the use of LOWER on the right side.

[joshdcuneo]

Not sure if this is the same issue but:

-- name: GetUserByEmailAndPassword :one
SELECT
  "email",
  "updated_at",
  "created_at",
  "id"
FROM
  "users"
WHERE
  "email" = $1
  AND "password" = crypt($2, "password")
LIMIT 1;

results in:

type GetUserByEmailAndPasswordParams struct {
	Email string `json:"email"`
	Crypt string `json:"crypt"`
}

but perhaps should be:

type GetUserByEmailAndPasswordParams struct {
	Email string `json:"email"`
	Password string `json:"password"`
}

[kyleconroy]

This has been fixed

https://play.sqlc.dev/p/8b270e64462166a51326f9c26712b83cc39b5e69c67617b6d14185604ab33e07

[mohammed90]

This has been fixed

https://play.sqlc.dev/p/8b270e64462166a51326f9c26712b83cc39b5e69c67617b6d14185604ab33e07

Is the field name becoming Lower expected behavior?

https://play.sqlc.dev/p/3d035e555d980f57e8a93672f9a1aec4a25fbcce02edff8946a528f3fc989b92

[kyleconroy]

Whoops, I checked the first case instead of the second case. Reopened

[chrislentz]

This happens when LOWER is used on the values side of an INSERT statement as well. The InsertUserParams struct name is "Lower" instead of "Username".

https://play.sqlc.dev/p/97df49dbc642f1d1c1a3e91c7594ea7da9e5ded863855db3af48b55d554b2e73

[fetchkash]

I was able to get around with by using sqlc's named parameters. My problem was when I had a query like

-- name: GetUserByFirstAndLastName :many
SELECT * FROM users
WHERE LOWER(first_name) LIKE LOWER($1) AND LOWER(last_name) LIKE LOWER($2);

the generated result was

type GetUserByFirstAndLastNameParams struct {
	Lower   string
	Lower_2 string
}

To get around this, I used named parameters:

-- name: GetUserByFirstAndLastName :many
SELECT * FROM users
WHERE LOWER(first_name) LIKE LOWER(@first_name::text) AND LOWER(last_name) LIKE LOWER(@last_name::text);

which resulted in

type GetUserByFirstAndLastNameParams struct {
	FirstName string
	LastName  string
}

Hope this helps.