Timothy Potter created SOLR-10341:
-------------------------------------
Summary: SQL AVG function mis-interprets field type.
Key: SOLR-10341
URL: https://issues.apache.org/jira/browse/SOLR-10341
Project: Solr
Issue Type: Bug
Security Level: Public (Default Security Level. Issues are Public)
Components: Parallel SQL
Affects Versions: 6.5
Reporter: Timothy Potter
Attachments: Screen Shot 2017-03-22 at 8.12.33 AM.png
Using movielens data (users, movies, ratings), I tried the following SQL:
{code}
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";
{code}
Solr returns this error:
{code}
{"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}]}}
{code}
rating is a TrieInt with docValues enabled.
{code}
<field name="rating" type="tint" docValues="true" multiValued="false"
indexed="true" stored="true"/>
{code}
see screenshot
--
This message was sent by Atlassian JIRA
(v6.3.15#6346)
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
