Mapd query error


#1
from pymapd import connect

con = connect('mapd://mapd:HyperInteractive@deepbox.thickdata.io:9091/mapd?''protocol=binary')


c = con.cursor()
res = c.execute("SELECT subquery.sale_prnt_nam, subquery.\"Period 1 - Average\", subquery.\"Period 2 - Average\" FROM (SELECT sale_prnt_nam, coalesce(sum(acp_dcsn_ct) FILTER (WHERE ord_cre_dt BETWEEN '2018-08-05' AND '2018-08-11'), 0) / 6 AS \"Period 1 - Average\", coalesce(sum(acp_dcsn_ct) FILTER (WHERE ord_cre_dt BETWEEN '2018-06-24' AND '2018-08-04'), 0) / 41 AS \"Period 2 - Average\" FROM snidata GROUP BY sale_prnt_nam ORDER BY sale_prnt_nam LIMIT 501) AS subquery, snidata WHERE subquery.\"Period 1 - Average\" > subquery.\"Period 2 - Average\" ")

print(res.fetchall())

this is my code if i run this query it shows error as:
SQL execution error occured: Exception: Query would require a scan without a limit on table(s): snidata, $TEMPORARY_TABLE5


#2

Try changing those parameter in mapd.conf file of your instance

allow-loop-joins=true
from-table-reordering=false

Restart mapd and rerun the query. I am reading the query from the phone, so maybe i am wrong but looks like a cartesian product with a filter; are you sure that you cannot specify a equality join condition?


#3

no
and for some queries its giving result as
mapd.ttypes.TMapDException: TMapDException(error_msg=‘Exception: Sorting the result would be too slow’)

why is this issue


#4

it’s the watchdog thinking that doing the sort the query response time wouldnt be optimal stalling the system, so it’s blocking the query.

you can bypass disabling watchdog and allowing the system to run the query on CPU

enable-watchdog=false
allow-cpu-retry=true

btw: looking to your query join condition it’s a cartesian product 500*numer of records of snidata table from wehich you are not getting any data