Proper SQL support roadmap

Abstract

Since SpacetimeDB claims to be an SQL-compatible database, I hereafter propose a checklist on improving (and hopefully completing) the SQL querying & data manipulation support in it.

Note: I decided not to add all the already-implemented features in order to minimize necrobumping noise; feel free to do so if desired.

Syntax

• Timestamp literals: #2450
  • #2842
• Tuples: #2843
• Arrays: #2849
• Enum literals: #3434
  • #2844
• NULL literal: #3606

Compliance

• Meta: #2993
• Mixed projections: #2968
• Option compatibility: #3607
• BYTEA literal escape format: #3756
• SELECT behavior over PG: #3770
  • #3771

Features

• FORMAT: #1437
• EXPLAIN: #2058
  • #2075
  • #4793
• ORDER BY: #2457
• Foreign keys: #2608
• #2670
• OUTER JOIN: #2892
• :owner parameter: #3229
• Non-index joins: #3241
• ALTER: #3272
• Parametrized views: #3489
  • #3791
• COPY OUT #3928
• RETURNING: #3935
• SELECT 1
• OFFSET
• GROUP BY
• CAST()/CONVERT()
• Implicit joins
• Nested queries

Disclaimer: most of the linked PRs are mine. I'm not particularly trying to be pushy, but I found it desirable to categorize all the mess those make and perhaps help the maintainers get them over with imposing less friction.

Start by reading the SQL compatibility documentation and the linked issues in the syntax, compliance, and features sections. Choose a separately scoped unchecked item only after reviewing its linked issue or pull request; done would mean that item has been implemented and its SQL behavior verified.