hi,

there is currently an issue with sqlite when compiling with clang on a ppc64le system. this is not an issue in clang, and it is not a miscompilation.

ppc64le is little-endian, not big-endian, but when compiled with clang the endianness is detected incorrectly because of differences in the defines present. this results in corrupted databases, with all of the data byte swapped. i noticed this while trying to find and fix an issue in firefox, which this issue is the root cause of. you can see the issue when comparing a corrupted sqlite database (when compiled with clang) to an uncorrupted one (when compiled with gcc):

~ $ file /mnt/gentoo/home/linear/.mozilla/firefox/1utp25c9.default-release/content-prefs.sqlite
/mnt/gentoo/home/linear/.mozilla/firefox/1utp25c9.default-release/content-prefs.sqlite: SQLite 3.x database, user version 4, last written using SQLite version 3041002, page size 32768, file counter 7, database pages 7, cookie 0x6, schema 4, UTF-8, version-valid-for 7
~ $ file ~/.mozilla/firefox/5f4q1fc9.default-release/content-prefs.sqlite.corrupt 
/usr/people/linear/.mozilla/firefox/5f4q1fc9.default-release/content-prefs.sqlite.corrupt: SQLite 3.x database, last written using SQLite version -362402304, page size 32768, file counter 16777216, database pages 16777216, cookie 0, schema 0, largest root page 16777216, unknown 0 encoding, vacuum mode 16777216, version-valid-for 16777216
~ $ ./endian-swap 3041002
-362402304
~ $ 

there are two bugs in firefox that appear to describe this same issue.

Chimera Linux and Alpine Linux both currently carry a patch to fix this in firefox's vendored version of sqlite, since they both compile the package with clang.

a cursory search shows that several other people have encountered this issue with other packages.

the version in firefox is somewhat out of date, but i am able to confirm independently that the issue exists as of sqlite 3.43.0. if sqlite is built gcc, then i am able to write to open a database and run commands as expected:

mihari:~$ sqlite3 test.db
SQLite version 3.43.0 2023-08-24 12:36:59
Enter ".help" for usage hints.
sqlite> CREATE TABLE sample (id INTEGER PRIMARY KEY, name TEXT);
sqlite> INSERT INTO sample (name) VALUES ('John'), ('Doe');
sqlite> .exit
mihari:~$ file test.db 
test.db: SQLite 3.x database, last written using SQLite version 3043000, file counter 2, database pages 2, cookie 0x1, schema 4, UTF-8, version-valid-for 2
mihari:~$

if it is compiled with gcc, then i run into an error:

mihari:~$ sqlite3-clang test2.db
SQLite version 3.43.0 2023-08-24 12:36:59
Enter ".help" for usage hints.
sqlite> CREATE TABLE sample (id INTEGER PRIMARY KEY, name TEXT);
Parse error: database disk image is malformed (11)
sqlite> 

looking at the sqlite3 code, the problem appears to be that if __ppc__ is defined, then sqlite assumes a big endian system. clang defines __ppc__ on this architecture, but gcc does not:

mihari:~$ ./definetest-gcc 
__ppc__ is not defined
mihari:~$ ./definetest-clang 
__ppc__ is defined
mihari:~$

as a result, when compiled with gcc, sqlite falls back to runtime detection and (correctly) determines that the platform is little-endian, and exhibits the expected behavior. but when compiled with clang, the byte ordering is hard-coded, incorrectly.

i would suggest either removing the __ppc__ check entirely (noting that this check, as is, would also do the wrong thing on ppc32le, regardless of whether clang or gcc is used) or adding a check for __ppc64__ and then, for both, proceeding to check for __LITTLE_ENDIAN__ or __BIG_ENDIAN__.