Re: [sqlite] error in documentation of SELECT?

[Doug Currie]

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?

[Jean-Denis Muys]

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?

[Jean-Denis Muys]

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?

[John Machin]

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?

[Emil Obermayr]

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?

[Igor Tandetnik]

"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?

[Nuno Lucas]

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?

[Jean-Denis Muys]

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?

[Jean-Denis Muys]

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?

[Igor Tandetnik]

"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?

[John Machin]

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?

[Nuno Lucas]

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

[sqlite] error in documentation of SELECT?

[Mitchell L Model]

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.
-- 

--- 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?

[D. Richard Hipp]

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?

[Pavel Ivanov]

Actually I wanted to know if it can be useful somewhere. :-)

Pavel

On Mon, May 18, 2009 at 2:52 PM, Noah Hart <n...@lipmantpa.com> 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?

[Noah Hart]

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?

[Pavel Ivanov]

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?

[D. Richard Hipp]

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] error in documentation of SELECT?

[Mitchell L Model]

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.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users