# 6.7 xpack-sql throws "Rule execution limit %d reached" **URL:** \ **Category:** Elasticsearch\ **Tags:** elastic-stack-sql\ **Created:** [April 3, 2019, 7:24am UTC](https://discuss.elastic.co/t/6-7-xpack-sql-throws-rule-execution-limit-d-reached/175122 "2019-04-03T07:24:21Z")\ **Posts on this page:** 5\ **Page:** 1
**Author:** ![kaihong](https://avatars.discourse-cdn.com/v4/letter/k/3ab097/32.png) [@kaihong](https://discuss.elastic.co/u/kaihong)\ **Post date:** [April 3, 2019, 7:24am UTC](https://discuss.elastic.co/t/6-7-xpack-sql-throws-rule-execution-limit-d-reached/175122/1 "2019-04-03T07:24:21Z")
ERROR: ```auto { "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: ```auto 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: ```auto 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. ---
**Author:** ![kaihong](https://avatars.discourse-cdn.com/v4/letter/k/3ab097/32.png) [@kaihong](https://discuss.elastic.co/u/kaihong)\ **Post date:** [April 3, 2019, 7:25am UTC](https://discuss.elastic.co/t/6-7-xpack-sql-throws-rule-execution-limit-d-reached/175122/2 "2019-04-03T07:25:51Z")
reached words limitation. ES-DSL: ```auto GET lineitem/_search { "size": 0, "query":{ "bool": { "should": [ { "bool": { "filter": [ { "nested": { "path": "part", "query": { "term": { "part.p_brand": "BRAND1" #以实际为准 } } } }, { "nested": { "path": "part", "query": { "terms": { "part.p_container": ["SM CASE", "SM BOX", "SM PACK", "SM PKG"] } } } }, { "nested": { "path": "part", "query": { "range": { "part.p_size": { "gte": 1, "lte": 5 } } } } }, { "range": { "l_quantity": { "gte": 10, #以实际为准 "lte": 20 } } }, { "terms": { "l_shipmode": ["AIR", "AIR REG"] } }, { "term": { "l_shipinstruct": "DELIVER IN PERSON" } } ] } }, { "bool": { "filter": [ { "nested": { "path": "part", "query": { "term": { "part.p_brand": "BRAND2" #以实际为准 } } } }, { "nested": { "path": "part", "query": { "terms": { "part.p_container": ["MED BAG", "MED BOX", "MED PKG", "MED PACK"] } } } }, { "nested": { "path": "part", "query": { "range": { "part.p_size": { "gte": 1, "lte": 10 } } } } }, { "range": { "l_quantity": { "gte": 20, #以实际为准 "lte": 30 } } }, { "terms": { "l_shipmode": ["AIR", "AIR REG"] } }, { "term": { "l_shipinstruct": "DELIVER IN PERSON" } } ] } }, { "bool": { "filter": [ { "nested": { "path": "part", "query": { "term": { "part.p_brand": "BRAND3" #以实际为准 } } } }, { "nested": { "path": "part", "query": { "terms": { "part.p_container": ["LG CASE", "LG BOX", "LG PACK", "LG PKG"] } } } }, { "nested": { "path": "part", "query": { "range": { "part.p_size": { "gte": 1, "lte": 15 } } } } }, { "range": { "l_quantity": { "gte": 30, #以实际为准 "lte": 40 } } }, { "terms": { "l_shipmode": ["AIR", "AIR REG"] } }, { "term": { "l_shipinstruct": "DELIVER IN PERSON" } } ] } } ], "minimum_should_match" : 1 } }, "aggs": { "revenue": { "sum": { "script": { "source": "doc['l_extendedprice'].value * (1 - doc['l_discount'].value)" } } } } } ``` ---
**Author:** ![Andrei\_Stefan](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/andrei_stefan/32/47533_2.png) [@Andrei\_Stefan](https://discuss.elastic.co/u/Andrei_Stefan)\ **Post date:** [April 4, 2019, 9:21am UTC](https://discuss.elastic.co/t/6-7-xpack-sql-throws-rule-execution-limit-d-reached/175122/3 "2019-04-04T09:21:30Z")
Hi @kaihong, Thank you for bringing up this issue. I would, also, appreciate if next time you post here to format the code, please. I edited you two message and formatted them. Regarding the issue itself, I've created a github issue to look into it: [https://github.com/elastic/elasticsearch/issues/40835](https://github.com/elastic/elasticsearch/issues/40835) ---
**Author:** ![kaihong](https://avatars.discourse-cdn.com/v4/letter/k/3ab097/32.png) [@kaihong](https://discuss.elastic.co/u/kaihong)\ **Post date:** [April 7, 2019, 3:34pm UTC](https://discuss.elastic.co/t/6-7-xpack-sql-throws-rule-execution-limit-d-reached/175122/4 "2019-04-07T15:34:40Z")
Truly sorry for this bad formatting, I'm new here and still not so familiar with this editor, thank you so much for your time. I'll follow up this issue in github.🙂 ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [May 5, 2019, 3:34pm UTC](https://discuss.elastic.co/t/6-7-xpack-sql-throws-rule-execution-limit-d-reached/175122/5 "2019-05-05T15:34:41Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.