At 1:54 PM -0500 11/1/05, [EMAIL PROTECTED] wrote:
Please let me restate the proposed change: I desire to change SQLite so that it no longer distinguishes between integer and real. The two concepts are merged into a unified "numeric" type.
And because all number values are of the same type, they need to always be treated in the same way. A division should return a result that includes the fractional part, if there is one, regardless of the particular representation of the operands.
I'm not a DB person by trade, I'm a programmer. I have grown to love SQLite for it's very 'C' like philosophy of making the underlying mechanisms transparent.
Part of that is the manifest typing, which lets me control exactly what's going into the database, and lets me optimize that for best efficiency.
My $0.02 is that combining INTEGER and REAL is the wrong direction. It takes information away, information that sqlite is perhaps unique in preserving.
It seems that the column affinity mechanism already holds the answer to this problem. If a column could have REAL affinity, then whatever data was stored in that column would have an opportunity to act according to the SQL standard, without losing metadata about the original data.
http://www.sqlite.org/datatype3.html says:
A column that uses INTEGER affinity behaves in the same way as a column with NUMERIC affinity, except that if a real value with no floating point component (or text value that converts to such) is inserted it is converted to an integer and stored using the INTEGER storage class.
I propose a similar affinity be added called "REAL", that would behave the same as NUMERIC, except that INTEGERs would be converted to REAL.
Also, I'm surprised that the column affinity isn't applied when the data is read from the database vs inserted into the database, but that's a separate topic.
-pmb
