Output Formats - Query Result Modifiers Support

[mathiasrw]

Priority: 3-4 (Medium)
Impact: SQL-99 Compliance
Test Files: test/test269.js
Test Count: 22 tests

Problem Description

The test file contains skipped tests for various output format modifiers that control how query results are returned. These modifiers are essential for different application needs and data interchange scenarios.

Specific Test Cases

test269.js - Output Format Modifiers (22 tests)

• Lines 28-142: Various output format tests
• Tests VALUE, ROW, COLUMN, MATRIX, RECORDSET, INDEX, TEXTSTRING modifiers
• Tests different data transformation scenarios

Expected Behavior

AlaSQL should support SQL-99 standard output format modifiers:

1. VALUE: Return single scalar value
2. ROW: Return single row as array
3. COLUMN: Return single column as array
4. MATRIX: Return 2D array representation
5. RECORDSET: Return structured data with metadata
6. INDEX: Return key-value pairs
7. TEXTSTRING: Return formatted text output

Current Status

• Test Status: All skipped (it.skip)
• Error: Unknown (tests not executed)
• Root Cause: Output format modifiers not implemented in query engine

Implementation Requirements

1. Parser Support

Add output modifier syntax to src/alasqlparser.jison:

-- Set modifier globally
alasql.options.modifier = 'VALUE'

-- Or use in query (if supported)
SELECT VALUE column FROM table
SELECT ROW column_list FROM table  
SELECT COLUMN column FROM table
SELECT MATRIX * FROM table
SELECT RECORDSET * FROM table
SELECT INDEX key_column, value_column FROM table
SELECT TEXTSTRING column FROM table

2. Result Transformation Engine

• Value Extraction: Extract single values from result sets
• Array Conversion: Convert rows/columns to arrays
• Matrix Generation: Create 2D array representations
• Metadata Creation: Generate column information for RECORDSET
• Index Building: Create key-value mappings
• Text Formatting: Generate formatted text output

3. Format-Specific Logic

• VALUE: Return first value from first row
• ROW: Return first row as array of values
• COLUMN: Return all values from specified column
• MATRIX: Convert all rows to 2D array
• RECORDSET: Include column metadata with data
• INDEX: Map specified key to value
• TEXTSTRING: Format as delimited text

4. Modifier Integration

• Global Options: Support alasql.options.modifier
• Query-Level: Support modifier in query syntax
• Parameter Passing: Handle modifier parameters
• Type Conversion: Proper data type handling

5. Advanced Features

• Custom Formatters: Extensible format system
• Performance: Efficient result transformation
• Memory Usage: Optimize for large result sets
• Error Handling: Handle invalid modifier usage

SQL-99 Features Involved

• Query result formatting
• Data transformation functions
• Output specification modifiers
• Result set manipulation
• Data interchange formats

Dependencies

• AlaSQL parser (src/alasqlparser.jison)
• Query execution engine
• Result set processing
• Data type system
• Options management system

Acceptance Criteria

Basic Output Formats (test269.js)

• All 22 tests enabled and passing
• VALUE modifier for scalar results
• ROW modifier for single row arrays
• COLUMN modifier for column arrays
• MATRIX modifier for 2D arrays
• RECORDSET modifier with metadata
• INDEX modifier for key-value pairs
• TEXTSTRING modifier for formatted output
• ORDER BY with different modifiers
• Complex queries with modifiers

Implementation Strategy

Phase 1: Basic Modifiers

1. Parser Integration for modifier syntax
2. VALUE Modifier: Single value extraction
3. ROW Modifier: Single row array conversion
4. COLUMN Modifier: Column array extraction

Phase 2: Advanced Modifiers

1. MATRIX Modifier: 2D array generation
2. RECORDSET Modifier: Metadata inclusion
3. INDEX Modifier: Key-value mapping
4. TEXTSTRING Modifier: Text formatting

Phase 3: Integration and Optimization

1. Global Options support
2. Performance Optimization for large datasets
3. Error Handling for invalid usage
4. Custom Format extensibility

Test Implementation Examples

-- VALUE modifier - single value
alasql.options.modifier = 'VALUE';
var result = alasql('SELECT COUNT(*) FROM users');

-- ROW modifier - single row as array
alasql.options.modifier = 'ROW';
var result = alasql('SELECT name, age FROM users WHERE id = 1');

-- COLUMN modifier - column as array
alasql.options.modifier = 'COLUMN';
var result = alasql('SELECT name FROM users');

-- MATRIX modifier - 2D array
alasql.options.modifier = 'MATRIX';
var result = alasql('SELECT * FROM users');

-- RECORDSET modifier - with metadata
alasql.options.modifier = 'RECORDSET';
var result = alasql('SELECT * FROM users');
// Returns: {data: [...], columns: [...]}

-- INDEX modifier - key-value pairs
alasql.options.modifier = 'INDEX';
var result = alasql('SELECT id, name FROM users');
// Returns: {1: 'John', 2: 'Jane', ...}

-- TEXTSTRING modifier - formatted text
alasql.options.modifier = 'TEXTSTRING';
var result = alasql('SELECT name FROM users');
// Returns: "John\nJane\nBob\n"

Performance Considerations

• Memory Efficiency: Avoid unnecessary data copying
• Large Result Sets: Optimize for thousands of rows
• Type Conversion: Minimize type conversion overhead
• Streaming: Support streaming for TEXTSTRING format

Format Specifications

VALUE Format

// Input: [{a: 1, b: 2}, {a: 3, b: 4}]
// Query: SELECT VALUE a FROM table
// Output: 1

ROW Format

// Input: [{a: 1, b: 2}, {a: 3, b: 4}]
// Query: SELECT ROW a, b FROM table LIMIT 1
// Output: [1, 2]

COLUMN Format

// Input: [{a: 1, b: 2}, {a: 3, b: 4}]
// Query: SELECT COLUMN a FROM table
// Output: [1, 3]

MATRIX Format

// Input: [{a: 1, b: 2}, {a: 3, b: 4}]
// Query: SELECT MATRIX a, b FROM table
// Output: [[1, 2], [3, 4]]

RECORDSET Format

// Input: [{a: 1, b: 2}, {a: 3, b: 4}]
// Query: SELECT RECORDSET a, b FROM table
// Output: {
//   data: [{a: 1, b: 2}, {a: 3, b: 4}],
//   columns: [{columnid: 'a'}, {columnid: 'b'}]
// }

INDEX Format

// Input: [{a: 1, b: 2}, {a: 3, b: 4}]
// Query: SELECT INDEX a, b FROM table
// Output: {1: 2, 3: 4}

TEXTSTRING Format

// Input: [{a: 1}, {a: 3}, {a: 5}]
// Query: SELECT TEXTSTRING a FROM table
// Output: "1\n3\n5\n"

Edge Cases to Handle

• Empty Result Sets: Handle empty data gracefully
• NULL Values: Proper NULL handling in all formats
• Mixed Data Types: Handle different column types
• Large Values: Handle very long text values
• Invalid Modifiers: Error handling for unknown formats

Integration Points

• Options System: Integrate with alasql.options.modifier
• Query Engine: Modify result processing pipeline
• Type System: Handle data type conversions
• Error System: Provide clear error messages

Notes

Output format modifiers are essential for data interchange and application integration. The implementation should:

• Follow SQL-99 standards where applicable
• Provide good performance for all format types
• Handle edge cases gracefully
• Integrate well with existing AlaSQL options system