load_tables_and_aliases not handling subselects / subqueries

[jon-ga]

The following script only produces ["users"], instead of ["enrollments", "users"].

require 'pg_query'
require 'pp'
sql = 'select u.email, (select count(*) from enrollments e where e.user_id = u.id) as num_enrollments from users u'
pp PgQuery.parse(sql).tables

Given https://github.com/lfittl/pg_query/blob/961411a6741cf40b44a3b3843f89ee4c9ec8f8ad/lib/pg_query/parse.rb#L56-L58, the function will only look at the top-level FROM clause.

Picking apart the targetList, I can find the subquery used in this specific example, and extract the tables it uses:

subtree = PgQuery.parse(sql).parsetree[0]['SELECT']['targetList'][1]['RESTARGET']['val']['SUBLINK']['subselect']
pp PgQuery.new('', [subtree], []).tables
# ["enrollments"]

Here's the original parse tree:

#<PgQuery:0x007fc4828761f0
 @parsetree=
  [{"SELECT"=>
     {"distinctClause"=>nil,
      "intoClause"=>nil,
      "targetList"=>
       [{"RESTARGET"=>
          {"name"=>nil,
           "indirection"=>nil,
           "val"=>{"COLUMNREF"=>{"fields"=>["u", "email"], "location"=>8}},
           "location"=>8}},
        {"RESTARGET"=>
          {"name"=>"num_enrollments",
           "indirection"=>nil,
           "val"=>
            {"SUBLINK"=>
              {"subLinkType"=>4,
               "testexpr"=>nil,
               "operName"=>nil,
               "subselect"=>
                {"SELECT"=>
                  {"distinctClause"=>nil,
                   "intoClause"=>nil,
                   "targetList"=>
                    [{"RESTARGET"=>
                       {"name"=>nil,
                        "indirection"=>nil,
                        "val"=>
                         {"FUNCCALL"=>
                           {"funcname"=>["count"],
                            "args"=>nil,
                            "agg_order"=>nil,
                            "agg_filter"=>nil,
                            "agg_within_group"=>false,
                            "agg_star"=>true,
                            "agg_distinct"=>false,
                            "func_variadic"=>false,
                            "over"=>nil,
                            "location"=>25}},
                        "location"=>25}}],
                   "fromClause"=>
                    [{"RANGEVAR"=>
                       {"schemaname"=>nil,
                        "relname"=>"enrollments",
                        "inhOpt"=>2,
                        "relpersistence"=>"p",
                        "alias"=>
                         {"ALIAS"=>{"aliasname"=>"e", "colnames"=>nil}},
                        "location"=>39}}],
                   "whereClause"=>
                    {"AEXPR"=>
                      {"name"=>["="],
                       "lexpr"=>
                        {"COLUMNREF"=>
                          {"fields"=>["e", "user_id"], "location"=>59}},
                       "rexpr"=>
                        {"COLUMNREF"=>{"fields"=>["u", "id"], "location"=>71}},
                       "location"=>69}},
                   "groupClause"=>nil,
                   "havingClause"=>nil,
                   "windowClause"=>nil,
                   "valuesLists"=>nil,
                   "sortClause"=>nil,
                   "limitOffset"=>nil,
                   "limitCount"=>nil,
                   "lockingClause"=>nil,
                   "withClause"=>nil,
                   "op"=>0,
                   "all"=>false,
                   "larg"=>nil,
                   "rarg"=>nil}},
               "location"=>17}},
           "location"=>17}}],
      "fromClause"=>
       [{"RANGEVAR"=>
          {"schemaname"=>nil,
           "relname"=>"users",
           "inhOpt"=>2,
           "relpersistence"=>"p",
           "alias"=>{"ALIAS"=>{"aliasname"=>"u", "colnames"=>nil}},
           "location"=>101}}],
      "whereClause"=>nil,
      "groupClause"=>nil,
      "havingClause"=>nil,
      "windowClause"=>nil,
      "valuesLists"=>nil,
      "sortClause"=>nil,
      "limitOffset"=>nil,
      "limitCount"=>nil,
      "lockingClause"=>nil,
      "withClause"=>nil,
      "op"=>0,
      "all"=>false,
      "larg"=>nil,
      "rarg"=>nil}}],
 @query=
  "\nselect u.email, (select count(*) from enrollments e where e.user_id = u.id) as num_enrollments from users u\n",
 @warnings=[]>

[lfittl]

That indeed looks like a bug - thanks for finding that!

If you want you can submit a PR (ideally with a test case), otherwise I'd be happy to take a look the next couple of days :)