PROMPT_FIXUP

Fix up your query

The prompt_fixup table function allows MotherDuck AI to correct and completely rewrite SQL queries that have logical or severe syntactical issues. This powerful feature analyzes your problematic query, identifies issues, and generates a corrected version that follows proper SQL syntax and semantics.

tip

For minor syntax errors or typos in large queries, consider using the prompt_fix_line function instead, which is faster and more precise as it only rewrites the problematic line.

Syntax

CALL prompt_fixup('<SQL query>', [include_tables=['<table_name>', '<table_name>']]);

Parameters

Parameter	Required	Description
query	Yes	The SQL query that needs correction
include_tables	No	Array of table names to consider for context (defaults to all tables in current database)

Example Usage

Here are several examples using MotherDuck's sample Hacker News dataset from MotherDuck's sample data database.

Fixing syntax errors

-- Fixing misspelled keywords
CALL prompt_fixup('SEELECT COUNT(*) as domain_count FROM hn.hackers');

-- Fixing incorrect table names
CALL prompt_fixup('SELECT * FROM hn.stories WHERE score > 100 ODER BY score DESC');

-- Fixing missing clauses
CALL prompt_fixup('SELECT AVG(score) hn.hacker_news GROUP score > 10');

Fixing logical errors

-- Fixing incorrect join syntax
CALL prompt_fixup('SELECT u.name, s.title FROM hn.users u, hn.stories s WHERE u.id = s.user_id ORDER BY s.score');

-- Fixing aggregation issues
CALL prompt_fixup('SELECT user_id, AVG(score) FROM hn.stories GROUP BY score');

-- Fixing complex query structure
CALL prompt_fixup('SELECT COUNT(*) FROM hn.stories WHERE timestamp > "2020-01-01" AND timestamp < "2020-12-31" WITH score > 100');

Example output

When you run a prompt_fixup query, you'll receive a single-column table with the corrected SQL:

query
SELECT COUNT(*) as domain_count FROM hn.hacker_news

How it works

The prompt_fixup function processes your query in several steps:

1. Analysis: examines your query to identify syntax errors, logical issues, and structural problems
2. Schema validation: checks your query against the database schema to ensure table and column references are valid
3. Correction: applies fixes based on the identified issues and your likely intent
4. Rewriting: generates a complete, corrected version of your query that maintains your original goal

For example, when fixing this query with multiple issues:

CALL prompt_fixup('SEELECT AVG(scor) FRUM hn.stories WERE timestamp > "2020-01-01" GRUP BY user_id');

The function will:

• Correct misspelled keywords (SEELECT → SELECT, FRUM → FROM, WERE → WHERE, GRUP → GROUP)
• Fix column name typos (scor → score)
• Ensure proper clause ordering and syntax

Resulting in a properly formatted query:

query
SELECT AVG(score) FROM hn.stories WHERE timestamp > '2020-01-01' GROUP BY user_id

For logical errors, the process is similar but focuses on semantic correctness:

CALL prompt_fixup('SELECT user_id, AVG(score) FROM hn.stories GROUP BY score');

Will be corrected to:

query
SELECT user_id, AVG(score) FROM hn.stories GROUP BY user_id

The function recognized that grouping should be by user_id (the non-aggregated column) rather than by score (which is being averaged).

Best practices

For the best results with prompt_fixup:

1. Include the entire query: even if only part of it has issues
2. Be specific with table names: use the include_tables parameter for large databases
3. Review the fixed query: always check that the corrected query matches your intent
4. Use for complex issues: prefer this function for logical errors or major syntax problems
5. Consider alternatives: for simple typos, prompt_fix_line may be more efficient

Limitations

While prompt_fixup is powerful, be aware of these limitations:

• May change query logic if the original intent isn't clear
• Performance depends on the complexity of your query
• Works best with standard SQL patterns and common table structures
• May not preserve exact formatting or comments from the original query
• Cannot fix issues related to missing tables or columns in your database

Troubleshooting

If you're not getting the expected results:

• Check that you've included all relevant tables in the include_tables parameter
• Ensure your database schema is accessible to the function
• For very complex queries, try breaking them into smaller parts
• If the fixed query doesn't match your intent, try providing more context in comments

Notes

MotherDuck AI operates on your current database by evaluating the schemas and contents of the database. You can specify which tables and columns should be considered using the optional include_tables parameter. By default, all tables in the current database are considered.

To point MotherDuck AI at a specific database, execute the USE database command (learn more about switching databases).

These capabilities are provided by MotherDuck's integration with OpenAI. For availability and pricing, see MotherDuck's Pricing Model.

If you need higher usage limits or have specific requirements, please reach out to the Slack support channel or email support@motherduck.com.