problem with "WITH" statement on trino SQL, SQL Error [57]: ...: Schema must be specified when session schema is not set

Question

I recently switched from PostgreSQL to trino, and I run into the problem, that I obviously can't write any WITH statement properly to make it run. I tried to research it but only problem I found is here, and it was a typo in the query.

My query is basically the same, but without the typo, and I still have the same problem:

WITH tab1 as (
    select 
     id,
     (case when col2 > 0 then 'a' else 'b' end) as cathegory
    from work_schema.generic_table
)

select 
 tab1.id
 -- id     # just to demonstrate I tried both, none of them works
from tab1

This query triggers very generic error that I just cannot find anything about in documentation (anything helpful), nor here or anywhere else.

SQL Error [57]: Query failed (#20250120_133332_03607_ritng): line 3:6: Schema must be specified when session schema is not set

The query inside WITH statement alone works, I just don't understand why similar definitions work when I copy them into trino.

Can you guys explain any of these?

samhita · Accepted Answer · 2025-01-20 14:59:38Z

0

Can you try setting the schema or catalog.schema in your session as the error mentions about session schema. something like this

USE work_catalog.work_schema;
--your query

OR

USE work_schema;
-- your query

This is from documentation

Update the session to use the specified catalog and schema.

answered Jan 20 at 14:59

samhita

4,2512 gold badges12 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

quakenstein · Accepted Answer · 2025-01-21 08:17:59Z

0

For anyone looking for an answer - it is very prosaic and I was able to identify the problem.

The problem is empty line in the middle of the query, which is interpreted as end of query by DBeaver.

So easy fix is:

learn to write queries without them.

Harder fix:

set interpretation rules so empty line is not interpreted as end of query (behavior is similar to “;”)

Viva la long night, but finally I know why this was happening.

answered Jan 21 at 8:17

quakenstein

641 silver badge7 bronze badges

Collectives™ on Stack Overflow

problem with "WITH" statement on trino SQL, SQL Error [57]: ...: Schema must be specified when session schema is not set

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related