[sqlite] order by not working in combination with random()

On 27 Aug 2015 at 18:49, Simon Slavin wrote: > On 27 Aug 2015, at 6:41pm, Domingo Alvarez Duarte dev.dadbiz.es> > wrote: > >> select random(), random() from blah order by random() >> >> >> >> Error ambiguous column "random()" near "order by". > > Thing is, that's not ambiguous. I don't really

[sqlite] Insertion into the large DB drastically slows down at some point

How many indexes on your data? Can you DROP your indexes for the insert, then CREATE INDEX them again after all the content is in place? -- D. Richard Hipp drh at sqlite.org

[sqlite] Insertion into the large DB drastically slows down at some point

I'm with Simon. If you're writing 5 million rows to memory, the OS has to move unused memory out to disk. I'd say write the data to disk (SSD if you can) and check if the data written out is at a consistent speed. On Thu, Aug 27, 2015 at 10:29 PM, Simon Slavin wrote: > > On 28 Aug 2015, at

[sqlite] order by not working in combination with random()

>I can see both sides of this debate, whether or not random() should be >evaluated twice in this context: > >select random() from blah order by random() There are 2 distinct and volontary function invokations, so I don't see how SQL engine would decide not to perform the second call. >So let

[sqlite] Insertion into the large DB drastically slows down at some point

On 08/27/2015 19:45, Richard Hipp wrote: > How many indexes on your data? > > Can you DROP your indexes for the insert, then CREATE INDEX them again > after all the content is in place? Only indexes needed during the insert are in DB. Only 2 indexes exist, plus there are several "uniq"

[sqlite] Insertion into the large DB drastically slows down at some point

On 08/27/2015 19:29, Simon Slavin wrote: > Your computer has a certain amount of free memory. Once your database is > bigger than that size the computer has to keep moving parts of the database > into storage so it has room for the new data. > > I will guess that if you got more RAM in your

[sqlite] order by not working in combination with random()

On this case: select random(), random() from blah order by random() ? Error ambiguous column "random()" near "order by". Cheers ! ? > Thu Aug 27 2015 6:48:54 pm CEST CEST from "Scott Robison" > Subject: Re: [sqlite] order by not working in >combination with random() > > On Thu,

[sqlite] autoincrement field

On 8/27/15, Levente Kovacs wrote: > On Thu, 27 Aug 2015 23:40:15 +0200 > Jean-Christophe Deschamps wrote: > >> http://www.sqlite.org/c3ref/last_insert_rowid.html is what you need. > > Yes, thanks a lot! You should probably also read the documentation on AUTOINCREMENT

[sqlite] Insertion into the large DB drastically slows down at some point

I build a fairly large DB, with major tables having 800k..5M rows, with several relationships between tables. At ~30% into the insertion process it slows down rapidly. Records #171k..172k are still fast, and records #172k...173k are already ~10 times slower. (all records are more or less

[sqlite] why I don't get an error ?

Hi Simon, > > So you can declare a column as INTEGER and supply the string '1234' > and it will be converted to the number 1234 before it is stored. You > can check this out using > > SELECT x,typeof(x) FROM myTable > > This means that the conversion is done once on storage rather than > each

[sqlite] order by not working in combination with random()

On 27 Aug 2015, at 6:41pm, Domingo Alvarez Duarte wrote: > select random(), random() from blah order by random() > > > > Error ambiguous column "random()" near "order by". Thing is, that's not ambiguous. I don't really care how SQLite implements it, but there is no excuse for

[sqlite] FTS5

On 8/27/15, Scott Doctor wrote: > > Is FTS5 fully tested and part of the current version, or is it > still experimental? > It is merged with trunk, but it is not included in a standard build. I'd call it experimental yet. -- D. Richard Hipp drh at sqlite.org

[sqlite] order by not working in combination with random()

At 16:00 27/08/2015, you wrote: > >An *ORDER BY* clause in SQL specifies >that a SQL SELECT statement >returns a result set with the >rows being sorted by the values of one

[sqlite] order by not working in combination with random()

On 2015-08-27 04:50 PM, Scott Hess wrote: > I keep thinking I remember a thread from years ago where a lot of this was > hashed out, but I cannot find it. > //// > There is already some precedent for this, because ORDER BY RANDOM() must > internally be holding the random values used fixed

[sqlite] autoincrement field

On 8/27/2015 5:25 PM, Levente Kovacs wrote: > Is there any way to get the 'id' of newly inserted row? http://www.sqlite.org/c3ref/last_insert_rowid.html http://www.sqlite.org/lang_corefunc.html#last_insert_rowid -- Igor Tandetnik

[sqlite] explain plan change between SQLite 3.8.3.1 and 3.8.11.1

On Thu, Aug 27, 2015 at 4:20 PM, Hick Gunter wrote: > But you have to run the query as opposed to just parsing EXPLAIN > The doc says: The authorizer callback is invoked as SQL statements are being compiled by sqlite3_prepare() [and co.] So the query to prepare is different indeed (drop

[sqlite] why I don't get an error ?

On 2015-08-27 04:06 PM, Nicolas J?ger wrote: > Hi Darko, Igor and others. > >so the only reason to define datatype in sqlite is for the size on >the disk ? > >so why not just only using `BLOB` (excepted for `INTEGER PRIMARY >KEY`) ? > >being less persmissive wouldn't make

[sqlite] explain plan change between SQLite 3.8.3.1 and 3.8.11.1

On Thu, Aug 27, 2015 at 3:37 PM, Richard Hipp wrote: > On 8/27/15, Clemens Ladisch wrote: > > Dominique Devienne wrote: > >> how can we programatically reliably discover which (v)tables a view > >> accesses, staying in documented behavior land? > > > > With an authorizer callback: > >

[sqlite] order by not working in combination with random()

On 2015-08-27 03:29 PM, Simon Slavin wrote: > > Sure. I chose to use an alias just to emphasise how wrong the result looked. > However, I have seen code written by teams where the person writing the > query has no real idea whether they're querying a TABLE, a VIEW, or a virtual > table.

[sqlite] explain plan change between SQLite 3.8.3.1 and 3.8.11.1

On 27 Aug 2015, at 3:20pm, Hick Gunter wrote: > But you have to run the query as opposed to just parsing EXPLAIN You have to _prepare() it. You don't have to use _step() . Though I suppose you should _finalize() it if you don't intend to use _step() . Simon.

[sqlite] why I don't get an error ?

On 27 Aug 2015, at 3:06pm, Nicolas J?ger wrote: > so the only reason to define datatype in sqlite is for the size on > the disk ? Nope. It has no effect on the size on disk. > so why not just only using `BLOB` (excepted for `INTEGER PRIMARY > KEY`) ? > > being less persmissive wouldn't

[sqlite] football.db - New 2015/16 Seasons - English Premier League, Bundesliga, etc.

Hello, I've put together a new sport.db (football.db) quick starter sample using the Mauritius Premier League [1] to get you started creating your own leagues/cups/etc. from scratch. You can test drive the quick starter sample with a single command e.g. $ sportdb build That's it. Now

[sqlite] explain plan change between SQLite 3.8.3.1 and 3.8.11.1

Dominique Devienne wrote: > how can we programatically reliably discover which (v)tables a view > accesses, staying in documented behavior land? With an authorizer callback: http://www.sqlite.org/c3ref/set_authorizer.html Regards, Clemens

[sqlite] FTS5

Is FTS5 fully tested and part of the current version, or is it still experimental? Scott Doctor scott at scottdoctor.com --

[sqlite] order by not working in combination with random()

On 27 Aug 2015, at 9:11am, Domingo Alvarez Duarte wrote: > A very instructive post, could you give your opinion about what should be the > behavior for the "WHERE" clause ? > > I meam if we have a function on the field definition and reference it on the > "WHERE" clause: > > CREATE TABLE

[sqlite] why I don't get an error ?

Nope. The reason to define datatype in SQLite is because other databases do so. The difference ist hat SQLite does not enforce typing but uses the concept of affinity instead. -Urspr?ngliche Nachricht- Von: Nicolas J?ger [mailto:jagernicolas at legtux.org] Gesendet: Donnerstag, 27.

[sqlite] explain plan change between SQLite 3.8.3.1 and 3.8.11.1

But you have to run the query as opposed to just parsing EXPLAIN -Urspr?ngliche Nachricht- Von: Richard Hipp [mailto:drh at sqlite.org] Gesendet: Donnerstag, 27. August 2015 15:37 An: General Discussion of SQLite Database Betreff: Re: [sqlite] explain plan change between SQLite 3.8.3.1

[sqlite] FTS4 - simulate phrase with NEAR/0

On Thu, Aug 27, 2015 at 1:02 PM, Scott Hess wrote: > NEAR/0 will probably not care about ordering. Ah, yes. You are correct. This match expression: MATCH 'column:word1 NEAR/0 column:word2 NEAR/0 column:word3' matcher both "word1 word2 word3" and "word3 word2 word1" phrases. So, it is a no go.

[sqlite] explain plan change between SQLite 3.8.3.1 and 3.8.11.1

On Thu, Aug 27, 2015 at 1:08 PM, Richard Hipp wrote: > On 8/27/15, Dominique Devienne wrote: > > > > I understand that https://www.sqlite.org/opcode.html doesn't explicitly > say > > what VOpen's p4 column will contain, so I guess one could argue this is > > undocumented behavior we should not

[sqlite] order by not working in combination with random()

On Thu, Aug 27, 2015 at 11:55 AM, Jean-Christophe Deschamps < jcd at antichoc.net> wrote: > > I can see both sides of this debate, whether or not random() should be >> evaluated twice in this context: >> >> select random() from blah order by random() >> > > There are 2 distinct and volontary

[sqlite] order by not working in combination with random()

On 2015-08-27 03:03 AM, James K. Lowden wrote: > On Wed, 26 Aug 2015 13:39:09 +0100 > Simon Slavin wrote: > >> On 26 Aug 2015, at 11:27am, tonyp at acm.org wrote: >> >>> Plus, it apparently recognizes if the random() expression in the >>> ORDER BY is the same as the SELECT one and again sort

[sqlite] FTS4 - simulate phrase with NEAR/0

Hi: My name is Martin, I have been using SQLite for web development for several years. Can't say enough good things about it. Recently, I was asked to implement full-text search in an application with up to 1 million items, each with several columns having AND, OR and a phrase search

[sqlite] order by not working in combination with random()

On Thu, Aug 27, 2015 at 11:41 AM, Domingo Alvarez Duarte < sqlite-mail at dev.dadbiz.es> wrote: > On this case: > > select random(), random() from blah order by random() > > > > Error ambiguous column "random()" near "order by". > > Cheers ! > Are you saying ambiguous column is what *should* be

[sqlite] FTS4 - simulate phrase with NEAR/0

On Thu, Aug 27, 2015 at 11:29 AM, Martin Kucej < i.librarian.software at gmail.com> wrote: > On Thu, Aug 27, 2015 at 1:02 PM, Scott Hess wrote: > > NEAR/0 will probably not care about ordering. > > Ah, yes. You are correct. This match expression: > > MATCH 'column:word1 NEAR/0 column:word2

[sqlite] FTS4 - simulate phrase with NEAR/0

On Thu, Aug 27, 2015 at 10:50 AM, Martin Kucej < i.librarian.software at gmail.com> wrote: > Recently, I was asked to implement full-text search in an application > with up to 1 million items, each with several columns having AND, OR > and a phrase search capabilities. I can only work with FTS4,

[sqlite] order by not working in combination with random()

On Thu, Aug 27, 2015 at 10:06 AM, Jean-Christophe Deschamps < jcd at antichoc.net> wrote: > At 16:00 27/08/2015, you wrote: > >> >> An *ORDER BY* clause in SQL specifies >> that a SQL SELECT statement >> returns a

[sqlite] SQLite3 to 64-bit DLL

Hello Papa, I just link it in as static library. Just include the code in each project or create a static lib project. It depends on the rest of your application though. If you're trying to static link the rest of the application then a static linked Sqlite makes sense. If you're dynamic linking

[sqlite] why I don't get an error ?

ok, thx everyone! especially R. Smith regards, Nicolas

[sqlite] why I don't get an error ?

On 8/27/2015 10:06 AM, Nicolas J?ger wrote: >so the only reason to define datatype in sqlite is for the size on >the disk ? I don't quite see how size on disk has anything to do with it. The main reason to define the column data type is to establish column affinity. >so why not

[sqlite] order by not working in combination with random()

A very instructive post, could you give your opinion about what should be the behavior for the "WHERE" clause ? I meam if we have a function on the field definition and reference it on the "WHERE" clause: CREATE TABLE myTable (a INTEGER); INSERT INTO myTable VALUES (1),(2),(3),(4),(5);

[sqlite] why I don't get an error ?

Hi Darko, Igor and others. so the only reason to define datatype in sqlite is for the size on the disk ? so why not just only using `BLOB` (excepted for `INTEGER PRIMARY KEY`) ? being less persmissive wouldn't make querries run faster ? for example, the comparisons would not have to

[sqlite] SQLite3 to 64-bit DLL

2015-08-26 19:03 GMT+02:00 : > On my Win10, I'd like to compile SQLite3 to create a 64-bit DLL. The > intention is to use this DLL instead of the amalgamation, this way more than > one C++ application can access the same DLL. Please note that I am an > aficionado, not an expert when it comes

[sqlite] explain plan change between SQLite 3.8.3.1 and 3.8.11.1

On 8/27/15, Clemens Ladisch wrote: > Dominique Devienne wrote: >> how can we programatically reliably discover which (v)tables a view >> accesses, staying in documented behavior land? > > With an authorizer callback: > http://www.sqlite.org/c3ref/set_authorizer.html > Clever! I was about to

[sqlite] explain plan change between SQLite 3.8.3.1 and 3.8.11.1

Recently upgraded, and we ran into failures because references to vtables (via opcode VOpen's p4 column) changed from vtab:module_ptr:vtable_ptr to just vtab:vtable_ptr. What was the intent behind this change? How can code one determine the module the vtable is coming from now? This is not a

[sqlite] order by not working in combination with random()

In the case: SELECT random() AS rr FROM sometable ORDER BY rr, the SQLite result is anti-intuitive. In my ignorance, I thought that ORDER BY sorted the results of the SELECT. It sure _looks_ that way from my view point. I cannot access the ANSI standard because I'm too cheap to buy them. So I went

[sqlite] why I don't get an error ?

SQLite records have fields that are variable sized and encode type and length information for each field and no table constraint changes this. The table constraints only change how some values are interpreted. On Thu, Aug 27, 2015 at 7:06 AM, Nicolas J?ger wrote: > Hi Darko, Igor and others. >

[sqlite] order by not working in combination with random()

I keep thinking I remember a thread from years ago where a lot of this was hashed out, but I cannot find it. I seem to remember one point which made sense was that while most functions with no parameters were reasonably considered static across the entire statement's execution, RANDOM() needed to

[sqlite] SQLite3 to 64-bit DLL

Hi, I do it all the time - why not? There are basically two approaches 1) download precompiled binary from SQLite website 2) compile it yourself. As for the second one we use CMake for everything. The following little CMake script does the job: cmake_minimum_required(VERSION 2.8)

[sqlite] explain plan change between SQLite 3.8.3.1 and 3.8.11.1

On 8/27/15, Dominique Devienne wrote: > > I understand that https://www.sqlite.org/opcode.html doesn't explicitly say > what VOpen's p4 column will contain, so I guess one could argue this is > undocumented behavior we should not rely on, On needn't argue this; it is explicitly stated in

[sqlite] why I don't get an error ?

On 8/26/2015 11:51 PM, Nicolas J?ger wrote: > my error is obvious, but why sqlite doesn't return an error ? http://sqlite.org/datatype3.html -- Igor Tandetnik

[sqlite] why I don't get an error ?

Hi, I have a table built by: CREATE TABLE IF NOT EXISTS TAGS (ID INTEGER PRIMARY KEY AUTOINCREMENT, NAME TEXT NOT NULL, COUNT INTEGER NOT NULL); where `COUNT` is an `INTEGER`. I wanted to increment `COUNT` with that command : UPDATE TAGS SET COUNT = 'COUNT + 1' WHERE ID = '666'; but when I