Not equal to operators


#1

I am attempting to compare a table to itself based on its ID not being the same - essentially 1 to many relationships for every row, however when I use operators like != or <> I get an error for each one.

select a.*, DISTANCE_IN_METERS(a.longitude, a.latitude, b.longitude, b.latitude) from hotdog_cart a join hotdog_cart b on a.site_id <> b.site_id;

<> : Exception: Hash join failed, reason: No equijoin expression found

select a.*, DISTANCE_IN_METERS(a.longitude, a.latitude, b.longitude, b.latitude) from hotdog_cart a join hotdog_cart b on a.site_id != b.site_id;

!= : Exception: ERROR-- Parse failed: null, Bang equal ‘!=’ is not allowed under the current SQL conformance level

It sounds like these operators are just not implemented yet, but I am not sure. Any ideas?


#2

Hi,

not equals <> is implemented.

The Exception: Hash join failed, reason: No equijoin expression found message is coming from the nature of the join not producing an equijoin between the tables.

Please see:

and

http://docs.mapd.com/latest/mapd-core-guide/dml/#table-expression-and-join-support

regards


#3

If the table is of reasonable size you may be able to execute this query by starting mapd_server with the --allow-loop-joins flag (not officially supported yet).

Note we don’t push down filters below a loop join so it will actually have to iterate over NXN records. However for small tables this can still be performant on the GPU.


#4

@dwayneberry Thanks for the elaboration. I had a feeling the <> was more likely to exist than the != as the error message was more logic related. Unfortunately the one to one part will limit me for now as I am looking for more than 1:1 right now. Thanks for the guidance, I will look into querying this through impala and importing to MapD for now.


#5

Hi @Qwerty, did you try your query with --allow-loop-joins as suggested above? Depending on the cardinality you might be able to run it that way.


#6

@todd I did not as he suggested it was not supported yet. Assuming you are inferring its possible to implement, I will try it. Thanks.