PROMPT_FIX_LINE

Fix your query line-by-line

The prompt_fix_line table function allows MotherDuck AI to correct specific lines in your SQL queries that contain syntax or spelling errors. Unlike prompt_fixup, which rewrites the entire query, this function targets only the problematic lines, making it faster and more precise for localized errors.

tip

This function is ideal for fixing minor syntax errors in large queries where you want to preserve most of the original query structure and formatting.

Syntax

CALL prompt_fix_line('<SQL query>', error='<Error message>', [include_tables=['<table_name>', '<table_name>']]);

Parameters

Parameter	Required	Description
query	Yes	The SQL query that needs correction
error	No	The error message from the SQL parser (helps identify the problematic line)
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 simple syntax errors

-- Fixing a misspelled keyword with error message
CALL prompt_fix_line('SEELECT COUNT(*) as domain_count FROM hn.hackers', error='
Parser Error: syntax error at or near "SEELECT"
LINE 1: SEELECT COUNT(*) as domain_count FROM h...
        ^');

-- Fixing a typo in a column name
CALL prompt_fix_line('SELECT user_id, titlee, score FROM hn.stories LIMIT 10');

-- Fixing incorrect operator usage
CALL prompt_fix_line('SELECT * FROM hn.stories WHERE score => 100');

Fixing errors in multi-line queries

-- Fixing a specific line in a complex query
CALL prompt_fix_line('SELECT 
    user_id,
    COUNT(*) AS post_count,
    AVG(scor) AS average_score
FRUM hn.stories
GROUP BY user_id
ORDER BY post_count DESC
LIMIT 10', error='
Parser Error: syntax error at or near "FRUM"
LINE 5: FRUM hn.stories
        ^');

Example output

When you run a prompt_fix_line query, you'll receive a two-column table with the line number and corrected content:

line_number	line_content
1	SELECT COUNT(*) as domain_count FROM hn.hackers

For multi-line queries, only the problematic line is corrected:

line_number	line_content
5	FROM hn.stories

How it works

The prompt_fix_line function processes your query in a targeted way:

1. Error localization: uses the error message (if provided) to identify the specific line with issues
2. Context analysis: examines surrounding lines to understand the query's structure and intent
3. Targeted correction: fixes only the problematic line while preserving the rest of the query
4. Line replacement: returns the corrected line with its line number for easy integration

For example, when fixing a syntax error in a single line:

CALL prompt_fix_line('SEELECT COUNT(*) as domain_count FROM hn.hackers', error='
Parser Error: syntax error at or near "SEELECT"
LINE 1: SEELECT COUNT(*) as domain_count FROM h...
        ^');

The function will focus only on line 1, correcting the misspelled keyword:

line_number	line_content
1	SELECT COUNT(*) as domain_count FROM hn.hackers

For multi-line queries with an error on a specific line:

CALL prompt_fix_line('SELECT 
    user_id,
    COUNT(*) AS post_count,
    AVG(scor) AS average_score
FRUM hn.stories
GROUP BY user_id
ORDER BY post_count DESC
LIMIT 10', error='
Parser Error: syntax error at or near "FRUM"
LINE 5: FRUM hn.stories
        ^');

The function will only correct line 5, leaving the rest of the query untouched:

line_number	line_content
5	FROM hn.stories

This allows you to apply the fix by replacing just the problematic line in your original query, which is especially valuable for large, complex queries where a complete rewrite would be disruptive.

When multiple errors exist, you would run prompt_fix_line multiple times, fixing one line at a time:

-- First fix
CALL prompt_fix_line('SELECT 
    user_id,
    COUNT(*) AS post_count,
    AVG(scor) AS average_score
FRUM hn.stories
GROUP BY user_id
ORDER BY post_count DESC
LIMIT 10', error='
Parser Error: syntax error at or near "FRUM"
LINE 5: FRUM hn.stories
        ^');

-- After applying the first fix, run again for the second error
CALL prompt_fix_line('SELECT 
    user_id,
    COUNT(*) AS post_count,
    AVG(scor) AS average_score
FROM hn.stories
GROUP BY user_id
ORDER BY post_count DESC
LIMIT 10', error='
Parser Error: column "scor" does not exist
LINE 4:     AVG(scor) AS average_score
                ^');

The second call would return:

line_number	line_content
4	AVG(score) AS average_score

Note: you need to run prompt_fix_line multiple times to fix all errors.

Best practices

For the best results with prompt_fix_line:

1. Include the error message: the parser error helps pinpoint the exact issue
2. Preserve query structure: use this function when you want to maintain most of your original query
3. Fix one error at a time: to address multiple errors, run prompt_fix_line multiple times
4. Include context: provide the complete query, not just the problematic line
5. Be specific with table names: use the include_tables parameter for large databases

Limitations

While prompt_fix_line is efficient, be aware of these limitations:

• Only fixes syntax errors, not logical errors in query structure
• Accurate error messages help identify the problematic line and improve output
• May not be able to fix errors that span multiple lines
• Cannot fix issues related to missing tables or columns in your database
• Works best with standard SQL patterns and common table structures

Troubleshooting

If you're not getting the expected results:

• Ensure you've included the complete error message
• Check that the line numbers in the error message match your query
• For complex errors, try using prompt_fixup instead
• If multiple lines need fixing, address them one at a time
• Verify that your database schema is accessible to the function

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.