[SOLR-16009] Solr SQL does not return documents for 'AND' operation on multi-valued field

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 8.11.1
• Fix Version/s: 9.0, 8.11.2
• Component/s: Parallel SQL
• Labels: RobustSQL
• Assignee: Timothy Potter
• Reporter: Kiran
• Created: 14/Feb/22 22:35
• Updated: 12/May/22 00:35
• Resolved: 03/Mar/22 01:40

Description

SolrSQL fails if `AND` operation is performed on multi-valued field in Solr

Data

 

[{
        "name_s":"Foo",
        "cities_ss":["SFO",
          "NYC"],
        "userid_i":1,
        "id":"1"},
      {
        "name_s":"Bar",
        "cities_ss":["SEA",
          "LA"],
        "userid_i":2,
        "id":"2"}] 

Sql query

 

select name_s, cities_ss FROM sql_test WHERE cities_ss IN ('SFO') AND cities_ss IN ('NYC') 

The above query returns no documents even though a match exists in Solr

Similar query (below) returns no documents

select name_s, cities_ss FROM sql_test WHERE cities_ss='SFO' and cities_ss='NYC' 

Multi-valued support in SQL is messy but it would be good to get support for this as filtering on multi-valued fields are quite common in Solr

This is probably a bug in Calcite giving precedence to some default rules

 

 

Issue Links

• links to: GitHub Pull Request #715
• links to: GitHub Pull Request #718
• links to: GitHub Pull Request #719
• links to: GitHub Pull Request #2644