SQLite Forum

Strange parsing problem in the shell
Login

Strange parsing problem in the shell

(1) By Harald Hanche-Olsen (hanche) on 2021-10-01 15:28:04 [link] [source]

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] [source]

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) By Harald Hanche-Olsen (hanche) on 2021-10-01 17:56:19 in reply to 2 updated by 3.1 [link] [source]

> (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` 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.

(3.1) By Harald Hanche-Olsen (hanche) on 2021-10-01 17:58:19 edited from 3.0 in reply to 2 [link] [source]

(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] [source]

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) By Larry Brasfield (larrybr) on 2021-10-02 01:07:29 in reply to 1 updated by 5.1 [link] [source]

> 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.

(5.1) By Larry Brasfield (larrybr) on 2021-10-02 01:27:06 edited from 5.0 in reply to 1 updated by 5.2 [link] [source]

> 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\> 
</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.
sqlite>

(5.2) By Larry Brasfield (larrybr) on 2021-10-02 02:47:37 edited from 5.1 in reply to 1 [link] [source]

This is a bit odd: ...

Here is a screen-scrape, just now taken: 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>

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: 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; ...> ; ...>

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: 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>

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) By Harald Hanche-Olsen (hanche) on 2021-10-02 07:12:19 in reply to 5.2 updated by 6.1 [link] [source]

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. But as I said, I will experiment some more and report back.

(6.1) By Harald Hanche-Olsen (hanche) on 2021-10-02 07:40:46 edited from 6.0 in reply to 5.2 updated by 6.2 [link] [source]

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>
```

(6.2) By Harald Hanche-Olsen (hanche) on 2021-10-02 07:56:20 edited from 6.1 in reply to 5.2 [link] [source]

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) By Harald Hanche-Olsen (hanche) on 2021-10-02 09:51:26 in reply to 6.2 updated by 7.1 [link] [source]

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.

(7.1) By Harald Hanche-Olsen (hanche) on 2021-10-02 09:55:48 edited from 7.0 in reply to 6.2 [link] [source]

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) By Larry Brasfield (larrybr) on 2021-10-02 22:25:18 in reply to 7.1 updated by 8.1 [link] [source]

I think this poor behavior was fixed by [this checkin today](https://sqlite.org/src/info/928c2a34986644d3).

Can you try it?

(8.1) By Larry Brasfield (larrybr) on 2021-10-03 02:18:03 edited from 8.0 in reply to 7.1 [link] [source]

... 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.

Can you try it?

(9) By Harald Hanche-Olsen (hanche) on 2021-10-03 18:39:23 in reply to 8.1 [source]

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