Re: [sqlite] SQLite and integer division 1/2=0

2013-05-12 Thread James K. Lowden
On Sun, 12 May 2013 10:59:39 -0400 Stephen Chrzanowski wrote: > Correct me if I'm wrong, but the reason behind doing the int div > first is due to integer division is faster than float division? In the abstract, it's actually impossible to say that one or the other type of

Re: [sqlite] Possible bug in type conversion prior to comparison

2013-05-12 Thread Richard Hipp
On Sun, May 12, 2013 at 7:55 AM, Tomasz Pawlak < tomasz.paw...@cs.put.poznan.pl> wrote: > > So, type of '1' is 'text'. > > * If one operand has INTEGER, REAL or NUMERIC affinity and the other > operand as TEXT or NONE affinity then NUMERIC affinity is applied to other > operand. " > > So, if we

[sqlite] UDFs don't work in WHERE clauses?

2013-05-12 Thread Alix Axel
This is my first posting here, so forgive me if I'm breaking any rules or conventions. I'm having a hard time figuring out a weird behavior with SQLite WHERE clauses when they reference a UDF. I've explained the issue clearly on StackOverflow and perhaps it's better to link the question here

[sqlite] Possible bug in type conversion prior to comparison

2013-05-12 Thread Tomasz Pawlak
Hello, First of all, I'm not sure is this a good place to report a SQLite bug, however let me try. SQLite version: 3.7.15.2 How to reproduce error: 1. Create in-memory (or disk, whatever) database 2. Execute following statements to prepare schema: CREATE TABLE data (id INTEGER PRIMARY KEY

Re: [sqlite] SQLite and integer division 1/2=0

2013-05-12 Thread Simon Slavin
On 12 May 2013, at 7:15pm, Paul van Helden wrote: > On Sun, May 12, 2013 at 1:54 PM, Michael Black wrote: > >> PRAGMA INTEGER_DIVISION would probably not have saved you this bug as you >> would not have known to turn it on (default would have to be

Re: [sqlite] SQLite and integer division 1/2=0

2013-05-12 Thread Tim Streater
On 12 May 2013 at 19:15, Paul van Helden wrote: > I can live with SELECT 1/2 vs SELECT 1.0/2. The problem is that there is no > way to specify a float when you insert into a NUMERIC. 1.0 turns into an > integer. Then you do a division on all rows with an SQL select and you

Re: [sqlite] SQLite and integer division 1/2=0

2013-05-12 Thread Paul van Helden
On Sun, May 12, 2013 at 1:54 PM, Michael Black wrote: > PRAGMA INTEGER_DIVISION would probably not have saved you this bug as you > would not have known to turn it on (default would have to be OFF for > backwards compatibility). I will use it on every connection I make in

Re: [sqlite] SQLite and integer division 1/2=0

2013-05-12 Thread Stephen Chrzanowski
I remember something about this kinda thing a LONG time ago. Correct me if I'm wrong, but the reason behind doing the int div first is due to integer division is faster than float division? ___ sqlite-users mailing list sqlite-users@sqlite.org

Re: [sqlite] SQLite and integer division 1/2=0

2013-05-12 Thread Yan Seiner
Simon Slavin wrote: On 12 May 2013, at 11:07am, Paul van Helden wrote: SELECT 1/2 returns 0 which to me is a little odd, but I see from this page: http://www.sqlite.org/sqllogictest/wiki?name=Differences+Between+Engines that most other engines do the same. A

Re: [sqlite] SQLite and integer division 1/2=0

2013-05-12 Thread Simon Slavin
On 12 May 2013, at 11:07am, Paul van Helden wrote: > SELECT 1/2 returns 0 which to me is a little odd, but I see from this page: > http://www.sqlite.org/sqllogictest/wiki?name=Differences+Between+Engines > that most other engines do the same. A few year ago I was

Re: [sqlite] SQLite and integer division 1/2=0

2013-05-12 Thread Michael Black
PRAGMA INTEGER_DIVISION would probably not have saved you this bug as you would not have known to turn it on (default would have to be OFF for backwards compatibility). Changing to float math everywhere might hurt some embedded systems. MYSQL was never designed to work on embedded systems. So