[
https://issues.apache.org/jira/browse/SOLR-10341?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15938671#comment-15938671
]
Joel Bernstein commented on SOLR-10341:
---------------------------------------
Here is the list of manual tests that I run in both *facet* and *map_reduce*
mode, which all work. The test cases cover lots of different scenarios as well.
The avg function just slipped through the cracks:
{code}
select id from collection1 limit 10
select id, test_s from collection1 limit 10
select id, test_s, test_t from collection1 limit 10
select id, test_s, test_t, year_i from collection1 limit 10
select id, test_s, test_t, year_i, price_f from collection1 limit 10
select id, test_s, test_t, year_i, price_f, prod_ss from collection1 limit 10
select id, test_s, test_t, year_i, price_f, prod_ss from collection1 where
year_i > 36 limit 10
select id, test_s, test_t, year_i, price_f, prod_ss from collection1 where
year_i > 36 and year_i < 38 limit 10
select id, test_s, test_t, year_i, price_f, prod_ss from collection1 where
(year_i > 36 and year_i < 38) limit 10
select id, test_s, test_t, year_i, price_f, prod_ss from collection1 where
(year_i > 36 and year_i < 37) limit 10
select id, test_s, test_t, year_i, price_f, prod_ss from collection1 where
(year_i > 36 and year_i < 37) or id = '410005' limit 10
select id, test_s, test_t, year_i, price_f, prod_ss from collection1 where
(year_i > 36 and year_i < 38) order by price_f desc limit 10
select id, test_s, test_t, year_i, day_i, price_f, prod_ss from collection1
where (year_i > 36 and year_i < 38) order by day_i desc, price_f desc limit 10
select id, test_s, test_t as textField, year_i, day_i, price_f, prod_ss from
collection1 where (year_i > 36 and year_i < 38) order by day_i desc, price_f
desc limit 10
select id, test_s, test_t as textField, year_i, day_i as testday, price_f,
prod_ss from collection1 where (year_i > 36 and year_i < 38) order by testday
desc, price_f desc limit 10
select id, test_s, test_t as textField, year_i, day_i as `day`, price_f,
prod_ss from collection1 where (year_i > 36 and year_i < 38) order by `day`
desc, price_f desc limit 10
select count(*) from collection1
select count(*) as `count` from collection1
select count(*), min(day_i) from collection1
select count(*), min(day_i), max(day_i) from collection1
select count(*) as `count`, min(day_i) as min_day, max(day_i) as max_day from
collection1
select count(*) as `count`, min(day_i) as min_day, max(day_i) as max_day,
sum(month_i) as sum_month from collection1
select distinct year_i from collection1
select distinct year_i from collection1 where year_i > 10
select distinct year_i from collection1 where year_i > 10 order by year_i desc
select distinct year_i, month_i from collection1 where year_i > 30 order by
year_i asc, month_i asc
select distinct year_i, month_i from collection1 where year_i > 30 order by
year_i asc, month_i desc
select distinct year_i, month_i from collection1 where year_i > 30 order by
year_i desc, month_i desc
select distinct year_i, month_i, day_i from collection1 where year_i > 30 order
by year_i desc, month_i desc, day_i desc
select distinct year_i, month_i, day_i from collection1 where year_i > 30 order
by year_i desc, month_i desc, day_i desc limit 2000
select distinct year_i as `year`, month_i as `month`, day_i as `day` from
collection1 where year_i > 30 order by year_i desc, month_i desc, day_i desc
limit 2000
select id, score from collection1 where test_t='(hello world)' order by score
limit 500
select year_i, count(*) from collection1 group by year_i
select year_i, avg(month_i) from collection1 group by year_i
select year_i, count(*) as `count` from collection1 group by year_i
select year_i, count(*) as `count` from collection1 where year_i > 30 group by
year_i
select year_i, count(*) as `count` from collection1 where year_i > 30 group by
year_i having count(*) = 200183
select year_i, count(*) as `count` from collection1 group by year_i order by
count(*) desc
select year_i, count(*) from collection1 group by year_i order by year_i asc
select year_i, count(*) as `count`, min(day_i) as min_day , max(day_i) as
max_day from collection1 group by year_i
select year_i, month_i, count(*) as `count`, min(day_i) as min_day , max(day_i)
as max_day from collection1 group by year_i, month_i
select year_i, month_i, count(*) as `count`, sum(day_i) as sum_day ,
sum(month_i) as sum_month, max(price_f) as max_price from collection1 where
year_i >30 group by year_i, month_i order by sum(day_i) desc
{code}
> 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
