Re: [sqlite] Division accuracy

On 2014/09/25 19:32, Clemens Ladisch wrote: RSmith wrote: the time of finishing does not determine position alone, there are bonuses and penalties which doesn't stack up to integer values, but is measurable [...] Without going into too much detail about how bonuses and penalties are

Re: [sqlite] A new extension for sqlite to analyze the stringmetrics

use -Wl,-Bstatic to force static linking (use lib.a in preference to lib.dll.a when linking) use -static-libgcc to force a static link of the gcc runtime libraries ie gcc -s -O3 -mwin32 -pipe -march=i686 -mtune=i686 -shared -DSQLITE_API=__declspec(dllexport) -Wl,-Bstatic -mthreads

Re: [sqlite] GROUP BY: ambiguous column name?

On Thu, 25 Sep 2014 20:32:29 +0200 Mark Lawrence wrote: > I would have expected the group to work the same as the order, given > that I think of the group as happening on the result set before any > joins. ORDER BY is different. It's not even *related* to GROUP BY. To

Re: [sqlite] Division accuracy

On Thu, 25 Sep 2014 18:40:23 +0200 RSmith wrote: > It's much harder to make a mathematical positioning result than > simply working out if the current is better or worse than any > existing position. So you say. But someone is doing *something* to determine who comes before

Re: [sqlite] DEFAULT expression ignored for INTEGER PRIMARY KEYs?

Would dropping the non-functioning default clause from the schema be a "breaking change"? That is from: # sqlite3 dummy.db sqlite> CREATE TABLE x( ...> id INTEGER PRIMARY KEY DEFAULT (random()), ...> val VARCHAR ...> ); sqlite> .schema CREATE TABLE x( *id

Re: [sqlite] DEFAULT expression ignored for INTEGER PRIMARY KEYs?

On Thu Sep 25, 2014 at 03:59:55PM -0400, Richard Hipp wrote: > > I will make an effort to clarify this in the documentation. If you are going to keep this behaviour would it not make more sense to ensure that the table creation fails? The DEFAULT clause is pretty straight-forward and I don't

Re: [sqlite] DEFAULT expression ignored for INTEGER PRIMARY KEYs?

On Thu, Sep 25, 2014 at 3:30 PM, Mark Lawrence wrote: > > I understand that that behaviour exists and applies when an insert does > not provide a value, but I don't see the contradiction. The table > defines an *explicit* default that should (to my mind) override any > kind of

Re: [sqlite] DEFAULT expression ignored for INTEGER PRIMARY KEYs?

On Thu Sep 25, 2014 at 03:18:04PM -0400, Adam Devita wrote: > Your table definition seems to have a contradiction. The expression > INTEGER PRIMARY KEY is a special keyword that means 'auto-increment', > which would be a default value. I understand that that behaviour exists and applies when an

Re: [sqlite] DEFAULT expression ignored for INTEGER PRIMARY KEYs?

Your table definition seems to have a contradiction. The expression INTEGER PRIMARY KEY is a special keyword that means 'auto-increment', which would be a default value. DEFAULT (random() ) would contradict the auto-increment instruction. The row id was being used to generate the key. On Thu,

[sqlite] DEFAULT expression ignored for INTEGER PRIMARY KEYs?

Plan: CREATE TABLE x( id INTEGER PRIMARY KEY DEFAULT (random()), val VARCHAR ); INSERT INTO x(val) VALUES ('a'); SELECT * FROM x; Result: id val -- -- 1 a Expected result: id

Re: [sqlite] GROUP BY: ambiguous column name?

This is just the way the SQL standard mandates it to work, not an oddity specific to SQLite. I imagine the optimizer is probably smart enough to not do the work twice here, but someone else will need to chime in to confirm that. If you want to be sure, you can use a CTE. On Thu, Sep 25, 2014 at

Re: [sqlite] GROUP BY: ambiguous column name?

On Thu Sep 25, 2014 at 01:43:20PM -0500, Cory Nelson wrote: > GROUP BY works on your input data, not output data. You want to GROUP > BY COALESCE(x.id, y.id) That describes the behaviour I demonstrated, but not the reasoning behind it nor the documentation pointing to that reasoning. Is SQLite

Re: [sqlite] GROUP BY: ambiguous column name?

GROUP BY works on your input data, not output data. You want to GROUP BY COALESCE(x.id, y.id) On Thu, Sep 25, 2014 at 1:37 PM, Mark Lawrence wrote: > On Thu Sep 25, 2014 at 08:32:29PM +0200, Mark Lawrence wrote: > > GROUP BY on a result column fails with "ambiguous column name":

Re: [sqlite] GROUP BY: ambiguous column name?

On Thu Sep 25, 2014 at 08:32:29PM +0200, Mark Lawrence wrote: > GROUP BY on a result column fails with "ambiguous column name": > > SELECT > COALESCE(x.id, y.id) AS id > FROM > y > LEFT JOIN > x > ON > x.id = y.fk > ORDER BY > id > ;

[sqlite] GROUP BY: ambiguous column name?

Don't know if this is a bug or intended behaviour. Given the following schema: CREATE TABLE x( id INTEGER ); CREATE TABLE y( id INTEGER, fk INTEGER REFERENCES x(id) ); ORDER BY on a result column name is allowed: SELECT COALESCE(x.id, y.id)

Re: [sqlite] A new extension for sqlite to analyze the stringmetrics

Since the source is there, I may give-it-a-go myself this eve, if it's not too difficult to set up (the extension should be easy, but I don't know about the other project that provides the actual implementation). If I do, I'll make it statically linked so there will be no dependencies. For

Re: [sqlite] A new extension for sqlite to analyze the stringmetrics

thanks for your help Dave. As I'm not a real developer but just an SQLite user, I cannot compile the DLL -- I just downloaded the compiled DLL. So your guess is correct. I'm working on a Win 8.1 PC and there's no libgcc-s-dw2-1.dll on my system. gert 2014-09-25 19:26 GMT+02:00 dave

Re: [sqlite] Division accuracy

RSmith wrote: > the time of finishing does not determine position alone, there are > bonuses and penalties which doesn't stack up to integer values, but is > measurable [...] Without going into too much detail about how bonuses > and penalties are calculated How can anybody help you without

Re: [sqlite] A new extension for sqlite to analyze the stringmetrics

Update: never mind what I mentioned below; he said he is using mingw (it really does help to read the entire thread!) But I have tried and failed to load it (the prebuilt binary) myself; I notice there is a further dependency on libgcc-s-dw2-1.dll, so maybe that's the origin of Gert's problem.

Re: [sqlite] Forcing a manual fsync in WAL/NORMAL mode

On 25 Sep 2014, at 10:53am, Deon Brewis wrote: > In the majority of my application, I'm fine running in WAL/NORMAL and lose > some committed transactions if the application crashes. When you lose data are you ever losing part of a transaction ? SQLite is allowed to lose

Re: [sqlite] Forcing a manual fsync in WAL/NORMAL mode

On Thu, Sep 25, 2014 at 5:53 AM, Deon Brewis wrote: > In the majority of my application, I'm fine running in WAL/NORMAL and lose > some committed transactions if the application crashes. (Which actually > just > happened to me on iOS - I thought that can only happen on a full

Re: [sqlite] A new extension for sqlite to analyze the stringmetrics

Gert: Did you build it yourself, or download a binary? Are you running on XP, or a later OS? I ask these questions because I have seen this code due to c dll runtime issues like (msvcrt100.dll), etc. Depending on what tool was used to build the binary, some changes may need to be made to the

[sqlite] Fwd: Re: sqlite generate strange field name in union...

On 2014/09/25 05:04, 麦田观望者 wrote: Hi, RSmith‍: I can't find a method to reply you message,so i send it to you mailbox directly, sorry for disturber. you say: >It is just whatever the Query producer feels comfortable writing in the >header to identify the column‍ maybe you are right on the

[sqlite] Forcing a manual fsync in WAL/NORMAL mode

In the majority of my application, I'm fine running in WAL/NORMAL and lose some committed transactions if the application crashes. (Which actually just happened to me on iOS - I thought that can only happen on a full O/S crash - not just app. But oh well). However, every now and again I need to

Re: [sqlite] Division accuracy

On 2014/09/25 15:43, James K. Lowden wrote: On Thu, 25 Sep 2014 10:36:31 +0200 Clemens Ladisch wrote: Yes, and yes, absolutely. In that case the order is established by the user, and can be captured by the application as integers, and stored in the database. The problem

Re: [sqlite] group_concat query performance

On Thu, Sep 25, 2014 at 9:34 AM, Richard Hipp wrote: > Thanks for the schema. Unfortunately, that was insufficient to reproduce > the problem. Are you able to send me the actual database file, via private > email? > Thanks for sending the data. But I'm still not able to

Re: [sqlite] A new extension for sqlite to analyze the string metrics

Andea, Seems like a very interesting extension to me. I cannot make it run on Windows. I get error code 0xc00012f. Any idea why this is ? thanks gert 2014-09-25 10:11 GMT+02:00 Andrea Peri : > Hi, > for who eventually interested. > > Fr a specific internal use I develope

Re: [sqlite] group_concat query performance

Richard Hipp wrote: > Note that the use of AUTOINCREMENT has nothing to do with your > problem - I just see people using it a lot and I'm wondering why > it is so popular MySQL needs it. Every search for "autoincrement" will find it. This keyword's name appears to imply that you do _not_ get

Re: [sqlite] Division accuracy

On Thu, 25 Sep 2014 10:36:31 +0200 Clemens Ladisch wrote: > > The first question I'd have is: Where are the ordering criteria, > > and why aren't they in the database? Someone is imposing an order, > > but the basis for it is not included in the database design. >

Re: [sqlite] group_concat query performance

Thanks for the schema. Unfortunately, that was insufficient to reproduce the problem. Are you able to send me the actual database file, via private email? Aside: Why are you using AUTOINCREMENT? Do you really need it? Are you aware that there are space and time penalties for using

Re: [sqlite] group_concat query performance

Hi, Thank you for the fast response. Below this line you'll find sqlite3.exe's output: CREATE TABLE tblFolderNames ( pkintFolderNameID integer PRIMARY KEY AUTOINCREMENT NOT NULL, txtNametext NOT NULL UNIQUE COLLATE nocase ); CREATE TABLE tblFolders ( pkintFolderID

Re: [sqlite] 50% faster than 3.7.17

On Tue, 2014-09-23 at 17:48 +0100, David Woodhouse wrote: > That looks really promising; thanks for all this work. > > Tristan, you have a comprehensive set of benchmarks for Evolution's > addressbook; is it possible for someone else to run those or would it > take more of your time to babysit

Re: [sqlite] group_concat query performance

Please run the ".fullschema" command on your database and send us the output. I mean by this: (1) Download the latest version of sqlite3.exe from the website. (2) Run "sqlite3 YOURDATABASE .fullschema >out.txt" (3) Include the text of out.txt in the body of a follow-up email. That information

Re: [sqlite] group_concat query performance

On Thu, Sep 25, 2014 at 12:54 PM, Kraijenbrink - FixHet - Systeembeheer < kraijenbr...@fixhet.nl> wrote: > > SELECT GROUP_CONCAT(Parent.fkintFolderID,'\') FilePath > FROM tblFolderNestedSets Node > , tblFolderNestedSets Parent > WHERE Node.intLeft BETWEEN Parent.intLeft AND Parent.intRight > AND

[sqlite] group_concat query performance

Hi all, I've searched through this forum but couldn't find any related topic regarding my question. I'm having serious performance problems (queries up to 20/sec) while running a SQLite query since i added a group_concat clause. The query looks like: SELECT

Re: [sqlite] Non-optimal query plan

>> 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 populated table) sqlite

Re: [sqlite] 50% faster than 3.7.17

On Wed, 2014-09-24 at 19:36 -0600, Keith Medcalf wrote: > > 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

Re: [sqlite] Division accuracy

James K. Lowden wrote: > RSmith wrote: >> ID | Next | Data >> 1 | 4 | 'First Row' >> 2 | 3 | 'Eventual Fourth Row' >> 3 | 1 | 'Last Row' >> 4 | 5 | 'New Second Row' >> 5 | 2 | 'New Third Row' > > The first question I'd have is: Where are the ordering

Re: [sqlite] Division accuracy

I've read through the rest of the thread on this so far. I like the linked list idea, as updating three rows seems to be the better way of doing things, but the question does remain 'what drives the sort reasoning?', however, if you don't want to get that deep into CTEs and stuff for the linked

[sqlite] A new extension for sqlite to analyze the string metrics

Hi, for who eventually interested. Fr a specific internal use I develope a new simple extension for sqlite. The LibStringmetrics. https://github.com/aperi2007/libstringmetrics It is based on the LibSimmetrics c lib from Johnathan Botha - available from here:

Re: [sqlite] 50% faster than 3.7.17

In the vein of configurability, and in a day dream I just had, it would be nice (But probably not possible as there could be compiler directives you can't use at the same time) that we could have a single output DLL/SO/whatever dumped from the compiler that had everything available, then, via

Re: [sqlite] Division accuracy

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