Re: [sqlite] error in documentation of SELECT?
On May 19, 2009, at 10:05 AM, Jean-Denis Muys wrote: > On 5/19/09 2:44 PM, "Igor Tandetnik" wrote: >> >> Wikipedia gives a definition different from yours, for what it's >> worth: >> >> http://en.wikipedia.org/wiki/Remainder#The_case_of_general_integers > > Also to support my version, the same article says a bit later: > > " Usually, in number theory, we always choose the positive remainder". > > While programming languages seems to make rather different choices: > > "C99 and Pascal choose the remainder with the same sign as the > dividend a. > (Before C99, the C language allowed either choice.) Perl and Python > choose > the remainder with the same sign as the divisor d." For those with ACM digital library access, the three options are defined and analyzed in some detail in the paper "The Euclidean definition of the functions div and mod" by Raymond T. Boute, University of Nijmegen, ACM Transactions on Programming Languages and Systems (TOPLAS) Volume 14, Issue 2 (April 1992), Pages: 127 - 144, Year of Publication: 1992, ISSN:0164-0925 http://portal.acm.org/citation.cfm?doid=128861.128862 e ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] error in documentation of SELECT?
On 5/19/09 2:44 PM, "Igor Tandetnik" wrote: > > Wikipedia gives a definition different from yours, for what it's worth: > > http://en.wikipedia.org/wiki/Remainder#The_case_of_general_integers Also to support my version, the same article says a bit later: " Usually, in number theory, we always choose the positive remainder". While programming languages seems to make rather different choices: "C99 and Pascal choose the remainder with the same sign as the dividend a. (Before C99, the C language allowed either choice.) Perl and Python choose the remainder with the same sign as the divisor d." Rejoice ! Jean-Denis ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] error in documentation of SELECT?
On 5/19/09 2:44 PM, "Igor Tandetnik" wrote: > > Well then, for the equality to hold, (-1)/7 should be -1. Would you be > happy with such an outcome? > Yep > Wikipedia gives a definition different from yours, for what it's worth: > > http://en.wikipedia.org/wiki/Remainder#The_case_of_general_integers > http://en.wikipedia.org/wiki/Modulo_operation Well, I contend it doesn't. It says: "−42 = 9×(−5) + 3 as is usual for mathematicians" Which was my point. But as was pointed out, there are two choices, both equally valid. > > Once you decide that division truncates towards zero (as is the > long-standing tradition in C and many other languages, for better or > worse), you have no choice but to let the remainder take the sign of the > dividend. True enough. It seems Computer Science has most of the time made the opposite choice than mathematicians. I can live with that too. > > Igor Tandetnik > Jean-Denis Muys ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] error in documentation of SELECT?
On 19/05/2009 9:57 PM, Igor Tandetnik wrote: > "John Machin" wrote > in message news:4a129cb4.2090...@lexicon.net >> It's handy for checking how things work e.g. >> >> sqlite> select (-1) % 7; >> -1 >> sqlite> -- it's not a real modulo operator :-( > > What do you feel is wrong with this result? What should a "real" modulo > operator return, in your opinion? Before you answer, note that you very > likely want this equality to hold for all a, b!=0 : > > a = q*b + r > where q = a/b, r = a%b Sure do. No problem for Python: >>> for a in (1, -1): ... for b in (7, -7): ... q = a / b ... r = a % b ... x = b * q + r ... print a, b, q, r, x ... 1 7 0 1 1 1 -7 -1 -6 1 -1 7 -1 6 -1 -1 -7 0 -1 -1 >>> ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] error in documentation of SELECT?
On Tue, May 19, 2009 at 02:06:37PM +0200, Jean-Denis Muys wrote: > > There exists unique natural numbers q and r such as: > > a = b*q+r > 0 <= r < b > > q is defined as the quotient, r is defined as the remainder. > > So if the % operator wants to match that math definition, its results should > never be negative. In the example given, (-1)%7 should therefore be 6. It depends wether you focus on a arithmetic meaning of the remainder or on what you call "mathematics" (what would be "Restklasse" in german, I don't know the english term). In terms of "Restklasse" you want a positive modulo and get a negative division result. In terms of arithmetics you want a positive division result and get a negative modulo. Both are valid solutions. At the end you have to live with the definition of the given implementation. Just check the sign and correct the sign of the result according to your needs. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] error in documentation of SELECT?
"Jean-Denis Muys" wrote in message news:c6386d6d.45a7%jdm...@kleegroup.com > On 5/19/09 1:57 PM, "Igor Tandetnik" > wrote: > >> "John Machin" >> wrote in message >> news:4a129cb4.2090...@lexicon.net >>> It's handy for checking how things work e.g. >>> >>> sqlite> select (-1) % 7; >>> -1 >>> sqlite> -- it's not a real modulo operator :-( >> >> What do you feel is wrong with this result? What should a "real" >> modulo operator return, in your opinion? Before you answer, note >> that you very likely want this equality to hold for all a, b!=0 : >> >> a = q*b + r >> where q = a/b, r = a%b >> >> Igor Tanetnik >> > > My math courses taught me a long time ago, that the remainder r in > Euclidian division of a by b is defined by: > > There exists unique natural numbers q and r such as: > > a = b*q+r > 0 <= r < b > > q is defined as the quotient, r is defined as the remainder. > > So if the % operator wants to match that math definition, its results > should > never be negative. In the example given, (-1)%7 should therefore be 6. Well then, for the equality to hold, (-1)/7 should be -1. Would you be happy with such an outcome? Wikipedia gives a definition different from yours, for what it's worth: http://en.wikipedia.org/wiki/Remainder#The_case_of_general_integers http://en.wikipedia.org/wiki/Modulo_operation Once you decide that division truncates towards zero (as is the long-standing tradition in C and many other languages, for better or worse), you have no choice but to let the remainder take the sign of the dividend. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] error in documentation of SELECT?
On Tue, May 19, 2009 at 12:49 PM, John Machin wrote: > It's handy for checking how things work e.g. > > sqlite> select (-1) % 7; > -1 > sqlite> -- it's not a real modulo operator :-( I also used it as: sqlite> .mode col sqlite> .h 1 sqlite> select "€", length("€"), length(cast("€" as blob)), hex("€"); "€" length("€") length(cast("€" as blob)) hex("€") -- - --- -- € 1 3E282AC To make sure the sqlite shell was inserting UTF-8 as it should. Regards, ~Nuno Lucas > > Cheers, > John ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] error in documentation of SELECT?
My memory failed me on a detail if I want to be rigorous. In the definition of q and r, r is a natural number, but q is a relative number, not a natural. On 5/19/09 2:06 PM, "Jean-Denis Muys" wrote: > On 5/19/09 1:57 PM, "Igor Tandetnik" wrote: > >> "John Machin" wrote >> in message news:4a129cb4.2090...@lexicon.net >>> It's handy for checking how things work e.g. >>> >>> sqlite> select (-1) % 7; >>> -1 >>> sqlite> -- it's not a real modulo operator :-( >> >> What do you feel is wrong with this result? What should a "real" modulo >> operator return, in your opinion? Before you answer, note that you very >> likely want this equality to hold for all a, b!=0 : >> >> a = q*b + r >> where q = a/b, r = a%b >> >> Igor Tanetnik >> > > My math courses taught me a long time ago, that the remainder r in Euclidian > division of a by b is defined by: > > There exists unique natural numbers q and r such as: > > a = b*q+r > 0 <= r < b > > q is defined as the quotient, r is defined as the remainder. > > So if the % operator wants to match that math definition, its results should > never be negative. In the example given, (-1)%7 should therefore be 6. > > But I will not argue that the % operator needs to match the math definition, > though I would probably prefer it to. > > Jean-Denis > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] error in documentation of SELECT?
On 5/19/09 1:57 PM, "Igor Tandetnik" wrote: > "John Machin" wrote > in message news:4a129cb4.2090...@lexicon.net >> It's handy for checking how things work e.g. >> >> sqlite> select (-1) % 7; >> -1 >> sqlite> -- it's not a real modulo operator :-( > > What do you feel is wrong with this result? What should a "real" modulo > operator return, in your opinion? Before you answer, note that you very > likely want this equality to hold for all a, b!=0 : > > a = q*b + r > where q = a/b, r = a%b > > Igor Tanetnik > My math courses taught me a long time ago, that the remainder r in Euclidian division of a by b is defined by: There exists unique natural numbers q and r such as: a = b*q+r 0 <= r < b q is defined as the quotient, r is defined as the remainder. So if the % operator wants to match that math definition, its results should never be negative. In the example given, (-1)%7 should therefore be 6. But I will not argue that the % operator needs to match the math definition, though I would probably prefer it to. Jean-Denis ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] error in documentation of SELECT?
"John Machin" wrote in message news:4a129cb4.2090...@lexicon.net > It's handy for checking how things work e.g. > > sqlite> select (-1) % 7; > -1 > sqlite> -- it's not a real modulo operator :-( What do you feel is wrong with this result? What should a "real" modulo operator return, in your opinion? Before you answer, note that you very likely want this equality to hold for all a, b!=0 : a = q*b + r where q = a/b, r = a%b Igor Tanetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] error in documentation of SELECT?
On 19/05/2009 9:37 PM, Nuno Lucas wrote: > On Mon, May 18, 2009 at 5:03 PM, Mitchell L Model wrote: >> I may be misreading the select-core diagram on >> http://www.sqlite.org/lang_select.html but it appears that the down-arrow >> that would allow a query without a FROM clause should not be there. Is it >> really possible to have a SELECT with no FROM? If so, could someone provide >> an example; if not, would someone maintaining the documentation make a note >> of this? Thank you. > > Yes. > > An use-case could be when you need to pass results to a function > expecting table result data, but you actually have const data. Instead > of having a select from some table (that must exist for the SQL to be > valid), you can just issue a select with no "FROM" clause. > > sqlite> select 1,"Some Data" UNION select 2, "Some More Data"; > 1|Some Data > 2|Some More Data > > Note that although this is not standard SQL, some other engines also > have similar features, for example, Oracle let's you do "SELECT 1 FROM > DUAL", where "DUAL" means "not really from any table". In Oracle you > could use it, for example, to get the server date/time so you could > sync your client application clock. As SQLite is not a server, most > pratical examples are for running user defined functions. It's handy for checking how things work e.g. sqlite> select (-1) % 7; -1 sqlite> -- it's not a real modulo operator :-( Cheers, John ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] error in documentation of SELECT?
On Mon, May 18, 2009 at 5:03 PM, Mitchell L Model wrote: > I may be misreading the select-core diagram on > http://www.sqlite.org/lang_select.html but it appears that the down-arrow > that would allow a query without a FROM clause should not be there. Is it > really possible to have a SELECT with no FROM? If so, could someone provide > an example; if not, would someone maintaining the documentation make a note > of this? Thank you. Yes. An use-case could be when you need to pass results to a function expecting table result data, but you actually have const data. Instead of having a select from some table (that must exist for the SQL to be valid), you can just issue a select with no "FROM" clause. sqlite> select 1,"Some Data" UNION select 2, "Some More Data"; 1|Some Data 2|Some More Data Note that although this is not standard SQL, some other engines also have similar features, for example, Oracle let's you do "SELECT 1 FROM DUAL", where "DUAL" means "not really from any table". In Oracle you could use it, for example, to get the server date/time so you could sync your client application clock. As SQLite is not a server, most pratical examples are for running user defined functions. The documentation could be ommiting this, but it's an esoteric enough feature. Regards, ~Nuno Lucas > -- > > --- Mitchell L Model ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] error in documentation of SELECT?
On May 18, 2009, at 2:53 PM, Pavel Ivanov wrote: > Actually I wanted to know if it can be useful somewhere. :-) I already shown you one useful thing to do with a SELECT that omits the FROM clause: Determine the version of SQLite you are running using "SELECT sqlite_version()". In applications I write, I typically have an SQLite database connection open and the infrastructure in place to get query results easily, and so I find queries such as the following to be useful and convenient: SELECT datetime('now');-- Get the current date and time in IS0-8601 SELECT lower(hex(randomblob(32))); -- Get a universally unique identifier The original reason that SELECT without FROM was added is so that one could invoke application-defined functions, or the RAISE() function, from within triggers: CREATE TRIGGER ex1 AFTER UPDATE ON table1 BEGIN SELECT do_something_using_c_code(); END; D. Richard Hipp d...@hwaci.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] error in documentation of SELECT?
On Mon, May 18, 2009 at 7:53 PM, Pavel Ivanov wrote: > Actually I wanted to know if it can be useful somewhere. :-) You could have a calculator command on your application and let SQLite parse the result for you ;-) sqlite> SELECT 1+2*(3+4*5); 47 Regards, ~Nuno Lucas > > Pavel > > On Mon, May 18, 2009 at 2:52 PM, Noah Hart wrote: >> Just because the syntax allows it, doesn't mean that it will be useful >> >> SELECT '1', sqlite_version() as A where A < 'a' group by 1 order by 1 >> >> Noah >> >> -Original Message- >> From: sqlite-users-boun...@sqlite.org >> [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Pavel Ivanov >> Sent: Monday, May 18, 2009 11:37 AM >> To: General Discussion of SQLite Database >> Subject: Re: [sqlite] error in documentation of SELECT? >> >> I didn't notice it earlier and now I'm a bit surprised. Can I ask a >> more elaborate example which will include WHERE and/or GROUP BY but >> not include FROM? >> >> Pavel >> >> >> >> >> CONFIDENTIALITY NOTICE: >> This message may contain confidential and/or privileged information. If you >> are not the addressee or authorized to receive this for the addressee, you >> must not use, copy, disclose, or take any action based on this message or >> any information herein. If you have received this message in error, please >> advise the sender immediately by reply e-mail and delete this message. Thank >> you for your cooperation. >> >> >> ___ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] error in documentation of SELECT?
Actually I wanted to know if it can be useful somewhere. :-) Pavel On Mon, May 18, 2009 at 2:52 PM, Noah Hart wrote: > Just because the syntax allows it, doesn't mean that it will be useful > > SELECT '1', sqlite_version() as A where A < 'a' group by 1 order by 1 > > Noah > > -Original Message- > From: sqlite-users-boun...@sqlite.org > [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Pavel Ivanov > Sent: Monday, May 18, 2009 11:37 AM > To: General Discussion of SQLite Database > Subject: Re: [sqlite] error in documentation of SELECT? > > I didn't notice it earlier and now I'm a bit surprised. Can I ask a > more elaborate example which will include WHERE and/or GROUP BY but > not include FROM? > > Pavel > > > > > CONFIDENTIALITY NOTICE: > This message may contain confidential and/or privileged information. If you > are not the addressee or authorized to receive this for the addressee, you > must not use, copy, disclose, or take any action based on this message or any > information herein. If you have received this message in error, please advise > the sender immediately by reply e-mail and delete this message. Thank you for > your cooperation. > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] error in documentation of SELECT?
Just because the syntax allows it, doesn't mean that it will be useful SELECT '1', sqlite_version() as A where A < 'a' group by 1 order by 1 Noah -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Pavel Ivanov Sent: Monday, May 18, 2009 11:37 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] error in documentation of SELECT? I didn't notice it earlier and now I'm a bit surprised. Can I ask a more elaborate example which will include WHERE and/or GROUP BY but not include FROM? Pavel CONFIDENTIALITY NOTICE: This message may contain confidential and/or privileged information. If you are not the addressee or authorized to receive this for the addressee, you must not use, copy, disclose, or take any action based on this message or any information herein. If you have received this message in error, please advise the sender immediately by reply e-mail and delete this message. Thank you for your cooperation. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] error in documentation of SELECT?
I didn't notice it earlier and now I'm a bit surprised. Can I ask a more elaborate example which will include WHERE and/or GROUP BY but not include FROM? Pavel On Mon, May 18, 2009 at 2:32 PM, D. Richard Hipp wrote: > > On May 18, 2009, at 2:18 PM, Mitchell L Model wrote: > >> Is it really possible to have a SELECT with no FROM? If so, could >> someone provide an example; i > > SELECT sqlite_version(); > > D. Richard Hipp > d...@hwaci.com > > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] error in documentation of SELECT?
On May 18, 2009, at 2:18 PM, Mitchell L Model wrote: > Is it really possible to have a SELECT with no FROM? If so, could > someone provide an example; i SELECT sqlite_version(); D. Richard Hipp d...@hwaci.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users