在Apache演练中，JDBC扫描时没有将列名传递给PostgreSQL。

Question

在尝试为PostgreSQL运行SQL查询时，表中的列名将它向下推到数据库中。

select
  m.m_id,
  cnt_c_no
from (
    select
      m_id
    from pg_test_main.test1.table1
    where
      last_date >= '2019-01-01 00:00:00'
  ) as m
left join (
    select
      ci.m_id,
      count(ci.c_no) as cnt_c_no
    from (
        select
          m_id,
          c_no
        from pg_test.public.table2
      ) as ci
    inner join (
        select
          c_no
        from pg_test.public.table3
        where
          is_del = 'F'
      ) as c on ci.c_no = c.c_no
    group by
      ci.m_id
  ) as join1 on m.m_id = join1.m_id;

00-00    Screen
00-01      Project(m_id=[$0], cnt_c_no=[$1])
00-02        Project(m_id=[$0], cnt_c_no=[$2])
00-03          HashJoin(condition=[=($0, $1)], joinType=[left], semi-join: =[false])
00-05            Jdbc(sql=[SELECT "m_id" FROM "test1"."table1" WHERE "last_date" >= '2019-01-01 00:00:00' ])
00-04            Project(m_id0=[$0], cnt_c_no=[$1])
00-06              HashAgg(group=[{0}], cnt_c_no=[COUNT($1)])
00-07                Project(m_id=[$0], c_no=[$1])
00-08                  HashJoin(condition=[=($1, $2)], joinType=[inner], semi-join: =[false])
00-10                    Project(m_id=[$3], c_no=[$1])
00-12                      Jdbc(sql=[SELECT * FROM "public"."table2" ])
00-09                    Project(c_no0=[$0])
00-11                      Project(c_no=[$0])
00-13                        SelectionVectorRemover
00-14                          Filter(condition=[=($60, 'F')])
00-15                            Jdbc(sql=[SELECT * FROM "public"."table3" ])

如您所见，对table1的Jdbc扫描使用的是列名。

但是，对table2和table3的Jdbc扫描并没有使用列名。它向下推*到数据库。

我如何控制jdbc扫描，以便它可以按下colume名称？

Apache钻取版本为1.16.0 (嵌入式模式)

Answer 1

我尝试在演练1.17和演练1.15上使用MySQL复制它，但是对于查询，与您指定的查询类似，所有查询都被推入JDBC存储中：

SELECT m.person_id,
       cnt_c_no
FROM
  (SELECT person_id
   FROM mysql.`drill_mysql_test1`.person1
   WHERE date_field >= '2019-01-01 00:00:00') AS m
LEFT JOIN
  (SELECT ci.person_id,
          count(ci.last_name) AS cnt_c_no
   FROM
     (SELECT person_id,
             last_name
      FROM mysql.`drill_mysql_test`.person) AS ci
   INNER JOIN
     (SELECT last_name
      FROM mysql.`drill_mysql_test`.person2
      WHERE boolean_field = 'F' ) AS c ON ci.last_name = c.last_name
   GROUP BY ci.person_id) AS join1 ON m.person_id = join1.person_id

此查询的计划：

00-00    Screen
00-01      Project(person_id=[$0], cnt_c_no=[$1])
00-02        Jdbc(sql=[SELECT `t0`.`person_id`, `t5`.`cnt_c_no` FROM (SELECT `person_id` FROM `drill_mysql_test1`.`person1` WHERE `date_field` >= '2019-01-01 00:00:00') AS `t0` LEFT JOIN (SELECT `t1`.`person_id`, COUNT(`t1`.`last_name`) AS `cnt_c_no` FROM (SELECT `person_id`, `last_name` FROM `drill_mysql_test`.`person`) AS `t1` INNER JOIN (SELECT `last_name` FROM `drill_mysql_test`.`person2` WHERE `boolean_field` = 'F') AS `t3` ON `t1`.`last_name` = `t3`.`last_name` GROUP BY `t1`.`person_id`) AS `t5` ON `t0`.`person_id` = `t5`.`person_id` ])

请您为Postgres表提供CTAS，因此我将再次尝试使用特定的数据类型来再现它。此外，如果可能的话，请检查这个问题是否仍然转载在钻1.17上。

UPD:在这个答案下的评论帮助发现这个问题是由以下问题引起的：https://issues.apache.org/jira/browse/DRILL-7340，它将在演练1.18.0中得到解决。