Filters NOT working on virtual tables created using WITH clause

[githubuser181226]

Hello.

Just like the title says. I think this is a BUG.

If you create a view using WITH clause to create and query a virtual table, then although the results in data view are displayed correctly, they CANNOT be filtered using UI edit boxes (both the global filter and the individual column filters).

When filtering such view program always shows zero records.

Thank you.

[pawelsalawa]

Do you mean something like:

CREATE VIEW view123 AS
  WITH cte AS (
    SELECT * FROM my_virtual_table
  );

?

[githubuser181226]

Hi.
Not exactly.
Try this in SQL editor, then save it as view.

WITH virtual_table AS (
  SELECT * FROM actual_table;
)
SELECT * FROM virtual_table

Then open the view and try to filter using UI.
Cheers.

[githubuser181226]

Strange. My simple example does work as expected.
I need to research this more. I have a large db, and querying it directly makes the program annoyingly slow when updating the data view.
My idea was if I used with to put the entire db into memory and select from that, then it will be much faster, which is true.
SQLiteStudio updates data view much faster, but then, at some point, I tried to filter the view using UI and it didn't work - always showing no records, regardless which filter mode I tried.
I try to find the view definition I used and report later if the issue reapeared.
Cheers.

[pawelsalawa]

Okay, I will keep it open, yet I won't label it with a bug until a recipie for reproducing is found.

[githubuser181226]

Hi.

I found the view I have the problem with.

The following is [Dni wolne] table. It stores a date, week day number, flag if it's a holiday, and a description for the holiday. Records span a decade from year 2022 to 2023. I can upload the database file, but don't know how.

CREATE TABLE [Dni wolne]
( Data   TEXT    PRIMARY KEY
                 UNIQUE
                 NOT NULL,
  Dzień  INTEGER AS ( strftime( '%u', Data ) ),
  Święto INTEGER NOT NULL,
  Uwagi  TEXT );

The UI filters in data view of the table itself works all good.

Here's but a view which has the reported filtering issue.

CREATE VIEW [Dni wolne w roku 2025] AS
    WITH [Dni wolne 2025] AS
    ( SELECT *
    FROM [Dni wolne]
    WHERE Data BETWEEN '2025-01-01' AND '2025-12-31' )
    SELECT *
    FROM [Dni wolne 2025];

The view executes corrently, ie. it shows all the correct rows from year 2025 from [Dni wolne] table.

However, if I try to filter this view using UI, it doesn't work.
I tried using the global filter input box, no matter the mode, say "filter text if contains", and literally copy and paste one of the description, but the result is "Total rows loaded: 0"
I can switch to individual column filters and put a specific number, ie. 1 for column Święto (1 means the day is a off-work holiday, 0 means it's a working day), apply the filter and have the same result (no records).

Now my guess is, it's either the spaces or the Polish characters in the identifiers.
I'll modify the table and the view to not use those and see if that changes anything.

In the meantime, please tell me how can I upload the db file for you to test.
Will filemail.com or something like that be ok?

[pawelsalawa]

You can zip the database file and drop it on top of your comment (yet to be posted) - it will be attached.

[githubuser181226]

Hi. Thank for quick reply.
Here's a screen capture of what I've talked about.
And the database file I've used in the recording.

5342.mp4

dni_wolne.zip

[pawelsalawa]

Thanks. That will surely make it easier to get fixed.

[githubuser181226]

Hello again.
Apparently removing spaces from the name of the temporary table, removes the issue.

After modifying the view to (notice Dni_wolne_2025 instead of [Dni wolne 2025]):

CREATE VIEW Dni_wolne_2025 AS
    WITH Dni_wolne_2025 AS
    ( SELECT *
    FROM [Dni wolne]
    WHERE Data BETWEEN '2025-01-01' AND '2025-12-31' )
    SELECT *
    FROM Dni_wolne_2025;

I can filter the results properly again.

[pawelsalawa]

That's a helpful tip. It points to tokenizer or formatter part which handles the WITH clause. Thanks!