ERROR:
{
"error": {
"root_cause": [
{
"type": "rule_execution_exception",
"reason": "Rule execution limit %d reached"
}
],
"type": "rule_execution_exception",
"reason": "Rule execution limit %d reached"
},
"status": 500
}
My query:
GET _xpack/sql
{
"query": """
select
sum(l_extendedprice * (1 - l_discount) ) as revenue
from
lineitem_join_test
where
(
part.p_brand = 'BRAND1'
and part.p_container = 'SM CASE'
and l_quantity >= 10 and l_quantity <= 20
and part.p_size between 1 and 5
and l_shipmode = 'AIR'
and l_shipinstruct = 'DELIVER IN PERSON'
)
or
(
part.p_brand = 'BRAND2'
and part.p_container = 'MED BAG'
and l_quantity >= 10 and l_quantity <= 30
and part.p_size between 1 and 10
and l_shipmode = 'AIR'
and l_shipinstruct = 'DELIVER IN PERSON'
)
or
(
part.p_brand = 'BRAND3'
and part.p_container = 'LG CASE'
and l_quantity >= 20 and l_quantity <= 30
and part.p_size between 1 and 15
and l_shipmode = 'AIR'
and l_shipinstruct = 'DELIVER IN PERSON'
)
"""
}
There is no actual docs in this index, just an empty one.
- index mapping:
PUT lineitem_join_test
{
"aliases": {},
"mappings": {
"_doc": {
"properties": {
"l_comment": {
"type": "text"
},
"l_commitdate": {
"type": "date",
"format": "yyyy-MM-dd"
},
"l_discount": {
"type": "scaled_float",
"scaling_factor": 100
},
"l_extendedprice": {
"type": "scaled_float",
"scaling_factor": 100
},
"l_linenumber": {
"type": "keyword"
},
"l_linestatus": {
"type": "keyword"
},
"l_orderkey": {
"type": "keyword"
},
"l_partkey": {
"type": "keyword"
},
"l_quantity": {
"type": "long"
},
"l_receiptdate": {
"type": "date",
"format": "yyyy-MM-dd"
},
"l_returnflag": {
"type": "keyword"
},
"l_shipdate": {
"type": "date",
"format": "yyyy-MM-dd"
},
"l_shipinstruct": {
"type": "keyword"
},
"l_shipmode": {
"type": "keyword"
},
"l_suppkey": {
"type": "keyword"
},
"l_tax": {
"type": "scaled_float",
"scaling_factor": 100
},
"part": {
"type": "nested",
"properties": {
"p_brand": {
"type": "keyword"
},
"p_comment": {
"type": "text"
},
"p_container": {
"type": "keyword"
},
"p_mfgr": {
"type": "keyword"
},
"p_name": {
"type": "keyword"
},
"p_partkey": {
"type": "keyword"
},
"p_retailprice": {
"type": "scaled_float",
"scaling_factor": 100
},
"p_size": {
"type": "long"
},
"p_type": {
"type": "keyword"
}
}
}
}
}
},
"settings": {
"index": {
"refresh_interval": "-1",
"number_of_shards": "128",
"translog": {
"flush_threshold_size": "2gb",
"sync_interval": "100s",
"durability": "async"
},
"merge": {
"scheduler": {
"max_thread_count": "15"
},
"policy": {
"max_merged_segment": "256M"
}
},
"number_of_replicas": "0"
}
}
}
I remember in 6.3.2 version, this sql query will directly cause OOM in coordinating node. Now in
6.7.0, sql is already a GA feature, but this query still cannot run normally.
If translated into dsl, it's just a simple OR query.
