Incorrect query is parsed without any errors

[VaibhaveS]

Query

query = """
CREATE OR REPLACE FUNCTION update_customer_total_sales(customer_id INT)
RETURNS VOID AS $$
DECLARE
    total NUMERIC;
BEGIN
    -- Calculate the total sales for the specified customer
    SELECT COALESCE(SUM(amount), 0) INTO total
    FROM orders
    WHERE customer_id $1;   <------ this part is invalid
    
    -- Update the total_sales in the customers table
    UPDATE customers
    SET total_sales = total
    WHERE customer_id;

    -- Optionally, you can return the total for confirmation
    RAISE NOTICE 'Total sales for customer ID %: %', customer_id, total;
END;
$$ LANGUAGE plpgsql;
"""

root = parse_plpgsql(query)

Returns

[{'PLpgSQL_function': {'datums': [{'PLpgSQL_var': {'refname': 'customer_id', 'datatype': {'PLpgSQL_type': {'typname': 'UNKNOWN'}}}}, {'PLpgSQL_var': {'refname': 'found', 'datatype': {'PLpgSQL_type': {'typname': 'UNKNOWN'}}}}, {'PLpgSQL_var': {'refname': 'total', 'lineno': 3, 'datatype': {'PLpgSQL_type': {'typname': 'NUMERIC'}}}}, {'PLpgSQL_row': {'refname': '(unnamed row)', 'lineno': 6, 'fields': [{'name': 'total', 'varno': 2}]}}], 'action': {'PLpgSQL_stmt_block': {'lineno': 4, 'body': [{'PLpgSQL_stmt_execsql': {'lineno': 6, 'sqlstmt': {'PLpgSQL_expr': {'query': 'SELECT COALESCE(SUM(amount), 0)                FROM orders\n    WHERE customer_id $1', 'parseMode': 0}}, 'into': True, 'target': {'PLpgSQL_row': {'refname': '(unnamed row)', 'lineno': 6, 'fields': [{'name': 'total', 'varno': 2}]}}}}, {'PLpgSQL_stmt_execsql': {'lineno': 11, 'sqlstmt': {'PLpgSQL_expr': {'query': 'UPDATE customers\n    SET total_sales = total\n    WHERE customer_id', 'parseMode': 0}}}}, {'PLpgSQL_stmt_raise': {'lineno': 16, 'elog_level': 18, 'message': 'Total sales for customer ID %: %', 'params': [{'PLpgSQL_expr': {'query': 'customer_id', 'parseMode': 2}}, {'PLpgSQL_expr': {'query': 'total', 'parseMode': 2}}]}}, {'PLpgSQL_stmt_return': {}}]}}}}]

[lelit]

Hi, please can you clarify what the problem is? It's not easy to tell from what you wrote above...

[VaibhaveS]

Hi @lelit,

The query is incorrect, because there is no = between WHERE customer_id $1 however parse_plpgsql(query) returns correctly.

[lelit]

Oh, I see. This is another case where the problem is in the underlying lbpg_query parser and there's little pglast can do.

If I change the original simple_plpgsql.c using your statement like this

#include <pg_query.h>
#include <stdio.h>
#include <stdlib.h>

int main() {
  PgQueryPlpgsqlParseResult result;

  result = pg_query_parse_plpgsql(" \
CREATE OR REPLACE FUNCTION update_customer_total_sales(customer_id INT) \
RETURNS VOID AS $$ \
DECLARE \
    total NUMERIC; \
BEGIN \
    SELECT COALESCE(SUM(amount), 0) INTO total \
    FROM orders \
    WHERE customer_id $1; \
    UPDATE customers \
    SET total_sales = total \
    WHERE customer_id; \
    RAISE NOTICE 'Total sales for customer ID %: %', customer_id, total; \
END; \
$$ LANGUAGE plpgsql;");

  if (result.error) {
    printf("error: %s at %d\n", result.error->message, result.error->cursorpos);
  } else {
    printf("%s\n", result.plpgsql_funcs);
  }

  pg_query_free_plpgsql_parse_result(result);

  // Optional, this ensures all memory is freed upon program exit (useful when running Valgrind)
  pg_query_exit();

  return 0;
}

and execute it

$ make examples/simple_plpgsql
$ examples/simple_plpgsql

I get

[
{"PLpgSQL_function":{"datums":[{"PLpgSQL_var":{"refname":"customer_id","datatype":{"PLpgSQL_type":{"typname":"UNKNOWN"}}}},{"PLpgSQL_var":{"refname":"found","datatype":{"PLpgSQL_type":{"typname":"UNKNOWN"}}}},{"PLpgSQL_var":{"refname":"total","lineno":1,"datatype":{"PLpgSQL_type":{"typname":"NUMERIC"}}}},{"PLpgSQL_row":{"refname":"(unnamed row)","lineno":1,"fields":[{"name":"total","varno":2}]}}],"action":{"PLpgSQL_stmt_block":{"lineno":1,"body":[{"PLpgSQL_stmt_execsql":{"lineno":1,"sqlstmt":{"PLpgSQL_expr":{"query":"SELECT COALESCE(SUM(amount), 0)                FROM orders     WHERE customer_id $1","parseMode":0}},"into":true,"target":{"PLpgSQL_row":{"refname":"(unnamed row)","lineno":1,"fields":[{"name":"total","varno":2}]}}}},{"PLpgSQL_stmt_execsql":{"lineno":1,"sqlstmt":{"PLpgSQL_expr":{"query":"UPDATE customers     SET total_sales = total     WHERE customer_id","parseMode":0}}}},{"PLpgSQL_stmt_raise":{"lineno":1,"elog_level":18,"message":"Total sales for customer ID %: %","params":[{"PLpgSQL_expr":{"query":"customer_id","parseMode":2}},{"PLpgSQL_expr":{"query":"total","parseMode":2}}]}},{"PLpgSQL_stmt_return":{}}]}}}}
]

Can you try creating an issue there?

[lelit]

Thanks, see issue libpg_query#261.