SQLite Forum

output in column mode is truncated

output in column mode is truncated

(1) By Ondrej Dubaj (odubaj) on 2020-05-14 09:46:35 [source]

Description of problem:
When sqlite is in column mode, it truncates output of select query. It seems that sqlite estimates width of text column by using length of text value in the first returned row.

Version-Release number of selected component (if applicable):

How reproducible:

Steps to Reproduce:
1) Create a table containing text field and fill in some (text) values of different length.
2) Switch to column mode
3) Select the text column from the table and order it from the shortest to the longest. Now you should see, that the longer values are truncated.
4) Try reversing the order (from the longest to the shortest). Now you should see untruncated results.


sqlite3 test.sqlite << EOF
create table tab1(str text);
insert into tab1 values('sth');
insert into tab1 values('something a bit longer');
.mode column
select * from tab1 order by length(str);
select '--------------------------------';
select * from tab1 order by length(str) desc;

Actual results:

something a bit longer

Expected results:

something a bit longer 
something a bit longer

(2) By Larry Brasfield (LarryBrasfield) on 2020-05-14 13:09:39 in reply to 1 [link] [source]

This issue is a mismatch between your expectation and what the tool is intended to do. Columnar display with semi-fixed widths will never be able to avoid truncating some text fields. That display form is for allowing a quick look at the data (or some/most of it), not for preserving it. If you want to display it all, emit CSV (comma-separated values) and plunk it into a spreadsheet. (See the .once shell command.) These days, they allow absurd amounts of text to appear in single cells. But even then, choices have to be made as to display truncation versus expanding row or column sizes at the expense of diluting display of everything at once.

(3) By Keith Medcalf (kmedcalf) on 2020-05-14 18:09:21 in reply to 2 [link] [source]

Or output it as HTML and open it in a web browser.