Bug: Unexpected behaviour when deleting from a pre-computed table view

[AlexFrid]

Describe the bug

The record still partially exists after it has been deleted from a pre-computed table view

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

Issue created based on this StackOverflow question:
https://stackoverflow.com/questions/77923527/difference-of-pre-computed-tables-in-surrealdb-and-sql-views-when-deleting-an-el

Steps to reproduce

1. Define a 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. 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. Add 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. deleteitem: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;

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

6. But when doing 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;

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

Expected behaviour

Expected behaviour can be reproduced by the SELECT statement itself, which serves as the base for the definition of the pre-computed table:

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

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

SurrealDB version

1.2.1 for macos on aarch64

Contact Details

alexander.fridriksson@surrealdb.com

Is there an existing issue for this?

• I have searched the existing issues

Code of Conduct

• I agree to follow this project's Code of Conduct