[ https://issues.apache.org/jira/browse/SOLR-10341?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15938689#comment-15938689 ]
Joel Bernstein commented on SOLR-10341: --------------------------------------- We are pushing the down the AVG but always return a double from an average calculation. Calcite is expecting a long when the avg is done on an integer. > 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: dev-unsubscr...@lucene.apache.org For additional commands, e-mail: dev-h...@lucene.apache.org