[BUG] Unsupported function doesn't fail the query when the response format set to CSV or JSON

[LantaoJin]

What is the bug?
When unsupported function is included in a query, current execution engine has to fallback to legacy SQL engine to verify whether the function comes from OpenSearch core. For example, query including percentiles is not supported in V2 and falling back to old SQL engine. If the function is also unsupported in OpenSearch core, it will throw The following method is not supported in Schema: functionNotExisted. That is the current expected behaviour.

POST /_plugins/_sql
{
  "query" : """
  SELECT functionNotExisted(taxful_total_price) FROM opensearch_dashboards_sample_data_ecommerce
  """
}

returns

{
  "error": {
    "reason": "There was internal problem at backend",
    "details": "The following method is not supported in Schema: functionNotExisted",
    "type": "UnsupportedOperationException"
  },
  "status": 500
}

However, when the response format set to CSV or JSON, the query will return results instead of throwing failure.

POST /_plugins/_sql?format=csv
{
  "query" : """
  SELECT functionNotExisted(taxful_total_price) FROM opensearch_dashboards_sample_data_ecommerce
  """
}

returns

geoip,customer_first_name,customer_phone,type,manufacturer,products,customer_full_name,order_date,customer_last_name,day_of_week_i,total_quantity,currency,taxless_total_price,total_unique_products,category,customer_id,sku,event,order_id,user,customer_gender,email,day_of_week,taxful_total_price
"{continent_name=Africa, city_name=Cairo, country_iso_code=EG, location={lon=31.3, lat=30.1}, region_name=Cairo Governorate}",Eddie,,order,"[Elitelligence, Oceanavigations]","[{tax_amount=0, taxful_price=11.99, quantity=1, taxless_price=11.99, discount_amount=0, base_unit_price=11.99, discount_percentage=0, product_name=Basic T-shirt - dark blue/white, manufacturer=Elitelligence, min_price=6.35, created_on=2016-12-26T09:28:48+00:00, unit_discount_amount=0, price=11.99, product_id=6283, base_price=11.99, _id=sold_product_584677_6283, category=Men's Clothing, sku=ZO0549605496}, {tax_amount=0, taxful_price=24.99, quantity=1, taxless_price=24.99, discount_amount=0, base_unit_price=24.99, discount_percentage=0, product_name=Sweatshirt - grey multicolor, manufacturer=Oceanavigations, min_price=11.75, created_on=2016-12-26T09:28:48+00:00, unit_discount_amount=0, price=24.99, product_id=19400, base_price=24.99, _id=sold_product_584677_19400, category=Men's Clothing, sku=ZO0299602996}]",Eddie Underwood,2024-05-27T09:28:48+00:00,Underwood,0,2,EUR,36.98,2,[Men's Clothing],38,"[ZO0549605496, ZO0299602996]",{dataset=sample_ecommerce},584677,eddie,MALE,eddie@underwood-family.zzz,Monday,36.98
"{continent_name=Asia, city_name=Dubai, country_iso_code=AE, location={lon=55.3, lat=25.3}, region_name=Dubai}",Mary,,order,"[Champion Arts, Pyramidustries]","[{tax_amount=0, taxful_price=24.99, quantity=1, taxless_price=24.99, discount_amount=0, base_unit_price=24.99, discount_percentage=0, product_name=Denim dress - black denim, manufacturer=Champion Arts, min_price=11.75, created_on=2016-12-25T21:59:02+00:00, unit_discount_amount=0, price=24.99, product_id=11238, base_price=24.99, _id=sold_product_584021_11238, category=Women's Clothing, sku=ZO0489604896}, {tax_amount=0, taxful_price=28.99, quantity=1, taxless_price=28.99, discount_amount=0, base_unit_price=28.99, discount_percentage=0, product_name=Shorts - black, manufacturer=Pyramidustries, min_price=15.65, created_on=2016-12-25T21:59:02+00:00, unit_discount_amount=0, price=28.99, product_id=20149, base_price=28.99, _id=sold_product_584021_20149, category=Women's Clothing, sku=ZO0185501855}]",Mary Bailey,2024-05-26T21:59:02+00:00,Bailey,6,2,EUR,53.98,2,[Women's Clothing],20,"[ZO0489604896, ZO0185501855]",{dataset=sample_ecommerce},584021,mary,FEMALE,mary@bailey-family.zzz,Sunday,53.98
...

How can one reproduce the bug?
Steps to reproduce the behavior:

1. Go to Dev Tools page in https://playground.opensearch.org/
2. Try and compare there two queries

POST /_plugins/_sql
{
  "query" : """
  SELECT functionNotExisted(taxful_total_price) FROM opensearch_dashboards_sample_data_ecommerce
  """
}
POST /_plugins/_sql?format=csv
{
  "query" : """
  SELECT functionNotExisted(taxful_total_price) FROM opensearch_dashboards_sample_data_ecommerce
  """
}

What is the expected behavior?
Whatever the format type set, the query should fail with readable message.

What is your host/environment?

• Version [2.14.0, 3.0.0-SNAPSHOT]
• Plugins [SQL]

Do you have any screenshots?
If applicable, add screenshots to help explain your problem.

Do you have any additional context?
When CVS or JSON format is set, CSVResultRestExecutor or ElasticDefaultRestExecutor will be used to handle the query instead of PrettyFormatRestExecutor, but only PrettyFormatRestExecutor have the logic to verify the field with a function currently.

[dblock]

Catch All Triage - 1 2 3 4 5 6