[SOLR-10341] SQL AVG function mis-interprets field type.

• Type: Bug
• Status: Resolved
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 6.5
• Component/s: Parallel SQL
• Assignee: Unassigned
• Reporter: Timothy Potter
• Created: 22/Mar/17 14:17
• Updated: 08/Oct/19 14:51
• Resolved: 07/Apr/17 12:38

Description

Using movielens data (users, movies, ratings), I tried the following SQL:

curl --data-urlencode "stmt=SELECT solr.title as title, avg(rating) as avg_rating FROM ratings INNER JOIN (select movie_id,title from movies where _query_='plot_txt_en:love') as solr ON ratings.movie_id = solr.movie_id GROUP BY title ORDER BY avg_rating DESC LIMIT 10" "http://localhost:8983/solr/movies/sql?aggregationMode=facet"

Solr returns this error:

{"result-set":{"docs":[{"EXCEPTION":"Failed to execute sqlQuery 'SELECT solr.title as title, avg(rating) as avg_rating FROM ratings INNER JOIN (select movie_id,title from movies where _query_='plot_txt_en:love') as solr ON ratings.movie_id = solr.movie_id GROUP BY title ORDER BY avg_rating DESC LIMIT 10' against JDBC connection 'jdbc:calcitesolr:'.\nError while executing SQL \"SELECT solr.title as title, avg(rating) as avg_rating FROM ratings INNER JOIN (select movie_id,title from movies where _query_='plot_txt_en:love') as solr ON ratings.movie_id = solr.movie_id GROUP BY title ORDER BY avg_rating DESC LIMIT 10\": From line 1, column 29 to line 1, column 39: Cannot apply 'AVG' to arguments of type 'AVG(<JAVATYPE(CLASS JAVA.LANG.STRING)>)'. Supported form(s): 'AVG(<NUMERIC>)'","EOF":true,"RESPONSE_TIME":92}]}}

rating is a TrieInt with docValues enabled.

<field name="rating" type="tint" docValues="true" multiValued="false" indexed="true" stored="true"/>

see screenshot