You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@solr.apache.org by th...@apache.org on 2021/06/16 19:49:40 UTC
[solr] branch main updated: SOLR-15460: Implement LIKE, IS NOT NULL,
IS NULL, and support wildcard * in equals string literal (#173)
This is an automated email from the ASF dual-hosted git repository.
thelabdude pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/solr.git
The following commit(s) were added to refs/heads/main by this push:
new a79e71c SOLR-15460: Implement LIKE, IS NOT NULL, IS NULL, and support wildcard * in equals string literal (#173)
a79e71c is described below
commit a79e71c732a4488b82d24713568b1a553c85266b
Author: Timothy Potter <th...@gmail.com>
AuthorDate: Wed Jun 16 13:49:29 2021 -0600
SOLR-15460: Implement LIKE, IS NOT NULL, IS NULL, and support wildcard * in equals string literal (#173)
Also fixes SOLR-15459 & SOLR-15463
---
solr/CHANGES.txt | 2 +
.../org/apache/solr/handler/sql/SolrFilter.java | 261 +++++++++++++--------
.../org/apache/solr/handler/TestSQLHandler.java | 250 ++++++++++++++++----
.../solr-ref-guide/src/parallel-sql-interface.adoc | 21 +-
4 files changed, 383 insertions(+), 151 deletions(-)
diff --git a/solr/CHANGES.txt b/solr/CHANGES.txt
index ff4dffd..c7a611c 100644
--- a/solr/CHANGES.txt
+++ b/solr/CHANGES.txt
@@ -143,6 +143,8 @@ when told to. The admin UI now tells it to. (Nazerke Seidan, David Smiley)
* SOLR-15362: Let core and collection dropdowns in Admin UI float wide to see entire core or collection name. (Matthias Krepp, Eric Pugh)
+* SOLR-15460: Implement LIKE, IS NOT NULL, IS NULL, and support wildcard * in equals string literal for Parallel SQL (Timothy Potter, Houston Putman)
+
Other Changes
----------------------
* SOLR-14656: Autoscaling framework removed (Ishan Chattopadhyaya, noble, Ilan Ginzburg)
diff --git a/solr/core/src/java/org/apache/solr/handler/sql/SolrFilter.java b/solr/core/src/java/org/apache/solr/handler/sql/SolrFilter.java
index e35a238..2f81750 100644
--- a/solr/core/src/java/org/apache/solr/handler/sql/SolrFilter.java
+++ b/solr/core/src/java/org/apache/solr/handler/sql/SolrFilter.java
@@ -16,7 +16,15 @@
*/
package org.apache.solr.handler.sql;
-import org.apache.calcite.plan.*;
+import java.util.ArrayList;
+import java.util.List;
+import java.util.Map;
+
+import org.apache.calcite.plan.RelOptCluster;
+import org.apache.calcite.plan.RelOptCost;
+import org.apache.calcite.plan.RelOptPlanner;
+import org.apache.calcite.plan.RelOptUtil;
+import org.apache.calcite.plan.RelTraitSet;
import org.apache.calcite.rel.RelNode;
import org.apache.calcite.rel.core.Filter;
import org.apache.calcite.rel.metadata.RelMetadataQuery;
@@ -27,10 +35,6 @@ import org.apache.calcite.rex.RexNode;
import org.apache.calcite.sql.SqlKind;
import org.apache.calcite.util.Pair;
-import java.util.ArrayList;
-import java.util.List;
-import java.util.Map;
-
/**
* Implementation of a {@link org.apache.calcite.rel.core.Filter} relational expression in Solr.
*/
@@ -45,7 +49,8 @@ class SolrFilter extends Filter implements SolrRel {
assert getConvention() == child.getConvention();
}
- @Override public RelOptCost computeSelfCost(RelOptPlanner planner, RelMetadataQuery mq) {
+ @Override
+ public RelOptCost computeSelfCost(RelOptPlanner planner, RelMetadataQuery mq) {
return super.computeSelfCost(planner, mq).multiplyBy(0.1);
}
@@ -55,7 +60,7 @@ class SolrFilter extends Filter implements SolrRel {
public void implement(Implementor implementor) {
implementor.visitChild(0, getInput());
- if(getInput() instanceof SolrAggregate) {
+ if (getInput() instanceof SolrAggregate) {
HavingTranslator translator = new HavingTranslator(SolrRules.solrFieldNames(getRowType()), implementor.reverseAggMappings);
String havingPredicate = translator.translateMatch(condition);
implementor.setHavingPredicate(havingPredicate);
@@ -69,26 +74,84 @@ class SolrFilter extends Filter implements SolrRel {
private static class Translator {
- private final List<String> fieldNames;
+ protected final List<String> fieldNames;
public boolean negativeQuery = true;
Translator(List<String> fieldNames) {
this.fieldNames = fieldNames;
}
- private String translateMatch(RexNode condition) {
- if (condition.getKind().belongsTo(SqlKind.COMPARISON)) {
+ protected String translateMatch(RexNode condition) {
+ final SqlKind kind = condition.getKind();
+ if (kind.belongsTo(SqlKind.COMPARISON) || kind == SqlKind.NOT) {
return translateComparison(condition);
} else if (condition.isA(SqlKind.AND)) {
- return "(" + translateAnd(condition) + ")";
+ // see if this is a translated range query of greater than or equals and less than or equal on same field
+ // if so, then collapse into a single range criteria, e.g. field:[gte TO lte] instead of two ranges AND'd together
+ RexCall call = (RexCall) condition;
+ List<RexNode> operands = call.getOperands();
+ String query = null;
+ if (operands.size() == 2) {
+ RexNode lhs = operands.get(0);
+ RexNode rhs = operands.get(1);
+ if (lhs.getKind() == SqlKind.GREATER_THAN_OR_EQUAL && rhs.getKind() == SqlKind.LESS_THAN_OR_EQUAL) {
+ query = translateBetween(lhs, rhs);
+ } else if (lhs.getKind() == SqlKind.LESS_THAN_OR_EQUAL && rhs.getKind() == SqlKind.GREATER_THAN_OR_EQUAL) {
+ // just swap the nodes
+ query = translateBetween(rhs, lhs);
+ }
+ }
+ return query != null ? query : "(" + translateAnd(condition) + ")";
} else if (condition.isA(SqlKind.OR)) {
return "(" + translateOr(condition) + ")";
+ } else if (kind == SqlKind.LIKE) {
+ return translateLike(condition, false);
+ } else if (kind == SqlKind.IS_NOT_NULL || kind == SqlKind.IS_NULL) {
+ return translateIsNullOrIsNotNull(condition);
} else {
return null;
}
}
- private String translateOr(RexNode condition) {
+ protected String translateBetween(RexNode gteNode, RexNode lteNode) {
+ Pair<String, RexLiteral> gte = getFieldValuePair(gteNode);
+ Pair<String, RexLiteral> lte = getFieldValuePair(lteNode);
+ String fieldName = gte.getKey();
+ String query = null;
+ if (fieldName.equals(lte.getKey()) && compareRexLiteral(gte.right, lte.right) < 0) {
+ query = fieldName + ":[" + gte.getValue() + " TO " + lte.getValue() + "]";
+ this.negativeQuery = false; // so we don't get *:* AND range
+ }
+ return query;
+ }
+
+ @SuppressWarnings("unchecked")
+ private int compareRexLiteral(final RexLiteral gte, final RexLiteral lte) {
+ return gte.getValue().compareTo(lte.getValue());
+ }
+
+ protected String translateIsNullOrIsNotNull(RexNode node) {
+ if (!(node instanceof RexCall)) {
+ throw new AssertionError("expected RexCall for predicate but found: " + node);
+ }
+ RexCall call = (RexCall) node;
+ List<RexNode> operands = call.getOperands();
+ if (operands.size() != 1) {
+ throw new AssertionError("expected 1 operand for " + node);
+ }
+
+ final RexNode left = operands.get(0);
+ if (left instanceof RexInputRef) {
+ String name = fieldNames.get(((RexInputRef) left).getIndex());
+ SqlKind kind = node.getKind();
+ this.negativeQuery = false;
+ return kind == SqlKind.IS_NOT_NULL ? "+" + name + ":*" : "(*:* -" + name + ":*)";
+ }
+
+ throw new AssertionError("expected field ref but found " + left);
+ }
+
+ protected String translateOr(RexNode condition) {
List<String> ors = new ArrayList<>();
for (RexNode node : RelOptUtil.disjunctions(condition)) {
ors.add(translateMatch(node));
@@ -96,7 +159,7 @@ class SolrFilter extends Filter implements SolrRel {
return String.join(" OR ", ors);
}
- private String translateAnd(RexNode node0) {
+ protected String translateAnd(RexNode node0) {
List<String> andStrings = new ArrayList<>();
List<String> notStrings = new ArrayList<>();
@@ -111,7 +174,7 @@ class SolrFilter extends Filter implements SolrRel {
String andString = String.join(" AND ", andStrings);
- if (nots.size() > 0) {
+ if (!nots.isEmpty()) {
for (RexNode node : nots) {
notStrings.add(translateMatch(node));
}
@@ -122,23 +185,43 @@ class SolrFilter extends Filter implements SolrRel {
}
}
- private String translateComparison(RexNode node) {
- Pair<String, RexLiteral> binaryTranslated = null;
- if (((RexCall) node).getOperands().size() == 2) {
- binaryTranslated = translateBinary((RexCall) node);
+ protected String translateLike(RexNode like, boolean isNegativeQuery) {
+ Pair<String, RexLiteral> pair = getFieldValuePair(like);
+ String terms = pair.getValue().toString().trim();
+ terms = terms.replace("'", "").replace('%', '*').replace('_', '?');
+ boolean wrappedQuotes = false;
+ if (!terms.startsWith("(") && !terms.startsWith("[") && !terms.startsWith("{")) {
+ terms = "\"" + terms + "\"";
+ wrappedQuotes = true;
}
- switch (node.getKind()) {
- case NOT:
- return "-" + translateComparison(((RexCall) node).getOperands().get(0));
+ this.negativeQuery = isNegativeQuery;
+ String query = pair.getKey() + ":" + terms;
+ return wrappedQuotes ? "{!complexphrase}" + query : query;
+ }
+
+ protected String translateComparison(RexNode node) {
+ final SqlKind kind = node.getKind();
+ if (kind == SqlKind.NOT) {
+ RexNode negated = ((RexCall) node).getOperands().get(0);
+ return "-" + (negated.getKind() == SqlKind.LIKE ? translateLike(negated, true) : translateComparison(negated));
+ }
+
+ Pair<String, RexLiteral> binaryTranslated = getFieldValuePair(node);
+ switch (kind) {
case EQUALS:
String terms = binaryTranslated.getValue().toString().trim();
- terms = terms.replace("'","");
+ terms = terms.replace("'", "");
+ boolean wrappedQuotes = false;
if (!terms.startsWith("(") && !terms.startsWith("[") && !terms.startsWith("{")) {
terms = "\"" + terms + "\"";
+ wrappedQuotes = true;
}
String clause = binaryTranslated.getKey() + ":" + terms;
+ if (terms.contains("*") && wrappedQuotes) {
+ clause = "{!complexphrase}" + clause;
+ }
this.negativeQuery = false;
return clause;
case NOT_EQUALS:
@@ -155,15 +238,34 @@ class SolrFilter extends Filter implements SolrRel {
case GREATER_THAN_OR_EQUAL:
this.negativeQuery = false;
return "(" + binaryTranslated.getKey() + ": [ " + binaryTranslated.getValue() + " TO * ])";
+ case LIKE:
+ return translateLike(node, false);
+ case IS_NOT_NULL:
+ case IS_NULL:
+ return translateIsNullOrIsNotNull(node);
default:
throw new AssertionError("cannot translate " + node);
}
}
+ protected Pair<String, RexLiteral> getFieldValuePair(RexNode node) {
+ if (!(node instanceof RexCall)) {
+ throw new AssertionError("expected RexCall for predicate but found: " + node);
+ }
+
+ RexCall call = (RexCall) node;
+ Pair<String, RexLiteral> binaryTranslated = call.getOperands().size() == 2 ? translateBinary(call) : null;
+ if (binaryTranslated == null) {
+ throw new AssertionError("unsupported predicate expression: " + node);
+ }
+
+ return binaryTranslated;
+ }
+
/**
* Translates a call to a binary operator, reversing arguments if necessary.
*/
- private Pair<String, RexLiteral> translateBinary(RexCall call) {
+ protected Pair<String, RexLiteral> translateBinary(RexCall call) {
List<RexNode> operands = call.getOperands();
if (operands.size() != 2) {
throw new AssertionError("Invalid number of arguments - " + operands.size());
@@ -184,13 +286,11 @@ class SolrFilter extends Filter implements SolrRel {
/**
* Translates a call to a binary operator. Returns whether successful.
*/
- private Pair<String, RexLiteral> translateBinary2(RexNode left, RexNode right) {
- switch (right.getKind()) {
- case LITERAL:
- break;
- default:
- return null;
+ protected Pair<String, RexLiteral> translateBinary2(RexNode left, RexNode right) {
+ if (right.getKind() != SqlKind.LITERAL) {
+ return null;
}
+
final RexLiteral rightLiteral = (RexLiteral) right;
switch (left.getKind()) {
case INPUT_REF:
@@ -210,17 +310,17 @@ class SolrFilter extends Filter implements SolrRel {
}
}
- private static class HavingTranslator {
+ private static class HavingTranslator extends Translator {
- private final List<String> fieldNames;
- private Map<String,String> reverseAggMappings;
+ private final Map<String, String> reverseAggMappings;
HavingTranslator(List<String> fieldNames, Map<String, String> reverseAggMappings) {
- this.fieldNames = fieldNames;
+ super(fieldNames);
this.reverseAggMappings = reverseAggMappings;
}
- private String translateMatch(RexNode condition) {
+ @Override
+ protected String translateMatch(RexNode condition) {
if (condition.getKind().belongsTo(SqlKind.COMPARISON)) {
return translateComparison(condition);
} else if (condition.isA(SqlKind.AND)) {
@@ -232,7 +332,8 @@ class SolrFilter extends Filter implements SolrRel {
}
}
- private String translateOr(RexNode condition) {
+ @Override
+ protected String translateOr(RexNode condition) {
List<String> ors = new ArrayList<>();
for (RexNode node : RelOptUtil.disjunctions(condition)) {
ors.add(translateMatch(node));
@@ -240,8 +341,7 @@ class SolrFilter extends Filter implements SolrRel {
StringBuilder builder = new StringBuilder();
builder.append("or(");
- int i = 0;
- for (i = 0; i < ors.size(); i++) {
+ for (int i = 0; i < ors.size(); i++) {
if (i > 0) {
builder.append(",");
}
@@ -252,7 +352,8 @@ class SolrFilter extends Filter implements SolrRel {
return builder.toString();
}
- private String translateAnd(RexNode node0) {
+ @Override
+ protected String translateAnd(RexNode node0) {
List<String> andStrings = new ArrayList<>();
List<String> notStrings = new ArrayList<>();
@@ -278,14 +379,14 @@ class SolrFilter extends Filter implements SolrRel {
builder.append(")");
- if (nots.size() > 0) {
+ if (!nots.isEmpty()) {
for (RexNode node : nots) {
notStrings.add(translateMatch(node));
}
StringBuilder notBuilder = new StringBuilder();
- for(int i=0; i< notStrings.size(); i++) {
- if(i > 0) {
+ for (int i = 0; i < notStrings.size(); i++) {
+ if (i > 0) {
notBuilder.append(",");
}
notBuilder.append("not(");
@@ -293,42 +394,17 @@ class SolrFilter extends Filter implements SolrRel {
notBuilder.append(")");
}
- return "and(" + builder.toString() + ","+ notBuilder.toString()+")";
+ return "and(" + builder.toString() + "," + notBuilder.toString() + ")";
} else {
return builder.toString();
}
}
- private String translateComparison(RexNode node) {
- Pair<String, RexLiteral> binaryTranslated = null;
- if (((RexCall) node).getOperands().size() == 2) {
- binaryTranslated = translateBinary((RexCall) node);
- }
-
- switch (node.getKind()) {
- case EQUALS:
- String terms = binaryTranslated.getValue().toString().trim();
- String clause = "eq(" + binaryTranslated.getKey() + "," + terms + ")";
- return clause;
- case NOT_EQUALS:
- return "not(eq(" + binaryTranslated.getKey() + "," + binaryTranslated.getValue() + "))";
- case LESS_THAN:
- return "lt(" + binaryTranslated.getKey() + "," + binaryTranslated.getValue() + ")";
- case LESS_THAN_OR_EQUAL:
- return "lteq(" + binaryTranslated.getKey() + "," + binaryTranslated.getValue() + ")";
- case GREATER_THAN:
- return "gt(" + binaryTranslated.getKey() + "," + binaryTranslated.getValue() + ")";
- case GREATER_THAN_OR_EQUAL:
- return "gteq(" + binaryTranslated.getKey() + "," + binaryTranslated.getValue() + ")";
- default:
- throw new AssertionError("cannot translate " + node);
- }
- }
-
/**
* Translates a call to a binary operator, reversing arguments if necessary.
*/
- private Pair<String, RexLiteral> translateBinary(RexCall call) {
+ @Override
+ protected Pair<String, RexLiteral> translateBinary(RexCall call) {
List<RexNode> operands = call.getOperands();
if (operands.size() != 2) {
throw new AssertionError("Invalid number of arguments - " + operands.size());
@@ -338,8 +414,8 @@ class SolrFilter extends Filter implements SolrRel {
final Pair<String, RexLiteral> a = translateBinary2(left, right);
if (a != null) {
- if(reverseAggMappings.containsKey(a.getKey())) {
- return new Pair<String, RexLiteral>(reverseAggMappings.get(a.getKey()),a.getValue());
+ if (reverseAggMappings.containsKey(a.getKey())) {
+ return new Pair<>(reverseAggMappings.get(a.getKey()), a.getValue());
}
return a;
}
@@ -350,32 +426,25 @@ class SolrFilter extends Filter implements SolrRel {
throw new AssertionError("cannot translate call " + call);
}
- /**
- * Translates a call to a binary operator. Returns whether successful.
- */
- private Pair<String, RexLiteral> translateBinary2(RexNode left, RexNode right) {
- switch (right.getKind()) {
- case LITERAL:
- break;
- default:
- return null;
- }
-
- final RexLiteral rightLiteral = (RexLiteral) right;
- switch (left.getKind()) {
- case INPUT_REF:
- final RexInputRef left1 = (RexInputRef) left;
- String name = fieldNames.get(left1.getIndex());
- return new Pair<>(name, rightLiteral);
- case CAST:
- return translateBinary2(((RexCall) left).operands.get(0), right);
-// case OTHER_FUNCTION:
-// String itemName = SolrRules.isItem((RexCall) left);
-// if (itemName != null) {
-// return translateOp2(op, itemName, rightLiteral);
-// }
+ @Override
+ protected String translateComparison(RexNode node) {
+ Pair<String, RexLiteral> binaryTranslated = getFieldValuePair(node);
+ switch (node.getKind()) {
+ case EQUALS:
+ String terms = binaryTranslated.getValue().toString().trim();
+ return "eq(" + binaryTranslated.getKey() + "," + terms + ")";
+ case NOT_EQUALS:
+ return "not(eq(" + binaryTranslated.getKey() + "," + binaryTranslated.getValue() + "))";
+ case LESS_THAN:
+ return "lt(" + binaryTranslated.getKey() + "," + binaryTranslated.getValue() + ")";
+ case LESS_THAN_OR_EQUAL:
+ return "lteq(" + binaryTranslated.getKey() + "," + binaryTranslated.getValue() + ")";
+ case GREATER_THAN:
+ return "gt(" + binaryTranslated.getKey() + "," + binaryTranslated.getValue() + ")";
+ case GREATER_THAN_OR_EQUAL:
+ return "gteq(" + binaryTranslated.getKey() + "," + binaryTranslated.getValue() + ")";
default:
- return null;
+ throw new AssertionError("cannot translate " + node);
}
}
}
diff --git a/solr/core/src/test/org/apache/solr/handler/TestSQLHandler.java b/solr/core/src/test/org/apache/solr/handler/TestSQLHandler.java
index 5bc68532..c105191 100644
--- a/solr/core/src/test/org/apache/solr/handler/TestSQLHandler.java
+++ b/solr/core/src/test/org/apache/solr/handler/TestSQLHandler.java
@@ -18,6 +18,7 @@ package org.apache.solr.handler;
import java.io.IOException;
import java.util.ArrayList;
+import java.util.Arrays;
import java.util.List;
import java.util.Map;
@@ -31,6 +32,7 @@ import org.apache.solr.client.solrj.io.stream.TupleStream;
import org.apache.solr.client.solrj.request.CollectionAdminRequest;
import org.apache.solr.client.solrj.request.UpdateRequest;
import org.apache.solr.cloud.SolrCloudTestCase;
+import org.apache.solr.common.SolrInputDocument;
import org.apache.solr.common.params.CommonParams;
import org.apache.solr.common.params.ModifiableSolrParams;
import org.apache.solr.common.params.SolrParams;
@@ -40,7 +42,7 @@ import org.junit.Test;
@Slow
@SolrTestCaseJ4.SuppressSSL
-@LuceneTestCase.SuppressCodecs({"Lucene3x", "Lucene40","Lucene41","Lucene42","Lucene45"})
+@LuceneTestCase.SuppressCodecs({"Lucene3x", "Lucene40", "Lucene41", "Lucene42", "Lucene45"})
public class TestSQLHandler extends SolrCloudTestCase {
private static final String COLLECTIONORALIAS = "collection1";
@@ -72,6 +74,16 @@ public class TestSQLHandler extends SolrCloudTestCase {
}
}
+ public static SolrParams mapParams(String... vals) {
+ ModifiableSolrParams params = new ModifiableSolrParams();
+ assertEquals("Parameters passed in here must be in pairs!", 0, (vals.length % 2));
+ for (int idx = 0; idx < vals.length; idx += 2) {
+ params.add(vals[idx], vals[idx + 1]);
+ }
+
+ return params;
+ }
+
@Before
public void cleanIndex() throws Exception {
new UpdateRequest()
@@ -98,7 +110,7 @@ public class TestSQLHandler extends SolrCloudTestCase {
"stmt",
"select id, field_i, str_s, field_f, field_d, field_l from collection1 where (text_t='(XXXX)' OR text_t='XXXX') AND text_t='XXXX' order by field_i desc");
- String baseUrl = cluster.getJettySolrRunners().get(0).getBaseUrl().toString()+"/"+COLLECTIONORALIAS;
+ String baseUrl = cluster.getJettySolrRunners().get(0).getBaseUrl().toString() + "/" + COLLECTIONORALIAS;
List<Tuple> tuples = getTuples(sParams, baseUrl);
assert (tuples.size() == 8);
@@ -317,7 +329,6 @@ public class TestSQLHandler extends SolrCloudTestCase {
}
-
@Test
public void testWhere() throws Exception {
@@ -332,7 +343,7 @@ public class TestSQLHandler extends SolrCloudTestCase {
.add("id", "8", "text_t", "XXXX XXXX", "str_s", "c", "field_i", "60")
.commit(cluster.getSolrClient(), COLLECTIONORALIAS);
- String baseUrl = cluster.getJettySolrRunners().get(0).getBaseUrl().toString()+"/"+COLLECTIONORALIAS;
+ String baseUrl = cluster.getJettySolrRunners().get(0).getBaseUrl().toString() + "/" + COLLECTIONORALIAS;
// Equals
SolrParams sParams = mapParams(CommonParams.QT, "/sql",
@@ -349,7 +360,7 @@ public class TestSQLHandler extends SolrCloudTestCase {
sParams = mapParams(CommonParams.QT, "/sql",
"stmt", "select id from collection1 where id <> 1 order by id asc limit 10");
- tuples = getTuples(sParams,baseUrl);
+ tuples = getTuples(sParams, baseUrl);
assertEquals(7, tuples.size());
@@ -456,7 +467,7 @@ public class TestSQLHandler extends SolrCloudTestCase {
.add("id", "8", "Text_t", "XXXX XXXX", "Str_s", "c", "Field_i", "60")
.commit(cluster.getSolrClient(), COLLECTIONORALIAS);
- String baseUrl = cluster.getJettySolrRunners().get(0).getBaseUrl().toString()+"/"+COLLECTIONORALIAS;
+ String baseUrl = cluster.getJettySolrRunners().get(0).getBaseUrl().toString() + "/" + COLLECTIONORALIAS;
SolrParams sParams = mapParams(CommonParams.QT, "/sql", "aggregationMode", "map_reduce",
"stmt", "select id, Field_i, Str_s from collection1 where Text_t='XXXX' order by Field_i desc");
@@ -556,7 +567,7 @@ public class TestSQLHandler extends SolrCloudTestCase {
.add("id", "8", "text_t", "XXXX XXXX", "str_s", "c", "field_i", "60")
.commit(cluster.getSolrClient(), COLLECTIONORALIAS);
- String baseUrl = cluster.getJettySolrRunners().get(0).getBaseUrl().toString()+"/"+COLLECTIONORALIAS;
+ String baseUrl = cluster.getJettySolrRunners().get(0).getBaseUrl().toString() + "/" + COLLECTIONORALIAS;
SolrParams sParams = mapParams(CommonParams.QT, "/sql", "aggregationMode", "facet",
@@ -729,7 +740,6 @@ public class TestSQLHandler extends SolrCloudTestCase {
}
-
@Test
public void testSelectDistinct() throws Exception {
@@ -744,7 +754,7 @@ public class TestSQLHandler extends SolrCloudTestCase {
.add("id", "8", "text_t", "XXXX XXXX", "str_s", "c", "field_i", "60")
.commit(cluster.getSolrClient(), COLLECTIONORALIAS);
- String baseUrl = cluster.getJettySolrRunners().get(0).getBaseUrl().toString()+"/"+COLLECTIONORALIAS;
+ String baseUrl = cluster.getJettySolrRunners().get(0).getBaseUrl().toString() + "/" + COLLECTIONORALIAS;
SolrParams sParams = mapParams(CommonParams.QT, "/sql", "aggregationMode", "map_reduce",
"stmt", "select distinct str_s, field_i from collection1 order by str_s asc, field_i asc");
@@ -919,7 +929,7 @@ public class TestSQLHandler extends SolrCloudTestCase {
.add("id", "8", "text_t", "XXXX XXXX", "str_s", "c", "field_i", "60")
.commit(cluster.getSolrClient(), COLLECTIONORALIAS);
- String baseUrl = cluster.getJettySolrRunners().get(0).getBaseUrl().toString()+"/"+COLLECTIONORALIAS;
+ String baseUrl = cluster.getJettySolrRunners().get(0).getBaseUrl().toString() + "/" + COLLECTIONORALIAS;
SolrParams sParams = mapParams(CommonParams.QT, "/sql", "numWorkers", "2", "aggregationMode", "map_reduce",
"stmt", "select distinct str_s, field_i from collection1 order by str_s asc, field_i asc");
@@ -1098,7 +1108,7 @@ public class TestSQLHandler extends SolrCloudTestCase {
.add("id", "9", "text_t", "XXXX XXXY", "str_s", "d", "field_i", "70")
.commit(cluster.getSolrClient(), COLLECTIONORALIAS);
- String baseUrl = cluster.getJettySolrRunners().get(0).getBaseUrl().toString()+"/"+COLLECTIONORALIAS;
+ String baseUrl = cluster.getJettySolrRunners().get(0).getBaseUrl().toString() + "/" + COLLECTIONORALIAS;
SolrParams sParams = mapParams(CommonParams.QT, "/sql", "aggregationMode", "facet",
"stmt", "select str_s, count(*), sum(field_i), min(field_i), max(field_i), " +
@@ -1303,7 +1313,7 @@ public class TestSQLHandler extends SolrCloudTestCase {
.add(id, "9", "a_s", "hello0", "a_i", "14", "a_f", "10")
.commit(cluster.getSolrClient(), COLLECTIONORALIAS);
- String baseUrl = cluster.getJettySolrRunners().get(0).getBaseUrl().toString()+"/"+COLLECTIONORALIAS;
+ String baseUrl = cluster.getJettySolrRunners().get(0).getBaseUrl().toString() + "/" + COLLECTIONORALIAS;
SolrParams sParams = mapParams(CommonParams.QT, "/sql", "stmt",
"select count(*), sum(a_i), min(a_i), max(a_i), cast(avg(1.0 * a_i) as float), sum(a_f), " +
@@ -1441,7 +1451,7 @@ public class TestSQLHandler extends SolrCloudTestCase {
// Test zero hits
sParams = mapParams(CommonParams.QT, "/sql",
"stmt", "select count(*), sum(a_i), min(a_i), max(a_i), cast(avg(1.0 * a_i) as float), sum(a_f), " +
- "min(a_f), max(a_f), avg(a_f) from collection1 where a_s = 'blah'");
+ "min(a_f), max(a_f), avg(a_f) from collection1 where a_s = 'blah'");
tuples = getTuples(sParams, baseUrl);
@@ -1487,7 +1497,7 @@ public class TestSQLHandler extends SolrCloudTestCase {
.commit(cluster.getSolrClient(), COLLECTIONORALIAS);
- String baseUrl = cluster.getJettySolrRunners().get(0).getBaseUrl().toString()+"/"+COLLECTIONORALIAS;
+ String baseUrl = cluster.getJettySolrRunners().get(0).getBaseUrl().toString() + "/" + COLLECTIONORALIAS;
SolrParams sParams = mapParams(CommonParams.QT, "/sql", "aggregationMode", "map_reduce",
"stmt", "select year_i, sum(item_i) from collection1 group by year_i order by year_i desc");
@@ -1590,7 +1600,7 @@ public class TestSQLHandler extends SolrCloudTestCase {
.commit(cluster.getSolrClient(), COLLECTIONORALIAS);
- String baseUrl = cluster.getJettySolrRunners().get(0).getBaseUrl().toString()+"/"+COLLECTIONORALIAS;
+ String baseUrl = cluster.getJettySolrRunners().get(0).getBaseUrl().toString() + "/" + COLLECTIONORALIAS;
SolrParams sParams = mapParams(CommonParams.QT, "/sql", "aggregationMode", "map_reduce",
"stmt", "select id, str_s from collection1 where text_t='XXXX' order by field_iff desc");
@@ -1632,7 +1642,6 @@ public class TestSQLHandler extends SolrCloudTestCase {
assert (tuple.getException().contains("No match found for function signature blah"));
}
-
@Test
public void testTimeSeriesGroupingFacet() throws Exception {
@@ -1648,7 +1657,7 @@ public class TestSQLHandler extends SolrCloudTestCase {
.commit(cluster.getSolrClient(), COLLECTIONORALIAS);
- String baseUrl = cluster.getJettySolrRunners().get(0).getBaseUrl().toString()+"/"+COLLECTIONORALIAS;
+ String baseUrl = cluster.getJettySolrRunners().get(0).getBaseUrl().toString() + "/" + COLLECTIONORALIAS;
SolrParams sParams = mapParams(CommonParams.QT, "/sql", "aggregationMode", "facet",
"stmt", "select year_i, sum(item_i) from collection1 group by year_i order by year_i desc");
@@ -1751,7 +1760,7 @@ public class TestSQLHandler extends SolrCloudTestCase {
.commit(cluster.getSolrClient(), COLLECTIONORALIAS);
- String baseUrl = cluster.getJettySolrRunners().get(0).getBaseUrl().toString()+"/"+COLLECTIONORALIAS;
+ String baseUrl = cluster.getJettySolrRunners().get(0).getBaseUrl().toString() + "/" + COLLECTIONORALIAS;
SolrParams sParams = mapParams(CommonParams.QT, "/sql", "numWorkers", "2", "aggregationMode", "map_reduce",
"stmt", "select year_i, sum(item_i) from collection1 group by year_i order by year_i desc");
@@ -1843,23 +1852,22 @@ public class TestSQLHandler extends SolrCloudTestCase {
}
-
public boolean assertLong(Tuple tuple, String fieldName, long l) throws Exception {
- long lv = (long)tuple.get(fieldName);
- if(lv != l) {
- throw new Exception("Longs not equal:"+l+" : "+lv);
+ long lv = (long) tuple.get(fieldName);
+ if (lv != l) {
+ throw new Exception("Longs not equal:" + l + " : " + lv);
}
return true;
}
public boolean assertString(Tuple tuple, String fieldName, String expected) throws Exception {
- String actual = (String)tuple.get(fieldName);
+ String actual = (String) tuple.get(fieldName);
- if( (null == expected && null != actual) ||
+ if ((null == expected && null != actual) ||
(null != expected && null == actual) ||
- (null != expected && !expected.equals(actual))){
- throw new Exception("Longs not equal:"+expected+" : "+actual);
+ (null != expected && !expected.equals(actual))) {
+ throw new Exception("Longs not equal:" + expected + " : " + actual);
}
return true;
@@ -1867,25 +1875,25 @@ public class TestSQLHandler extends SolrCloudTestCase {
public boolean assertDouble(Tuple tuple, String fieldName, double d) throws Exception {
double dv = tuple.getDouble(fieldName);
- if(dv != d) {
- throw new Exception("Doubles not equal:"+d+" : "+dv);
+ if (dv != d) {
+ throw new Exception("Doubles not equal:" + d + " : " + dv);
}
return true;
}
- protected boolean assertMaps(@SuppressWarnings({"rawtypes"})List<Map> maps, int... ids) throws Exception {
- if(maps.size() != ids.length) {
- throw new Exception("Expected id count != actual map count:"+ids.length+":"+maps.size());
+ protected boolean assertMaps(@SuppressWarnings({"rawtypes"}) List<Map> maps, int... ids) throws Exception {
+ if (maps.size() != ids.length) {
+ throw new Exception("Expected id count != actual map count:" + ids.length + ":" + maps.size());
}
- int i=0;
- for(int val : ids) {
+ int i = 0;
+ for (int val : ids) {
@SuppressWarnings({"rawtypes"})
Map t = maps.get(i);
- String tip = (String)t.get("id");
- if(!tip.equals(Integer.toString(val))) {
- throw new Exception("Found value:"+tip+" expecting:"+val);
+ String tip = (String) t.get("id");
+ if (!tip.equals(Integer.toString(val))) {
+ throw new Exception("Found value:" + tip + " expecting:" + val);
}
++i;
}
@@ -1898,7 +1906,7 @@ public class TestSQLHandler extends SolrCloudTestCase {
tupleStream.open();
List<Tuple> tuples = new ArrayList<>();
- for (;;) {
+ for (; ; ) {
Tuple t = tupleStream.read();
//log.info(" ... {}", t.fields);
if (t.EOF) {
@@ -1911,16 +1919,6 @@ public class TestSQLHandler extends SolrCloudTestCase {
return tuples;
}
- public static SolrParams mapParams(String... vals) {
- ModifiableSolrParams params = new ModifiableSolrParams();
- assertEquals("Parameters passed in here must be in pairs!", 0, (vals.length % 2));
- for (int idx = 0; idx < vals.length; idx += 2) {
- params.add(vals[idx], vals[idx + 1]);
- }
-
- return params;
- }
-
protected Tuple getTuple(TupleStream tupleStream) throws IOException {
tupleStream.open();
Tuple t = tupleStream.read();
@@ -1928,4 +1926,162 @@ public class TestSQLHandler extends SolrCloudTestCase {
return t;
}
+ @Test
+ public void testIn() throws Exception {
+ new UpdateRequest()
+ .add("id", "1", "text_t", "foobar", "str_s", "a")
+ .add("id", "2", "text_t", "foobaz", "str_s", "b")
+ .add("id", "3", "text_t", "foobaz", "str_s", "c")
+ .add("id", "4", "text_t", "foobaz", "str_s", "d")
+ .commit(cluster.getSolrClient(), COLLECTIONORALIAS);
+
+ SolrParams sParams = mapParams(CommonParams.QT, "/sql",
+ "stmt",
+ "select id from collection1 where str_s IN ('a','b','c')");
+
+ String baseUrl = cluster.getJettySolrRunners().get(0).getBaseUrl().toString() + "/" + COLLECTIONORALIAS;
+ List<Tuple> tuples = getTuples(sParams, baseUrl);
+ assertEquals(3, tuples.size());
+ }
+
+ private String sqlUrl() {
+ return cluster.getJettySolrRunners().get(0).getBaseUrl().toString() + "/" + COLLECTIONORALIAS;
+ }
+
+ private List<Tuple> expectResults(String sql, final int expectedCount) throws Exception {
+ String sqlStmt = sql.replace("$ALIAS", COLLECTIONORALIAS);
+ SolrParams params = mapParams(CommonParams.QT, "/sql", "stmt", sqlStmt);
+ List<Tuple> tuples = getTuples(params, sqlUrl());
+ assertEquals(expectedCount, tuples.size());
+ return tuples;
+ }
+
+ @Test
+ public void testColIsNotNull() throws Exception {
+ new UpdateRequest()
+ .add("id", "1", "b_s", "foobar")
+ .add("id", "2", "b_s", "foobaz")
+ .add("id", "3")
+ .commit(cluster.getSolrClient(), COLLECTIONORALIAS);
+
+ expectResults("SELECT b_s FROM $ALIAS WHERE b_s IS NOT NULL", 2);
+ }
+
+ @Test
+ public void testColIsNull() throws Exception {
+ new UpdateRequest()
+ .add("id", "1", "b_s", "foobar")
+ .add("id", "2")
+ .add("id", "3", "b_s", "foobaz")
+ .add("id", "4")
+ .add("id", "5", "b_s", "bazbar")
+ .add("id", "6")
+ .commit(cluster.getSolrClient(), COLLECTIONORALIAS);
+
+ expectResults("SELECT id FROM $ALIAS WHERE b_s IS NULL", 3);
+ }
+
+ @Test
+ public void testLike() throws Exception {
+ new UpdateRequest()
+ .add("id", "1", "a_s", "hello-1", "b_s", "foo")
+ .add("id", "2", "a_s", "world-2", "b_s", "foo")
+ .add("id", "3", "a_s", "hello-3", "b_s", "foo")
+ .add("id", "4", "a_s", "world-4", "b_s", "foo")
+ .add("id", "5", "a_s", "hello-5", "b_s", "foo")
+ .add("id", "6", "a_s", "world-6", "b_s", "bar")
+ .commit(cluster.getSolrClient(), COLLECTIONORALIAS);
+
+ expectResults("SELECT a_s FROM $ALIAS WHERE a_s LIKE 'h_llo-%'", 3);
+
+ // not technically valid SQL but we support it for legacy purposes, see: SOLR-15463
+ expectResults("SELECT a_s FROM $ALIAS WHERE a_s='world-*'", 3);
+
+ // no results
+ expectResults("SELECT a_s FROM $ALIAS WHERE a_s LIKE '%MATCHNONE%'", 0);
+
+ // like but without wildcard, should still work
+ expectResults("SELECT b_s FROM $ALIAS WHERE b_s LIKE 'foo'", 5);
+
+ // NOT LIKE
+ expectResults("SELECT b_s FROM $ALIAS WHERE b_s NOT LIKE 'f%'", 1);
+
+ // leading wildcard
+ expectResults("SELECT b_s FROM $ALIAS WHERE b_s LIKE '%oo'", 5);
+
+ // user supplied parens around arg, no double-quotes ...
+ expectResults("SELECT b_s FROM $ALIAS WHERE b_s LIKE '(fo%)'", 5);
+
+ expectResults("SELECT b_s FROM $ALIAS WHERE b_s LIKE '(ba*)'", 1);
+ }
+
+ @Test
+ public void testBetween() throws Exception {
+ new UpdateRequest()
+ .add(withMultiValuedField("b_is", Arrays.asList(1, 5), "id", "1", "a_i", "1"))
+ .add(withMultiValuedField("b_is", Arrays.asList(2, 6), "id", "2", "a_i", "2"))
+ .add(withMultiValuedField("b_is", Arrays.asList(3, 7), "id", "3", "a_i", "3"))
+ .add(withMultiValuedField("b_is", Arrays.asList(4, 8), "id", "4", "a_i", "4"))
+ .add(withMultiValuedField("b_is", Arrays.asList(5, 9), "id", "5", "a_i", "5"))
+ .commit(cluster.getSolrClient(), COLLECTIONORALIAS);
+
+ expectResults("SELECT a_i FROM $ALIAS WHERE a_i BETWEEN 2 AND 4", 3);
+ expectResults("SELECT a_i FROM $ALIAS WHERE a_i NOT BETWEEN 2 AND 4", 2);
+ expectResults("SELECT id FROM $ALIAS WHERE b_is BETWEEN 2 AND 4", 3);
+ expectResults("SELECT id FROM $ALIAS WHERE b_is BETWEEN 1 AND 9", 5);
+ expectResults("SELECT id FROM $ALIAS WHERE b_is BETWEEN 8 AND 10", 2);
+ expectResults("SELECT id FROM $ALIAS WHERE b_is >= 2 AND b_is <= 4", 3);
+ expectResults("SELECT id FROM $ALIAS WHERE b_is <= 4 AND b_is >= 2", 3);
+ expectResults("SELECT id FROM $ALIAS WHERE b_is <= 2 OR b_is >= 8", 4);
+ // tricky ~ with Solr, this should return 2 docs, but Calcite short-circuits this query and returns 0
+ // Calcite sees the predicate as disjoint from a single-valued field perspective ...
+ expectResults("SELECT id FROM $ALIAS WHERE b_is >= 5 AND b_is <= 2", 0);
+ // hacky work-around the aforementioned problem ^^
+ expectResults("SELECT id FROM $ALIAS WHERE b_is = '(+[5 TO *] +[* TO 2])'", 2);
+ }
+
+ private SolrInputDocument withMultiValuedField(String mvField, List<Object> values, String... fields) {
+ SolrInputDocument doc = new SolrInputDocument(fields);
+ doc.addField(mvField, values);
+ return doc;
+ }
+
+ @Test
+ public void testMultipleFilters() throws Exception {
+ new UpdateRequest()
+ .add("id", "1", "a_s", "hello-1", "b_s", "foo", "c_s", "bar", "d_s", "x")
+ .add("id", "2", "a_s", "world-2", "b_s", "foo", "a_i", "2", "d_s", "a")
+ .add("id", "3", "a_s", "hello-3", "b_s", "foo", "c_s", "bar", "d_s", "x")
+ .add("id", "4", "a_s", "world-4", "b_s", "foo", "a_i", "3", "d_s", "b")
+ .add("id", "5", "a_s", "hello-5", "b_s", "foo", "c_s", "bar", "d_s", "x")
+ .add("id", "6", "a_s", "world-6", "b_s", "bar", "a_i", "4", "d_s", "c")
+ .add("id", "7", "a_s", "hello-7", "b_s", "foo", "c_s", "baz blah", "d_s", "x")
+ .add("id", "8", "a_s", "world-8", "b_s", "bar", "a_i", "5", "d_s", "c")
+ .commit(cluster.getSolrClient(), COLLECTIONORALIAS);
+
+ List<Tuple> tuples = expectResults("SELECT a_s FROM $ALIAS WHERE a_s LIKE 'world%' AND b_s IS NOT NULL AND c_s IS NULL AND a_i BETWEEN 2 AND 4 AND d_s IN ('a','b','c') ORDER BY id ASC LIMIT 10", 3);
+ assertEquals("world-2", tuples.get(0).getString("a_s"));
+ assertEquals("world-4", tuples.get(1).getString("a_s"));
+ assertEquals("world-6", tuples.get(2).getString("a_s"));
+
+ tuples = expectResults("SELECT a_s FROM $ALIAS WHERE a_s NOT LIKE 'hello%' AND b_s IS NOT NULL AND c_s IS NULL AND a_i NOT BETWEEN 2 AND 4 AND d_s IN ('a','b','c') ORDER BY id ASC LIMIT 10", 1);
+ assertEquals("world-8", tuples.get(0).getString("a_s"));
+ }
+
+ @Test
+ public void testCountWithFilters() throws Exception {
+ new UpdateRequest()
+ .add("id", "1", "a_s", "hello-1", "b_s", "foo", "c_s", "bar", "d_s", "x")
+ .add("id", "2", "a_s", "world-2", "b_s", "foo", "a_i", "2", "d_s", "a")
+ .add("id", "3", "a_s", "hello-3", "b_s", "foo", "c_s", "bar", "d_s", "x")
+ .add("id", "4", "a_s", "world-4", "b_s", "foo", "a_i", "3", "d_s", "b")
+ .add("id", "5", "a_s", "hello-5", "b_s", "foo", "c_s", "bar", "d_s", "x")
+ .add("id", "6", "a_s", "world-6", "b_s", "bar", "a_i", "4", "d_s", "c")
+ .add("id", "7", "a_s", "hello-7", "b_s", "foo", "c_s", "baz blah", "d_s", "x")
+ .add("id", "8", "a_s", "world-8", "b_s", "bar", "a_i", "5", "d_s", "c")
+ .commit(cluster.getSolrClient(), COLLECTIONORALIAS);
+
+ List<Tuple> tuples = expectResults("SELECT COUNT(1) as `the_count` FROM $ALIAS as `alias` WHERE (`alias`.`b_s`='foo' AND `alias`.`a_s` LIKE 'hell%' AND `alias`.`c_s` IS NOT NULL) HAVING (COUNT(1) > 0)", 1);
+ assertTrue(4L == tuples.get(0).getLong("the_count"));
+ }
}
diff --git a/solr/solr-ref-guide/src/parallel-sql-interface.adoc b/solr/solr-ref-guide/src/parallel-sql-interface.adoc
index 95c7584..34f7fe6 100644
--- a/solr/solr-ref-guide/src/parallel-sql-interface.adoc
+++ b/solr/solr-ref-guide/src/parallel-sql-interface.adoc
@@ -240,16 +240,21 @@ The parallel SQL interface supports and pushes down most common SQL operators, s
|= |Equals |`fielda = 10` |`fielda:10`
|<> |Does not equal |`fielda <> 10` |`-fielda:10`
|!= |Does not equal |`fielda != 10` |`-fielda:10`
-|> |Greater than |`fielda > 10` | `fielda:{10 TO *]`
-|>= |Greater than or equals |`fielda >= 10` | `fielda:[10 TO *]`
-|< |Less than |`fielda < 10` | `fielda:[* TO 10}`
-|\<= |Less than or equals |`fielda \<= 10` | `fielda:[* TO 10]`
+|> |Greater than |`fielda > 10` |`fielda:{10 TO *]`
+|>= |Greater than or equals |`fielda >= 10` |`fielda:[10 TO *]`
+|< |Less than |`fielda < 10` |`fielda:[* TO 10}`
+|\<= |Less than or equals |`fielda \<= 10` |`fielda:[* TO 10]`
+|IN |Specify multiple values (shorthand for multiple OR clasues) |`fielda IN (10,20,30)` |`(fielda:10 OR fielda:20 OR fielda:30)`
+|LIKE |Wildcard match on string or text fields |`fielda LIKE 'day%'` |`{!complexphrase}fielda:"day*"`
+|BETWEEN |Range match |`fielda BETWEEN 2 AND 4` |`fielda: [2 TO 4]`
+|IS NULL |Match columns with null value |`fielda IS NULL` |`(*:* -field:*)`
+|IS NOT NULL |Match columns with value |`fielda IS NOT NULL` |`field:*`
|===
-Some operators that are not supported are BETWEEN, LIKE and IN. However, there are workarounds for BETWEEN and LIKE.
-
-* BETWEEN can be supported with a range query, such as `field = [50 TO 100]`.
-* A simplistic LIKE can be used with a wildcard, such as `field = 'sam*'`.
+* IN, LIKE, BETWEEN support the NOT keyword to find rows where the condition is not true, such as `fielda NOT LIKE 'day%'`
+* String literals must be wrapped in single-quotes; double-quotes indicate database objects and not a string literal.
+* A simplistic LIKE can be used with an asterisk wildcard, such as `field = 'sam*'`; this is Solr specific and not part of the SQL standard.
+* When performing ANDed range queries over a multi-valued field, Apache Calcite short-circuits to zero results if the ANDed predicates appear to be disjoint sets. For example, `b_is <= 2 AND b_is >= 5` appears to Calcite to be disjoint sets, which they are from a single-valued field perspective. However, this may not be the case with multi-valued fields, as Solr might match documents. The work-around is to use Solr query syntax directly inside of an equals expression wrapped in parens: ` [...]
=== ORDER BY Clause