SQLPage parse error on list_filter (DuckDB)

[kspeeckaert]

Introduction

When using the list_filter function, SQLPage throws an error, stating it's unable to parse the SQL statement (while valid in the underlying database).

To Reproduce

List of steps to reproduce the behavior. Include the sql file you are using and the eventual relevant parts of your database schema

-- DDL statement to create the table:
CREATE SCHEMA loipro;

CREATE TABLE loipro."data"(
    row_num BIGINT
    , filepath VARCHAR[]
    , filename VARCHAR
    , msg_dt DATE
    , ChangeTimestamp TIMESTAMP
    , Components STRUCT(ComponentNumber VARCHAR, Quantity VARCHAR, UnitOfMeasure VARCHAR, Scrap VARCHAR, BomPosition VARCHAR, IssueingStorageLoc VARCHAR, OperationNumberMat VARCHAR, ItemCategory VARCHAR, Backflush VARCHAR)[]
    , ChangeDate VARCHAR
    , Operations STRUCT(OperationNumber VARCHAR, MachineCode VARCHAR, SetupGroupCategory VARCHAR, ProcStartDate VARCHAR, ProcStartTime VARCHAR, EndDatOperation VARCHAR, EndTimeOperation VARCHAR, SetupTime VARCHAR, SetupTimeUOM VARCHAR)[]
    , PlantID VARCHAR
    , Unit VARCHAR
    , QuantityPlanned VARCHAR
    , ChangeTime VARCHAR
    , ArticleNumber VARCHAR
    , OrderNumber VARCHAR
    , OrderType VARCHAR
    , ReceivingStorageLoc VARCHAR
    , ProductionVersion VARCHAR
);

Query in SQLPlan causing the issue:

SET orders_wo_material = 
    select count(1)
    from loipro.data
    where ArticleNumber IS NULL
        or Components.list_filter(lambda x: x.ComponentNumber is NULL).length() = Components.length()
;

Actual behavior

[2026-01-04T11:37:10.849Z ERROR sqlpage::webserver::database::execute_queries] Parsing failed: SQLPage couldn't understand the SQL file. Please check for syntax errors on line 28:
        where ArticleNumber IS NULL
            or Components.list_filter(lambda x: x.ComponentNumber is NULL).length() = Components.length()
                                            ⬆️
    ;
    Caused by:
        0: sql parser error: Expected: ), found: x at Line: 28, Column: 42
        1: loipro/index.sql contains a syntax error preventing SQLPage from parsing and preparing its SQL statements.
[2026-01-04T11:37:10.849Z ERROR sqlpage::render] SQL error: Parsing failed: SQLPage couldn't understand the SQL file. Please check for syntax errors on line 28:
        where ArticleNumber IS NULL
            or Components.list_filter(lambda x: x.ComponentNumber is NULL).length() = Components.length()
                                            ⬆️
    ;
    Caused by:
        0: sql parser error: Expected: ), found: x at Line: 28, Column: 42
        1: loipro/index.sql contains a syntax error preventing SQLPage from parsing and preparing its SQL statements.

Screenshots

Expected behavior

Expected a single (numeric) value to be returned and stored in the variable.

Version information

• OS: macOS 26.2
• Database: DuckDB v1.4.3 (Andium) d1dc88f950
• ODBC: unixODBC 2.3.14
• DuckDB ODBC driver version: 1.4.3.0
• SQLPage Version : sqlpage 0.41.0