| set hive.mapred.mode=nonstrict; |
| create table L as select 4436 id; |
| create table LA_n11 as select 4436 loan_id, 4748 aid, 4415 pi_id; |
| create table FR as select 4436 loan_id; |
| create table A_n11 as select 4748 id; |
| create table PI as select 4415 id; |
| |
| create table acct as select 4748 aid, 10 acc_n, 122 brn; |
| insert into table acct values(4748, null, null); |
| insert into table acct values(4748, null, null); |
| |
| --[HIVE-10841] (WHERE col is not null) does not work sometimes for queries with many JOIN statements |
| explain select |
| acct.ACC_N, |
| acct.brn |
| FROM L |
| JOIN LA_n11 ON L.id = LA_n11.loan_id |
| JOIN FR ON L.id = FR.loan_id |
| JOIN A_n11 ON LA_n11.aid = A_n11.id |
| JOIN PI ON PI.id = LA_n11.pi_id |
| JOIN acct ON A_n11.id = acct.aid |
| WHERE |
| L.id = 4436 |
| and acct.brn is not null; |
| |
| select |
| acct.ACC_N, |
| acct.brn |
| FROM L |
| JOIN LA_n11 ON L.id = LA_n11.loan_id |
| JOIN FR ON L.id = FR.loan_id |
| JOIN A_n11 ON LA_n11.aid = A_n11.id |
| JOIN PI ON PI.id = LA_n11.pi_id |
| JOIN acct ON A_n11.id = acct.aid |
| WHERE |
| L.id = 4436 |
| and acct.brn is not null; |
