Uploaded image for project: 'Solr'
  1. Solr
  2. SOLR-10341

SQL AVG function mis-interprets field type.

    Details

    • Type: Bug
    • Status: Resolved
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 6.5
    • Fix Version/s: None
    • Component/s: Parallel SQL
    • Security Level: Public (Default Security Level. Issues are Public)
    • Labels:
      None

      Description

      Using movielens data (users, movies, ratings), I tried the following SQL:

      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"
      

      Solr returns this error:

      {"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}]}}
      

      rating is a TrieInt with docValues enabled.

      <field name="rating" type="tint" docValues="true" multiValued="false" indexed="true" stored="true"/>
      

      see screenshot

        Attachments

        1. SOLR-10341.patch
          38 kB
          Joel Bernstein
        2. SOLR-10341.patch
          43 kB
          Joel Bernstein
        3. Screen Shot 2017-03-22 at 8.12.33 AM.png
          121 kB
          Timothy Potter

          Activity

            People

            • Assignee:
              Unassigned
              Reporter:
              thelabdude Timothy Potter
            • Votes:
              1 Vote for this issue
              Watchers:
              5 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: