[
https://issues.apache.org/jira/browse/SOLR-10341?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15936521#comment-15936521
]
Joel Bernstein commented on SOLR-10341:
---------------------------------------
Since we have no test cases for joins it's really hard to know how they well
behave. We've pushed down so much functionality and we don't know what rules
will get triggered during the joins, so its hard to know whats going to happen.
> 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]
