Difference of pre computed tables in SurrealDB and SQL-Views when deleting an element?

Question

According to the SurrealDB-Documentation, pre computed tables in SurrealDB are similar to views in relational databases. But they seem to act a bit differently, when elements are deleted.

Basically, pre computed tables seem to be a short cut for a SELECT statement (as SQL views could also be seen as). But I do not understand, how deleted items are treated differently by pre computed tables. Could you give me a hint, please?

I did the following in the "sandbox" in [surrealist.app](surrealist.app):

1. Define simple item table with category and amount field:

DEFINE TABLE item SCHEMAFULL;
DEFINE FIELD category ON TABLE item TYPE string;
DEFINE FIELD amount ON TABLE item TYPE number;

2. I created a pre computed table categories with a GROUP BY-clause and some window functions.

DEFINE TABLE categories AS 
   SELECT category, 
          math::sum(amount) AS sum, 
          count() as count, 
          math::max(amount) as max
   FROM item 
   GROUP BY category;

3. I added two items of category "green" and one (item:C) of category "blue".

CREATE item:A SET category="green", amount=2;
CREATE item:B SET category="green", amount=3;
CREATE item:C SET category="blue", amount=4;

4. I deleted item:C. Thus, no items of category "blue" are left.

DELETE item:C;

5. A simple select of TABLE item shows the removal of item:C (and of the category "blue"):

SELECT * FROM item;

I get:

[
    {
        "amount": 2,
        "category": "green",
        "id": "item:A"
    },
    {
        "amount": 3,
        "category": "green",
        "id": "item:B"
    }
]

6. But when I do a SELECT on pre computed TABLE categories, the category "blue" still exists (contrary to my expectation). It has - as expected - no rows (count) and a sum of 0. But - again contrary to my expectations - the "blue" category has a max-value:

SELECT * FROM categories;

I get:

    {
        "category": "blue",
        "count": 0,
        "id": "categories:['blue']",
        "max": 4,
        "sum": 0
    },
    {
        "category": "green",
        "count": 2,
        "id": "categories:['green']",
        "max": 3,
        "sum": 5
    }
]

7. What I had expected, can be reproduced by the SELECT statement itself which serves as base for the definition of the precomputed table:

SELECT category, 
          math::sum(amount) AS sum, 
          count() as count, 
          math::max(amount) as max
   FROM item 
   GROUP BY category;

I get simply the "green" category:

[
    {
        "category": "green",
        "count": 2,
        "max": 3,
        "sum": 5
    }
]

In the end, what does the documentation want to say with the "similarity" between pre computed SurrealDB tables and views in relational databases? What is the difference in treating deleted elements?

Answer 1

Alexander from SurrealDB here.

Our "Pre-computed table views" are similar to materialized views, instead of typical views. Will update that in the documentation.

They are incrementally updated when inserting/deleting data, which would be more performant than typical views as you wouldn't need to run the entire select statement every time.

That being said, it appears there might be a bug here, which we'll look into.

I've created a bug issue for this, which you can track here: https://github.com/surrealdb/surrealdb/issues/3546