https://www.sqlite.org/fileformat2.html#record_format
The storage type of each record is given by an integer. And in the current format, all non-negative integers are used. To me of course that begs the question: Are negative serial types an option? That would of course mean a full 9 bytes per field just to hold the type (it's a varint) so it's an annoying overhead, but is it an option? Versions earlier than the one that implements this wouldn't be able to read it. But then we've already had additions like "without rowid" where any old version of the library isn't going be able to understand a new database with a without rowid table. And if a new database doesn't use the new negative serial types then the resulting file is still perfectly readable by older versions. I suppose the issue though is that rules are already in place for determining the affinity of a column and those rules currently give a value for <anything>, so if you create a new type of "decimal(a, b)" then an old version, instead of saying "I don't know what that is" will say "ok, that's numeric and I internally store it as a float" and give bad results instead of no results. I suppose then you could then expand on the "without rowid" model, and designate new features at the end of the create table text so that old versions don't <think> they know what to do and would give an error... create table foo (field1 numeric(5, 2) primary key) without rowid with numeric with someNewFeature; An old database where someone had already declared something as "decimal(5,2)" wouldn't have the "with decimal" in the create table text, so you could still keep the historical affinity and not mix it up with something intended for the new "decimal" type. ...but that gets ugly and complicated quick. Anyway, sorry for my rambling, I'll go hide in a corner now. -----Original Message----- From: sqlite-users <sqlite-users-boun...@mailinglists.sqlite.org> On Behalf Of Thomas Kurz Sent: Wednesday, June 12, 2019 1:05 PM To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org> Subject: Re: [sqlite] Should SQLite distinguish between +0.0 and -0.0 on output? > It would also be a file format change, rendering about 1e12 existing database files obsolete. Maybe, but maybe there could be some clever implementation which doesn't break compatibility. I don't know about the exact internals of how SQlite stores values in the file. But I think there must be some identifier that tells whether a value is binary, integer, or float. Wouldn't it be possible to store both values, binary float and decimal float, in such a way that older versions would just read the binary float and ignore the additional data? Then, newer versions could read either, according on whether PRAGMA DECIMAL_MATH=TRUE is set or not. Just an idea, don't know whether this would be feasible or not. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users