Preloading in query returns only one element instead of all

Questions / Help
1

Hi, for some reason when I preload data in the query directly, not using the Repo.preload/2, I get only one result instead of all.

The problem this is also causing me is that I can’t use Repo.preload/2 with the data I have because it has to preload for around 50k elements which is more MSSQL accepts as parameters for a query, so it fails.

My module:

defmodule Migrator.TEmpleados do
  use Ecto.Schema
  import Ecto.Query

  @primary_key false

  schema "TEmpleados" do
    field :CodEmpleado, :integer
    field :CodPersona, :integer

    belongs_to :persona, Migrator.TPersonas, define_field: false, foreign_key: :CodPersona, references: :CodPersona
  end

  def no_preload_query do
    q =
      from e in __MODULE__,
        inner_join: p in assoc(e, :persona)

    Migrator.OldRepo.all(q)
  end

  def separate_preload_query do
    q =
      from e in __MODULE__,
      inner_join: p in assoc(e, :persona)

    Migrator.OldRepo.all(q)
    |> Migrator.OldRepo.preload(:persona)
  end

  def in_preload_query do
    q =
      from e in __MODULE__,
        inner_join: p in assoc(e, :persona),
        preload: [persona: p]

    Migrator.OldRepo.all(q)
  end
end

This is when I run just the query fetching the data and obtaining the results


iex(1)> Migrator.TEmpleados.no_preload_query() |> length

[debug] QUERY OK source="TEmpleados" db=525.1ms decode=6.7ms queue=347.8ms idle=1186.7ms

SELECT T0.[CodEmpleado], T0.[CodPersona] FROM [TEmpleados] AS T0 INNER JOIN [TPersonas] AS T1 ON T1.[CodPersona] = T0.[CodPersona] []

696

Next is the problem: when I try to preload in the query


iex(5)> Migrator.TEmpleados.in_preload_query() |> length

[debug] QUERY OK source="TEmpleados" db=900.2ms queue=277.8ms idle=1433.6ms

SELECT T0.[CodEmpleado], T0.[CodPersona], T1.[CodPersona], T1.[CodTipoDocumento], T1.[NDocumento] FROM [TEmpleados] AS T0 INNER JOIN [TPersonas] AS T1 ON T1.[CodPersona] = T0.[CodPersona] []

1

And in case you are wondering, this is by doing Repo.preload/2


iex(4)> Migrator.TEmpleados.separate_preload_query() |> length

[debug] QUERY OK source="TEmpleados" db=658.3ms queue=262.9ms idle=409.6ms

SELECT T0.[CodEmpleado], T0.[CodPersona] FROM [TEmpleados] AS T0 INNER JOIN [TPersonas] AS T1 ON T1.[CodPersona] = T0.[CodPersona] []

[debug] QUERY OK source="TPersonas" db=865.5ms queue=610.1ms idle=1337.7ms

SELECT T0.[CodPersona], T0.[CodTipoDocumento], T0.[NDocumento], T0.[CodPersona] FROM [TPersonas] AS T0 WHERE (T0.[CodPersona] IN (@1,@2,@3,@4,@5,@6,@7,@8,@9,@10,@11,@12,@13,@14,@15,@16,@17,@18,@19,@20,@21,@22,@23,@24,@25,@26,@27,@28,@29,@30,@31,@32,@33,@34,@35,@36,@37,@38,@39,@40,@41,@42,@43,@44,@45,@46,@47,@48,@49,@50,@51,@52,@53,@54,@55,@56,@57,@58,@59,@60,@61,@62,@63,@64,@65,@66,@67,@68,@69,@70,@71,@72,@73,@74,@75,@76,@77,@78,@79,@80,@81,@82,@83,@84,@85,@86,@87,@88,@89,@90,@91,@92,@93,@94,@95,@96,@97,@98,@99,@100,@101,@102,@103,@104,@105,@106,@107,@108,@109,@110,@111,@112,@113,@114,@115,@116,@117,@118,@119,@120,@121,@122,@123,@124,@125,@126,@127,@128,@129,@130,@131,@132,@133,@134,@135,@136,@137,@138,@139,@140,@141,@142,@143,@144,@145,@146,@147,@148,@149,@150,@151,@152,@153,@154,@155,@156,@157,@158,@159,@160,@161,@162,@163,@164,@165,@166,@167,@168,@169,@170,@171,@172,@173,@174,@175,@176,@177,@178,@179,@180,@181,@182,@183,@184,@185,@186,@187,@188,@189,@190,@191,@192,@193,@194,@195,@196,@197,@198,@199,@200,@201,@202,@203,@204,@205,@206,@207,@208,@209,@210,@211,@212,@213,@214,@215,@216,@217,@218,@219,@220,@221,@222,@223,@224,@225,@226,@227,@228,@229,@230,@231,@232,@233,@234,@235,@236,@237,@238,@239,@240,@241,@242,@243,@244,@245,@246,@247,@248,@249,@250,@251,@252,@253,@254,@255,@256,@257,@258,@259,@260,@261,@262,@263,@264,@265,@266,@267,@268,@269,@270,@271,@272,@273,@274,@275,@276,@277,@278,@279,@280,@281,@282,@283,@284,@285,@286,@287,@288,@289,@290,@291,@292,@293,@294,@295,@296,@297,@298,@299,@300,@301,@302,@303,@304,@305,@306,@307,@308,@309,@310,@311,@312,@313,@314,@315,@316,@317,@318,@319,@320,@321,@322,@323,@324,@325,@326,@327,@328,@329,@330,@331,@332,@333,@334,@335,@336,@337,@338,@339,@340,@341,@342,@343,@344,@345,@346,@347,@348,@349,@350,@351,@352,@353,@354,@355,@356,@357,@358,@359,@360,@361,@362,@363,@364,@365,@366,@367,@368,@369,@370,@371,@372,@373,@374,@375,@376,@377,@378,@379,@380,@381,@382,@383,@384,@385,@386,@387,@388,@389,@390,@391,@392,@393,@394,@395,@396,@397,@398,@399,@400,@401,@402,@403,@404,@405,@406,@407,@408,@409,@410,@411,@412,@413,@414,@415,@416,@417,@418,@419,@420,@421,@422,@423,@424,@425,@426,@427,@428,@429,@430,@431,@432,@433,@434,@435,@436,@437,@438,@439,@440,@441,@442,@443,@444,@445,@446,@447,@448,@449,@450,@451,@452,@453,@454,@455,@456,@457,@458,@459,@460,@461,@462,@463,@464,@465,@466,@467,@468,@469,@470,@471,@472,@473,@474,@475,@476,@477,@478,@479,@480,@481,@482,@483,@484,@485,@486,@487,@488,@489,@490,@491,@492,@493,@494,@495,@496,@497,@498,@499,@500,@501,@502,@503,@504,@505,@506,@507,@508,@509,@510,@511,@512,@513,@514,@515,@516,@517,@518,@519,@520,@521,@522,@523,@524,@525,@526,@527,@528,@529,@530,@531,@532,@533,@534,@535,@536,@537,@538,@539,@540,@541,@542,@543,@544,@545,@546,@547,@548,@549,@550,@551,@552,@553,@554,@555,@556,@557,@558,@559,@560,@561,@562,@563,@564,@565,@566,@567,@568,@569,@570,@571,@572,@573,@574,@575,@576,@577,@578,@579,@580,@581,@582,@583,@584,@585,@586,@587,@588,@589,@590,@591,@592,@593,@594,@595,@596,@597,@598,@599,@600,@601,@602,@603,@604,@605,@606,@607,@608,@609,@610,@611,@612,@613,@614,@615,@616,@617,@618,@619,@620,@621,@622,@623,@624,@625,@626,@627,@628,@629,@630,@631,@632,@633,@634,@635,@636,@637,@638,@639,@640,@641,@642,@643,@644,@645,@646,@647,@648,@649,@650,@651,@652,@653,@654,@655,@656,@657,@658,@659,@660,@661,@662,@663,@664,@665,@666,@667,@668,@669,@670,@671,@672,@673,@674,@675,@676,@677,@678,@679,@680,@681,@682,@683,@684,@685,@686,@687,@688,@689,@690,@691,@692,@693,@694,@695,@696)) [138090, 138007, 137586, 137194, 137174, 136923, 136363, 136270, 135947, 135946, 135311, 135287, 134742, 134702, 134335, 134330, 133302, 132898, 132758, 131810, 131806, 131805, 131743, 131380, 130809, 130562, 129296, 129184, 129084, 129081, 128699, 128365, 127626, 127032, 126117, 126069, 126067, 126022, 125743, 125739, 125432, 125431, 125430, 124660, 124386, 123722, 122951, 122945, 122728, 122357, ...]

696

I’ve tested the raw SQL generated by Ecto and it works as excected: returns 696 rows.

Any ideas?

2

Just a wild guess: have you tried with left_join?

3 
def left_preload_query do
    q =
      from e in __MODULE__,
        left_join: p in assoc(e, :persona),
        preload: [persona: p]

    Migrator.OldRepo.all(q)
  end

iex(8)> Migrator.TEmpleados.left_preload_query() |> length()

[debug] QUERY OK source="TEmpleados" db=920.4ms queue=309.4ms idle=1021.8ms
SELECT T0.[CodEmpleado], T0.[CodPersona], T1.[CodPersona], T1.[CodTipoDocumento], T1.[NDocumento] FROM [TEmpleados] AS T0 LEFT OUTER JOIN [TPersonas] AS T1 ON T1.[CodPersona] = T0.[CodPersona] []

1

Tried it and it’s the same problem, preloading in the query gives only one result, but not not preloading gives all.