Values not match on join


#1

Hi!

Have issue with joins. Values have to be the same, but they are different:

mapdql> select p.id_plu,m.id_plu 
from tbl_pro p left join tbl_mat m on p.id_plu=m.id_plu limit 10;
id_plu     | id_plu0
3036841 | 3377538
3149485 | 3377541 

Rewriting query not solving problem:

select tbl_pro.id_plu, m.id_plu bb,tbl_pro.id_plu=m.id_plu from tbl_pro, tbl_mat m where tbl_pro.id_plu=m.id_plu limit 10

Join column types are the same - TEXT ENCODING DICT(32) :

CREATE TABLE tbl_pro (
date_t DATE,
plant TEXT ENCODING DICT(32),
id_plu TEXT ENCODING DICT(32) 
)

CREATE TABLE tbl_mat (
id_plu TEXT ENCODING DICT(32),
id_fc TEXT ENCODING DICT(32)
)

Check for:

For all but the first table list in the from-list, the data values in the join column must be unique. In data warehouse terms, list the “fact” table first, followed by any number of “dimension” tables.

mapdql> select count(distinct(id_plu)),count(id_plu) from tbl_pro;
EXPR$0|EXPR$1
8427|362190140
mapdql> select count(distinct(id_plu)),count(id_plu) from tbl_mat;
EXPR$0|EXPR$1
705152|705152
mapdql> \version
MapD Server Version: 3.3.1-20171108-32e7bcc

#2

Hi,

This is a known issue https://github.com/mapd/mapd-core/issues/131

It will be fixed in the next release, or if you are building from source code the fix is already in.

regards


#3

Hi,

As a work around for now you may want to use a shared dictionary on those columns

This is placed on the create table statement, for example

mapdql> create table t10(t1 text);
mapdql> create table t100(t2 text, shared dictionary (t2) references t10(t1));

regards