sqlite3: .width counts bytes, not characters

classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

sqlite3: .width counts bytes, not characters

Software
Hi

I use sqlite3 (sqlite3 --version = "3.11.0 2016-02-15 17:29:24 3d862f207e3adc00f78066799ac5a8c282430a5f" on Ubuntu 16.04.6 LTS) for formatted output. '.width' does not behave as I expected when non-ASCII Unicode characters are printed. It seems that .width counts bytes and not characters. See test case below (in case the email does not display non-ASCII characters properly: the second INSERT has 6 characters '3/4' as first field). Is there an option to format the output nicely in columns when non-ASCII characters may be included?

Test case:
echo "CREATE TEMPORARY TABLE width_test (data, description);" > width_test.sql
echo "INSERT INTO width_test VALUES('aaaaaa', '6 ASCII letters');" >> width_test.sql
echo "INSERT INTO width_test VALUES('¾¾¾¾¾¾', '6 non-AScII letters');" >> width_test.sql
echo ".mode col" >> width_test.sql
echo ".width 6 20" >> width_test.sql
echo "SELECT * FROM width_test;" >> width_test.sql
echo ".width 9 20" >> width_test.sql
echo "SELECT * FROM width_test;" >> width_test.sql
echo ".width 12 20" >> width_test.sql
echo "SELECT * FROM width_test;" >> width_test.sql
cat width_test.sql | sqlite3 "test.db"


Best regards

Andreas
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: sqlite3: .width counts bytes, not characters

Jose Isaias Cabrera-4

[hidden email], on Monday, March 9, 2020 04:02 PM, wrote...

>
> Hi
>
> I use sqlite3 (sqlite3 --version = "3.11.0 2016-02-15 17:29:24
> 3d862f207e3adc00f78066799ac5a8c282430a5f" on Ubuntu 16.04.6 LTS) for
> formatted output. '.width' does not behave as I expected when non-ASCII
> Unicode characters are printed. It seems that .width counts bytes and not
> characters. See test case below (in case the email does not display
> non-ASCII characters properly: the second INSERT has 6 characters '3/4' as
> first field). Is there an option to format the output nicely in columns
> when non-ASCII characters may be included?
>
> Test case:
> echo "CREATE TEMPORARY TABLE width_test (data, description);" >
> width_test.sql
> echo "INSERT INTO width_test VALUES('aaaaaa', '6 ASCII letters');" >>
> width_test.sql
> echo "INSERT INTO width_test VALUES('¾¾¾¾¾¾', '6 non-AScII
> letters');" >> width_test.sql
> echo ".mode col" >> width_test.sql
> echo ".width 6 20" >> width_test.sql
> echo "SELECT * FROM width_test;" >> width_test.sql
> echo ".width 9 20" >> width_test.sql
> echo "SELECT * FROM width_test;" >> width_test.sql
> echo ".width 12 20" >> width_test.sql
> echo "SELECT * FROM width_test;" >> width_test.sql
> cat width_test.sql | sqlite3 "test.db"

On Windows is working as expected.  Or maybe, I don't understand the problem, which is something that could be very likely.
12:10:51.82>sqlite3
SQLite version 3.24.0 2018-06-04 19:24:41
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> CREATE TEMPORARY TABLE width_test (data, description);
sqlite> INSERT INTO width_test VALUES('aaaaaa', '6 ASCII letters');
sqlite> INSERT INTO width_test VALUES('¾¾¾¾¾¾', '6 non-AScII letters');
sqlite> .mode col
sqlite> .width 6 20
sqlite> SELECT * FROM width_test;
aaaaaa  6 ASCII letters
______  6 non-AScII letters
sqlite> .width 9 20
sqlite> SELECT * FROM width_test;
aaaaaa     6 ASCII letters
______     6 non-AScII letters
sqlite> .width 12 20
sqlite> SELECT * FROM width_test;
aaaaaa        6 ASCII letters
______        6 non-AScII letters
sqlite>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: sqlite3: .width counts bytes, not characters

Keith Medcalf
In reply to this post by Software
>I use sqlite3 (sqlite3 --version = "3.11.0 2016-02-15 17:29:24
>3d862f207e3adc00f78066799ac5a8c282430a5f" on Ubuntu 16.04.6 LTS) for

Have you tried version more recent than 4 years and 1 month old?  
I think some of these issues may have been fixed in the last couple of years.

--
The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume.



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: sqlite3: .width counts bytes, not characters

Richard Hipp-3
On 3/9/20, Keith Medcalf <[hidden email]> wrote:
>>I use sqlite3 (sqlite3 --version = "3.11.0 2016-02-15 17:29:24
>>3d862f207e3adc00f78066799ac5a8c282430a5f" on Ubuntu 16.04.6 LTS) for
>
> Have you tried version more recent than 4 years and 1 month old?
> I think some of these issues may have been fixed in the last couple of
> years.

I think the enhancement is here:
https://sqlite.org/src/timeline?c=ed0842c156ab1a78

That would correspond to version 3.20.0.

--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: sqlite3: .width counts bytes, not characters

Software
In reply to this post by Software

> I think the enhancement is here:
> https://sqlite.org/src/timeline?c=ed0842c156ab1a78
>
> That would correspond to version 3.20.0.
>
> --
> D. Richard Hipp

Thank you. The upcoming Ubuntu LTS release (20.04) includes sqlite version 3.31.1, so the issue should disappear soon also for me.
@Keith: I aim to stay with the packages in the distribution; to avoid incompatibilities and as I have to maintain 7 linux installations for me and my family.

Best regards

Andreas
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users