SQLite Forum

Hello,

I'm working on a SQL generator that wants to "flatten" a to-many relation backed by a foreign key into a to-one relation by focusing on a single row in the associated rows.

Sometimes examples make things more clear. Let's say we want to fetch "all chats along with their latest post", given the following schema:

    CREATE TABLE chat (
      id INTEGER PRIMARY KEY
    );
    CREATE TABLE post (
      id INTEGER PRIMARY KEY,
       chatId INTEGER REFERENCES chat(id),
       date DATETIME
    );

In my experiments looking for a SQL pattern that provides correct results, while remaining composable (I'm not looking for the most efficient SQL, and generally speaking I trust the SQLite query planner), I stumbled upon a surprising behavior with SQLite version 3.28.0.

This query works:

    -- Success
    SELECT chat.*, post.* 
    FROM chat 
    JOIN post ON post.id = (SELECT id FROM post WHERE post.id = chat.id ORDER BY date DESC LIMIT 1);

This one fails with a "no such column" error:

    -- Error: no such column: chat.id
    SELECT chat.*, post.* 
    FROM chat 
    JOIN (SELECT * FROM post WHERE post.id = chat.id ORDER BY date DESC LIMIT 1) post;

I have two questions, if someone can help. The first is the most important to me. The second is just a request for clarification:

1. Can I rely on the fact that the first query will never give such an error in the future?
2. Why can some subqueries refer to outer tables, and some others can not?