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

[Tim Streater]

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 care how SQLite implements it,
> but there is no excuse for generating such a ridiculous error message.

Humph. Just tried that:

  Third-Mini% sqlite3 wiggy
  -- Loading resources from /Users/tim/.sqliterc

  SQLite version 3.8.11.1 2015-07-29 20:00:57
  Enter ".help" for usage hints.
  sqlite> create table blah (i integer);
  sqlite> insert into blah values (1),(2),(3);
  sqlite> select * from blah;
  i 
  --
  1 
  2 
  3 
  sqlite> select random(), random() from blah order by random();
  random()  random()
    
  -1160299830889587722  -7414485036625888028
  -6378873629815772700  -1303234951729421433
  -8825674707761070860  -5468899718757218165
  sqlite> 

No error message.

--
Cheers  --  Tim

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

[Richard Hipp]

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

[Stephen Chrzanowski]

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 3:15am, Yuri  wrote:
>
> > * Database is written into memory
> > [...]
> > How can I understand why the slowdown occurs? Especially, why the
> slowdown in so "sharp"?
>
> 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 computer you would have
> faster operations for longer.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

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

[Jean-Christophe Deschamps]

>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 me pose a question. What should the following query generate?
>
>select random(), random() from blah order by random()

Ditto, but with now 3 function direct invokations, hence in the case of 
random(), 3 probably distinct values. That a more recent post shows 
that SQLite issues an error about ambiguousness of random() is even 
more worrisome!

This is completely different from refering to an *-already computed 
value-* like in
select random() rr where rr < 0 or rr >= 0 group by rr order by rr

Here: only 1 function invokation and multiple references to an already 
computed column.
What must occur here is (paraphrased):

create temp table tmp as select random() rr;
select * from tmp where rr < 0 or rr >= 0 group by rr order by rr; -- 
providing the result set
drop table tmp;

Now let's broaden the picture: if rr is in fact a large, complex 
subquery, taking 45 hours to compute, then should "order by rr" rerun 
the subquery for every row comparison? Sounds ridiculous to me.

I don't know SQLite code enough to start digging but I doubt that 
having, where, group by and order clauses to have any right and need to 
introspection into the column definition and private interpretation of 
what's there, lest re-running what produced them. This is none of their 
business.

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

[Yuri]

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" constraints, also added to be used by 
inserts. Without indexes/uniq constrainst it will slow down, and plans 
will show table scans.

Yuri

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

[Yuri]

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 computer you would have faster 
> operations for longer.


Good point.

My computer has 24GB ob RAM of which 4GB is free. At the time of the 
problem the size of the process is 325MB. And the size of complete DB is 
~250MB. So this isn't it.

What's worth mentioning though is that at the time of the event in 
question size of the process increases by 5MB.

Yuri

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

[Domingo Alvarez Duarte]

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, 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 result set  with the
>>> rows being sorted by the values of one or more columns. The sort criteria
>>> do not have to be included in the result set. The sort criteria can be
>>> expressions, including ? but not limited to ? column names,
>>>user-defined
>>> functions ,
>>> arithmetic
>>> operations, or CASE expressions. The expressions are evaluated and the
>>> results are used for the sorting, i.e. the values stored in the column or
>>> the results of the function call.
>>> 
>>> 
>>> 

>>  Pretty clear until now.
>> 
>> So, applying this to our subject, "rr" is _NOT_ the name a column in the
>>  
>>>table.
>>> 
>>> 

>>  Yes: it's a temporary schema name.
>> 
>> 

>  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()
> 
> So let me pose a question. What should the following query generate?
> 
> select random(), random() from blah order by random()
> 
> Should all three random values be the same? Certainly I can see a case for
> that, but it doesn't seem useful to me. What would be even worse is if it
> behaved like other functions which maintain their value over an entire
> select statement as DRH indicated recently. Do we really want a single
> random number used three times per row for thousands of rows? I doubt it.
> 
> Perhaps you think it should create two random values for the columns but
> reuse one of them for the order by. But which? Sure, if the columns were
> aliased with names one might expect it to pick the right one, but it seems
> that is not a requirement of at least one interpretation of the standard.
> 
> Ultimately it is a question of "what should happen with a function that has
> side effects"? SQLite has a flag for user defined functions named
> SQLITE_DETERMINISTIC. This flag allows the author of the function to inform
> SQLite that the function will always return a single result for a given set
> of input. I can only assume that the "native" functions of SQLite use
> something very similar to this interface (where "using that exact
> interface" is as similar as possible).
> 
> Clearly random() cannot be deterministic as it takes no user supplied input
> (even indirectly from a separate seed function) yet is expected to
> (probably) return different numbers from each invocation that cannot be
> predicted in advance.
> 
> It seems that if someone needs a list of random values that can
> subsequently be sorted (or selected or whatever) they need to resort to
> temp tables or subqueries. One step to generate the random values, another
> to process the now fixed set of random numbers.
> 
> Alternatively, we need a third class of functions. NON_DETERMINISTIC,
> DETERMINISTIC, and PSEUDO_DETERMINISTIC. I don't care for that solution.
> 
> Realizing this is not a democracy, my vote if cast would be to keep the
> existing functionality. It is not difficult to work around the
> non-deterministic nature of the random function via temp tables or
> subqueries. I dare say it is an extremely tiny subset of queries that will
> ever have to worry about this. Update the documentation in some way to make
> it clear random behaves this way (and perhaps that column aliases in an
> order by clause re-evaluate their definitions from the select). But my vote
> doesn't mean much, so whatever.
> 
> Maybe the best answer to this issue is "to change the functionality at this
> point may break existing queries that depend on the current behavior" and
> thus it should not be changed.
> 
> -- 
> Scott Robison
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> 
>
>  



?

[sqlite] autoincrement field

[Richard Hipp]

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
(https://www.sqlite.org/autoinc.html) since it probably does not do
what you think it does.


>
> Lev
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


-- 
D. Richard Hipp
drh at sqlite.org

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

[Yuri]

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 similar)

During Insertion process, selects are also performed to obtain keys to 
insert into other tables.

I followed every advise I could find:
* Database is written into memory
* Whole insertion happens in one transaction in one giant BEGIN 
TRANSACTON; ... END TRANSACTION; block.
* Foreign keys are deferred: PRAGMA defer_foreign_keys=ON;
* Journal is disabled: PRAGMA journal_mode = OFF;
* Synchronous mode is disabled: PRAGMA synchronous = OFF;

Plan for each "select" statement shows that it uses an index or primary 
key. Every insert statement is a simple insert "insert into xxx(x,x,x) 
values(?,?,?)" Selects are also all simple one-table selects. All 
statements used in prepared form.

How can I understand why the slowdown occurs? Especially, why the 
slowdown in so "sharp"?

Something drastic happens, like some strategy is recomputed, some index 
is rebuilt, etc...

Thank you,
Yuri

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

[Nicolas Jäger]

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 time on retrieval, just as you would want for speed.  If just
> means that if you try to supply the string 'NaN' or 'infinity' for
> the next row SQLite will still store it rather than generating an
> error message.
> 
> Simon.
thx, can be useful.

regards,
Nicolas

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

[Simon Slavin]

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 generating such a ridiculous error message.

I would say that the above command should use the same number for all the 
occurences of 'random()' in each 'sqlite3_step()'.  But I also wouldn't mind it 
if it came up with three different values for random() in each 
'sqlite3_step()'.  On the other hand

SELECT random() AS aa, random() FROM blah ORDER BY aa

Should not generate three different random values.  It should be either one or 
two.

Simon.

[sqlite] FTS5

[Richard Hipp]

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()

[Jean-Christophe Deschamps]

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 or more columns. The sort criteria
>do not have to be included in the result set. The sort criteria can be
>expressions, including ? but not limited to ? column names, user-defined
>functions , 
>arithmetic
>operations, or CASE expressions. The expressions are evaluated and the
>results are used for the sorting, i.e. the values stored in the column or
>the results of the function call.
>

Pretty clear until now.

>So, applying this to our subject, "rr" is _NOT_ the name a column in the
>table.

Yes: it's a temporary schema name.

>  It is an alias to a "user-defined function" (user-defined in this
>case meaning not defined in the SQL standard itself).

You're bending the meaning above. rr is not a C preprocessor define nor 
a C++ function reference, it is an alias to a column, i.e. a schema 
name, not an invokation of the computation that took place for creating 
the value in the column beforehand.

>  Therefore, based on
>the last sentence of the above quote, the random() is evaluated AT THE 
>TIME
>THE ORDER BY IS EXECUTED.

That would be a bug (like it currently is). Like Scott Hess just said 
shows that it isn't possible to perform a sort using this 
interpretation. Let's forget random() and use instead a user function 
inc() which initializes an integer sequence when fed an integral 
parameter and returns the next integer every time it's invoke without 
parameter.

Then "select inc() col order by col" wouldn't return the correct value. 
Granted that the order by clause isn't of much use here but if ever 
such construct is used to assign increasing reference numbers in step=1 
to items in a stock, then you're going to throw SQLite away.

Worst, say a user function new_uuid() returns new unique IDs and also 
stores them elsewhere (e.g. by communication with the underlying 
application) you'll end up with diverging lists of "used" uuids.

The fact that this example delivers a result clearly violating the 
where clause (and also the order by if you put "order by rr") 
demonstrates that this interpretation doesn't hold water.

drop table if exists mytable;
drop view if exists myview;
CREATE TABLE myTable (a INTEGER);
INSERT INTO myTable VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9);
CREATE VIEW myView AS SELECT a,random()%100 AS rr FROM myTable;
SELECT rr FROM myView WHERE rr < 30;

The wrong behavior contradicts SQLite own documentation, 
http://www.sqlite.org/lang_select.html and especially the second 
paragraph below

>Each ORDER BY expression is processed as follows:
>* If the ORDER BY expression is a constant integer K then the 
> expression is considered an alias for the K-th column of the result 
> set (columns are numbered from left to right starting with 1).
>* If the ORDER BY expression is an identifier that corresponds to 
> the alias of one of the output columns, then the expression is 
> considered an alias for that column.
>* Otherwise, if the ORDER BY expression is any other expression, 
> it is evaluated and the returned value used to order the output rows. 
> If the SELECT statement is a simple SELECT, then an ORDER BY may 
> contain any arbitrary expressions. However, if the SELECT is a 
> compound SELECT, then ORDER BY expressions that are not aliases to 
> output columns must be exactly the same as an expression used as an 
> output column.

--
jcd 

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

[R.Smith]

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 during the course of the
> sort, rather than regenerating them each time it looks the value up.

Firstly, I agree on the broad content, but just to point out, the last 
statement is not quite true, in that there is no need for SQLite or any 
other sorter to /MUST/ have a list of random values to sort with. The 
following statement is perfectly capable (as the comparator in a 
sort-engine) of sorting in a random order:

   if Random() > Random() then swap =  1 else
   if Random() < Random() then swap = -1 else
   swap = 0;

There is no prerequisite for a list of any kind.

(This is probably a close approximation of what happens inside SQLite 
when a column value contains simply a Random() function - but that is 
just a guess. Postgres however /MUST/ produce a list, we know this 
because any actual sort-order would be impossible on non pre-rendered 
Random function results).

[sqlite] autoincrement field

[Igor Tandetnik]

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

[Dominique Devienne]

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 the EXPLAIN prefix I
currently add).
Note also my current EXPLAIN query is actually "run" as well, i.e. stepped
through. --DD

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

[R.Smith]

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 querries run faster ?
>for example, the comparisons would not have to try to attempt to
>convert or even check and determine the type of the values.

Well yes, but because the data type is not strictly enforced does not 
mean SQLite is not optimizing storage or speed. SQLite checks and stores 
the actual type information with every value very efficiently. (It's 
referred to as "affinity" in SQLite).

As an example, storing the value 1 needs a very small footprint, the 
same even (if not smaller than) the blob value 0x01.

If the next row in that same column requires saving the word 
'Rubberduck' it will be saved with different type information. You can 
always retrieve exactly what you've added and be sure of its storage 
being efficient. Compare this to the old CHAR(n) types where you had to 
provision say 80 characters when you 99% of the time only used 5, ending 
up with a typical data density of less than 8%.

The point about comparisons is somewhat valid, usually as long as both 
compared values are of the same type it is not a problem, or if the 
column has a collation (or the index used, or the actual comparison). 
Sometimes though, you do end up having to convert, which eats up a few 
cycles, but still very little as the type information is still present.

Internally though, all values in SQLite's mind are really just blobs - 
so your point is served.

[sqlite] explain plan change between SQLite 3.8.3.1 and 3.8.11.1

[Dominique Devienne]

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:
> > http://www.sqlite.org/c3ref/set_authorizer.html
> >
>
> Clever!  I was about to write back that SQLite does not provide the
> capability that Dominique wants, but I think Clemens's answer is better!
>

Thanks Clemens and Richard. If VTable names are reported, this will work
for us!
I'll try it, to move away from our current reliance on undefined behavior.
--DD

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

[R.Smith]

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.  They could end up using a column called 'rr' without any 
> understanding of how it is generated.  And they would be disconcerted by a 
> result like the above.
>

I agree, and your example was quite clear but there were replies which 
seemed to have attached the meaning I described.

As for the specific idea that the average assumption leans toward one 
expectation... well yes, and I will agree that people might find a 
certain interpretation problematic or unintuitive or just outside of the 
assumption - but then we have all done that, haven't we? Usually a quick 
scan of the standard and/or the documentation sets us straight. There is 
no reason to cater for what people might expect to happen, there is only 
an obligation to adhere to the standard and, where that might be fuzzy, 
document the implementation specifics well.

(I am not advocating that SQLite (or indeed Postgres) does either of the 
above 100% correctly either... I am merely pointing out that unique 
expectations need not necessarily be served).

[sqlite] explain plan change between SQLite 3.8.3.1 and 3.8.11.1

[Simon Slavin]

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 ?

[Simon Slavin]

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 make querries run faster ?
>  for example, the comparisons would not have to try to attempt to
>  convert or even check and determine the type of the values.

SQLite does convert before the data is stored it if finds that it can do so 
without losing accuracy.

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 time on 
retrieval, just as you would want for speed.  If just means that if you try to 
supply the string 'NaN' or 'infinity' for the next row SQLite will still store 
it rather than generating an error message.

Simon.

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

[Gerald Bauer]

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 try:

  $ sqlite3 sport.db

> SQLite version 3.7.16
> Enter ".help" for instructions
> Enter SQL statements terminated with a ";"
>
> sqlite> .tables
>
> alltime_standing_entries  events_groundsnames
> alltime_standings events_teams  parts
> assocsgames persons
> assocs_assocs goals places
> badgesgrounds   props
> citiesgroup_standing_entriesrosters
> continentsgroup_standings   rounds
> counties  groupsseasons
> countries groups_teams  states
> country_codes langs taggings
> districts leagues   tags
> event_standing_entrieslogs  teams
> event_standings   metrosusages
> eventsmunis zones
>
> sqlite> select * from countries;
> 1|Mauritius|mauritius|mu|1|MUS|||1261200|2040|||f|t|f|f|
>
> sqlite> select * from teams;
> 1|joachim|Cercle de Joachim||CDJ|Cercle de Joachim SC|Joachim|1||t|f|
> 2|chamarel|Chamarel SC||CHA|Chamarel|Chamarel Sport Club|1||t|f|

  and so on and so forth.   Cheers.


[1] github.com/sportkit/mu-mauritius

[sqlite] explain plan change between SQLite 3.8.3.1 and 3.8.11.1

[Clemens Ladisch]

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

[Scott Doctor]

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()

[Simon Slavin]

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 myTable (a INTEGER);
> INSERT INTO myTable VALUES (1),(2),(3),(4),(5);
> CREATE VIEW myView AS SELECT a,random()%100 AS rr FROM myTable;
> SELECT rr FROM myView WHERE rr < 30 ORDER BY rr;

That's very clever.  You don't even need the 'ORDER BY' to show the problem, 
just a situation where a SELECT states specifically 'WHERE rr < 30' but gets 
values of rr greater than 30.  The WHERE clause is definitely part of the 
SELECT command and not a processing of later results.  It makes a better test 
for consistency than my original 'ORDER BY rr'.

I just ran a specific test on my (not quite up-to-date) version of SQLite:

SQLite version 3.8.10.2 2015-05-20 18:17:19
Enter ".help" for usage hints.
sqlite> CREATE TABLE myTable (a INTEGER);
sqlite> INSERT INTO myTable VALUES (1),(2),(3),(4),(5);
sqlite> CREATE VIEW myView AS SELECT a,random()%100 AS rr FROM myTable;
sqlite> SELECT rr FROM myView WHERE rr < 30;
21
83
8

I find it difficult to say that this result can be anything except wrong.

It's worth noting that this is not a problem just with the 'random()' function 
but also with any other function which does not lead to the same result every 
time.  For instance, a user-defined function.  It may be that a fix which 
checks specifically for the 'random()' function is the wrong thing to do.


On 27 Aug 2015, at 12:11pm, R.Smith  wrote:

> Further to this, among other posts there seem to be some confusion about the 
> purpose of aliasing - especially since people think in programming terms. An 
> alias isn't a variable or an array, it doesn't magically hold a state - it 
> simply is a nick-name for a function.

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.  
They could end up using a column called 'rr' without any understanding of how 
it is generated.  And they would be disconcerted by a result like the above.

Simon.

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

[Hick Gunter]

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. August 2015 16:07
An: sqlite-users at mailinglists.sqlite.org
Betreff: Re: [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 try to attempt to
  convert or even check and determine the type of the values.

regards,
Nicolas J.
___
sqlite-users mailing list
sqlite-users at mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___
 Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: hick at scigames.at

This communication (including any attachments) is intended for the use of the 
intended recipient(s) only and may contain information that is confidential, 
privileged or legally protected. Any unauthorized use or dissemination of this 
communication is strictly prohibited. If you have received this communication 
in error, please immediately notify the sender by return e-mail message and 
delete all copies of the original communication. Thank you for your cooperation.

[sqlite] explain plan change between SQLite 3.8.3.1 and 3.8.11.1

[Hick Gunter]

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 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 write back that SQLite does not provide the capability 
that Dominique wants, but I think Clemens's answer is better!
--
D. Richard Hipp
drh at sqlite.org
___
sqlite-users mailing list
sqlite-users at mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___
 Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: hick at scigames.at

This communication (including any attachments) is intended for the use of the 
intended recipient(s) only and may contain information that is confidential, 
privileged or legally protected. Any unauthorized use or dissemination of this 
communication is strictly prohibited. If you have received this communication 
in error, please immediately notify the sender by return e-mail message and 
delete all copies of the original communication. Thank you for your cooperation.

[sqlite] FTS4 - simulate phrase with NEAR/0

[Martin Kucej]

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

[sqlite] explain plan change between SQLite 3.8.3.1 and 3.8.11.1

[Dominique Devienne]

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 rely on, 
>
> On needn't argue this; it is explicitly stated in the EXPLAIN
> documentation (https://www.sqlite.org/lang_explain.html):
>
>"The output from EXPLAIN and EXPLAIN QUERY PLAN is intended for
> interactive analysis and troubleshooting only. The details of the
> output format are subject to change from one release of SQLite to the
> next. Applications should not use EXPLAIN or EXPLAIN QUERY PLAN since
> their exact behavior is variable and only partially documented."
>

OK. Right you are of course. Thank you for pointing this out for me.

I did that a long time ago, and probably didn't notice this disclaimer (I
assume it was already there back then).

That does not remove though the need for our application to do this kind of
introspection.

How then do you recommend we go about this?
I.e. how can we programatically reliably discover which (v)tables a view
accesses, staying in documented behavior land?

Thanks, --DD

PS: FWIW, I'm still curious why change took place. I could do a "blame" if
I was Fossil-savvy, but even then I'm not sure I'd get it from the diff.

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

[Scott Robison]

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 function invokations, so I don't see
> how SQL engine would decide not to perform the second call.


Agreed, though I'm pretty sure I've read messages in this thread at
advocate the same function should return the same value when called
multiple times in a single select statement.


> So let me pose a question. What should the following query generate?
>>
>> select random(), random() from blah order by random()
>>
>
> Ditto, but with now 3 function direct invokations, hence in the case of
> random(), 3 probably distinct values. That a more recent post shows that
> SQLite issues an error about ambiguousness of random() is even more
> worrisome!
>

Well, 3.8.11 (at least) does not issue such an error. I'm thinking it was a
"this is what should be done IMO" comment vs a "this is what actually
happens".


> This is completely different from refering to an *-already computed
> value-* like in
> select random() rr where rr < 0 or rr >= 0 group by rr order by rr
>
> Here: only 1 function invokation and multiple references to an already
> computed column.
> What must occur here is (paraphrased):
>
> create temp table tmp as select random() rr;
> select * from tmp where rr < 0 or rr >= 0 group by rr order by rr; --
> providing the result set
> drop table tmp;
>
> Now let's broaden the picture: if rr is in fact a large, complex subquery,
> taking 45 hours to compute, then should "order by rr" rerun the subquery
> for every row comparison? Sounds ridiculous to me.
>
> I don't know SQLite code enough to start digging but I doubt that having,
> where, group by and order clauses to have any right and need to
> introspection into the column definition and private interpretation of
> what's there, lest re-running what produced them. This is none of their
> business.


Using sqlfiddle.com I'm testing the following:

create table blah(a int);
insert into blah values(1),(2),(3);
select rand(), rand() as b from blah order by b;

where rand() is whatever random number function the platform in question
provides.

SQL Server 2008: same row three times; rand() is computed at the beginning
of the query and cached. Which makes for decidedly non-random numbers and
doesn't seem very useful IMO.

MySQL 5.6 returns three different rows, and it seems to be sorted on b. Six
invocations of the query always resulted in the set being sorted on b, so
I'm going to assume it worked as expected without generating a third random
number.

PostgreSQL 9.3: same result as MySQL 5.6.

Oracle 11g R2: same result as MySQL 5.6.

SQLite: we already know.

I think it still comes back to my earlier comment: Would changing it to
behave more like the most common / expected outcome above be a breaking
change?

-- 
Scott Robison

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

[R.Smith]

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 correctly
>>> (without re-evaluating) and without needing an alias.
>> Ah, but I would call /that/ a bug !
> I think I can convince you Postgres is right and SQLite is not.  :-)
> I'll give you 4 reasons:
>
> 1.  Theory
> 2.  Authority
> 3.  Atomicity
> 4.  Consistency

Thanks for the informative post, and I do agree with it except for 
specifically the Random() function.

Random() is not a function you use when you expect reproducible results. 
The very nature of the word suggests the opposite.

Further to this, among other posts there seem to be some confusion about 
the purpose of aliasing - especially since people think in programming 
terms. An alias isn't a variable or an array, it doesn't magically hold 
a state - it simply is a nick-name for a function.

This statement:
   SELECT Random() AS rr ORDER BY rr

cannot ever be functionally different to:
   SELECT Random() ORDER BY Random()

To use programming terms: rr is not a variable - it does not "hold" a 
value for the Random() function, it simply is a alphanumeric "pointer" 
to the the Random() function (in this case).

I wouldn't go so far as to call the behavior in Postgres a "bug" but 
just an implementation detail - I mean the values do end up being 
random, do they not? They just chose to make the sorting possible by 
keeping the state information - a good thing since it produces what most 
people (especially programmers) seem to expect, but in no way (I think) 
an absolute adherence to a standard.

By James' standard, the Query:
SELECT Random() FROM t;  -- imagine table t hold 10 tuples

Should provide:
Random()
--
76398763987097830
76398763987097830
76398763987097830
76398763987097830
76398763987097830
76398763987097830
76398763987097830
76398763987097830
76398763987097830
76398763987097830

And I happen to agree if we do apply strict relational theory. The thing 
is though, when people invoke the Random() function, they really do not 
wish to see the above (as opposed to when the reference the Now() or 
similar functions, the above would be exactly what they wish to see). So 
SQLite, like PostGres, offer an alternate implementation detail in this 
regard.

The debate on whether either is absolutely "right" or "wrong" is just a 
silly one.  Postgres has the luxury of not being "Lite" nor needing to 
run on a phone, so the added code and memory to deal with saving a state 
of randomness for an entire result-set is not really problematic in 
Postgres, and not really in the design goals of SQLite.


FWIW,
Ryan

[sqlite] FTS4 - simulate phrase with NEAR/0

[Martin Kucej]

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 capabilities. I can only work with FTS4, which
does not allow a phrase search with column specifiers, e. g. this is
invalid:

MATCH 'column1:"lorem ipsum" column2:"gaudeamus igitur"'

My question is - can I simulate phrases with the NEAR keyword? Like so:

MATCH 'column1:lorem NEAR/0 column1:ipsum column2:gaudeamus NEAR/0
column2:igitur'

The way NEAR keyword is explained, it should work, and all my tests
give me correct results. I just want to make sure it is always going
to be correct. Thanks.

Best,
Martin

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

[Scott Robison]

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 reported, or are you
saying that is the error message that *is* reported? Because I just tried
the query with sqlite3.exe 3.8.11 and it worked just fine.


>
>
> >  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, 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 result set  with
> the
> >>> rows being sorted by the values of one or more columns. The sort
> criteria
> >>> do not have to be included in the result set. The sort criteria can be
> >>> expressions, including ? but not limited to ? column names,
> >>>user-defined
> >>> functions ,
> >>> arithmetic
> >>> operations, or CASE expressions. The expressions are evaluated and the
> >>> results are used for the sorting, i.e. the values stored in the column
> or
> >>> the results of the function call.
> >>> 
> >>>
> >>>
>
> >>  Pretty clear until now.
> >>
> >> So, applying this to our subject, "rr" is _NOT_ the name a column in the
> >>
> >>>table.
> >>>
> >>>
>
> >>  Yes: it's a temporary schema name.
> >>
> >>
>
> >  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()
> >
> > So let me pose a question. What should the following query generate?
> >
> > select random(), random() from blah order by random()
> >
> > Should all three random values be the same? Certainly I can see a case
> for
> > that, but it doesn't seem useful to me. What would be even worse is if it
> > behaved like other functions which maintain their value over an entire
> > select statement as DRH indicated recently. Do we really want a single
> > random number used three times per row for thousands of rows? I doubt it.
> >
> > Perhaps you think it should create two random values for the columns but
> > reuse one of them for the order by. But which? Sure, if the columns were
> > aliased with names one might expect it to pick the right one, but it
> seems
> > that is not a requirement of at least one interpretation of the standard.
> >
> > Ultimately it is a question of "what should happen with a function that
> has
> > side effects"? SQLite has a flag for user defined functions named
> > SQLITE_DETERMINISTIC. This flag allows the author of the function to
> inform
> > SQLite that the function will always return a single result for a given
> set
> > of input. I can only assume that the "native" functions of SQLite use
> > something very similar to this interface (where "using that exact
> > interface" is as similar as possible).
> >
> > Clearly random() cannot be deterministic as it takes no user supplied
> input
> > (even indirectly from a separate seed function) yet is expected to
> > (probably) return different numbers from each invocation that cannot be
> > predicted in advance.
> >
> > It seems that if someone needs a list of random values that can
> > subsequently be sorted (or selected or whatever) they need to resort to
> > temp tables or subqueries. One step to generate the random values,
> another
> > to process the now fixed set of random numbers.
> >
> > Alternatively, we need a third class of functions. NON_DETERMINISTIC,
> > DETERMINISTIC, and PSEUDO_DETERMINISTIC. I don't care for that solution.
> >
> > Realizing this is not a democracy, my vote if cast would be to keep the
> > existing functionality. It is not difficult to work around the
> > non-deterministic nature of the random function via temp tables or
> > subqueries. I dare say it is an extremely tiny subset of queries that
> will
> > ever have to worry about this. Update the documentation in some way to
> make
> > it clear random behaves this way (and perhaps that column aliases in an
> > order by clause re-evaluate their definitions from the select). But my
> vote
> > doesn't mean much, so whatever.
> >
> > Maybe the best answer to this issue is "to change the functionality at
> this
> > point may break existing queries that depend on the current behavior" and
> > thus it should not be changed.
> >
> > --
> > Scott Robison
> > ___
> > sqlite-users mailing list
> > sqlite-users at mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
> >
> >
>
>
>
>
> 

[sqlite] FTS4 - simulate phrase with NEAR/0

[Scott Hess]

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 NEAR/0 column:word3'
>
> matcher both "word1 word2 word3" and "word3 word2 word1" phrases. So,
> it is a no go.


Due to grammar rules, for most languages the query's given order will
probably dominate the results, so performance-wise it might be fine to
post-process using a sub-query or in the client code.  It will only really
lose if the reverse ordering is common relative to the intended ordering.

-scott

[sqlite] FTS4 - simulate phrase with NEAR/0

[Scott Hess]

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, which
> does not allow a phrase search with column specifiers, e. g. this is
> invalid:
>
> MATCH 'column1:"lorem ipsum" column2:"gaudeamus igitur"'
>
> My question is - can I simulate phrases with the NEAR keyword? Like so:
>
> MATCH 'column1:lorem NEAR/0 column1:ipsum column2:gaudeamus NEAR/0
> column2:igitur'
>
> The way NEAR keyword is explained, it should work, and all my tests
> give me correct results. I just want to make sure it is always going
> to be correct.
>

NEAR/0 will probably not care about ordering.

It might be easier to just put the columns in separate fts4 tables and join
on rowid.  Or phrase-match the columns separately in sub-queries against
the same table (one sub-query does column1 MATCH "lorem ipsum" the other
does the other match, and you join them together).

I think performance-wise, joining on rowid shouldn't be a problem unless
you have a very large number of hits for both queries.

-scott

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

[Scott Robison]

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 result set  with the
>> rows being sorted by the values of one or more columns. The sort criteria
>> do not have to be included in the result set. The sort criteria can be
>> expressions, including ? but not limited to ? column names, user-defined
>> functions ,
>> arithmetic
>> operations, or CASE expressions. The expressions are evaluated and the
>> results are used for the sorting, i.e. the values stored in the column or
>> the results of the function call.
>> 
>>
>
> Pretty clear until now.
>
> So, applying this to our subject, "rr" is _NOT_ the name a column in the
>> table.
>>
>
> Yes: it's a temporary schema name.
>

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()

So let me pose a question. What should the following query generate?

select random(), random() from blah order by random()

Should all three random values be the same? Certainly I can see a case for
that, but it doesn't seem useful to me. What would be even worse is if it
behaved like other functions which maintain their value over an entire
select statement as DRH indicated recently. Do we really want a single
random number used three times per row for thousands of rows? I doubt it.

Perhaps you think it should create two random values for the columns but
reuse one of them for the order by. But which? Sure, if the columns were
aliased with names one might expect it to pick the right one, but it seems
that is not a requirement of at least one interpretation of the standard.

Ultimately it is a question of "what should happen with a function that has
side effects"? SQLite has a flag for user defined functions named
SQLITE_DETERMINISTIC. This flag allows the author of the function to inform
SQLite that the function will always return a single result for a given set
of input. I can only assume that the "native" functions of SQLite use
something very similar to this interface (where "using that exact
interface" is as similar as possible).

Clearly random() cannot be deterministic as it takes no user supplied input
(even indirectly from a separate seed function) yet is expected to
(probably) return different numbers from each invocation that cannot be
predicted in advance.

It seems that if someone needs a list of random values that can
subsequently be sorted (or selected or whatever) they need to resort to
temp tables or subqueries. One step to generate the random values, another
to process the now fixed set of random numbers.

Alternatively, we need a third class of functions. NON_DETERMINISTIC,
DETERMINISTIC, and PSEUDO_DETERMINISTIC. I don't care for that solution.

Realizing this is not a democracy, my vote if cast would be to keep the
existing functionality. It is not difficult to work around the
non-deterministic nature of the random function via temp tables or
subqueries. I dare say it is an extremely tiny subset of queries that will
ever have to worry about this. Update the documentation in some way to make
it clear random behaves this way (and perhaps that column aliases in an
order by clause re-evaluate their definitions from the select). But my vote
doesn't mean much, so whatever.

Maybe the best answer to this issue is "to change the functionality at this
point may break existing queries that depend on the current behavior" and
thus it should not be changed.

-- 
Scott Robison

[sqlite] SQLite3 to 64-bit DLL

[Teg]

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 the run time library, you might as well dynamic link Sqlite.

I find that generally, statically linked applications are more reliable
in  Windows.  The  problem  isn't windows so much as the poor state of
most of my user's PC's.

C


Wednesday, August 26, 2015, 1:03:18 PM, you wrote:

pac> On my Win10, I'd like to compile  SQLite3 to create a 64-bit
pac> DLL. The intention is to use this DLL instead of the
pac> amalgamation, this way more than one C++ application can access
pac> the same DLL. Please note that I am an aficionado, not an expert
pac> when it comes to C++ and for SQL, well, this would be my second
pac> attempt to get an application to use a database, as opposed to flat files.

pac> How would you suggest I should proceed? is this even a good idea?
pac> ___
pac> sqlite-users mailing list
pac> sqlite-users at mailinglists.sqlite.org
pac> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



-- 
 Tegmailto:Teg at djii.com

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

[Nicolas Jäger]

ok, thx everyone!

especially R. Smith

regards,
Nicolas

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

[Igor Tandetnik]

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 just only using `BLOB` (excepted for `INTEGER PRIMARY
>KEY`) ?

Feel free to. I'm not sure I understand the question.

>being less persmissive wouldn't make querries run faster ?

I don't know. I don't have a clone of SQLite that's just like existing 
SQLite only with rigid type system, with which to compare.
-- 
Igor Tandetnik

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

[Domingo Alvarez Duarte]

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);  

CREATE VIEW myView AS SELECT a,random()%100 AS rr FROM myTable;  

SELECT rr FROM myView WHERE rr < 30 ORDER BY rr;  

?  

Cheers !  
>  Thu Aug 27 2015 3:03:21 am CEST CEST from "James K. Lowden"
>  Subject: Re: [sqlite] order by not working in
>combination with random()
>
>  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 correctly
>>> (without re-evaluating) and without needing an alias.
>>> 

>>  Ah, but I would call /that/ a bug !
>> 

>  I think I can convince you Postgres is right and SQLite is not. :-)
> I'll give you 4 reasons:
> 
> 1. Theory
> 2. Authority
> 3. Atomicity
> 4. Consistency
> 
> Theory. Like the relation of relational algebra, a SQL table has no
> order. (Not only tables, but any table-like thing: view, CTE,
> subquery.) If you take a table and permute its order, it's still the
> same table. ORDER BY permutes the order in a particular way. It does
> not change the table. Relationally, the input and output of ORDER BY
> must be identical. 
> 
> Authority. As a matter of fact, CJ Date says the output of ORDER BY is
> *not* a table, but a cursor. That makes sense, and also explains why
> ORDER BY can, according to the SQL standard, appear only once in a
> query, and only at the end. You could think of ORDER BY not as a sort
> operator but as a row-returning operator that magically returns the
> "next" row per a specification. 
> 
> The SQL-92 standard distinqushes between the SELECT statement with
> all its many clauses, and ORDER BY:
> 
> """
> Format
> 
>  ::=
>  [  ]
> """
> 
> SQLite would do well to adopt this notion in its query processor. It
> would simplify the system, and would have avoided this error. 
> 
> Atomicity. The rule in SQL is that all functions act *as if* they were
> invoked only once. We had this confusion some time back with 
> "date(now)". There's only one "now" in a query, and there's only one
> random() in a row-column. 
> 
> The column-specification of SELECT is applied *once* to each row. The
> column-specification of ORDER BY (and GROUP BY) is *not* applied; it
> merely *designates* the column by using the same specification text.
> Consider this example:
> 
> sqlite> select 2+2 order by '2+2';
> 2+2 
> --
> 4 
> 
> '2+2' here is only the column name. It's not an instruction to create
> data. 
> 
> Consistency. Any two semantically equivalent queries that produce
> different outputs from the same input constitute prima facia evidence
> of a bug. However you restate a query, if it is logically equivalent,
> it should produce the same results. The fact that you can rejigger
> queries with RANDOM() and get different answers tells you immediately
> that something is wrong. 
> 
> It's sometimes useful to think about how a DBMS works, and imagine
> traversing trees, sorting, etc. It helps in understanding how to
> express a query, and in how it will be processed. But you have to
> throw all that away when thinking about query semantics. The user has
> *one* chance only to state the query; he has no control over how its
> executed. As he is constrained, so is the system: it cannot let
> implementation details affect query semantics. That's the way it is
> and should be, because it best serves the interests of both parties. 
> 
> --jkl
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> 
>
>  



?

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

[Nicolas Jäger]

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 try to attempt to
  convert or even check and determine the type of the values.

regards,
Nicolas J.

[sqlite] SQLite3 to 64-bit DLL

[Jan Nijtmans]

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 to C++ and for SQL, well, this would 
> be my second attempt to get an application to use a database, as opposed to 
> flat files.
>
> How would you suggest I should proceed? is this even a good idea?

If you like, you can download my build of the 64-bit dll, and
the corresponding import files:
 
The sqlite-dll-win64-x64-3081101.zip file contains two
import libraries you can use for your project:
   sqlite3.lib  : Suitable for VC++ 6.0 or higher (up to the latest
Visual Studio 2014)
   libsqlite3.dll.a : Suitable for any gcc version (mingw/mingw-64/msys/msys-2)
The dll is suitable for any 64-bit windows environment.

Regards,
   Jan Nijtmans

[sqlite] explain plan change between SQLite 3.8.3.1 and 3.8.11.1

[Richard Hipp]

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 write back that SQLite does not provide the
capability that Dominique wants, but I think Clemens's answer is
better!
-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] explain plan change between SQLite 3.8.3.1 and 3.8.11.1

[Dominique Devienne]

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 showstopper for us, because we have a single module and we
were just asserting it matched our "singleton" module, but I definitely
missed that in the release notes :)

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, but the alternatives of query
parsing, w/o an SQLite-provided AST of its queries would be even worse IMHO.

Just trying to get clarification on this. Thanks, --DD

/*!
 * \brief Introspects the pcode (compiled query) of a view.
 *
 * Uses an EXPLAIN SQL statement to list all SQLite VDBE instructions a
simple
 * query accessing the view compiles into, and looks for \em TableLock and
 * \em VOpen opcodes, gathering their p4 arguments. In the case of VOpen,
uses
 * a virtual table specific introspection function.
 *
 * \param view_name a SQL view name.
 * \return the list of table or virtual tables accessed by that view.
 *
 * \sa VirtualSchema::find_by_pcode()
 *
 * \note this type of introspection should be much more reliable than trying
 * to parse the SQL definition of the view, given SQL's complex grammar.
 */

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

[John McKown]

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 to Wikipedia: https://en.wikipedia.org/wiki/Order_by (not
authoritative, I know) and got the following:

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 or more columns. The sort criteria
do not have to be included in the result set. The sort criteria can be
expressions, including ? but not limited to ? column names, user-defined
functions , arithmetic
operations, or CASE expressions. The expressions are evaluated and the
results are used for the sorting, i.e. the values stored in the column or
the results of the function call.

So, applying this to our subject, "rr" is _NOT_ the name a column in the
table. It is an alias to a "user-defined function" (user-defined in this
case meaning not defined in the SQL standard itself). Therefore, based on
the last sentence of the above quote, the random() is evaluated AT THE TIME
THE ORDER BY IS EXECUTED. Therefore, although not what we might want and
expect, SQLite is conformant. I think that this logic also applies in the
case of a VIEW or CTE. Which basically means that, to get what is wanted,
we need to first put our data into another, perhaps TEMPORARY, table. And
then do the SELECT ... ORDER BY using that table with column names, not
functions.

This is only my thought on the situation. I don't claim that it is correct.
But it does, sort of [pun intended], explain the ORDER BY results which
were observed.

-- 

Schrodinger's backup: The condition of any backup is unknown until a
restore is attempted.

Yoda of Borg, we are. Futile, resistance is, yes. Assimilated, you will be.

He's about as useful as a wax frying pan.

10 to the 12th power microphones = 1 Megaphone

Maranatha! <><
John McKown

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

[Darko Volaric]

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.
>
>   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 try to attempt to
>   convert or even check and determine the type of the values.
>
> regards,
> Nicolas J.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

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

[Scott Hess]

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 be a special case to be useful
for much.  For instance:
   SELECT RANDOM();
would be an obvious way to select a random value, with no ambiguity, while
in either of these two cases:
   SELECT *, RANDOM() FROM t;
   SELECT * FROM t ORDER BY RANDOM() LIMIT 1;
the statements make no sense if RANDOM() is lifted as constant across the
entire execution.  But only in the second case is SQLite really in a
privileged position - in the first case, the client code could trivially
augment the result set with random values, while in the second case SQLite
can internally optimize to return a random subset efficiently.

Now, something like:
   SELECT RANDOM(), * FROM t ORDER BY 1 LIMIT 1;
is ambiguous.  On the one hand, the author's intent seems obvious, to pick
a random row and keep the value used to pick the row.  On the other hand,
you have to stretch a bit to construct a logical reason why the client code
cares what random values were being used by SQLite for this.  Likewise for
using RANDOM() in the WHERE clause - what possible difference could it make
to the client?

Where I think that ends up is a principle-of-least-surprise position.  If a
statement uses RANDOM() in multiple places, each of those should be
independent each time within a row, and also each time across rows.  But
RANDOM() AS r should allow r to be logically consistent when used in a
WHERE clause or ORDER BY.

There is already some precedent for this, because ORDER BY RANDOM() must
internally be holding the random values used fixed during the course of the
sort, rather than regenerating them each time it looks the value up.

-scott


On Thu, Aug 27, 2015 at 4:11 AM, R.Smith  wrote:

>
>
> 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 correctly
 (without re-evaluating) and without needing an alias.

>>> Ah, but I would call /that/ a bug !
>>>
>> I think I can convince you Postgres is right and SQLite is not.  :-)
>> I'll give you 4 reasons:
>>
>> 1.  Theory
>> 2.  Authority
>> 3.  Atomicity
>> 4.  Consistency
>>
>
> Thanks for the informative post, and I do agree with it except for
> specifically the Random() function.
>
> Random() is not a function you use when you expect reproducible results.
> The very nature of the word suggests the opposite.
>
> Further to this, among other posts there seem to be some confusion about
> the purpose of aliasing - especially since people think in programming
> terms. An alias isn't a variable or an array, it doesn't magically hold a
> state - it simply is a nick-name for a function.
>
> This statement:
>   SELECT Random() AS rr ORDER BY rr
>
> cannot ever be functionally different to:
>   SELECT Random() ORDER BY Random()
>
> To use programming terms: rr is not a variable - it does not "hold" a
> value for the Random() function, it simply is a alphanumeric "pointer" to
> the the Random() function (in this case).
>
> I wouldn't go so far as to call the behavior in Postgres a "bug" but just
> an implementation detail - I mean the values do end up being random, do
> they not? They just chose to make the sorting possible by keeping the state
> information - a good thing since it produces what most people (especially
> programmers) seem to expect, but in no way (I think) an absolute adherence
> to a standard.
>
> By James' standard, the Query:
> SELECT Random() FROM t;  -- imagine table t hold 10 tuples
>
> Should provide:
> Random()
> --
> 76398763987097830
> 76398763987097830
> 76398763987097830
> 76398763987097830
> 76398763987097830
> 76398763987097830
> 76398763987097830
> 76398763987097830
> 76398763987097830
> 76398763987097830
>
> And I happen to agree if we do apply strict relational theory. The thing
> is though, when people invoke the Random() function, they really do not
> wish to see the above (as opposed to when the reference the Now() or
> similar functions, the above would be exactly what they wish to see). So
> SQLite, like PostGres, offer an alternate implementation detail in this
> regard.
>
> The debate on whether either is absolutely "right" or "wrong" is just a
> silly one.  Postgres has the luxury of not being "Lite" nor needing to run
> on a phone, so the added code and memory to deal with saving a state of
> randomness for an entire result-set is not really problematic in Postgres,
> and not really in the design goals of SQLite.
>
>
> FWIW,
> Ryan
>
>
> ___
> sqlite-users mailing list
> 

[sqlite] SQLite3 to 64-bit DLL

[Jakub Zakrzewski]

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)
project(sqlite C)

set(CMAKE_SKIP_BUILD_RPATH ON)
set(CMAKE_RUNTIME_OUTPUT_DIRECTORY ${CMAKE_BINARY_DIR}/bin)
set(CMAKE_LIBRARY_OUTPUT_DIRECTORY ${CMAKE_RUNTIME_OUTPUT_DIRECTORY})
set(CMAKE_INCLUDE_CURRENT_DIRECTORY ON)

if(CMAKE_SYSTEM MATCHES Windows)
# warning C4251: class x needs to have dll-interface to be used by 
clients of class y
add_definitions(-DWIN32_LEAN_AND_MEAN -Zc:wchar_t -wd4251 -Zi)
endif(CMAKE_SYSTEM MATCHES Windows)

set(Sources 
sqlite3.c
)

add_library(sqlite3 SHARED ${Sources})
set_target_properties(sqlite3 PROPERTIES DEFINE_SYMBOL 
"SQLITE_API=__declspec(dllexport)")


Note that you can manipulate the "add_definitions" thing. We just happen to use 
the same set everywhere.

--
Gruesse,
Jakub


-Original Message-
From: sqlite-users-bounces at mailinglists.sqlite.org 
[mailto:sqlite-users-bounces at mailinglists.sqlite.org] On Behalf Of 
p...@arbolone.ca
Sent: Mittwoch, 26. August 2015 19:03
To: MailingList SQLite3
Subject: [sqlite] SQLite3 to 64-bit DLL

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 to C++ and for SQL, well, this would be my second 
attempt to get an application to use a database, as opposed to flat files.

How would you suggest I should proceed? is this even a good idea?
___
sqlite-users mailing list
sqlite-users at mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] explain plan change between SQLite 3.8.3.1 and 3.8.11.1

[Richard Hipp]

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 the EXPLAIN
documentation (https://www.sqlite.org/lang_explain.html):

   "The output from EXPLAIN and EXPLAIN QUERY PLAN is intended for
interactive analysis and troubleshooting only. The details of the
output format are subject to change from one release of SQLite to the
next. Applications should not use EXPLAIN or EXPLAIN QUERY PLAN since
their exact behavior is variable and only partially documented."

-- 
D. Richard Hipp
drh at sqlite.org

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

[Igor Tandetnik]

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 ?

[Nicolas Jäger]

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 looked where `ID` = 666, in the `COUNT` cell I read :

COUNT + 1

my error is obvious, but why sqlite doesn't return an error ? as I
specified I want an integer, I should not be granted to set the value
by a string. Or there is some reason for sqlite being permissive ?


regards,

Nicolas J.