[
https://issues.apache.org/jira/browse/SOLR-10341?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15941006#comment-15941006
]
Joel Bernstein edited comment on SOLR-10341 at 3/24/17 7:44 PM:
----------------------------------------------------------------
The patch that was just added does the following:
1) It aligns the *facet* and *map_reduce* aggregation outputs fixing SOLR-9372.
Now both approaches output a long when sum, min, max are applied to long
fields.
2) The *avg* function now works on int fields. The avg function by default
always returns a *rounded long* when averaging an int field. You can *cast* to
return a double when averaging a long field:
{code}
select str_s, count(*), sum(field_i), min(field_i), max(field_i),
cast(avg(1.0 * field_i) as float)
from collection1 where text='XXXX' group by str_s
order by sum(field_i) asc limit 2
{code}
When averaging a float field a double is always returned.
3) Added aggregation support for tint, tlong and tdouble fields.
was (Author: joel.bernstein):
The patch that was just added does the following:
1) It aligns the *facet* and *map_reduce* aggregation outputs fixing SOLR-9372.
Now both approaches output a long when sum, min, max are applied to long
fields.
2) The *avg* function now works on int fields. The avg function by default
always returns a *rounded long* when averaging an int field. You can *cast* to
return a double when averaging a long field:
{code}
select str_s, count(*), sum(field_i), min(field_i), max(field_i),
cast(avg(1.0 * field_i) as float)
from collection1 where text='XXXX' group by str_s
order by sum(field_i) asc limit 2
{code}
When averaging a float field a double is always returned.
3) Added support for tint, tlong and tdouble fields.
> 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,
> SOLR-10341.patch
>
>
> 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
