I run the following query,
SELECT count(a.imsi),sum(a.download) from test_a a JOIN test_zc1 b ON a.imsi =b.imsi;
and it returns an exception:
Exception: Hash join failed, reason: Could not build a 1-to-1 correspondence for columns involved in equijoin
The test_a table contains 200 millions of records, and the test_zc1 contains 800 millions. So what’s the meaning of the exception? I’m sure that the imsi attribute is unique in both of the tables.
More, I have some doubts about the JOIN operation in Mapd.
Whether Mapd support multi-table JOIN or not?
Can I treat the subquery result table as the right table of JOIN ? Like this query:
select MSISDN,20140926,AREA_ID,USER_ID from DATUM.MD_PER_INF_ATTRIBUTE_DAY_03 z join (select S00000000023 from EDC_SUB_TEMP_10000049978_ZZ5 a left join DATUM.MD_VW_PER_USER02_DAY_03 b on b.USER_ID=a.S00000000023 and b.DAY_NUMBER = 25 and b.IS_HMD_MDR_MG=1 where b.USER_ID is null ) y on z.USER_ID=y.S00000000023 where z.DAY_NUMBER = 26 and z.AREA_ID in (select t.area_id from edc_area t where t.parent_area_id in (1000515));