[sqlite] Re: Probably, bug in SQLite (Or, what is wrong with this query?)
> Does these queries work on other databases? I can test only MS SQL Server today, and the original query generates error. IMHO, the bug is in both SQLite and MS SQL Server :) > Are they valid correlated subqueries? I think, yes. - Put my initial question another way, I think this is correct SQL: select ( select a from (select a from (select a from (select a from (select a from (select b as a) as T1 ) as T2 ) as T3 ) as T4 ) as T5 ) as column_1 from (select 1 as b) as T0 It is certainly weird, but it is correct. But SQLite generate error -- Best regards, Alexander mailto:[EMAIL PROTECTED]
Re: [sqlite] Probably, bug in SQLite (Or, what is wrong with this query?)
Cory Nelson wrote: > > Can anybody tell me, what is wrong with this simple query: > > > >select a, (select c > > from (select b + 1 as c) as Table2) as d > >from (select 1 as a, 2 as b) as Table1 > > I don't claim to be a master of SQL, but isn't "select b + 1 as c" its > own query, without access to the "2 as b" in the outer query? But this query is work well: select a, (select b + 1 as c) as d from (select 1 as a, 2 as b) as Table1 The problem is only with subqueries nested in another subquery. -- Best regards, Alexander mailto:[EMAIL PROTECTED]
[sqlite] Probably, bug in SQLite (Or, what is wrong with this query?)
Hello!
Can anybody tell me, what is wrong with this simple query:
select a, (select c
from (select b + 1 as c) as Table2) as d
from (select 1 as a, 2 as b) as Table1
The error arise at line 2 ("no such column: b").
AFAIK, the syntax is perfectly correct.
This error arise when query contains double-nested subquery
in its SELECT clause, and the inner subqery refers on columns
of the main query.
I think, this is bug in SQLite, but I'm not entirely sure,
because similar error arise in MS SQL Server 2000.
Any thought?
---
Below is (almost) real-life example of query with double-nested
subquery in SELECT clause, which refers on columns of the main
query. Consider this tables:
create table Products
(
name text primary key
);
create table Orders
(
order_date timestamp not null,
order_idintegernot null,
-- orders with different dates can have same id
primary key (order_date, order_id)
);
create table OrderDetails
(
order_datetimestamp,
order_id integer,
product_name text,
amountinteger not null,
primary key (order_date, order_id, product_name),
foreign key (order_date, order_id)
references Orders (order_date, order_id),
foreign key (product_name)
references Products (name)
);
The question is: "for each pair of products calculate number of orders
where this two products has been shipped together" (I'm sorry if my
English is not correct). One of possible solutions is:
select X, Y, (select count(*)
from (select order_date, order_id
from OrderDetails where product_name = X
intersect
select order_date, order_id
from OrderDetails where product_name = Y) as Table2
) as Z
from (select P1.name as X, P1.name as Y
from Products P1, Products P2
where P1.name < P2.name) as Table1
This query does not work, because SQLite complaints on comparisons
"product_name = X" and "product_name = Y".
I solve this problem by rewriting of the original query as:
select D1.product_name, D2.product_name, count(*)
from OrderDetails D1 inner join OrderDetails D2
using (order_date, order_id)
where D1.product_name < D2.product_name
group by D1.product_name, D2.product_name
But (IMHO) original query is also correct. Is I'm mistaken?
--
Best regards,
Alexandermailto:[EMAIL PROTECTED]
Re: [sqlite] proposal for improving concurrency in SQLite
Hello! I think, there is another way for high-concurrency SQLite-based systems. **It is not entirely universal**, but I hope it may be used for high-traffic web sites and similar kind of systems, where each individual transaction (such as a page retrieval or a form submission) is very short and can be replayed automatically in the case of transaction failure. The main idea is in additional abstraction layer on the top of SQLite. This layer is high level transaction management system. Each high-level optimistic transaction is (almost) fully ACID, but many high-level transactions executes in the scope of single low-level SQLite transaction. This way the count of file buffers flushing is decreased significantly. Below is very simplified example. Let imagine high-level transaction: BEGIN SELECT ... -- potentially slow processing occurred here SELECT ... -- potentially slow processing occurred here INSERT ... UPDATE ... DELETE ... COMMIT This transaction in many cases can be split into two independent transactions: 1. Read-only transaction 2. Read-write optimistic transaction with conflict detection BEGIN SELECT ... -- potentially slow processing occurred here SELECT ... -- potentially slow processing occurred here COMMIT BEGIN SELECT ... -- additional short SELECTs for conflict detection -- ROLLBACK if some conflict was detected INSERT ... UPDATE ... DELETE ... COMMIT Because the first transaction is read-only, it may be performed in parallel with similar transactions. That is, no locking conflicts is occurred on this phase. Because the second transaction contains optimistic conflict detection, it can be combined with many similar transactions into one low-level SQLite transaction. This way, the count of file buffers flushing is reduced greatly. This transaction will be relatively short because no application-specific slow processing take place on that phase. The main trick is how to split the original transaction automatically. It may be very hard if original transaction is expressed directly in SQL. I hope, such transformation can be performed much more easily if operations are expressed in terms of some object-relational mapping system. Such ORM can begin read-only SQLite transaction and then delay all the modifications till the end of high-level transaction. If application code will try execute direct SQL data modification query, then the read-only transaction will degrade smoothly to unbatched read-write transaction. On typical web-site the majority of all transactions is read-only. If the web site must perform 1000 read-only transactions per second and 20 read-write transactions per second, then this 20 read-write transactions can be slightly delayed and then processed in the batch as single low-level SQLite transaction. But what if optimistic locking conflict is detected in one of this 20 transactions? Does it means the others 19 high-level transaction must be rolled back, as well as low-level SQLite transaction? No, it is not. Only transaction with conflicts will be excluded from low-level SQLite transaction, and then the entire process of this HTTP request will be reiterated by the web-server. In the near future I'll try to implement SQLite-oriented ORM which will demonstrate this approach. Best regards, Alexandermailto:[EMAIL PROTECTED]
Re: [sqlite] Error: database or disk is full
Anton Kuznetsov wrote: > I use SQLite 3.2.8. > > When comitting a transaction I get an error "database or disk is full". But > there is 6 Gb of free space on my hard drive. Database is about 4 Gb. > There is no error when doing the same inserts but without transaction. > > Why does it happen? Do you have write permission on the directory containing the database file? In process of transaction SQLite creates additional file (transaction log) in this directory. Best regards, Alexandermailto:[EMAIL PROTECTED]
[sqlite] Can I have access to in-memory DB from different threads?
Hi, all! Can I have access to in-memory DB from two different threads? Best regards, Alexander mailto:[EMAIL PROTECTED]
Re: [sqlite] Usability problem with quoted names
D. Richard Hipp wrote: > The rule in SQLite is > that a column name (or table name) can be any sequence of > characters that does not include US-ASCII punctuation or > control characters and does not begin with a digit. It is cool! I have not seen this in SQLite documentation > Note also that if you names do include punctuation, then you > can enclose them in [...] instead of "..." and they will > never be mistaken for literals. Yes, it solves the problem completely. Thanks for response! Best regards, Alexandermailto:[EMAIL PROTECTED]
Re: [sqlite] Usability problem with quoted names
Teg wrote: > Why not use "?" then fill it in the the actual value later? Quoting > and having to actually look at values to make sure they were legal > gave me no end of problems now I: > ... > I never pass any kind of quoted values any more. I'm sorry, my previous post is quite obscure. This is because my English is not very good. Now I try to explain it in more clear terms: 1. SQLite has a feature of double-quoted column names. It is good feature and is part of SQL standard. 2. Double-quoting allows columns with internationalized names. I teach SQL-92 to russian beginner students, and use SQLite in this course. Many students like gives russian names to columns, and such columns must be double-quoted. 3. If student misprint non-English column name in complex query, SQLite don't report about error, but treat this misprinted name as string literal. This SQLite behaviour can introduce very subtle bugs, if error happens in subquery or aggregate function. 4. This is because SQLite allows two absolutely different meaning of double-quoted strings. It may be column name OR string literal. I think this is misfeature. 5. Good system must have brittle behaviour in respect of programmer errors. If program (or SQL query) contains error, system must report about this error as soon as possible. Instead, SQLite current behaviour hide this class of errors, and introduce bug. 6. I propose deprecation of double-quoted string literal in next SQLite version, and then (after some deprecation period) completely remove this misfeature. Best regards, Alexandermailto:[EMAIL PROTECTED]
[sqlite] Usability problem with quoted names
Hi! If I use "some double-quoted stuff" in my query, this one can have two absolutely different meanings. Usually it is column name, but if I wrote this string with error, it silently converts into string literal. I think, this silent behaviour is not very good. If it happens inside deeply nested subquery or inside aggregate function (e.g. having count(distinct "cusotmer_id") = 1 ) the query still works, but produces incorrect result. IMHO, it is better if double quoted string always mean "column name", and single quoted string stand for 'literal value' Best regards, Alexander mailto:[EMAIL PROTECTED]
Re: [sqlite] qmark style updates
The second parameter of cursor.execute() accept **sequence** of
bindings. Try this:
c.execute(toDo, [s1])
> from pysqlite2 import dbapi2 as sqlite
>
> con = sqlite.connect("mydb.db")
> c = con.cursor()
>
> s1 =3
> toDo ="Update ex set amount = ? where ex_id = 1"
> c.execute(toDo,s1)
> con.commit()
> c.close()
>
>
> Can anyone help?
Best regards,
Alexandermailto:[EMAIL PROTECTED]
Re[2]: [sqlite] Unsupported SQL feature
> > select * from t1 where a >= ALL (select b from t2)
>
> What are these constructs suppose to do?
"""
QUANTIFIED SUBQUERIES
A quantified subquery allows several types of tests and
can use the full set of comparison operators. It has
the following general format:
value-1 {=|>|<|>=|<=|<>} {ALL|ANY|SOME} (query-1)
The comparison operator specifies how to compare value-1
to the single query column value from each subquery
result row. The ANY, ALL, SOME specifiers give the type
of match expected. ANY and SOME must match at least one row
in the subquery. ALL must match all rows in the subquery,
or the subquery must be empty (produce no rows).
"""
The next two WHERE condition are equivalent:
total_price > ALL (SELECT total_price FROM items
WHERE order_num = 1023)
total_price > (SELECT MAX(total_price) FROM items
WHERE order_num = 1023)
The next two WHERE condition are equivalent also:
total_price > ANY (SELECT total_price FROM items
WHERE order_num = 1023)
total_price > (SELECT MIN(total_price) FROM items
WHERE order_num = 1023)
ANY and SOME are synonyms.
The next example show real use-case.
Table: Assemblies
++--+--+
| AssemblyName | PartNumber | PartWeight |
++--+--+
| Assembly1 | 01 | 100 |
| Assembly1 | 02 | 150 |
| Assembly2 | 01 | 120 |
| ... | ... | ... |
Query: Assemblies with max total weight:
Because SQL doesn't allow nested aggregated functions
MAX(SUM(...)), it is easy for some people
write this query as:
select AssemblyName from Assemblies
group by AssemblyName
having SUM(PartWeight) >= ALL (
select SUM(PartWeight)
from T1
group by AssemblyName
)
Of course, this query may be reformulated as:
select TOP 1 AssemblyName from Assemblies
group by AssemblyName
order by SUM(PartWeight) DESC
> x all (select y from t where ...)
>
> is equivalent to
>
> not exists (select y from t where not (x y) and ...)
>
> Any and some are synonyms.
>
> x any (select y from t where ...)
>
> is equivalent to
>
> exists (select y from t where x y and ...)
Nice summary
Best regards,
Alexandermailto:[EMAIL PROTECTED]
[sqlite] Unsupported SQL feature
Hi! I just notice, SQLite don't support this type of queries: select * from t1 where a >= ALL (select b from t2) Of course, queries with ALL, ANY and SOME clauses may be reformulated, but this is part of SQL standard. I use SQLite for teach SQL to students, and will be very happy if such feature appears in next version of SQLite Best regards, Alexander mailto:[EMAIL PROTECTED]
