Re: [sqlite] An order by problem, maybe a bug?

[Simon Slavin]

On 20 Sep 2014, at 11:09pm, Mark Halegua  wrote:

> I'm going to have a separate table for pennames.  Lester del Rey will be last 
> name del Rey, 
> first name Lester.
> 
> My problem will be the following:
> 
> Therer are a number of writers who used several non de plummes, however there 
> are some 
> credited authors who are house names (Maxwell Grant is one, with no fewer 
> than three 
> writers writing the Shadow as Grant - Walter B. Gibson 285 of the 325, Ted 
> Tinsley, and 
> others) and it is this I'm having trouble normalizing.  Thinking for those 
> house names I'm 
> going to have to refer to the story or issue to fully credit the corect 
> writer,  Any thoughts 
> here are welcome.

I suspect that you may need a many-to-many relationship.  You have already 
listed a case where one author used many pen-names, and a case where different 
people wrote with the same pen-name.  To make it worse, some works are written 
by someone writing under their own name.  Inconceivable.

If you need to capture the whole truth in your data columns, taking into 
account all combinations which happened, no matter how rare, then each work 
published needs two fields: real author and pen-name.  Each of which, of 
course, should be foreign keys into two other files.  Often the texts shown in 
those two fields will match each-other.  But one might be an informal version 
of the other, for example "Francis H. Burns" in the person file but "Frank 
Burns" in the pen-name file because that's the way the name is usually printed.

Another way to capture the whole truth would be to establish the many-to-many 
relationship between people and pen-names, and have each work point at a record 
in the middle file of that relationship.

If you only need to capture 99% of the truth, and can insert a note for a few 
works explaining anything unusual, then it may be possible to store only the 
pen-name for each work.  In your pen-name file you can list what would normally 
be just the name of the one person who used that pen-name, however you might 
want to establish a format where there could be multiple names separated by 
commas or semicolons or something.

I hate making choices like these.  Good luck picking one which turns out to be 
the one your users will find most convenient.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] An order by problem, maybe a bug?

[Mark Halegua]

On Friday, September 19, 2014 08:07:06 PM Simon Slavin wrote:
> No, no he's just working on US Pulp Magazines.  All pulp writers have
> traditional names.  He's not going to have any trouble.
> 
> Except, of course, with Daniel Keys Moran.  Who doesn't use his first name
> except when writing.  And Marion Zimmer Bradley, whose name should be
> sorted under 'Z'.  Though, of course, Edgar Rice Burroughs should be sorted
> under 'B'.  And Margaret St. Clair whose name is correctly pronounced and
> sorted as if it was Margaret Sinclair.
> 
> And Lester del Rey.  Whose name should be sorted under 'd' (his wife was
> Judy-Lynn del Rey).  And who normally claimed (falsely) that his name was
> Ramon Felipe San Juan Mario Silvio Enrico Smith Heartcourt-Brace Sierra y
> Alvarez del Rey y de los Uerdes.

I'm going to have a separate table for pennames.  Lester del Rey will be last 
name del Rey, 
first name Lester.

My problem will be the following:

Therer are a number of writers who used several non de plummes, however there 
are some 
credited authors who are house names (Maxwell Grant is one, with no fewer than 
three 
writers writing the Shadow as Grant - Walter B. Gibson 285 of the 325, Ted 
Tinsley, and 
others) and it is this I'm having trouble normalizing.  Thinking for those 
house names I'm 
going to have to refer to the story or issue to fully credit the corect writer, 
 Any thoughts 
here are welcome.

> 
> Of course, the author Eando Binder was actually two people (Edward and Oscar
> Binder) but that doesn't count.
Actually, it does, but it will be taken care of in the pennames table.  And, 
for a period, one of 
the Binder brothers left and the remaining Binder continued to write as Eando 
Binder.

Mark
 
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] An order by problem, maybe a bug?

[Simon Slavin]

On 20 Sep 2014, at 9:42pm, Petite Abeille  wrote:

> Your last name contains invalid characters
> http://blog.jgc.org/2010/06/your-last-name-contains-invalid.html

Ah yes, John Graham-Cumming.  One of those sneaky non-standard-format foreign 
names.  Probably a terrorist.

On 20 Sep 2014, at 9:27pm, John Hascall  wrote:

> The problem with your suggestion of 'two uses => two fields' is that no 
> sooner do you do that then somebody comes up with additional uses, for 
> example, formal greeting, informal greeting, the appropriate form for 
> government form X123, and so on

Banks I've worked with include a field called "Salutation" which says things 
like "Dear Ms. White" or "Dear Mrs. White" depending on the account-holder's 
preference.

Financial and legal institutions, by the way, are the toughest at getting this 
right.  They have a legal requirement to know the account-holder's 
commonly-used form of name.  Not some twisted abbreviated version of it.  If 
the account-holder calls themself Tarquin 
Fin-tim-lin-bin-whin-bim-lim-bus-stop-F'tang-F'tang-Olé-Biscuitbarrel that's 
what they have to have in their database.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Performance regression between 3.7.17 and 3.8.0.2?

[Richard Hipp]

On Sat, Sep 20, 2014 at 12:45 PM, Merike  wrote:

> 19.09.2014 04:21, Richard Hipp kirjutas:
> > A simple script to reproduce the problem in the latest SQLite is as
> > follows: CREATE TABLE t1(a INTEGER PRIMARY KEY, b INTEGER, c INTEGER,
> > d INTEGER); CREATE INDEX t1b ON t1(b); CREATE TABLE t2(x INTEGER
> > PRIMARY KEY, y); explain query plan SELECT * FROM t1, t2 WHERE x=c AND
> > b=?1 ORDER BY a; .print -- CREATE INDEX t1bd ON
> > t1(b,d); explain query plan SELECT * FROM t1, t2 WHERE x=c AND b=?1
> > ORDER BY a; If you copy/paste the above script into an sqlite3
> > command-line shell, you'll see that, for some reason, the query
> > planner decides to sort manually rather than using an index for
> > sorting after the t1bd index is added. We are still investigating to
> > try understand why that is. Please note that if you run ANALYZE on
> > your sample database, the query is fast again. On my (Ubuntu) desktop,
> > the query takes 648 milliseconds before being analyzed and 600
> > microseconds afterwards - a 1000-fold speedup.
>
> A question: is the query being fast again after analyze call indicative
> of the bug being fixed? Because I tried it on my original database too
> and there I don't see a speedup after analyze. Should I try to minimize
> it to a smaller database again where the bug still occurs, even after
> analyze? Or will the change you made fix my original database speed as
> well despite the analyze call not helping it?
>

The change fixes the problem (for us) *without* requiring ANALYZE.

-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Performance regression between 3.7.17 and 3.8.0.2?

[Merike]

19.09.2014 04:21, Richard Hipp kirjutas:
> A simple script to reproduce the problem in the latest SQLite is as
> follows: CREATE TABLE t1(a INTEGER PRIMARY KEY, b INTEGER, c INTEGER,
> d INTEGER); CREATE INDEX t1b ON t1(b); CREATE TABLE t2(x INTEGER
> PRIMARY KEY, y); explain query plan SELECT * FROM t1, t2 WHERE x=c AND
> b=?1 ORDER BY a; .print -- CREATE INDEX t1bd ON
> t1(b,d); explain query plan SELECT * FROM t1, t2 WHERE x=c AND b=?1
> ORDER BY a; If you copy/paste the above script into an sqlite3
> command-line shell, you'll see that, for some reason, the query
> planner decides to sort manually rather than using an index for
> sorting after the t1bd index is added. We are still investigating to
> try understand why that is. Please note that if you run ANALYZE on
> your sample database, the query is fast again. On my (Ubuntu) desktop,
> the query takes 648 milliseconds before being analyzed and 600
> microseconds afterwards - a 1000-fold speedup.

A question: is the query being fast again after analyze call indicative
of the bug being fixed? Because I tried it on my original database too
and there I don't see a speedup after analyze. Should I try to minimize
it to a smaller database again where the bug still occurs, even after
analyze? Or will the change you made fix my original database speed as
well despite the analyze call not helping it?

Merike
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] An order by problem, maybe a bug?

[John Hascall]

The problem with your suggestion of 'two uses => two fields' is that no sooner 
do you do that then somebody comes up with additional uses, for example, formal 
greeting, informal greeting, the appropriate form for government form X123, and 
so on

John Hascall
IT Services
Iowa State Univ.

> On Sep 20, 2014, at 2:21 PM, Simon Slavin  wrote:
> 
> 
>> On 20 Sep 2014, at 7:42pm, James K. Lowden  wrote:
>> 
>> I'm saying more than one sort order is often needed.  If you don't
>> distinguish among the components of the person's name, you can't sort
>> by those components.  
> 
> I don't understand why anyone would want to sort on firstname or middle name. 
>  I suppose having them indexed might be useful for searching, but apart from 
> very specific census questions like "How popular were certain names in the 
> 1990s ?" I see no reason to do it.
> 
>> You offered the OP sweeping contrarian advise on how to represent names
>> in a database, referring him to one odd source having nothing to do
>> with databases.
> 
> That one odd source is so widely quoted nobody else has bothered to write 
> anything on the subject.  A search on "People have exactly one canonical full 
> name" gives me 1,830 hits and that's just the quotes rather than the links 
> and references.
> 
>> Your suggestion essentially amounts to "names are not
>> decomposable, so keep one version for the user and one for the
>> system."
> 
> Sorry, I don't think I got that across effectively.  If I make up a database 
> that stores names, I'm storing them pretty-much for two main reasons: to know 
> the name and to sort by name on displays and printouts.  So I keep one column 
> for the whole name ("to know the name") and one for the name in the order I 
> want to see it sorted ("to sort by names").  Two things you need, make two 
> columns.  I see no reason to split name up into three artificial units.
> 
> Searching by name is going to require LIKE and Soundex or something like it.  
> I've seen a database where a third column was used to store the soundex 
> encoding of the full name.  (Might have been something else like Soundex.  It 
> was a long time ago.)  In SQLite you could probably feed that column with 
> TRIGGERs.
> 
>> Given the thousands of databases out there that use first,
>> middle, and last names as columns -- with apparent success -- and
>> theoretical and practical problems arising from your alternative, I
>> suggest your advice is ill-founded.  
> 
> I suspect that those thousands of databases had their fields defined in the 
> 1970s, along with checking to see that all three fields weren't blank and 
> didn't have anything but letters in them.  And that such things would only be 
> done in the US.  Certainly anyone who designed a database that way in England 
> (where I live) would have to immediately write a page full of instructions 
> about how to squeeze many names I see around here into those three fields.
> 
> If you want to assess their success, ask yourself how many times you've seen 
> NO-MIDDLE-NAME in official records.  Or someone who apparently has the 
> lastname of OMALLEY.  Or someone who apparently has a lastname of Al-Haj and 
> is therefore sorted into the 'A's.
> 
> Anyone who worked for a big company these days and created such a database 
> should get called in and told to do it again properly.
> 
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] An order by problem, maybe a bug?

[Petite Abeille]

On Sep 20, 2014, at 9:21 PM, Simon Slavin  wrote:

> Anyone who worked for a big company these days and created such a database 
> should get called in and told to do it again properly.

Along these same lines:

Your last name contains invalid characters
http://blog.jgc.org/2010/06/your-last-name-contains-invalid.html

Falsehoods Programmers Believe About Names
http://www.kalzumeus.com/2010/06/17/falsehoods-programmers-believe-about-names/

How do you like the vCard specification?

http://tools.ietf.org/html/rfc6350

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] An order by problem, maybe a bug?

[Simon Slavin]

On 20 Sep 2014, at 7:42pm, James K. Lowden  wrote:

> I'm saying more than one sort order is often needed.  If you don't
> distinguish among the components of the person's name, you can't sort
> by those components.  

I don't understand why anyone would want to sort on firstname or middle name.  
I suppose having them indexed might be useful for searching, but apart from 
very specific census questions like "How popular were certain names in the 
1990s ?" I see no reason to do it.

> You offered the OP sweeping contrarian advise on how to represent names
> in a database, referring him to one odd source having nothing to do
> with databases.

That one odd source is so widely quoted nobody else has bothered to write 
anything on the subject.  A search on "People have exactly one canonical full 
name" gives me 1,830 hits and that's just the quotes rather than the links and 
references.

> Your suggestion essentially amounts to "names are not
> decomposable, so keep one version for the user and one for the
> system."

Sorry, I don't think I got that across effectively.  If I make up a database 
that stores names, I'm storing them pretty-much for two main reasons: to know 
the name and to sort by name on displays and printouts.  So I keep one column 
for the whole name ("to know the name") and one for the name in the order I 
want to see it sorted ("to sort by names").  Two things you need, make two 
columns.  I see no reason to split name up into three artificial units.

Searching by name is going to require LIKE and Soundex or something like it.  
I've seen a database where a third column was used to store the soundex 
encoding of the full name.  (Might have been something else like Soundex.  It 
was a long time ago.)  In SQLite you could probably feed that column with 
TRIGGERs.

> Given the thousands of databases out there that use first,
> middle, and last names as columns -- with apparent success -- and
> theoretical and practical problems arising from your alternative, I
> suggest your advice is ill-founded.  

I suspect that those thousands of databases had their fields defined in the 
1970s, along with checking to see that all three fields weren't blank and 
didn't have anything but letters in them.  And that such things would only be 
done in the US.  Certainly anyone who designed a database that way in England 
(where I live) would have to immediately write a page full of instructions 
about how to squeeze many names I see around here into those three fields.

If you want to assess their success, ask yourself how many times you've seen 
NO-MIDDLE-NAME in official records.  Or someone who apparently has the lastname 
of OMALLEY.  Or someone who apparently has a lastname of Al-Haj and is 
therefore sorted into the 'A's.

Anyone who worked for a big company these days and created such a database 
should get called in and told to do it again properly.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] An order by problem, maybe a bug?

[James K. Lowden]

On Fri, 19 Sep 2014 17:40:52 +0100
Simon Slavin  wrote:

> > Problems arising from the schema you suggest:
> > 
> > 1.  select by last name
> > 2.  select by first name
> > 3.  duplicate detection[1]
> > 4.  "however they want" is unknown and idiosyncratic
> > 5.  "whatever order" may be more than one
> 
> For 1, why are you selecting by last name ?  The person I named above
> has the surname "Nielsen Hayden".  Which would you be selecting and
> why ?

Some small confusion here.  By "last name" I mean surname, not "last
string in name delimited by white space."  

First name: Wernher
Last name:  von Braun

You say it shouldn't sort by "von", but that's a cultural choice, too,
cf. the Manhattan phone book.  

> For 2, match the string entered with the beginning of the 'name'
> field, probably using LIKE and a percent character.

So you're assuming the beginning of the "name" column -- entered
"however they want" is the first name.  I guess in a literal sense
that's true.  You're going to have trouble with Fran, Frank, and
Francis, though.  That you have to use pattern matching for equality
suggests a problem, if not an error.  

> For 5, are you telling me you don't know how you want the name
> sorted ?  

No, I'm saying more than one sort order is often needed.  If you don't
distinguish among the components of the person's name, you can't sort
by those components.  

You offered the OP sweeping contrarian advise on how to represent names
in a database, referring him to one odd source having nothing to do
with databases.  Your suggestion essentially amounts to "names are not
decomposable, so keep one version for the user and one for the
system."  Given the thousands of databases out there that use first,
middle, and last names as columns -- with apparent success -- and
theoretical and practical problems arising from your alternative, I
suggest your advice is ill-founded.  

I'm aware that different cultures use a variety of systems.  When
people in those cultures encounter computerized databases and large
bureaucracies, they deal.  They adapt.  Family tradition might reckon
17 names and three hyphens, but the birth certificate will hold only
three or so, and that forms the legal basis for the baby's
identification in society.  

You don't have to go very far before the straightjacket doesn't fit,
but for practical purposes it doesn't matter. I know a woman "Peg" who
was born "Margaret". Which name appears in which database depends on its
purpose.  In the email system she's Peg; on her paycheck it says
Margaret.  

I imagine the application exists that requires the name be rendered
just as the person prefers.  Usually, though, databases are used for
sorting, searching, and (most important) identifying.  Those purposes
are better served by distinguishing conventionally among the parts of a
person's name.  

--jkl
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] An order by problem, maybe a bug?

[James K. Lowden]

On Fri, 19 Sep 2014 11:42:26 -0700
Roger Binns  wrote:

> You do realise there are more people in the US than just those born
> in the country with good old fashioned roman alphabet 26 ascii
> letters?  

Yes.  Did I mention ASCII?  

--jkl
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] 50% faster than 3.7.17

[Richard Hipp]

On Sat, Sep 20, 2014 at 1:34 PM,  wrote:

> In trying to see if the new version breaks any of my queries, I ran
> several of them repeatedly, and they all appear to have produced the
> expected output.
>
> The only thing I noticed which maybe of interest in relation to speed
> performance was (with .timer on) that although the first two run time
> numbers (real & user) were consistently smaller in 3.8.7 (when compared to
> 3.8.6), the third number (sys) was consistently higher (or same in one
> occasion).  I guess the first number is the actual time (in seconds) it
> took to run the query.  I don't even know what the 2nd and 3rd numbers
> represent, and how or if they maybe related to the first one.  Is that
> increase in sys to be expected?
>

Thanks for the report.  I think the increased system time is harmless, but
I want to investigate further to be sure.  Except right now I'm preoccupied
with Mr. Xu's new bug.  So please remind me next week if I don't bring this
up again.  :-)

What OS are you using?

Can you share your database and test script with us?



>
> A few examples from many more I tried that all follow the same pattern
> (same query & database in each case):
>
> 3.8.6: Run Time: real 2.434 user 2.386815 sys 0.00
> 3.8.7: Run Time: real 1.856 user 1.778411 sys 0.062400
> ---
> 3.8.6: Run Time: real 584.465 user 560.293192 sys 1.638011
> 3.8.7: Run Time: real 518.227 user 430.469159 sys 53.617544
> ---
> 3.8.6: Run Time: real 2.449 user 2.340015 sys 0.046800
> 3.8.7: Run Time: real 1.935 user 1.794012 sys 0.046800
>
> (Thank you for two great solutions I use daily -- SQLite3 and Fossil)
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] 50% faster than 3.7.17

[tonyp]

In trying to see if the new version breaks any of my queries, I ran several 
of them repeatedly, and they all appear to have produced the expected 
output.


The only thing I noticed which maybe of interest in relation to speed 
performance was (with .timer on) that although the first two run time 
numbers (real & user) were consistently smaller in 3.8.7 (when compared to 
3.8.6), the third number (sys) was consistently higher (or same in one 
occasion).  I guess the first number is the actual time (in seconds) it took 
to run the query.  I don't even know what the 2nd and 3rd numbers represent, 
and how or if they maybe related to the first one.  Is that increase in sys 
to be expected?


A few examples from many more I tried that all follow the same pattern (same 
query & database in each case):


3.8.6: Run Time: real 2.434 user 2.386815 sys 0.00
3.8.7: Run Time: real 1.856 user 1.778411 sys 0.062400
---
3.8.6: Run Time: real 584.465 user 560.293192 sys 1.638011
3.8.7: Run Time: real 518.227 user 430.469159 sys 53.617544
---
3.8.6: Run Time: real 2.449 user 2.340015 sys 0.046800
3.8.7: Run Time: real 1.935 user 1.794012 sys 0.046800

(Thank you for two great solutions I use daily -- SQLite3 and Fossil)

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Inefficient query plan with ORDER BY and UNION ALL subquery

[Richard Hipp]

On Sat, Sep 20, 2014 at 12:57 PM, Clemens Ladisch 
wrote:

> Yuanzhong Xu wrote:
> > There is a related issue:
> >
> > If you use this valid efficient query as a subquery of SELECT id FROM
> (...), i.e.,
> >
> > SELECT id FROM (SELECT id,data FROM (SELECT * FROM t1 UNION ALL SELECT
> > * FROM t2) WHERE id=10 ORDER BY data);
> >
> > SQLite reports error:
> > Error: 1st ORDER BY term out of range - should be between 1 and 1
>
> This is a bug in SQLite.
>

Indeed.  If you turn on assert()s (by compiling with -DSQLITE_DEBUG) then
it gives an assertion fault.  The problem is recorded in a new ticket:


http://www.sqlite.org/src/tktview/d11a6e908f8bb04ddf0de0f95b9229ddaad2fd85



>
> Minimum example to reproduce this:
>
>   create table t(x);
>   select x from (select 42, x from (select x from t union all select x
> from t) order by x);
>
>
> Regards,
> Clemens
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Inefficient query plan with ORDER BY and UNION ALL subquery

[Clemens Ladisch]

Yuanzhong Xu wrote:
> There is a related issue:
>
> If you use this valid efficient query as a subquery of SELECT id FROM (...), 
> i.e.,
>
> SELECT id FROM (SELECT id,data FROM (SELECT * FROM t1 UNION ALL SELECT
> * FROM t2) WHERE id=10 ORDER BY data);
>
> SQLite reports error:
> Error: 1st ORDER BY term out of range - should be between 1 and 1

This is a bug in SQLite.

Minimum example to reproduce this:

  create table t(x);
  select x from (select 42, x from (select x from t union all select x from t) 
order by x);


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Inefficient query plan with ORDER BY and UNION ALL subquery

[Yuanzhong Xu]

There is a related issue:

If you use this valid efficient query ,

SELECT id,data FROM (SELECT * FROM t1 UNION ALL SELECT * FROM t2)
WHERE id=10 ORDER BY data;

as a subquery of SELECT id FROM (...), i.e.,

SELECT id FROM (SELECT id,data FROM (SELECT * FROM t1 UNION ALL SELECT
* FROM t2) WHERE id=10 ORDER BY data);

SQLite reports error:
Error: 1st ORDER BY term out of range - should be between 1 and 1

Thanks,
Yuanzhong

On Sat, Sep 20, 2014 at 5:36 AM, Dan Kennedy  wrote:
> On 09/20/2014 10:59 AM, Yuanzhong Xu wrote:
>>
>> I think this is related to a check for restriction (18) in subquery
>> flattening.
>>
>> (18) If the sub-query is a compound select, then all terms of the
>> ORDER by clause of the parent must be simple references to
>> columns of the sub-query.
>
>
> Quite correct. Internally, SQLite 'flattens' the first query to:
>
>SELECT id, data FROM t1 WHERE id=10
>  UNION ALL
>SELECT id, data FROM t2 WHERE id=10
>ORDER BY data
>
> Which can be executed efficiently. But it does not currently do the same
> trick for the second, as:
>
>SELECT id FROM t1 WHERE id=10
>  UNION ALL
>SELECT id FROM t2 WHERE id=10
>ORDER BY data
>
> is not a valid statement in SQLite.
>
> Dan.
>
>
>
>>
>> Thanks,
>> Yuanzhong
>>
>> On Fri, Sep 19, 2014 at 8:16 PM, Yuanzhong Xu  wrote:
>>>
>>> Hi,
>>>
>>> In SQLite 3.8.6, suppose I have two tables in the same format:
>>>
>>> CREATE TABLE t1 (id INTEGER PRIMARY KEY AUTOINCREMENT, data TEXT);
>>> CREATE TABLE t2 (id INTEGER PRIMARY KEY AUTOINCREMENT, data TEXT);
>>>
>>>
>>> The query plan can be quite efficient for:
>>>
>>> EXPLAIN QUERY PLAN SELECT id,data FROM (SELECT * FROM t1 UNION ALL
>>> SELECT * FROM t2) WHERE id=10 ORDER BY data;
>>>
>>> 1|0|0|SEARCH TABLE t1 USING INTEGER PRIMARY KEY (rowid=?)
>>> 2|0|0|SEARCH TABLE t2 USING INTEGER PRIMARY KEY (rowid=?)
>>> 0|0|0|COMPOUND SUBQUERIES 1 AND 2 (UNION ALL)
>>>
>>> However, if you remove "data" column from the outer SELECT, it becomes
>>> very inefficient:
>>>
>>> EXPLAIN QUERY PLAN SELECT id FROM (SELECT * FROM t1 UNION ALL SELECT *
>>> FROM t2) WHERE id=10 ORDER BY data;
>>>
>>> 2|0|0|SCAN TABLE t1
>>> 3|0|0|SCAN TABLE t2
>>> 1|0|0|COMPOUND SUBQUERIES 2 AND 3 (UNION ALL)
>>> 0|0|0|SCAN SUBQUERY 1
>>> 0|0|0|USE TEMP B-TREE FOR ORDER BY
>>>
>>>
>>> Thanks,
>>> --
>>> Yuanzhong Xu
>>> Ph.D. Student
>>> Department of Computer Science
>>> the University of Texas at Austin
>>
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How preserve the string metric in an extension.

[Andrea Peri]

Hi,

I resolve the bug.

Thx for explanations.

Effectively the proble was that the documentation dont explain very
well why is needed to call a sqlite3_value_text.
The only obvious explanation is that it is changed,
but never say about what is this changed, neither is report what
transformation algoritm is used to transform to and from.
So I have not any information to understand is the bug is in my code
or in the sqlite code.

Starting from the response of Richard I study the code and see where
was the bug and resolve it.

Greetings,

A.



-- 
-
Andrea Peri
. . . . . . . . .
qwerty àèìòù
-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Rrepresentation of REAL type - answered!

[TimeOfDay.nl]

Adminer sets the precision 
 directive.

That's the correct answer. So it is PHP itself thats rounds to 14 decimals,
as defined in php.ini.  Adminer overrides it.

On Sat, Sep 20, 2014 at 4:37 AM, Richard Hipp  wrote:

> On Fri, Sep 19, 2014 at 11:46 AM, TimeOfDay.nl 
> wrote:
>
> > I am looking at my SQLite database in two ways:
> >
> > 1. from PHP, in my application,  thru 'select * from table' statements
> and
> > the such
> > 2. from Adminer, as a database management tool
> >
> > When I look at a field that is type Real, and has the value of -41.29 as
> > the entered value, the results differ.
> >
> > - The PHP select gives me -41.29
> > - Adminer gives me -41.289147
> >
> > Why this difference?  Does the php library do something to 'round'
> numbers
> > to so-many decimals?
> >
>
>
> See http://www.sqlite.org/faq.html#q16
>
> The think to remember is that IEEE floating point numbers are
> approximations.  They are designed to solve real world physic problems.
> And in the real world it is pysically impossible to tell -41.29 and
> -41.289147 apart.  So you might as well think of them as the
> same number.
>
> A 64-bit IEEE floating point number variable cannot, in fact, contain
> exactly -41.29.  The closest it can get is
> -41.28914734871708787977695465087890625.  SQLite doesn't even
> try to get that close - promising only that floating point value are
> correct to 15 significant digits.
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
TimeOfDay web services | timeofday.nl | +31 (0)68 198 6848
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Inefficient query plan with ORDER BY and UNION ALL subquery

[Dan Kennedy]

On 09/20/2014 10:59 AM, Yuanzhong Xu wrote:

I think this is related to a check for restriction (18) in subquery flattening.

(18) If the sub-query is a compound select, then all terms of the
ORDER by clause of the parent must be simple references to
columns of the sub-query.


Quite correct. Internally, SQLite 'flattens' the first query to:

   SELECT id, data FROM t1 WHERE id=10
 UNION ALL
   SELECT id, data FROM t2 WHERE id=10
   ORDER BY data

Which can be executed efficiently. But it does not currently do the same 
trick for the second, as:


   SELECT id FROM t1 WHERE id=10
 UNION ALL
   SELECT id FROM t2 WHERE id=10
   ORDER BY data

is not a valid statement in SQLite.

Dan.





Thanks,
Yuanzhong

On Fri, Sep 19, 2014 at 8:16 PM, Yuanzhong Xu  wrote:

Hi,

In SQLite 3.8.6, suppose I have two tables in the same format:

CREATE TABLE t1 (id INTEGER PRIMARY KEY AUTOINCREMENT, data TEXT);
CREATE TABLE t2 (id INTEGER PRIMARY KEY AUTOINCREMENT, data TEXT);


The query plan can be quite efficient for:

EXPLAIN QUERY PLAN SELECT id,data FROM (SELECT * FROM t1 UNION ALL
SELECT * FROM t2) WHERE id=10 ORDER BY data;

1|0|0|SEARCH TABLE t1 USING INTEGER PRIMARY KEY (rowid=?)
2|0|0|SEARCH TABLE t2 USING INTEGER PRIMARY KEY (rowid=?)
0|0|0|COMPOUND SUBQUERIES 1 AND 2 (UNION ALL)

However, if you remove "data" column from the outer SELECT, it becomes
very inefficient:

EXPLAIN QUERY PLAN SELECT id FROM (SELECT * FROM t1 UNION ALL SELECT *
FROM t2) WHERE id=10 ORDER BY data;

2|0|0|SCAN TABLE t1
3|0|0|SCAN TABLE t2
1|0|0|COMPOUND SUBQUERIES 2 AND 3 (UNION ALL)
0|0|0|SCAN SUBQUERY 1
0|0|0|USE TEMP B-TREE FOR ORDER BY


Thanks,
--
Yuanzhong Xu
Ph.D. Student
Department of Computer Science
the University of Texas at Austin

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] An order by problem, maybe a bug?

[Klaas V]

Simon wrote about parts of names like e.g. the Dutch/Flemish 'van'

They should definitely not be capitalised

Is not always true. Especially in northern Belgium names are often spelled like 
Van (often even connected with the last name)  and I did personally the same to 
see which of the two e-mail clients I used at work so the NOCASE tag may cause 
inconsistencies as well.

Anyway to split a name in two, three or maybe even more parts is not a bad 
idea. The solution with a number of commas is a very good one.

Kind regards | Cordiali saluti | Vriendelijke groeten | Freundliche Grüsse,
Klaas `Z4us` V  - OrcID -0001-7190-2544
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users