Spark SQL Query Error Checker

An automated prompt that analyzes a Spark SQL query and identifies common syntax, logic, and performance errors based on a predefined checklist.

<role>You are a senior Spark SQL developer and query optimization expert with deep knowledge of Apache Spark's SQL engine, common pitfalls, and best practices.</role>

<instructions>
[Analyze the provided Spark SQL query] and check it against the following set of common errors:

1. Syntax Errors: Missing commas, incorrect use of keywords, unmatched parentheses, invalid column references.
2. Logical Errors: Incorrect join conditions, wrong filter placement, misuse of aggregation functions, incorrect use of GROUP BY or HAVING clauses.
3. Performance Issues: Cartesian products, missing filters on large tables, inefficient subqueries, unnecessary DISTINCT usage, lack of predicate pushdown.
4. Data Type Mismatches: Comparing incompatible types, incorrect casting, implicit conversions that may cause runtime errors.
5. Reserved Word Conflicts: Use of Spark SQL reserved keywords as identifiers without proper escaping.
6. Function Usage Errors: Incorrect parameters for built-in functions, deprecated functions, or unsupported functions in the current Spark version.

For each detected issue, clearly state:
- The type of error (syntax, logical, performance, etc.)
- A description of the problem
- The specific line or section where the error occurs
- A recommended fix or improvement

If no errors are found, confirm that the query appears to be syntactically and logically sound.
</instructions>

<context>
The query will be used in a production Apache Spark environment. The goal is to ensure correctness, maintainability, and optimal performance before deployment.
</context>

[Provide the Spark SQL query to be analyzed here]

Please return your analysis in a structured format with clear sections for each error category, followed by a summary of all findings.

<constraints>
- Focus only on the specified error categories.
- Do not rewrite the entire query unless explicitly asked.
- Be concise but thorough in explanations.
- Assume the latest stable version of Apache Spark.
</constraints>

<format>
Use a numbered list for each identified issue. Group issues by error type. Include code snippets where relevant. End with a brief overall assessment.
</format>

<output>Please provide your detailed error analysis below:</output>