I struggle with text or flexible typing:

CREATE TABLE Fruits ( fruit text, price text);INSERT INTO Fruits (fruit, price) VALUES('apple', '1.0'),('banana', '2.5'),('cherry', '8.00');

select * from Fruits where price = 1.00 or price = 8.00 or price = 2.50

-> no cherry... what happens here? I asked an AI but the answer did not help me.

what I think fixes my SQL is: select * from Fruits where price*1 = ...

I don't understand why the apple and banana show up in the results, given that '1.0'=1.0 and '2.5'=2.5 both return false (i.e., zero). There seems to be some unexpected and inconsistent implicit typecasting going on here.

Anyway, replacing price*1 by cast(price as numeric) in your fixed query makes the meaning more clear.

The price column of the table has TEXT affinity due to its definition.

That column is being compared (three times) with expressions that have no affinity.

So in order to compare them, the expression is converted to TEXT.

The three expressions are converted to their text representations: '1.0', '8.0', and '2.5'. Two of these match the text stored in the columns, and so those rows are selected. The other does not match as '8.00' != '8.0'.

Affinities and datatypes can be surprising. If you want to compare strings with numbers and avoid surprises, it is best to be explicit and either CAST the numbers to strings, or the strings to numbers.

Perhaps the following could be added after the description of the COLLATE operator:

See the Comparison Expressions of the Datatypes In SQLite3 document for detailed information on type conversions and sort order in the context of comparison operators.

found the answer here -> https://sqlite.org/datatype3.html

Since the affinity of column "price" is text, the comparison in the sql is a text comparison, i.e. the right hand side will be converted from numeric to text. And the string from numeric value 8.00 ( or 8 or ...) is "8.0" it is not equal to the text column value "8.00".

And the string from numeric value 8.00 ( or 8 or ...) is "8.0"

I am sure the SQLite developers decided based on some logic that the number 8.00, when converted to a string, should become '8.0' and not '8.00'. I would love to know that logic. Is it documented somewhere? I do not see it in https://www.sqlite.org/datatype3.html#type_conversions_prior_to_comparison

I would love to know that logic.

Stripping all superfluous trailing zeroes is standard practice in double-to-string conversions. The zero immediately after the dot is the obligatory exception, to disambiguate from the integer 8.

Now I'm getting outrageously nitpicky here, but the trailing zero isn't really obligatory, since sqlite parses 8. as a float. But writing it that way does seem to flout convention. And what do I know? Perhaps other implementations don't like a decimal point with no digits following. (I remember being puzzled the first time I saw something like .05, since conventions I grew up with, demanded at least one digit in front of the decimal marker (a comma in my part of the world).)

If you're talking about 8.00 as a "number" it's more accurate to write it as 1.0*2^3 (where 1.0 is a binary value) because that is how floating point numbers are stored internally by computers. From a mathematical perspective, this is indistinguishable from 8 or 8.0 or 8.00 or 8.00000000 etc.

ie. once you leave the realm of strings, trailing zeroes in a decimal number are not represented. This is not really a decision of the SQLite developers but rather a consequence of how modern computers work with real numbers.

It might be more accurate to say that, once you leave the realm of strings, a float we might write as "8.0" has rather a large number of zeroes in its representation. It's conventional to emit only one of those, since the meaning is the same as for all of them, or any in between: these will be represented as the same binary floating point value.

To render them with one zero is, I suppose, a decision of the SQLite developers, but it's a perfectly conventional decision, and anything else would be surprising.

This is one of a cluster of related reasons why monetary amounts are frequently stored as integers, such as representing one dollar as 1000, or even as 10,000, rather than as floating point. OP's application might not require this, but it was worth mentioning: among other reasons, a floating point value is lossy, since it cannot represent e.g. $1.10 precisely. Comparisons also become much easier.

You are of course perfectly correct that in mathematics, 1.0 and 1 and 1.0000... are indistinguishable, in the same way "one" and "1" are. The difference for floats is that the final one, with the ellipsis showing an infinite number of zeroes, is not representable, because the zeroes are finite.

My main purpose in commenting is to suggest representing monetary amounts as an integer, representing the smallest unit as 10, and letting the application code display 8000 as $8.00. Floats often seem like the correct way to do it, but it often turns out they are not. Why not 800? Division will truncate, using 8000 means you can round properly.

You simply need to decide if you're going to search by text or numeric.

You're entering text but searching by numeric, and '8.0' doesn't equal 8. You're comparing apples with bobby pins.

It would make more sense to put the price in your database as a numeric not text. This would resolve this issue. You could then find items below 5 which you can't currently do either.

And if we're giving database design advice, store the 8.00 as 800 credit units, never use floats for money. Just don't. Ever.

The problem with using floats for monetary values has been covered in great detail in many places. Here are some links worth reading:

https://stackoverflow.com/questions/588004/is-floating-point-math-broken

https://docs.oracle.com/cd/E19957-01/806-3568/ncg_goldberg.html
This one is very dense, but worth it if you're interested in the nitty gritty details.

https://stackoverflow.com/a/3730249/18157

https://www.h-schmidt.net/FloatConverter/IEEE754.html

That last one will break out any floating point number into an annotated explanation of the internal representation. It will show you exactly what's happening, including the Decimal Representation, the Value actually stored in float, the error due to conversion, and the Binary and hexadeximal Representation. Try entering some decimal values, like 0.1 to see the inability to represent it exactly.

The only values float/double can represent exactly are multiples and sums of powers of 2. So for example, 0.25 is exact, as is .375 and other such fractions. 0.1 -- not so much.

The only values float/double can represent exactly are multiples and sums of powers of 2. So for example, 0.25 is exact, as is .375 and other such fractions. 0.1 -- not so much.

Just to make it crystal clear what binary decimals represent and why these particular numbers work:

0.25 can be represented exactly in binary because 0.25 = 1/4 = 1/(2²)

Similarly 0.375 = 0.25 + 0.125 = 1/4 + 1/8 = 1/(2²) + 1/(2³). All the denominators are powers of two so this is compatible with binary fractions.

For 0.1 we can only reach an approximation within 32 or 64 bits. The start of the sequence looks like: 1/16 + 1/32 + 1/256 + 1/512 + 1/4096 + 1/8192 = 0.0999755859375

But we would need an infinite sum to reach exactly 0.1, the same way that an infinite number of decimal digits are required to exactly represent fractions such as 1/3. Computers can't do floating point operations at infinite precision, so this gets truncated at some point resulting in an inexact representation.

At 32-bit precision (as you can see by using the FloatConverter linked by Jim G), the closest floating point number you can actually store is this one:

0.100000001490116119384765625

The next lowest number that can be represented exactly is:

0.0999999940395355224609375

And the next highest number is:

0.10000000894069671630859375

Obviously these kind of values are highly inconvenient to work with, so display routines simplify them for human consumption and the end result is that any number within 0.100000001490116119384765625 ± 0.000000003725290298461914000 is assigned exactly the same binary representation and displayed in decimal as "0.1"

The SQLite shell (and website fiddle) has useful built in functions to explore these:

.mode line
with input(x) as (values(0.1))
select
  ieee754_mantissa(x) as 'mantissa',
  ieee754_exponent(x) as 'exponent',
  decimal_mul(
    ieee754_mantissa(x),
    decimal_pow2(ieee754_exponent(x))
  ) as 'exact'
from input;

mantissa = 3602879701896397
exponent = -55
   exact = 0.1000000000000000055511151231257827021181583404541015625

Thanks, Alan. Slightly simpler:

.mode line
with input(x) as (values(0.1))
select
  ieee754_mantissa(x) as 'mantissa',
  ieee754_exponent(x) as 'exponent',
  decimal(x) as 'exact'
from input;

Thanks, Richard. Every day I find something new!