Result Sets and Output Modes

Why this stage exists

So far the executor prints. The code that pulls rows from the plan also writes 1 | ada | [email protected] and (3 rows). That was fine while there was one way to show a result. There will be more: a second output mode in this stage, and later a network protocol that sends rows to a client instead of printing them. If every one of them lives inside the executor, the executor is mostly about formatting.

So this stage separates the two jobs. The executor produces a result: the column names, the rows one at a time, how many there were. Or, for an INSERT, how many rows it added. Or an error. A printer turns a result into lines. The table format you already print becomes one printer, and CSV becomes a second, chosen with .mode csv.

What changes

The executor no longer writes lines. It tells a printer what it has, in order, and the printer writes:

The rows still travel one at a time, as the Volcano executor produces them. A printer that needed every row before it could print the first one, to line up columns for instance, would have to keep the whole result in memory. Neither printer here needs to, so neither does.

The affected count

A SELECT's result is rows. An INSERT's result is a number: how many rows it added. The printer now says it: Insert statement executed successfully (1 rows).

An INSERT always adds exactly one row, so the count is always 1 here, and still rows, like the SELECT footer. It matters more in delete-update, where one statement can change any number of rows and the count is how you know. A statement that fails prints its error and no success line, as before.

CSV, and the comma that is data

CSV is comma-separated values: the header and each row on one line, the values separated by commas, and no footer. It is what a spreadsheet opens and what most tools import.

The problem is a value that holds a comma. Smith, Bob written as it is would read back as two values. So a value that contains a comma, or a double quote, is written between double quotes, and every double quote inside it is written twice:

Nothing else is quoted: not spaces, not numbers, not empty strings. A row with only an id is 2,,. These are the common CSV rules, the ones spreadsheets follow when they read a file.

What the mode does not touch

Only SELECT's rows change with the mode. Error lines, the INSERT success line, [PLAN], [BTREE] and [PAGER] lines, and .help print exactly the same in both modes. They are not rows, so no printer formats them.

The mode lasts until the session ends. The next session, and every -c run, starts in table mode again.

Output contract for this stage

These are the exact lines the tests check. The full definition is on the output contract page, sections 10 and 11.

What you should see when done

$droid --db users.db
Welcome to droid!
>insert into users (id, name, email) values (1, 'ada', '[email protected]');
Insert statement executed successfully (1 rows)
>insert into users (id, name, email) values (2, 'Smith, Bob', '[email protected]');
Insert statement executed successfully (1 rows)
>insert into users (id, name, email) values (3, 'Cy "the kid"', '[email protected]');
Insert statement executed successfully (1 rows)
>insert into users (id, name, email) values (1, 'eve', '[email protected]');
[ERROR:00701] Key 1 already exists
>select * from users;
id | name | email
1 | ada | [email protected]
2 | Smith, Bob | [email protected]
3 | Cy "the kid" | [email protected]
(3 rows)
>.mode csv
>select * from users;
id,name,email
1,ada,[email protected]
2,"Smith, Bob",[email protected]
3,"Cy ""the kid""",[email protected]
>select name from users where id > 5;
name
>btree find 2
[BTREE] Find key=2: FOUND (page=0 cell=1)
[BTREE] Row: id=2 name='Smith, Bob' email='[email protected]'
>.mode json
[ERROR:01001] Unknown output mode
>.mode table
>select id, name from users where id = 2;
id | name
2 | Smith, Bob
(1 rows)
>.exit
exiting.. good bye!

The CSV SELECT that matched nothing printed its header line alone. btree find printed the same lines it prints in table mode, comma and all. After the refused .mode json the session was still in CSV mode, until .mode table.

Common bugs in this stage

• The footer in CSV. (3 rows) at the end of a CSV result is a fourth row to anything that reads the file. CSV has no footer.
• Quoting everything, or nothing. Quoting every field is valid CSV to some readers and fails the tests, which follow the contract. Quote only a value with a comma or a double quote.
• Forgetting to double the quotes. "Cy "the kid"" ends the field at the second quote. Every " inside a quoted field is written "".
• Quoting the padding. The value is the string up to its first zero byte, as in table mode, not the 28-byte field.
• CSV leaking into other lines. An error or a [PLAN] line with a comma in it gets quoted, or loses its brackets. Only rows go through the CSV printer.
• A refused mode switches anyway. .mode json prints 01001 and then leaves the session in some third state. An error changes nothing.
• The mode survives the session. Saving it with the database, or in a global that outlives a -c run, makes the next session start in CSV. Every session starts in table mode.
• Counting what failed. A duplicate INSERT prints (1 rows), or (0 rows), after its error. A failed statement prints its error and nothing else.

Language notes

The page above is the same for every language. These are the only language-specific hints.

• C: a printer can be a struct of three function pointers, columns, row and end, with the table and CSV printers as two instances of it. The executor takes a pointer to whichever the session chose.
• C++: an abstract Printer class with virtual columns, row and end, and two subclasses.
• Rust: a trait with three methods, and the session holding a Box<dyn Printer>, or a two-variant enum with a match in each method.
• Zig: a tagged union of the two printers with a switch in each operation is enough for two modes.