SQLite Forum

In the page on data types, section 3.2. Affinity Of Expressions, first bullet point:

The right-hand operand of an IN or NOT IN operator has no affinity if the operand is a list and has the same affinity as the affinity of the result set expression if the operand is a SELECT.
[my boldface]

That sentence needs at least a comma after the words “is a list”.

I had to read this several times to make sense of it, because when I arrived at “is a list and” my brain figured out that there are two criteria to be fulfilled for the expression to have no affinity, namely (a) it is a list, and (b) it has the same affinity as … oh wait, if it has affinity, then it supposed not to have affinity? Makes no sense. Backtrack and read it again. Nope, that is what it says. So I read the rest of the sentence, and realise that I am looking at two different statements. Once confusion has set in, it can be amazingly difficult to get unconfused sometimes.

I suggest making it two sentences:

The right-hand operand of an IN or NOT IN operator has no affinity if the operand is a list. It has the same affinity as the affinity of the result set expression if the operand is a SELECT.

perfect is the enemy of the good, Voltaire

… who was quoting an old Italian proverb, Le meglio è l'inimico del bene, in which it is the better that is the enemy of the good.

I like the Shakespeare version, though:

Were it not sinful then, striving to mend,
To mar the subject that before was well?

(Source: Wikipedia.)

Sorry for the off-off-topic tangent. To get back to the merely off-topic tangent, every floating point number, being a dyadic rational, has a finite decimal expansion. I would not dream of suggesting one should print that, but in a “better” world, one could print enough digits so that reading it back would yield the exact same number. I believe Common Lisp implementations strive to achieve this goal, possible at a loss of performance. There is a large literature on the efficient and exact printing of floating point numbers, apparently a tricky subject. Conversely, can one assume that reading the decimal representation of a number would result in the exact float that is closest to the given number, subject to appropriate rounding behaviour? I suspect not.

Everything in the previous paragraph is indeed the enemy of the current perfectly(!) good state of affairs, and striving to mend it seems sinful indeed.

I don’t see it in the current online docs

It’s right here.

I see only the modifier ’unixepoch’ there, not the function.

I guess the changes to the source have not made it to the online docs yet, or did my browser cache an ancient version?

Worse, I have seen reports that cheap USB drives lie; You send the command to flush data to disk, and it reports back that it has done so, before actually doing it. That way lies data corruption and mayhem if the computer crashes or power goes out at the wrong moment. (Admittedly, this information is old and possibly outdated.)

I suppose solid state drives are more reliable in that respect, since there is no reason to reorder writes on them. But I wouldn’t know for sure.

That’s good. And thanks for the bonus link! It’s been too long since I read any of Aesop’s fables.

PS. Glad to see the unixepoch function too. I don’t see it in the current online docs, so it (or its mention in the documentation) must be a recent addition.

I apologize for the final paragraph of my initial post. It has led the discussion astray, or at least not where I wanted it to go. To me, the important point is the type of the result, not whether or not it is some microseconds off.

but I read too fast when busy sometimes

Me too, all the time.

I don't think the tiny difference in values is important enough to mention. But a user with an application processing loads of daytime values might want to avoid the conversion from real to string and back for performance reasons.

And here is one example where the differnt results could be important, if the result of the query is exported:

▶▶▶ select json_array(julianday('now'),strftime('%J','now')) daytimes;
daytimes
-------------------------------------
[2459606.4024086,"2459606.402408599"]

(Note the quotes around the second array element.)

That is a good change, but it is somewhat orthogonal to my concern, which is that julianday(…) and strftime('%J',…) are not exactly equivalent, as they return different types:

▶▶▶ create table tbl(date, type generated as (typeof(date)));
▶▶▶ insert into tbl(date) values (julianday('now')), (strftime('%J','now'));
▶▶▶ select * from tbl;
date               type
-----------------  ----
2459606.18498622   real
2459606.184986215  text

The difference probably doesn't matter in the vast majority of uses, but it is a difference.

But I agree that the doc is indeed very clear, with this itty-bitty reservation.

The docs for Date And Time Functions leaves the reader with the impression that julianday(…) returns a string, same as strftime('%J', ...). But a bit of experimentation reveals that it returns a real number instead.

That little bit of information may be worth documenting. It could be done by adding a few words (shown here in boldface):

The julianday() function returns the Julian day - the number of days since noon in Greenwich on November 24, 4714 B.C. (Proleptic Gregorian calendar) as a real number.

I have little doubt, though I haven't tested it, that CAST(strftime('%J',…) AS REAL) would produce the exact same number, down to the least significant bit. But that should be unnecessary. Possibly, the example showing strftime('%J',…) as being equivalent to julianday(…) should also be amended to use this CAST expression.

That got seriously mangled by the markup (markdown?) process, as the vertical bars turned your whole code into a table! Try it with a narrow window for some hilarious result (that is how I discovered it).

Here it is again, properly markdown-quoted:

select 'Q' || ((cast(substr(`accounting period`, 1, 2) as integer) + 2) / 3) || '-' || substr(`accounting period`, 7, 4) || '.csv' AS Filename from B

or yet again, with some newlines added for legibility:

select 'Q'
 || ((cast(substr(`accounting period`, 1, 2) as integer) + 2) / 3) 
 || '-' || substr(`accounting period`, 7, 4) 
 || '.csv' AS Filename from B

Making a mistake by allowing non-standard syntax and later complaining that still so many people use this, is not the finest thing to do.

I think that is unnecessarily harsh. First, the syntax in question was introduced for a reason that may well have seemed valid at the time (MS SQL server did it). Second, the SQLite project is extremely careful to maintain backward compatibility, more so than many other projects. You may disagree with this stance, but ”not the finest thing” is an unfair characterization.

Of course, there are ways to get rid of such warts. For example, one could introduce a pragma to turn off this syntactical misfeature. Using that pragma might simultaneously enable other uses of the square brackets, thus encouraging people to use it. And after a year or two, the default could switch, so that you now have to use the pragma to enable the misfeature instead. (It would have to take a boolean argument to allow its usage to remain the same.) Should it be done? That is not for me to decide, but I certainly wouldn't mind.

Pro tip: Try to create a way for others to repeat your BUG!

It should start with an empty database, followed by one (or more) CREATE TABLE statements, followed by INSERT INTO … VALUES (…), … to populate the table, followed by a SELECT statement to exhibit the BUGgy behaviour.

Vary this recipe according to need, but present it all as a bunch of lines that people can copy and paste into a file and feed directly to sqlite3. Then all can reproduce the BUG! for themselves, and all will be happy. Unicorns and rainbows all around.

Yet another idea: This is assuming that you have an old backup. Presumably, you don't want to use that, since it will contain outdated data. But you might try using the first 16K from it! With luck, it won't have changed.

I fail to see the part of the documentation where it says that the .parameter command uses parameter binding.

What I do see, though, is the claim that when the shell is going to run a query, it will extract available data from the sqlite_parameters table and bind the values to the corresponding parameters in the query.

From a cursory glance at src/shell.c.in, it looks like the function named bind_prepared_stmt performs this task.

Am I wrong about that?

Now, to be sure, due to the implementation of command line parsing in the shell and the .parameter command, using that command is not a way to secure against SQL injection and the like. Possibly, the documentation should contain a warning against that misconception.

Indeed, I discovered this recently, but as I could not for the life of me think up any possible harm coming from it, I decided it not worth reporting.

Someone might wish to (ab)use this for a temporary key-value store, but my own preference would be to make my own temp table with a shorter name for the purpose.

I think you exaggerate the need to explain. Those who know enough to access the sqlite_parameters table directly, also know enough not to be surprised by the consequences if it does not exist.

That said, when I look more closely at the documentation, I agree that it would require more than a minor rewrite to incorporate this nugget of information. It is quite possibly not worth the bother.

If I were to rewrite it, I would probably just describe .param set/unset/list/clear without reference to the underlying table, then explain the implementation next, including .param init. That might make the documentation more accessible to the casual user.

For my own part, I'll just stop writing .param init and get on with my life. ;-)

This is a minor documentation nit, but:

In my experiments with the sqlite3 shell, I can set parameters without doing .param init manually. The sqlite_parameters table gets created automagically when needed. But the documentation seems to indicate otherwise, though it is not stated explicitly. In fact, as far as I have been able to find out by experimenting, .param init has no user-visible effect unless the user names the sqlite_parameters table explicitly.

Digging a bit further, this seems to have been the behaviour since the .parameter command was introduced in checkin 1f9fa58541 a couple of years ago. (See the call to bind_table_init(p) in line 7158 of src/shell.c.in at the time of that checkin.

So my wild guess is that this line was added at a late stage of coding the feature, after the documentation had already been written. And ever since, people have just assumed one needs to run .param init.

Using proposed by Richard workaround
sqlite> .param set @vvv "'+1+2+3'"
sorry, but is not intuitively > understandable why to do it, especially if this shell's behavior isn't documented.

It is, in fact, documented:

Text bounded by a pair of single-quotes or double-quotes is treated as a single argument, with the quotes stripped. Within a double-quoted argument, traditional C-string literal, backslash escape sequence translation is done.

That is pretty clear to me, though admittedly, C-style quoting may not be for the faint of heart. As they say, the difference between a bug and a feature is whether it is documented or not. (I don't totally agree, but there is some truth to it.)

Look, I get it: Escape conventions are indeed very tricky, and we all fall victim to their various traps from time to time. It would be very nice indeed they could be avoided.

To that end, it would be very nice indeed if there were a way to avoid it. One way might be to add a third way of quoting to dot-commands: For example, if white space is followed by a # character, the rest of the intput line would be taken verbatim as the final argument (choice of trigger character subject to debate, of course).

Another way would be to introduce special quoting rules for .param, but that ruins consistency, and is better avoided, I think.

The whole point of .dot command .param is to emulate prepared statement to be make sure value will be passed to database engine as is, without modifications and what is most important - without trying to evaluate content(!!!).

Hmm, I thought it was just a convenient way to store a value that you might want to use repeatedly. Or, to let you easily rerun a complex query with different values, by changing the parameter and rerunning the exact same query. Your proposed rationale does not make a lot of sense to me.

Okay, good points there. Thanks for taking the time to explain.

My experience with MySQL is so limited, you might call it non-existent.

Now that I thought about it some more, what about choosing a slightly different pair of operators? By all means, pick -> for one, but what about something like ->- or => or ~> for the other? That way, PG (and MySQL?) users will suffer less mental friction when moving between databases. (I, for one, use both regularly, for wildly different purposes, but still. Oh, and I think the tilde and minus signs are easily distinguished in any programming font; less so on this forum.)

errors due to malformed json can't happen

Here is a counter example that I just ran on PG 9.6 over at sqlfiddle:

SELECT '{not valid json}'::json -> 'a';

But here it is the typecast to json that throws the error, not the -> operator. I.e.,

SELECT '{not valid json}'::json;

throws the same error (on PG 14 – for some reason, your sqlfiddle link doesn't open for me).

But I'm nitpicking. Personally, I have no problem with the somewhat different semantics anyhow. But I thought the issue worth mentioning, in case someone thinks it's a big deal.

I have used the -> and ->> operators in PG quite a bit, and find them extremely useful. However, the semantics is a bit different in PG: -> returns a json (or jsonb) object, while ->> returns text. Both require a json (or jsonb) object on the left, so errors due to malformed json can't happen. This difference could be confusing to some.

I haven't looked carefully, but it appears to be in the incubator branch of the main sqlean repository. At least, there is a file called src/array.c there.

It would appear that when you refer to ⟨schema⟩.⟨table⟩.⟨column⟩ and ⟨table⟩ is a valid alias name, then ⟨schema⟩ is ignored.

To get data out of your database using sqlite3, you just need to use a more robust format, such as csv: Either one of

sqlite3 -csv /product.sqlite " SELECT title,description,price FROM listings "
sqlite3 /product.sqlite ".mode csv" " SELECT title,description,price FROM listings "

should produce a csv file that can be reliably read by any conformant csv reader, including sqlite3 itself.

Most of the other output formats are intended for human consumption, not for computer programs to read.

For completeness' sake, on macOS the file system enforces UTF-8 file names. On older filesystems (HFS), it uses a decomposed normal form (so, e.g., ‘å’ becomes ‘a’ followed by a ring accent), albeit a somewhat macOS-specific version. (I am rather fuzzy on the details. The macOS native iconv program knows this encoding as UTF-8-MAC.)

On newer filesystems (APFS), UTF-8 is still enforced, but decomposed normal form is not. If you create a file using a GUI application using standard macOS libraries, the filename is created in decomposed form. But from the command line you can easily create files with name in composed form, and it will remain so on the filesystem. Further, GUI applications can open these too. The filsystem will enforce uniqueness of filenames modulo normal form, though. Say I have two files named blå.txt, one with the name in composed normal form and one in the decomposed form: They cannot coexist in the same directory. If I move one file into a directory containing the other, the other file will be replaced, even though a bytewise comparison of filenames indicate they are different.

So here be dragons …

Are you Googling from China perhaps?

No; I guess my google-fu has gone downhill a bit. But thanks.

By the way, there is a free alternative: SQLCipher. I don't know how good it is, or how it compares with SEE.

And just because I am curious, having never heard of SEE: What is it? It seems like an impossible thing to google.

I think that is what he was showing: He tried to execute sql code from a file simply by entering the filename from the interactive prompt.

To the OP: You might try .read c:/sqlite/my1st.sql instead.

I imagine something along the lines of

WITH
  pi (projid) AS (VALUES('PR0000019191')),
  LastEntries(projid,ml_insert)
  AS ( 
    SELECT projid max(insertdate)
    FROM pi
    JOIN Project_Keytask_and_Milestones
    USING(projid)
  )
SELECT ...;

might do the trick?

I ran a little experiment. There is indeed an anomaly there:

▶▶▶ .shell cat a.csv
,,,
1,2,4,8
42,,,
,9,3,1
▶▶▶ create table a(x,y,z,w);
▶▶▶ .import -csv -skip 1 a.csv a
a.csv:1: expected 4 columns but found 3 - filling the rest with NULL
▶▶▶ .nullvalue ø
▶▶▶ select * from a;
x   y  z  w
--  -  -  -
          ø
1   2  4  8
42
    9  3  1

Notice that the first line got imported with three blanks and a null.

If I add an extra character to the first line, either a comma or something else, the error goes away.

Additionally, the error is still there if I replace the first line with ,a,b,c. So it appears to be justy the initial comma that is problematic.

The patch I posted here was broken by the refactoring in checkin e548e9299d. Here is a new patch, in case anyone is interested.

Index: src/date.c
==================================================================
--- src/date.c
+++ src/date.c
@@ -989,17 +989,20 @@
 **
 ** Return a string described by FORMAT.  Conversions as follows:
 **
 **   %d  day of month
 **   %f  ** fractional seconds  SS.SSS
+**   %G  year 0000-9999 of current week's Thursday
 **   %H  hour 00-24
 **   %j  day of year 000-366
 **   %J  ** julian day number
 **   %m  month 01-12
 **   %M  minute 00-59
 **   %s  seconds since 1970-01-01
 **   %S  seconds 00-59
+**   %u  day of week 1-7  monday==1
+**   %V  week of year 01-53  week belongs to year with most of its days
 **   %w  day of week 0-6  sunday==0
 **   %W  week of year 00-53
 **   %Y  year 0000-9999
 **   %%  %
 */
@@ -1079,15 +1082,44 @@
         break;
       }
       case 'S': {
         sqlite3_str_appendf(&sRes,"%02d",(int)x.s);
         break;
+      }
+      case 'u': {
+        sqlite3_str_appendchar(&sRes, 1,
+                       (char)(((x.iJD+43200000)/86400000) % 7) + '1');
+        break;
       }
       case 'w': {
         sqlite3_str_appendchar(&sRes, 1,
                        (char)(((x.iJD+129600000)/86400000) % 7) + '0');
         break;
+      }
+      case 'V':  /* Fall thru */
+      case 'G': {
+        DateTime y = x;
+        computeJD(&y);
+        y.validYMD = 0;
+        /* Adjust date to Thursday this week:
+           The number in parentheses is 0 for Monday, 3 for Thursday */
+        y.iJD += (3 - (((y.iJD+43200000)/86400000) % 7))*86400000;
+        computeYMD(&y);
+        if( zFmt[i]=='G' ){
+          sqlite3_str_appendf(&sRes,"%04d",y.Y);
+        }else{
+          /* borrowed code from case 'j' */
+          sqlite3_int64 tJD = y.iJD;
+          int nDay;
+          y.validJD = 0;
+          y.M = 1;
+          y.D = 1;
+          computeJD(&y);
+          nDay = (int)((tJD-y.iJD+43200000)/86400000);
+          sqlite3_str_appendf(&sRes,"%02d",nDay/7+1);
+        }
+        break;
       }
       case 'Y': {
         sqlite3_str_appendf(&sRes,"%04d",x.Y);
         break;
       }

That seems to have done the trick! (And a one line fix, to boot.)

I have been bisecting a bit, using my last example in the quoted post:

bisect complete
  1 BAD     2021-09-22 14:26:22 96610cc8251ad4ff
  3 BAD     2021-09-21 20:03:05 4a3f98ad0dd2a8c0
  5 BAD     2021-09-21 19:19:28 a1c7f7f8e1b46440 CURRENT
  4 GOOD    2021-09-21 17:26:23 2c0e7ae541e9ecb8
  2 GOOD    2021-09-18 16:15:54 99d6bb22e8735681

From my brief reading of the code, I can't for the life of me see where the number of escaped double quotes in the file argument can have any impact.

Replacing \" by \042 does seem to help, though.

Thanks; I will experiment a bit further. Just one quick note, though: tail -r works fine on macOS. It does not on linux, which I assume is where you ran your experiment. On (most? all?) linux system you have the tac command that does the same thing: It collects lines from stdin and outputs them in reverse order.

So no, I don't buy the hypothesis that my problem arises from a failed pipeline.

I cooked up a simpler example. I think it shows more clearly that the problem does not arise from the complexity of the pipeline nor from any error in the pipeline, but from the presence of the backslash-escaped double quote.

⬥ cat tt.csv
,irrelevant,
"2021-10-02",some,data
,also,irrelevant
⬥ sqlite3 tt.db
SQLite version 3.37.0 2021-09-24 16:38:15
Enter ".help" for usage hints.
sqlite> create table tt(date,x,y);
sqlite> .import -csv "|grep '^\"2' tt.csv" tt
sqlite> select 42;
   ...>

⬥ sqlite3 tt.db
SQLite version 3.37.0 2021-09-24 16:38:15
Enter ".help" for usage hints.
sqlite> select * from tt;
2021-10-02|some|data
sqlite>

⬥ sqlite3 tt.db
SQLite version 3.37.0 2021-09-24 16:38:15
Enter ".help" for usage hints.
sqlite> .import -csv "|grep '^.2' tt.csv" tt
sqlite> select * from tt;
2021-10-02|some|data
2021-10-02|some|data
sqlite>

Edited to add an even simpler example. It seems that an odd number of occurrences of \" within the double quoted argument is the crux here:

⬥ sqlite3 tt.db
SQLite version 3.37.0 2021-09-24 16:38:15
Enter ".help" for usage hints.
sqlite> drop table tt;
sqlite> create table tt(x);
sqlite> .import -csv "|echo '\"hi there\"'" tt
sqlite> select * from tt;
hi there
sqlite> .import -csv "|echo '\"hi there\"' | tr -d '\"'" tt
sqlite> select * from tt;
   ...>

(I expect you need to double that backslash.)

The backslash is there to escape the double quote, so that the resulting argument following -csv will be

|iconv -f l1 < trans.csv | grep '^"20' | tail -r

That string, in its turn, should be parsed by /bin/sh (with the initial | removed) so that the argument that grep sees is

^"20

meaning a line beginning with the three characters "20.

And indeed, the data winding up in the database file is consistent with my expectations, so I think I got the number of backslashes right.

This is a bit odd:

⬥ sqlite3 foo.db
SQLite version 3.37.0 2021-09-24 16:38:15
Enter ".help" for usage hints.
sqlite>
sqlite> .import -csv "|iconv -f l1 < trans.csv  | grep '^\"20' | tail -r" incoming
sqlite> select 42;
   ...>

That's a secondary prompt string, indicating that the shell is waiting for more input. I haven't found any way out of it other than to hit Crrl-D, exiting the shell.

Interestingly, when I reenter the shell, I find that the import has completed successfully.

Is it the complicated form of the .import line that throws it off? But if so, why does it present a primary prompt after that line? Isn't the parser completely reset after each line that it parses successfully?

I could add that incoming is a view with an INSTEAD OF INSERT trigger. That ought to be irrelevant; but I suppose I could try with a copy of the database in which incoming has been replaced by a regular table.

That just loads SQL code. And SQLite dot commands, I think. But it can't do what you can't do from the command line.

Edit: Oops, that was mistaken. Sorry.