On Windows, I am unable to define a column with default value being date('now').

SQLite version 3.42.0 2023-05-16 12:36:15
Enter ".help" for usage hints.
sqlite>  create table if not exists tblXYZ (Name TEXT, SALARY INT, Inserted TEXT DEFAULT date('now') NOT NULL,Updated Text);
Parse error: near "(": syntax error
  (Name TEXT, SALARY INT, Inserted TEXT DEFAULT date('now') NOT NULL,Updated Tex
                                      error here ---^

• Does the default value have to be a constant or can it be dynamic e.g. date('now')?

• In a STRICT table a column intended to hold null or datetime values is defined as TEXT: is my understanding correct?

create table if not exists tblXYZ (Name TEXT, SALARY INT, Inserted TEXT DEFAULT (date('now')) NOT NULL, Updated Text);

Thanks; that works fine.

The default clause requires a constant or a constant expression. The constant / constant expression does not need to be deterministic or pure.

GENERATED ALWAYS requires a constant / constant expression and that constant / constant expression must be both deterministic and pure.

Thanks for pointing this out:

An expression in a DEFAULT clause is evaluated once, when the row is inserted, and the result stored as a static value.

That is a very valuable feature: exactly what I wanted when inserting several records via a single SQL statement.

I think that the same date will be used for all the inserted records corresponding to the "single SQL statement", but I may be mistaken. I think 'now' is captured the first time used and doesn't changed for each usage in the same statement. I don't know if that's a problem for your use case or not.

I think 'now' is captured the first time used and doesn't changed for each usage in the same statement.

My initial tests suggest that your synopsis is correct.

What I'd prefer if for multiple statements in a transaction to retain the same 'now' value.

now is step-stable (that is, the value of now is reset each time sqlite_step is called on a statement, but it remains unchanged for the duration of each step.

However, it is also a slow change constant so unless you have forced a parameter change (and thus re-evaluation) the result will remain constant thoughout the execution of the entire statement.

If that is important, you could create a temporary table at the beginning of the transaction, store the timestamp in it, and use the stored value subsequently.

Really? I have been under the assumption that the full result set was created before you called the first "step". What you're saying cannot be accurate. That would be ridiculously slow to have to figure out and calculate the next result each time step was called.

I have been under the assumption that the full result set was created before you called the first "step".

That is an incorrect assumption.

That would be ridiculously slow ...

Also incorrect (to the extent an opinion can be.)

I have been under the assumption that the full result set was created before you called the first "step".

This would be an obvious error. It is not possible to compute anything before asking for it to be computed. That is to say, how do you believe the value 3.14159 "answer" can be generated before asking "What is the value of Pi?"

That would be ridiculously slow to have to figure out and calculate the next result each time step was called.

This is a non-sequitor.

Maybe you should read the documentation.

Mind-blowing! Does that mean the Sqlite does I/O to get the next row each time you call 'step', given that the query is returning results from the disk? Obviously, I/O is buffered to not require constant disk access. How does it handle "COUNT(*)" without gathering the result set before presenting the first (or nth) result?

Does that mean the Sqlite does I/O to get the next row each time you call 'step

Yes.

given that the query is returning results from the disk

or paper tape, or magnetic tape, or a scanner, or ram, or rom, or anython else where information might be obtained.

Obviously, I/O is buffered to not require constant disk access.

Obviously. For thousands of years, yes.

How does it handle "COUNT(*)" without gathering the result set before presenting the first (or nth) result?

Please provide a sample SELECT statement that demonstrates what you are talking about since the statement you made does not parse.

if the statement were select count() from t then I cannot fathom your malfunction.

After preparing the statement, you call sqlite3_step. This causes the statement to begin execution, counting the rows in t, and returning a row containing the count() by returning from sqlite3_step preseting the code SQLITE_ROW.

You do whatever you want with the row returned, and then call sqlite3_step again to retrieve the next row. But there are no more rows, the query is done, so sqlite3_step return SQLITE_DONE.

The statement is a generator, and sqlite3_step() runs one loop of the generator and either returns a row (SQLITE_ROW) or that there are no more rows (SQLITE_DONE).

You may find running the following in the CLI to be informative: explain select count(*) from sqlite_schema; , when read in conjunction with the VDBE doc.

Consider me educated! I never thought about the implementation before and just ass-umed that the result set was complete and waiting for me at the first step. Instead, it's just in time.

That’s what happens with many client-server RDMS’s like PosgreSQL, in their default mode, because the entire resultset is transmitted through the network client-side before the PQexecute call returns. Because that avoids many network round trips. Since SQLite is embedded OTOH, it can afford to always run in cursor mode with zero-latency roundtrips. Explicitly opt-in to cursors in PostgreSQL, or PIPELINE mode, or COPY, to get a resultset in chunks.

Good point about order by. I can't think of a single query that I have written that expected more than one row to be returned where I didn't sort the results. It's a case of getting the data ready for presentation to the user - either do it in the application or let the database engine do it.

I'm having a hard time imagining a case where the select query (which expects more than one row to be returned) is run without an order by or group by (which also orders). Since the full result set must be produced before any result is given to the step, my initial assumption (that the full result set is produced prior to the first step) was correct - for my use case.

Are they any use cases for select in an application where the order of the results does not matter?

Are they any use cases for select in an application where the order of the results does not matter?

When you want to cache some information in memory using an unordered data structure such as a hash table.

Are they any use cases for select in an application where the order of the results does not matter?

Obviously yes. That is an application design issue.

As an aside, just because you are using an order by or group by does not mean that the results cannot be retrieved and presented one after each -- that is, they do not necessarily imply "sorting the whole/partial set of results". Nor does a UNION or a UNION ALL.

This is the purpose of the query planner -- to plan how to obtain the data commanded in the most efficient manner possible.

You could use one of ... DEFAULT CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP, instead of date('now')