[DOC] Add comprehensive documentation for COALESCE function and pre-3.1 alternatives

[earthling-shruti]

What do you want to do?

• Request a change to existing documentation
• Add new documentation
• Report a technical problem with the documentation
• Other

Tell us about your request. Provide a summary of the request.
Documentation is needed for COALESCE functionality in OpenSearch SQL/PPL in two areas:

1. OpenSearch 3.1+ COALESCE function:

Document the new native COALESCE function syntax and usage
Include common use cases and examples
Provide performance considerations
Document any limitations or restrictions

2. Pre-3.1 COALESCE alternative patterns:
   Add documentation for achieving COALESCE-like functionality in OpenSearch versions prior to 3.1. While OpenSearch 3.1+ has native COALESCE support, earlier versions can achieve similar results using nested IFNULL statements. This documentation will help users working with older OpenSearch versions handle null values across multiple fields effectively.

Example use case:

ifnull(cast(attributes.http.response.status_code as int), 
       ifnull(cast(attributes.http.status_code as int),  
              -1))

Version: List the OpenSearch version to which this issue applies, e.g. 2.14, 2.12--2.14, or all.
3.1+ for native COALESCE documentation
Pre-3.1 for alternative patterns

What other resources are available? Provide links to related issues, POCs, steps for testing, etc.

• Can be tested using any pre-3.1 OpenSearch instance with sample HTTP data
• Addresses common observability use cases where field names may vary

[aalva500-prog]

Hi @earthling-shruti is this a ducplicate of (#4196)?

[aalva500-prog]

Hi @earthling-shruti, thank you for raising this request. As you may know, the new coalesce functionality is already documented coalesce and it includes some of the enhancements you are referring in your request, but it also lacks some information regarding coalesce support in previous OpenSearch versions, as you can see below.

Current COALESCE Documentation Status

✅ Well Covered - OpenSearch 3.1+ COALESCE function:

The documentation already includes comprehensive coverage of the native COALESCE function:

1. Syntax and usage - ✅ Documented with clear usage pattern
2. Common use cases and examples - ✅ Multiple detailed examples including:
   • Basic field fallback scenarios
   • Empty string handling
   • Mixed data types with auto coercion
   • Non-existent field handling
3. Version specification - ✅ Clearly marked as "Version: 3.1.0"
4. Behavior details - ✅ Comprehensive behavior section covering:
   • Null and missing value handling
   • Empty string treatment
   • Type coercion rules
   • Return type determination

❌ Missing - Pre-3.1 COALESCE alternative patterns:
The documentation is missing the requested section for pre-3.1 OpenSearch versions. Specifically missing:

1. No documentation for nested IFNULL patterns as COALESCE alternatives
2. No examples of the pattern you mentioned: ifnull(cast(attributes.http.response.status_code as int), ifnull(cast(attributes.http.status_code as int), -1))
3. No version guidance for users on older OpenSearch versions
4. No observability use case examples for varying field names

❌ Missing - Performance considerations:
The current documentation doesn't include performance considerations for the COALESCE function.

❌ Missing - Limitations or restrictions:
While the documentation covers behavior well, it doesn't explicitly call out limitations or restrictions.

Recommendation

I would recommend enhancing the documentation with a new section covering pre-3.1 alternatives. This would help users on older OpenSearch versions achieve COALESCE-like functionality using nested IFNULL statements, particularly for the observability use cases you mentioned where HTTP status code field names may vary. Also, we can add a section for performance and limitations in the newly added coalesce function.

Please let me know if the above summary/explanation is what you are looking for and/or if we should include something else. Looking forward to your response. Thanks!

[earthling-shruti]

Apologies for missing the coalesce doc, was looking for it under https://github.com/opensearch-project/sql/tree/main/docs/user/ppl/cmd.
And yes, the recommendation makes sense.