No feedback for executing a mathematical expression

classic Classic list List threaded Threaded
13 messages Options
Reply | Threaded
Open this post in threaded view
|

No feedback for executing a mathematical expression

Octopus ZHANG
Hi all,


I try to run a simple math expression, but SQLite gives no feedback :

sqlite> select 99-(55/(30/57));




Should I expect it to return nothing?


Thank you

--

Yushan
_______________________________________________
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: No feedback for executing a mathematical expression

Simon Slavin-3
On 10 Mar 2020, at 7:21am, Octopus ZHANG <[hidden email]> wrote:

> sqlite> select 99-(55/(30/57));
>
> Should I expect it to return nothing?

That's going in my list of annoying questions.  Thank you.

sqlite> select 99.0-(55.0/(30.0/57.0));
-5.5

Now you can work out what the problem is.
_______________________________________________
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: No feedback for executing a mathematical expression

Keith Medcalf
In reply to this post by Octopus ZHANG

On Tuesday, 10 March, 2020 01:22, Octopus ZHANG <[hidden email]> wrote:

>I try to run a simple math expression, but SQLite gives no feedback :

>sqlite> select 99-(55/(30/57));

>Should I expect it to return nothing?

It is returning something.  It is returning NULL.

sqlite> .nullvalue <NULL>
sqlite> select 99-(55/(30/57));
<NULL>
sqlite>

99 - (55 / (30 / 57))

30 / 57 -> 0

55 / 0 -> NULL

99 - NULL -> NULL

If you want the result of 30/57 to be a floating point number (ie, not zero), you need to have one of those numbers be floating point, after which each successive operation will be carried out in floating point rather than integer arithmetic.

30. / 57 == 30 / 57. == 30. / 57. -> 0.526315789473684

55 / 0.526315789473684 -> 104.5

99 - 104.5 -> -5.5

--
The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume.



_______________________________________________
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: No feedback for executing a mathematical expression

Radovan Antloga
In reply to this post by Octopus ZHANG
Hi,

because sqlite calculate expression 30/57 as zero or 0.
So this give then divide by zero error when 55/0 is calculated.
Search more on google "Divide by 0 not giving error in sqlite".

If you need to calculate you must change this so that one number
has affinity real like this:

select 99-(55/(30.0/57))

But I like that when you have integer numbers is like div function.
You remember div function like: 10 div 5 = 2.


On 10.03.2020 08:21, Octopus ZHANG wrote:

> Hi all,
>
>
> I try to run a simple math expression, but SQLite gives no feedback :
>
> sqlite> select 99-(55/(30/57));
>
>
>
>
> Should I expect it to return nothing?
>
>
> Thank you
>

_______________________________________________
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: No feedback for executing a mathematical expression

Octopus ZHANG
Yeah, I expected it should show the error message. I will check the related
threads.


radovan5 <[hidden email]> 于2020年3月10日周二 下午4:02写道:

> Hi,
>
> because sqlite calculate expression 30/57 as zero or 0.
> So this give then divide by zero error when 55/0 is calculated.
> Search more on google "Divide by 0 not giving error in sqlite".
>
> If you need to calculate you must change this so that one number
> has affinity real like this:
>
> select 99-(55/(30.0/57))
>
> But I like that when you have integer numbers is like div function.
> You remember div function like: 10 div 5 = 2.
>
>
> On 10.03.2020 08:21, Octopus ZHANG wrote:
> > Hi all,
> >
> >
> > I try to run a simple math expression, but SQLite gives no feedback :
> >
> > sqlite> select 99-(55/(30/57));
> >
> >
> >
> >
> > Should I expect it to return nothing?
> >
> >
> > Thank you
> >
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


--

Yushan
_______________________________________________
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: No feedback for executing a mathematical expression

Octopus ZHANG
In reply to this post by Keith Medcalf
Thanks for the detailed explanation!

Keith Medcalf <[hidden email]> 于2020年3月10日周二 下午3:58写道:

>
> On Tuesday, 10 March, 2020 01:22, Octopus ZHANG <[hidden email]>
> wrote:
>
> >I try to run a simple math expression, but SQLite gives no feedback :
>
> >sqlite> select 99-(55/(30/57));
>
> >Should I expect it to return nothing?
>
> It is returning something.  It is returning NULL.
>
> sqlite> .nullvalue <NULL>
> sqlite> select 99-(55/(30/57));
> <NULL>
> sqlite>
>
> 99 - (55 / (30 / 57))
>
> 30 / 57 -> 0
>
> 55 / 0 -> NULL
>
> 99 - NULL -> NULL
>
> If you want the result of 30/57 to be a floating point number (ie, not
> zero), you need to have one of those numbers be floating point, after which
> each successive operation will be carried out in floating point rather than
> integer arithmetic.
>
> 30. / 57 == 30 / 57. == 30. / 57. -> 0.526315789473684
>
> 55 / 0.526315789473684 -> 104.5
>
> 99 - 104.5 -> -5.5
>
> --
> The fact that there's a Highway to Hell but only a Stairway to Heaven says
> a lot about anticipated traffic volume.
>
>
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


--

Yushan
_______________________________________________
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: No feedback for executing a mathematical expression

Octopus ZHANG
In reply to this post by Simon Slavin-3
I will use floating point as you all suggested. Thank you!

Simon Slavin <[hidden email]> 于2020年3月10日周二 下午3:25写道:

> On 10 Mar 2020, at 7:21am, Octopus ZHANG <[hidden email]> wrote:
>
> > sqlite> select 99-(55/(30/57));
> >
> > Should I expect it to return nothing?
>
> That's going in my list of annoying questions.  Thank you.
>
> sqlite> select 99.0-(55.0/(30.0/57.0));
> -5.5
>
> Now you can work out what the problem is.
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


--

Yushan
_______________________________________________
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: No feedback for executing a mathematical expression

Puneet Kishor-2
In reply to this post by Keith Medcalf
A very helpful and clear explanation to many of us not familiar with SQLite’s math idiosyncracies, or simply needing a refresher. Many thanks Keith.

> On Mar 10, 2020, at 8:57 AM, Keith Medcalf <[hidden email]> wrote:
>
>
> On Tuesday, 10 March, 2020 01:22, Octopus ZHANG <[hidden email]> wrote:
>
>> I try to run a simple math expression, but SQLite gives no feedback :
>
>> sqlite> select 99-(55/(30/57));
>
>> Should I expect it to return nothing?
>
> It is returning something.  It is returning NULL.
>
> sqlite> .nullvalue <NULL>
> sqlite> select 99-(55/(30/57));
> <NULL>
> sqlite>
>
> 99 - (55 / (30 / 57))
>
> 30 / 57 -> 0
>
> 55 / 0 -> NULL
>
> 99 - NULL -> NULL
>
> If you want the result of 30/57 to be a floating point number (ie, not zero), you need to have one of those numbers be floating point, after which each successive operation will be carried out in floating point rather than integer arithmetic.
>
> 30. / 57 == 30 / 57. == 30. / 57. -> 0.526315789473684
>
> 55 / 0.526315789473684 -> 104.5
>
> 99 - 104.5 -> -5.5
>




_______________________________________________
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: No feedback for executing a mathematical expression

Jose Isaias Cabrera-4
In reply to this post by Simon Slavin-3

Simon Slavin, on Tuesday, March 10, 2020 03:25 AM, wrote...
>
> On 10 Mar 2020, at 7:21am, Octopus ZHANG <[hidden email]> wrote:
>
> > sqlite> select 99-(55/(30/57));
> >
> > Should I expect it to return nothing?
>
> That's going in my list of annoying questions.  Thank you.

Simon, with all due respect, and grateful for all the answers you have provided to me, this is not an annoying question.  Not everyone in the world knows what you know.  Some people do answer questions because they don't know.  I actually was going to add other ones that I found trying to figure this one out.  But, Keith has provided the answer.  Otherwise, I would have added some more.  In this forum, email-list, most people ask questions because we just don't know, and we want to learn.  Thanks.

josé
_______________________________________________
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: No feedback for executing a mathematical expression

Jose Isaias Cabrera-4
In reply to this post by Keith Medcalf

Keith Medcalf, on Tuesday, March 10, 2020 03:57 AM, wrote...

>
>
> On Tuesday, 10 March, 2020 01:22, Octopus ZHANG <[hidden email]>
> wrote:
>
> >I try to run a simple math expression, but SQLite gives no feedback :
>
> >sqlite> select 99-(55/(30/57));
>
> >Should I expect it to return nothing?
>
> It is returning something.  It is returning NULL.
>
> sqlite> .nullvalue <NULL>
> sqlite> select 99-(55/(30/57));
> <NULL>
> sqlite>
>
> 99 - (55 / (30 / 57))
>
> 30 / 57 -> 0
>
> 55 / 0 -> NULL
>
> 99 - NULL -> NULL
>
> If you want the result of 30/57 to be a floating point number (ie, not
> zero), you need to have one of those numbers be floating point, after which
> each successive operation will be carried out in floating point rather than
> integer arithmetic.
>
> 30. / 57 == 30 / 57. == 30. / 57. -> 0.526315789473684
>
> 55 / 0.526315789473684 -> 104.5
>
> 99 - 104.5 -> -5.5
>
Thanks Keith for the lesson of the day. ;-)

josé
_______________________________________________
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: No feedback for executing a mathematical expression

Simon Slavin-3
In reply to this post by Jose Isaias Cabrera-4
On 10 Mar 2020, at 12:40pm, Jose Isaias Cabrera <[hidden email]> wrote:

> Simon Slavin, on Tuesday, March 10, 2020 03:25 AM, wrote...
>
>> That's going in my list of annoying questions.  Thank you.
>
> Simon, with all due respect, and grateful for all the answers you have provided to me, this is not an annoying question.  Not everyone in the world knows what you know.

Jose, I must apologise for not explaining myself better.

It's a delightful annoying question.  Anyone who asks that question should be annoyed, at computers in general and the one they're swearing at in particular.  I love those questions and I giggle over them when people tell me that programming must be easy because computers are simple and logical.
_______________________________________________
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: No feedback for executing a mathematical expression

Jose Isaias Cabrera-4

Simon Slavin, on Tuesday, March 10, 2020 09:23 AM, wrote...

>
> On 10 Mar 2020, at 12:40pm, Jose Isaias Cabrera <[hidden email]>
> wrote:
>
> > Simon Slavin, on Tuesday, March 10, 2020 03:25 AM, wrote...
> >
> >> That's going in my list of annoying questions.  Thank you.
> >
> > Simon, with all due respect, and grateful for all the answers you have
> provided to me, this is not an annoying question. Not everyone in the world
> knows what you know.
>
> Jose, I must apologise for not explaining myself better.
>
> It's a delightful annoying question. Anyone who asks that question should
> be annoyed, at computers in general and the one they're swearing at in
> particular. I love those questions and I giggle over them when people tell
> me that programming must be easy because computers are simple and logical.

Apologies, Simon.  I knew there was something wrong, here: me.  Yes, I said to myself, "that is not like Simon to answer that harsh!" But you actually were talking about the SQL syntax itself.  It's hard with emails to see the faces, intentions, meanings, etc., behind the content. Thanks for explaining yourself. :-)  I knew I was wrong.

josé
_______________________________________________
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: No feedback for executing a mathematical expression

Puneet Kishor-2
In reply to this post by Octopus ZHANG
Hi,

Besides the most excellent explanation given by Keith Medcalf, I want to point out a couple of (hopefully) helpful things –

1. Contrary to your subject line, SQLite actually does give a feedback/returns something. It is just not good enough (for many of us). Consider the following:

```
○ → sqlite3
SQLite version 3.30.0 2019-10-04 15:03:17
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> SELECT 30 / 2;
15
sqlite> SELECT 30 / 57;
0
sqlite> SELECT 55 / 0;

sqlite>
```

See that blank line after the last operation? That is SQLite “printing” out a NULL value. The `.nullvalue STRING` setting in the command line client can change that blank like to something more visual/meaningful.

2. The `typeof()` operator is super. It is like the detective cousin of CAST(). The latter allows you to change the type of a data value and the former allows you to find out the typeof data.

```
sqlite> SELECT typeof(55 / 0);
null
sqlite> SELECT typeof(30 / 2);
integer
sqlite> SELECT typeof(30.0 / 55);
real
sqlite> SELECT 30.0 / 55;
0.545454545454545
sqlite>
```

Good luck. And nice question as it reminded us of this math idiosyncrasy of SQLite.

> On Mar 10, 2020, at 8:21 AM, Octopus ZHANG <[hidden email]> wrote:
>
> I try to run a simple math expression, but SQLite gives no feedback :
>
> sqlite> select 99-(55/(30/57));
>
>
>
>
> Should I expect it to return nothing?





--
Puneet Kishor
Just Another Creative Commoner
http://punkish.org/About

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