This is an automated email from the ASF dual-hosted git repository.
airborne pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/doris.git
The following commit(s) were added to refs/heads/master by this push:
new 4128eb41d2d [fix](search) Make AND/OR/NOT operators case-sensitive in
search DSL (#59747)
4128eb41d2d is described below
commit 4128eb41d2d58e3e14bf98db9453d0c2cbe0d799
Author: Jack <[email protected]>
AuthorDate: Sat Feb 14 16:12:17 2026 +0800
[fix](search) Make AND/OR/NOT operators case-sensitive in search DSL
(#59747)
### What problem does this PR solve?
Issue Number: close #xxx
Related PR: #59394
Problem Summary:
The search DSL should only recognize uppercase `AND`, `OR`, `NOT` as
boolean operators in search lucene boolean mode. Previously, lowercase
`and`, `or`, `not` were also treated as operators, which does not
conform to the specification.
This PR makes the boolean operators case-sensitive:
- Only uppercase `AND`, `OR`, `NOT` are recognized as operators
- Lowercase `and`, `or`, `not` are now treated as regular search terms
- Using lowercase operators in DSL will result in a parse error
### Release note
Make search DSL boolean operators (AND/OR/NOT) case-sensitive in lucene
boolean mode.
---
.../org/apache/doris/nereids/search/SearchLexer.g4 | 6 +-
.../functions/scalar/SearchDslParserTest.java | 43 ++--
.../data/search/test_search_dsl_operators.out | 45 ++++
regression-test/data/search/test_search_escape.out | 9 -
.../search/test_search_boundary_cases.groovy | 40 ++--
.../suites/search/test_search_dsl_operators.groovy | 231 ++++++++++++++++++
.../suites/search/test_search_escape.groovy | 40 ++--
.../search/test_search_function.groovy.backup | 261 +++++++++++++++++++++
.../search/test_search_null_regression.groovy | 24 +-
.../search/test_search_null_semantics.groovy | 16 +-
.../search/test_search_vs_match_consistency.groovy | 30 +--
11 files changed, 638 insertions(+), 107 deletions(-)
diff --git
a/fe/fe-core/src/main/antlr4/org/apache/doris/nereids/search/SearchLexer.g4
b/fe/fe-core/src/main/antlr4/org/apache/doris/nereids/search/SearchLexer.g4
index 4dab0af2ed2..7767e66ac7d 100644
--- a/fe/fe-core/src/main/antlr4/org/apache/doris/nereids/search/SearchLexer.g4
+++ b/fe/fe-core/src/main/antlr4/org/apache/doris/nereids/search/SearchLexer.g4
@@ -41,9 +41,9 @@ fragment QUOTED_CHAR
// ============== Default lexer rules ==============
-AND : 'AND' | 'and' ;
-OR : 'OR' | 'or' ;
-NOT : 'NOT' | 'not' | '!' ;
+AND : 'AND' ;
+OR : 'OR' ;
+NOT : 'NOT' | '!' ;
LPAREN : '(' ;
RPAREN : ')' ;
diff --git
a/fe/fe-core/src/test/java/org/apache/doris/nereids/trees/expressions/functions/scalar/SearchDslParserTest.java
b/fe/fe-core/src/test/java/org/apache/doris/nereids/trees/expressions/functions/scalar/SearchDslParserTest.java
index e7daf12f100..d293433eb1a 100644
---
a/fe/fe-core/src/test/java/org/apache/doris/nereids/trees/expressions/functions/scalar/SearchDslParserTest.java
+++
b/fe/fe-core/src/test/java/org/apache/doris/nereids/trees/expressions/functions/scalar/SearchDslParserTest.java
@@ -872,16 +872,13 @@ public class SearchDslParserTest {
@Test
public void testLowercaseAndOperator() {
- // Test: Currently lowercase 'and' is also treated as operator
- // According to PDF requirement, only uppercase should be operators
- // This test documents current behavior - may need to change
+ // Lowercase 'and' is NOT an operator in ANTLR grammar
(case-sensitive).
+ // With bareQuery rule, it's parsed as a bare term without field.
+ // Without default_field, bare term throws exception.
String dsl = "field:a and field:b";
- QsPlan plan = SearchDslParser.parseDsl(dsl);
-
- Assertions.assertNotNull(plan);
- // Current behavior: lowercase 'and' IS an operator
- Assertions.assertEquals(QsClauseType.AND, plan.getRoot().getType());
- // TODO: If PDF requires only uppercase, this should fail and return
OR or different structure
+ Assertions.assertThrows(RuntimeException.class, () -> {
+ SearchDslParser.parseDsl(dsl);
+ });
}
@Test
@@ -897,15 +894,13 @@ public class SearchDslParserTest {
@Test
public void testLowercaseOrOperator() {
- // Test: Currently lowercase 'or' is also treated as operator
- // According to PDF requirement, only uppercase should be operators
+ // Lowercase 'or' is NOT an operator in ANTLR grammar (case-sensitive).
+ // With bareQuery rule, it's parsed as a bare term without field.
+ // Without default_field, bare term throws exception.
String dsl = "field:a or field:b";
- QsPlan plan = SearchDslParser.parseDsl(dsl);
-
- Assertions.assertNotNull(plan);
- // Current behavior: lowercase 'or' IS an operator
- Assertions.assertEquals(QsClauseType.OR, plan.getRoot().getType());
- // TODO: If PDF requires only uppercase, this should fail
+ Assertions.assertThrows(RuntimeException.class, () -> {
+ SearchDslParser.parseDsl(dsl);
+ });
}
@Test
@@ -920,15 +915,13 @@ public class SearchDslParserTest {
@Test
public void testLowercaseNotOperator() {
- // Test: Currently lowercase 'not' is also treated as operator
- // According to PDF requirement, only uppercase should be operators
+ // Lowercase 'not' is NOT an operator in ANTLR grammar
(case-sensitive).
+ // With bareQuery rule, it's parsed as a bare term without field.
+ // Without default_field, bare term throws exception.
String dsl = "not field:spam";
- QsPlan plan = SearchDslParser.parseDsl(dsl);
-
- Assertions.assertNotNull(plan);
- // Current behavior: lowercase 'not' IS an operator
- Assertions.assertEquals(QsClauseType.NOT, plan.getRoot().getType());
- // TODO: If PDF requires only uppercase, this should fail
+ Assertions.assertThrows(RuntimeException.class, () -> {
+ SearchDslParser.parseDsl(dsl);
+ });
}
@Test
diff --git a/regression-test/data/search/test_search_dsl_operators.out
b/regression-test/data/search/test_search_dsl_operators.out
new file mode 100644
index 00000000000..3d4e890e091
--- /dev/null
+++ b/regression-test/data/search/test_search_dsl_operators.out
@@ -0,0 +1,45 @@
+-- This file is automatically generated. You should know what you did if you
want to edit this
+-- !dsl_or_chain --
+1 aterm bterm
+2 bterm cterm
+3 cterm dterm
+4 dterm eterm aterm
+
+-- !dsl_and_chain --
+4 dterm eterm aterm
+
+-- !dsl_and_or_mixed --
+1 aterm bterm
+4 dterm eterm aterm
+
+-- !dsl_and_not_or --
+4 dterm eterm aterm
+
+-- !dsl_implicit_and --
+3 cterm dterm
+
+-- !dsl_phrase_wrong_order --
+
+-- !dsl_phrase_correct_order --
+4 dterm eterm aterm
+
+-- !dsl_escaped_space_and --
+4 dterm eterm aterm
+
+-- !dsl_phrase_and_term --
+4 dterm eterm aterm
+
+-- !dsl_phrase_wrong_and_term --
+
+-- !dsl_phrase_or_term_1 --
+2 bterm cterm
+3 cterm dterm
+
+-- !dsl_phrase_or_term_2 --
+2 bterm cterm
+3 cterm dterm
+4 dterm eterm aterm
+
+-- !dsl_and_or_min_should_1 --
+1 aterm bterm
+
diff --git a/regression-test/data/search/test_search_escape.out
b/regression-test/data/search/test_search_escape.out
index 09bd9f80b2b..fbe1e731f61 100644
--- a/regression-test/data/search/test_search_escape.out
+++ b/regression-test/data/search/test_search_escape.out
@@ -26,15 +26,6 @@
-- !uppercase_not --
8 second fruit
--- !lowercase_and --
-7 first fruit
-
--- !lowercase_or --
-1 first content
-2 second content
-7 first fruit
-8 second fruit
-
-- !exclamation_not --
8 second fruit
diff --git a/regression-test/suites/search/test_search_boundary_cases.groovy
b/regression-test/suites/search/test_search_boundary_cases.groovy
index c5ed5ffd55f..e29b5decb30 100644
--- a/regression-test/suites/search/test_search_boundary_cases.groovy
+++ b/regression-test/suites/search/test_search_boundary_cases.groovy
@@ -86,31 +86,31 @@ suite("test_search_boundary_cases") {
// Boundary Test 1: All NULL fields
qt_boundary_1_all_null_or """
SELECT /*+SET_VAR(enable_common_expr_pushdown=true) */ count(*) FROM
${tableName}
- WHERE search('field1:anything or field2:anything or field3:anything or
field4:anything or field5:anything')
+ WHERE search('field1:anything OR field2:anything OR field3:anything OR
field4:anything OR field5:anything')
"""
qt_boundary_1_all_null_and """
SELECT /*+SET_VAR(enable_common_expr_pushdown=true) */ count(*) FROM
${tableName}
- WHERE search('field1:anything and field2:anything and field3:anything
and field4:anything and field5:anything')
+ WHERE search('field1:anything AND field2:anything AND field3:anything
AND field4:anything AND field5:anything')
"""
// Boundary Test 2: Single field NULL vs multiple fields NULL in OR
qt_boundary_2_single_null_or """
SELECT /*+SET_VAR(enable_common_expr_pushdown=true) */ id FROM
${tableName}
- WHERE search('field1:nonexistent or field2:test')
+ WHERE search('field1:nonexistent OR field2:test')
ORDER BY id
"""
qt_boundary_2_multiple_null_or """
SELECT /*+SET_VAR(enable_common_expr_pushdown=true) */ id FROM
${tableName}
- WHERE search('field1:nonexistent or field2:test or field3:nonexistent')
+ WHERE search('field1:nonexistent OR field2:test OR field3:nonexistent')
ORDER BY id
"""
// Boundary Test 3: NOT with various NULL combinations
qt_boundary_3_not_null_field """
SELECT /*+SET_VAR(enable_common_expr_pushdown=true) */ count(*) FROM
${tableName}
- WHERE search('not field1:test')
+ WHERE search('NOT field1:test')
"""
qt_boundary_3_external_not_null """
@@ -138,59 +138,59 @@ suite("test_search_boundary_cases") {
// Boundary Test 5: Complex nested boolean with NULLs
qt_boundary_5_complex_nested """
SELECT /*+SET_VAR(enable_common_expr_pushdown=true) */ count(*) FROM
${tableName}
- WHERE search('((field1:test or field2:test) and (field3:test or
field4:test)) or field5:test')
+ WHERE search('((field1:test OR field2:test) AND (field3:test OR
field4:test)) OR field5:test')
"""
qt_boundary_5_detailed_result """
SELECT /*+SET_VAR(enable_common_expr_pushdown=true) */ id, field1,
field2, field3, field4, field5 FROM ${tableName}
- WHERE search('((field1:test or field2:test) and (field3:test or
field4:test)) or field5:test')
+ WHERE search('((field1:test OR field2:test) AND (field3:test OR
field4:test)) OR field5:test')
ORDER BY id
"""
// Boundary Test 6: Large OR query with many NULL fields
qt_boundary_6_large_or """
SELECT /*+SET_VAR(enable_common_expr_pushdown=true) */ count(*) FROM
${tableName}
- WHERE search('field1:"target" or field1:"keyword" or field1:"apple" or
field1:"unique1" or
- field2:"target" or field2:"keyword" or field2:"apple" or
field2:"unique2" or
- field3:"target" or field3:"keyword" or field3:"banana" or
field3:"unique3" or
- field4:"target" or field4:"keyword" or field4:"banana" or
field4:"unique4" or
- field5:"target" or field5:"keyword" or field5:"cherry" or
field5:"unique5"')
+ WHERE search('field1:"target" OR field1:"keyword" OR field1:"apple" OR
field1:"unique1" OR
+ field2:"target" OR field2:"keyword" OR field2:"apple" OR
field2:"unique2" OR
+ field3:"target" OR field3:"keyword" OR field3:"banana" OR
field3:"unique3" OR
+ field4:"target" OR field4:"keyword" OR field4:"banana" OR
field4:"unique4" OR
+ field5:"target" OR field5:"keyword" OR field5:"cherry" OR
field5:"unique5"')
"""
// Boundary Test 7: Special characters and NULL interaction
qt_boundary_7_special_chars_or """
SELECT /*+SET_VAR(enable_common_expr_pushdown=true) */ count(*) FROM
${tableName}
- WHERE search('field1:special123 or field2:nonexistent')
+ WHERE search('field1:special123 OR field2:nonexistent')
"""
qt_boundary_7_special_chars_and """
SELECT /*+SET_VAR(enable_common_expr_pushdown=true) */ count(*) FROM
${tableName}
- WHERE search('field1:special123 and field2:chars456')
+ WHERE search('field1:special123 AND field2:chars456')
"""
// Boundary Test 8: Case sensitivity with NULL fields
qt_boundary_8_case_variations """
SELECT /*+SET_VAR(enable_common_expr_pushdown=true) */ id FROM
${tableName}
- WHERE search('field1:Target or field2:TARGET or field3:target or
field4:TaRgEt')
+ WHERE search('field1:Target OR field2:TARGET OR field3:target OR
field4:TaRgEt')
ORDER BY id
"""
// Boundary Test 9: Multiple NOT operations
qt_boundary_9_multiple_not """
SELECT /*+SET_VAR(enable_common_expr_pushdown=true) */ count(*) FROM
${tableName}
- WHERE search('not (field1:nonexistent or field2:nonexistent or
field3:nonexistent)')
+ WHERE search('NOT (field1:nonexistent OR field2:nonexistent OR
field3:nonexistent)')
"""
qt_boundary_9_external_multiple_not """
SELECT /*+SET_VAR(enable_common_expr_pushdown=true) */ count(*) FROM
${tableName}
- WHERE not search('field1:nonexistent or field2:nonexistent or
field3:nonexistent')
+ WHERE not search('field1:nonexistent OR field2:nonexistent OR
field3:nonexistent')
"""
// Boundary Test 10: Performance with NULL-heavy dataset
qt_boundary_10_performance """
SELECT /*+SET_VAR(enable_common_expr_pushdown=true) */ count(*) FROM
${tableName}
- WHERE search('(field1:test or field1:target or field1:keyword) and
- (field2:test or field2:target or field2:keyword) and
- not (field3:nonexistent or field4:nonexistent or
field5:nonexistent)')
+ WHERE search('(field1:test OR field1:target OR field1:keyword) AND
+ (field2:test OR field2:target OR field2:keyword) AND
+ NOT (field3:nonexistent OR field4:nonexistent OR
field5:nonexistent)')
"""
}
\ No newline at end of file
diff --git a/regression-test/suites/search/test_search_dsl_operators.groovy
b/regression-test/suites/search/test_search_dsl_operators.groovy
new file mode 100644
index 00000000000..37ac49abf55
--- /dev/null
+++ b/regression-test/suites/search/test_search_dsl_operators.groovy
@@ -0,0 +1,231 @@
+// Licensed to the Apache Software Foundation (ASF) under one
+// or more contributor license agreements. See the NOTICE file
+// distributed with this work for additional information
+// regarding copyright ownership. The ASF licenses this file
+// to you under the Apache License, Version 2.0 (the
+// "License"); you may not use this file except in compliance
+// with the License. You may obtain a copy of the License at
+//
+// http://www.apache.org/licenses/LICENSE-2.0
+//
+// Unless required by applicable law or agreed to in writing,
+// software distributed under the License is distributed on an
+// "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+// KIND, either express or implied. See the License for the
+// specific language governing permissions and limitations
+// under the License.
+
+/**
+ * Tests for search DSL operator scenarios
+ *
+ * This test suite validates Lucene mode parsing against the exact test cases
+ * documented in specification to ensure behavior matches Elasticsearch/Lucene
semantics.
+ *
+ * Test Data Setup:
+ * | Email | Firstname |
+ * | [email protected] | "aterm bterm" |
+ * | [email protected] | "bterm cterm" |
+ * | [email protected] | "cterm dterm" |
+ * | [email protected] | "dterm eterm aterm" |
+ *
+ * Key Lucene Semantics:
+ * - Operators are processed left-to-right as modifiers
+ * - AND marks preceding and current terms as MUST (+)
+ * - OR marks preceding and current terms as SHOULD
+ * - NOT marks current term as MUST_NOT (-)
+ * - With minimum_should_match=0 and MUST clauses present, SHOULD clauses are
discarded
+ */
+suite("test_search_dsl_operators") {
+ def tableName = "search_dsl_operators_test"
+
+ sql "DROP TABLE IF EXISTS ${tableName}"
+
+ // Create table with inverted indexes
+ // Using parser=english to tokenize firstname field
+ sql """
+ CREATE TABLE ${tableName} (
+ id INT,
+ email VARCHAR(100),
+ firstname VARCHAR(200),
+ INDEX idx_firstname(firstname) USING INVERTED PROPERTIES("parser"
= "english")
+ ) ENGINE=OLAP
+ DUPLICATE KEY(id)
+ DISTRIBUTED BY HASH(id) BUCKETS 1
+ PROPERTIES ("replication_allocation" = "tag.location.default: 1")
+ """
+
+ // Insert test data
+ sql """INSERT INTO ${tableName} VALUES
+ (1, '[email protected]', 'aterm bterm'),
+ (2, '[email protected]', 'bterm cterm'),
+ (3, '[email protected]', 'cterm dterm'),
+ (4, '[email protected]', 'dterm eterm aterm')
+ """
+
+ // Wait for index building
+ Thread.sleep(3000)
+
+ // ============ Test 1: aterm OR bterm OR cterm ============
+ // All OR operators -> at least one must match (minimum_should_match=1)
+ // Expected: rows 1,2,3,4 (all match at least one term)
+ qt_dsl_or_chain """
+ SELECT /*+SET_VAR(enable_common_expr_pushdown=true) */ id, firstname
+ FROM ${tableName}
+ WHERE search('aterm OR bterm OR cterm',
'{"default_field":"firstname","default_operator":"and","mode":"lucene"}')
+ ORDER BY id
+ """
+
+ // ============ Test 2: dterm AND eterm AND aterm ============
+ // All AND operators -> all must match
+ // Expected: row 4 only (the only one with all three terms)
+ qt_dsl_and_chain """
+ SELECT /*+SET_VAR(enable_common_expr_pushdown=true) */ id, firstname
+ FROM ${tableName}
+ WHERE search('dterm AND eterm AND aterm',
'{"default_field":"firstname","default_operator":"and","mode":"lucene"}')
+ ORDER BY id
+ """
+
+ // ============ Test 3: aterm AND bterm OR cterm ============
+ // Lucene left-to-right parsing with minimum_should_match=0:
+ // - aterm: MUST (first term, default_operator=AND)
+ // - bterm: MUST (AND introduces)
+ // - cterm: SHOULD (OR introduces), bterm becomes SHOULD too
+ // Final: +aterm bterm cterm
+ // With minimum_should_match=0 and MUST present, SHOULD discarded
+ // Result: effectively +aterm only
+ // Expected: rows 1, 4 (rows containing "aterm")
+ qt_dsl_and_or_mixed """
+ SELECT /*+SET_VAR(enable_common_expr_pushdown=true) */ id, firstname
+ FROM ${tableName}
+ WHERE search('aterm AND bterm OR cterm',
'{"default_field":"firstname","default_operator":"and","mode":"lucene","minimum_should_match":0}')
+ ORDER BY id
+ """
+
+ // ============ Test 4: aterm AND NOT bterm OR cterm ============
+ // Lucene left-to-right parsing:
+ // - aterm: MUST
+ // - bterm: MUST_NOT (NOT modifier)
+ // - cterm: SHOULD (OR introduces)
+ // Final: +aterm -bterm cterm
+ // With minimum_should_match=0 and MUST present, SHOULD discarded
+ // Result: +aterm -bterm
+ // Expected: row 4 only (has "aterm" but NOT "bterm")
+ qt_dsl_and_not_or """
+ SELECT /*+SET_VAR(enable_common_expr_pushdown=true) */ id, firstname
+ FROM ${tableName}
+ WHERE search('aterm AND NOT bterm OR cterm',
'{"default_field":"firstname","default_operator":"and","mode":"lucene","minimum_should_match":0}')
+ ORDER BY id
+ """
+
+ // ============ Test 5: cterm dterm (implicit AND) ============
+ // No explicit operators, default_operator=AND
+ // Same as: cterm AND dterm
+ // Expected: row 3 only (has both "cterm" AND "dterm")
+ qt_dsl_implicit_and """
+ SELECT /*+SET_VAR(enable_common_expr_pushdown=true) */ id, firstname
+ FROM ${tableName}
+ WHERE search('cterm dterm',
'{"default_field":"firstname","default_operator":"and","mode":"lucene"}')
+ ORDER BY id
+ """
+
+ // ============ Test 6: "aterm eterm" (phrase query, wrong order)
============
+ // Phrase query requires tokens in exact order
+ // Data has "dterm eterm aterm" - "aterm" comes AFTER "eterm", not before
+ // Expected: no match
+ qt_dsl_phrase_wrong_order """
+ SELECT /*+SET_VAR(enable_common_expr_pushdown=true) */ id, firstname
+ FROM ${tableName}
+ WHERE search('"aterm eterm"',
'{"default_field":"firstname","default_operator":"and","mode":"lucene"}')
+ ORDER BY id
+ """
+
+ // ============ Test 7: "eterm aterm" (phrase query, correct order)
============
+ // Phrase query requires tokens in exact order
+ // Data has "dterm eterm aterm" - "eterm aterm" appears in this order
+ // Expected: row 4
+ qt_dsl_phrase_correct_order """
+ SELECT /*+SET_VAR(enable_common_expr_pushdown=true) */ id, firstname
+ FROM ${tableName}
+ WHERE search('"eterm aterm"',
'{"default_field":"firstname","default_operator":"and","mode":"lucene"}')
+ ORDER BY id
+ """
+
+ // ============ Test 8: eterm\ dterm AND aterm (escaped space test)
============
+ // Tests escape handling in Lucene mode
+ // In current implementation, the escaped space is processed such that
+ // the query effectively becomes a term query for individual tokens
+ // Row 4 contains all terms (dterm, eterm, aterm)
+ // Expected: row 4
+ qt_dsl_escaped_space_and """
+ SELECT /*+SET_VAR(enable_common_expr_pushdown=true) */ id, firstname
+ FROM ${tableName}
+ WHERE search('eterm\\\\ dterm AND aterm',
'{"default_field":"firstname","default_operator":"and","mode":"lucene"}')
+ ORDER BY id
+ """
+
+ // ============ Test 9: "dterm eterm" AND aterm ============
+ // Phrase query + AND
+ // Row 4 has "dterm eterm aterm" - phrase "dterm eterm" matches, and
"aterm" is also present
+ // Expected: row 4
+ qt_dsl_phrase_and_term """
+ SELECT /*+SET_VAR(enable_common_expr_pushdown=true) */ id, firstname
+ FROM ${tableName}
+ WHERE search('"dterm eterm" AND aterm',
'{"default_field":"firstname","default_operator":"and","mode":"lucene"}')
+ ORDER BY id
+ """
+
+ // ============ Test 10: "eterm dterm" AND aterm (phrase wrong order)
============
+ // Phrase "eterm dterm" is wrong order (data has "dterm eterm")
+ // Expected: no match
+ qt_dsl_phrase_wrong_and_term """
+ SELECT /*+SET_VAR(enable_common_expr_pushdown=true) */ id, firstname
+ FROM ${tableName}
+ WHERE search('"eterm dterm" AND aterm',
'{"default_field":"firstname","default_operator":"and","mode":"lucene"}')
+ ORDER BY id
+ """
+
+ // ============ Test 11: "eterm dterm" OR cterm ============
+ // Phrase OR term
+ // Phrase "eterm dterm" won't match (wrong order)
+ // cterm matches rows 2, 3
+ // Expected: rows 2, 3
+ qt_dsl_phrase_or_term_1 """
+ SELECT /*+SET_VAR(enable_common_expr_pushdown=true) */ id, firstname
+ FROM ${tableName}
+ WHERE search('"eterm dterm" OR cterm',
'{"default_field":"firstname","default_operator":"and","mode":"lucene"}')
+ ORDER BY id
+ """
+
+ // ============ Test 12: "dterm eterm" OR cterm ============
+ // Phrase OR term
+ // Phrase "dterm eterm" matches row 4
+ // cterm matches rows 2, 3
+ // Expected: rows 2, 3, 4
+ qt_dsl_phrase_or_term_2 """
+ SELECT /*+SET_VAR(enable_common_expr_pushdown=true) */ id, firstname
+ FROM ${tableName}
+ WHERE search('"dterm eterm" OR cterm',
'{"default_field":"firstname","default_operator":"and","mode":"lucene"}')
+ ORDER BY id
+ """
+
+ // ============ Test 13: aterm AND bterm OR cterm with
minimum_should_match=1 ============
+ // Same as Test 3 but with minimum_should_match=1
+ // Final state: +aterm bterm cterm (aterm is MUST, bterm and cterm are
SHOULD)
+ // With minimum_should_match=1, at least 1 SHOULD must match
+ // Result: aterm AND (bterm OR cterm)
+ // Expected: rows 1, 2 (row 1 has aterm+bterm, row 2 doesn't have aterm)
+ // Wait - row 2 doesn't have aterm, so it shouldn't match
+ // Row 1: has aterm, has bterm -> matches
+ // Row 4: has aterm, doesn't have bterm or cterm -> doesn't match (no
SHOULD satisfied)
+ // Actually row 4 has aterm but no bterm/cterm...
+ // Expected: row 1 only
+ qt_dsl_and_or_min_should_1 """
+ SELECT /*+SET_VAR(enable_common_expr_pushdown=true) */ id, firstname
+ FROM ${tableName}
+ WHERE search('aterm AND bterm OR cterm',
'{"default_field":"firstname","default_operator":"and","mode":"lucene","minimum_should_match":1}')
+ ORDER BY id
+ """
+
+ // Cleanup
+ sql "DROP TABLE IF EXISTS ${tableName}"
+}
diff --git a/regression-test/suites/search/test_search_escape.groovy
b/regression-test/suites/search/test_search_escape.groovy
index 629d3fcc1f5..5414455d9d9 100644
--- a/regression-test/suites/search/test_search_escape.groovy
+++ b/regression-test/suites/search/test_search_escape.groovy
@@ -143,21 +143,31 @@ suite("test_search_escape") {
ORDER BY id
"""
- // ============ Test 9: Lowercase and operator ============
- qt_lowercase_and """
- SELECT /*+SET_VAR(enable_common_expr_pushdown=true) */ id, content
- FROM ${tableName}
- WHERE search('content:first and content:fruit')
- ORDER BY id
- """
-
- // ============ Test 10: Lowercase or operator ============
- qt_lowercase_or """
- SELECT /*+SET_VAR(enable_common_expr_pushdown=true) */ id, content
- FROM ${tableName}
- WHERE search('content:first or content:second')
- ORDER BY id
- """
+ // ============ Test 9: Lowercase 'and' should cause parse error
============
+ // Per requirement: Only uppercase AND/OR/NOT are operators
+ // Lowercase 'and' is treated as a bare term (no field), causing error
+ test {
+ sql """
+ SELECT /*+SET_VAR(enable_common_expr_pushdown=true) */ id, content
+ FROM ${tableName}
+ WHERE search('content:first and content:fruit')
+ ORDER BY id
+ """
+ exception "No field specified and no default_field configured"
+ }
+
+ // ============ Test 10: Lowercase 'or' should cause parse error
============
+ // Per requirement: Only uppercase AND/OR/NOT are operators
+ // Lowercase 'or' is treated as a bare term (no field), causing error
+ test {
+ sql """
+ SELECT /*+SET_VAR(enable_common_expr_pushdown=true) */ id, content
+ FROM ${tableName}
+ WHERE search('content:first or content:second')
+ ORDER BY id
+ """
+ exception "No field specified and no default_field configured"
+ }
// ============ Test 11: Exclamation NOT operator ============
qt_exclamation_not """
diff --git a/regression-test/suites/search/test_search_function.groovy.backup
b/regression-test/suites/search/test_search_function.groovy.backup
new file mode 100644
index 00000000000..47e5944e393
--- /dev/null
+++ b/regression-test/suites/search/test_search_function.groovy.backup
@@ -0,0 +1,261 @@
+// Licensed to the Apache Software Foundation (ASF) under one
+// or more contributor license agreements. See the NOTICE file
+// distributed with this work for additional information
+// regarding copyright ownership. The ASF licenses this file
+// to you under the Apache License, Version 2.0 (the
+// "License"); you may not use this file except in compliance
+// with the License. You may obtain a copy of the License at
+//
+// http://www.apache.org/licenses/LICENSE-2.0
+//
+// Unless required by applicable law or agreed to in writing,
+// software distributed under the License is distributed on an
+// "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+// KIND, either express or implied. See the License for the
+// specific language governing permissions and limitations
+// under the License.
+
+suite("test_search_function") {
+
+ def tableName = "search_test_table"
+ def indexTableName = "search_test_index_table"
+
+ sql "DROP TABLE IF EXISTS ${tableName}"
+ sql "DROP TABLE IF EXISTS ${indexTableName}"
+
+ // Create test table without inverted index
+ sql """
+ CREATE TABLE ${tableName} (
+ id INT,
+ title VARCHAR(255),
+ content TEXT,
+ category VARCHAR(100),
+ tags VARCHAR(200),
+ publish_date DATE,
+ view_count INT
+ ) ENGINE=OLAP
+ DUPLICATE KEY(id)
+ DISTRIBUTED BY HASH(id) BUCKETS 3
+ PROPERTIES (
+ "replication_allocation" = "tag.location.default: 1"
+ )
+ """
+
+ // Create test table with inverted index
+ sql """
+ CREATE TABLE ${indexTableName} (
+ id INT,
+ title VARCHAR(255),
+ content TEXT,
+ category VARCHAR(100),
+ tags VARCHAR(200),
+ publish_date DATE,
+ view_count INT,
+ INDEX idx_title (title) USING INVERTED,
+ INDEX idx_content (content) USING INVERTED PROPERTIES("parser" =
"english"),
+ INDEX idx_category (category) USING INVERTED,
+ INDEX idx_tags (tags) USING INVERTED PROPERTIES("parser" =
"english")
+ ) ENGINE=OLAP
+ DUPLICATE KEY(id)
+ DISTRIBUTED BY HASH(id) BUCKETS 3
+ PROPERTIES (
+ "replication_allocation" = "tag.location.default: 1"
+ )
+ """
+
+ // Insert test data
+ def testData = [
+ [1, "Machine Learning Basics", "Introduction to machine learning
algorithms and concepts", "Technology", "machine learning, AI, algorithms",
"2023-01-15", 1500],
+ [2, "Deep Learning Tutorial", "Advanced deep learning techniques and
neural networks", "Technology", "deep learning, neural networks, AI",
"2023-02-20", 2300],
+ [3, "Python Programming Guide", "Complete guide to Python programming
language", "Programming", "python, programming, tutorial", "2023-03-10", 1800],
+ [4, "Data Science Methods", "Statistical methods for data science and
analytics", "Science", "data science, statistics, analytics", "2023-04-05",
1200],
+ [5, "Web Development Tips", "Modern web development best practices",
"Technology", "web development, javascript, HTML", "2023-05-12", 950],
+ [6, "Algorithm Design", "Fundamental algorithms and data structures",
"Computer Science", "algorithms, data structures, programming", "2023-06-18",
1650],
+ [7, "Natural Language Processing", "NLP techniques and applications",
"Technology", "NLP, natural language, processing", "2023-07-22", 1100],
+ [8, "Cloud Computing Overview", "Introduction to cloud computing
platforms", "Technology", "cloud computing, AWS, Azure", "2023-08-14", 1350],
+ [9, "Database Systems", "Relational and NoSQL database concepts",
"Technology", "database, SQL, NoSQL", "2023-09-09", 1450],
+ [10, "Software Engineering", "Best practices in software development",
"Programming", "software engineering, development, practices", "2023-10-01",
1750]
+ ]
+
+ for (def row : testData) {
+ sql """INSERT INTO ${tableName} VALUES (${row[0]}, '${row[1]}',
'${row[2]}', '${row[3]}', '${row[4]}', '${row[5]}', ${row[6]})"""
+ sql """INSERT INTO ${indexTableName} VALUES (${row[0]}, '${row[1]}',
'${row[2]}', '${row[3]}', '${row[4]}', '${row[5]}', ${row[6]})"""
+ }
+
+ // Wait for index building and data settling
+ Thread.sleep(10000)
+
+ // Verify data insertion
+ qt_sql "SELECT COUNT(*) FROM ${tableName}"
+ qt_sql "SELECT COUNT(*) FROM ${indexTableName}"
+
+ // Test 1: Basic term search
+ qt_sql "SELECT id, title FROM ${indexTableName} WHERE
search('title:Machine')"
+
+ // Test 2: Phrase search
+ qt_sql "SELECT id, title FROM ${indexTableName} WHERE
search('title:\"Machine Learning\"')"
+
+ // Test 3: Multiple field search with AND
+ qt_sql "SELECT id, title FROM ${indexTableName} WHERE
search('title:Learning AND category:Technology') ORDER BY id"
+
+ // Test 4: Multiple field search with OR
+ qt_sql "SELECT id, title FROM ${indexTableName} WHERE search('title:Python
OR title:Algorithm') ORDER BY id"
+
+ // Test 5: NOT search
+ qt_sql "SELECT COUNT(*) FROM ${indexTableName} WHERE
search('category:Technology AND NOT title:Machine')"
+
+ // Test 6: Complex nested search
+ test {
+ sql "SELECT id, title FROM ${indexTableName} WHERE
search('(title:Learning OR content:algorithms) AND category:Technology') ORDER
BY id"
+ result([
+ [1, "Machine Learning Basics"],
+ [2, "Deep Learning Tutorial"]
+ ])
+ }
+
+ // Test 7: Wildcard search
+ test {
+ sql "SELECT id, title FROM ${indexTableName} WHERE
search('title:Learn*') ORDER BY id"
+ result([
+ [1, "Machine Learning Basics"],
+ [2, "Deep Learning Tutorial"]
+ ])
+ }
+
+ // Test 8: Prefix search
+ test {
+ sql "SELECT id, title FROM ${indexTableName} WHERE
search('title:Data*')"
+ result([
+ [4, "Data Science Methods"],
+ [6, "Algorithm Design"]
+ ])
+ }
+
+ // Test 9: Search in content field
+ test {
+ sql "SELECT id, title FROM ${indexTableName} WHERE
search('content:neural')"
+ result([
+ [2, "Deep Learning Tutorial"]
+ ])
+ }
+
+ // Test 10: Search in tags field
+ test {
+ sql "SELECT id, title FROM ${indexTableName} WHERE
search('tags:programming') ORDER BY id"
+ result([
+ [3, "Python Programming Guide"],
+ [6, "Algorithm Design"]
+ ])
+ }
+
+ // Test 11: Case insensitive search
+ test {
+ sql "SELECT id, title FROM ${indexTableName} WHERE
search('title:MACHINE')"
+ result([
+ [1, "Machine Learning Basics"]
+ ])
+ }
+
+ // Test 12: Search with spaces in field values
+ test {
+ sql "SELECT id, title FROM ${indexTableName} WHERE
search('content:\"machine learning\"')"
+ result([
+ [1, "Machine Learning Basics"]
+ ])
+ }
+
+ // Test 13: Empty search result
+ test {
+ sql "SELECT COUNT(*) FROM ${indexTableName} WHERE
search('title:nonexistent')"
+ result([
+ [0]
+ ])
+ }
+
+ // Test 14: Search combined with other WHERE conditions
+ test {
+ sql "SELECT id, title FROM ${indexTableName} WHERE
search('category:Technology') AND view_count > 1400 ORDER BY id"
+ result([
+ [1, "Machine Learning Basics"],
+ [2, "Deep Learning Tutorial"]
+ ])
+ }
+
+ // Test 15: Search with GROUP BY
+ test {
+ sql "SELECT category, COUNT(*) as cnt FROM ${indexTableName} WHERE
search('title:Learning OR title:Programming') GROUP BY category ORDER BY
category"
+ result([
+ ["Programming", 1],
+ ["Technology", 2]
+ ])
+ }
+
+ // Test 16: Search with ORDER BY
+ test {
+ sql "SELECT id, title, view_count FROM ${indexTableName} WHERE
search('tags:AI OR tags:programming') ORDER BY view_count DESC"
+ result([
+ [2, "Deep Learning Tutorial", 2300],
+ [3, "Python Programming Guide", 1800],
+ [6, "Algorithm Design", 1650],
+ [1, "Machine Learning Basics", 1500]
+ ])
+ }
+
+ // Test 17: Search with LIMIT
+ test {
+ sql "SELECT id, title FROM ${indexTableName} WHERE
search('category:Technology') ORDER BY id LIMIT 3"
+ result([
+ [1, "Machine Learning Basics"],
+ [2, "Deep Learning Tutorial"],
+ [5, "Web Development Tips"]
+ ])
+ }
+
+ // Test 18: Search function in SELECT clause (should not be allowed -
search is a predicate)
+ test {
+ try {
+ sql "SELECT id, search('title:Machine') FROM ${indexTableName}"
+ assertTrue(false, "Expected exception for search in SELECT clause")
+ } catch (Exception e) {
+ assertTrue(e.getMessage().contains("search") ||
e.getMessage().contains("not found"))
+ }
+ }
+
+ // Test 19: Invalid DSL syntax
+ test {
+ try {
+ sql "SELECT id FROM ${indexTableName} WHERE search('title:')"
+ assertTrue(false, "Expected exception for invalid DSL")
+ } catch (Exception e) {
+ assertTrue(e.getMessage().contains("Invalid") ||
e.getMessage().contains("syntax"))
+ }
+ }
+
+ // Test 20: ANY query test
+ test {
+ sql "SELECT id, title FROM ${indexTableName} WHERE search('tags:ANY(AI
programming)') ORDER BY id"
+ check { result ->
+ assertTrue(result.size() > 0, "Should find records with AI or
programming in tags")
+ }
+ }
+
+ // Test 21: ALL query test
+ test {
+ sql "SELECT id, title FROM ${indexTableName} WHERE
search('tags:ALL(machine learning)') ORDER BY id"
+ result([
+ [1, "Machine Learning Basics"]
+ ])
+ }
+
+ // Test 22: Search on non-indexed table (should still work but may be
slower)
+ test {
+ sql "SELECT id, title FROM ${tableName} WHERE search('title:Machine')"
+ result([
+ [1, "Machine Learning Basics"]
+ ])
+ }
+
+ // Cleanup
+ sql "DROP TABLE IF EXISTS ${tableName}"
+ sql "DROP TABLE IF EXISTS ${indexTableName}"
+}
diff --git a/regression-test/suites/search/test_search_null_regression.groovy
b/regression-test/suites/search/test_search_null_regression.groovy
index 3fe85461455..742f86959a1 100644
--- a/regression-test/suites/search/test_search_null_regression.groovy
+++ b/regression-test/suites/search/test_search_null_regression.groovy
@@ -69,7 +69,7 @@ suite("test_search_null_regression") {
// vs title match "Ronald" or (content match_all "Selma Blair")
qt_regression_1_search_or """
SELECT /*+SET_VAR(enable_common_expr_pushdown=true) */ count(*) FROM
${tableName}
- WHERE search('title:Ronald or (content:ALL(Selma Blair))')
+ WHERE search('title:Ronald OR (content:ALL(Selma Blair))')
"""
qt_regression_1_match_or """
@@ -80,7 +80,7 @@ suite("test_search_null_regression") {
// Detailed verification - get actual matching rows for OR query
qt_regression_1_search_or_rows """
SELECT /*+SET_VAR(enable_common_expr_pushdown=true) */ id, title,
content FROM ${tableName}
- WHERE search('title:Ronald or (content:ALL(Selma Blair))')
+ WHERE search('title:Ronald OR (content:ALL(Selma Blair))')
ORDER BY id
"""
@@ -94,7 +94,7 @@ suite("test_search_null_regression") {
// This reproduces: search('not content:"Round"') vs not
search('content:"Round"')
qt_regression_2_internal_not """
SELECT /*+SET_VAR(enable_common_expr_pushdown=true) */ count(*) FROM
${tableName}
- WHERE search('not content:Round')
+ WHERE search('NOT content:Round')
"""
qt_regression_2_external_not """
@@ -105,7 +105,7 @@ suite("test_search_null_regression") {
// Detailed verification for NOT query
qt_regression_2_internal_not_rows """
SELECT /*+SET_VAR(enable_common_expr_pushdown=true) */ id, title,
content FROM ${tableName}
- WHERE search('not content:Round')
+ WHERE search('NOT content:Round')
ORDER BY id
"""
@@ -119,7 +119,7 @@ suite("test_search_null_regression") {
// Verify that OR queries properly handle NULL values according to SQL
semantics
qt_regression_3_null_or """
SELECT /*+SET_VAR(enable_common_expr_pushdown=true) */ id FROM
${tableName}
- WHERE search('title:NonExistent or content:Ronald')
+ WHERE search('title:NonExistent OR content:Ronald')
ORDER BY id
"""
@@ -133,14 +133,14 @@ suite("test_search_null_regression") {
// Regression Test 4: NULL Handling in AND Queries
qt_regression_4_null_and """
SELECT /*+SET_VAR(enable_common_expr_pushdown=true) */ id FROM
${tableName}
- WHERE search('title:Ronald and content:biography')
+ WHERE search('title:Ronald AND content:biography')
ORDER BY id
"""
// Regression Test 5: Complex Boolean Operations
qt_regression_5_complex_search """
SELECT /*+SET_VAR(enable_common_expr_pushdown=true) */ count(*) FROM
${tableName}
- WHERE search('(title:Ronald or content:Selma) and not content:Round')
+ WHERE search('(title:Ronald OR content:Selma) AND NOT content:Round')
"""
qt_regression_5_complex_match """
@@ -151,7 +151,7 @@ suite("test_search_null_regression") {
// Regression Test 6: Edge Case - All NULL Query
qt_regression_6_all_null """
SELECT /*+SET_VAR(enable_common_expr_pushdown=true) */ count(*) FROM
${tableName}
- WHERE search('title:NonExistent and content:NonExistent')
+ WHERE search('title:NonExistent AND content:NonExistent')
"""
// Regression Test 7: Case Sensitivity and Variations
@@ -168,23 +168,23 @@ suite("test_search_null_regression") {
// Regression Test 8: Multiple NOT operations
qt_regression_8_multiple_not """
SELECT /*+SET_VAR(enable_common_expr_pushdown=true) */ count(*) FROM
${tableName}
- WHERE search('not (title:nonexistent or content:nonexistent)')
+ WHERE search('NOT (title:nonexistent OR content:nonexistent)')
"""
qt_regression_8_external_multiple_not """
SELECT /*+SET_VAR(enable_common_expr_pushdown=true) */ count(*) FROM
${tableName}
- WHERE not search('title:nonexistent or content:nonexistent')
+ WHERE not search('title:nonexistent OR content:nonexistent')
"""
// Regression Test 9: Empty string handling
qt_regression_9_empty_string """
SELECT /*+SET_VAR(enable_common_expr_pushdown=true) */ count(*) FROM
${tableName}
- WHERE search('title:"" or content:Round')
+ WHERE search('title:"" OR content:Round')
"""
// Regression Test 10: Performance test with complex query
qt_regression_10_performance """
SELECT /*+SET_VAR(enable_common_expr_pushdown=true) */ count(*) FROM
${tableName}
- WHERE search('(title:Ronald or title:Selma or content:Round) and not
(title:NonExistent and content:NonExistent)')
+ WHERE search('(title:Ronald OR title:Selma OR content:Round) AND NOT
(title:NonExistent AND content:NonExistent)')
"""
}
\ No newline at end of file
diff --git a/regression-test/suites/search/test_search_null_semantics.groovy
b/regression-test/suites/search/test_search_null_semantics.groovy
index c7d97c18bdc..9e18e063cf9 100644
--- a/regression-test/suites/search/test_search_null_semantics.groovy
+++ b/regression-test/suites/search/test_search_null_semantics.groovy
@@ -61,7 +61,7 @@ suite("test_search_null_semantics") {
// title match "Ronald" or (content match_all "Selma Blair")
qt_test_case_1_search """
SELECT /*+SET_VAR(enable_common_expr_pushdown=true) */ count(*) FROM
${tableName}
- WHERE search('title:Ronald or (content:ALL(Selma Blair))')
+ WHERE search('title:Ronald OR (content:ALL(Selma Blair))')
"""
qt_test_case_1_match """
@@ -73,7 +73,7 @@ suite("test_search_null_semantics") {
// search('not content:"Round"') should match not search('content:"Round"')
qt_test_case_2_internal_not """
SELECT /*+SET_VAR(enable_common_expr_pushdown=true) */ count(*) FROM
${tableName}
- WHERE search('not content:Round')
+ WHERE search('NOT content:Round')
"""
qt_test_case_2_external_not """
@@ -92,7 +92,7 @@ suite("test_search_null_semantics") {
// Verify that NULL OR TRUE = TRUE logic works
qt_test_case_3_or_with_null """
SELECT /*+SET_VAR(enable_common_expr_pushdown=true) */ id, title,
content FROM ${tableName}
- WHERE search('title:Ronald or content:biography')
+ WHERE search('title:Ronald OR content:biography')
ORDER BY id
"""
@@ -100,14 +100,14 @@ suite("test_search_null_semantics") {
// Verify that NULL AND TRUE = NULL logic works
qt_test_case_4_and_with_null """
SELECT /*+SET_VAR(enable_common_expr_pushdown=true) */ id, title,
content FROM ${tableName}
- WHERE search('title:Ronald and content:biography')
+ WHERE search('title:Ronald AND content:biography')
ORDER BY id
"""
// Test Case 5: Complex OR query with multiple NULL scenarios
qt_test_case_5_complex_or_search """
SELECT /*+SET_VAR(enable_common_expr_pushdown=true) */ count(*) FROM
${tableName}
- WHERE search('title:Unknown or content:mascot or category:Test')
+ WHERE search('title:Unknown OR content:mascot OR category:Test')
"""
qt_test_case_5_complex_or_match """
@@ -118,7 +118,7 @@ suite("test_search_null_semantics") {
// Test Case 6: NOT query with different field types
qt_test_case_6_not_title_search """
SELECT /*+SET_VAR(enable_common_expr_pushdown=true) */ count(*) FROM
${tableName}
- WHERE search('not title:Ronald')
+ WHERE search('NOT title:Ronald')
"""
qt_test_case_6_not_title_external """
@@ -129,13 +129,13 @@ suite("test_search_null_semantics") {
// Test Case 7: Mixed boolean operations
qt_test_case_7_mixed """
SELECT /*+SET_VAR(enable_common_expr_pushdown=true) */ count(*) FROM
${tableName}
- WHERE search('(title:Ronald or content:Selma) and not
category:Unknown')
+ WHERE search('(title:Ronald OR content:Selma) AND NOT
category:Unknown')
"""
// Test Case 8: Edge case - all NULL fields
qt_test_case_8_all_null """
SELECT /*+SET_VAR(enable_common_expr_pushdown=true) */ count(*) FROM
${tableName}
- WHERE search('title:NonExistent or content:NonExistent or
category:NonExistent')
+ WHERE search('title:NonExistent OR content:NonExistent OR
category:NonExistent')
"""
// ------------------------------------------------------------------
diff --git
a/regression-test/suites/search/test_search_vs_match_consistency.groovy
b/regression-test/suites/search/test_search_vs_match_consistency.groovy
index 05ba88344cf..e39268838e5 100644
--- a/regression-test/suites/search/test_search_vs_match_consistency.groovy
+++ b/regression-test/suites/search/test_search_vs_match_consistency.groovy
@@ -117,7 +117,7 @@ suite("test_search_vs_match_consistency") {
// Test Suite 1: Basic OR query consistency
qt_test_1_1_search """
SELECT /*+SET_VAR(enable_common_expr_pushdown=true) */ count(*) FROM
${tableName}
- WHERE search('title:Ronald or title:Selma')
+ WHERE search('title:Ronald OR title:Selma')
"""
qt_test_1_1_match """
@@ -128,7 +128,7 @@ suite("test_search_vs_match_consistency") {
// Test 1.2: OR across different fields
qt_test_1_2_search """
SELECT /*+SET_VAR(enable_common_expr_pushdown=true) */ count(*) FROM
${tableName}
- WHERE search('title:Ronald or content:Selma')
+ WHERE search('title:Ronald OR content:Selma')
"""
qt_test_1_2_match """
@@ -139,7 +139,7 @@ suite("test_search_vs_match_consistency") {
// Test 1.3: Complex OR with ALL operation
qt_test_1_3_search """
SELECT /*+SET_VAR(enable_common_expr_pushdown=true) */ count(*) FROM
${tableName}
- WHERE search('title:Ronald or (content:ALL(Selma Blair))')
+ WHERE search('title:Ronald OR (content:ALL(Selma Blair))')
"""
qt_test_1_3_match """
@@ -150,7 +150,7 @@ suite("test_search_vs_match_consistency") {
// Test Suite 2: NOT query consistency
qt_test_2_1_internal_not """
SELECT /*+SET_VAR(enable_common_expr_pushdown=true) */ count(*) FROM
${tableName}
- WHERE search('not content:Round')
+ WHERE search('NOT content:Round')
"""
qt_test_2_1_external_not """
@@ -161,7 +161,7 @@ suite("test_search_vs_match_consistency") {
// Test 2.2: NOT with different fields
qt_test_2_2_internal_not """
SELECT /*+SET_VAR(enable_common_expr_pushdown=true) */ count(*) FROM
${tableName}
- WHERE search('not title:Ronald')
+ WHERE search('NOT title:Ronald')
"""
qt_test_2_2_external_not """
@@ -172,49 +172,49 @@ suite("test_search_vs_match_consistency") {
// Test 2.3: NOT with complex expression
qt_test_2_3_internal_not """
SELECT /*+SET_VAR(enable_common_expr_pushdown=true) */ count(*) FROM
${tableName}
- WHERE search('not (title:Ronald and content:biography)')
+ WHERE search('NOT (title:Ronald AND content:biography)')
"""
qt_test_2_3_external_not """
SELECT /*+SET_VAR(enable_common_expr_pushdown=true) */ count(*) FROM
${tableName}
- WHERE not search('title:Ronald and content:biography')
+ WHERE not search('title:Ronald AND content:biography')
"""
// Test Suite 3: NULL value behavior in OR queries
qt_test_3_1_or_with_null """
SELECT /*+SET_VAR(enable_common_expr_pushdown=true) */ id, title,
content FROM ${tableName}
- WHERE search('title:NonExistent or content:Ronald')
+ WHERE search('title:NonExistent OR content:Ronald')
ORDER BY id
"""
qt_test_3_2_or_multiple_null """
SELECT /*+SET_VAR(enable_common_expr_pushdown=true) */ id, title,
content FROM ${tableName}
- WHERE search('title:Mystery or content:Round')
+ WHERE search('title:Mystery OR content:Round')
ORDER BY id
"""
// Test Suite 4: AND query behavior with NULLs
qt_test_4_1_and_with_null """
SELECT /*+SET_VAR(enable_common_expr_pushdown=true) */ id, title,
content FROM ${tableName}
- WHERE search('title:Ronald and content:biography')
+ WHERE search('title:Ronald AND content:biography')
ORDER BY id
"""
// Test Suite 5: Edge cases and complex scenarios
qt_test_5_1_empty_string """
SELECT /*+SET_VAR(enable_common_expr_pushdown=true) */ count(*) FROM
${tableName}
- WHERE search('title:"" or content:Round')
+ WHERE search('title:"" OR content:Round')
"""
qt_test_5_2_complex_nested """
SELECT /*+SET_VAR(enable_common_expr_pushdown=true) */ count(*) FROM
${tableName}
- WHERE search('(title:Ronald or title:Selma) and not (content:Round and
author:NonExistent)')
+ WHERE search('(title:Ronald OR title:Selma) AND NOT (content:Round AND
author:NonExistent)')
"""
// Test Suite 6: Performance and consistency verification
qt_test_6_1_large_or_search """
SELECT /*+SET_VAR(enable_common_expr_pushdown=true) */ count(*) FROM
${tableName}
- WHERE search('title:Ronald or title:Selma or content:Round or
content:biography or author:Smith or tags:history')
+ WHERE search('title:Ronald OR title:Selma OR content:Round OR
content:biography OR author:Smith OR tags:history')
"""
qt_test_6_1_large_or_match """
@@ -278,7 +278,7 @@ suite("test_search_vs_match_consistency") {
// Mandy/Kesha consistency checks
qt_man_pat_1_search """
SELECT /*+SET_VAR(enable_common_expr_pushdown=true) */ count(*) FROM
${mandyTable}
- WHERE search('content:ALL("Mandy Patinkin") or not
(content:ANY("Kesha"))')
+ WHERE search('content:ALL("Mandy Patinkin") OR NOT
(content:ANY("Kesha"))')
"""
qt_man_pat_1_match """
@@ -291,7 +291,7 @@ suite("test_search_vs_match_consistency") {
CASE WHEN title IS NULL THEN 'NULL' ELSE 'NOT_NULL' END AS
title_status,
CASE WHEN content IS NULL THEN 'NULL' ELSE 'NOT_NULL' END AS
content_status
FROM ${mandyTable}
- WHERE search('content:ALL("Mandy Patinkin") or not
(content:ANY("Kesha"))')
+ WHERE search('content:ALL("Mandy Patinkin") OR NOT
(content:ANY("Kesha"))')
ORDER BY id
"""
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
