Greetings.

The round() function is declared thus:

round(X)
round(X,Y)

The round(X,Y) function returns a floating-point value X rounded to Y digits to the right of the decimal point. If the Y argument is omitted or negative, it is taken to be 0.

So, when I round I expect the amount of Y digits to be displayed, but it's not.

sqlite> select round(123.123,2), round(123,2),round(123.00,2);
┌──────────────────┬──────────────┬─────────────────┐
│ round(123.123,2) │ round(123,2) │ round(123.00,2) │
├──────────────────┼──────────────┼─────────────────┤
│ 123.12           │ 123.0        │ 123.0           │
└──────────────────┴──────────────┴─────────────────┘
VM-steps: 19
Run Time: real 0.006 user 0.000000 sys 0.000000
sqlite>

I know I can use other ways to get the .00 to appear, but, just for my understanding, why does it not round() honors the Y digits? Thanks.

josé

round() is a function that returns a number, not a text value.

Ok. Why then just display a 123.0 and not just display 123?

Actually, when converted to text by the default output alogithms any value between 122.999999999999503 and 123.000000000000497 (that is 123.0 +/- 35 ulp) will display as the text '123.0'.

Because ROUND() returns a floating point value, which is rendered that way by default.

Because '123' is an integer and '123.0' is floating point. Both are numbers and the computer does not really give a crap. It is for the convenience of humans that the conversion to text both rounds and distingushes between integers and floating point.

The conversion routines could be written in a manner where they did not care about such niceties that (a) stop your eyes from bleeding (if all floating point numbers were displayed "properly" you would almost always see somewhere between 16 and 18 digits for every floating point number) and (b) let you distinguish between integers and floating point.

So, we're talking pure math here. :-) For some reason I was thinking that, although math was involved, there was some type of text manipulation being done int he background to get the formatting done. Got it. Thanks you both.

Actually, when converted to text by the default output alogithms any value between 122.999999999999503 and 123.000000000000497 (that is 123.0 +/- 35 ulp) will display as the text '123.0'.

Ah-hah! So there is some text manipulation somewhere. I knew it. :-)

Thanks Keith for your always precise input. I am a big fan. Although, most of the times these bits of knowledge are flying way over my head, from time to time, after I shoot them in the sky, some dusts of knowledge fall upon me. I am able to take those in. :-)

If you take accuracy into account 123.0 and 123.00 are not the same.

123.0 can be read as "between 122.95 and 123.05", 123.00 can be read as "between 122.995 and 123.005".

So, the second zero can be called a significant digit.

It is not the business of round() to be concerned with this.

Just to compare this to MySQL round() output behavior:

select round(123.0123, 2);
123.01

select round(122.999, 2);
123.00

select round(123.01, 2);
123.01

select round(123.00, 2);
123.00

select round(123.0, 2);
123.0

select round(123, 2);
123

My expectation would also align with yours -- round(X,N) should round using the full precision of the given number and output the result up to the smaller of the requested and input number of decimals.

V:I.ddd  (ddd number of the given decimals)

round(V, nnn) ==> R:Y.rrr  (rrr = min(ddd,nnn), number of decimals in the rounded output)

Of course, it's the output formatting and the underlying float value should be rounded numerically as usual.

SQLite3 round() function converts its (first) argument to a double precision IEEE floating point value and applies rounding to that. The return value is the double precision IEEE floating point result.

The only way to obtain the output you show is if the output is not a IEEE double precision floating point number, but is something else (like text) or perhaps a "packed decimal" number that preserves trailing zero's, or something else that also includes a "precision" datum.

In any event of the cause and whatever MySQL does, SQLite3 only works with plain unadorned IEEE-754 double precision floating point numbers in which you can print/input as many trailing zero's (after the decimal point and the significant decimals) as your litte heart desires with absolutely no effect whatsoever on the value.

In particular, SQLite has no way to distinguish between 123.00 and 123.0.

It can distinguish them just fine, provided the column has TEXT affinity.

Actually, that is 15 significant digits. The number is "rounded" to 15 significant base-10 digits and then output with trailing 0's after the first decimal place omitted. (The digits to the left of the decimal point are significant as well, not just those to the right.)

The "exact" trippable representation of value would be format('%!.17e', value).

Remember that floating point values and "decimal"/"numeric" values have different display properties. A decimal value remembers how much precision it has, and displays differently. A floating point value is just the value, and that's it.

When you're typing a number in as a literal in a statement remember you're still giving the parser a text string representation of a number, not an actual number. The parser can use the context of where it is to decide if it's going to turn that literal into a float, a decimal, etc.

SQLite only has floating point, and not decimal values by default. It does have the Decimal extension though, which I suggest you try out if you really need significant trailing zeros.

MySQL seems to allow floating point in the 2 argument version of round(). I'm not familiar with MySQL though.

But Postgres only allows the 2 argument version with numeric types. If you give it a literal it will turn the literal into a numeric value. But if you try to give it a floating point value it will error out.

testing=> select version();
                          version
------------------------------------------------------------
 PostgreSQL 15.2, compiled by Visual C++ build 1914, 64-bit
(1 row)

testing=> select pg_typeof(123.0);
 pg_typeof
-----------
 numeric
(1 row)

testing=> select round(123.0, 4), pg_typeof(round(123.0, 4));
  round   | pg_typeof
----------+-----------
 123.0000 | numeric
(1 row)

testing=> select round(cast(123.0 as double precision), 4);
ERROR:  function round(double precision, integer) does not exist
LINE 1: select round(cast(123.0 as double precision), 4);
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

As you can see, your assertion is what we call balderdash.

The values have to be text values, not real values. If they are real, then there is no difference between them. Furthermore, the value being "compared against" must also be a text value, not a real value.

sqlite> create table x(x text);
sqlite> insert into x values (123.00),(123.0);
sqlite> select * from x;
┌─────────┐
│    x    │
├─────────┤
│ '123.0' │
│ '123.0' │
└─────────┘
sqlite> insert into x values ('123.00'),('123.0');
sqlite> select x from x;
┌──────────┐
│    x     │
├──────────┤
│ '123.0'  │
│ '123.0'  │
│ '123.00' │
│ '123.0'  │
└──────────┘
sqlite> create table y(y real);
sqlite> insert into y values (123.00),(123.0);
sqlite> insert into y values ('123.00'),('123.0');
sqlite> select y from y;
┌───────┐
│   y   │
├───────┤
│ 123.0 │
│ 123.0 │
│ 123.0 │
│ 123.0 │
└───────┘
sqlite> select * from x where x = 123.0;
┌─────────┐
│    x    │
├─────────┤
│ '123.0' │
│ '123.0' │
│ '123.0' │
└─────────┘
sqlite> select * from x where x = 123.00;
┌─────────┐
│    x    │
├─────────┤
│ '123.0' │
│ '123.0' │
│ '123.0' │
└─────────┘
sqlite> select * from x where x = '123.0';
┌─────────┐
│    x    │
├─────────┤
│ '123.0' │
│ '123.0' │
│ '123.0' │
└─────────┘
sqlite> select * from x where x = '123.00';
┌──────────┐
│    x     │
├──────────┤
│ '123.00' │
└──────────┘
sqlite> select * from y where y = 123.0;
┌───────┐
│   y   │
├───────┤
│ 123.0 │
│ 123.0 │
│ 123.0 │
│ 123.0 │
└───────┘
sqlite> select * from y where y = 123.00;
┌───────┐
│   y   │
├───────┤
│ 123.0 │
│ 123.0 │
│ 123.0 │
│ 123.0 │
└───────┘
sqlite> select * from y where y = '123.0';
┌───────┐
│   y   │
├───────┤
│ 123.0 │
│ 123.0 │
│ 123.0 │
│ 123.0 │
└───────┘
sqlite> select * from y where y = '123.00';
┌───────┐
│   y   │
├───────┤
│ 123.0 │
│ 123.0 │
│ 123.0 │
│ 123.0 │
└───────┘
sqlite>

Any questions? :-)