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 a3d1080e87d [test](search) add more regression case (#56704)
a3d1080e87d is described below
commit a3d1080e87d5d7f09e3414172c6c0529cc3ca25a
Author: Jack <[email protected]>
AuthorDate: Sun Oct 5 21:10:20 2025 +0800
[test](search) add more regression case (#56704)
---
.../test_complex_or_null_semantics.groovy | 240 +++++++++++++++++++++
.../test_cross_field_or_with_null.groovy | 192 +++++++++++++++++
.../test_search_or_null_semantics.groovy | 195 +++++++++++++++++
3 files changed, 627 insertions(+)
diff --git
a/regression-test/suites/inverted_index_p0/test_complex_or_null_semantics.groovy
b/regression-test/suites/inverted_index_p0/test_complex_or_null_semantics.groovy
new file mode 100644
index 00000000000..d922a00160e
--- /dev/null
+++
b/regression-test/suites/inverted_index_p0/test_complex_or_null_semantics.groovy
@@ -0,0 +1,240 @@
+// 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_complex_or_null_semantics") {
+ // This regression test verifies the fix for complex nested OR queries
with NULL values
+ // Based on the real-world bug where MATCH and SEARCH returned different
results
+
+ def tableName = "test_complex_or_null_table"
+
+ sql "DROP TABLE IF EXISTS ${tableName}"
+
+ sql """
+ CREATE TABLE ${tableName} (
+ id INT,
+ title TEXT,
+ content TEXT,
+ author TEXT,
+ category TEXT,
+ INDEX idx_title (title) USING INVERTED PROPERTIES("parser" =
"english"),
+ INDEX idx_content (content) USING INVERTED PROPERTIES("parser" =
"english"),
+ INDEX idx_author (author) USING INVERTED PROPERTIES("parser" =
"english"),
+ INDEX idx_category (category) USING INVERTED
+ ) ENGINE=OLAP
+ DUPLICATE KEY(id)
+ DISTRIBUTED BY HASH(id) BUCKETS 3
+ PROPERTIES (
+ "replication_allocation" = "tag.location.default: 1"
+ )
+ """
+
+ // Insert test data simulating Wikipedia-like articles
+ sql """ INSERT INTO ${tableName} VALUES
+ -- Rows 1-15: title = "Philosophy", content = NULL
+ (1, 'Philosophy', NULL, 'Author A', 'Education'),
+ (2, 'Philosophy', NULL, 'Author B', 'Science'),
+ (3, 'Philosophy', NULL, 'Author C', 'History'),
+ (4, 'Philosophy', NULL, 'Author D', 'Culture'),
+ (5, 'Philosophy', NULL, 'Author E', 'Education'),
+ (6, 'Philosophy', NULL, 'Author F', 'Science'),
+ (7, 'Philosophy', NULL, 'Author G', 'History'),
+ (8, 'Philosophy', NULL, 'Author H', 'Education'),
+ (9, 'Philosophy', NULL, 'Author I', 'Culture'),
+ (10, 'Philosophy', NULL, 'Author J', 'Education'),
+ (11, 'Philosophy', NULL, 'Author K', 'Science'),
+ (12, 'Philosophy', NULL, 'Author L', 'History'),
+ (13, 'Philosophy', NULL, 'Author M', 'Education'),
+ (14, 'Philosophy', NULL, 'Author N', 'Culture'),
+ (15, 'Philosophy', NULL, 'Author O', 'Education'),
+ -- Row 16: Different article
+ (16, 'Technology News', 'Disney+ Hotstar streaming platform', 'Tech
Writer', 'Technology'),
+ -- Rows 17-30: Various other articles with content
+ (17, 'Science Article', 'President of the United States speech',
'Political Writer', 'Politics'),
+ (18, 'History Piece', 'List of presidents of India', 'Historian',
'History'),
+ (19, 'Movie Review', 'Braveheart film analysis', 'Critic',
'Entertainment'),
+ (20, 'Tech Article', 'University of Southern California research',
'Academic', 'Education'),
+ (21, 'Biography', 'Elliot Page interview', 'Entertainment Writer',
'Celebrity'),
+ (22, 'History Document', 'Archduke Franz Ferdinand of Austria',
'Historian', 'History'),
+ (23, 'Film Review', 'Renée Zellweger performance', 'Critic', 'Movies'),
+ (24, 'Sports', 'Randy Savage wrestling career', 'Sports Writer',
'Sports'),
+ (25, 'Random', 'Cindy story', 'Random Writer', 'General'),
+ (26, 'Wildlife', 'Bindi Irwin conservation work', 'Nature Writer',
'Environment'),
+ (27, 'Job Portal', 'Indeed job listings', 'HR Writer', 'Business'),
+ (28, 'Short Story', 'Boy adventure tale', 'Fiction Writer',
'Literature'),
+ (29, 'Generic', 'Article about of preposition', 'Grammar Writer',
'Language'),
+ (30, 'Misc', 'Random content', 'Anonymous', 'Misc')
+ """
+
+ sql "SET enable_common_expr_pushdown = true"
+
+ // Test 1: The core bug scenario - cross-field OR with one field NULL
+ // title:ALL("Philosophy") OR content:ALL("Disney+ Hotstar")
+ // Should match rows 1-16 (15 with title match despite NULL content, 1
with content match)
+
+ def test1_match = sql """
+ SELECT COUNT(*) FROM ${tableName}
+ WHERE title MATCH_ALL 'Philosophy' OR content MATCH_ALL 'Disney+
Hotstar'
+ """
+
+ def test1_search = sql """
+ SELECT COUNT(*) FROM ${tableName}
+ WHERE SEARCH('title:ALL(Philosophy) OR content:ALL("Disney+ Hotstar")')
+ """
+
+ assertEquals(16, test1_match[0][0], "MATCH should return 16 rows")
+ assertEquals(16, test1_search[0][0], "SEARCH should return 16 rows")
+ assertEquals(test1_match[0][0], test1_search[0][0], "MATCH and SEARCH
should return same count")
+
+ logger.info("Test 1 PASSED: Cross-field OR with NULL - MATCH and SEARCH
consistent")
+
+ // Test 2: Complex nested query similar to original bug report
+ def test2_match = sql """
+ SELECT COUNT(*) FROM ${tableName}
+ WHERE content MATCH_ANY 'President of the United States'
+ OR NOT (
+ content MATCH 'Braveheart'
+ AND (
+ NOT content MATCH_ALL 'List of presidents of India'
+ AND NOT (
+ title MATCH_ALL 'Philosophy'
+ OR content MATCH_ALL 'Disney+ Hotstar'
+ )
+ )
+ )
+ """
+
+ def test2_search = sql """
+ SELECT COUNT(*) FROM ${tableName}
+ WHERE SEARCH('
+ content:ANY("President of the United States")
+ OR NOT (
+ content:Braveheart
+ AND (
+ NOT content:ALL("List of presidents of India")
+ AND NOT (
+ title:ALL(Philosophy)
+ OR content:ALL("Disney+ Hotstar")
+ )
+ )
+ )
+ ')
+ """
+
+ assertEquals(test2_match[0][0], test2_search[0][0],
+ "Complex nested query: MATCH and SEARCH should return same count")
+
+ logger.info("Test 2 PASSED: Complex nested query - MATCH and SEARCH
consistent (count: ${test2_match[0][0]})")
+
+ // Test 3: Verify the 15 critical rows are included
+ def test3 = sql """
+ SELECT COUNT(*) FROM ${tableName}
+ WHERE title MATCH_ALL 'Philosophy'
+ AND content IS NULL
+ AND SEARCH('title:ALL(Philosophy) OR content:ALL("Disney+ Hotstar")')
+ """
+
+ assertEquals(15, test3[0][0], "The 15 rows with NULL content should be
included")
+
+ logger.info("Test 3 PASSED: 15 NULL content rows correctly included")
+
+ // Test 4: Three-way OR with different NULL patterns
+ def test4_match = sql """
+ SELECT COUNT(*) FROM ${tableName}
+ WHERE title MATCH_ALL 'Philosophy'
+ OR content MATCH_ALL 'Disney+ Hotstar'
+ OR author MATCH_ANY 'Political'
+ """
+
+ def test4_search = sql """
+ SELECT COUNT(*) FROM ${tableName}
+ WHERE SEARCH('title:ALL(Philosophy) OR content:ALL("Disney+ Hotstar")
OR author:ANY(Political)')
+ """
+
+ assertEquals(test4_match[0][0], test4_search[0][0], "Three-way OR should
be consistent")
+
+ logger.info("Test 4 PASSED: Three-way OR with different NULL patterns")
+
+ // Test 5: OR within AND with NULL
+ def test5_match = sql """
+ SELECT COUNT(*) FROM ${tableName}
+ WHERE category MATCH 'Education'
+ AND (title MATCH_ALL 'Philosophy' OR content MATCH_ALL 'Disney')
+ """
+
+ def test5_search = sql """
+ SELECT COUNT(*) FROM ${tableName}
+ WHERE SEARCH('category:Education AND (title:ALL(Philosophy) OR
content:ALL(Disney))')
+ """
+
+ assertEquals(test5_match[0][0], test5_search[0][0], "OR within AND should
be consistent")
+
+ logger.info("Test 5 PASSED: OR within AND with NULL")
+
+ // Test 6: Deeply nested OR with multiple NULL fields
+ def test6_match = sql """
+ SELECT COUNT(*) FROM ${tableName}
+ WHERE (
+ (title MATCH_ALL 'Philosophy' OR content MATCH_ALL 'Disney')
+ OR (author MATCH_ANY 'Writer' OR category MATCH 'Technology')
+ )
+ """
+
+ def test6_search = sql """
+ SELECT COUNT(*) FROM ${tableName}
+ WHERE SEARCH('
+ (title:ALL(Philosophy) OR content:ALL(Disney))
+ OR (author:ANY(Writer) OR category:Technology)
+ ')
+ """
+
+ assertEquals(test6_match[0][0], test6_search[0][0], "Deeply nested OR
should be consistent")
+
+ logger.info("Test 6 PASSED: Deeply nested OR with multiple NULL fields
(count: ${test6_match[0][0]})")
+
+ // Test 7: Verify SQL three-valued logic truth table for OR
+ // TRUE OR NULL = TRUE
+ def test7a = sql """
+ SELECT COUNT(*) FROM ${tableName}
+ WHERE (title MATCH_ALL 'Philosophy' AND content IS NULL)
+ AND (title MATCH_ALL 'Philosophy' OR content MATCH_ALL 'Disney+
Hotstar')
+ """
+ assertEquals(15, test7a[0][0], "TRUE OR NULL should be TRUE (15 rows)")
+
+ // FALSE OR NULL = NULL (excluded from WHERE clause)
+ def test7b = sql """
+ SELECT COUNT(*) FROM ${tableName}
+ WHERE (title NOT MATCH_ALL 'Philosophy' AND content IS NULL)
+ AND (title MATCH_ALL 'Philosophy' OR content MATCH_ALL 'Disney+
Hotstar')
+ """
+ assertEquals(0, test7b[0][0], "FALSE OR NULL should be NULL (0 rows)")
+
+ logger.info("Test 7 PASSED: SQL three-valued logic truth table verified")
+
+ // Test 8: Performance - ensure both use inverted index
+ sql "SET enable_profile = true"
+
+ sql """
+ SELECT COUNT(*) FROM ${tableName}
+ WHERE SEARCH('title:ALL(Philosophy) OR content:ALL("Disney+ Hotstar")')
+ """
+
+ // Just ensure query completes without error (actual profile checking
would be more complex)
+ logger.info("Test 8 PASSED: Query execution successful with profiling
enabled")
+
+ sql "SET enable_profile = false"
+ logger.info("All tests PASSED: Complex OR NULL semantics work correctly")
+}
diff --git
a/regression-test/suites/inverted_index_p0/test_cross_field_or_with_null.groovy
b/regression-test/suites/inverted_index_p0/test_cross_field_or_with_null.groovy
new file mode 100644
index 00000000000..55b317e4329
--- /dev/null
+++
b/regression-test/suites/inverted_index_p0/test_cross_field_or_with_null.groovy
@@ -0,0 +1,192 @@
+// 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_cross_field_or_with_null") {
+ // This test verifies the fix for the bug where OR queries with NULL values
+ // incorrectly filtered out rows where "TRUE OR NULL" should evaluate to
TRUE
+
+ def tableName = "test_cross_field_or_null_table"
+
+ sql "DROP TABLE IF EXISTS ${tableName}"
+
+ // Create table with inverted indexes on multiple fields
+ sql """
+ CREATE TABLE ${tableName} (
+ id INT,
+ title VARCHAR(200),
+ content TEXT,
+ category VARCHAR(100),
+ INDEX idx_title (title) USING INVERTED PROPERTIES("parser" =
"english"),
+ INDEX idx_content (content) USING INVERTED PROPERTIES("parser" =
"english"),
+ INDEX idx_category (category) USING INVERTED
+ ) ENGINE=OLAP
+ DUPLICATE KEY(id)
+ DISTRIBUTED BY HASH(id) BUCKETS 3
+ PROPERTIES (
+ "replication_allocation" = "tag.location.default: 1"
+ )
+ """
+
+ // Insert test data with specific NULL patterns
+ // Rows 1-15: title matches "Philosophy", content is NULL
+ // Row 16: both title and content have values
+ // Rows 17-20: content is NULL, title does not match
+ sql """ INSERT INTO ${tableName} VALUES
+ (1, 'Philosophy 101', NULL, 'Education'),
+ (2, 'Ancient Philosophy', NULL, 'History'),
+ (3, 'Modern Philosophy', NULL, 'Education'),
+ (4, 'Eastern Philosophy', NULL, 'Culture'),
+ (5, 'Western Philosophy', NULL, 'Culture'),
+ (6, 'Philosophy of Mind', NULL, 'Science'),
+ (7, 'Philosophy of Science', NULL, 'Science'),
+ (8, 'Philosophy Basics', NULL, 'Education'),
+ (9, 'Greek Philosophy', NULL, 'History'),
+ (10, 'Medieval Philosophy', NULL, 'History'),
+ (11, 'Renaissance Philosophy', NULL, 'History'),
+ (12, 'Contemporary Philosophy', NULL, 'Education'),
+ (13, 'Philosophy and Logic', NULL, 'Science'),
+ (14, 'Philosophy Fundamentals', NULL, 'Education'),
+ (15, 'Introduction to Philosophy', NULL, 'Education'),
+ (16, 'Science Today', 'Disney+ Hotstar streaming service',
'Technology'),
+ (17, 'Random Article', NULL, 'News'),
+ (18, 'Another Topic', NULL, 'General'),
+ (19, 'Sample Entry', NULL, 'Misc'),
+ (20, 'Test Data', NULL, 'Test')
+ """
+
+ // Test 1: Simple cross-field OR with NULL
+ // title MATCH "Philosophy" OR content MATCH "Disney+ Hotstar"
+ // Expected: Rows 1-16 (rows 1-15 have title match, row 16 has content
match)
+ sql "SET enable_common_expr_pushdown = true"
+
+ def result1_match = sql """
+ SELECT COUNT(*) FROM ${tableName}
+ WHERE title MATCH_ALL 'Philosophy' OR content MATCH_ALL 'Disney+
Hotstar'
+ """
+
+ def result1_search = sql """
+ SELECT COUNT(*) FROM ${tableName}
+ WHERE SEARCH('title:ALL(Philosophy) OR content:ALL("Disney+ Hotstar")')
+ """
+
+ // Both should return 16 rows
+ assertEquals(16, result1_match[0][0])
+ assertEquals(16, result1_search[0][0])
+
+ logger.info("Test 1 passed: Simple cross-field OR with NULL")
+
+ // Test 2: Verify that the 15 NULL content rows with matching title are
included
+ def result2 = sql """
+ SELECT COUNT(*) FROM ${tableName}
+ WHERE title MATCH_ALL 'Philosophy'
+ AND content IS NULL
+ AND (title MATCH_ALL 'Philosophy' OR content MATCH_ALL 'Disney+
Hotstar')
+ """
+
+ assertEquals(15, result2[0][0])
+ logger.info("Test 2 passed: Verified 15 rows with NULL content are
included")
+
+ // Test 3: Complex nested OR query
+ // (title MATCH "Philosophy" OR (content MATCH "Disney" OR content MATCH
"streaming"))
+ def result3_match = sql """
+ SELECT COUNT(*) FROM ${tableName}
+ WHERE title MATCH_ALL 'Philosophy'
+ OR (content MATCH_ALL 'Disney' OR content MATCH_ALL 'streaming')
+ """
+
+ def result3_search = sql """
+ SELECT COUNT(*) FROM ${tableName}
+ WHERE SEARCH('title:ALL(Philosophy) OR (content:ALL(Disney) OR
content:ALL(streaming))')
+ """
+
+ assertEquals(result3_match[0][0], result3_search[0][0])
+ logger.info("Test 3 passed: Complex nested OR query")
+
+ // Test 4: NOT with cross-field OR
+ // NOT (title MATCH "Philosophy" OR content MATCH "Disney+ Hotstar")
+ // Rows 1-16: title matches or content matches -> OR = TRUE -> NOT TRUE =
FALSE (excluded)
+ // Rows 17-20: title doesn't match (FALSE) and content is NULL -> FALSE OR
NULL = NULL -> NOT NULL = NULL (excluded)
+ // Expected: 0 rows (all rows are either TRUE or NULL in the OR, none are
FALSE)
+ def result4_match = sql """
+ SELECT COUNT(*) FROM ${tableName}
+ WHERE NOT (title MATCH_ALL 'Philosophy' OR content MATCH_ALL 'Disney+
Hotstar')
+ """
+
+ def result4_search = sql """
+ SELECT COUNT(*) FROM ${tableName}
+ WHERE SEARCH('NOT (title:ALL(Philosophy) OR content:ALL("Disney+
Hotstar"))')
+ """
+
+ assertEquals(0, result4_match[0][0]) // All rows excluded due to NULL
semantics
+ assertEquals(result4_match[0][0], result4_search[0][0])
+ logger.info("Test 4 passed: NOT with cross-field OR (NULL semantics
correctly exclude rows)")
+
+ // Test 5: AND with cross-field OR containing NULL
+ // category = "Education" AND (title MATCH "Philosophy" OR content MATCH
"Disney")
+ def result5_match = sql """
+ SELECT COUNT(*) FROM ${tableName}
+ WHERE category MATCH 'Education'
+ AND (title MATCH_ALL 'Philosophy' OR content MATCH_ALL 'Disney')
+ """
+
+ def result5_search = sql """
+ SELECT COUNT(*) FROM ${tableName}
+ WHERE SEARCH('category:Education AND (title:ALL(Philosophy) OR
content:ALL(Disney))')
+ """
+
+ assertEquals(result5_match[0][0], result5_search[0][0])
+ logger.info("Test 5 passed: AND with cross-field OR containing NULL")
+
+ // Test 6: Test with pushdown disabled (should also work correctly)
+ sql "SET enable_common_expr_pushdown = false"
+
+ def result6_nopush = sql """
+ SELECT COUNT(*) FROM ${tableName}
+ WHERE title MATCH_ALL 'Philosophy' OR content MATCH_ALL 'Disney+
Hotstar'
+ """
+
+ assertEquals(16, result6_nopush[0][0])
+ logger.info("Test 6 passed: Query works correctly without pushdown")
+
+ // Test 7: Multiple OR conditions with different NULL patterns
+ sql "SET enable_common_expr_pushdown = true"
+
+ def result7_match = sql """
+ SELECT COUNT(*) FROM ${tableName}
+ WHERE title MATCH_ALL 'Philosophy'
+ OR content MATCH_ALL 'Disney+ Hotstar'
+ OR category MATCH 'Technology'
+ """
+
+ def result7_search = sql """
+ SELECT COUNT(*) FROM ${tableName}
+ WHERE SEARCH('title:ALL(Philosophy) OR content:ALL("Disney+ Hotstar")
OR category:Technology')
+ """
+
+ assertEquals(result7_match[0][0], result7_search[0][0])
+ logger.info("Test 7 passed: Multiple OR conditions with different NULL
patterns")
+
+ // Test 8: Verify individual field queries work correctly
+ def result8_title = sql "SELECT COUNT(*) FROM ${tableName} WHERE title
MATCH_ALL 'Philosophy'"
+ def result8_content_null = sql "SELECT COUNT(*) FROM ${tableName} WHERE
content IS NULL"
+
+ assertEquals(15, result8_title[0][0])
+ assertEquals(19, result8_content_null[0][0]) // Rows 1-15, 17-20
+ logger.info("Test 8 passed: Individual field queries return expected
counts")
+
+ sql "DROP TABLE IF EXISTS ${tableName}"
+}
diff --git
a/regression-test/suites/inverted_index_p0/test_search_or_null_semantics.groovy
b/regression-test/suites/inverted_index_p0/test_search_or_null_semantics.groovy
new file mode 100644
index 00000000000..c9852bccfb5
--- /dev/null
+++
b/regression-test/suites/inverted_index_p0/test_search_or_null_semantics.groovy
@@ -0,0 +1,195 @@
+// 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_or_null_semantics") {
+ // This test verifies the fix for the bug in
UnionScorer::_collect_child_nulls()
+ // in buffered_union_scorer.cpp where NULL bitmaps were incorrectly
combined using OR
+ // instead of AND for SEARCH function queries
+ //
+ // Bug location:
be/src/olap/rowset/segment_v2/inverted_index/query_v2/buffered_union_scorer.cpp:151-164
+ // The bug caused rows with (TRUE OR NULL) to be incorrectly filtered out
+
+ def tableName = "test_search_or_null_table"
+
+ sql "DROP TABLE IF EXISTS ${tableName}"
+
+ sql """
+ CREATE TABLE ${tableName} (
+ id INT,
+ title TEXT,
+ content TEXT,
+ author TEXT,
+ category TEXT,
+ INDEX idx_title (title) USING INVERTED PROPERTIES("parser" =
"english"),
+ INDEX idx_content (content) USING INVERTED PROPERTIES("parser" =
"english"),
+ INDEX idx_author (author) USING INVERTED PROPERTIES("parser" =
"english"),
+ INDEX idx_category (category) USING INVERTED
+ ) ENGINE=OLAP
+ DUPLICATE KEY(id)
+ DISTRIBUTED BY HASH(id) BUCKETS 3
+ PROPERTIES (
+ "replication_allocation" = "tag.location.default: 1"
+ )
+ """
+
+ // Insert test data simulating Wikipedia-like articles
+ sql """ INSERT INTO ${tableName} VALUES
+ -- Rows 1-15: title = "Philosophy", content = NULL (TRUE OR NULL =
TRUE)
+ (1, 'Philosophy', NULL, 'Author A', 'Education'),
+ (2, 'Philosophy', NULL, 'Author B', 'Science'),
+ (3, 'Philosophy', NULL, 'Author C', 'History'),
+ (4, 'Philosophy', NULL, 'Author D', 'Culture'),
+ (5, 'Philosophy', NULL, 'Author E', 'Education'),
+ (6, 'Philosophy', NULL, 'Author F', 'Science'),
+ (7, 'Philosophy', NULL, 'Author G', 'History'),
+ (8, 'Philosophy', NULL, 'Author H', 'Education'),
+ (9, 'Philosophy', NULL, 'Author I', 'Culture'),
+ (10, 'Philosophy', NULL, 'Author J', 'Education'),
+ (11, 'Philosophy', NULL, 'Author K', 'Science'),
+ (12, 'Philosophy', NULL, 'Author L', 'History'),
+ (13, 'Philosophy', NULL, 'Author M', 'Education'),
+ (14, 'Philosophy', NULL, 'Author N', 'Culture'),
+ (15, 'Philosophy', NULL, 'Author O', 'Education'),
+ -- Row 16: Different article (FALSE OR TRUE = TRUE)
+ (16, 'Technology News', 'Disney+ Hotstar streaming platform', 'Tech
Writer', 'Technology'),
+ -- Rows 17-20: Various other articles (FALSE OR NULL = NULL, excluded)
+ (17, 'Science Article', NULL, 'Political Writer', 'Politics'),
+ (18, 'History Piece', NULL, 'Historian', 'History'),
+ (19, 'Movie Review', NULL, 'Critic', 'Entertainment'),
+ (20, 'Tech Article', NULL, 'Academic', 'Education')
+ """
+
+ sql "SET enable_common_expr_pushdown = true"
+
+ // Test 1: The core bug scenario - cross-field OR with one field NULL
+ // Before fix: SEARCH returned 1 row (only row 16, lost 15 rows with NULL
content)
+ // After fix: SEARCH returns 16 rows (rows 1-16)
+ def test1 = sql """
+ SELECT /*+SET_VAR(enable_common_expr_pushdown=true) */ COUNT(*) FROM
${tableName}
+ WHERE SEARCH('title:ALL(Philosophy) OR content:ALL("Disney+ Hotstar")')
+ """
+
+ assertEquals(16, test1[0][0], "SEARCH should return 16 rows (15 with title
match + 1 with content match)")
+ logger.info("Test 1 PASSED: Cross-field OR with NULL - SEARCH returns 16
rows")
+
+ // Test 2: Verify the 15 critical rows are included
+ def test2 = sql """
+ SELECT /*+SET_VAR(enable_common_expr_pushdown=true) */ COUNT(*) FROM
${tableName}
+ WHERE title MATCH_ALL 'Philosophy'
+ AND content IS NULL
+ AND SEARCH('title:ALL(Philosophy) OR content:ALL("Disney+ Hotstar")')
+ """
+
+ assertEquals(15, test2[0][0], "The 15 rows with NULL content should be
included (TRUE OR NULL = TRUE)")
+ logger.info("Test 2 PASSED: 15 NULL content rows correctly included")
+
+ // Test 3: Three-way OR with different NULL patterns
+ def test3 = sql """
+ SELECT /*+SET_VAR(enable_common_expr_pushdown=true) */ COUNT(*) FROM
${tableName}
+ WHERE SEARCH('title:ALL(Philosophy) OR content:ALL("Disney+ Hotstar")
OR author:ANY(Political)')
+ """
+
+ assertEquals(17, test3[0][0], "Three-way OR should return 17 rows (15
title + 1 content + 1 author)")
+ logger.info("Test 3 PASSED: Three-way OR with different NULL patterns")
+
+ // Test 4: OR within AND with NULL
+ def test4 = sql """
+ SELECT /*+SET_VAR(enable_common_expr_pushdown=true) */ COUNT(*) FROM
${tableName}
+ WHERE SEARCH('category:Education AND (title:ALL(Philosophy) OR
content:ALL(Disney))')
+ """
+
+ def test4_count = test4[0][0]
+ assertTrue(test4_count >= 6, "Should return at least 6 rows (rows with
Education category and title match)")
+ logger.info("Test 4 PASSED: OR within AND with NULL (returned
${test4_count} rows)")
+
+ // Test 5: Deeply nested OR with multiple NULL fields
+ def test5 = sql """
+ SELECT /*+SET_VAR(enable_common_expr_pushdown=true) */ COUNT(*) FROM
${tableName}
+ WHERE SEARCH('
+ (title:ALL(Philosophy) OR content:ALL(Disney))
+ OR (author:ANY(Writer) OR category:Technology)
+ ')
+ """
+
+ def test5_count = test5[0][0]
+ assertTrue(test5_count >= 16, "Deeply nested OR should return at least 16
rows")
+ logger.info("Test 5 PASSED: Deeply nested OR with multiple NULL fields
(returned ${test5_count} rows)")
+
+ // Test 6: Verify SQL three-valued logic truth table for OR
+ // TRUE OR NULL = TRUE
+ def test6a = sql """
+ SELECT /*+SET_VAR(enable_common_expr_pushdown=true) */ COUNT(*) FROM
${tableName}
+ WHERE (title MATCH_ALL 'Philosophy' AND content IS NULL)
+ """
+ assertEquals(15, test6a[0][0], "Should have 15 rows with TRUE OR NULL
pattern")
+
+ def test6b = sql """
+ SELECT /*+SET_VAR(enable_common_expr_pushdown=true) */ COUNT(*) FROM
${tableName}
+ WHERE (title MATCH_ALL 'Philosophy' AND content IS NULL)
+ AND SEARCH('title:ALL(Philosophy) OR content:ALL("Disney+ Hotstar")')
+ """
+ assertEquals(15, test6b[0][0], "TRUE OR NULL should be TRUE (15 rows
included)")
+ logger.info("Test 6 PASSED: SQL three-valued logic - TRUE OR NULL = TRUE")
+
+ // Test 7: Complex nested query similar to original bug report
+ def test7 = sql """
+ SELECT /*+SET_VAR(enable_common_expr_pushdown=true) */ COUNT(*) FROM
${tableName}
+ WHERE SEARCH('
+ title:ALL(Philosophy)
+ OR NOT (
+ content:ALL(History)
+ AND (
+ NOT category:Education
+ AND NOT (
+ title:ALL(Science)
+ OR author:ANY(Writer)
+ )
+ )
+ )
+ ')
+ """
+
+ def test7_count = test7[0][0]
+ assertTrue(test7_count >= 15, "Complex nested query should include the 15
Philosophy rows")
+ logger.info("Test 7 PASSED: Complex nested query (returned ${test7_count}
rows)")
+
+ // Test 8: NOT with OR and NULL (SQL three-valued logic)
+ // Rows 1-16: OR = TRUE -> NOT TRUE = FALSE (excluded)
+ // Rows 17-20: OR = NULL -> NOT NULL = NULL (excluded)
+ def test8 = sql """
+ SELECT /*+SET_VAR(enable_common_expr_pushdown=true) */ COUNT(*) FROM
${tableName}
+ WHERE SEARCH('NOT (title:ALL(Philosophy) OR content:ALL("Disney+
Hotstar"))')
+ """
+
+ assertEquals(0, test8[0][0], "NOT OR should exclude all rows due to NULL
semantics")
+ logger.info("Test 8 PASSED: NOT OR with NULL correctly excludes all rows")
+
+ // Test 9: Verify SEARCH works with and without pushdown
+ sql "SET enable_common_expr_pushdown = false"
+
+ def test9 = sql """
+ SELECT /*+SET_VAR(enable_common_expr_pushdown=true) */ COUNT(*) FROM
${tableName}
+ WHERE SEARCH('title:ALL(Philosophy) OR content:ALL("Disney+ Hotstar")')
+ """
+
+ assertEquals(16, test9[0][0], "SEARCH should return 16 rows even without
pushdown")
+ logger.info("Test 9 PASSED: SEARCH works correctly without pushdown")
+
+ sql "DROP TABLE IF EXISTS ${tableName}"
+
+ logger.info("All tests PASSED: SEARCH OR NULL semantics work correctly")
+}
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
