Re: [sqlite] No feedback for executing a mathematical expression
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 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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] No feedback for executing a mathematical expression
Simon Slavin, on Tuesday, March 10, 2020 09:23 AM, wrote... > > On 10 Mar 2020, at 12:40pm, Jose Isaias Cabrera > 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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] No feedback for executing a mathematical expression
On 10 Mar 2020, at 12:40pm, Jose Isaias Cabrera 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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] No feedback for executing a mathematical expression
Keith Medcalf, on Tuesday, March 10, 2020 03:57 AM, wrote... > > > On Tuesday, 10 March, 2020 01:22, Octopus ZHANG > 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 > sqlite> select 99-(55/(30/57)); > > 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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] No feedback for executing a mathematical expression
Simon Slavin, on Tuesday, March 10, 2020 03:25 AM, wrote... > > On 10 Mar 2020, at 7:21am, Octopus ZHANG 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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] No feedback for executing a mathematical expression
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 wrote: > > > On Tuesday, 10 March, 2020 01:22, Octopus ZHANG > 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 > sqlite> select 99-(55/(30/57)); > > 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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] No feedback for executing a mathematical expression
I will use floating point as you all suggested. Thank you! Simon Slavin 于2020年3月10日周二 下午3:25写道： > On 10 Mar 2020, at 7:21am, Octopus ZHANG 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 > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > -- Yushan ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] No feedback for executing a mathematical expression
Thanks for the detailed explanation! Keith Medcalf 于2020年3月10日周二 下午3:58写道： > > On Tuesday, 10 March, 2020 01:22, Octopus ZHANG > 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 > sqlite> select 99-(55/(30/57)); > > 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 > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > -- Yushan ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] No feedback for executing a mathematical expression
Yeah, I expected it should show the error message. I will check the related threads. radovan5 于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 > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > -- Yushan ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] No feedback for executing a mathematical expression
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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] No feedback for executing a mathematical expression
On Tuesday, 10 March, 2020 01:22, Octopus ZHANG 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 sqlite> select 99-(55/(30/57)); 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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] No feedback for executing a mathematical expression
On 10 Mar 2020, at 7:21am, Octopus ZHANG 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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] No feedback for executing a mathematical expression
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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
