Querying Existing Query Functions Failing with no such function: cos


#1

Hello, I am using the query existing queries feature for my redash app and when I try to run my queries I get the following error. I should probably also add that this is a nested query, so query_132001 is actually a table that pulls from another query_table is that possible?:

Error running query: no such function: COS

I was also getting the same issue when I used PI() and removed it but was still getting errors

SELECT count(*) AS transactionvolume
   FROM query_132001 p2
   WHERE p2.livingspacesquarefootageag BETWEEN p1.livingspacesquarefootageag * 0.8 AND p1.livingspacesquarefootageag * 1.2
     AND p2.bathroomsFull BETWEEN p1.bathroomsfull - 1 AND p1.bathroomsfull + 1
     AND p2.bedrooms BETWEEN p1.bedrooms - 1 AND p1.bedrooms + 1
     AND p2.buildingtype = buildingtype
     AND p2.community = community
     AND p2.transactiontype = 'historicalTransaction'
     AND (

    6371000.0*6371000.0*
     (
     (((p2.longitude - p1.longitude) * 2.0 * 3.141592654)/360.0*COS((p2.latitude * 2.0 * 3.141592654) /360.0)) * (((p2.longitude - p1.longitude) * 2.0 * 3.141592654)/360.0*COS((p2.latitude * 2.0 * 3.141592654) /360.0)) +

     (((p2.latitude - p1.latitude) * 2.0 * 3.141592654)/360.0) * (((p2.latitude - p1.latitude) * 2.0 * 3.141592654)/360.0)
     )

   ) < 800*800

It is strange because when I run the exact same queries with my postgres database as the data source and I don’t get any issues.


#3

The Query Results data source uses SQLite’s syntax and not the syntax of the original query. My guess is that SQLite doesn’t have cos/pi functions, but worth checking its reference.


#4

Okay thanks for the quick response, Arik that was helpful.

For anyone interested, it seems like the solution is to try and precalculate the columns using cos before you get to the SQLite stage. See these two links for more information: