SQLite Forum

3.33.0 column mode breaks -noheader in CLI

3.33.0 column mode breaks -noheader in CLI

(1) By anonymous on 2021-08-30 17:33:56 [link] [source]

SQlite release 3.33.0 change 6(b):
seems to break the CLI -noheader behavior from previous versions.

That is, using .mode column in the input renders the CLI -noheader inop when specified.  This behavior is different from previous versions and may be related to the 'automatically turns ".header" on if it has not been previously set' in change 6(b) above.

My input SQL:
.mode column
select type, name from sqlite_master limit 2

sqlite3-3.35.3 -noheader test.db < test.sql produces
type   name
-----  ----
table  foo
table  bar

whereas 3.29.0 (at least) removes the header as desired.

This change has unfavorable impact when accumulating sqlite3 CLI output from multiple invocations without duplicating headers in subsequent calls.  Being able to just disable headers with -noheader while allowing the input to use different output formats (e.g. .mode csv, .mode column, etc.) is flexible.


(2) By anonymous on 2021-09-07 21:49:27 in reply to 1 [link] [source]

I've confirmed this changed behavior remains in 3.36.0 and originated in 3.33.0 (as 3.32.3 behaves as previously).

Furthermore, simply disabling lines 8496-8498 in shell.c.in:

      if( (p->shellFlgs & SHFLG_HeaderSet)==0 ){
        p->showHeader = 1;

will restore the previous functionality (wrt -noheader) while maintaining the auto-column-width feature added in 3.33.0.
See https://www2.sqlite.org/cgi/src/info/2827c0a186596299

Is there anything else I can do help with addressing/fixing this issue?


(3) By anonymous on 2021-09-08 23:51:59 in reply to 1 [link] [source]

Have I done something wrong in reporting this issue, or do I just need to be patient?  Any acknowledgement (with disposition) would be appreciated.


(4) By Larry Brasfield (larrybr) on 2021-09-09 02:30:28 in reply to 3 [link] [source]

You did not do anything meriting the label "wrong". And you've been patient enough, I think. Your issue seemed muddled to me at first, (but not upon a closer reading now), so I passed on it 9 days ago due to being busy.

I will reply substantively to your post #1.

(5) By Larry Brasfield (larrybr) on 2021-09-09 02:41:33 in reply to 1 [link] [source]

I think that there are one or two solutions for this issue that do not require backing out the semi-automatic header enable (which is sensible, usually.)

One solution, perhaps useful in the short term, is to put ".header off" just before your ".mode column" command.

I would argue that when -noheader is specified among the sqlite3 invocation arguments, that should have the same effect as the insertion of ".header off" that I suggest above. There may be a reason for this not be be done for the next release, so I do not yet count a revision to effect this as a solid second solution.

The disposition for now is: We will consider your problem report and determine whether a code change is warranted.

(6) By anonymous on 2021-09-09 19:50:57 in reply to 5 [link] [source]

Thank you for your response.

If I may provide any additional information or amplify my advocacy to return to previous behavior wrt -[no]header, please let me know.

SQLite is wonderful; thanks to everyone involved, but especially the developers.

Thanks again.

(7) By Larry Brasfield (larrybr) on 2021-09-09 20:39:45 in reply to 6 [link] [source]

Would you not consider an adequate solution to be that automatic header display (for those modes that do so) would be suppressed when -noheader is given on the sqlite3 command line?

As for advocacy, please rebut these reasons for usually enabling headers for columns mode:

Without the column names and particularly the ----- ... line appearing above the row output, distinguishing column values from the presentation can become ambiguous when there are few rows and the values contain arbitrary whitespace. The default for columns mode makes one-liner display of a table's values more convenient and unlikely to yield a confounding result.

Your stated desire to make convenient the concatenation (and subsequent interpretation, presumably) of multiple table displays in columns mode is unavailing as a reason to back out the feature because it is unlikely that tables will be rendered with the same column widths, leading to another source of ambiguity.

(8) By Larry Brasfield (larrybr) on 2021-09-10 01:51:20 in reply to 6 [link] [source]

If I understand your use case and recent disappointment with it, the behavior you regret is now avoided by checkin https://sqlite.org/src/info/733b7d4f95a4e2f0 . Unless Richard dislikes it for some yet-to-be-fathomed reason, it should appear in the next release.

I'm still interested in your argument for eliminating auto-headers if it survives this alternative remedy.

(9) By anonymous on 2021-09-10 18:47:10 in reply to 7 [link] [source]

> automatic header display […] would be suppressed when -noheader is given on the sqlite3 command line?

Yes,  I would consider that adequate, wrt to -noheader functioning as before in column mode.

(10) By anonymous on 2021-09-10 19:18:38 in reply to 8 [link] [source]

> I'm still interested in your argument for eliminating auto-headers

Mainly because this is a change in default behavior from previous versions, and I know we all appreciate the SQLite philosophy of backwards compatibility and max/high capability.  And, previous versions already had various ways of turning on/off headers – which was separated from how to format the result columns (.mode, -csv, etc.).  Now, depending on the .mode (or -mode?), there is a connection between result column formats and the presence of headers.  It seems like the desired behaviors we both cite were available with previous capability.

I should also clarify my use case was accumulating output across multiple invocations of the CLI, against *multiple databases* – not multiple tables within a database.   That accumulation was easily accomplished by a sequence of CLI invocations using the same SQL input, with only tweaking the CLI options to get the desired result of just one set of headers.  This capability worked for multiple column modes (e.g. csv, column) specified in various ways (e.g. .mode or -mode).  For mode column accumulations, it might be necessary to explicitly set column widths in the SQL input – but that was manageable.  To be clear, I like the .mode column auto-width feature – just not at the expense of -[no]header and the linking of header presence with result column formats.

As an aside, I find it interesting the modification involved adding code rather than removing – to restore previous capability regarding headers -- while retaining the now default auto-width of column mode (which I like).  I thought previous versions had all the desired capability, but perhaps I’m missing a use case.  But now I’m nit-picking…

I’ve been using SQLite for a non-profit educational and public welfare use for ~5 years with 100s of millions of rows, and have found SQLite to be a gem.  I like the approach, the philosophies (recently listened to Hipp podcast, have read many of the advocacy/explanatory web pages, etc.), capability, performance, and the flexibility.  And of course I very much appreciate and admire the work of the developers, even if y’all can be a bit prickly sometimes.  :-)

Thanks for asking, and responding, and for your work on SQLite.  Have a great weekend.


(11) By Larry Brasfield (larrybr) on 2021-09-10 20:48:25 in reply to 10 [link] [source]

(On argument for eliminating auto-header behavior for certain output modes:)

Mainly because this is a change in default behavior from previous versions

It is a change, albeit one with rare impact for the reasons I stated. The argument against change carries more weight before the change has been released. Now, over 15 months later, that argument must be balanced against the guessed impact of changing it back to the pre-June-2020 behavior for people who may unknowingly rely on the auto-header feature.

... we all appreciate the SQLite philosophy of backwards compatibility ...

You should know (or "appreciate" ;-) that the value of backwards compatibility for the SQLite library is taken extremely seriously when changes are considered. For the CLI shell, that value is still given weight, but much less so than for the library. We consider the shell to be a convenience tool among its other virtues. For outputs that are generally meant for human rather than programmatic consumption, strict backward compatibility may be sacrificed for convenient invocation. (Hence, my "one-liner" reference earlier.)

... adding code rather than removing – to restore previous capability regarding headers ...

The reason for that is to retain invocation convenience (and 15-month-past backward compatibility) but not frustrate those who (like yourself) attempt to override the "default" columns output.

I’ve been using SQLite [a lot.] ...

We try to keep you all happy or, failing that, to approach that state. I had the same sort of history with SQLite and appreciation of it.

I very much appreciate ... even if y’all can be a bit prickly sometimes.

There are few if any mean bones to be found among the project members. It would probably be best to ascribe occasional abruptness or perhaps overly-pointed remarks to fatigue and pressure of other tasks.

(12) By Scott Robison (casaderobison) on 2021-09-10 22:59:45 in reply to 10 [source]

As for "prickly sometimes", you may already know this, but it isn't always clear to people just who is officially a member of the team speaking for the team, and who is just an opinionated person with varying degrees of bed side manner quality.

For that reason, it might be good for those who really do speak for the project to have some identification of that status. Then when I fly off the handle and insult someone's parentage*, they can safely ascribe my opinions to myself and not the project.

I do not mean to imply that non-team personnel shouldn't chime in and try to help! I have found that multiple sources of information can be helpful, because if I do not understand the first, I might understand the next. Just that it is useful to be able to filter out opinions of people that do not speak for the project from those who do. If I tell you "feature suggestion X is stupid and should never be implemented" it carries approximately no weight. If drh loves it and implements it, who cares what I think?

*I have no plans to insult anyone's parentage.

(13) By anonymous on 2021-09-13 19:20:24 in reply to 8 [link] [source]

> checkin https://sqlite.org/src/info/733b7d4f95a4e2f0

After applying these changes to 3.36.0 I can confirm that -noheader is again functional.  Thanks!