|
| 1 | +// Licensed to the Apache Software Foundation (ASF) under one |
| 2 | +// or more contributor license agreements. See the NOTICE file |
| 3 | +// distributed with this work for additional information |
| 4 | +// regarding copyright ownership. The ASF licenses this file |
| 5 | +// to you under the Apache License, Version 2.0 (the |
| 6 | +// "License"); you may not use this file except in compliance |
| 7 | +// with the License. You may obtain a copy of the License at |
| 8 | +// |
| 9 | +// http://www.apache.org/licenses/LICENSE-2.0 |
| 10 | +// |
| 11 | +// Unless required by applicable law or agreed to in writing, |
| 12 | +// software distributed under the License is distributed on an |
| 13 | +// "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY |
| 14 | +// KIND, either express or implied. See the License for the |
| 15 | +// specific language governing permissions and limitations |
| 16 | +// under the License. |
| 17 | + |
| 18 | +suite("test_search_score_topn_predicates", "p0") { |
| 19 | + sql "DROP TABLE IF EXISTS test_search_score_topn_predicates" |
| 20 | + |
| 21 | + sql """ |
| 22 | + CREATE TABLE test_search_score_topn_predicates ( |
| 23 | + id INT, |
| 24 | + status VARCHAR(20), |
| 25 | + plain_status VARCHAR(20), |
| 26 | + category VARCHAR(20), |
| 27 | + title TEXT, |
| 28 | + body TEXT, |
| 29 | + INDEX idx_status (status) USING INVERTED, |
| 30 | + INDEX idx_category (category) USING INVERTED, |
| 31 | + INDEX idx_title (title) USING INVERTED PROPERTIES("parser" = "english", "support_phrase" = "true"), |
| 32 | + INDEX idx_body (body) USING INVERTED PROPERTIES("parser" = "english", "support_phrase" = "true") |
| 33 | + ) ENGINE=OLAP |
| 34 | + DUPLICATE KEY(id) |
| 35 | + DISTRIBUTED BY HASH(id) BUCKETS 1 |
| 36 | + PROPERTIES ( |
| 37 | + "replication_allocation" = "tag.location.default: 1", |
| 38 | + "disable_auto_compaction" = "true" |
| 39 | + ) |
| 40 | + """ |
| 41 | + |
| 42 | + sql """ |
| 43 | + INSERT INTO test_search_score_topn_predicates VALUES |
| 44 | + (1, 'drop', 'drop', 'plain', 'apple apple apple apple apple apple apple apple apple apple apple apple', 'alpha'), |
| 45 | + (2, 'keep', 'keep', 'plain', 'apple', 'alpha'), |
| 46 | + (3, 'keep', 'keep', 'plain', 'apple apple apple apple apple', 'alpha'), |
| 47 | + (4, 'keep', 'keep', 'plain', 'apple apple apple', 'beta beta beta'), |
| 48 | + (5, 'drop', 'drop', 'plain', 'banana', 'beta beta beta beta beta beta beta beta beta beta beta beta'), |
| 49 | + (6, 'keep', 'keep', 'plain', 'pear', 'beta beta beta beta beta'), |
| 50 | + (7, 'keep', 'keep', 'special', 'cherry cherry cherry cherry', 'gamma'), |
| 51 | + (8, 'drop', 'drop', 'special', 'cherry cherry cherry cherry cherry cherry cherry cherry cherry cherry', 'gamma') |
| 52 | + """ |
| 53 | + |
| 54 | + sql "sync" |
| 55 | + sql "set enable_nereids_planner = true" |
| 56 | + sql "set enable_fallback_to_original_planner = false" |
| 57 | + sql "set enable_segment_limit_pushdown = true" |
| 58 | + sql "set enable_inverted_index_query_cache = false" |
| 59 | + |
| 60 | + qt_single_search """ |
| 61 | + SELECT id FROM ( |
| 62 | + SELECT id, score() AS s |
| 63 | + FROM test_search_score_topn_predicates |
| 64 | + WHERE search('title:apple') |
| 65 | + ORDER BY s DESC |
| 66 | + LIMIT 1 |
| 67 | + ) t |
| 68 | + ORDER BY id |
| 69 | + """ |
| 70 | + |
| 71 | + qt_search_with_equal_predicate """ |
| 72 | + SELECT id FROM ( |
| 73 | + SELECT id, score() AS s |
| 74 | + FROM test_search_score_topn_predicates |
| 75 | + WHERE search('title:apple') AND status = 'keep' |
| 76 | + ORDER BY s DESC |
| 77 | + LIMIT 1 |
| 78 | + ) t |
| 79 | + ORDER BY id |
| 80 | + """ |
| 81 | + |
| 82 | + qt_search_with_plain_equal_predicate """ |
| 83 | + SELECT id FROM ( |
| 84 | + SELECT id, score() AS s |
| 85 | + FROM test_search_score_topn_predicates |
| 86 | + WHERE search('title:apple') AND plain_status = 'keep' |
| 87 | + ORDER BY s DESC |
| 88 | + LIMIT 1 |
| 89 | + ) t |
| 90 | + ORDER BY id |
| 91 | + """ |
| 92 | + |
| 93 | + qt_search_with_equal_limit_two """ |
| 94 | + SELECT id FROM ( |
| 95 | + SELECT id, score() AS s |
| 96 | + FROM test_search_score_topn_predicates |
| 97 | + WHERE search('title:apple') AND status = 'keep' |
| 98 | + ORDER BY s DESC |
| 99 | + LIMIT 2 |
| 100 | + ) t |
| 101 | + ORDER BY id |
| 102 | + """ |
| 103 | + |
| 104 | + qt_search_with_equal_offset """ |
| 105 | + SELECT id FROM ( |
| 106 | + SELECT id, score() AS s |
| 107 | + FROM test_search_score_topn_predicates |
| 108 | + WHERE search('title:apple') AND status = 'keep' |
| 109 | + ORDER BY s DESC |
| 110 | + LIMIT 1 OFFSET 1 |
| 111 | + ) t |
| 112 | + ORDER BY id |
| 113 | + """ |
| 114 | + |
| 115 | + qt_search_with_match_predicate """ |
| 116 | + SELECT id FROM ( |
| 117 | + SELECT id, score() AS s |
| 118 | + FROM test_search_score_topn_predicates |
| 119 | + WHERE search('title:apple') AND status MATCH 'keep' |
| 120 | + ORDER BY s DESC |
| 121 | + LIMIT 1 |
| 122 | + ) t |
| 123 | + ORDER BY id |
| 124 | + """ |
| 125 | + |
| 126 | + qt_search_with_range_predicate """ |
| 127 | + SELECT id FROM ( |
| 128 | + SELECT id, score() AS s |
| 129 | + FROM test_search_score_topn_predicates |
| 130 | + WHERE search('title:apple') AND id > 1 |
| 131 | + ORDER BY s DESC |
| 132 | + LIMIT 1 |
| 133 | + ) t |
| 134 | + ORDER BY id |
| 135 | + """ |
| 136 | + |
| 137 | + qt_multiple_search_predicates """ |
| 138 | + SELECT id FROM ( |
| 139 | + SELECT id, score() AS s |
| 140 | + FROM test_search_score_topn_predicates |
| 141 | + WHERE search('title:apple') AND search('body:beta') |
| 142 | + ORDER BY s DESC |
| 143 | + LIMIT 1 |
| 144 | + ) t |
| 145 | + ORDER BY id |
| 146 | + """ |
| 147 | + |
| 148 | + qt_search_with_score_range_only """ |
| 149 | + SELECT id FROM ( |
| 150 | + SELECT id, score() AS s |
| 151 | + FROM test_search_score_topn_predicates |
| 152 | + WHERE search('title:apple') AND score() > 0 |
| 153 | + ORDER BY s DESC |
| 154 | + LIMIT 2 |
| 155 | + ) t |
| 156 | + ORDER BY id |
| 157 | + """ |
| 158 | + |
| 159 | + qt_search_with_score_range_and_other_predicate """ |
| 160 | + SELECT id FROM ( |
| 161 | + SELECT id, score() AS s |
| 162 | + FROM test_search_score_topn_predicates |
| 163 | + WHERE search('title:apple') AND score() > 0 AND status = 'keep' |
| 164 | + ORDER BY s DESC |
| 165 | + LIMIT 1 |
| 166 | + ) t |
| 167 | + ORDER BY id |
| 168 | + """ |
| 169 | + |
| 170 | + qt_nested_search_with_other_predicate """ |
| 171 | + SELECT id FROM ( |
| 172 | + SELECT id, score() AS s |
| 173 | + FROM test_search_score_topn_predicates |
| 174 | + WHERE (search('title:cherry') OR category MATCH 'special') AND status = 'keep' |
| 175 | + ORDER BY s DESC |
| 176 | + LIMIT 1 |
| 177 | + ) t |
| 178 | + ORDER BY id |
| 179 | + """ |
| 180 | + |
| 181 | + qt_not_search_with_other_search """ |
| 182 | + SELECT id FROM ( |
| 183 | + SELECT id, score() AS s |
| 184 | + FROM test_search_score_topn_predicates |
| 185 | + WHERE NOT search('title:apple') AND search('body:beta') AND status = 'keep' |
| 186 | + ORDER BY s DESC |
| 187 | + LIMIT 1 |
| 188 | + ) t |
| 189 | + ORDER BY id |
| 190 | + """ |
| 191 | + |
| 192 | + // limit + offset overflows the long range. score() must report the standard TopN |
| 193 | + // overflow error instead of skipping score pushdown and reporting a score() usage error. |
| 194 | + test { |
| 195 | + sql """ |
| 196 | + SELECT id, score() AS s |
| 197 | + FROM test_search_score_topn_predicates |
| 198 | + WHERE search('title:apple') |
| 199 | + ORDER BY s DESC |
| 200 | + LIMIT 9223372036854775807 OFFSET 9223372036854775807 |
| 201 | + """ |
| 202 | + exception "limit + offset overflows the long range" |
| 203 | + } |
| 204 | +} |
0 commit comments