«Column projections are not supported in subscriptions» error in one-off query

[egormanga]

Error: Column projections are not supported in subscriptions; Subscriptions must return a table type

when issuing a one-off query with JOINs via WebSocket API.

[gefjon]

What query were you issuing? Do you have a reproducer?

[egormanga]

Reproduction

Default template + a second table:

#[spacetimedb::table(name = person)]
pub struct Person {
    name: String
}

#[spacetimedb::table(name = person2)]
pub struct Person2 {
    name: String
}

An /sql REST API request (using the CLI):

SELECT person.name AS a, person2.name AS b FROM person JOIN person2;

works.

A one-off query via WS API (with #2960):

ctx.queryBuilder()
	.onResolved(console.log)
	.onError((ctx, error) => console.error(error))
	.query(`SELECT person.name AS a, person2.name AS b FROM person JOIN person2;`);

results in the above error.

SpacetimeDB log clearly says:

module_host.rs:1022: One-off query: SELECT person.name AS a, person2.name AS b FROM person JOIN person2;

for the latter request.

[egormanga]

Offending line, according to my research:

SpacetimeDB/crates/core/src/host/module_host.rs

Line 1127 in ca66340

) = compile_subscription(&query, &tx, &auth)?;

[gefjon]

It is surprising to me that one-off queries go through the subscription planner rather than the ad-hoc planner. However, I can't say confidently that this is unintentional. We'll have to wait for input from @joshua-spacetime , who is currently on vacation.

[egormanga]

@gefjon Hey! I noticed @joshua-spacetime's activity today, assuming welcoming him back :)

Is it possible for the review at this time?

[joshua-spacetime]

@egormanga This limitation is intentional. We restrict the type of queries one can submit via the WebSocket API, because our client SDKs, which use the WebSocket API, cannot decode arbitrarily constructed rows. This includes both subscriptions and one-off queries.

[egormanga]

So what's the point of having joins at all then? I see this as a conflict of concepts that requires resolution.

I could agree that the clients cannot safely type arbitrary responses ahead-of-time, but decode? — absolutely positive, at least for TypeScript.

[gefjon]

So what's the point of having joins at all then? I see this as a conflict of concepts that requires resolution.

Joins, specifically semi-joins, allow filtering to select rows based on data contained in other rows in other tables in the database.

I could agree that the clients cannot safely type arbitrary responses ahead-of-time, but decode? — absolutely positive, at least for TypeScript.

Our serialization format(s), BSATN and SATS-JSON, are not self-describing. Decoding them requires prior knowledge of the type/schema of the encoded value. This is an intentional design choice - it minimizes network data and improves performance in both the serialize and deserialize paths.

Relatedly, this is why our client SDKs' API for remote queries inserts the SELECT table.* FROM table automatically, making it impossible to submit queries of the type you're seeing be rejected here.