Passing NULL not supported?

[meoyawn]

Problem: I want to pass NULL (nil) in some queries, and I'm getting :not_found

And I have to duplicate every query where null might be passed:

-- name: check_result
INSERT INTO checks (pid, roundtrip, ts)
VALUES             (:pid, :roundtrip, :ts)

-- name: check_null
INSERT INTO checks (pid, roundtrip, ts)
VALUES             (:pid, NULL, :ts)

There's no mention of nil in README, is this intentional?

BTW postgrex supports nil just fine https://github.com/elixir-ecto/postgrex/blob/master/README.md#data-representation

[alexdesousa]

Hey, thanks for reporting this :)

The problem is AyeSQL tries to find values for every mandatory parameter in the query and treats nil as a missing parameter. Though this shouldn't be the case, as NULL is a valid value in SQL.

The required change seems easy, but I need to check the possible consequences. I will try to patch this as soon as possible.

In the meantime, I have a possible workaround for you:

-- name: null
NULL

-- name: check_result
-- docs: Checks result.
INSERT INTO checks (pid, roundtrip, ts)
VALUES             (:pid, :roundtrip, :ts)

And then you can call this as:

iex> params = [pid: pid, roundtrip: &Queries.null/2, ts: ts]
iex> Queries.check_result(params)

Explanation:

• Queries without docs are considered private.
• Queries can be anything. AyeSQL does not check the SQL query is valid.
• Queries can be composed with other queries as long as the final result is a valid SQL query.

Hope this hepls.

[alexdesousa]

@adelnizamutdinov The fix worked perfectly, so you can ignore my proposed workaround and actually pass nil :) It's available in hex as v0.5.4

Again, thanks for reporting the issue :)

[meoyawn]

wow, that was fast :) already using it, thank you so much!