[lucene-solr] branch branch_8x updated: SOLR-15566: Clarify ref guide documentation about SQL queries with SELECT * requiring a LIMIT clause (#2537)

[th...@apache.org]

This is an automated email from the ASF dual-hosted git repository.

thelabdude pushed a commit to branch branch_8x
in repository https://gitbox.apache.org/repos/asf/lucene-solr.git


The following commit(s) were added to refs/heads/branch_8x by this push:
     new 32250a4  SOLR-15566: Clarify ref guide documentation about SQL queries with SELECT * requiring a LIMIT clause (#2537)
32250a4 is described below

commit 32250a4a26278aca2cc37e5fc96d33b14f93a984
Author: Timothy Potter <th...@gmail.com>
AuthorDate: Thu Jul 29 10:55:45 2021 -0600

    SOLR-15566: Clarify ref guide documentation about SQL queries with SELECT * requiring a LIMIT clause (#2537)
---
 solr/CHANGES.txt                                           |  2 ++
 .../src/test/org/apache/solr/handler/TestSQLHandler.java   | 14 ++++++++++++++
 solr/solr-ref-guide/src/parallel-sql-interface.adoc        | 10 +++++++---
 3 files changed, 23 insertions(+), 3 deletions(-)

diff --git a/solr/CHANGES.txt b/solr/CHANGES.txt
index 80787a3..6fbcbf5 100644
--- a/solr/CHANGES.txt
+++ b/solr/CHANGES.txt
@@ -58,6 +58,8 @@ Other Changes
 
 * SOLR-15502: Remove MetricsCollectorHandler deprecated warning from the logs. (Bernd Wahlen, ab)
 
+* SOLR-15566: Clarify ref guide documentation about SQL queries with `SELECT *` requiring a `LIMIT` clause (Timothy Potter)
+
 ==================  8.9.0 ==================
 
 Consult the LUCENE_CHANGES.txt file for additional, low level, changes in this release.
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 c8689dc..06c9edd 100644
--- a/solr/core/src/test/org/apache/solr/handler/TestSQLHandler.java
+++ b/solr/core/src/test/org/apache/solr/handler/TestSQLHandler.java
@@ -2267,4 +2267,18 @@ public class TestSQLHandler extends SolrCloudTestCase {
     String country = id % 2 == 0 ? "US" : "CA";
     return updateRequest.add("id", String.valueOf(id), "str_s", String.format(Locale.ROOT, padFmt, id % cardinality), "country_s", country);
   }
+
+  @Test
+  public void testSelectStarWithLimit() 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")
+        .commit(cluster.getSolrClient(), COLLECTIONORALIAS);
+
+    expectResults("SELECT * FROM $ALIAS LIMIT 100", 4);
+    // select * w/o limit is not supported by Solr SQL
+    expectThrows(IOException.class, () -> expectResults("SELECT * FROM $ALIAS", -1));
+  }
 }
diff --git a/solr/solr-ref-guide/src/parallel-sql-interface.adoc b/solr/solr-ref-guide/src/parallel-sql-interface.adoc
index 5f8ff0b..f249142 100644
--- a/solr/solr-ref-guide/src/parallel-sql-interface.adoc
+++ b/solr/solr-ref-guide/src/parallel-sql-interface.adoc
@@ -31,7 +31,7 @@ In a standard `SELECT` statement such as `SELECT <expressions> FROM <table>`, th
 
 Column names in the SQL query map directly to fields in the Solr index for the collection being queried. These identifiers are case sensitive. Aliases are supported, and can be referenced in the `ORDER BY` clause.
 
-The `*` syntax to indicate all fields is not supported in either limited or unlimited queries. The `score` field can be used only with queries that contain a `LIMIT` clause.
+The `*` syntax to indicate all fields is only supported for queries with a `LIMIT` and includes the `score` field. The `score` field can be used only with queries that contain a `LIMIT` clause.
 
 For example, we could index Solr's sample documents and then construct an SQL query like this:
 
@@ -163,10 +163,14 @@ Solr supports a broad range of SQL syntax.
 The SQL parser being used by Solr to translate the SQL statements is case insensitive. However, for ease of reading, all examples on this page use capitalized keywords.
 ====
 
-.SELECT * is not supported
+.SELECT * is only supported when using LIMIT
 [IMPORTANT]
 ====
-The SQL parser being used by Solr does not support the SELECT * syntax, you must specify each field you wish to return.
+In general, you should project the fields you need to return for each query explicitly and avoid using `select *`, which is generally considered a bad practice and
+can lead to unexpected results when the underlying Solr schema changes.
+However, Solr does support `select *` for queries that also include a `LIMIT` clause.
+All stored fields and the `score` will be included in the results.
+Without a `LIMIT` clause, only fields with docValues enabled can be returned.
 ====
 
 === Escaping Reserved Words