You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@drill.apache.org by "Zhenhua Dong (JIRA)" <ji...@apache.org> on 2016/07/19 05:00:26 UTC
[jira] [Created] (DRILL-4787) column value is always null in inner
join query
Zhenhua Dong created DRILL-4787:
-----------------------------------
Summary: column value is always null in inner join query
Key: DRILL-4787
URL: https://issues.apache.org/jira/browse/DRILL-4787
Project: Apache Drill
Issue Type: Bug
Affects Versions: 1.6.0
Environment: OS: SUSE Linux Enterprise Server 11 SP3 (x86_64)
Cluster: 2 control node + 2 payload node
Reporter: Zhenhua Dong
1. query result is not correct
select USER_A.NAMEID,
USER_A.CSLOC,
USER_A.PSLOC
FROM USER_B
inner join USER_A
on USER_B.NAMEID=USER_A.NAMEID
where USER_B.NAMEID=49000000000
+---------+--------+--------+
| NAME | CSLOC | PSLOC |
+---------+--------+--------+
| null | 2 | 2 |
+---------+--------+--------+
2. execute plan
>explain plan for select USER_A.NAMEID,
USER_A.CSLOC,
USER_A.PSLOC
FROM USER_B
inner join USER_A
on USER_B.NAMEID=USER_A.NAMEID
where USER_B.NAMEID=49000000000;
00-00 Screen
00-01 Project(NAMEID=[$0], CSLOC=[$1], PSLOC=[$2])
00-02 Project(NAMEID=[$20], CSLOC=[$25], PSLOC=[$26])
00-03 Jdbc(sql=[SELECT *
FROM (SELECT *
FROM `mysqldb`.`USER_B`
WHERE `NAMEID` = 49000000000) AS `t`
INNER JOIN `mysqldb`.`USER_A` ON `t`.`NAMEID` = `USER_A`.`NAMEID`])
3. the result follow the execute plan
>SELECT *
FROM (SELECT *
FROM `USER_B`
WHERE `NAMEID` = 49000000000) AS `t`
INNER JOIN `USER_A` ON `t`.`NAMEID` = `USER_A`.`NAMEID`;
+--------------+------------------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-----------+-------+-----------+-------+-----------+-------+----------+--------+---------+---------+----------+--------+--------+----------+-----------+-------+-----------------------------------+----------+----------+----------+--------+-----------+-------+
| NAMEID | IMSI | TS11 | TS21 | TS22 | TS62 | BS22 | BS24 | BS25 | BS26 | BS2G | BS3G | BS2F | BS3F | ANAMEID1 | BC1 | ANAMEID2 | BC2 | ANAMEID3 | BC3 | NAMEID0 | IMSI0 | IMEISV | VLRADD | SGSNNUM | CSLOC | PSLOC | NPREFIX | SUBSTYPE | KIND | EKI | AKATYPE | A3A8IND | FSETIND | A4IND | AUTHINFO | RID |
+--------------+------------------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-----------+-------+-----------+-------+-----------+-------+----------+--------+---------+---------+----------+--------+--------+----------+-----------+-------+-----------------------------------+----------+----------+----------+--------+-----------+-------+
| 49000000000 | 260000000000000 | 1 | 1 | 1 | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | 2 | 2 | null | null | 325 | 12345678901234567890123456789012 | 0 | 4 | 15 | 2 | null | null |
+--------------+------------------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-----------+-------+-----------+-------+-----------+-------+----------+--------+---------+---------+----------+--------+--------+----------+-----------+-------+-----------------------------------+----------+----------+----------+--------+-----------+-------+
1 row selected (0.979 seconds)
4. drill view describe
> describe USER_CSPS_CSdata;
+--------------+--------------------+--------------+
| COLUMN_NAME | DATA_TYPE | IS_NULLABLE |
+--------------+--------------------+--------------+
| NAMEID | CHARACTER VARYING | YES |
| NAME | CHARACTER VARYING | YES |
| TS11 | TINYINT | YES |
| TS21 | TINYINT | YES |
| TS22 | TINYINT | YES |
| TS62 | TINYINT | YES |
| BS22 | TINYINT | YES |
| BS24 | TINYINT | YES |
| BS25 | TINYINT | YES |
| BS26 | TINYINT | YES |
| BS2G | TINYINT | YES |
| BS3G | TINYINT | YES |
| BS2F | TINYINT | YES |
| BS3F | TINYINT | YES |
| AMSISDN1 | CHARACTER VARYING | YES |
| BC1 | INTEGER | YES |
| AMSISDN2 | CHARACTER VARYING | YES |
| BC2 | INTEGER | YES |
| AMSISDN3 | CHARACTER VARYING | YES |
| BC3 | INTEGER | YES |
+--------------+--------------------+--------------+
> describe USER_CSPS_Subscription;
+--------------+--------------------+--------------+
| COLUMN_NAME | DATA_TYPE | IS_NULLABLE |
+--------------+--------------------+--------------+
| NAMEID | CHARACTER VARYING | YES |
| NAME | CHARACTER VARYING | YES |
| IMEISV | CHARACTER VARYING | YES |
| VLRADD | CHARACTER VARYING | YES |
| SGSNNUM | CHARACTER VARYING | YES |
| CSLOC | TINYINT | YES |
| PSLOC | TINYINT | YES |
| NPREFIX | CHARACTER VARYING | YES |
| SUBSTYPE | TINYINT | YES |
| KIND | SMALLINT | YES |
| EKI | CHARACTER VARYING | YES |
| AKATYPE | TINYINT | YES |
| A3A8IND | TINYINT | YES |
| FSETIND | TINYINT | YES |
| A4IND | TINYINT | YES |
| AUTHINFO | TINYINT | YES |
| RID | TINYINT | YES |
+--------------+--------------------+--------------+
5. switch table sequence can get correct result
select USER_A.NAMEID,
USER_A.CSLOC,
USER_A.PSLOC
FROM USER_A
inner join USER_B
on USER_B.NAMEID=USER_A.NAMEID
where USER_B.NAMEID=49000000000
+--------------+--------+--------+
| NAME | CSLOC | PSLOC |
+--------------+--------+--------+
| NULL | 2 | 2 |
+--------------+--------+--------+
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)