Possible BUG on joins in 3.1.2

#1

Hi

after the upgrade to 3.1.2 version of the product, i cant join anymore a table more than once.

select count(*) from flights f join airports a on f.dest=a.iata join airports b f.origin=b.iata

returns this exception

Exception: Hash join failed, reason: A hash table is already built for the table of this column

on 3.1.1 the same query worked.

as a workaround i created a view (create view as select * from v_airports) and i rewrote the query using the view for the second join

select count(*) from flights f join airports a on f.dest=a.iata join v_airports b f.origin=b.iata;

if i try to use the view twice in the query i get this exception

Exception: Query couldn’t keep the entire working set of columns in GPU memory

#2

Hi

I cannot reproduce your issue at smaller scale

create table flights (flight text, dest text, origin text);
insert into flights values ('f1', 'nyc','la');
insert into flights values ('f2', 'nyc','la');
insert into flights values ('f3', 'nyc','la');

create table airports (iata text, name text);

insert into airports values ('nyc', 'New York');
insert into airports values ('la', 'Los Angeles');

select count(*) from flights f join airports d on f.dest=d.iata join airports o on f.origin=o.iata;

result

mapdql> select count(*) from flights f join airports d on f.dest=d.iata join airports o on  f.origin=o.iata;
EXPR$0
3

Could you confirm if this simple scenario works for you?

The second issue around using a view is a know issue and is being addresses. It has to do with some missing optimization of the query when using a view.

regards

#3

i get a senseless error, because the text is dictionary eoncoded

Exception: Cast from dictionary-encoded string to none-encoded would be slow

but if works for you it’s a problem of my enviroment only

creating a table airports2 works…weird.

i tried to downgrade to previous release and works flawless

#4

Hi,

Can you share your schema, what type is iata?

regards

#5

the one you supplied for test?

CREATE TABLE airports (
iata TEXT ENCODING DICT(32),
name TEXT ENCODING DICT(32))

CREATE TABLE flights (
flight TEXT ENCODING DICT(32),
dest TEXT ENCODING DICT(32),
origin TEXT ENCODING DICT(32))

mine is 16 bits encoeded