Hello, On 2016-03-14 22:23, James K. Lowden wrote: > [...]
Thank you for expanding my list of inconsistencies and things which can be done in an other manner, with good reasons why ``other'' could become ``better''. A rationale for my question was born while trying to fix rather obvious inconsistencies described in http://www.mail-archive.com/sqlite-users%40mailinglists.sqlite.org/msg07399.html, pts 2. & 4.: CREATE TABLE tttest (col_num NUMERIC); INSERT INTO tttest VALUES ...; SELECT CAST(col_num AS NUMERIC) FROM tttest; In the following table, ``...'' means 17 zeroes, '123' --- TEXT value, 123 --- INTEGER value: +--------+--------------------------+------------------------+ |VALUE |col_num |CAST(col_num AS NUMERIC)| +--------+--------+-------------+---+-----+-------------+----+ | |is |IMHO shall be|ok?|is |IMHO shall be|ok? | +--------+--------+-------------+---+-----+-------------+----+ |'9...1' | 9...1 | 9...1 |OK |9...1|9...1 |OK | |'9...1 '| 9...0 | 9...1 |-- |9...0|9...1 |-- | |'9...1x'|'9...1x'|'9...1x' |OK |9...0|9...1 |-- | +--------+--------+-------------+---+-----+-------------+----+ 2nd row: why REALs can have trailing spaces, while INTEGERs cannot? 3rd row: why REALs can have trailing trash, while INTEGERs cannot? While fixing, I spotted a problem mentioned by you: > 2) sqlite> select cast('100000000000000000000' as int); > cast('100000000000000000000' as int) > ------------------------------------ > 7766279631452241920 It would be: 1. left as is, i.e. CAST(manydigitstext AS INTEGER) == MAX(TEXT2INT(manydigitstext)%2^64,LARGEST_INT64) --- Clemens Ladisch' opinion; 2. CAST(manydigitstext AS INTEGER) == {SMALLEST_INT64,LARGEST_INT64} --- your opinion as I understood you well; 3. extremely (even exaggeratedly) adjusted with the doc's ``the longest possible prefix of the value that can be interpreted as an integer number is extracted'' and ignoring digits which cause overflow --- the worst case scenario, as it could introduce many new inconsistencies. -- best regards Cezary H. Noweta
