Incorrect. All data is stored as NULL, integer, float, text or blob. The
"declared type" is taken as a hint from the designer in respect to the kind of
values he intends to store there. The result of the hint is called an
"affinity", i.e. the kind of data the field "likes to" store. If the type of
the value presented differs from the affinity, it may be converted, if
losslessly and reversibly possible (e.g. '1' <=> 1); this is called "applying
affinity". Or, if not possible ('hugo' cannot be converted to a number), simply
stored without conversion. This is the sense in which SQLite is "typeless".
Even if the field has a declared type, any kind of value may be stored there
without error.
"Affinity" is a volatile property. It may be lost (by using a value in a
general expression) or gained (by using a cast or in the context of a
comparison). This is mainly for the benefit of users who for some reason or
other need to compare (text, e.g. a text constant) '1' with (integer, e.g. a
value from a field) 1 and have it come out equal. Note that constants have a
type but no affinity, so SELECT '1' = 1; returns 0.
-----Ursprüngliche Nachricht-----
Von: sqlite-users [mailto:[email protected]] Im
Auftrag von Don V Nielsen
Gesendet: Dienstag, 27. Dezember 2016 16:18
An: SQLite mailing list <[email protected]>
Betreff: Re: [sqlite] Possible bug with union and join.
Theory related question. I'm being argumentative, I know. But this issue is in
the same category as one discussed weeks ago.
SQLite is, in a sense, typeless. All data is stored as text (ignore blob).
Correct? It is when one casts a column to something other than text that
triggers SQLite to treat the text differently.
Disregarding auto-incremented key values, why have an integer key.
Even if the key value will only be numeric digits like 1, 255, 1024, etc...,
are they "truly" integers? If the value is not used in a mathematical formula,
why think of it as an integer? It is still just text...a string of ascii
digits... but still text. Is there something behind the scenes of how text data
comprised of numeric digits is stored?
Like the previous issue I suggested keeping the keys between tables the same
data type. The issue resolves itself. The same would be true, here. One table
has text which could be '1,10'. But in the other table, it is integer 1 & 10.
It could be text '1' & '10'. No type conversion problems.
I don't know. I would like to hear what others have to say.
dvn
On Sun, Dec 25, 2016 at 2:43 PM, Adrian Stachlewski
<[email protected]> wrote:
> Fortunately names of columns are much more transparent and documented
> in our internal specification. 'Id' was created only for example, but
> thanks for advice :)
>
> Adrian
>
> 2016-12-25 13:44 GMT+01:00 Simon Slavin <[email protected]>:
>
>>
>> On 23 Dec 2016, at 4:55pm, Adrian Stachlewski
>> <[email protected]>
>> wrote:
>>
>> > Id field in one table is defined as TEXT, because there are stored
>> > identifiers which can be numeric or text mostly like in the example
>> > ("4", "4,5", "10-1") (to be precise this map is created on the fly
>> > by concatenating some ids and names from another tables). In
>> > second table there are stored identifiers which are integer only.
>> > This ids means something entirely different, but there is one case,
>> > when table with date keeps ids from both tables. Unfortunately I
>> > cannot change input data - it is taken from some APIs using csv files.
>>
>> Okay. You’re wedded to a data format created by someone else. That
>> explains the problem.
>>
>> If you have the opportunity to rename your columns when you import
>> from the CSV files, I might recommend that you do not call the TEXT field
>> 'id'.
>> The convention for 'id' is for an INTEGER PRIMARY KEY and it might
>> confuse other people who see your database.
>>
>> Good luck with problem you posted about.
>>
>> Simon.
>> _______________________________________________
>> 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
_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___________________________________________
Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: [email protected]
This communication (including any attachments) is intended for the use of the
intended recipient(s) only and may contain information that is confidential,
privileged or legally protected. Any unauthorized use or dissemination of this
communication is strictly prohibited. If you have received this communication
in error, please immediately notify the sender by return e-mail message and
delete all copies of the original communication. Thank you for your cooperation.
_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
