Skip to content

Error using incorrect cache in subquery with union #3110

@KalvinHom

Description

@KalvinHom

Environment

Elixir 1.9.0
Erlang/OTP 22
Mysql 5.8
Ecto 3.1.7
MyXQL 0.2.6

Current behavior

When doing a subquery with unions, the cache is incorrectly applied if the second query in the union is cached, but the first one isn't.

defmodule Schema1 do
  use Ecto.Schema

  schema "external_payments" do
    field(:request_id, :integer)
  end
end

defmodule Schema2 do
  use Ecto.Schema

  schema "external_request_reports" do
    field(:request_id, :integer)
  end
end

defmodule Test do
  import Ecto.Query
  alias UnionTest.Repo

  def test_union() do
    test_data_1 = [1, 2, 3]
    test_data_2 = [4]

    query_1 =
      Schema1
      |> where([r], r.request_id in ^test_data_1)
      |> select([r], %Schema1{id: r.id})

    query_2 =
      Schema2
      |> where([s], s.request_id in [1, 2])
      |> select([r], %Schema2{id: r.id})
      |> distinct(true)

    subquery = Ecto.Query.subquery(union_all(query_1, ^query_2))
    Repo.all(from(a in subquery))

    query_3 =
      Schema1
      |> where([r], r.request_id in ^test_data_2)
      |> select([r], %Schema1{id: r.id})

    subquery = Ecto.Query.subquery(union_all(query_3, ^query_2))
    Repo.all(from(a in subquery))
  end
end

query_3 is trying to use query_1 's cached query, and has the wrong number of interpolated values as a result.

** (ArgumentError) parameters must be of length 3 for query %MyXQL.Query{cache: :reference, name: "ecto_679", num_params: 3, ref: #Reference<0.3417688783.2265972742.94111>, statement: "SELECT s0.`id` FROM (SELECT e0.`id` AS `id` FROM `external_payments` AS e0 WHERE (e0.`request_id` IN (?,?,?)) UNION ALL (SELECT DISTINCT e0.`id` FROM `external_payments` AS e0 WHERE (e0.`request_id` IN (1,2)))) AS s0", statement_id: 1}
    (myxql) lib/myxql/query.ex:53: DBConnection.Query.MyXQL.Query.encode/3
    (db_connection) lib/db_connection.ex:1161: DBConnection.maybe_encode/4
    (db_connection) lib/db_connection.ex:591: DBConnection.execute/4
    (ecto_sql) lib/ecto/adapters/myxql/connection.ex:28: Ecto.Adapters.MyXQL.Connection.execute/4
    (ecto_sql) lib/ecto/adapters/sql.ex:572: Ecto.Adapters.SQL.execute!/4
    (ecto_sql) lib/ecto/adapters/sql.ex:554: Ecto.Adapters.SQL.execute/5
    (ecto) lib/ecto/repo/queryable.ex:153: Ecto.Repo.Queryable.execute/4
    (ecto) lib/ecto/repo/queryable.ex:18: Ecto.Repo.Queryable.all/3

If you reverse the union order such that query_2 comes first in both unions, there is no error, I think since query_1 and query_3 are determined to not be the same cached queries first?

The key being used in Ecto.Query.Planner.query_lookup

[
  :all,
  0,
  [
    :all,
    0,
    {:union_all,
     [
       {:where,
        [
          and: {:in, [],
           [
             {{:., [], [{:&, [], [0]}, :request_id]}, [], []},
             %Ecto.Query.Tagged{
               tag: nil,
               type: {:array, {0, :request_id}},
               value: [1, 2]
             }
           ]}
        ]},
       {"external_request_reports", Schema2, 128332857, nil},
       {:select,
        {:%, [],
         [Schema2, {:%{}, [], [id: {{:., [], [{:&, [], [0]}, :id]}, [], []}]}]}},
       {:distinct, true}
     ]} |
    :nocache
  ],
  {:select, {:&, [], [0]}}
]

Expected behavior

Query should succeed and not try to use the cache.

Metadata

Metadata

Assignees

No one assigned

    Labels

    No labels
    No labels

    Type

    No type

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions