Hash join failed, reason(s): Cannot apply hash join to inner column type BOOLEAN

HeavyDB
#1

Hi Team,

Could you please help us with this. I am trying to run below query on OMNISCI which is failing but same query works on POSTGIS.

SELECT
COUNT(),
xy.start_polygon_id,
xy.end_polygon_id
FROM
(
SELECT
t.,
b.id as start_polygon_id,
c.id as end_polygon_id
FROM test10m t
JOIN GIANT_POLYGONS b ON
ST_INTERSECTS(ST_Point(t.start_station_longitude, t.start_station_latitude), b.wkt) = ‘t’
JOIN GIANT_POLYGONS c ON
ST_INTERSECTS(ST_Point(t.end_station_longitude, t.end_station_latitude), c.wkt) = ‘t’
) xy
GROUP BY xy.start_polygon_id, xy.end_polygon_id;

All the following columns are of type DOUBLE.
start_station_longitude
start_station_latitude
end_station_longitude
end_station_latitude

When I run complete query, i get below msgs.
error_msg:multiple SQL statements not allowed

I get below error msg when I run sub query,

SELECT
  t.*,
  b.id as start_polygon_id,
  c.id as end_polygon_id
FROM test10m t
JOIN GIANT_POLYGONS b ON
  ST_INTERSECTS(ST_Point(t.start_station_longitude, t.start_station_latitude), b.wkt) = 't'
JOIN GIANT_POLYGONS c ON
  ST_INTERSECTS(ST_Point(t.end_station_longitude, t.end_station_latitude), c.wkt) = 't'

Hash join failed, reason(s): Cannot apply hash join to inner column type BOOLEAN | Cannot fall back to loop join for intermediate join quals

#2

Hi @Ajay_Chaudhary,

Welcome to our community :wink:

Using the bike-sharing dataset and the nyc_taxi_zones, I reproduced a similar query, running against an RTX 2080ti.

SELECT start_polygon_id,end_polygon_id ,count(*) 
FROM  (SELECT t.*, nyx_taxi_zone.rowid as start_polygon_id, tz.rowid as  end_polygon_id from nyc_citibike_tripdata_80m t 
JOIN nyx_taxi_zone ON  ST_CONTAINS(omnisci_geo,ST_SetSRID(ST_Point(startstationlongitude,startstationlatitude), 4326)) join nyx_taxi_zone tz on ST_CONTAINS(tz.omnisci_geo,ST_SetSRID(ST_Point(endstationlongitude,endstationlatitude), 4326)) )
)
GROUP BY 1,2;

It runs in about 2000ms using ST_CONTAINS and 8000ms using ST_INTERSECTS; I suggest you swap the conditions on the ST_* function to fire an hash-join.
check this section of our docs for the details

To speed up the query in CPU mode, you should change the fragment size according to the number of cores in the machine.

Regards,
Candido

#3

Hi,
Thank you so much. Appreciate your quick response, this issue is resolved.

I had put allow-loop-joins=true in cat /var/lib/omnisci/omnisci.conf and restarted the server.

#4

hi,

allow-loop-joins should be true by default, but the order of tables/conditions is important in order to trigger a hash join that’s really faster than a loop join