Ecto.Adapters.SQL.query behavior and result interpretation

Hi again!

Since I haven’t got a solution with building my queries from dynamic request maps, I’ve decided to try to build a string with query and a list with binding params and use Ecto.Adapters.SQL.query to do DB requests.

But here I got another rock :slight_smile:

Here is a number of subsequent queries with count():

iex(12)> Ecto.Adapters.SQL.query(Repo, "select count(*) from nioktr as n LEFT OUTER JOIN nioktr_to_grnti as ng ON n.id = ng.nioktr_id LEFT OUTER JOIN spr_grnti as g ON g.id = ng.spr_grnti_id WHERE g.id = ANY(ARRAY[733486, 730000])", [])
[debug] QUERY OK db=2.9ms
select count(*) from nioktr as n LEFT OUTER JOIN nioktr_to_grnti as ng ON n.id = ng.nioktr_id LEFT OUTER JOIN spr_grnti as g ON g.id = ng.spr_grnti_id WHERE g.id = ANY(ARRAY[733486, 730000]) []
{:ok,
 %Postgrex.Result{columns: ["count"], command: :select, connection_id: 18519,
  num_rows: 1, rows: ['8']}}

iex(13)> Ecto.Adapters.SQL.query(Repo, "select count(*) from nioktr as n LEFT OUTER JOIN nioktr_to_grnti as ng ON n.id = ng.nioktr_id LEFT OUTER JOIN spr_grnti as g ON g.id = ng.spr_grnti_id WHERE g.id = ANY(ARRAY[730000])", [])        
[debug] QUERY OK db=2.5ms
select count(*) from nioktr as n LEFT OUTER JOIN nioktr_to_grnti as ng ON n.id = ng.nioktr_id LEFT OUTER JOIN spr_grnti as g ON g.id = ng.spr_grnti_id WHERE g.id = ANY(ARRAY[730000]) []
{:ok,
 %Postgrex.Result{columns: ["count"], command: :select, connection_id: 18517,
  num_rows: 1, rows: [[1]]}}

iex(14)> Ecto.Adapters.SQL.query(Repo, "select count(*) from nioktr as n LEFT OUTER JOIN nioktr_to_grnti as ng ON n.id = ng.nioktr_id LEFT OUTER JOIN spr_grnti as g ON g.id = ng.spr_grnti_id WHERE g.id = ANY(ARRAY[733486])", [])
[debug] QUERY OK db=3.0ms
select count(*) from nioktr as n LEFT OUTER JOIN nioktr_to_grnti as ng ON n.id = ng.nioktr_id LEFT OUTER JOIN spr_grnti as g ON g.id = ng.spr_grnti_id WHERE g.id = ANY(ARRAY[733486]) []
{:ok,
 %Postgrex.Result{columns: ["count"], command: :select, connection_id: 18520,
  num_rows: 1, rows: ['7']}}

iex(15)> Ecto.Adapters.SQL.query(Repo, "select count(*) from nioktr as n LEFT OUTER JOIN nioktr_to_grnti as ng ON n.id = ng.nioktr_id LEFT OUTER JOIN spr_grnti as g ON g.id = ng.spr_grnti_id WHERE g.id = ANY(ARRAY[000000])", [])
[debug] QUERY OK db=2.8ms
select count(*) from nioktr as n LEFT OUTER JOIN nioktr_to_grnti as ng ON n.id = ng.nioktr_id LEFT OUTER JOIN spr_grnti as g ON g.id = ng.spr_grnti_id WHERE g.id = ANY(ARRAY[000000]) []
{:ok,
 %Postgrex.Result{columns: ["count"], command: :select, connection_id: 18515,
  num_rows: 1, rows: [[0]]}}

The same queries (copied one-to-one via Ctrl+C\Ctrl+V) in the same order to the psql:

[ db ] # select count(*) from nioktr as n LEFT OUTER JOIN nioktr_to_grnti as ng ON n.id = ng.nioktr_id LEFT OUTER JOIN spr_grnti as g ON g.id = ng.spr_grnti_id WHERE g.id = ANY(ARRAY[733486, 730000]);
 count 
-------
    56
(1 row)
Time: 0,962 ms

[ db ] # select count(*) from nioktr as n LEFT OUTER JOIN nioktr_to_grnti as ng ON n.id = ng.nioktr_id LEFT OUTER JOIN spr_grnti as g ON g.id = ng.spr_grnti_id WHERE g.id = ANY(ARRAY[730000]);
 count 
-------
     1
(1 row)
Time: 0,798 ms

[ db ] # select count(*) from nioktr as n LEFT OUTER JOIN nioktr_to_grnti as ng ON n.id = ng.nioktr_id LEFT OUTER JOIN spr_grnti as g ON g.id = ng.spr_grnti_id WHERE g.id = ANY(ARRAY[733486]);
 count 
-------
    55
(1 row)
Time: 1,166 ms

[ db ] # select count(*) from nioktr as n LEFT OUTER JOIN nioktr_to_grnti as ng ON n.id = ng.nioktr_id LEFT OUTER JOIN spr_grnti as g ON g.id = ng.spr_grnti_id WHERE g.id = ANY(ARRAY[000000]);
 count 
-------
     0
(1 row)
Time: 1,130 ms

So I don’t understand how to interpret SQL.query results. Can somebody explain me, please?

1 Like

By the way, I believe, that this symbols are just characters with corresponding ASCII codes. But why whould Elixir\Ecto interpret results as char lists and how to avoid it?

So, as usually, I’ve found an answer - I’m too quick and too silly :smiley:

Don’t know why it lists as char lists, but of course you can use this values as normal integers, so it’s just a presentation problem.

You encountered the charlist issue, where lists of small integers are interpreted as erlang-style strings - charlists. That only affects the way they are printed, the data is still the same.

iex(1)> '8' == [56]
true

You can learn more on this by using the i/1 helper in iex on the data, i.e.

iex(4)> i '8'
Term
  '8'
Data type
  List
Description
  This is a list of integers that is printed as a sequence of characters
  delimited by single quotes because all the integers in it represent valid
  ASCII characters. Conventionally, such lists of integers are referred to as
  "charlists" (more precisely, a charlist is a list of Unicode codepoints,
  and ASCII is a subset of Unicode).
Raw representation
  [56]
Reference modules
  List
1 Like

Thanks a lot for i/1 helper!