Yeah, Calcite doesn't support field aliases in the having clause. The query should work if you use count(*). We could consider this a regression, but I think this will be a won't fix.
Joel Bernstein http://joelsolr.blogspot.com/ On Tue, May 16, 2017 at 12:51 PM, Timothy Potter <thelabd...@gmail.com> wrote: > This SQL used to work pre-calcite: > > SELECT movie_id, COUNT(*) as num_ratings, avg(rating) as aggAvg FROM > ratings GROUP BY movie_id HAVING num_ratings > 100 ORDER BY aggAvg ASC > LIMIT 10 > > Now I get: > Caused by: java.io.IOException: --> > http://192.168.1.4:8983/solr/ratings_shard2_replica1/:Failed to > execute sqlQuery 'SELECT movie_id, COUNT(*) as num_ratings, > avg(rating) as aggAvg FROM ratings GROUP BY movie_id HAVING > num_ratings > 100 ORDER BY aggAvg ASC LIMIT 10' against JDBC > connection 'jdbc:calcitesolr:'. > Error while executing SQL "SELECT movie_id, COUNT(*) as num_ratings, > avg(rating) as aggAvg FROM ratings GROUP BY movie_id HAVING > num_ratings > 100 ORDER BY aggAvg ASC LIMIT 10": From line 1, column > 103 to line 1, column 113: Column 'num_ratings' not found in any table > at org.apache.solr.client.solrj.io.stream.SolrStream.read( > SolrStream.java:235) > at com.lucidworks.spark.query.TupleStreamIterator.fetchNextTuple( > TupleStreamIterator.java:82) > at com.lucidworks.spark.query.TupleStreamIterator.hasNext( > TupleStreamIterator.java:47) > ... 31 more >
