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 86d95ce SOLR-9853: Ability to project multi-valued fields in SQL
query results (#252)
86d95ce is described below
commit 86d95ce05d2273c6b5692f911fa047dd7ebf5fba
Author: Timothy Potter <[email protected]>
AuthorDate: Wed Aug 11 08:28:18 2021 -0600
SOLR-9853: Ability to project multi-valued fields in SQL query results
(#252)
---
solr/CHANGES.txt | 2 +
.../apache/solr/handler/sql/SolrEnumerator.java | 13 ---
.../org/apache/solr/handler/sql/SolrSchema.java | 112 +++++++++++++--------
.../test-files/solr/configsets/sql/conf/schema.xml | 15 +++
.../org/apache/solr/handler/TestSQLHandler.java | 108 ++++++++++++++++++++
.../solr-ref-guide/src/parallel-sql-interface.adoc | 9 +-
.../solr/client/solrj/io/stream/JDBCStream.java | 18 +++-
.../apache/solr/client/solrj/io/sql/JdbcTest.java | 86 ++++++++++++++--
8 files changed, 296 insertions(+), 67 deletions(-)
diff --git a/solr/CHANGES.txt b/solr/CHANGES.txt
index 326f91b..db129f4 100644
--- a/solr/CHANGES.txt
+++ b/solr/CHANGES.txt
@@ -392,6 +392,8 @@ Improvements
* SOLR-15576: Allow filtering on ISO-8601 formatted timestamp literals in SQL
WHERE clause (Timothy Potter)
+* SOLR-9853: Project multi-valued fields in SQL query results (Timothy Potter)
+
Optimizations
---------------------
* SOLR-15433: Replace transient core cache LRU by Caffeine cache. (Bruno
Roustant)
diff --git a/solr/core/src/java/org/apache/solr/handler/sql/SolrEnumerator.java
b/solr/core/src/java/org/apache/solr/handler/sql/SolrEnumerator.java
index 944435c..e450755 100644
--- a/solr/core/src/java/org/apache/solr/handler/sql/SolrEnumerator.java
+++ b/solr/core/src/java/org/apache/solr/handler/sql/SolrEnumerator.java
@@ -26,7 +26,6 @@ import java.io.IOException;
import java.lang.invoke.MethodHandles;
import java.util.Date;
import java.util.List;
-import java.util.ArrayList;
import java.util.Map;
/** Enumerator that reads from a Solr collection. */
@@ -36,7 +35,6 @@ class SolrEnumerator implements Enumerator<Object> {
private final TupleStream tupleStream;
private final List<Map.Entry<String, Class<?>>> fields;
private Tuple current;
- private char sep = 31;
/** Creates a SolrEnumerator.
*
@@ -93,17 +91,6 @@ class SolrEnumerator implements Enumerator<Object> {
val = tuple.getDate(field.getKey());
}
- if(val instanceof ArrayList) {
- ArrayList<?> arrayList = (ArrayList<?>) val;
- StringBuilder buf = new StringBuilder();
-
- for(Object o : arrayList) {
- buf.append(sep);
- buf.append(o.toString());
- }
- val = buf.toString();
- }
-
return val;
}
diff --git a/solr/core/src/java/org/apache/solr/handler/sql/SolrSchema.java
b/solr/core/src/java/org/apache/solr/handler/sql/SolrSchema.java
index b5c99e3..14f3102 100644
--- a/solr/core/src/java/org/apache/solr/handler/sql/SolrSchema.java
+++ b/solr/core/src/java/org/apache/solr/handler/sql/SolrSchema.java
@@ -24,6 +24,8 @@ import java.util.HashMap;
import java.util.Map;
import java.util.Properties;
import java.util.Set;
+import java.util.TreeMap;
+import java.util.concurrent.ConcurrentHashMap;
import java.util.stream.Collectors;
import java.util.stream.Stream;
@@ -36,6 +38,7 @@ import org.apache.calcite.rel.type.RelProtoDataType;
import org.apache.calcite.schema.Table;
import org.apache.calcite.schema.impl.AbstractSchema;
import org.apache.calcite.sql.type.SqlTypeFactoryImpl;
+import org.apache.calcite.sql.type.SqlTypeName;
import org.apache.solr.client.solrj.SolrServerException;
import org.apache.solr.client.solrj.impl.CloudSolrClient;
import org.apache.solr.client.solrj.io.SolrClientCache;
@@ -57,6 +60,11 @@ class SolrSchema extends AbstractSchema implements Closeable
{
final SolrClientCache solrClientCache;
private volatile boolean isClosed = false;
+ // collection schema gets requested 2x times during query execution,
+ // so there's some benefit to caching it for the duration of a statement
+ // every statement gets a new SolrSchema instance
+ private Map<String, RelDataType> schemaCache = new ConcurrentHashMap<>();
+
SolrSchema(Properties properties, SolrClientCache solrClientCache) {
super();
this.properties = properties;
@@ -133,8 +141,27 @@ class SolrSchema extends AbstractSchema implements
Closeable {
private boolean isStoredOrDocValues(final EnumSet<FieldFlag> flags) {
return flags != null && (flags.contains(FieldFlag.STORED) ||
flags.contains(FieldFlag.DOC_VALUES));
}
-
+
+ private EnumSet<FieldFlag> getFieldFlags(final LukeResponse.FieldInfo
luceneFieldInfo) {
+ EnumSet<FieldFlag> flags = luceneFieldInfo.getSchemaFlags();
+ if (flags == null) {
+ String fieldSchema = luceneFieldInfo.getSchema();
+ if (fieldSchema != null) {
+ flags = LukeResponse.FieldInfo.parseFlags(fieldSchema);
+ }
+ }
+ return flags;
+ }
+
RelProtoDataType getRelDataType(String collection) {
+ return RelDataTypeImpl.proto(getRowSchema(collection));
+ }
+
+ RelDataType getRowSchema(String collection) {
+ return schemaCache.computeIfAbsent(collection, this::buildRowSchema);
+ }
+
+ RelDataType buildRowSchema(String collection) {
// Temporary type factory, just for the duration of this method. Allowable
// because we're creating a proto-type, not a type; before being used, the
// proto-type will be copied into a real type factory.
@@ -147,12 +174,12 @@ class SolrSchema extends AbstractSchema implements
Closeable {
LukeResponse schema = getSchema(collection);
// Only want fields that are stored or have docValues enabled
Map<String, LukeResponse.FieldInfo> storedFields =
schema.getFieldInfo().entrySet().stream()
- .filter(e -> isStoredOrDocValues(e.getValue().getFlags()))
+ .filter(e -> isStoredOrDocValues(getFieldFlags(e.getValue())))
.collect(Collectors.toMap(Map.Entry::getKey, Map.Entry::getValue));
// merge the actual fields in use returned by Luke with the declared
fields in the schema that are empty
Map<String, LukeResponse.FieldInfo> combinedFields =
Stream.of(fieldsInUseMap, storedFields)
.flatMap(map -> map.entrySet().stream())
- .collect(Collectors.toMap(Map.Entry::getKey, Map.Entry::getValue,
(v1, v2) -> v1));
+ .collect(Collectors.toMap(Map.Entry::getKey, Map.Entry::getValue,
(v1, v2) -> v1, TreeMap::new));
Map<String, Class<?>> javaClassForTypeMap = new HashMap<>(); // local
cache for custom field types we've already resolved
@@ -166,51 +193,54 @@ class SolrSchema extends AbstractSchema implements
Closeable {
}
RelDataType type;
- switch (luceneFieldType) {
- case "string":
- type = typeFactory.createJavaType(String.class);
- break;
- case "tint":
- case "tlong":
- case "int":
- case "long":
- case "pint":
- case "plong":
- type = typeFactory.createJavaType(Long.class);
- break;
- case "tfloat":
- case "tdouble":
- case "float":
- case "double":
- case "pfloat":
- case "pdouble":
- type = typeFactory.createJavaType(Double.class);
- break;
- case "pdate":
- type = typeFactory.createJavaType(Date.class);
- break;
- default:
- Class<?> javaClass = javaClassForTypeMap.get(luceneFieldType);
- if (javaClass == null) {
- javaClass =
guessJavaClassForFieldType(schema.getFieldTypeInfo().get(luceneFieldType));
- javaClassForTypeMap.put(luceneFieldType, javaClass);
- }
- type = typeFactory.createJavaType(javaClass);
- }
- /*
- EnumSet<FieldFlag> flags =
luceneFieldInfo.parseFlags(luceneFieldInfo.getSchema());
- if(flags != null && flags.contains(FieldFlag.MULTI_VALUED)) {
- type = typeFactory.createArrayType(type, -1);
+ // We have to pass multi-valued fields through Calcite as SQL Type ANY
+ // Array doesn't work for aggregations! Calcite doesn't like GROUP BY on
an ARRAY field
+ // but Solr happily computes aggs on a multi-valued field, so we have a
paradigm mis-match and
+ // ANY is the best way to retain use of operators on multi-valued fields
while still being able
+ // to GROUP BY and project the multi-valued fields in results
+ EnumSet<FieldFlag> flags = getFieldFlags(luceneFieldInfo);
+ if (flags != null && flags.contains(FieldFlag.MULTI_VALUED)) {
+ type = typeFactory.createSqlType(SqlTypeName.ANY);
+ } else {
+ switch (luceneFieldType) {
+ case "string":
+ type = typeFactory.createJavaType(String.class);
+ break;
+ case "tint":
+ case "tlong":
+ case "int":
+ case "long":
+ case "pint":
+ case "plong":
+ type = typeFactory.createJavaType(Long.class);
+ break;
+ case "tfloat":
+ case "tdouble":
+ case "float":
+ case "double":
+ case "pfloat":
+ case "pdouble":
+ type = typeFactory.createJavaType(Double.class);
+ break;
+ case "pdate":
+ type = typeFactory.createJavaType(Date.class);
+ break;
+ default:
+ Class<?> javaClass = javaClassForTypeMap.get(luceneFieldType);
+ if (javaClass == null) {
+ javaClass =
guessJavaClassForFieldType(schema.getFieldTypeInfo().get(luceneFieldType));
+ javaClassForTypeMap.put(luceneFieldType, javaClass);
+ }
+ type = typeFactory.createJavaType(javaClass);
+ }
}
- */
-
fieldInfo.add(entry.getKey(), type).nullable(true);
}
fieldInfo.add("_query_", typeFactory.createJavaType(String.class));
fieldInfo.add("score", typeFactory.createJavaType(Double.class));
- return RelDataTypeImpl.proto(fieldInfo.build());
+ return fieldInfo.build();
}
private Class<?> guessJavaClassForFieldType(LukeResponse.FieldTypeInfo
typeInfo) {
diff --git a/solr/core/src/test-files/solr/configsets/sql/conf/schema.xml
b/solr/core/src/test-files/solr/configsets/sql/conf/schema.xml
index 0638a79..a945d2e 100644
--- a/solr/core/src/test-files/solr/configsets/sql/conf/schema.xml
+++ b/solr/core/src/test-files/solr/configsets/sql/conf/schema.xml
@@ -19,6 +19,19 @@
<!-- The Solr schema file, version 1.6 -->
<schema name="sql" version="1.6">
+
+ <field name="pdoublexmv" type="pdouble" multiValued="true" stored="true"
docValues="true"/>s
+ <field name="pdoublesx" type="pdoubles" stored="true"/>
+ <field name="stringsx" type="strings" stored="true" indexed="true"/>
+ <field name="stringxmv" type="string" stored="true" multiValued="true"
docValues="true"/>
+
+ <field name="booleans" type="boolean" stored="true" indexed="true"
docValues="true" multiValued="true"/>
+ <field name="longs" type="plongx" stored="true" indexed="true"
docValues="true" multiValued="true"/>
+
+ <fieldType name="pdouble" class="solr.DoublePointField" docValues="true"/>
+ <fieldType name="pdoubles" class="solr.DoublePointField" docValues="true"
multiValued="true"/>
+
+
<!-- attribute "name" is the name of this schema and is only used for
display purposes.
Applications should change this to reflect the nature of the search
collection.
version="x.y" is Solr's version number for the schema syntax and
semantics. It should
@@ -68,6 +81,7 @@
<field name="notstored" type="string" indexed="true" docValues="false"
stored="false"/>
<field name="dvonly" type="string" docValues="true" stored="false"/>
+ <field name="textmv" type="text" multiValued="true"/>
<!-- Field type demonstrating an Analyzer failure -->
<fieldType name="failtype1" class="solr.TextField">
@@ -119,6 +133,7 @@
<fieldType name="boolean" class="solr.BoolField" sortMissingLast="true"/>
<fieldType name="string" class="solr.StrField" sortMissingLast="true"/>
+ <fieldType name="strings" class="solr.StrField" sortMissingLast="true"
multiValued="true" docValues="true" indexed="true"/>
<!-- format for date is 1995-12-31T23:59:59.999Z and only the fractional
seconds part (.999) is optional.
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 adb7332..db5f2a2 100644
--- a/solr/core/src/test/org/apache/solr/handler/TestSQLHandler.java
+++ b/solr/core/src/test/org/apache/solr/handler/TestSQLHandler.java
@@ -17,10 +17,13 @@
package org.apache.solr.handler;
import java.io.IOException;
+import java.time.Instant;
import java.util.Arrays;
+import java.util.Date;
import java.util.LinkedList;
import java.util.List;
import java.util.Locale;
+import java.util.stream.Collectors;
import org.apache.lucene.util.LuceneTestCase;
import org.apache.lucene.util.LuceneTestCase.Slow;
@@ -2290,4 +2293,109 @@ public class TestSQLHandler extends SolrCloudTestCase {
expectThrows(IOException.class, () -> expectResults("SELECT id, stringx,
notafield_i FROM $ALIAS", 5));
expectThrows(IOException.class, () -> expectResults("SELECT id, stringx,
notstored FROM $ALIAS", 5));
}
+
+ @Test
+ public void testMultiValuedFieldHandling() throws Exception {
+ List<String> textmv = Arrays.asList("just some text here", "across
multiple values", "the quick brown fox jumped over the lazy dog");
+ List<String> listOfTimestamps = Arrays.asList("2021-08-06T15:37:52Z",
"2021-08-06T15:37:53Z", "2021-08-06T15:37:54Z");
+ List<Date> dates = listOfTimestamps.stream().map(ts -> new
Date(Instant.parse(ts).toEpochMilli())).collect(Collectors.toList());
+ List<String> stringxmv = Arrays.asList("a", "b", "c");
+ List<String> stringsx = Arrays.asList("d", "e", "f");
+ List<Double> pdoublesx = Arrays.asList(1d, 2d, 3d);
+ List<Double> pdoublexmv = Arrays.asList(4d, 5d, 6d);
+ List<Boolean> booleans = Arrays.asList(false, true);
+ List<Long> evenLongs = Arrays.asList(2L, 4L, 6L);
+ List<Long> oddLongs = Arrays.asList(1L, 3L, 5L);
+
+ UpdateRequest update = new UpdateRequest();
+ final int maxDocs = 10;
+ for (int i = 0; i < maxDocs; i++) {
+ SolrInputDocument doc = new SolrInputDocument("id", String.valueOf(i));
+ if (i % 2 == 0) {
+ doc.setField("stringsx", stringsx);
+ doc.setField("pdoublexmv", pdoublexmv);
+ doc.setField("longs", evenLongs);
+ } else {
+ // stringsx & pdoublexmv null
+ doc.setField("longs", oddLongs);
+ }
+ doc.setField("stringxmv", stringxmv);
+ doc.setField("pdoublesx", pdoublesx);
+ doc.setField("pdatexs", dates);
+ doc.setField("textmv", textmv);
+ doc.setField("booleans", booleans);
+ update.add(doc);
+ }
+ update.add("id", String.valueOf(maxDocs)); // all multi-valued fields are
null
+ update.commit(cluster.getSolrClient(), COLLECTIONORALIAS);
+
+ expectResults("SELECT stringxmv, stringsx, booleans FROM $ALIAS WHERE
stringxmv > 'a'", 10);
+ expectResults("SELECT stringxmv, stringsx, booleans FROM $ALIAS WHERE
stringxmv NOT IN ('a')", 1);
+ expectResults("SELECT stringxmv, stringsx, booleans FROM $ALIAS WHERE
stringxmv > 'a' LIMIT 10", 10);
+ expectResults("SELECT stringxmv, stringsx, booleans FROM $ALIAS WHERE
stringxmv NOT IN ('a') LIMIT 10", 1);
+
+ // can't sort by a mv field
+ expectThrows(IOException.class,
+ () -> expectResults("SELECT stringxmv FROM $ALIAS WHERE stringxmv IS
NOT NULL ORDER BY stringxmv ASC", 0));
+
+ // even id's have these fields, odd's are null ...
+ expectListInResults("0", "stringsx", stringsx, -1, 5);
+ expectListInResults("0", "pdoublexmv", pdoublexmv, -1, 5);
+ expectListInResults("1", "stringsx", null, -1, 0);
+ expectListInResults("1", "pdoublexmv", null, -1, 0);
+ expectListInResults("2", "stringsx", stringsx, 10, 5);
+ expectListInResults("2", "pdoublexmv", pdoublexmv, 10, 5);
+
+ expectListInResults("1", "stringxmv", stringxmv, -1, 10);
+ expectListInResults("1", "pdoublesx", pdoublesx, -1, 10);
+ expectListInResults("1", "pdatexs", listOfTimestamps, -1, 10);
+ expectListInResults("1", "booleans", booleans, -1, 10);
+ expectListInResults("1", "longs", oddLongs, -1, 5);
+
+ expectListInResults("2", "stringxmv", stringxmv, 10, 10);
+ expectListInResults("2", "pdoublesx", pdoublesx, 10, 10);
+ expectListInResults("2", "pdatexs", listOfTimestamps, 10, 10);
+ expectListInResults("2", "textmv", textmv, 10, 10);
+ expectListInResults("2", "booleans", booleans, 10, 10);
+ expectListInResults("2", "longs", evenLongs, 10, 5);
+
+ expectAggCount("stringxmv", 3);
+ expectAggCount("stringsx", 3);
+ expectAggCount("pdoublesx", 3);
+ expectAggCount("pdoublexmv", 3);
+ expectAggCount("pdatexs", 3);
+ expectAggCount("booleans", 2);
+ expectAggCount("longs", 6);
+ }
+
+ private void expectListInResults(String id, String mvField, List<?>
expected, int limit, int expCount) throws Exception {
+ String projection = limit > 0 ? "*" : "id," + mvField;
+ String sql = "SELECT " + projection + " FROM $ALIAS WHERE id='" + id + "'";
+ if (limit > 0) sql += " LIMIT " + limit;
+ List<Tuple> results = expectResults(sql, 1);
+ if (expected != null) {
+ assertEquals(expected, results.get(0).get(mvField));
+ } else {
+ assertNull(results.get(0).get(mvField));
+ }
+
+ if (expected != null) {
+ String crit = "'" + expected.get(0) + "'";
+ sql = "SELECT " + projection + " FROM $ALIAS WHERE " + mvField + "=" +
crit;
+ if (limit > 0) sql += " LIMIT " + limit;
+ expectResults(sql, expCount);
+
+ // test "IN" operator but skip for text analyzed fields
+ if (!"textmv".equals(mvField)) {
+ String inClause = expected.stream().map(o -> "'" + o +
"'").collect(Collectors.joining(","));
+ sql = "SELECT " + projection + " FROM $ALIAS WHERE " + mvField + " IN
(" + inClause + ")";
+ if (limit > 0) sql += " LIMIT " + limit;
+ expectResults(sql, expCount);
+ }
+ }
+ }
+
+ private void expectAggCount(String mvField, int expCount) throws Exception {
+ expectResults("SELECT COUNT(*), " + mvField + " FROM $ALIAS GROUP BY " +
mvField, expCount);
+ }
}
diff --git a/solr/solr-ref-guide/src/parallel-sql-interface.adoc
b/solr/solr-ref-guide/src/parallel-sql-interface.adoc
index 68caf06..c888ca9 100644
--- a/solr/solr-ref-guide/src/parallel-sql-interface.adoc
+++ b/solr/solr-ref-guide/src/parallel-sql-interface.adoc
@@ -124,6 +124,13 @@ In some cases, fields used in SQL queries must be
configured as DocValue fields.
If queries are unlimited, all fields must be DocValue fields.
If queries are limited (with the `limit` clause) then fields do not have to be
have DocValues enabled.
+.Multi-valued Fields
+[IMPORTANT]
+====
+Multi-valued fields in the project list will be returned as a `List` of
values; with JDBC, use `getObject(col)` to retrieve the multi-valued field and
then cast to a `List`.
+In general, you can project, filter, and group by, but you cannot sort by
multi-valued fields.
+====
+
=== Sending Queries
The SQL Interface provides a basic JDBC driver and an HTTP interface to
perform queries.
@@ -299,7 +306,6 @@ The parallel SQL interface supports and pushes down most
common SQL operators, s
|Operator |Description |Example |Solr Query
|= |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}`
@@ -311,6 +317,7 @@ The parallel SQL interface supports and pushes down most
common SQL operators, s
|IS NOT NULL |Match columns with value |`fielda IS NOT NULL` |`field:*`
|===
+* Use `<>` instead of `!=` for not equals
* 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.
diff --git
a/solr/solrj/src/java/org/apache/solr/client/solrj/io/stream/JDBCStream.java
b/solr/solrj/src/java/org/apache/solr/client/solrj/io/stream/JDBCStream.java
index 8045e34..3044a86 100644
--- a/solr/solrj/src/java/org/apache/solr/client/solrj/io/stream/JDBCStream.java
+++ b/solr/solrj/src/java/org/apache/solr/client/solrj/io/stream/JDBCStream.java
@@ -387,7 +387,7 @@ public class JDBCStream extends TupleStream implements
Expressible {
return columnName;
}
};
- }
+ }
// Here we are switching to check against the SQL type because date/times
are
// notorious for not being consistent. We don't know if the driver is
mapping
// to a java.time.* type or some old-school type.
@@ -427,7 +427,21 @@ public class JDBCStream extends TupleStream implements
Expressible {
return columnName;
}
};
- }
+ } else if (Object.class.getName().equals(className)) {
+ // Calcite SQL type ANY comes across as generic Object (for multi-valued
fields)
+ valueSelector = new ResultSetValueSelector() {
+ @Override
+ public Object selectValue(ResultSet resultSet) throws SQLException {
+ Object obj = resultSet.getObject(columnNumber);
+ return resultSet.wasNull() ? null : obj;
+ }
+
+ @Override
+ public String getColumnName() {
+ return columnName;
+ }
+ };
+ }
// Now we're going to start seeing if things are assignable from the
returned type
// to a more general type - this allows us to cover cases where something
we weren't
// explicitly expecting, but can handle, is being returned.
diff --git
a/solr/solrj/src/test/org/apache/solr/client/solrj/io/sql/JdbcTest.java
b/solr/solrj/src/test/org/apache/solr/client/solrj/io/sql/JdbcTest.java
index 066b47e..4451778 100644
--- a/solr/solrj/src/test/org/apache/solr/client/solrj/io/sql/JdbcTest.java
+++ b/solr/solrj/src/test/org/apache/solr/client/solrj/io/sql/JdbcTest.java
@@ -40,6 +40,7 @@ import
org.apache.solr.client.solrj.request.CollectionAdminRequest;
import org.apache.solr.client.solrj.request.UpdateRequest;
import org.apache.solr.cloud.AbstractDistribZkTestBase;
import org.apache.solr.cloud.SolrCloudTestCase;
+import org.apache.solr.common.SolrInputDocument;
import org.apache.solr.common.cloud.Aliases;
import org.apache.solr.common.cloud.ZkStateReader;
import org.junit.BeforeClass;
@@ -84,7 +85,7 @@ public class JdbcTest extends SolrCloudTestCase {
CollectionAdminRequest.createAlias(COLLECTIONORALIAS,
collection).process(cluster.getSolrClient());
}
- new UpdateRequest()
+ UpdateRequest update = new UpdateRequest()
.add(id, "0", "a_s", "hello0", "a_i", "0", "a_f", "1", "testnull_i",
null)
.add(id, "2", "a_s", "hello0", "a_i", "2", "a_f", "2", "testnull_i",
"2")
.add(id, "3", "a_s", "hello3", "a_i", "3", "a_f", "3", "testnull_i",
null)
@@ -94,8 +95,21 @@ public class JdbcTest extends SolrCloudTestCase {
.add(id, "6", "a_s", "hello4", "a_i", "11", "a_f", "7", "testnull_i",
null)
.add(id, "7", "a_s", "hello3", "a_i", "12", "a_f", "8", "testnull_i",
"8")
.add(id, "8", "a_s", "hello3", "a_i", "13", "a_f", "9", "testnull_i",
null)
- .add(id, "9", "a_s", "hello0", "a_i", "14", "a_f", "10", "testnull_i",
"10")
- .commit(cluster.getSolrClient(), collection);
+ .add(id, "9", "a_s", "hello0", "a_i", "14", "a_f", "10", "testnull_i",
"10");
+
+ SolrInputDocument withMVs = new SolrInputDocument();
+ withMVs.setField(id, "10");
+ withMVs.setField("s_multi", Arrays.asList("abc", "lmn", "xyz"));
+ withMVs.setField("d_multi", Arrays.asList(1d, 2d, 3d));
+ update.add(withMVs);
+
+ withMVs = new SolrInputDocument();
+ withMVs.setField(id, "11");
+ withMVs.setField("s_multi", Arrays.asList("a", "b", "c"));
+ withMVs.setField("d_multi", Arrays.asList(3d, 4d, 5d));
+ update.add(withMVs);
+
+ update.commit(cluster.getSolrClient(), collection);
zkHost = cluster.getZkServer().getZkAddress();
}
@@ -107,7 +121,7 @@ public class JdbcTest extends SolrCloudTestCase {
try (Connection con = DriverManager.getConnection("jdbc:solr://" + zkHost
+ "?collection=" + COLLECTIONORALIAS, props)) {
try (Statement stmt = con.createStatement()) {
- try (ResultSet rs = stmt.executeQuery("select id, a_i, a_s, a_f from "
+ COLLECTIONORALIAS + " order by a_i desc limit 2")) {
+ try (ResultSet rs = stmt.executeQuery("select id, a_i, a_s, a_f from "
+ COLLECTIONORALIAS + " WHERE a_i IS NOT NULL order by a_i desc limit 2")) {
assertTrue(rs.next());
assertEquals(14, rs.getLong("a_i"));
@@ -130,7 +144,7 @@ public class JdbcTest extends SolrCloudTestCase {
}
//Test statement reuse
- try (ResultSet rs = stmt.executeQuery("select id, a_i, a_s, a_f from "
+ COLLECTIONORALIAS + " order by a_i asc limit 2")) {
+ try (ResultSet rs = stmt.executeQuery("select id, a_i, a_s, a_f from "
+ COLLECTIONORALIAS + " WHERE a_i IS NOT NULL order by a_i asc limit 2")) {
assertTrue(rs.next());
assertEquals(0, rs.getLong("a_i"));
@@ -155,7 +169,7 @@ public class JdbcTest extends SolrCloudTestCase {
//Test connection reuse
try (Statement stmt = con.createStatement()) {
- try (ResultSet rs = stmt.executeQuery("select id, a_i, a_s, a_f from "
+ COLLECTIONORALIAS + " order by a_i desc limit 2")) {
+ try (ResultSet rs = stmt.executeQuery("select id, a_i, a_s, a_f from "
+ COLLECTIONORALIAS + " WHERE a_i IS NOT NULL order by a_i desc limit 2")) {
assertTrue(rs.next());
assertEquals(14, rs.getLong("a_i"));
@@ -171,7 +185,7 @@ public class JdbcTest extends SolrCloudTestCase {
//Test statement reuse
stmt.setMaxRows(2);
- try (ResultSet rs = stmt.executeQuery("select id, a_i, a_s, a_f from "
+ COLLECTIONORALIAS + " order by a_i asc")) {
+ try (ResultSet rs = stmt.executeQuery("select id, a_i, a_s, a_f from "
+ COLLECTIONORALIAS + " WHERE a_i IS NOT NULL order by a_i asc")) {
assertTrue(rs.next());
assertEquals(0, rs.getLong("a_i"));
@@ -186,7 +200,7 @@ public class JdbcTest extends SolrCloudTestCase {
}
//Test simple loop. Since limit is set it will override the statement
maxRows.
- try (ResultSet rs = stmt.executeQuery("select id, a_i, a_s, a_f from "
+ COLLECTIONORALIAS + " order by a_i asc LIMIT 100")) {
+ try (ResultSet rs = stmt.executeQuery("select id, a_i, a_s, a_f from "
+ COLLECTIONORALIAS + " WHERE a_i IS NOT NULL order by a_i asc LIMIT
100")) {
int count = 0;
while (rs.next()) {
++count;
@@ -246,7 +260,7 @@ public class JdbcTest extends SolrCloudTestCase {
props.put("numWorkers", "2");
try (Connection con = DriverManager.getConnection("jdbc:solr://" + zkHost
+ "?collection=" + COLLECTIONORALIAS, props)) {
try (Statement stmt = con.createStatement()) {
- try (ResultSet rs = stmt.executeQuery("select a_s, sum(a_f) from " +
COLLECTIONORALIAS + " group by a_s " +
+ try (ResultSet rs = stmt.executeQuery("select a_s, sum(a_f) from " +
COLLECTIONORALIAS + " WHERE a_s IS NOT NULL group by a_s " +
"order by sum(a_f) desc")) {
assertTrue(rs.next());
@@ -290,7 +304,7 @@ public class JdbcTest extends SolrCloudTestCase {
assert (p.getProperty("numWorkers").equals("2"));
try (Statement stmt = con.createStatement()) {
- try (ResultSet rs = stmt.executeQuery("select a_s, sum(a_f) from " +
COLLECTIONORALIAS + " group by a_s " +
+ try (ResultSet rs = stmt.executeQuery("select a_s, sum(a_f) from " +
COLLECTIONORALIAS + " WHERE a_s IS NOT NULL group by a_s " +
"order by sum(a_f) desc")) {
assertTrue(rs.next());
@@ -909,4 +923,56 @@ public class JdbcTest extends SolrCloudTestCase {
assertFalse(rs.next());
}
+
+ @Test
+ public void doTestMultiValued() throws Exception {
+
+ Properties props = new Properties();
+
+ try (Connection con = DriverManager.getConnection("jdbc:solr://" + zkHost
+ "?collection=" + COLLECTIONORALIAS, props)) {
+ try (Statement stmt = con.createStatement()) {
+ // Multi-valued field projection
+ try (ResultSet rs = stmt.executeQuery("select id, s_multi, d_multi
from " + COLLECTIONORALIAS + " WHERE s_multi IS NOT NULL order by id desc limit
2")) {
+ assertTrue(rs.next());
+
+ assertEquals(Arrays.asList("a", "b", "c"), rs.getObject("s_multi"));
+ assertEquals(Arrays.asList(3d, 4d, 5d), rs.getObject("d_multi"));
+
+ assertTrue(rs.next());
+
+ assertEquals(Arrays.asList("abc", "lmn", "xyz"),
rs.getObject("s_multi"));
+ assertEquals(Arrays.asList(1d, 2d, 3d), rs.getObject("d_multi"));
+
+ assertFalse(rs.next());
+ }
+
+ // Filtering with multi-valued fields
+ try (ResultSet rs = stmt.executeQuery("select id, s_multi, d_multi
from " + COLLECTIONORALIAS + " WHERE s_multi IN ('a', 'abc') AND d_multi >= 1
order by id desc limit 2")) {
+ assertTrue(rs.next());
+
+ assertEquals(Arrays.asList("a", "b", "c"), rs.getObject("s_multi"));
+ assertEquals(Arrays.asList(3d, 4d, 5d), rs.getObject("d_multi"));
+
+ assertTrue(rs.next());
+
+ assertEquals(Arrays.asList("abc", "lmn", "xyz"),
rs.getObject("s_multi"));
+ assertEquals(Arrays.asList(1d, 2d, 3d), rs.getObject("d_multi"));
+
+ assertFalse(rs.next());
+ }
+
+ // group by multi-valued
+ try (ResultSet rs = stmt.executeQuery("select count(*) as the_count,
d_multi from " + COLLECTIONORALIAS + " WHERE d_multi IS NOT NULL GROUP BY
d_multi ORDER BY count(*) DESC")) {
+ assertTrue(rs.next());
+ assertEquals(2, rs.getLong("the_count"));
+ assertTrue(3d == rs.getDouble("d_multi"));
+ assertTrue(rs.next()); // 4 other values, all with count 1
+ assertTrue(rs.next());
+ assertTrue(rs.next());
+ assertTrue(rs.next());
+ assertFalse(rs.next());
+ }
+ }
+ }
+ }
}
