[RFC] PPL `convert` Command

[aalva500-prog]

Problem Statement

PPL currently lacks a unified command for converting field values between different data types and formats. Users frequently need to convert data for analysis, calculations, and reporting purposes, but must rely on chaining multiple evaluation functions or writing complex expressions. Common conversion needs include:

• Converting string representations to numeric values
• Transforming timestamp formats for readability or calculations
• Converting duration strings to seconds for mathematical operations
• Normalizing memory values to consistent units
• Extracting numeric values from mixed alphanumeric strings

This creates verbose queries and increases the learning curve for users who need to remember multiple function names and their specific syntax.

Current State

Users must currently use evaluation functions within the eval command to perform conversions:

source=logs | eval balance_num = tonumber(balance) | eval time_readable = strftime(_time, "%H:%M:%S")

Limitations of the current approach:

• Requires knowledge of specific function names scattered across documentation
• No unified interface for common conversion operations
• Difficult to apply conversions to multiple fields efficiently
• No support for specialized conversions like duration-to-seconds or memory unit normalization
• Verbose syntax for simple operations

Long-Term Goals

• Provide a unified convert command that consolidates common data type conversion operations
• Simplify PPL queries by reducing the need for multiple eval statements
• Support bulk field conversions with wildcard patterns
• Enable in-place field conversion or creation of new fields via AS clause
• Maintain high performance with large datasets through optimized conversion implementations
• Ensure the solution is extensible for future conversion function additions
• Align with SQL plugin architecture and PPL command patterns
• Support 90% confidence that this addresses user conversion needs based on common log analysis patterns

Proposal

Implement a convert command in PPL with the following conversion functions:

1. Numeric Conversions

   • auto() - Automatically converts fields to numbers using best-fit heuristics
   • num() - Converts to numbers, removing non-convertible values
   • rmcomma() - Removes commas from numeric strings
   • rmunit() - Extracts leading numbers and removes trailing text

2. Time Conversions

   • ctime() - Converts UNIX timestamps to human-readable format
   • mktime() - Converts human-readable time strings to epoch time
   • mstime() - Converts MM:SS.SSS format to seconds
   • dur2sec() - Converts duration format [D+]HH:MM:SS to seconds

3. Memory Conversions

   • memk() - Converts memory values (with k/m/g suffixes) to kilobytes

4. Field Control

   • none() - Excludes specific fields from wildcard conversions

Syntax

... | convert [timeformat=<string>] <convert-function>(<field>) [AS <field>] [<convert-function>(<field>) [AS <field>]]...

Parameters

• timeformat: (Optional) Output format for time conversions (used by ctime and mktime)
• <convert-function>: One of the conversion functions listed above
• <field>: Field name to convert, supports wildcards (*)
• AS <field>: (Optional) Create new field with converted value, preserving original

Approach

Phase 1: Grammar and Parser Updates

1. Update OpenSearchPPLParser.g4

   • Add convertCommand rule to commands section
   • Define convertFunction rule with all conversion function types
   • Add convertClause for handling multiple conversions in one command
   • Support timeformat parameter and AS clause

2. Update OpenSearchPPLLexer.g4

   • Add keywords: CONVERT, AUTO, CTIME, MKTIME, MSTIME, DUR2SEC, MEMK, RMCOMMA, RMUNIT, TIMEFORMAT

Phase 2: AST Building

3. Create AstBuilder visitor method
   • Implement visitConvertCommand() in AstBuilder.java
   • Parse conversion function types and field expressions
   • Handle wildcard field patterns
   • Extract timeformat parameter and AS aliases
   • Build Convert UnresolvedPlan node with list of ConvertFunction objects

Phase 3: Calcite UDF Implementation

4. Create BaseConversionUDF abstract class

   • Extend ImplementorUDF to integrate with Calcite
   • Provide single constructor accepting conversion method name
   • Use ConversionImplementor (NotNullImplementor) for code generation
   • Implement getReturnTypeInference() returning nullable DOUBLE type
   • Define toDoubleOrNull() helper for consistent null handling

5. Implement individual UDF classes

   • Create AutoConvertFunction extending BaseConversionUDF
   • Create NumConvertFunction extending BaseConversionUDF
   • Create RmcommaConvertFunction extending BaseConversionUDF
   • Create RmunitConvertFunction extending BaseConversionUDF
   • Each class: simple constructor calling super with method name (e.g., "autoConvert")
   • Register each function in Calcite's function repository

Phase 4: Conversion Logic Implementation

6. Create ConversionUtils utility class

   • Implement static methods for each conversion function:
     • autoConvert() - Intelligent conversion with comma/unit handling
     • numConvert() - Number extraction with comma removal
     • rmcommaConvert() - Pure comma removal and parsing
     • rmunitConvert() - Leading number extraction
   • Each method signature: public static Object methodName(Object value)
   • Return Double on success, null on failure (consistent with eval+cast)

7. Conversion logic details

   • auto(): Uses ConversionStrategy.COMPREHENSIVE with fallback chain

   • num(): Uses ConversionStrategy.STANDARD with numeric focus

   • rmcomma(): ConversionStrategy.COMMA_ONLY - regex comma removal

   • rmunit(): ConversionStrategy.UNIT_ONLY - extract leading numbers

   • All use private helper methods:

     • tryParseDouble() - Safe Double.parseDouble with null handling
     • isPotentiallyConvertible() - Quick validation before conversion
     • convertStandard(), convertComprehensive(), etc. - Strategy implementations

Phase 5: Integration and Execution

8. Calcite integration flow

   • Convert command parsed → AST Convert node created
   • Calcite planner resolves UDF function calls
   • BaseConversionUDF.ConversionImplementor generates execution code
   • Generated code calls ConversionUtils.methodName()
   • Result passed through toDoubleOrNull() for type safety
   • Calcite handles expression evaluation and null propagation

9. No custom operators needed

   • Leverage Calcite's expression evaluation engine
   • Benefits from Calcite's optimization passes
   • Integrates seamlessly with other Calcite features
   • Standard UDF pattern familiar to developers

Phase 6: Testing and Documentation

10. Unit tests (ConversionUtilsTest.java)

    • Test each conversion function with various inputs
    • Verify null handling for non-convertible values
    • Test edge cases (empty strings, special characters, etc.)

11. Integration tests

    • Test command parsing and execution
    • Test with AS clause for new field creation
    • Test wildcard patterns and multiple conversions

12. Documentation (docs/user/ppl/cmd/convert.md)

    • Document each conversion function with examples
    • Explain null behavior on conversion failure
    • Provide usage patterns and best practices

Examples

Example 1: Convert all fields to numeric values

source=accounts | convert auto(*)

Input:

account_number	balance	age
1	"39,225"	"32"
2	"5,686"	"28"

Output:

account_number	balance	age
1	39225	32
2	5686	28

Note: auto() automatically converts string values to numbers where possible. "39,225" becomes 39225 by removing commas, "32" becomes 32.*

Example 2: Convert specific fields while excluding others

source=accounts | convert auto(*) none(account_id)

Input:

account_number	balance	age	account_id
1	"39,225"	"32"	"ACC001"
2	"5,686"	"28"	"ACC002"

Output:

account_number	balance	age	account_id
1	39225	32	"ACC001"
2	5686	28	"ACC002"

Note: auto() converts balance and age (removing special characters), but none(account_id) excludes account_id from conversion, preserving it as a string.*

Example 3: Convert duration to seconds

source=sessions | convert dur2sec(session_time) dur2sec(login_duration)

Input:

user_id	session_time	login_duration
user1	"00:15:30"	"00:05:45"
user2	"01:20:15"	"00:10:30"

Output:

user_id	session_time	login_duration
user1	930	345
user2	4815	630

Note: dur2sec() converts HH:MM:SS format to total seconds. 00:15:30 = (0×3600) + (15×60) + 30 = 930 seconds, 00:05:45 = 345 seconds.

Example 4: Convert with extended duration format (days + time)

source=logs | convert dur2sec(delay)

Input:

request_id	delay
req1	"1+02:10:15"
req2	"00:05:30"
req3	"3+00:02:45"

Output:

request_id	delay
req1	94215
req2	330
req3	259365

Note: dur2sec() handles extended format D+HH:MM:SS where D+ represents days. 1+02:10:15 = (1×86400) + (2×3600) + (10×60) + 15 = 94215 seconds.

Example 5: Remove units from field values

source=metrics | convert rmunit(duration)

Input:

metric_id	duration
m1	"212 sec"
m2	"450 minutes"
m3	"30 hours"

Output:

metric_id	duration
m1	212
m2	450
m3	30

Note: rmunit() extracts numbers from the beginning of values and removes trailing text. "212 sec" becomes 212, "450 minutes" becomes 450.

Example 6: Convert memory values to kilobytes

source=processes | convert memk(memory_usage)

Input:

process_id	memory_usage
p1	"512m"
p2	"1g"
p3	"256k"

Output:

process_id	memory_usage
p1	524288
p2	1048576
p3	256

Note: memk() converts memory units to kilobytes. "512m" = 512×1024 = 524288 KB, "1g" = 1×1024×1024 = 1048576 KB, "256k" = 256 KB.

Example 7: Convert UNIX timestamp to readable format

source=events | convert timeformat="%H:%M:%S" ctime(unix_time) AS readable_time

Input:

event_id	unix_time
e1	1522196414
e2	1522196465
e3	1522196823

Output:

event_id	unix_time	readable_time
e1	1522196414	"00:20:14"
e2	1522196465	"00:21:05"
e3	1522196823	"00:27:03"

Note: ctime() converts UNIX timestamps to human-readable format. timeformat="%H:%M:%S" shows only hours:minutes:seconds. Original unix_time field is preserved with AS clause.

Example 8: Convert MM:SS.SSS to seconds

source=responses | convert mstime(response_time)

Input:

request_id	response_time
r1	"02:15.500"
r2	"01:30.250"
r3	"03:45.750"

Output:

request_id	response_time
r1	135.5
r2	90.25
r3	225.75

Note: mstime() converts MM:SS.SSS to total seconds. "02:15.500" = (2×60) + 15.5 = 135.5 seconds.

Example 9: Remove commas from numeric values

source=accounts | convert rmcomma(balance)

Input:

account_id	balance
acc1	"39,225.50"
acc2	"5,686.75"
acc3	"32,838.25"

Output:

account_id	balance
acc1	39225.50
acc2	5686.75
acc3	32838.25

Note: rmcomma() removes all commas from values. "39,225.50" becomes 39225.50 for proper numeric calculations.

Example 10: Multiple conversions in one command

source=data | convert rmcomma(balance) auto(age) memk(memory) rmunit(duration)

Input:

id	balance	age	memory	duration
1	"39,225"	"32"	"512m"	"45 minutes"
2	"5,686"	"28"	"1g"	"30 seconds"

Output:

id	balance	age	memory	duration
1	39225	32	524288	45
2	5686	28	1048576	30

Note: Multiple convert functions can be used in one command. rmcomma() removes commas, auto() converts to numbers, memk() converts to KB, rmunit() removes text.

Example 11: Create new fields with AS clause

source=logs | convert dur2sec(time_elapsed) AS time_seconds | stats sum(time_seconds) by user_id

Input:

user_id	time_elapsed
user1	"01:30:45"
user2	"02:15:30"
user3	"00:45:15"

After convert:

user_id	time_elapsed	time_seconds
user1	"01:30:45"	5445
user2	"02:15:30"	8130
user3	"00:45:15"	2715

Note: dur2sec() converts HH:MM:SS to seconds, then the AS clause creates a new field time_seconds while preserving the original time_elapsed field. The converted values can then be used in aggregations like stats sum().

Alternative

Temporary workarounds users can employ:

1. Continue using eval with tonumber(), tostring(), and other existing functions
2. Pre-process data during ingestion to normalize formats
3. Create custom UDFs for specific conversion needs

However, these alternatives:

• Don't provide the unified interface that reduces cognitive load
• Require more verbose queries
• Don't cover all conversion scenarios (e.g., dur2sec, memk)
• Don't support efficient bulk conversions with wildcards

Implementation Discussion

1. Phased Implementation: Should we implement all 10 functions in the initial release, or prioritize based on usage patterns?

   • Recommendation: Start with numeric conversions (auto, num, rmcomma, rmunit) and time conversions (ctime, dur2sec) as these cover 80% of use cases

2. Error Handling: How should the command handle conversion failures?

   • Option A: Silently leave unconvertible values unchanged
   • Option B: Set failed conversions to null (matches eval behavior) ✓
   • Option C: Provide warning logs for troubleshooting
   • Recommendation: Option B for consistency with existing PPL behavior

3. Performance Optimization: For wildcard conversions, should we:

   • Analyze all field values to determine convertibility before applying?
   • Apply conversion attempts and roll back if too many failures occur?
   • Recommendation: Use sampling approach - analyze first N rows to determine field convertibility

4. Timeformat Default: Should we use locale-aware defaults or fixed format?

   • Recommendation: Use ISO 8601 format (%Y-%m-%d %H:%M:%S) as default for international compatibility

5. Extensibility: Should we design an interface for adding custom conversion functions?

   • Recommendation: No interface needed ( BaseConversionUDF abstract class is sufficient)

6. Wildcard Performance: How to optimize wildcard matching with large field counts?

   • Recommendation: Build field pattern cache at planning time, apply conversions in streaming fashion

7. AS Clause Scoping: When using AS clause, should original field remain accessible downstream?

   • Recommendation: Yes, both original and converted fields should be available unless explicitly dropped

[coderabbitai]

📝 CodeRabbit Plan Mode

Generate an implementation plan and prompts that you can use with your favorite coding agent.

Examples

• Example 1
• Example 2

---

🔗 Related PRs

#4605 - issue #4514 tonumber function as part of roadmap #4287 [merged]
#4754 - Feature addtotals and addcoltotals [merged]
#4839 - Support mvfind eval function [merged]
#4841 - [BugFix] Fix Memory Exhaustion for Multiple Filtering Operations in PPL [merged]
#4856 - Support mvmap eval function [merged]

👤 Suggested Assignees

• asifabashar
• ahkcs
• RyanL1997

---

🧪 Issue enrichment is currently in open beta.

You can configure auto-planning by selecting labels in the issue_enrichment configuration.

To disable automatic issue enrichment, add the following to your .coderabbit.yaml:

issue_enrichment:
  auto_enrich:
    enabled: false

💬 Have feedback or questions? Drop into our discord!

[aalva500-prog]

@coderabbitai plan

[coderabbitai]

Coding Plan

Approach

Implement a unified convert command in PPL that consolidates common data type and format conversions (numeric, time, memory) with support for wildcard field patterns and AS clause, following existing eval command patterns while adding convert-specific semantics.

Observations

The OpenSearch SQL codebase uses a layered architecture: ANTLR grammars define PPL syntax (language-grammar/), AstBuilder converts parse trees to UnresolvedPlan nodes (ppl/src/main/java/), logical planning resolves field references and creates LogicalPlan nodes (core/src/main/java/org/opensearch/sql/planner/logical/), and physical operators execute queries using expression evaluation (core/src/main/java/org/opensearch/sql/planner/physical/). Extensive conversion utilities already exist (TypeCastOperators, ToNumberFunction, DateTimeFunctions) that can be leveraged. Tests follow a three-layer pattern: grammar parsing tests, Calcite unit tests, and integration tests extending PPLIntegTestCase.

Assumptions

Assumption 1: Should convert command have its own logical plan node or reuse LogicalEval?

Options Considered:

• Reuse LogicalEval completely (treat convert as syntactic sugar for eval)
• Create dedicated UnresolvedConvert and LogicalConvert nodes but reuse EvalOperator for execution

Chosen Option: 2

Rationale: Creating separate logical plan nodes provides semantic clarity and enables convert-specific optimizations (wildcard expansion, function validation) while still leveraging eval's proven execution model.

Assumption 2: How should wildcard field expansion be handled?

Options Considered:

• Expand wildcards during grammar parsing
• Expand wildcards at logical plan resolution stage
• Expand wildcards during physical execution

Chosen Option: 2

Rationale: Expanding at logical plan resolution follows the pattern used by fields command and allows validation before execution while keeping grammar rules simple.

Assumption 3: How should the none() exclusion function work with wildcards?

Options Considered:

• Process none() as a special marker that filters wildcard expansion results
• Implement none() as an actual conversion function that returns original value

Chosen Option: 1

Rationale: Processing none() as a filter during wildcard expansion is more efficient and matches user expectations (excluded fields simply aren't converted).

💡 User Tips

Regenerate the plan with different choices with @coderabbitai <feedback>.

Implementation Steps

Phase 1: Grammar Definition

Define the convert command syntax in ANTLR grammar files following the established pattern of other PPL commands.

Task 1: Add convert command grammar rules

• Add convertCommand rule in OpenSearchPPLParser.g4 following the pattern of evalCommand and dedupCommand
• Define syntax: convertCommand : CONVERT (convertOptions)? convertClause (convertClause)*
• Create convertOptions rule for timeformat parameter: convertOptions : TIMEFORMAT EQUAL stringLiteral
• Create convertClause rule: convertClause : convertFunction LT_PRTHS wcFieldList RT_PRTHS (AS fieldExpression)?
• Create convertFunction rule as alternatives: convertFunction : AUTO | NUM | RMCOMMA | RMUNIT | CTIME | MKTIME | MSTIME | DUR2SEC | MEMK | NONE
• Add convertCommand to the commands rule list

Task 2: Add lexer tokens for convert command

• Add keyword tokens to OpenSearchPPLLexer.g4: CONVERT, AUTO, NUM, RMCOMMA, RMUNIT, CTIME, MKTIME, MSTIME, DUR2SEC, MEMK, NONE, TIMEFORMAT
• Follow existing case-insensitive token pattern used by other commands

🤖 Prompt for AI agents

Phase 1 implements the ANTLR grammar rules for the convert command in PPL. The
goal is to define the syntax that supports conversion functions with wildcards,
AS clauses, and timeformat options.

In OpenSearchPPLParser.g4:
- Add convertCommand rule with syntax: CONVERT (convertOptions)? convertClause
(convertClause)*
- Define convertOptions rule for timeformat parameter: TIMEFORMAT EQUAL
stringLiteral
- Define convertClause rule: convertFunction LT_PRTHS wcFieldList RT_PRTHS (AS
fieldExpression)?
- Define convertFunction rule with alternatives: AUTO | NUM | RMCOMMA | RMUNIT |
CTIME | MKTIME | MSTIME | DUR2SEC | MEMK | NONE
- Add convertCommand to the commands rule list

In OpenSearchPPLLexer.g4:
- Add keyword tokens: CONVERT, AUTO, NUM, RMCOMMA, RMUNIT, CTIME, MKTIME,
MSTIME, DUR2SEC, MEMK, NONE, TIMEFORMAT
- Follow the existing case-insensitive token pattern

Phase 2: AST Construction

Implement parse tree to AST conversion following the visitor pattern used by AstBuilder.

Task 1: Create UnresolvedConvert plan node

• Create new class UnresolvedConvert in ppl/src/main/java/org/opensearch/sql/ppl/domain/
• Store list of conversion clauses, each containing: conversion function type, field expressions (possibly wildcards), optional AS alias
• Store optional timeformat parameter
• Follow the structure pattern from other unresolved plans

Task 2: Implement AstBuilder visitor method

• Add visitConvertCommand() method in ppl/src/main/java/org/opensearch/sql/ppl/parser/AstBuilder.java
• Extract conversion clauses from context using stream mapping pattern (similar to visitEvalCommand)
• Parse conversion function type from convertFunction context
• Extract field expressions from wcFieldList (supporting wildcards)
• Extract optional AS alias and timeformat parameter
• Construct UnresolvedConvert node with extracted components

🤖 Prompt for AI agents

Phase 2 converts the parse tree into an AST node following the visitor pattern.
The goal is to create an UnresolvedConvert plan node and populate it with parsed
conversion clauses.

Create UnresolvedConvert class in
ppl/src/main/java/org/opensearch/sql/ppl/domain/:
- Store list of conversion clauses (conversion function type, field expressions
with wildcards, optional AS alias)
- Store optional timeformat parameter
- Follow structure pattern from other unresolved plans

Implement visitConvertCommand in AstBuilder.java:
- Extract conversion clauses using stream mapping (similar to visitEvalCommand)
- Parse conversion function type from convertFunction context
- Extract field expressions from wcFieldList
- Extract optional AS alias and timeformat parameter
- Construct and return UnresolvedConvert node

Phase 3: Logical Plan Resolution

Transform unresolved plan to logical plan with wildcard expansion and field validation.

Task 1: Create LogicalConvert plan node

• Create LogicalConvert class in core/src/main/java/org/opensearch/sql/planner/logical/
• Extend LogicalPlan and implement the visitor pattern
• Store resolved conversion expressions as List<Pair<ReferenceExpression, Expression>> (same structure as LogicalEval)
• Include logic to expand wildcards by matching against input schema
• Handle none() exclusions by filtering matched fields from wildcard expansion
• Transform AS clauses into appropriate field reference bindings

Task 2: Implement plan resolution logic

• Add resolve logic that converts UnresolvedConvert to LogicalConvert
• For each conversion clause, resolve field expressions against input schema
• Expand wildcard patterns to concrete field list based on schema
• Apply none() exclusions to wildcard expansion results
• Create conversion expression for each target field
• Handle AS clause by creating new field reference or modifying existing

Task 3: Integrate with planner

• Update planner to recognize and handle LogicalConvert nodes
• Add visitor method in LogicalPlanNodeVisitor interface
• Ensure LogicalConvert fits into the plan tree between source and downstream operations

🤖 Prompt for AI agents

Phase 3 transforms the unresolved plan to a logical plan with wildcard expansion
and validation. The goal is to create LogicalConvert nodes with resolved field
references and expanded wildcards.

Create LogicalConvert class in
core/src/main/java/org/opensearch/sql/planner/logical/:
- Extend LogicalPlan and implement visitor pattern
- Store resolved conversion expressions as List<Pair<ReferenceExpression,
Expression>>
- Implement wildcard expansion logic by matching against input schema
- Handle none() exclusions by filtering matched fields
- Transform AS clauses into field reference bindings

Implement plan resolution logic:
- Convert UnresolvedConvert to LogicalConvert
- Resolve field expressions against input schema
- Expand wildcard patterns to concrete field list
- Apply none() exclusions
- Create conversion expressions for each target field
- Handle AS clause appropriately

Integrate with planner:
- Add visitor method in LogicalPlanNodeVisitor interface
- Ensure LogicalConvert fits into plan tree between source and downstream
operations

Phase 4: Conversion Function Implementation

Implement conversion functions as Expression subclasses, leveraging existing utilities where possible.

Task 1: Implement numeric conversion functions

• Create Expression implementations in core/src/main/java/org/opensearch/sql/expression/function/convert/
• AutoFunction: Leverage TypeCastOperators to try casting to numeric types, handle comma removal similar to rmcomma
• NumFunction: Wrap ToNumberFunction.toNumber() with base 10 parameter
• RmCommaFunction: Implement regex-based comma removal from string values, then attempt numeric conversion
• RmUnitFunction: Parse leading numeric characters and discard trailing text using pattern matching
• Follow the function registration pattern from ToNumberFunction and ToStringFunction

Task 2: Implement time conversion functions

• Create time conversion Expression implementations in core/src/main/java/org/opensearch/sql/expression/function/convert/
• CtimeFunction: Wrap DateTimeFunctions utilities to convert unix timestamp to formatted string, respect timeformat parameter
• MktimeFunction: Wrap DateTimeFunctions parsing utilities to convert formatted time string to unix timestamp
• MstimeFunction: Parse MM:SS.SSS format and calculate total seconds as decimal
• Dur2SecFunction: Parse [D+]HH:MM:SS format, calculate total seconds including optional days component
• Reuse DateTimeFormatterUtil for format specifier handling

Task 3: Implement memory and utility functions

• Create remaining Expression implementations in core/src/main/java/org/opensearch/sql/expression/function/convert/
• MemkFunction: Parse memory values with k/m/g/t suffixes, convert to kilobytes using appropriate multipliers (1024 for binary units)
• NoneFunction: Marker function used during wildcard expansion, can be no-op in execution (fields already filtered)
• Follow consistent error handling pattern: return null on conversion failure, similar to eval behavior

Task 4: Register conversion functions

• Update function repository to register conversion functions
• Add function signatures and metadata
• Ensure functions are discoverable during expression resolution

🤖 Prompt for AI agents

Phase 4 implements the conversion functions as Expression subclasses. The goal
is to create all conversion functions (numeric, time, memory) leveraging
existing utilities where possible.

Create numeric conversion functions in
core/src/main/java/org/opensearch/sql/expression/function/convert/:
- AutoFunction: Use TypeCastOperators to cast to numeric types, handle comma
removal
- NumFunction: Wrap ToNumberFunction.toNumber() with base 10
- RmCommaFunction: Remove commas using regex, then convert to numeric
- RmUnitFunction: Parse leading numeric characters, discard trailing text

Create time conversion functions:
- CtimeFunction: Convert unix timestamp to formatted string using
DateTimeFunctions, respect timeformat parameter
- MktimeFunction: Convert formatted time string to unix timestamp using
DateTimeFunctions
- MstimeFunction: Parse MM:SS.SSS format, calculate total seconds
- Dur2SecFunction: Parse [D+]HH:MM:SS format, calculate total seconds including
days
- Reuse DateTimeFormatterUtil for format handling

Create memory and utility functions:
- MemkFunction: Parse memory values with k/m/g/t suffixes, convert to kilobytes
using 1024 multipliers
- NoneFunction: Marker function for wildcard expansion (no-op in execution)
- Return null on conversion failures

Register all conversion functions:
- Update function repository with function signatures and metadata
- Ensure discoverability during expression resolution

Phase 5: Physical Execution

Transform logical convert plan to executable expressions following the eval execution pattern.

Task 1: Create physical plan transformation

• Add logic to transform LogicalConvert to physical plan representation
• Convert each conversion clause to an expression that can be evaluated
• For in-place conversions (no AS clause), create assignment expressions that replace field values
• For AS clause conversions, create new field bindings while preserving originals
• The resulting structure should match eval's expression list format

Task 2: Implement execution using eval pattern

• Leverage EvalOperator's execution model by transforming convert expressions to eval-compatible format
• Ensure left-to-right evaluation order (later conversions can reference earlier ones)
• Use environment-based scope extension to allow field chaining
• Handle type checking and conversion errors gracefully (null on failure)
• Maintain tuple structure with converted field values

🤖 Prompt for AI agents

Phase 5 transforms the logical plan into executable physical operations. The
goal is to leverage EvalOperator's execution model for convert command
execution.

Create physical plan transformation:
- Transform LogicalConvert to physical plan representation
- Convert each conversion clause to evaluable expression
- For in-place conversions (no AS clause), create assignment expressions that
replace field values
- For AS clause conversions, create new field bindings and preserve original
fields
- Match eval's expression list format

Implement execution using eval pattern:
- Leverage EvalOperator's execution model
- Ensure left-to-right evaluation order for field chaining
- Use environment-based scope extension
- Handle conversion errors gracefully (return null on failure)
- Maintain tuple structure with converted field values

Phase 6: Testing and Validation

Implement comprehensive tests following the established test patterns.

Task 1: Create grammar parsing tests

• Add tests in ppl/src/test/java/org/opensearch/sql/ppl/antlr/
• Create parameterized tests for each conversion function type using PPLSyntaxParser
• Test various syntax combinations: single conversions, multiple conversions, with/without AS clause, with/without timeformat
• Test wildcard patterns and none() exclusions
• Follow the pattern from NowLikeFunctionParserTest.java

Task 2: Create unit tests for conversion functions

• Add unit tests for each conversion function Expression class
• Test conversion logic with valid inputs
• Test error handling with invalid inputs (should return null)
• Test edge cases: empty strings, special characters, boundary values
• Test timeformat parameter handling for ctime and mktime
• Follow patterns from existing function tests

Task 3: Create integration tests

• Add integration tests in integ-test/src/test/java/org/opensearch/sql/ppl/
• Create ConvertCommandIT.java extending PPLIntegTestCase
• Test each example scenario from the ticket
• Test wildcard expansion with test indices
• Test AS clause behavior (field preservation vs. replacement)
• Test multiple conversions in single command
• Use verifySchema() and verifyDataRows() from MatcherUtils for validation
• Follow the pattern from CalcitePPLEvalMaxMinFunctionIT.java

Task 4: Create Calcite-based logical plan tests

• Add tests in ppl/src/test/java/org/opensearch/sql/ppl/calcite/ extending CalcitePPLAbstractTest
• Test that convert commands parse to correct logical plan structure
• Test wildcard expansion produces expected field list
• Test none() exclusions filter correctly
• Use assertEqual() method to compare PPL query strings to expected AST structures

🤖 Prompt for AI agents

Phase 6 implements comprehensive testing at all levels. The goal is to validate
grammar parsing, conversion function logic, and end-to-end integration.

Create grammar parsing tests in ppl/src/test/java/org/opensearch/sql/ppl/antlr/:
- Add parameterized tests for each conversion function using PPLSyntaxParser
- Test single conversions, multiple conversions, with/without AS clause,
with/without timeformat
- Test wildcard patterns and none() exclusions
- Follow pattern from NowLikeFunctionParserTest.java

Create unit tests for conversion functions:
- Test each conversion function Expression class
- Test valid inputs, invalid inputs (should return null), and edge cases
- Test timeformat parameter handling for ctime and mktime
- Follow existing function test patterns

Create integration tests in integ-test/src/test/java/org/opensearch/sql/ppl/:
- Create ConvertCommandIT.java extending PPLIntegTestCase
- Test example scenarios from ticket
- Test wildcard expansion, AS clause behavior, multiple conversions
- Use verifySchema() and verifyDataRows() for validation
- Follow pattern from CalcitePPLEvalMaxMinFunctionIT.java

Create Calcite-based logical plan tests in
ppl/src/test/java/org/opensearch/sql/ppl/calcite/:
- Extend CalcitePPLAbstractTest
- Test convert commands parse to correct logical plan structure
- Test wildcard expansion and none() exclusions
- Use assertEqual() to compare PPL queries to expected AST structures

---

🚀 Next Steps

🤖 All AI agent prompts combined

Task: 1

Phase 1 implements the ANTLR grammar rules for the convert command in PPL. The
goal is to define the syntax that supports conversion functions with wildcards,
AS clauses, and timeformat options.

In OpenSearchPPLParser.g4:
- Add convertCommand rule with syntax: CONVERT (convertOptions)? convertClause
(convertClause)*
- Define convertOptions rule for timeformat parameter: TIMEFORMAT EQUAL
stringLiteral
- Define convertClause rule: convertFunction LT_PRTHS wcFieldList RT_PRTHS (AS
fieldExpression)?
- Define convertFunction rule with alternatives: AUTO | NUM | RMCOMMA | RMUNIT |
CTIME | MKTIME | MSTIME | DUR2SEC | MEMK | NONE
- Add convertCommand to the commands rule list

In OpenSearchPPLLexer.g4:
- Add keyword tokens: CONVERT, AUTO, NUM, RMCOMMA, RMUNIT, CTIME, MKTIME,
MSTIME, DUR2SEC, MEMK, NONE, TIMEFORMAT
- Follow the existing case-insensitive token pattern
===============================================================================

Task: 2

Phase 2 converts the parse tree into an AST node following the visitor pattern.
The goal is to create an UnresolvedConvert plan node and populate it with parsed
conversion clauses.

Create UnresolvedConvert class in
ppl/src/main/java/org/opensearch/sql/ppl/domain/:
- Store list of conversion clauses (conversion function type, field expressions
with wildcards, optional AS alias)
- Store optional timeformat parameter
- Follow structure pattern from other unresolved plans

Implement visitConvertCommand in AstBuilder.java:
- Extract conversion clauses using stream mapping (similar to visitEvalCommand)
- Parse conversion function type from convertFunction context
- Extract field expressions from wcFieldList
- Extract optional AS alias and timeformat parameter
- Construct and return UnresolvedConvert node
===============================================================================

Task: 3

Phase 3 transforms the unresolved plan to a logical plan with wildcard expansion
and validation. The goal is to create LogicalConvert nodes with resolved field
references and expanded wildcards.

Create LogicalConvert class in
core/src/main/java/org/opensearch/sql/planner/logical/:
- Extend LogicalPlan and implement visitor pattern
- Store resolved conversion expressions as List<Pair<ReferenceExpression,
Expression>>
- Implement wildcard expansion logic by matching against input schema
- Handle none() exclusions by filtering matched fields
- Transform AS clauses into field reference bindings

Implement plan resolution logic:
- Convert UnresolvedConvert to LogicalConvert
- Resolve field expressions against input schema
- Expand wildcard patterns to concrete field list
- Apply none() exclusions
- Create conversion expressions for each target field
- Handle AS clause appropriately

Integrate with planner:
- Add visitor method in LogicalPlanNodeVisitor interface
- Ensure LogicalConvert fits into plan tree between source and downstream
operations
===============================================================================

Task: 4

Phase 4 implements the conversion functions as Expression subclasses. The goal
is to create all conversion functions (numeric, time, memory) leveraging
existing utilities where possible.

Create numeric conversion functions in
core/src/main/java/org/opensearch/sql/expression/function/convert/:
- AutoFunction: Use TypeCastOperators to cast to numeric types, handle comma
removal
- NumFunction: Wrap ToNumberFunction.toNumber() with base 10
- RmCommaFunction: Remove commas using regex, then convert to numeric
- RmUnitFunction: Parse leading numeric characters, discard trailing text

Create time conversion functions:
- CtimeFunction: Convert unix timestamp to formatted string using
DateTimeFunctions, respect timeformat parameter
- MktimeFunction: Convert formatted time string to unix timestamp using
DateTimeFunctions
- MstimeFunction: Parse MM:SS.SSS format, calculate total seconds
- Dur2SecFunction: Parse [D+]HH:MM:SS format, calculate total seconds including
days
- Reuse DateTimeFormatterUtil for format handling

Create memory and utility functions:
- MemkFunction: Parse memory values with k/m/g/t suffixes, convert to kilobytes
using 1024 multipliers
- NoneFunction: Marker function for wildcard expansion (no-op in execution)
- Return null on conversion failures

Register all conversion functions:
- Update function repository with function signatures and metadata
- Ensure discoverability during expression resolution
===============================================================================

Task: 5

Phase 5 transforms the logical plan into executable physical operations. The
goal is to leverage EvalOperator's execution model for convert command
execution.

Create physical plan transformation:
- Transform LogicalConvert to physical plan representation
- Convert each conversion clause to evaluable expression
- For in-place conversions (no AS clause), create assignment expressions that
replace field values
- For AS clause conversions, create new field bindings and preserve original
fields
- Match eval's expression list format

Implement execution using eval pattern:
- Leverage EvalOperator's execution model
- Ensure left-to-right evaluation order for field chaining
- Use environment-based scope extension
- Handle conversion errors gracefully (return null on failure)
- Maintain tuple structure with converted field values
===============================================================================

Task: 6

Phase 6 implements comprehensive testing at all levels. The goal is to validate
grammar parsing, conversion function logic, and end-to-end integration.

Create grammar parsing tests in ppl/src/test/java/org/opensearch/sql/ppl/antlr/:
- Add parameterized tests for each conversion function using PPLSyntaxParser
- Test single conversions, multiple conversions, with/without AS clause,
with/without timeformat
- Test wildcard patterns and none() exclusions
- Follow pattern from NowLikeFunctionParserTest.java

Create unit tests for conversion functions:
- Test each conversion function Expression class
- Test valid inputs, invalid inputs (should return null), and edge cases
- Test timeformat parameter handling for ctime and mktime
- Follow existing function test patterns

Create integration tests in integ-test/src/test/java/org/opensearch/sql/ppl/:
- Create ConvertCommandIT.java extending PPLIntegTestCase
- Test example scenarios from ticket
- Test wildcard expansion, AS clause behavior, multiple conversions
- Use verifySchema() and verifyDataRows() for validation
- Follow pattern from CalcitePPLEvalMaxMinFunctionIT.java

Create Calcite-based logical plan tests in
ppl/src/test/java/org/opensearch/sql/ppl/calcite/:
- Extend CalcitePPLAbstractTest
- Test convert commands parse to correct logical plan structure
- Test wildcard expansion and none() exclusions
- Use assertEqual() to compare PPL queries to expected AST structures

💡 Iterate on the plan with: @coderabbitai <feedback>

Example Feedback
- @coderabbitai You can skip phase 3. Add a simple unit test case for phase 2.
- @coderabbitai For assumption 1 go ahead with option 3 and replan.

---

💬 Have feedback or questions? Drop into our discord!