After attaching SQLite DB, cannot create a view with a query including `union all`

[jongmmm]

What happens?

This happens only when a SQLite database is attached and explicitly used. Please see the SQL queries below.

To Reproduce

Clean-start DuckDB CLI and type below commands.

v1.1.3 fa5c2fe
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
D attach 'test.db' (type sqlite);
D use test;
D create table aaa as select 1 as id;
D create table bbb as select 1 as id;
D select * from aaa union all select * from bbb;
┌───────┐
│  id   │
│ int64 │
├───────┤
│     1 │
│     1 │
└───────┘
D create view test as select * from aaa union all select * from bbb;
Invalid Error: Failed to execute query "CREATE VIEW test AS (SELECT * FROM aaa) UNION ALL (SELECT * FROM bbb)": near "(": syntax error

OS:

Linux

SQLite Version:

3.48.0

DuckDB Version:

1.1.3

DuckDB Client:

CLI

Full Name:

Jong-Myun Moon

Affiliation:

See LinkedIn

Have you tried this on the latest main branch?

• [x] I agree

Have you tried the steps to reproduce? Do they include all relevant data and configuration? Does the issue you report still appear there?

• [x] I agree

[wayhoowang]

I encountered the same.

DuckDB is able to query the view if the same view is created from SQLite cmdline.