Strange parsing problem in the shell
(1) By Harald Hanche-Olsen (hanche) on 2021-10-01 15:28:04 [link]
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.
(2) By Larry Brasfield (larrybr) on 2021-10-01 16:26:02 in reply to 1 [link]
That ought to work, almost. (I expect you need to double that backslash.) I suggest using .print to see what your actual submission to the shell meta-command is. As for the continuation prompt: I am with you on expecting that the primary prompt after that hairy piping means that it at least completed (even if it did not do quite what you wanted.) My suspicion would be that you may have exposed a bug in the shell's own input source switching logic. Later today, I will take a look at some less hairy equivalent meta-commands and see what is going on. > Isn't the parser completely reset after each line that it parses successfully? Yes, for meta-command input. But the input source switch and restore may have become confused. It bears investigation. Please feel welcome and encouraged to add more detail to the working/failing boundary on this.
(3.1) By Harald Hanche-Olsen (hanche) on 2021-10-01 17:58:19 edited from 3.0 in reply to 2 [link]
> (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.
(4) By Larry Brasfield (larrybr) on 2021-10-01 18:23:54 in reply to 3.1 [link]
Upon closer reading, I agree. BTW, I have exercised pipe-style meta-command input quite a bit and have not seen the sort of strangeness you reported. So I look forward to seeing what is special about your input.
(5.2) By Larry Brasfield (larrybr) on 2021-10-02 02:47:37 edited from 5.1 in reply to 1
> This is a bit odd: ... Here is a screen-scrape, just now taken:<code> larry@Bit-Booger:~/SQLiteDev/LibTrunk$ ./sqlite3 SQLite version 3.37.0 2021-10-01 22:48:52 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. sqlite\> .import -csv "|iconv -f L1 \< pets.csv | grep '^M' | cat -" incoming sqlite\> .header on sqlite\> select * from incoming; Moses|dog|12 Meowser|cat|17 sqlite\> </code> I adjusted your inconv from encoding to reflect ones listed via -l, and replaced your "tail -r" with something that does not break the pipe (as happens with Ubuntu 20.04's tail which knows not the -r option.) However, using an equivalent to your posted .import pipe extravaganza, I get:<code> sqlite\> .import -csv "|iconv -f l1 \< pets.csv | grep '^\\"20' | tail -r" incoming tail: invalid option -- 'r' Try 'tail --help' for more information. \<pipe\>: empty file sqlite\> select * from sqlite_schema; ...\> ; ...\> </code> This appears to replicate your problem. There is a simple, near-term solution: Do not subject the CLI's piped-input-accepting commands to broken pipes. Meanwhile, I will investigate why such expectable input produces this strange result. (A 2nd work-around appended via edit:) It seems that output reaching a shared stderr stream may be involved in this problem:<code> sqlite\> .import "|cat /dev/null" junk \<pipe\>: empty file sqlite\> select * from sqlite_schema; sqlite\> .import -csv "|iconv -f L1 \< pets.csv | grep '^M' | tail -r 2\>/dev/null" incoming \<pipe\>: empty file sqlite\> sqlite\> </code> Note that an empty input pipe, by itself, does not trigger the misbehavior. It can be avoided by merely diverting the pipe-breaker's moaning away from the same stderr stream the CLI is using. So that would be another short-term work-around.
(6.2) By Harald Hanche-Olsen (hanche) on 2021-10-02 07:56:20 edited from 6.1 in reply to 5.2 [link]
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; ...> ```
(7.1) By Harald Hanche-Olsen (hanche) on 2021-10-02 09:55:48 edited from 7.0 in reply to 6.2 [link]
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.
(8.1) By Larry Brasfield (larrybr) on 2021-10-03 02:18:03 edited from 8.0 in reply to 7.1 [link]
> ... I don't buy the hypothesis that my problem arises from a failed pipeline. The misbehavior was not caused *solely* by the broken pipe issue. It was due to a bug which was exposed by a broken pipe among other scenarios. That bug was fixed today, by [this checkin](https://sqlite.org/src/info/928c2a34986644d3). Can you try it?
(9) By Harald Hanche-Olsen (hanche) on 2021-10-03 18:39:23 in reply to 8.1 [link]
That seems to have done the trick! (And a one line fix, to boot.)