So I bumped into this weird behaviour:
query = from(p in Post, select: {:foo, p.id})
from(p in Post)
|> select([p], {"bar", p.id})
|> union_all(^query)
|> Repo.all()
Looks like bar
string is passed to the query, while :foo
atom is not, so the queries end up having different number of columns:
** (Exqlite.Error) SELECTs to the left and right of UNION ALL do not have the same number of result columns
SELECT 'bar', p0."id" FROM "posts" AS p0 UNION ALL SELECT p0."id" FROM "posts" AS p0
(ecto_sql 3.12.1) lib/ecto/adapters/sql.ex:1096: Ecto.Adapters.SQL.raise_sql_call_error/1
(ecto_sql 3.12.1) lib/ecto/adapters/sql.ex:994: Ecto.Adapters.SQL.execute/6
(ecto 3.12.4) lib/ecto/repo/queryable.ex:232: Ecto.Repo.Queryable.execute/4
(ecto 3.12.4) lib/ecto/repo/queryable.ex:19: Ecto.Repo.Queryable.all/3
ecto_sql.exs:50: Main.main/0
ecto_sql.exs:55: (file)
There’s also an interesting interaction if both queries use an atom:
query = from(p in Post, select: {:total_views, sum(p.views)})
from(p in Post)
|> select([p], {:highest_views, max(p.views)})
|> union_all(^query)
|> Repo.all()
|> IO.inspect()
# [highest_views: 15, highest_views: 25]
Is this a bug or a known limitation perhaps?
Here's a one file example for anyone interested
Mix.install([
{:ecto_sql, "~> 3.12"},
{:ecto_sqlite3, "~> 0.17.4"}
])
Application.put_env(:foo, Repo, database: "mix_install_examples.db")
defmodule Repo do
use Ecto.Repo, adapter: Ecto.Adapters.SQLite3, otp_app: :foo
end
defmodule Migration0 do
use Ecto.Migration
def change do
create table("posts") do
add(:title, :string)
add(:views, :integer)
timestamps(type: :utc_datetime_usec)
end
end
end
defmodule Post do
use Ecto.Schema
schema "posts" do
field(:title, :string)
field(:views, :integer)
timestamps(type: :utc_datetime_usec)
end
end
defmodule Main do
import Ecto.Query, warn: false
def main do
Repo.__adapter__().storage_down(Repo.config())
:ok = Repo.__adapter__().storage_up(Repo.config())
{:ok, _} = Supervisor.start_link([Repo], strategy: :one_for_one)
Ecto.Migrator.run(Repo, [{0, Migration0}], :up, all: true, log_migrations_sql: :info)
Repo.insert!(%Post{
title: "Post 1",
views: 10
})
Repo.insert!(%Post{
title: "Post 2",
views: 15
})
query = from(p in Post, select: {:total_views, sum(p.views)})
from(p in Post)
|> select([p], {:highest_views, max(p.views)})
|> union_all(^query)
|> Repo.all()
|> IO.inspect()
end
end
Main.main()