[sqlite] Why this SQL does not work?
Hi, I have two tables: CREATE TABLE A ( ID integer primary key, SERVER_ID integer ) CREATE TABLE B ( ID integer primary key, GROUP_ID integer, SERVER_ID integer ) The following SQL does not work, complaint is Error: no such column: B.GROUP_ID select ID, (select SERVER_ID from A where A.ID=B.GROUP_ID) as GROUP_ID from B I realize that I can do it in other ways to make it work. However, anyone has an explanation why this SQL does not work with SQLITE? It works with MYSQL and MS ACCESS. Thanks in advance. Wei
Re: [sqlite] Why this SQL does not work?
On Thu, Nov 11, 2004 at 09:40:44AM -0500, [EMAIL PROTECTED] wrote: [...] The following SQL does not work, complaint is Error: no such column: B.GROUP_ID select ID, (select SERVER_ID from A where A.ID=B.GROUP_ID) as GROUP_ID from B [...] Looks like you haven't read this resource, yet: http://sqlite.org/omitted.html It mentions, among others: Variable subqueries Subqueries must be static. They are evaluated only once. They may not, therefore, refer to variables in the main query. -- Gerhard -- A: Because it messes up the order in which people normally read text. Q: Why is top-posting such a bad thing? A: Top-posting. Q: What is the most annoying thing on usenet and in e-mail? signature.asc Description: Digital signature
Re: [sqlite] Why this SQL does not work?
Gerhard Haering wrote: On Thu, Nov 11, 2004 at 09:40:44AM -0500, [EMAIL PROTECTED] wrote: [...] The following SQL does not work, complaint is Error: no such column: B.GROUP_ID select ID, (select SERVER_ID from A where A.ID=B.GROUP_ID) as GROUP_ID from B [...] Looks like you haven't read this resource, yet: http://sqlite.org/omitted.html It mentions, among others: Variable subqueries Subqueries must be static. They are evaluated only once. They may not, therefore, refer to variables in the main query. I'm not the original poster.. but I'm trying to come up to speed on SQL/sqlite and I'm having some trouble understanding this... What is an example of a static subquery? And how would one re-write this to eliminate the subquery? -Alan
Re: [sqlite] Why this SQL does not work?
select ID, (select SERVER_ID from A where A.ID=B.GROUP_ID) as GROUP_ID from B [...] It mentions, among others: Variable subqueries Subqueries must be static. They are evaluated only once. They may not, therefore, refer to variables in the main query. I'm not the original poster.. but I'm trying to come up to speed on SQL/sqlite and I'm having some trouble understanding this... What is an example of a static subquery? The above is an example of a static sub-query. It's a query within a query. And how would one re-write this to eliminate the subquery? select B.ID, A.SERVER_ID from B inner join A on B.GROUP_ID = A.ID or select B.ED, A.SERVER_ID fromA, B where A.ID = B.GROUP_ID I think.
Re: [sqlite] Why this SQL does not work?
Brass Tilde wrote: select ID, (select SERVER_ID from A where A.ID=B.GROUP_ID) as GROUP_ID from B [...] It mentions, among others: Variable subqueries Subqueries must be static. They are evaluated only once. They may not, therefore, refer to variables in the main query. I'm not the original poster.. but I'm trying to come up to speed on SQL/sqlite and I'm having some trouble understanding this... What is an example of a static subquery? The above is an example of a static sub-query. It's a query within a query. Hmm.. thanks but I'm still confused... isn't the above an example of a non-static query that used variables from the main query? -Alan
Re: [sqlite] Why this SQL does not work?
amead [EMAIL PROTECTED] writes: Brass Tilde wrote: select ID, (select SERVER_ID from A where A.ID=B.GROUP_ID) as GROUP_ID Hmm.. thanks but I'm still confused... isn't the above an example of a non-static query that used variables from the main query? If it uses variables from the main query, then by SQLite's definition, it's not a static query. Another way to put it, is that subqueries in SQLite may not use variables from the main query. Derrell
[sqlite] looking for suggestions to speed up SQL
Hi All, I have a situation where populating data in a temp table takes a long time, about 8 minutes, and I am wondering if anyone can offer any suggestions for ways to speed it up. I have the permanent table and associated index, shown below, which holds tree structured component data. Each node is related to its parent by the container_id field. create table Component ( component_idinteger primary key, component_kind_id integer not null references Component_Kind, component_numberinteger not null, -- number of this component -- within its container container_idinteger references Component, -- component id of container component_path varchar,-- complete path to component -- ids seperated by slashes component_function_id integer default 0 references Component_Function, unique (container_id, component_kind_id, component_number) ); --index used to speed container lookups create index Component_Container_Index on Component (container_id); Some parts of my application need additional data. Specifically, the number of sub components and peer components associated with each component. Since this data is not needed by the main application, and can be recreated from the component tree table, it is not stored in the component table to minimize the size of the database. Where this additional data is needed I create a temporary table, populate it with the count data, and use a view to join the two tables. The temporary table is defined as; create temp table Component_Count ( component_idinteger primary key references Component, sub_count integer, --count of subnodes ie nodes where parent = id peer_count integer --count of peers ie same parent and same kind ); and I populate the temp table with the following SQL statement; insert into Component_Count select sub.id, sub_count, peer_count from (select pid as id, count(nid) as sub_count from (select p.component_id as pid, n.component_id as nid from Component p left outer join Component n on p.component_id = n.container_id) group by pid) as sub join (select nid as id, count(pid) as peer_count from (select n.component_id as nid, p.component_id as pid from Component n left outer join Component p on (n.component_id != p.component_id and n.container_id = p.container_id and n.component_kind_id = p.component_kind_id)) group by id) as peer using (id); This statement produces the correct result, but when the component table is large, about 30,000 records, it takes quite a long time to execute, about 8 minutes. So can anyone suggest another way to populate the table that might be faster? Thanks.
[sqlite] Any better way to get info about table
Hello, I wonder is there better way to get table column/types information than usage of EXPLAIN especially on empty table. Michael
[sqlite] Any better way to get info about table
Return Receipt Your [sqlite] Any better way to get info about table document : was Ishwar Jasuja/NA/Johnson_Controls received by: at: 11/11/2004 12:06:05 PM PST
Re: [sqlite] looking for suggestions to speed up SQL
Dennis Cote wrote: Hi All, I have a situation where populating data in a temp table takes a long time, about 8 minutes, and I am wondering if anyone can offer any suggestions for ways to speed it up. I have the permanent table and associated index, shown below, which holds tree structured component data. Each node is related to its parent by the container_id field. create table Component ( component_idinteger primary key, component_kind_id integer not null references Component_Kind, component_numberinteger not null, -- number of this component -- within its container ... component_function_id integer default 0 references Component_Function, unique (container_id, component_kind_id, component_number) ); A primary key is always unique. So putting the primary key as the first element of a UNIQUE index accomplishes no good. But it does make the database larger and it makes it run slowly by given the database engine more things to have to keep up-to-date. --index used to speed container lookups create index Component_Container_Index on Component (container_id); An index that is on the primary key is redundant. This is especially true of an INTEGER PRIMARY KEY in SQLite. The index will never be used to speed up a search. (If it where used, it would only slow the search down.) But CPU cycles and disk space are consumed maintaining the index all the same. So an index on a primary key is a net loss. and I populate the temp table with the following SQL statement; insert into Component_Count select sub.id, sub_count, peer_count from (select pid as id, count(nid) as sub_count from (select p.component_id as pid, n.component_id as nid from Component p left outer join Component n on p.component_id = n.container_id) group by pid) as sub join (select nid as id, count(pid) as peer_count from (select n.component_id as nid, p.component_id as pid from Component n left outer join Component p on (n.component_id != p.component_id and n.container_id = p.container_id and n.component_kind_id = p.component_kind_id)) group by id) as peer using (id); Joining two unindexed tables (and the result of a subquery is not going to have an index) will always be an O(N**2) operation. You can change it into an O(NlogN) operation by storing the results of subqueries in separate temporary tables, creating indices on those temporary tables, then doing the join on the temporary tables. -- D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565
RE: [sqlite] Any better way to get info about table
Thanks! Seems I completely missed some of the pragmas :( -Original Message- From: D. Richard Hipp [mailto:[EMAIL PROTECTED] Sent: Thursday, November 11, 2004 10:36 PM To: [EMAIL PROTECTED] Subject: Re: [sqlite] Any better way to get info about table Michael Elfial wrote: Hello, I wonder is there better way to get table column/types information than usage of EXPLAIN especially on empty table. http://www.sqlite.org/pragma.html#schema -- D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565
RE: [sqlite] Any better way to get info about table
Return Receipt Your RE: [sqlite] Any better way to get info about table document : was Ishwar Jasuja/NA/Johnson_Controls received by: at: 11/11/2004 12:59:59 PM PST
[sqlite] .import function
Maybe I'm blind but I've looked and don't see the answer to this... what is the format for the file to be imported using .import in the sqlite3 CLI database management tool? -Alan
Re: [sqlite] looking for suggestions to speed up SQL
D. Richard Hipp wrote: Dennis Cote wrote: Hi All, I have a situation where populating data in a temp table takes a long time, about 8 minutes, and I am wondering if anyone can offer any suggestions for ways to speed it up. I have the permanent table and associated index, shown below, which holds tree structured component data. Each node is related to its parent by the container_id field. create table Component ( component_idinteger primary key, component_kind_id integer not null references Component_Kind, component_numberinteger not null, -- number of this component -- within its container ... component_function_id integer default 0 references Component_Function, unique (container_id, component_kind_id, component_number) ); A primary key is always unique. So putting the primary key as the first element of a UNIQUE index accomplishes no good. But it does make the database larger and it makes it run slowly by given the database engine more things to have to keep up-to-date. Richard, I think you misread my SQL. The primary key is component_id, the first component of the unique constraint is container_id. These are different fields. The unique constraint is there to enforce a real application constraint. --index used to speed container lookups create index Component_Container_Index on Component (container_id); An index that is on the primary key is redundant. This is especially true of an INTEGER PRIMARY KEY in SQLite. The index will never be used to speed up a search. (If it where used, it would only slow the search down.) But CPU cycles and disk space are consumed maintaining the index all the same. So an index on a primary key is a net loss. Same thing here. The index is on the field container_id which is NOT the primary key. In fact this index is very useful for fast container based lookups as below and in other places in my application. and I populate the temp table with the following SQL statement; insert into Component_Count select sub.id, sub_count, peer_count from (select pid as id, count(nid) as sub_count from (select p.component_id as pid, n.component_id as nid from Component p left outer join Component n on p.component_id = n.container_id) group by pid) as sub join (select nid as id, count(pid) as peer_count from (select n.component_id as nid, p.component_id as pid from Component n left outer join Component p on (n.component_id != p.component_id and n.container_id = p.container_id and n.component_kind_id = p.component_kind_id)) group by id) as peer using (id); Joining two unindexed tables (and the result of a subquery is not going to have an index) will always be an O(N**2) operation. You can change it into an O(NlogN) operation by storing the results of subqueries in separate temporary tables, creating indices on those temporary tables, then doing the join on the temporary tables. Thanks for the hint about creating indexes on the temp tables. I had discovered that I could create separate temp tables with the required counts using the following SQL statements. These statements execute in seconds (or less, its hard to tell). create temp table container_count as select container_id as component_id, count(*) as sub_count from Component group by container_id; create temp table container_kind_count as select container_id, component_kind_id, count(*) as peer_count from Component group by container_id, component_kind_id; But a join to combine the component ids with the sub component counts like this; select component.component_id, sub_count from component left outer join container_count using(component_id); still took a very long time. By issuing a separate create statement for the container_count table, one with the component id declared as a integer primary key (which effectively creates an index on that table), and then filling the table with an insert as select... I got an equivalent temp table with an index. Since the component table already has an index on component_id (it's also an integer primary key), my join statement now executes in about a second as well. I now do this; create temp table container_count ( component_idinteger primary key, sub_count integer ); insert into container_count select container_id, count(*) as sub_count from Component group by container_id; select component.component_id, sub_count from component left outer join container_count using(component_id); which executes in a second or two for the same 30,000 record table. Now all I need to do is combine this table with the peer counts. SQLite rocks!
[sqlite] Update multiple columns from subquery
Is there some way to write this query in SQLite? UPDATE groups SET (fileCount, size) = (SELECT count(id), sum(size) FROM files where groupId = 15) WHERE groupId = 15; Or must it be written like this? UPDATE groups SET fileCount = (SELECT count(id) FROM files where groupId = 15), size = (SELECT sum(size) FROM files where groupId = 15) WHERE groupId = 15; I mostly want to get the two selects into one and I can't place code between the two.
Re: [sqlite] Update multiple columns from subquery
Peter Bartholdsson wrote: Is there some way to write this query in SQLite? UPDATE groups SET (fileCount, size) = (SELECT count(id), sum(size) FROM files where groupId = 15) WHERE groupId = 15; Sadly, no. SQLite only allows a subquery to return a single value, not a tuple. -- D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565
