Re: [sqlite] Regarding CoC

[Klaus Maas]

Funny world that one needs a code of contact to be considered civilized.

No need to defend your CoC.

I see it as a non-exhaustive list of values important to the developers.

That they are Christian-based gives some context for their interpretation.

Can't see any fault in that.

I may or may not share some/all/any of these values, but I should 
respect their significance for the developers.


Klaus

On 22/10/2018 18.32, Chris Brody wrote:

I would vote for a major simplification, down to something like "love
thy neighbor", "do unto others as ...", or "don't do unto others as
..."

For reference:
*https://www.simpletoremember.com/jewish/blog/loving-thy-neighbor-judaism/
*https://www.biblegateway.com/passage/?search=Galatians+5%3A14=KJV
On Mon, Oct 22, 2018 at 12:26 PM Simon Slavin  wrote:

On 22 Oct 2018, at 1:19pm, Richard Hipp  wrote:


Looks like that happened this morning.
https://news.ycombinator.com/item?id=18273530

It also hit Reddit, in /r/programming.  Currently 239 comments:



Simon.


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

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

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

Re: [sqlite] beginners question

[Klaus Maas]

email signature Klaus Maas

On 2018-08-30 21:39, Jürgen Palm wrote:

Richard Hipp wrote:

On 8/30/18, Jürgen Palm  wrote:


Richard Hipp wrote:

On 8/30/18, Klaus Maas  wrote:

Same issues on Xubuntu 16.04 and 18.04

US international keyboard layout

Actually 2 questions:

1) The precompiled binary for Linux of sqlite3  does not run, but
sqldiff and sqlite3_analyzer do.
./sqlite3: error while loading shared libraries: libz.so.1: cannot 
open

shared object file: No such file or directory
What steps can I take to provide the missing library?

You have a desktop linux machine on which libz is not installed?  I
didn't even know that was possible.

What flavor of linux are you running?



He mentioned it in the first sentence: Xubuntu 16.04 and 18.04. Of
course, libz.so.1 is installed. I've currently running a virtual 
machine

with Xubuntu 18.04 and tried also to execute the precompiled binary and
it didn't work, but I didn't actually expect it as I'm running a 64bit
system. Most likely the problem from Klaus is caused by the same 
reason.
So, perhaps the solution is for Klaus to compile it himself from 
sources?


If he is indeed running a 64bit system he could also just install the 
package zlib1g:i386, which contains the 32bit version of libz.so.1. 
With this package installed, the precompiled sqlite binary is running 
properly on my 64bit system.
Dear Jürgen, I am running 64-bit versions of Xubuntu as you assumed 
correctly.

Thank you for the tip - and for providing the lib name explicitly.
"sudo apt install zlib1g:i386" did the trick.
The precompiled Linux sqlite version 3.24.0 starts as expected and the 
keyboard codes of the navigation keys are interpreted correctly.

Tried it on Xubuntu 16.04 so far only - still my main system.
What puzzled me at first was that neither apt policy nor apt search 
would show me the lib, even when requesting it litterally.
Then I remembered that on a 64-bit OS the 32-bit libraries are filtered 
out by default.

Now that the 32bit lib is installed apt policy will show its status.
Thank you all for helping me so swiftly.
Klaus




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

[sqlite] beginners question

[Klaus Maas]

Same issues on Xubuntu 16.04 and 18.04

US international keyboard layout

Actually 2 questions:

1) The precompiled binary for Linux of sqlite3  does not run, but 
sqldiff and sqlite3_analyzer do.
./sqlite3: error while loading shared libraries: libz.so.1: cannot open 
shared object file: No such file or directory

What steps can I take to provide the missing library?

2) When following the compile instructions on the website I get an 
executable sqlite3, however:


Letters, numbers work fine.

When pressing arrow keys, pgup, pgdwn there key scan codes appear in the 
terminal.


^[[A^[[B for up-arrow and down-arrow.

How do I fix that?


Klaus


email signature Klaus Maas

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

Re: [sqlite] Changed behaviour or bug using field alias in 3.21.0

[Klaus Maas]

Radovan is correct.
Executing the same command sequence in version 3.11.0 and 3.21.0 results 
in different column names for table test2.

(Xubuntu 16.04 with sqlite3.11.0 and sqlite3.21.0)
I marked the results with '<='
Klaus


SQLite version 3.11.0 2016-02-15 17:29:24
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .header on
sqlite>
sqlite> create table test(a int, b int);
sqlite> insert into test values (1,1);
sqlite>
sqlite> select d from (select c as d from (select a as c from test));
d
1
sqlite>
sqlite> create table test2 as
   ...> select d from (select c as d from (select a as c from test));
sqlite> SELECT * FROM test2;
d  <=
1
sqlite>



SQLite version 3.21.0 2017-10-24 18:55:49
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .header on
sqlite> create table test(a int, b int);
sqlite> insert into test values (1,1);
sqlite>
sqlite> select d from (select c as d from (select a as c from test));
d
1
sqlite> create table test2 as
   ...> select d from (select c as d from (select a as c from test));
sqlite> SELECT * FROM test2;
a   <=
1
sqlite>

email signature Klaus Maas

On 2017-12-22 20:11, Radovan Antloga wrote:

Just try this sql-s:

create table test(a int, b int);
insert into test values (1,1);

select d from (select c as d from (select a as c from test));

will return name d.

create table test2 as
select d from (select c as d from (select a as c from test));

will create table test2 with column name a.

with PostgreSQL I get table test2 with name d.

Thank you very much for your time!


Richard Hipp je 22.12.2017 ob 20:06 napisal:

On 12/22/17, Radovan Antloga <radovan.antl...@siol.net> wrote:

My point is you do not have to change anything regards
to how select works. SELECT statement is working just
great or OK or fine.

CREATE TABLE AS
SELECT 

does not give same name as SELECT does.

SELECT give name d

CREATE TABLE AS SELECT
create table with name a instead of d.

Can you provide a simple test case for this behavior?



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


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

Re: [sqlite] extracting domain names from website addresses efficiently

[Klaus Maas]

Very interesting.

I must be very slow, because at first I did no get what this was about, 
although you fed it me on a spoon.


This is actually very straightforward. I find this easier to understand 
than recursive CTEs.


Thank you, Peter and IgorT, for helping me on my way to understand this 
powerful feature.


Still some way to go, but that is  part of the fun.


email signature Klaus Maas

On 2017-12-12 00:22, petern wrote:

Klaus.  The CTE manual with good examples is at
https://www.sqlite.org/lang_with.html
IgorT posted some good stuff about your problem using CTE.

FYI. TRIGGER is also recursive.  Could be more efficient if you have to
store them anyway:

CREATE TABLE domain(d TEXT);
CREATE TRIGGER domain_after_ins AFTER INSERT ON domain WHEN instr(NEW.d,'.')
   BEGIN INSERT INTO domain VALUES(substr(NEW.d,instr(NEW.d,'.')+1)); END;

PRAGMA recursive_triggers=1;
INSERT INTO domain VALUES ('a.b.c.d');
INSERT INTO domain VALUES('e.f.g.1.2');
SELECT * FROM domain;
d
--
a.b.c.d
b.c.d
c.d
d
e.f.g.1.2
f.g.1.2
g.1.2
1.2
2

Peter







On Mon, Dec 11, 2017 at 9:22 AM, Klaus Maas <k...@maasser.eu> wrote:


Thank you, Igor, for your solution.
I expected that I would be pointed to a recursive approach.
Your solution is quite elegant in my opinion.
My problem, however, is that I have not yet understood recursive CTE's.
Could you give me some pointers for good resources to learn about them?

Well, your code example is helping a lot in understanding them, although I
am still a long way off producing something similar myself without
assistance.

There is 1 item in the last where condition of which I do not understand
the purpose:
(instr(subdomain, '.') = 0 and subdomain = long)
This means that anything in the form of 'ftp://test/' would output the
string between the two delimiters (:// and /), in this case 'test'.
But that is not a domain name in the format domain.tld.
(I am working under the assumption that table links is cleaned up contains
valid links only with the protocol and :// prepended.)
Or am I missing something?.

Klaus


On 2017-12-11 14:59, Igor Tandetnik wrote:


Something like this (could likely be simplified further, but this should
give the idea):

with recursive subdomains as (
   SELECT substr(link, instr(link, '://')+3, instr(substr(link,
instr(link, '://')+3), '/')-1) AS long,
  substr(link, instr(link, '://')+3, instr(substr(link,
instr(link, '://')+3), '/')-1) as subdomain
   FROM links
union all
   select long, substr(subdomain, instr(subdomain, '.') + 1)
   from subdomains
   where instr(subdomain, '.') > 0)
select * from subdomains
where (instr(subdomain, '.') = 0 and subdomain = long) OR
   (instr(subdomain, '.') > 0 and instr(substr(subdomain,
instr(subdomain, '.') + 1), '.')=0);

The main point is to recursively build a table of all suffixes, then
select just the suffixes you want.


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


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


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

Re: [sqlite] extracting domain names from website addresses efficiently

[Klaus Maas]

Thank you for your help!



On 2017-12-11 22:43, Igor Tandetnik wrote:

On 12/11/2017 12:22 PM, Klaus Maas wrote:
There is 1 item in the last where condition of which I do not 
understand the purpose:

(instr(subdomain, '.') = 0 and subdomain = long)


That's a zero-dot case, when the original domain is simply 'com', say. 
Added for completeness.


This means that anything in the form of 'ftp://test/' would output 
the string between the two delimiters (:// and /), in this case 'test'.

But that is not a domain name in the format domain.tld.


Feel free to adjust to taste.


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

Re: [sqlite] extracting domain names from website addresses efficiently

[Klaus Maas]

Thank you, Igor, for your solution.
I expected that I would be pointed to a recursive approach.
Your solution is quite elegant in my opinion.
My problem, however, is that I have not yet understood recursive CTE's.
Could you give me some pointers for good resources to learn about them?

Well, your code example is helping a lot in understanding them, although 
I am still a long way off producing something similar myself without 
assistance.


There is 1 item in the last where condition of which I do not understand 
the purpose:

(instr(subdomain, '.') = 0 and subdomain = long)
This means that anything in the form of 'ftp://test/' would output the 
string between the two delimiters (:// and /), in this case 'test'.

But that is not a domain name in the format domain.tld.
(I am working under the assumption that table links is cleaned up 
contains valid links only with the protocol and :// prepended.)

Or am I missing something?.

Klaus


On 2017-12-11 14:59, Igor Tandetnik wrote:
Something like this (could likely be simplified further, but this 
should give the idea):


with recursive subdomains as (
  SELECT substr(link, instr(link, '://')+3, instr(substr(link, 
instr(link, '://')+3), '/')-1) AS long,
 substr(link, instr(link, '://')+3, instr(substr(link, 
instr(link, '://')+3), '/')-1) as subdomain

  FROM links
union all
  select long, substr(subdomain, instr(subdomain, '.') + 1)
  from subdomains
  where instr(subdomain, '.') > 0)
select * from subdomains
where (instr(subdomain, '.') = 0 and subdomain = long) OR
  (instr(subdomain, '.') > 0 and instr(substr(subdomain, 
instr(subdomain, '.') + 1), '.')=0);


The main point is to recursively build a table of all suffixes, then 
select just the suffixes you want.


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

[sqlite] extracting domain names from website addresses efficiently

[Klaus Maas]

I hope someone can help me to improve my SQL.

The solution I came up with becomes unreadable after 3 dots in a website 
address (at least for me).


This is my approach:

The domain/subdomain name of a website address is enclosed between '://' 
and the following '/'.
So I trim any text before and including '://' and after '/' in line 17 - 
AS long


I calculate the number of dots in long - line 16 - AS dots

Because I could not find a way to figure out the position of the 2nd to 
last dot directly (pseudo: substr(string, '2nd to last dot')  or 
substr(string, nth('.')) ), I work with nested string manipulation.
The problem is that the nested string manipulation becomes unreadable 
very quickly.


The case statement (lines 6 to 14) then strips all unwanted text 
depending on the number of dots.

For 1 dot:  no stripping required
For 2 dots: a subdomain and the additional dot must be stripped
For 3 dots: the sub-subdomain and the additional w dots must be stripped
etc.
(CASE WHEN dots = 1  isn't really required, because the ELSE clause will 
cover this case as well.)


Sample data:

PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE links(link);
INSERT INTO "links" 
VALUES('https://stackoverflow.com/questions/7826876/sqlite-reverse-string-function');
INSERT INTO "links" 
VALUES('https://www.pcwelt.de/ratgeber/Virtuelle-Netzwerke-mit-Open-VPN-aufbauen-9889432.html?utm_source=best-of-pc-welt-manuell_medium=email_campaign=newsletter_id=3291863_cat%5B0%5D=Netzwerktechnologie_cat%5B1%5D=Netzwerke+allgemein_cat%5B2%5D=Personal+Computer_cat%5B3%5D=Netzwerk+Hardware_cat%5B4%5D=Apps_cat%5B5%5D=Client+Hardware=3677484228301571=784281_ln=3');

INSERT INTO "links" VALUES('http://dot3.dot2.memyself.eu/blabl.html');
INSERT INTO "links" 
VALUES('http://dot4.dot3.dot2.memyself.eu/miamia/blabl.html');
INSERT INTO "links" 
VALUES('http://dot5.dot4.dot3.dot2.memyself.eu/miamia/blabl.html');
INSERT INTO "links" 
VALUES('http://dot6.dot5.dot4.dot3.dot2.memyself.eu/miamia/blabl.html');
INSERT INTO "links" 
VALUES('http://dot7.dot6.dot5.dot4.dot3.dot2.memyself.eu/miamia/blabl.html');
INSERT INTO "links" 
VALUES('http://dot8.dot7.dot6.dot5.dot4.dot3.dot2.memyself.eu/miamia/blabl.html');
INSERT INTO "links" 
VALUES('http://dot9.dot8.dot7.dot6.dot5.dot4.dot3.dot2.memyself.eu/miamia/blabl.html');

COMMIT;

The example below is limited to 5 dots (domain plus 4 subdomain levels).
I did it for 9 dots as well and then the query blows up to 3 screen pages.

links:        name of source table:
link:     field name of website address
long:        field name of trimmed string
dots:        field name of calculated dots
domain:   field name of resulting domain.tld-string (stripped of any 
subdomain)


.mode column
.header on
.width 51 -4 51
-- up to 5 dots (= 4 subdomain levels)
SELECT long, dots,
(CASE
WHEN dots = 1 THEN long
WHEN dots = 2 THEN substr(long, instr(long, '.')+1)
WHEN dots = 3 THEN substr(substr(long, instr(long, '.')+1), 
instr(substr(long, instr(long, '.')+1), '.')+1)
WHEN dots = 4 THEN substr(substr(substr(long, instr(long, '.')+1), 
instr(substr(long, instr(long, '.')+1), '.')+1), 
instr(substr(substr(long, instr(long, '.')+1), instr(substr(long, 
instr(long, '.')+1), '.')+1), '.')+1)
WHEN dots = 5 THEN substr(substr(substr(substr(long, instr(long, 
'.')+1), instr(substr(long, instr(long, '.')+1), '.')+1), 
instr(substr(substr(long, instr(long, '.')+1), instr(substr(long, 
instr(long, '.')+1), '.')+1), '.')+1), instr(substr(substr(substr(long, 
instr(long, '.')+1), instr(substr(long, instr(long, '.')+1), '.')+1), 
instr(substr(substr(long, instr(long, '.')+1), instr(substr(long, 
instr(long, '.')+1), '.')+1), '.')+1), '.')+1)

ELSE long
END) AS domain
FROM
(SELECT long, length(long) - length(replace(long, '.', '')) AS dots
FROM (SELECT substr(link, instr(link, '://')+3, instr(substr(link, 
instr(link, '://')+3), '/')-1) AS long FROM links)

);

Is there a way to do this better/smarter?

Klaus

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

Re: [sqlite] SQL top 3

[Klaus Maas]

Dear David,

could you help me to understand why your query does what it does?

The magic seems to be in this bit where the field country is 
self-referenced:



where c.country = countries.country

Only: I don't understand why this does what we want it to do.

I tried to build a query of my own mimicking what your query is doing 
with methods I am more familiar with:
(using a cross-join (lines 2-4), filtering out the superfluous results 
(line 14), specifying the output fields explicitly (line 1) to exclude 
field country added by the 2nd cross-join partner)


SELECT a.country, a.city, a.population
FROM countries AS a,
 (SELECT DISTINCT country
  FROM countries) AS d
WHERE population >=
  (SELECT min(population)
   FROM (SELECT population
 FROM countries AS c
 WHERE c.country = d.country
 ORDER BY population DESC
 LIMIT 3
    )
  )
  AND a.country = d.country
ORDER BY a.country, a.population DESC;

To me EXPLAIN QUERY PLAN looks rather similar for both.
My query plan has 3 additional lines because of the additional SELECT 
(inserted between line 1 and 2 compared to your query plan).
The other lines are identical (except for the adjusted subquery count, 
of course).
Admittedly, I have VERY little experience interpreting the output of 
EXPLAIN QUERY PLAN.


Klaus

email signature Klaus Maas

On 2017-11-17 22:21, David Raymond wrote:

I think this works even if there are less than 3 cities listed for a country. 
If there's a tie for 3rd it'll show all of them.


create table countries (country text, city text, population int);

insert into countries values ('UK', 'London', '10'), ('UK', 'Birmingham', 9), 
('UK', 'Manchester', '8'), ('UK', 'Podunk', 1), ('USA', 'New York', 10), 
('USA', 'Los Angeles', 9), ('USA', 'Chicago', 8), ('USA', 'Podunk', 1), 
('Canada', 'Podunk', 1);

select * from countries
where population >=
(select min(population) from
   (select population from countries as c
where c.country = countries.country
order by population desc limit 3
   )
)
order by country, population desc;


country   city  population
    
CanadaPodunk1
UKLondon10
UKBirmingham9
UKManchester8
USA   New York  10
USA   Los Angeles   9
USA   Chicago   8


-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Bart Smissaert
Sent: Friday, November 17, 2017 3:58 PM
To: General Discussion of SQLite Database
Subject: [sqlite] SQL top 3

Say I have a table like this:

CREATE TABLE COUNTRIES(COUNTRY TEXT, CITY TEXT, POPULATION INTEGER)

What would be the SQL to get 3 cities for each country with the highest 3
populations for
that country? So, for example for the United Kingdom it would show London,
Birmingham, Manchester in that order, and for the USA New York, Los
Angeles, Chicago. So, I would like
these top 3 cities for all countries.

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


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

Re: [sqlite] Unexpected result from SELECT * FROM (subquery);

[Klaus Maas]

The table contains a single row with a single column that contains a string.

Like this explanation. Much better than my attempt.

What the outer select sees in its from clause is an ["unnamed-table"] 
that [contains one row containing 'tab1'].


Thank you for pushing this point.

The point I was trying to make and obviously failed was that the string 
returned by the select statement nested in the from clause is just a 
string which happens to be the same text as the name of a table.

Looking the same does not make it the same.

Klaus
email signature Klaus Maas

On 2017-11-05 14:20, Peter Da Silva wrote:

The table contains a single row with a single column that contains a string.

That doesn't make it a string. It's still a table. When you say "select ... from table" it doesn't matter 
where the table came from, it's still an operation on a table. You are not performing "select ... from 
'tab1';", you're performing "select ... from unnamed-table" where "unnamed-table" contains one 
row containing 'tab1'.

The result of *that* select is yet another unnamed table that the sqlite3 shell 
displays for you.

Even if you perform

select * from (select * from (select * from ( ... ) )

The result is still a table. Select is not an indirection operator like 
accessing an element of an array or a structure.

On 2017-11-05, at 05:39, Klaus Maas <k...@maasser.eu> wrote:

Yes, correct.

But the contents of the returned table are not objects, but merely values.

In this case the returned table contains a single string value  which 
happens to be the name of a table, but it is not the table.

Or do I get this wrong?

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


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

Re: [sqlite] Unexpected result from SELECT * FROM (subquery);

[Klaus Maas]

Yes, correct.

But the contents of the returned table are not objects, but merely values.

In this case the returned table contains a single string value  
which happens to be the name of a table, but it is not the table.


Or do I get this wrong?

email signature Klaus Maas Klaus


On 2017-11-05 12:31, Peter Da Silva wrote:

On 2017-11-05, at 05:28, Klaus Maas <k...@maasser.eu> wrote:

I thought it was because what SQL returns is a value (in this case a string) 
and not an object?

The string value might be the same as the name of an object, but is not the 
object.

Select returns a table, not a name or a string.

The outer select operates on this unnamed table, not any particular cell in it.

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


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

Re: [sqlite] Unexpected result from SELECT * FROM (subquery);

[Klaus Maas]

I thought it was because what SQL returns is a value (in this case a 
string) and not an object?


The string value might be the same as the name of an object, but is not 
the object.


email signature Klaus Maas Klaus



On 2017-11-05 11:51, Richard Hipp wrote:

On 11/5/17, Shane Dev <devshan...@gmail.com> wrote:

In sqlite3, I executed the following statements -

sqlite> select name from tabs where rowid=1;
tab1
sqlite> select * from tab1;
first rec

sqlite> select * from (select name from tabs where rowid=1);
tab1

I expected the last statement to evaluate the subquery first to be 'tab1'
and then execute SELECT * FROM tab1 to yield 'first rec'. Why didn't this
happen?

Because that is not the way SQL works.  The statement

SELECT * FROM (SELECT name FROM tabs WHERE rowid=1);

is logically equivalent to:

CREATE TEMP TABLE "some-random-name" AS
  SELECT name FROM tabs WHERE rowid=1;
SELECT * FROM "some-random-name";
DROP TABLE "some-random-name";

SQL works on a compile-then-execute model.  Each SQL statement is
first analyzed and compiled into bytecode or into machine code or some
other executable format.  Then the resulting compiled code is run to
generate a result.  The names of tables and columns are fixed at
compile-time and cannot be modified at runtime, since to do so would
require on-the-fly changes to the compiled code.



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