BUG-004: Ternary Expressions Not Translated to SQL CASE WHEN

[improveTheWorld]

BUG-004: Ternary Expressions Not Translated to SQL

Summary

Ternary conditional expressions (? :) in LINQ selectors are not translated to SQL CASE WHEN statements. The expression translator throws NotSupportedException for ConditionalExpression nodes.

Error Message

System.NotSupportedException: Expression type FullConditionalExpression is not supported: 
    IIF((o.Amount > 1000), "High", "Low")

Affected Components

• DataFlow.Spark v1.2.0 (ColumnExpressionTranslator)
• DataFlow.Snowflake v1.2.0 (SnowflakeSqlTranslator)

Root Cause

Location:

• Spark: SparkQuery.cs:618 in ColumnExpressionTranslator.TranslateToColumn()
• Snowflake: SnowflakeQuery.cs in SnowflakeSqlTranslator.TranslateExpression()

The expression translators handle BinaryExpression, MemberExpression, MethodCallExpression, etc., but there's no case for ConditionalExpression (the C# representation of ? :).

// Missing case in TranslateToColumn():
case ExpressionType.Conditional:
    // Should translate to: CASE WHEN condition THEN trueValue ELSE falseValue END
    throw new NotSupportedException($"Expression type {expression.NodeType} is not supported");

Reproduction Steps

Spark

using DataFlow.Spark;

var context = Spark.Connect();
var orders = context.Read.Parquet<Order>("path/to/orders");

// This FAILS:
var results = orders
    .Select(o => new {
        o.Id,
        Category = o.Amount > 1000 ? "High" : "Low"  // Ternary
    })
    .ToList();  // Throws NotSupportedException

Snowflake

using DataFlow.Snowflake;

var context = Snowflake.Connect("connection-string");
var orders = context.Read.Table<Order>("ORDERS");

// This FAILS:
var results = orders
    .Select(o => new {
        o.Id,
        Status = o.IsActive ? "Active" : "Inactive"  // Ternary
    })
    .ToList();  // Throws NotSupportedException

Failing Tests

Project	Test
IntegrationTests	TernaryExpression_InSelect_ShouldTranslateToCaseWhen
IntegrationTests	TernaryExpression_NestedCondition_ShouldTranslate
SparkPackageAudit	BUG004_TernaryExpression_NotSupported

Current Workarounds

Workaround 1: Use Cases pattern (Spark/Snowflake)

// Instead of ternary, use Cases() for server-side routing
var highValue = orders.Where(o => o.Amount > 1000);
var lowValue = orders.Where(o => o.Amount <= 1000);

// Then merge results if needed

Workaround 2: Post-materialize transformation

var results = orders
    .Select(o => new { o.Id, o.Amount })
    .ToList()
    .Select(o => new {
        o.Id,
        Category = o.Amount > 1000 ? "High" : "Low"  // Client-side ternary
    });

Workaround 3: Use SQL directly (Snowflake)

var query = context.Read.Sql<OrderWithCategory>(
    "SELECT id, CASE WHEN amount > 1000 THEN 'High' ELSE 'Low' END as category FROM orders"
);

Proposed Fix

Add ConditionalExpression handling to both translators:

Spark (ColumnExpressionTranslator)

case ExpressionType.Conditional:
    var conditional = (ConditionalExpression)expression;
    var testColumn = TranslateToColumn(conditional.Test);
    var trueColumn = TranslateToColumn(conditional.IfTrue);
    var falseColumn = TranslateToColumn(conditional.IfFalse);
    return Functions.When(testColumn, trueColumn).Otherwise(falseColumn);

Snowflake (SnowflakeSqlTranslator)

case ExpressionType.Conditional:
    var conditional = (ConditionalExpression)expression;
    var testSql = TranslateExpression(conditional.Test);
    var trueSql = TranslateExpression(conditional.IfTrue);
    var falseSql = TranslateExpression(conditional.IfFalse);
    return $"CASE WHEN {testSql} THEN {trueSql} ELSE {falseSql} END";

Impact

• Severity: MEDIUM
• Frequency: Common (ternary is idiomatic for conditional logic)
• User Impact: Forces workarounds for simple conditional expressions

Labels

bug, spark, snowflake, expression-translator, enhancement