Is this a bug with expression evaluation?

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
10 messages Options
Reply | Threaded
Open this post in threaded view
|

Is this a bug with expression evaluation?

Tony Papadimitriou
I’ve noticed this (very annoying) behavior:

select column1*(24/100) wrong from (values(100));

Removing the parentheses yields the correct result:

select column1*24/100 correct from (values(100));

This obviously behaves like integer math is used and (24/100) gets truncated to zero.

If I add a dot to either number (e.g., 24. or 100.) I get the correct result.
But, with named fields, it’s not as easy as adding a dot:

select column1*(column2/column3) wrong from (values(100,24,100));
select column1*column2/column3 correct from (values(100,24,100));

So, to get correct answer I have to use a cast for either field?

select column1*(cast(column2 as float)/column3) correct from (values(100,24,100));

In this example removing the parentheses is a simple solution.
But if the expression was column1*(1+column2/column3) a cast is the only way?  (Hope not!)

Anyway, if all this happens to be so by design, is there at least some way to default to float math rather than integer?

Thanks.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Is this a bug with expression evaluation?

Marc L. Allen
I just multiply by 1.0

Select column1*(column2 * 1.0 / column3)...

Removing the parentheses only provide the correct results in your example.  It's still using integer math, it's just performing the multiply first, as per order of operations.

-----Original Message-----
From: sqlite-users [mailto:[hidden email]] On Behalf Of Tony Papadimitriou
Sent: Thursday, December 14, 2017 11:36 AM
To: [hidden email]
Subject: [sqlite] Is this a bug with expression evaluation?

I’ve noticed this (very annoying) behavior:

select column1*(24/100) wrong from (values(100));

Removing the parentheses yields the correct result:

select column1*24/100 correct from (values(100));

This obviously behaves like integer math is used and (24/100) gets truncated to zero.

If I add a dot to either number (e.g., 24. or 100.) I get the correct result.
But, with named fields, it’s not as easy as adding a dot:

select column1*(column2/column3) wrong from (values(100,24,100)); select column1*column2/column3 correct from (values(100,24,100));

So, to get correct answer I have to use a cast for either field?

select column1*(cast(column2 as float)/column3) correct from (values(100,24,100));

In this example removing the parentheses is a simple solution.
But if the expression was column1*(1+column2/column3) a cast is the only way?  (Hope not!)

Anyway, if all this happens to be so by design, is there at least some way to default to float math rather than integer?

Thanks.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


Confidentiality notice: This e-mail is intended solely for use of the individual or entity to which it is addressed and may contain information that is proprietary, privileged, company confidential and/or exempt from disclosure under applicable law. If the reader is not the intended recipient or agent responsible for delivering the message to the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited. If you have received this communication in error, please notify the sender by reply e-mail or collect telephone call and delete or destroy all copies of this e-mail message, any physical copies made of this e-mail message and/or any file attachment(s).
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Is this a bug with expression evaluation?

J. King-3
In reply to this post by Tony Papadimitriou
Someone please correct me if I'm wrong, but I believe it's mandated by the SQL standard that integer division is used when both operands are integers.

Your synthetic example doesn't use a fixed table, but if it did the easiest solution for you would probably be to define any columns where you need arbitrary precision as REAL rather than INTEGER, and SQLite column affinity would do the rest.

Otherwise, yes, I believe you would need to cast.

On December 14, 2017 11:36:19 AM EST, Tony Papadimitriou <[hidden email]> wrote:

>I’ve noticed this (very annoying) behavior:
>
>select column1*(24/100) wrong from (values(100));
>
>Removing the parentheses yields the correct result:
>
>select column1*24/100 correct from (values(100));
>
>This obviously behaves like integer math is used and (24/100) gets
>truncated to zero.
>
>If I add a dot to either number (e.g., 24. or 100.) I get the correct
>result.
>But, with named fields, it’s not as easy as adding a dot:
>
>select column1*(column2/column3) wrong from (values(100,24,100));
>select column1*column2/column3 correct from (values(100,24,100));
>
>So, to get correct answer I have to use a cast for either field?
>
>select column1*(cast(column2 as float)/column3) correct from
>(values(100,24,100));
>
>In this example removing the parentheses is a simple solution.
>But if the expression was column1*(1+column2/column3) a cast is the
>only way?  (Hope not!)
>
>Anyway, if all this happens to be so by design, is there at least some
>way to default to float math rather than integer?
>
>Thanks.
>_______________________________________________
>sqlite-users mailing list
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

--
Sent from my Android device with K-9 Mail. Please excuse my brevity.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] Is this a bug with expression evaluation?

Hick Gunter
In reply to this post by Tony Papadimitriou
This is well documented behaviour, see the explanation of affinity. See http://sqlite.org/datatype3.html#affinity

If you require floating point arithmetic, you must introduce REAL affinity, either by including a field with storage class REAL, a cast operation or a real literal value

-----Ursprüngliche Nachricht-----
Von: sqlite-users [mailto:[hidden email]] Im Auftrag von Tony Papadimitriou
Gesendet: Donnerstag, 14. Dezember 2017 17:36
An: [hidden email]
Betreff: [EXTERNAL] [sqlite] Is this a bug with expression evaluation?

I’ve noticed this (very annoying) behavior:

select column1*(24/100) wrong from (values(100));

Removing the parentheses yields the correct result:

select column1*24/100 correct from (values(100));

This obviously behaves like integer math is used and (24/100) gets truncated to zero.

If I add a dot to either number (e.g., 24. or 100.) I get the correct result.
But, with named fields, it’s not as easy as adding a dot:

select column1*(column2/column3) wrong from (values(100,24,100)); select column1*column2/column3 correct from (values(100,24,100));

So, to get correct answer I have to use a cast for either field?

select column1*(cast(column2 as float)/column3) correct from (values(100,24,100));

In this example removing the parentheses is a simple solution.
But if the expression was column1*(1+column2/column3) a cast is the only way?  (Hope not!)

Anyway, if all this happens to be so by design, is there at least some way to default to float math rather than integer?

Thanks.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___________________________________________
 Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Is this a bug with expression evaluation?

Tony Papadimitriou
In reply to this post by J. King-3
-----Original Message-----
From: J. King

>Someone please correct me if I'm wrong, but I believe it's mandated by the
>SQL standard that integer division is used when both operands are integers.

I really don't know what the standard says, but here are two different
opinions in implementation.

MySQL example:
mysql> select 1/2;
+--------+
| 1/2    |
+--------+
| 0.5000 |
+--------+
1 row in set (0.13 sec)

PostgreSQL example:
psql=# select 1/2;
?column?
----------
        0
(1 row)


>Your synthetic example doesn't use a fixed table, but if it did the easiest
>solution for you would probably be to define any columns where you need
>arbitrary precision as REAL rather than INTEGER, and SQLite column
> >affinity would do the rest.

SQLite3 (https://sqlite.org/datatype3.html) -- "Otherwise, an expression has
no affinity. "
It seems that 'no affinity' gets translated to integer affinity, then.

Is there a way to default to float?

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] Is this a bug with expression evaluation?

Simon Slavin-3
In reply to this post by Hick Gunter
What you see is not a bug, it’s an annoying heritage of C syntax.  Might even precede C.  Here’s the problem:

        select column1*(24/100);

And here’s what you’re meant to do for 24%:

        select column1*(24.0/100.0);

Alternatively, the value in column1 should be real.  That should also work.

If your numbers are real, express them as real, using either a decimal point or exponent/mantissa format.  "24" means integer 24.  "24.0" means real 24.

I’ve had this behaviour bite me when I was doing some simple maths in C and divided by "4" instead of "4.0".  It took me numerous debugging statements and three or four hours to figure out what was wrong, and when I figured it out I was so annoyed I left work early.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Is this a bug with expression evaluation?

Simon Slavin-3
In reply to this post by Tony Papadimitriou


On 14 Dec 2017, at 5:03pm, Tony Papadimitriou <[hidden email]> wrote:

> SQLite3 (https://sqlite.org/datatype3.html) -- "Otherwise, an expression has no affinity. "
> It seems that 'no affinity' gets translated to integer affinity, then.

Just to remind you that if something is not documented it can change.  The next version of SQLite might decide that 1 / 2 is 0.  So don’t write code that depends on it.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Is this a bug with expression evaluation?

Richard Hipp-3
In reply to this post by Tony Papadimitriou
On 12/14/17, Tony Papadimitriou <[hidden email]> wrote:
>
> MySQL example:
> mysql> select 1/2;
> +--------+
> | 1/2    |
> +--------+
> | 0.5000 |
> +--------+
> 1 row in set (0.13 sec)

MySQL is the only database engine that behaves this way.  All others
do integer arithmetic on integer values.

This is probably the reason that MySQL has the separate "DIV" operator
for integer division, whereas everybody else makes due with the
standard "/" operator.
--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Is this a bug with expression evaluation?

Peter da Silva
In reply to this post by Simon Slavin-3
On 12/14/17, 12:08 PM, "sqlite-users on behalf of Simon Slavin" <[hidden email] on behalf of [hidden email]> wrote:
> Just to remind you that if something is not documented it can change.  The next version of SQLite might decide that 1 / 2 is 0.  So don’t write code that depends on it.

I think it already does:

sqlite> select 1/2;
0
sqlite>


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Is this a bug with expression evaluation?

Clemens Ladisch
In reply to this post by Tony Papadimitriou
Tony Papadimitriou wrote:
> I really don't know what the standard says, but here are two different
> opinions in implementation.
>
> MySQL example:

You know that the "SQL" in "MySQL" is actually the abbreviation of
"something quite loose"?  ;-)

Anyway, it appears even MySQL conforms to SQL-92 subclause 6.12:

| 1) If the data type of both operands of a dyadic arithmetic opera-
|    tor is exact numeric, then the data type of the result is exact
|    numeric, with precision and scale determined as follows:
|    [...]
|    d) The precision and scale of the result of division is
|       implementation-defined.


Regards,
Clemens
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users