Floating point values are represented as * 2 ^^
The egde cases are inserting in sorted order.
Descending:
The first row is tagged with 1.0
Each new first row is tagged with 1/2 the previous.
This will either lose 1 bit of mantissa or decrement the exponent.
This means you will run out of
Please try the latest version of SQLite on trunk to see if that works
better. Specifically, apply the patch at
http://www.sqlite.org/src/vpatch?from=b2c89ef49cd1&to=ef30e0352b3d
--
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqli
Interesting. From that code you might want to try something like this:
SELECT uid, vcard, bdata
FROM folder_id
WHERE uid in ( select uid FROM email_list where value like 'p%'
union
select uid from folder_id where nickname LIKE 'p%'
union
Dr. Hipp,
I did the "port" (which was easy thanks to your excellent work with
EBCDIC which is already in the base) which is being distributed via
the CBTTape (http://cbttape.org) web site. I would be happy to test
any fixes which might desired, if that is in accordance with your
policy. I can unde
52
>-Original Message-
>From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
>boun...@sqlite.org] On Behalf Of RSmith
>Sent: Wednesday, 24 September, 2014 10:49
>To: sqlite-users@sqlite.org
>Subject: [sqlite] Division accuracy
>
>I'm trying to find what the limit is for dividing in
On Wed, Sep 24, 2014 at 12:48 PM, 麦田观望者 wrote:
> if we have to tables:
> create table t1(f1 integer,f2 integer);
> create table t2(f1 integer,f2 integer);
>
> the fellowing sql generate a result set with a strange field name:
>
> select t1.f1 from t1
> union
> select t2.f1 from t2
>
> we expec
On Wed, Sep 24, 2014 at 10:27 AM, k wrote:
> SQLite team,
> compile of sqlite3 v3.8.6 amalgamation failed on zOS 2.1 with err CCN3045
> undeclared identifier sqlite3CtypeMap in function
> sqlite3_compileoption_used. I notice that this was raised previously to the
> mailing list by John McKown. Pl
On 2014/09/24 18:48, 麦田观望者 wrote:
if we have to tables:
create table t1(f1 integer,f2 integer);
create table t2(f1 integer,f2 integer);
the fellowing sql generate a result set with a strange field name:
select t1.f1 from t1
union
select t2.f1 from t2
we expect a column named "f1" but we ge
if we have to tables:
create table t1(f1 integer,f2 integer);
create table t2(f1 integer,f2 integer);
the fellowing sql generate a result set with a strange field name:
select t1.f1 from t1
union
select t2.f1 from t2
we expect a column named "f1" but we get "t1.f1"
but "select t1.f1 fro
SQLite team,
compile of sqlite3 v3.8.6 amalgamation failed on zOS 2.1 with err
CCN3045 undeclared identifier sqlite3CtypeMap in function
sqlite3_compileoption_used. I notice that this was raised previously to
the mailing list by John McKown. Please consider adding the necessary
fixes to the SQ
On Wed, 24 Sep 2014 20:51:38 +0200
RSmith wrote:
> I was thinking in stead of maybe having a prev and next column, to
> just have a next column which points to an ID.
...
> ID | Next | Data
> 1 | 4 | 'First Row'
> 2 | 3 | 'Eventual Fourth Row'
> 3 | 1 | 'Last Row'
> 4 | 5 |
At 21:53 24/09/2014, you wrote:
> If the default cannot be represented exactly, its rounding will be the
> least of the difficulties.
Not always: in scientific applications I've had column values default
to plenty of irrational numbers: fractions of pi, sqrt(2.0) etc.
My main gripe is when t
RSmith wrote:
> On 2014/09/24 22:24, Clemens Ladisch wrote:
>> RSmith wrote:
>>> I'm liking the link list but did not go with it due to an expensive insert
>>> function
>>
>> Yes, you have to update two references (with prev/next), but how is that
>> worse than the update of all SortOrder values?
On Wed, 24 Sep 2014 20:53:32 +0100
Nathaniel Trellice wrote:
> > > You are doing CREATE TABLE statements based on text from an
> > > untrusted user? Really?
>
> > My reaction exactly.
>
>
> I'm writing a library so the safety of the input is out of my hands
> and in that of the application
On 24 Sep 2014, at 8:53pm, Nathaniel Trellice wrote:
> Imagine an application writer who's instructed my library to use pi (say) as
> a column's default. When reading the value out of the database, they may want
> to test if the value equals the default
Sorry, but you don't do this with REALs
No problem! Let us know which solution you picked!
Alessandro
2014-09-24 22:50 GMT+02:00 RSmith :
> Thanks Alessandro, this will work, it's just... ugly... and for other
> reasons I'd prefer the sort order to be numeric. If however it doesn't work
> out, this might just be the best so
Thanks Alessandro, this will work, it's just... ugly... and for other reasons I'd prefer the sort order to be numeric. If however it
doesn't work out, this might just be the best solution, ugly or not. :)
Thanks!
Ryan
On 2014/09/24 21:33, Alessandro Marzocchi wrote:
Which language are you using
On 2014/09/24 22:24, Clemens Ladisch wrote:
RSmith wrote:
Clemens I'm liking the link list but did not go with it due to an expensive
insert function
Yes, you have to update two references (with prev/next), but how is that
worse than the update of all SortOrder values?
Well the insert runs
RSmith wrote:
> Clemens I'm liking the link list but did not go with it due to an expensive
> insert function
Yes, you have to update two references (with prev/next), but how is that
worse than the update of all SortOrder values?
> how would I get a normal SQL query ORDER BY clause to use that?
Hi Richard, James,
> > > * SQL injection attacks;
> > >
> >
> > You are doing CREATE TABLE statements based on text from an untrusted
> > user? Really?
> My reaction exactly.
I'm writing a library so the safety of the input is out of my hands and in that
of the application writers who wor
Which language are you using? Andres ideas could give some nice solutions
using blob, even without a specific arbitrary precision library. eg:
Splitting data with an encoding like
(In-order id) (Out-of-order id)
In-order insert are encoded with an encoding that guarantees lexicografical
ordering (
Thanks all for the responses.
Thanks Scott for the calcs, I had somehow imagined using a set of values might yield more iterations, but of course that is just
wishful thinking, the bits are the bits.
The idea from Alessandro is great if I could control or guess where the next inserts will be,
Is the approach of 'just try it and if it goes badly fix it' doable?
mid = (lo + hi) / 2;
if ((mid <= lo) || (mid >= hi)) {
Fix it
}
John Hascall
IT Services
Iowa State Univ.
> On Sep 24, 2014, at 11:49 AM, RSmith wrote:
>
> I'm trying to find what the limit is for dividing in terms of a
RSmith wrote:
> I have one program that inserts values to a table and determine sort
> order using one standard trick that has a REAL column named
> "SortOrder" [...]
> reassign SortOrders simply in Integer steps: 1, 2, 3 etc.
>
> ID | SortOrder | Data
> 1 | 1 | 'First Row'
> 2 | 4
RSmith wrote:
> how can I accurately establish how many total-then-divide-by-2's a set
> of co-values in 64-bit FP guise can withstand before the difference is
> too small to make sense to the sorter in SQLite?
Internally, SQLite uses 64-bit IEEE floating-point numbers, which is the
same as "doubl
On 24/09/14 06:19, Simon Slavin wrote:
> How much max is max ?
Not giving up ACID. But for example stat4 is better than the default stat1.
Memory mapping (especially on 64 bit) is great. So is WAL. All are off by
default.
If you want to give up ACID then you should really be on your own to
Interesting trick.
I remember having seen something similar for Trees ...
Found it ...
http://www.tclcommunityassociation.org/wub/proceedings/Proceedings-2011/StephenHuntley/Huntley_Tcl2011.pdf
Well, he uses bignums.
Maybe this could be degenerated into a linear list, which is what you
are lo
Hello,
Why don't you try the same approach with 64 bit integers? You could
insert consecutive items at 0x10 intervals, in this way you would
be allowed about 4G items to me inserted in any order you like. You could
also change the interval to change the maximum consecutive items inserte
On Wed, Sep 24, 2014 at 10:49 AM, RSmith wrote:
> I'm trying to find what the limit is for dividing in terms of accuracy.
>
> Basically I have one program that inserts values to a table and determine
> sort order using one standard trick that has a REAL column named
> "SortOrder" which gets the v
I'm trying to find what the limit is for dividing in terms of accuracy.
Basically I have one program that inserts values to a table and determine sort order using one standard trick that has a REAL column
named "SortOrder" which gets the value Highest_previous_value+1 if an insert happens with s
On Wed, 24 Sep 2014 09:49:36 -0400
Richard Hipp wrote:
> > * SQL injection attacks;
> >
>
> You are doing CREATE TABLE statements based on text from an untrusted
> user? Really?
My reaction exactly.
> > * floating point value rounding in conversion to/from text;
If the default cannot be
On Wed, 24 Sep 2014 11:03:53 +0530
Prakash Premkumar wrote:
> Let's say an output of the join is:
>
> r11,r21,r31
> r11,r21,r32
> r11,r21,r33
>
> where r1i is the i th row in T1, r2i is the i th row in T2 and r3i is
> the ith row in T3:
>
> sqlite produces 3 result rows , but I would like to p
On 2014/09/24 16:52, Rob Golsteijn wrote:
Hi List,
I was looking at the query plan of a rather simple query, but I don't
understand why sqlite would choose this query plan.
...I was surprised that sqlite came up with the inferior query plan...
Note: After an "analyze aaa" (on a decently popu
On Wed, Sep 24, 2014 at 10:52 AM, Rob Golsteijn
wrote:
>
> Sqlite decides to create an AUTOMATIC INDEX (time complexity O(n log n))
> which it then uses to iterate table aaa1. This index is not re-used for
> anything else (it can't be re-used since 'type' is not used anywhere else)
> so only the
Hi List,
I was looking at the query plan of a rather simple query, but I don't
understand why sqlite would choose this query plan.
For the following example:
create table aaa(id INTEGER, name_id INTEGER, type CHAR);
create table bbb(name_id INTEGER, name CHAR);
create index ix_aaa ON aaa(id);
c
On Wed, Sep 24, 2014 at 7:36 AM, Nathaniel Trellice
wrote:
> Dear list members,
>
> I'm trying to set a default value for a column in my "CREATE TABLE"
> statement. I really, really want to bind the default value (using
> sqlite3_bind_*) rather than expressing it in SQL text to avoid the
> follow
It does sound like that, like an Object Relational Mapping. And maybe
someone has already done this.
Googling "ORM sqlite c++"
Yields a lot of existing ORM projects that support sqlite, so maybe Prakash
doesn't need to work so hard on implementing that part of his project
himself.
-dave
> -O
On Wed, Sep 24, 2014 at 9:22 AM, sqlitekyounoii
wrote:
> Hi, I'm Takashi
>
> I use sqlite in Android.
> I have access to SQLite using the API of Android.
> Today, my sqlite's db file have collapsed.
>
Takashi: This sentence did not translate well: "my db file have
collapsed". We do not know
On 2014/09/24 15:06, Prakash Premkumar wrote:
Thanks a lot , Simon and Hick,
What I am looking for is , instead of iterating through the result which
sqlite provides and then form the respective objects and setting pointers,
is it possible to hack sqlite to fill in the objects this way.
I would
This is a really terrible idea. It is dependent on the internals of sqlite
which makes it extremely fragile.
Have you profiled your code to show that this is your bottleneck? If so,
it's still a terrible idea,
but at least has some basis for considering the idea.
John
On Wed, Sep 24, 2014 at 8:
Sounds like you want to "hack up" where the resultrow is returned for each
table row visited as the query is processed, but only once for each table row
satisfying the query. You can certainly do this -- the source code is freely
available.
I am sure you could "hack that up" if you want, but
On Wed, 2014-09-24 at 06:13 -0600, Keith Medcalf wrote:
>
> Would it not be more efficient to skip the join altogether since all
> you want is the list of uid's, and assuming that you have maintained
> the referential integrity of your database mail_list(list_uid)
> references main(uid)?
>
> SELE
Hi, I'm Takashi
I use sqlite in Android.
I have access to SQLite using the API of Android.
Today, my sqlite's db file have collapsed.
I heard that db file was collapsed when mode when it was changed to jarnal from
wal log mode.
Is this true?
it is not possible to upgrade my Android os by certain
On 24 Sep 2014, at 2:13pm, jose isaias cabrera wrote:
> This would be a nice set of options. On my case, I would set all connections
> to our project to be" max_performance", as it is what we need. Just thinking
> out loud.
How much max is max ? Are you willing to give up ACID ? Are you wi
"Roger Binns" wrote...
On 22/09/14 10:48, Richard Hipp wrote:
But if you have any new ideas on how we can further reduce the I/O, we'd
love to hear from you.
The single biggest problem for me is defaults. SQLite supports memory
mapped i/o which has many advantages. The stat4 analyze does a
Thanks a lot , Simon and Hick,
What I am looking for is , instead of iterating through the result which
sqlite provides and then form the respective objects and setting pointers,
is it possible to hack sqlite to fill in the objects this way.
I would like to prevent the extra iteration through the
On 24 Sep 2014, at 1:33pm, Nathaniel Trellice wrote:
> Without the INSERT call, my test code runs through without crashing.
> Attempting the INSERT call in the shell tool crashes it too. Following your
> advice, the output of the .schema command looks dodgy:
>
> CREATE TABLE test_table (name
I think that the diagrams on http://www.sqlite.org/lang_createtable.html are
the generic parsing and construction rules for "expr" everywhere an "expr" can
be used. The text description further down the page describes restrictions and
usage particular to the CREATE TABLE statement itself.
Und
On Wed, Sep 24, 2014 at 8:33 AM, Nathaniel Trellice
wrote:
> Attempting the INSERT call in the shell tool crashes it too. Following
> your advice, the output of the .schema command looks dodgy:
>
> CREATE TABLE test_table (name TEXT DEFAULT ('joe'), interest TEXT DEFAULT
> (?1));
>
> I'm an SQLi
Hi Simon,
Thanks for the quick reply.
Without the INSERT call, my test code runs through without crashing. Attempting
the INSERT call in the shell tool crashes it too. Following your advice, the
output of the .schema command looks dodgy:
CREATE TABLE test_table (name TEXT DEFAULT ('joe'), in
Would it not be more efficient to skip the join altogether since all you want
is the list of uid's, and assuming that you have maintained the referential
integrity of your database mail_list(list_uid) references main(uid)?
SELECT list_uid
FROM mail_list
WHERE email LIKE 'al%'
UNION
SELECT
My guess would be that finalizing the create table statement makes the bound
value go out of scope and thus be unavailable to the insert statement.
Bound values reside somewhere in the internal prepared statement structure and
do not get copied into the database file, even if they happen to be d
How about writing something that uses the SQLite Api as intended and works
first?
-Ursprüngliche Nachricht-
Von: Prakash Premkumar [mailto:prakash.p...@gmail.com]
Gesendet: Mittwoch, 24. September 2014 12:54
An: General Discussion of SQLite Database
Betreff: Re: [sqlite] Results of Joins
On 24 Sep 2014, at 12:36pm, Nathaniel Trellice wrote:
> The example code, below, highlights the problem I'm having. The code creates
> a table with a bound default value for one of the two columns. The statement
> is prepared (no syntax error warning), and the value bound successfully. But
>
On 24 Sep 2014, at 11:53am, Prakash Premkumar wrote:
> Thanks a lot Hick,for your approach.
> With the approach you suggested, we are creating extra queries and if the
> join is on n tables there will be n+1 queries , and each query will have to
> go through a query planning stage.
> Is there an
Dear list members,
I'm trying to set a default value for a column in my "CREATE TABLE" statement.
I really, really want to bind the default value (using sqlite3_bind_*) rather
than expressing it in SQL text to avoid the following problems:
* SQL injection attacks;
* floating point value round
Thanks a lot Hick,for your approach.
With the approach you suggested, we are creating extra queries and if the
join is on n tables there will be n+1 queries , and each query will have to
go through a query planning stage.
Is there an alternative idea ?
Thanks a lot
Prakash
On Wed, Sep 24, 2014 at
On Fri, 2014-09-19 at 21:14 -0400, Richard Hipp wrote:
> The 50% faster number above is not about better query plans.
Speaking of better query plans, though... here's a query which takes
about 1700ms on my data set, followed by a couple of optimisations which
seem like they might be generically u
You are free to build your own result conversion routine on top of the SQLite
Api.
May I suggest selecting the rowids of the tables too i.e.
SELECT t1.rowid, t2.rowid, t3.rowid, <.. more fields ...> FROM <...your
join...>;
When you first come across a new rowid you can create your memory obje
Thanks for the reply Clemens. Yes.I'm sorry. It will not have 8 columns.
When one result row comes in , I want to create an object for each table
(My application map one struct to one table) and put the columns of
respective tables in their individual structs. i,e I would set column 0
and column
Prakash Premkumar wrote:
> Let's say I have tables T1,T2 and T3 with 2 columns each and I am joining
> them.
> The result rows will have 8 columns each.
No. The result will have between 4 and 6 columns, depending on how you
do the joins.
Example:
CREATE TABLE T1(ID1, Name);
INSERT INTO "T1" VA
61 matches
Mail list logo