udf-convert-to-sql

# Convert UDF to Spark SQL ## Workflow - [ ] Step 1: Implement the SQL expression - [ ] Step 2: Fill in the comparison test and iterate - [ ] Step 3: Run judge subagent if requested - [ ] Step 4: Review conversion **Before making any edits, create a visible TODO checklist for every workflow step in this skill and keep it updated.** Do not produce a final answer until every required checklist item is marked complete. ## Prerequisites - Project directory from Step 1 (udf-gen-test) with passing unit test Derive `<CamelName>` and `<snake_name>` from the UDF class name. > **Note:** Commands require access to `/tmp` (Spark temp storage) and `/dev` (GPU device). If commands fail due to sandbox restrictions, re-run them unsandboxed. ## Step 1: Implement the SQL Expression Implement the SQL expression in a file at `src/main/resources/<snake_name>.sql`. **Read `examples/` for example UDF-to-SQL conversions for the target language.** ### Guidelines - Focus on correctness FIRST, then GPU compatibility — the test will report which operators are not GPU-compatible - Avoid expensive joins; prefer window functions, CTEs, and built-in array/map functions over explode-and-aggregate patterns **Do NOT hardcode test sample values or outputs.** The SQL expression must work correctly for ANY potential input. ## Step 2: Fill in test and iterate Update `src/test/scala/com/udf/SqlComparisonTest.scala`: - Update the SQL file path to point to your `src/main/resources/<snake_name>.sql` file - Replace placeholders with the actual camel/snake UDF name Then run the test: ```bash mvn test -Dsuites=com.udf.SqlComparisonTest ``` If the test fails, analyze the error and iterate on the SQL expression. ### Difficult Test Failures Treat the unit test as the CPU behavior specification. Do not weaken or remove test cases silently. - Tests that check for CPU errors may not be directly applicable to SQL operators: Spark RAPIDS typically evaluates a whole column/batch and may produce nulls for invalid rows instead of throwing one row-level exception. Make an explicit judgment call about the UDF contract. Add a clear comment in the test and a `TODO/NOTE` in the SQL statement explaining the mismatch. - In rare cases, the Spark RAPIDS Plugin has known discrepancies in certain SQL operators. If a test case does not pass because of these discrepancies, notify the user and comment out the conflicting assertion/test only after documenting how you tried to make the behavior match and why those attempts failed. - If the behavior is important, common, or part of the documented input domain, **always prefer fixing the SQL expression** over commenting out the test case. The exception is a performance-vs-correctness tradeoff that the user explicitly approves. ## Step 3: Run Judge Subagent If Requested If the user explicitly asked for the judge, a judge subagent, or a review agent, treat that as an explicit request for delegation: you **MUST** launch a separate subagent with `model: inherit` and instruct it to use the **udf-judge-conversion** skill. Ask it to review the `UnitTest`, `SqlComparisonTest`, and SQL expression. If the user did not request a judge/review agent, mark this step as skipped and continue to Step 4. If a required judge subagent is blocked by tool policy, stop and tell the user that explicit permission/instruction is needed. If you run the judge, wait for it to complete and review its report. If the judge finds any issues, 1) fix the issues, 2) re-run the tests, and 3) re-run the judge subagent. ## Step 4: Review Conversion Review your own work to ensure: - The test runs on the GPU and directly compares CPU-SQL outputs - The implementation does not overfit to test cases ## Output Upon successful completion: - SQL file at `src/main/resources/<snake_name>.sql` - Comparison test passes These outputs are required for **Step 3: Benchmark**.