You "put" a ieee754 floating point double. If you retrieved an ieee754 floating point double, you would get back that which you put! The fact that internally SQLite3 stored it as a 3 (integer, token, string, whatever) is irrelevant. You "gets" what you "puts", as long as what you "putted" is the same as what you "asked" for. Are you complaining that you can "puts" anything anywhere, or that once "putted" you can "ask" for whatever format you want? Or are you complaining about how the "column type gibberish" is interpreted if you do not stick to correct affinities? Or that data conversions occur on the "putted" value when it is stored or that it may occur if you "asks" for a different format than what is stored?
The rules for the parsing of the gibberish are here by the way: https://www.sqlite.org/datatype3.html The easy solution to those issues are "don't do that" ... and if you don't trust that someone else didn't "don't do that" to your applications database, then you better check what you are doing when you "asks" for data... (And the command line shell is a bad way to do things because it does can only "puts" text (which must always be converted), and can only display text after conversion from whatever format the data was "putsed" and "stored" with). BTW, SQL delimits strings with single-quotes. Double-quotes mean that what is between them is an identifier. For historical reasons if something which is double-quoted cannot be resolved to an identifier in the context it was used, the assumption is that the coder (cuz programmers would never make such an error) is a dodo and *meant* to use single-quotes but was typing in all-caps at the time and put in the wrong character ... --- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. >-----Original Message----- >From: sqlite-users [mailto:sqlite-users- >[email protected]] On Behalf Of Thomas Kurz >Sent: Saturday, 30 June, 2018 01:04 >To: SQLite mailing list >Subject: Re: [sqlite] column types and constraints > >> I don't disagree, but this means we lose sight of the important >point >>that, if you distill the problem to just "INTEGER", then sure, it >looks >> silly, and sure, you can fix it with internal auto-CHECKing, but in >> SQLite the type affinity INTEGER stands father to LONGINT, >MEDIUMINT, > >Ok, I understand your argumentation now. But I don't think >distinguishing different integer types or strings with different >length declarations would be mandatory. IIRC, they were introduced to >save memory in times when database files were actually just a >sequence of fixed-length records/structs. SQLite on the other hand >always uses the least memory-consuming way for storing data anyway. > >But I must admit that a disctinction between alternatives of the same >"base type" was something I didn't have in mind, so consider me >convinced ;-) > >> COLUMN xy VARCHAR(50) >> COLUMN xy MEDIUMTEXT >> COLUMN xy BIT >> COLUMN xy DECIMAL(5,2) >> Should those raise errors? Because to SQLite those are the exact >same >> gibberish as: > >I would appreciate if SQLite raised an error each time the >declaration mismatches the interpretation. In your example, all >declarations are well-known SQL. And, if I understand correctly, >SQLite treats each of those as NUMERIC. So: > >a) Yes, error, as I indent to store strings, but SQLite uses numbers. >b) Yes, error, same case. >c) Preferably yes, but not mandatory, as BIT is compatible to NUMERIC >d) No, as DECIMAL ist compatible to NUMERIC. > >Let me explain my problem, something which happens to me quite often >as many programming languages name the thing "string" that DBMSs want >to have named "text" (why the hell??): > >CREATE TABLE a (col1 STRING); >INSERT INTO a (col1) VALUES ("3.0"); >SELECT * from a; >---> 3 // this should never happen!! > >Instead of raising an error on the column definition (which would be >most elegant imho), it would be perfectly ok if SQLite treated the >string ("text") that I provide in the insert-statement (double >quotes) actually as a string and did not try to convert it. Or, more >clearly spoken, if it recognized that the conversion is not >reversible to the original string. > >Currently, there is no warning whatsoever for me to lose data after >having used an invalid column declaration. Meanwhile I'm more aware >of that, but the first time I had spent hours trying to figure out >what's going wrong until I found the problem. > >INSERT INTO a (col1) VALUES (3.0); >SELECT * from a; >---> 3 // I would accept that as I have provided a number > >Maybe, this could be a compromise? > >In your previous post, you wrote: > >> [...] >> VARCHAR(3) >> Truncate the string to "MAM" in MySQL, without an error at all, >just kill some data and move on! > >My example is exactly the same. SQLite continues without an error >losing me data. There is no way getting it back. Even this don't >work: > >SELECT CAST(col1 AS TEXT) FROM a; >---> 3 // <> "3.0" !! > >_______________________________________________ >sqlite-users mailing list >[email protected] >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list [email protected] http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
