We are sending it to the database but does it change the result? If not, is the current behaviour a blocker? Ecto can optimize this and not send it in the future though.
So, we are using Redshift (which is old PG forked by Amazon). With some tweaks to Elixir PG library it works.
In this example I passed only one case with dynamic name, so there is only one $1. From the error message I see it wants 5 == 6 and seems like $1 confuses it.
Also it looks like a bug in Redshift I started a thread on forum with them, but as this is a critical ticket for me right now I’m looking for anything to help with. As the alternative for now I see is to define a bunch of functions…, hope this won’t happen.
This is the error I got:
error: Assert
code: 1000
context: handled_cols == m_num_colflds - handled_cols:5 m_num_colflds:6
query: 3555961
location: tbl_trans.cpp:865
process: padbmaster [pid=21613]
Here is my query:
SELECT $1, coalesce(sum(CASE WHEN r0."shop_id" = $2 THEN r0."all_num_people" ELSE 0.0::float END), 0),
coalesce(sum(CASE WHEN r0."shop_id" = $3 THEN r0."all_num_people" ELSE 0.0::float END), 0),
coalesce(sum(CASE WHEN r0."shop_id" = $4 THEN r0."all_num_people" ELSE 0.0::float END), 0),
r0."shop_id", r0."status"
FROM "reservations" AS r0
WHERE (r0."local_start_at_with_day_offset"::date BETWEEN $5::date AND $6::date)
AND (r0."shop_id" IN ($7,$8,$9))
GROUP BY r0."status", r0."shop_id"
Here is params
["sum_num_people_all_shop_id_1", "5184018070f78ebe1600004b", "5d43f5ebc9714e49f60000e5",
"5184018070f78ebe16000048", ~D[2000-09-17], ~D[2019-10-16], "5184018070f78ebe1600004b",
"5d43f5ebc9714e49f60000e5", "5184018070f78ebe16000048"]
As a temporary step, maybe you are able of returning them as a list or a tuple, and then build the map on the result afterwards? Or that wouldn’t solve it either?
Also I’m trying to solve issue with Redshift support. They asked me for types passed for prepared statement (“Do you have the entire PREPARE statement that you are using? Any chance it is passing in UNKNOWN as one of the data types?”).
i.e. having this all is OK:
PREPARE name1(char, char, char) AS
SELECT $1,
coalesce(sum(CASE WHEN r0."shop_id" = $2 THEN r0."all_num_people" ELSE 0.0::float END), 0),
r0."provider",
r0."shop_id"
FROM "reservations" AS r0
WHERE (r0."shop_id" IN ($3))
AND (NOT (r0."provider" IS NULL))
GROUP BY r0."provider", r0."shop_id";
EXECUTE name1('sum_num_people_all_shop_id_1', '5184018070f78ebe1600004b', '5184018070f78ebe1600004b');
and this doesn’t:
PREPARE name3(unknown, char, char) AS
SELECT $1,
coalesce(sum(CASE WHEN r0."shop_id" = $2 THEN r0."all_num_people" ELSE 0.0::float END), 0),
r0."provider",
r0."shop_id"
FROM "reservations" AS r0
WHERE (r0."shop_id" IN ($3))
AND (NOT (r0."provider" IS NULL))
GROUP BY r0."provider", r0."shop_id";
EXECUTE name3('sum_num_people_all_shop_id_1', '5184018070f78ebe1600004b', '5184018070f78ebe1600004b');
Could you please point me where can I find out what types (char, char, char) ecto/postgrex is sending?
I digged postgres for a while, but no success, I think it is somewhere in postgrex/lib/postgrex/protocol.ex