1Code.require_file "../../../integration_test/support/types.exs", __DIR__
2
3defmodule Ecto.Query.SubqueryTest do
4  use ExUnit.Case, async: true
5
6  import Ecto.Query
7
8  alias Ecto.Query.Planner
9  alias Ecto.Query.JoinExpr
10
11  defmodule Comment do
12    use Ecto.Schema
13
14    schema "comments" do
15      field :text, :string
16      field :temp, :string, virtual: true
17      belongs_to :post, Ecto.Query.SubqueryTest.Post
18      has_many :post_comments, through: [:post, :comments]
19    end
20  end
21
22  defmodule Post do
23    use Ecto.Schema
24
25    @primary_key {:id, Custom.Permalink, []}
26    schema "posts" do
27      field :title, :string, source: :post_title
28      field :text, :string
29      has_many :comments, Ecto.Query.SubqueryTest.Comment
30    end
31  end
32
33  defp prepare(query, operation \\ :all) do
34    Planner.prepare(query, operation, Ecto.TestAdapter, 0)
35  end
36
37  defp normalize(query, operation \\ :all) do
38    normalize_with_params(query, operation) |> elem(0)
39  end
40
41  defp normalize_with_params(query, operation \\ :all) do
42    {query, params, _key} = prepare(query, operation)
43    {query, _} =
44      query
45      |> Planner.returning(operation == :all)
46      |> Planner.normalize(operation, Ecto.TestAdapter, 0)
47    {query, params}
48  end
49
50  defp select_fields(fields, ix) do
51    for field <- fields do
52      {{:., [], [{:&, [], [ix]}, field]}, [], []}
53    end
54  end
55
56  test "prepare: subqueries" do
57    {query, params, key} = prepare(from(subquery(Post), []))
58    assert %{query: %Ecto.Query{}, params: []} = query.from
59    assert params == []
60    assert key == [:all, 0, [:all, 0, {"posts", Post, 127044068}]]
61
62    posts = from(p in Post, where: p.title == ^"hello")
63    query = from(c in Comment, join: p in subquery(posts), on: c.post_id == p.id)
64    {query, params, key} = prepare(query, [])
65    assert {"comments", Comment} = query.from
66    assert [%{source: %{query: %Ecto.Query{}, params: ["hello"]}}] = query.joins
67    assert params == ["hello"]
68    assert [[], 0, {:join, [{:inner, [:all|_], _}]}, {"comments", _, _}] = key
69  end
70
71  test "prepare: subqueries with association joins" do
72    {query, _, _} = prepare(from(p in subquery(Post), join: c in assoc(p, :comments)))
73    assert [%{source: {"comments", Comment}}] = query.joins
74
75    message = ~r/can only perform association joins on subqueries that return a source with schema in select/
76    assert_raise Ecto.QueryError, message, fn ->
77      prepare(from(p in subquery(from p in Post, select: p.title), join: c in assoc(p, :comments)))
78    end
79  end
80
81  test "prepare: subqueries with map updates in select can be used with assoc" do
82    query =
83      Post
84      |> select([post], %{post | title: ^"hello"})
85      |> subquery()
86      |> join(:left, [subquery_post], comment in assoc(subquery_post, :comments))
87      |> prepare()
88      |> elem(0)
89
90    assert %JoinExpr{on: on, source: source, assoc: nil, qual: :left} = hd(query.joins)
91    assert source == {"comments", Comment}
92    assert Macro.to_string(on.expr) == "&1.post_id() == &0.id()"
93  end
94
95  test "prepare: subqueries do not support preloads" do
96    query = from p in Post, join: c in assoc(p, :comments), preload: [comments: c]
97    assert_raise Ecto.SubQueryError, ~r/cannot preload associations in subquery/, fn ->
98      prepare(from(subquery(query), []))
99    end
100  end
101
102  describe "prepare: subqueries select" do
103    test "supports implicit select" do
104      query = prepare(from(subquery(Post), [])) |> elem(0)
105      assert "%Ecto.Query.SubqueryTest.Post{id: &0.id(), title: &0.title(), " <>
106             "text: &0.text()}" =
107             Macro.to_string(query.from.query.select.expr)
108    end
109
110    test "supports field selector" do
111      query = from p in "posts", select: p.text
112      query = prepare(from(subquery(query), [])) |> elem(0)
113      assert "%{text: &0.text()}" =
114             Macro.to_string(query.from.query.select.expr)
115
116      query = from p in Post, select: p.text
117      query = prepare(from(subquery(query), [])) |> elem(0)
118      assert "%{text: &0.text()}" =
119             Macro.to_string(query.from.query.select.expr)
120    end
121
122    test "supports maps" do
123      query = from p in Post, select: %{text: p.text}
124      query = prepare(from(subquery(query), [])) |> elem(0)
125      assert "%{text: &0.text()}" =
126             Macro.to_string(query.from.query.select.expr)
127    end
128
129    test "supports structs" do
130      query = from p in Post, select: %Post{text: p.text}
131      query = prepare(from(subquery(query), [])) |> elem(0)
132      assert "%Ecto.Query.SubqueryTest.Post{text: &0.text()}" =
133             Macro.to_string(query.from.query.select.expr)
134    end
135
136    test "supports update in maps" do
137      query = from p in Post, select: %{p | text: p.title}
138      query = prepare(from(subquery(query), [])) |> elem(0)
139      assert "%Ecto.Query.SubqueryTest.Post{id: &0.id(), title: &0.title(), " <>
140             "text: &0.title()}" =
141             Macro.to_string(query.from.query.select.expr)
142
143      query = from p in Post, select: %{p | unknown: p.title}
144      assert_raise Ecto.SubQueryError, ~r/invalid key `:unknown` on map update in subquery/, fn ->
145        prepare(from(subquery(query), []))
146      end
147    end
148
149    test "supports merge" do
150      query = from p in Post, select: merge(p, %{text: p.title})
151      query = prepare(from(subquery(query), [])) |> elem(0)
152      assert "%Ecto.Query.SubqueryTest.Post{id: &0.id(), title: &0.title(), " <>
153             "text: &0.title()}" =
154             Macro.to_string(query.from.query.select.expr)
155
156      query = from p in Post, select: merge(%{}, %{})
157      query = prepare(from(subquery(query), [])) |> elem(0)
158      assert "%{}" = Macro.to_string(query.from.query.select.expr)
159
160      assert_raise Ecto.SubQueryError, ~r/cannot merge because the left side is a map/, fn ->
161        query = from p in Post, select: merge(%{}, p)
162        prepare(from(subquery(query), []))
163      end
164
165      assert_raise Ecto.SubQueryError, ~r/cannot merge because the left side is a Ecto.Query/, fn ->
166        query = from p in Post, join: c in Comment, select: merge(p, c)
167        prepare(from(subquery(query), []))
168      end
169    end
170
171    test "requires atom keys for maps" do
172      query = from p in Post, select: %{p.id => p.title}
173      assert_raise Ecto.SubQueryError, ~r/only atom keys are allowed/, fn ->
174        prepare(from(subquery(query), []))
175      end
176    end
177
178    test "raises on custom expressions" do
179      query = from p in Post, select: fragment("? + ?", p.id, p.id)
180      assert_raise Ecto.SubQueryError, ~r/subquery must select a source \(t\), a field \(t\.field\) or a map/, fn ->
181        prepare(from(subquery(query), []))
182      end
183    end
184  end
185
186  test "prepare: allows type casting from subquery types" do
187    query = subquery(from p in Post, join: c in assoc(p, :comments),
188                                     select: %{id: p.id, title: p.title})
189
190    permalink = "1-hello-world"
191    {_query, params, _key} = prepare(query |> where([p], p.id == ^permalink))
192    assert params == [1]
193
194    assert_raise Ecto.Query.CastError, ~r/value `1` in `where` cannot be cast to type :string in query/, fn ->
195      prepare(query |> where([p], p.title == ^1))
196    end
197
198    assert_raise Ecto.QueryError, ~r/field `unknown` does not exist in subquery in query/, fn ->
199      prepare(query |> where([p], p.unknown == ^1))
200    end
201  end
202
203  test "prepare: wraps subquery errors" do
204    exception = assert_raise Ecto.SubQueryError, fn ->
205      query = Post |> where([p], p.title == ^1)
206      prepare(from(subquery(query), []))
207    end
208
209    assert %Ecto.Query.CastError{} = exception.exception
210    assert Exception.message(exception) =~ "the following exception happened when compiling a subquery."
211    assert Exception.message(exception) =~ "value `1` in `where` cannot be cast to type :string"
212    assert Exception.message(exception) =~ "where: p.title == ^1"
213    assert Exception.message(exception) =~ "from p in subquery(from p in Ecto.Query.SubqueryTest.Post"
214  end
215
216  test "normalize: subqueries" do
217    assert_raise Ecto.SubQueryError, ~r/does not allow `update` expressions in query/, fn ->
218      query = from p in Post, update: [set: [title: nil]]
219      normalize(from(subquery(query), []))
220    end
221
222    assert_raise Ecto.QueryError, ~r/`update_all` does not allow subqueries in `from`/, fn ->
223      query = from p in Post
224      normalize(from(subquery(query), update: [set: [title: nil]]), :update_all)
225    end
226  end
227
228  test "normalize: subqueries with params in from" do
229    query = from p in Post,
230              where: [title: ^"hello"],
231              order_by: [asc: p.text == ^"world"]
232
233    query = from p in subquery(query),
234              where: p.text == ^"last",
235              select: [p.title, ^"first"]
236
237    {query, params} = normalize_with_params(query)
238    assert [_, {:^, _, [0]}] = query.select.expr
239    assert [%{expr: {:==, [], [_, {:^, [], [1]}]}}] = query.from.query.wheres
240    assert [%{expr: [asc: {:==, [], [_, {:^, [], [2]}]}]}] = query.from.query.order_bys
241    assert [%{expr: {:==, [], [_, {:^, [], [3]}]}}] = query.wheres
242    assert params == ["first", "hello", "world", "last"]
243  end
244
245  test "normalize: subqueries with params in join" do
246    query = from p in Post,
247              where: [title: ^"hello"],
248              order_by: [asc: p.text == ^"world"]
249
250    query = from c in Comment,
251              join: p in subquery(query),
252              on: p.text == ^"last",
253              select: [p.title, ^"first"]
254
255    {query, params} = normalize_with_params(query)
256    assert [_, {:^, _, [0]}] = query.select.expr
257    assert [%{expr: {:==, [], [_, {:^, [], [1]}]}}] = hd(query.joins).source.query.wheres
258    assert [%{expr: [asc: {:==, [], [_, {:^, [], [2]}]}]}] = hd(query.joins).source.query.order_bys
259    assert {:==, [], [_, {:^, [], [3]}]} = hd(query.joins).on.expr
260    assert params == ["first", "hello", "world", "last"]
261  end
262
263  test "normalize: merges subqueries fields when requested" do
264    subquery = from p in Post, select: %{id: p.id, title: p.title}
265    query = normalize(from(subquery(subquery), []))
266    assert query.select.fields == select_fields([:id, :title], 0)
267
268    query = normalize(from(p in subquery(subquery), select: p.title))
269    assert query.select.fields == [{{:., [], [{:&, [], [0]}, :title]}, [], []}]
270
271    query = normalize(from(c in Comment, join: p in subquery(subquery), select: p))
272    assert query.select.fields == select_fields([:id, :title], 1)
273
274    query = normalize(from(c in Comment, join: p in subquery(subquery), select: p.title))
275    assert query.select.fields == [{{:., [], [{:&, [], [1]}, :title]}, [], []}]
276
277    subquery = from p in Post, select: %{id: p.id, title: p.title}
278    assert_raise Ecto.QueryError, ~r/it is not possible to return a map\/struct subset of a subquery/, fn ->
279      normalize(from(p in subquery(subquery), select: [:title]))
280    end
281  end
282end
283