[sqlite] Partial Indexes and use of LIKE
Hello all, The documentation for partial indexes (section 3.2) lists "=, <, >, <=, >=, <>, or IN" as operators that will trigger the use of an index WHERE c IS NOT NULL. It seems to me that LIKE should also be in that list, given that it will also only match when c is not null. I can force the query planner to say it will use the index by querying as such: WHERE col IS NOT NULL and col LIKE 'expr%' But am I correct in thinking that SQLite is still scanning though the table to evaluate expr%? If so, then am I also correct in thinking that if I want LIKE to use an index it has to be a full index? Regards, Mark -- Mark Lawrence ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bus Error on OpenBSD
On Fri Oct 28, 2016 at 05:48:48PM +0700, Dan Kennedy wrote:
> On 10/28/2016 05:39 PM, no...@null.net wrote:
> > Hi Rowan,
> >
> > On Fri Oct 28, 2016 at 06:19:59PM +0800, Rowan Worth wrote:
> > > Every sqlite_stmt you use *must* be finalized via sqlite3_finalize.
> > > I'm not exactly sure what that looks like from the other side of DBD,
> > > but I would be checking your perl code for a statement/resultset
> > > object which outlives the database connection itself.
> > >
> > Some of my new debug statements appear to confirm that: database
> > handles are being cleaned up before statement handles, even though
> > presumably the statement handle still has a reference back to the
> > database.
>
> SQLite should handle that. If you call sqlite3_close() before all statement
> handles have been cleaned up, the call fails with SQLITE_MISUSE. Or if you
> use sqlite3_close_v2(), the call succeeds, but a reference count is used to
> ensure that the db handle object is not actually deleted until all
> statements are. close_v2() was added for this situation - where a garbage
> collectors or similar is responsible for closing db handles and finalizing
> statements.
The "handles" I was referring to above were Perl DBI handles. No doubt
they contain a real SQLite handle somewhere, but I don't think it is
safe to assume a one-to-one mapping. For example, the following appears
to create two Perl objects for each of the $db and $sth variables:
use DBI;
sub DBI::db::DESTROY {
warn "DESTROY @_";
}
sub DBI::st::DESTROY {
warn "DESTROY @_";
}
my $db = DBI->connect('dbi:SQLite:dbname=:memory:');
my $sth = $db->prepare('select 1');
# DESTROY DBI::db=HASH(0x9acf68c) at x line 4.
# DESTROY DBI::st=HASH(0x9acf95c) at x line 8.
# DESTROY DBI::st=HASH(0x9acf754) at x line 8.
# DBI st handle 0x9acf95c has uncleared implementors data.
# dbih_clearcom (sth 0x9acf95c, com 0x9ad1518, imp
DBD::SQLite::st):
# FLAGS 0x100113: COMSET IMPSET Warn PrintError
PrintWarn
# PARENT DBI::db=HASH(0x9acf600)
# KIDS 0 (0 Active)
# NUM_OF_FIELDS 1
# NUM_OF_PARAMS 0
# DESTROY DBI::db=HASH(0x9acf600) at x line 4.
# DBI db handle 0x9acf68c has uncleared implementors data.
# dbih_clearcom (dbh 0x9acf68c, com 0x9ac1fd8, imp
DBD::SQLite::db):
# FLAGS 0x100317: COMSET IMPSET Active Warn PrintError
#PrintWarn AutoCommit
# PARENT DBI::dr=HASH(0x9acf1a0)
# KIDS 0 (0 Active)
#
It is not obvious to me why that is so and I don't know the DBD::SQLite
code base so I won't speculate.
> If this is repeatable, try running it under valgrind. The valgrind
> error should make it pretty clear whether or not the statement handle
> really has already been finalized.
Well I have found what is probably the original source of the error: I
was keeping (Perl) statement handles around after the database handles
had expired. That doesn't mean that there isn't an issue with
how DBD::SQLite is using SQLite, but I no longer have the motivation to
track down that error when the easy answer to my problem is "don't do
that." Plus I have another error to report in a new thread :-(
Thanks everyone for listening.
--
Mark Lawrence
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Spelling error on current https://sqlite.org/csv.html
Search for "scheam=" to find it. -- Mark Lawrence ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] design problem involving trigger
On Mon Aug 24, 2015 at 08:46:57AM +0200, Mark Lawrence wrote: > > You can achieve this using a partial index[1] on the Books.title > column, which is used only when the author is null. A test script to My apologies. It appears from the mailing list archive this was already mentioned, but I didn't see those messages in my inbox. -- Mark Lawrence
[sqlite] design problem involving trigger
> It's theoretically possible, but in that case I would be content to > force a difference in the title. It should be possible to have the > following: > > 'History of Scotland' | -> 'A. Jones' > 'History of Scotland' | -> 'T. Smith' > 'Manual of DOS' | NULL > > But, an attempt to insert another record 'Manual of DOS' with a NULL > author should fail. You can achieve this using a partial index[1] on the Books.title column, which is used only when the author is null. A test script to demonstrate: create table Books( id integer primary key, title text collate nocase not null, author references Authors(id), unique(title, author) ); create table Authors( id integer primary key, name text unique not null check(name <> '') ); -- ** create unique index no_author_index on Books(title) where author is null; -- ** insert into Authors values(1,'A. Jones'); insert into Authors values(2,'T. Smith'); insert into Books values(1, 'History of Scotland', 1); insert into Books values(2, 'History of Scotland', 2); insert into Books values(3, 'Manual of DOS', NULL); select b.title, a.name from Books b left join Authors a on a.id = b.author ; -- titlename -- --- -- -- History of Scotland A. Jones -- History of Scotland T. Smith -- Manual of DOSNULL insert into Books values(4, 'Manual of DOS', NULL); -- Error: near line 37: UNIQUE constraint failed: Books.title [1] https://www.sqlite.org/partialindex.html Mark -- Mark Lawrence
[sqlite] Double scan of table in WITH clause?
Given the following example table and data: create table x(id integer); with recursive src(id) as (select 1 union all select id + 1 from src where id < 5) insert into x(id) select id from src ; My goal is to select each row from table x, and the sum total, in the same query. The following returns the results correctly as expected: with x_all as (select id from x), x_sum as (select sum(id) from x_all) select * from x_all union all select * from x_sum ; /* id -- 1 2 3 4 5 15 */ However when doing an explain query plan I see that the source "x" table is scanned twice where I would have expected it to only be scanned once: selectid,order,from,detail 1,0,0,"SCAN TABLE x" 2,0,0,"SCAN TABLE x" 0,0,0,"COMPOUND SUBQUERIES 1 AND 2 (UNION ALL)" Is this expected? For my real query the x_all data is relatively complicated with lots of joins, and I was wondering if it is absolutely necessary for SQLite to be doing double the amount of work it needs to? Mark. -- Mark Lawrence
[sqlite] Contstant WHERE terms still require table scan?
I'm wondering if it would be possible to optimize the query planner for situations where one of the WHERE clauses is a constant that evaluates to false? CREATE TABLE x( id INTEGER PRIMARY KEY ); EXPLAIN QUERY PLAN SELECT x.id FROM x WHERE 1=0 ; -- selectidorder fromdetail -- -- -- -- -- 0 0 0 SCAN TABLE x This would potentially allow me to shortcut some largish UNION statements. Mark. -- Mark Lawrence Home: +41 44 520 12 59 Mobile: +41 76 796 65 68
Re: [sqlite] [SQL Query] Top 10 of 5 different groups sorted by length
On Tue Oct 14, 2014 at 09:25:20AM +0200, RSmith wrote: > > > >To get even more compact, I would go with Igor's SQL which is > >quite succint, but if those tables are big, that query will run > >quite a bit slower - which is only a problem if the speed really > >matters. I'm a little curious about why you say a CTE statement is slower than a VIEW for large tables. I don't have large tables to test on but I get the same query plan for both versions on small test tables. What changes with size? -- Mark Lawrence ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] decoding a bitmask
My apologies for the previous completely wrong mesage. I got mixed up with operator meaning & precedence... On Mon Oct 13, 2014 at 02:39:40PM +0100, Paul Sanderson wrote: > > My actual code is as folows > > (CASE visits.transition & 0xFF00 WHEN 0x0080 THEN 'Blocked' > ELSE '' END || But I can at least put some better light on your issue. SQLite returns different results for your style of case/when construct: CASE expr WHEN val THEN... and the alternative which I find reads easier: CASE WHEN truth_expr THEN... Here's a demonstration: WITH x AS ( SELECT 0x01 AS a, '0x01' AS txt UNION ALL SELECT 0x10, '0x10' UNION ALL SELECT 0x01 | 0x10, '0x01 | 0x10' ) SELECT x.txt, CASE WHEN x.a & 0x01 THEN 'A' ELSE '' END || ' ' || CASE WHEN x.a & 0x10 THEN 'B' ELSE '' END AS result1, CASE x.a & 0xff WHEN 0x01 THEN 'A' ELSE '' END || ' ' || CASE x.a & 0xff WHEN 0x10 THEN 'B' ELSE '' END AS result2 FROM x ; And the results: txt result1 result2 -- -- -- 0x01A A 0x10 B B 0x01 | 0x1 A B -- MarkeLawrence ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] decoding a bitmask
On Mon Oct 13, 2014 at 04:51:16PM +0200, Mark Lawrence wrote: > On Mon Oct 13, 2014 at 02:39:40PM +0100, Paul Sanderson wrote: > > Perl equivalent: > > use feature 'say'; > my $a = 0x0080 | 0x0800; > > say $a & 0x0080; > say $a & 0x0800; > say $a & 0x0800 & 0x0800; Sorry, my comment was invalid. The above is incorrect - I mixed up the 8's and the 0's. -- Mark Lawrence ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] decoding a bitmask
On Mon Oct 13, 2014 at 02:39:40PM +0100, Paul Sanderson wrote: > > The query is on a visits table from a google chrome history database. The > query seems to work OK if a single bit is set, but fails (a blank string is > returned) when multiple bits are set. Any ideas why? I suspect it is a bug with multiple bitwise ORs. Demonstration: WITH x AS ( SELECT 0x0080 | 0x0800 AS a ) SELECT a & 0x0080, a & 0x0800, a & 0x0080 & 0x0800 FROM x ; Result: a & 0x0080 a & 0x0800 a & 0x0080 & 0x0800 -- -- --- 8388608 134217728 0 Perl equivalent: use feature 'say'; my $a = 0x0080 | 0x0800; say $a & 0x0080; say $a & 0x0800; say $a & 0x0800 & 0x0800; Result: 8388608 134217728 134217728 -- Mark Lawrence ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Stored Procedures
On Thu Oct 09, 2014 at 05:38:57PM -0500, Nico Williams wrote: > I use triggers, particularly INSTEAD OF INSERT triggers on VIEWs. > The values of the columns of the rows to be "inserted" are the > "stored procedure's" arguments. I would like to able to do this too, but INSTEAD OF INSERT on a view does not support default values for arguments the same way that BEFORE INSERT on a regular table does. What would really be nice is if one could run the following inside a BEFORE or INSTEAD OF trigger: UPDATE NEW SET NEW.name = COALESCE(NEW.name, new_value) ; Could the SQLite team perhaps comment on how difficult this would be to implement? -- Mark Lawrence ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Stored Procedures
On Thu Oct 09, 2014 at 11:29:49AM -0700, J Decker wrote: > they can be implemented through registered extensions..(well no probably > not how you're thinking).. > > but apparently can't add syntax like 'EXEC" ... but could make them be > like "select * from (stored_proc)" as an alias for "exec (stored proc)" I use BEFORE INSERT triggers that end with a SELECT RAISE(IGNORE). That way I can use the following syntax to perform multiple operations within a single statement: INSERT INTO my_function_name( arg1, arg2, arg3 ) VALUES ( val1, val2, val3 ) Obviously you can't get a result from that, but it would of course be possible to let the insert succeed or to insert a "result row" somewhere. Unfortunately CTEs don't work inside triggers so complicated logic is somewhat limited and/or must be spread across multiple triggers. > For lack of portability I haven't used them; what's a good use case > for stored procedures? I generally use them for storing entities that must be entered in many tables, where it nicely presents an API to the caller that closely maps to the single action they want to take. This provides: Simplicity: a single statement can replace many individual statements, putting more of your application inside the database and can often remove the need for an explicit transaction. Efficiency: for non-C languages there is less translation between the language/SQLite boundary. -- Mark Lawrence ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Long lived prepared statements
> > By the way, the last call to fetchrow_array() (that returns > > 'undef') implicitly calls finish() internally. > I assume this is the same finish of the handle obtained from my > prepare. But because I have a reference to the handle holed away it > is not really "finished" and that is why I am able to continue to use > the prepared statement? Is this assumption correct? I suspect you are mixing a couple of different concepts together, although I can only what they are exactly. As the DBI documentation mentions, the finish() method should more accurately be called discard_pending_rows(). It does not actually do very much - more a way of saying "I don't need any more information from this handle." That method is unrelated to Perl's reference counting which keeps track of objects and their memory, and which you usually don't need to worry about. If you keep a reference to the statement handle somewhere then you can run execute() on it again, and when all the references are gone Perl will do what it needs to do memory wise which *may* involve calls to the underlying SQLite C API as needed. -- Mark Lawrence ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Long lived prepared statements
> I think I know the answer to this so I'm looking for some > conformation. What I do is to prepare a relatively large and complex > query. Then I run the same query forever never doing a finalize. My > assumption is I will have no memory leakage. In principle this should be fine, and if it isn't then it would be a bug somewhere, either in the SQLite code (very unlikely) or the DBD::SQLite driver for Perl's DBI interface (also unlikely). Simon wrote: > When you are finished with the query you should either end on a > _reset() or do a _finalize(). Or (harmlessly) do both. If you do > not do one of those, you may find that when you _close() some memory > is not released for the statement and/or the database (I'm not sure > which, but either way it's bad). On the Perl/DBI side of things these actions are usually be taken care of automatically when the associated objects holding the relevant resources go out of scope. On Sat Oct 04, 2014 at 03:16:23PM -0700, Jim Dodgen wrote: > It might be I need more of a Perl DBI question the order of the > statements I do are as follows > > prepare >> - Done once > > execute << done many times followed by: fetchrow_array << until > exhausted > > finish << never done > > I just don't see that the Execute/Fetchrow-array activity is going to > leave a handle or some resource dangling The above steps are exactly (but not exclusively) what the Perl DBI was intended to support. Although I haven't specifically measured the memory use, I do the above quite a lot without a problem. By the way, the last call to fetchrow_array() (that returns 'undef') implicitly calls finish() internally. -- Mark Lawrence ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Is there a simple command line data editor for sqlite3?
On Wed Oct 01, 2014 at 11:16:54AM +0100, c...@isbd.net wrote: > > > Thanks, that looks something like what I'm after. However what the > chances are of installing it successfully on a Beaglebone Black I > don't know! I'm currently installing it on my desktop machine to try > (quad core, 8Gb memory, loads of disk) and it appears to be installing > and compiling half of the world! On a lowly BBB with 512k memory and > only 4Gb disk in total I think it might be a bit slow. The Perl ecosystem is very modular and the default installation includes only very "core" modules. And often the number of dependencies for a Perl-based application has little to do with the run-time speed, due to the test-driven culture. > in a directory that contains sqlite3 databases it lists every file in > the hierarchy (why, there are thousands) and then fails with:- > > Ended searching > Available databases: > Can't locate object method "new" via package "JSON::XS" at > /usr/local/share/perl/5.18.2/App/DBBrowser/Opt.pm line 797. Looks like a dependency got missed. I would suggest installing the missing JSON::XS package and trying again: sudo cpan JSON::XS > It sounds as if db-browser could be useful but the user interface is a > little quirky! I agree that the interface seems a little quirky. I believe there is a way to specify a database file directly, but you'll have to read the documentation yourself to find out how. It is probbaly better that we stop this thread here as it is off-topic for the mailing list. But feel free to reply to me privately if you still have issues getting it to run. -- Mark Lawrence ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Is there a simple command line data editor for sqlite3?
On Wed Oct 01, 2014 at 09:50:16AM +0100, c...@isbd.net wrote: > > > Linux (xubuntu), > ... > in this case I need a command line utility. You may be interested in the db-browser *terminal application* available from CPAN: https://metacpan.org/pod/distribution/App-DBBrowser/bin/db-browser It can be installed as follows: sudo cpan App::DBBrowser After which the "db-browser" script should be in your path: db-browser -s $DIRECTORY_CONTAINING_SQLITE_DATABASE -- Mark Lawrence ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] DEFAULT expression ignored for INTEGER PRIMARY KEYs?
On Thu Sep 25, 2014 at 03:59:55PM -0400, Richard Hipp wrote: > > I will make an effort to clarify this in the documentation. If you are going to keep this behaviour would it not make more sense to ensure that the table creation fails? The DEFAULT clause is pretty straight-forward and I don't find it intuitive to go looking for PRIMARY KEY documentation when it is ignored. SQLite should either fail to accept the statement or do what the table definition says - anything else means heartache for those debugging problems (which I did for hours on this issue) and those reading the code afterwards. -- Mark Lawrence ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] DEFAULT expression ignored for INTEGER PRIMARY KEYs?
On Thu Sep 25, 2014 at 03:18:04PM -0400, Adam Devita wrote: > Your table definition seems to have a contradiction. The expression > INTEGER PRIMARY KEY is a special keyword that means 'auto-increment', > which would be a default value. I understand that that behaviour exists and applies when an insert does not provide a value, but I don't see the contradiction. The table defines an *explicit* default that should (to my mind) override any kind of magical-in-the-absence-of-a-default-default. Such an explicit default should certainly not be accepted if it is going to be ignored. -- Mark Lawrence ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] DEFAULT expression ignored for INTEGER PRIMARY KEYs?
Plan:
CREATE TABLE x(
id INTEGER PRIMARY KEY DEFAULT (random()),
val VARCHAR
);
INSERT INTO x(val) VALUES ('a');
SELECT * FROM x;
Result:
id val
-- --
1 a
Expected result:
id val
--- --
4841191733402647298 a
I get the expected result if I create the table WITHOUT ROWID.
--
Mark Lawrence
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] GROUP BY: ambiguous column name?
On Thu Sep 25, 2014 at 01:43:20PM -0500, Cory Nelson wrote: > GROUP BY works on your input data, not output data. You want to GROUP > BY COALESCE(x.id, y.id) That describes the behaviour I demonstrated, but not the reasoning behind it nor the documentation pointing to that reasoning. Is SQLite clever enough to recognize that a GROUP BY expression and a SELECT column are the same? Because in my mind I think of the query as working in the following stages for the most efficient operation: - JOIN ROWS - SELECT COLUMNS -- COALESCE done here - GROUP OUTPUT - ORDER OUTPUT However, it appears to be the case that the order is more like this: - JOIN ROWS - GROUP ROWS -- COALESCE done here - SELECT COLUMNS -- COALESCE also done here? - ORDER OUTPUT Which looks to me like the expression would be calculated twice. Is SQLite smart enough to figure out that the columns are the same and only do it once? If SQLite is capable of determining that the same expression is used twice, why not just accept a SELECT expression? -- Mark Lawrence ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] GROUP BY: ambiguous column name?
On Thu Sep 25, 2014 at 08:32:29PM +0200, Mark Lawrence wrote: > GROUP BY on a result column fails with "ambiguous column name": > > SELECT > COALESCE(x.id, y.id) AS id > FROM > y > LEFT JOIN > x > ON > x.id = y.fk > ORDER BY > id > ; Sorry, that should read GROUP BY of course. -- Mark Lawrence ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] GROUP BY: ambiguous column name?
Don't know if this is a bug or intended behaviour. Given the following schema: CREATE TABLE x( id INTEGER ); CREATE TABLE y( id INTEGER, fk INTEGER REFERENCES x(id) ); ORDER BY on a result column name is allowed: SELECT COALESCE(x.id, y.id) AS id FROM y LEFT JOIN x ON x.id = y.fk ORDER BY id ; GROUP BY on a result column fails with "ambiguous column name": SELECT COALESCE(x.id, y.id) AS id FROM y LEFT JOIN x ON x.id = y.fk ORDER BY id ; I would have expected the group to work the same as the order, given that I think of the group as happening on the result set before any joins. The syntax diagrams on the web page show the first as an "ordering-term" and the second as an "expr" which doesn't enlighten me much. -- Mark Lawrence ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SET (x,y) = (x1,y1)?
On Mon Sep 15, 2014 at 10:51:04AM +0200, Mark Lawrence wrote: > > Normally one could use a CTE to do the work once: > > WITH > cte > AS ( > SELECT 1 AS x, 2 AS y > ) > UPDATE > t > SET > x = cte.x, > y = cte.y > ; Actually this doesn't appear to work. I assumed it would based on the documentation which says: ...common table expressions (ordinary and recursive) are created by prepending a WITH clause in front of a SELECT, INSERT, DELETE, or UPDATE statement. Unfortunately there are no examples given for how a CTE works with an UPDATE. So I tried accessing the cte using subqueries which is perhaps how it is intended: WITH cte AS ( SELECT 1 AS x, 2 AS y ) UPDATE t SET x = (SELECT x FROM cte), y = (SELECT y FROM cte) ; Maybe the documentation could be improved with a couple of UPDATE/INSERT/DELETE examples? > However CTEs don't work within triggers. This is still my issue of course. -- Mark Lawrence ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SET (x,y) = (x1,y1)?
I occasionally have the need to update two columns based on complex sub queries, which are often very similar UPDATE t SET x = ( SELECT 1...), y = ( SELECT 2...)-- nearly the same as SELECT 1 ; Normally one could use a CTE to do the work once: WITH cte AS ( SELECT 1 AS x, 2 AS y ) UPDATE t SET x = cte.x, y = cte.y ; However CTEs don't work within triggers. I was wondering hard it would be to support the SET syntax as shown in the subject line. I believe something like that works in PostgreSQL and I could use it in SQLite for performance reasons. UPDATE t SET (x,y) = (SELECT 1,2) ; Alternatively, is there any effort underway to make CTEs work inside triggers? -- Mark Lawrence ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Expected behaviour of COUNT with no GROUP BY?
On Mon Aug 04, 2014 at 06:04:53PM +0200, RSmith wrote: > CREATE TABLE x(id INTEGER); > > SELECT Count(*) FROM x; > | 0 | > > --vs.-- > SELECT Count(*) FROM x GROUP BY id; > (No Results) > > > Paints a clear picture I hope! Yes it does, thanks. I guess I still find the combination of COUNT without a GROUP BY to be unintuitive, but at least I know why now. -- Mark Lawrence ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Expected behaviour of COUNT with no GROUP BY?
I try to remember to define GROUP BY values when using aggregate functions (and I wish SQLite considered it an error otherwise) but I forget once and the result surprised me. CREATE TABLE x( id INTEGER ); CREATE TABLE y( id INTEGER ); SELECT x.id, count(y.id) FROM x INNER JOIN y ON y.id = x.id ORDER BY x.id ; Result: id count(y.id) -- --- NULL0 It is expected behaviour that a row is returned in this situation even through the tables are empty? Adding a "GROUP BY x.id" returned the expected empty set. -- Mark Lawrence ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] new support for virtual tables written in Perl
Hi Laurent, > This is to announce that next version of DBD::SQLite (the Perl > driver for accessing SQLite databases) will include support for > virtual tables written in Perl. Thanks for the effort you put into this. I'm glad to see DBD::SQLite continuing to improve, although I already consider it one of the best interfaces to SQLite from a scripting language. > * PerlData This I could have used about a year ago. In one of my Perl programs I dynamically generate a large list of values which I then need to join with rows in SQLite. Currently I create a temporary table and insert each value as it appears. The PerlData virtual table would instead let me keep them in an @array variable to be used in a single, simple, SQL statement at the end. This would dramatically reduce the number calls I make into SQLite. -- Mark Lawrence ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] I want to use CREATE TABLE ... AS SELECT ... - but what if the SELECT returns no rows?
On Thu Jul 10, 2014 at 01:52:00PM +0100, Tim Streater wrote:
>
> What I'm actually doing is moving/copying a row from one database to
> another, where the two databases have identical schemas.
> ...
>
> So there are two copy steps. What I'd like to do is:
>
> ...
>
> but unfortunately there could easily be a conflict in the absid
> values, so I have to be able to set it to null to get a new value
> generated.
You were almost there. You can do it in one go if you specify the
columns exactly:
INSERT INTO
dst.messages(
col1,
col2,
col3
)
SELECT
col1,
col2,
col3
FROM
main.messages
WHERE
absid = some_value
;
And then retrieve the last rowid:
SELECT last_insert_rowid();
Mark.
--
Mark Lawrence
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] transactions do not respect delete
> It would appear the DELETE was successful, and the first INSERT was > successful. But when the second INSERT failed (as it was intended to)..it > did not ROLLBACK the database. Even though the second INSERT fails, your script still calls COMMIT on an open transaction in which the DELETE and first INSERT have succeeded. Typically an application would explicitly call ROLLBACK after a statement failure if it didn't want the transaction to commit. The following for example works the way you probably want it to. CREATE TABLE A(id INT PRIMARY KEY, val TEXT); INSERT INTO A VALUES(1, "hello"); BEGIN; DELETE FROM A; INSERT INTO A VALUES(1, "goodbye"); INSERT INTO A VALUES(1, "world"); ROLLBACK; SELECT * FROM A; It appears your expectation is that if a statement fails then the transaction is invalid (thereby ignoring the COMMIT). SQLite treats that situation differently. -- Mark Lawrence ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Mailing List
> On 2014年5月4日 GMT+08:00PM6:30:12, Del1 wrote: > > > >can you remove me from the mailing list? On Sun May 04, 2014 at 07:30:55PM +0800, Woody Wu wrote: > No I cannot. Assuming that you weren't answering the query as if it was personally directed at you (although I wouldn't know why you would think that to be the case) it doesn't hurt to be a little less abrupt and a little more helpful to those less experienced than you. Derek, Quite often mailing list managers add a set of List-* headers to outgoing mails, one of which indicates where or how to unsubscribe. For this list it shows: List-Unsubscribe: <http://sqlite.org:8080/cgi-bin/mailman/options/sqlite-users>, <mailto:sqlite-users-requ...@sqlite.org?subject=unsubscribe> In this case a mail to sqlite-users-requ...@sqlite.org with the subject "unsubscribe" should get you where you want. -- Mark Lawrence ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [Wishlist] Make RAISE function accept derived arguments
On Thu Apr 24, 2014 at 09:42:20AM +0200, Mark Lawrence wrote: > In triggers I often want to include information when raising an error. > However, RAISE doesn't accept dynamic/derived arguments which would > otherwise be valid in a SELECT. > > SELECT RAISE(ABORT, 'string1' || 'string2'); > -- Error: near "'string1'": syntax error I forgot to mention my real use case: using NEW.column / OLD.column values in the exception message. Neither of those work either (syntax error near "."). -- Mark Lawrence ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] [Wishlist] Make RAISE function accept derived arguments
In triggers I often want to include information when raising an error. However, RAISE doesn't accept dynamic/derived arguments which would otherwise be valid in a SELECT. SELECT RAISE(ABORT, 'string1' || 'string2'); -- Error: near "'string1'": syntax error Does anyone know of a work-around? -- Mark Lawrence ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Strange UNION ALL / ORDER BY behaviour
> The ticket is http://www.sqlite.org/src/info/8c63ff0eca > > The problem is that in the virtual machine that SQLite uses, a pair > of concurrent co-routines (one for each of the two SELECTs in the > UNION ALL) are both trying to use the same temporary register at the > same time. Bummer. Yep, bummer, but thanks for the quick confirmation. At least from my point of view I can now mark this issue as "someone else's problem." The actual query that broke for me was a UNION ALL with 7 SELECT statements, and I'm not really looking forward to having to run and sort those independently client-side. If you manage to determine some kind of work-around for the condition occuring I'd appreciate hearing about it, although potentially 7 co-routines hitting the same register is 7 times harder to avoid... -- Mark Lawrence ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Strange UNION ALL / ORDER BY behaviour
On Tue Feb 25, 2014 at 05:24:55PM +0100, Mark Lawrence wrote:
> On Tue Feb 25, 2014 at 09:37:41AM -0500, Richard Hipp wrote:
> > Can you please send the database schema, and possibly some test data?
>
> Attached is an SQL file containing enough to reproduce the issue on my
> system:
Righto, now included *inline* is the following:
* Schema for the tables
* Rows for the tables
* 4 queries demonstrating the issue:
1. Single SELECT query #1
2. Single SELECT query #2
3. UNION ALL query of #1 and #2
4. UNION ALL query of #1 and #2 plus ORDER BY
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE updates (
id integer NOT NULL PRIMARY KEY,
uuid char(40) NOT NULL UNIQUE DEFAULT '',
parent_id integer,
itime integer,
mtime integer NOT NULL
DEFAULT (strftime('%s','now')),
mtimetz integer NOT NULL
DEFAULT (strftime('%s','now','localtime') - strftime('%s','now')),
path varchar,
author varchar(255) NOT NULL,
email varchar(255) NOT NULL,
lang varchar(8) NOT NULL DEFAULT 'en',
message text NOT NULL DEFAULT '',
prefix varchar COLLATE NOCASE,
FOREIGN KEY(parent_id) REFERENCES updates(id)
ON DELETE CASCADE
);
CREATE TABLE topics (
id integer NOT NULL PRIMARY KEY,
uuid char(40) NOT NULL UNIQUE DEFAULT '',
first_update_id INTEGER NOT NULL,
kind varchar NOT NULL,
ctime integer NOT NULL,
ctimetz integer NOT NULL,
mtime integer NOT NULL,
mtimetz integer NOT NULL,
lang varchar(8) NOT NULL DEFAULT 'en',
hash varchar,
num_updates integer,
FOREIGN KEY(first_update_id) REFERENCES updates(id) ON DELETE CASCADE
);
CREATE TABLE projects (
id integer NOT NULL PRIMARY KEY,
parent_id integer,
name varchar(40) NOT NULL,
title varchar(1024) NOT NULL,
path varchar collate nocase,
status_id integer NOT NULL DEFAULT -1,
hash varchar,
num_updates integer,
FOREIGN KEY(id) REFERENCES topics(id)
ON DELETE CASCADE
FOREIGN KEY(parent_id) REFERENCES projects(id)
ON DELETE CASCADE,
FOREIGN KEY(status_id,id) REFERENCES project_status(id,project_id)
DEFERRABLE INITIALLY DEFERRED
);
CREATE TABLE project_updates (
id integer NOT NULL PRIMARY KEY DEFAULT (nextval('update_order')),
update_id integer NOT NULL,
project_id integer NOT NULL,
new integer,
parent_id integer,
name varchar(40),
title varchar(1024),
status_id integer,
FOREIGN KEY(update_id) REFERENCES updates(id)
ON DELETE CASCADE,
FOREIGN KEY(project_id) REFERENCES projects(id)
ON DELETE CASCADE
FOREIGN KEY(status_id,project_id) REFERENCES project_status(id,project_id)
ON DELETE CASCADE
) WITHOUT ROWID;
CREATE TABLE project_status (
id integer NOT NULL PRIMARY KEY,
project_id integer NOT NULL,
status varchar(40) NOT NULL,
rank integer NOT NULL,
UNIQUE (project_id,status),
UNIQUE (id,project_id), -- projects references this
FOREIGN KEY (id) REFERENCES topics(id) ON DELETE CASCADE,
FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE
);
CREATE TABLE project_status_updates (
id integer NOT NULL PRIMARY KEY DEFAULT (nextval('update_order')),
update_id integer NOT NULL,
project_status_id integer NOT NULL,
new integer,
status varchar,
rank integer,
UNIQUE(update_id,project_status_id), -- one change per update
FOREIGN KEY(update_id) REFERENCES updates(id) ON DELETE CASCADE
FOREIGN KEY(project_status_id) REFERENCES project_status(id)
ON DELETE CASCADE
) WITHOUT ROWID;
INSERT INTO "updates"
VALUES(1,'055ec25ae2e4498fd4be328feda54bcf9e77700a',NULL,1392381391,1392381391,3600,'201404512363105','Mark
Lawrence','an@email.address','en','init /home/mark/src/bif','055ec');
INSERT INTO "updates"
VALUES(2,'70d3ddc37b4dafb3a91a4f3657df2f133abcc049',NULL,1392381392,1392381392,3600,'201404512363270','Mark
Lawrence','an@email.address','en','m','70d3d');
INSERT INTO "updates"
VALUES(3,'9b7ba714cca0e21fd1b305a44b08de3fac7d69f9',1,1392381392,1392381392,3600,'201404512363105/20140451236329b','Mark
Lawrence','an@email.address','en','new project 2 [x]','9b7ba');
INSERT INTO "updates"
VALUES(4,'d7b78fe7911ad7c36341a225f5597dcb9c2c6e77',NULL,1392381392,1392381312,3600,'2014045123512d7','Mark
Lawrence','an@email.address','en','init /home/mark/src/bif/hub
--bare','d7b78');
INSERT INTO "updates"
VALUES(5,'51ffb8e75a1d9ad92f102edfe5e644
Re: [sqlite] Strange UNION ALL / ORDER BY behaviour
On Tue Feb 25, 2014 at 09:37:41AM -0500, Richard Hipp wrote: > Can you please send the database schema, and possibly some test data? Attached is an SQL file containing enough to reproduce the issue on my system: * Schema for the tables * Rows for the tables * 4 queries demonstrating the issue: 1. Single SELECT query #1 2. Single SELECT query #2 3. UNION ALL query of #1 and #2 4. UNION ALL query of #1 and #2 plus ORDER BY Note that there are of course a bunch of other tables and triggers not included, but I think they are irrelevant given I can reproduce the issue with what is in the file. Mark. -- Mark Lawrence ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Strange UNION ALL / ORDER BY behaviour
I am seeing what I think may be buggy behaviour, using a recent sqlite packaged by debian: 3.8.3.1 2014-02-11 14:52:19 ea3317a4803d71d88183b29f1d3086f46d68a00e What I am seeing is too few rows returned from a UNION ALL query. I can break it down as follows. Query 1 on its own works fine, returning 2 rows: SELECT 'project' AS "kind", status.uuid as update_order FROM project_updates INNER JOIN topics AS projects ON projects.id = project_updates.project_id LEFT JOIN topics AS status ON status.id = project_updates.status_id WHERE project_updates.update_id = 2 ; Results 1: kind update_order - project NULL project d2e2b16d45d4a7e514da610cdc46cbcfec29431a Query 2 on its own works fine return 5 rows: SELECT 'project_status' AS "kind", 1 AS update_order FROM updates INNER JOIN project_status_updates ON project_status_updates.update_id = updates.id WHERE updates.id = 2 ; Results 2: kind update_order project_status1 project_status1 project_status1 project_status1 project_status1 If I "union all" those two queries together I get the expected 7 (2 + 5) rows. kind update_order - project NULL project d2e2b16d45d4a7e514da610cdc46cbcfec29431a project_status 1 project_status 1 project_status 1 project_status 1 project_status 1 However if I union all with an ORDER BY clause like so: SELECT 'project' AS "kind", status.uuid as update_order FROM project_updates INNER JOIN topics AS projects ON projects.id = project_updates.project_id LEFT JOIN topics AS status ON status.id = project_updates.status_id WHERE project_updates.update_id = 2 UNION ALL SELECT 'project_status' AS "kind", 1 AS update_order FROM updates INNER JOIN project_status_updates ON project_status_updates.update_id = updates.id WHERE updates.id = 2 ORDER BY update_order ; Then I get a rather surprising three rows: kind update_order - project NULL project_status 1 project d2e2b16d45d4a7e514da610cdc46cbcfec29431a As far as I understand union all, it should never return less than the sum of the individual queries. Any ideas? Mark. -- Mark Lawrence ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] PRAGMA reverse_unordered_selects and GROUP_CONCAT
Hi,
I'm hitting an issue with ordering and GROUP_CONCAT that seems to
depend on whether the reverse_unordered_selects pragma is enabled,
and/or non-aggregate terms are also selected.
reverse_unordered_select with non-agg result
--
ONNoBad
OFF NoGood
ONYes Good
OFF Yes Good
The attached file should show some details. SQLite version is 3.7.12.1
on Debian. Is this a known or expected behaviour?
Mark.
--
Mark Lawrence
CREATE TABLE t(
id integer
);
INSERT INTO t VALUES(1);
INSERT INTO t VALUES(2);
INSERT INTO t VALUES(3);
PRAGMA reverse_unordered_selects = ON;
SELECT
GROUP_CONCAT(ordered.id)
FROM
(SELECT
id
FROM
t
ORDER BY
id ASC
) AS ordered
;
PRAGMA reverse_unordered_selects = OFF;
SELECT
GROUP_CONCAT(ordered.id)
FROM
(SELECT
id
FROM
t
ORDER BY
id DESC
) AS ordered
;
PRAGMA reverse_unordered_selects = ON;
SELECT
GROUP_CONCAT(ordered.id), 1 AS x
FROM
(SELECT
id
FROM
t
ORDER BY
id ASC
) AS ordered
GROUP BY
x
;
PRAGMA reverse_unordered_selects = OFF;
SELECT
GROUP_CONCAT(ordered.id), 1 AS x
FROM
(SELECT
id
FROM
t
ORDER BY
id DESC
) AS ordered
GROUP BY
x
;
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
