I am using dbt-trino and for some reason, it doesn't understand the MySQL query that works fine by executing it directly on MySQL. In this query, I want to select and group records that have been created during the previous month. The Query:
SELECT order_location, COUNT(*) as order_count
FROM {{ ref('x_stg_order_fields') }}
WHERE
created_at >= DATE_FORMAT( CURRENT_DATE - INTERVAL 1 MONTH, '%Y/%m/01' )
AND
created_at < DATE_FORMAT( CURRENT_DATE, '%Y/%m/01' )
GROUP BY order_location
While this query works fast and successfully directly on MySQL, it returns this error when executing with dbt run:
TrinoUserError(type=USER_ERROR, name=SYNTAX_ERROR, message="line 7:53: mismatched input 'COUNT'. Expecting: '*', <expression>")
Does this mean that dbt-trino doesn't support all MySQL functions?
That error is coming from your database, not from dbt itself. dbt does not parse your SQL commands, it just passes them through to your connected database.
My guess is that {{ ref('x_stg_order_fields' }} may be referring to an ephemeral model that contains a syntax error, or possibly a field named count that isn't quoted?
You can confirm or disprove that by looking at the SQL that dbt tried to run in your database, by inspecting the target directory in your project. Specifically, target/run/path/to/your_model.sql will show you the actual command being executed. You should be able to check line 7, col 53 in that file, and you will see the code that trino is erroring about.