Details
-
Bug
-
Status: Open
-
Major
-
Resolution: Unresolved
-
1.6.0
-
None
-
None
-
None
-
OS: SUSE Linux Enterprise Server 11 SP3 (x86_64)
Cluster: 2 control node + 2 payload node
Description
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 |
--------------------------