[sqlite] Possible substr() optimization?

Since Igor's response I've been studying "The SQLite Query Planner" with great interest. Staffan On Thu, Jul 23, 2015 at 4:59 PM, Bernardo Sulzbach < mafagafogigante at gmail.com> wrote: > > That's not quite the same. BETWEEN is inclusive of both ends. ('b' > BETWEEN 'a' and 'b') is true. > >

[sqlite] Possible substr() optimization?

Thanks, I wasn't aware. On Thu, Jul 23, 2015 at 4:21 PM, Igor Tandetnik wrote: > On 7/23/2015 10:17 AM, Staffan Tylen wrote: > >> Please consider this: >> >> create table tbl1 (col1 text primary key); >> insert ... >> select * from tbl1 where substr(col1,1,1

[sqlite] Possible substr() optimization?

Please consider this: create table tbl1 (col1 text primary key); insert ... select * from tbl1 where substr(col1,1,1)='a'; select * from tbl1 where col1='a'; According to explain (I'm on 3.8.6), the first select above causes a scan of the entire table, while the second select uses the index. My

[sqlite] sqlite3 and Unicode

> > >>> I think it assumes UTF8 in both cases. >>> >> >> when using '.dump', it does not create a UTF8 file on Windows 7 (sQlite >> version 3.8.8.3) >> > > It's UTF16-LE data. > > > I must admit that I'm a bit confused here. If I'm not wrong UTF-8 differs from ascii when the value is higher than

[sqlite] Complex insert query to normalised database

I suspect that this is wrong as nobody has suggested it but isn't this what triggers are meant to solve? Staffan On Wed, Feb 18, 2015 at 2:13 PM, Simon Slavin wrote: > > On 18 Feb 2015, at 11:38am, Flakheart wrote: > > > I can deal with single table inserts but I think this would be some

Re: [sqlite] aggregate functions with DISTINCT

On Mon, Jan 12, 2015 at 3:25 PM, Clemens Ladisch wrote: > Testing shows that the DISTINCT > applies to the function, not to individual parameters, and that indeed > the set of all parameters is checked: > > This is to me the most

Re: [sqlite] aggregate functions with DISTINCT

, Clemens Ladisch <clem...@ladisch.de> wrote: > Staffan Tylen wrote: > > Well, the SELECT is actually over 400 lines long [...] I > > can't use SELECT DISTINCT X as that wouldn't give the result I want, and > I > > can't use SELECT DISTINCT 'ABC' either. So my

Re: [sqlite] aggregate functions with DISTINCT

Just to add: my questions were - why this limitation with DISTINCT, and can it be changed at least for GROUP_CONCAT? On Sun, Jan 11, 2015 at 1:23 AM, Staffan Tylen <staffan.ty...@gmail.com> wrote: > Well, the SELECT is actually over 400 lines long so 'visualizing' it > wouldn't

Re: [sqlite] aggregate functions with DISTINCT

e DISTINCT with the GROUP_CONCAT function, which in this case is invalid. Staffan On Sun, Jan 11, 2015 at 1:00 AM, John McKown <john.archie.mck...@gmail.com> wrote: > On Sat, Jan 10, 2015 at 5:56 PM, Staffan Tylen <staffan.ty...@gmail.com> > wrote: > > > Thanks R

Re: [sqlite] aggregate functions with DISTINCT

Hmm, maybe I'm wrong there, it would remove duplicates of the entire collection combined, wouldn't it? Yes, it might work. Staffan On Sun, Jan 11, 2015 at 12:56 AM, Staffan Tylen <staffan.ty...@gmail.com> wrote: > Thanks Richard but unfortunately I cannot use DISTINCT in that w

Re: [sqlite] aggregate functions with DISTINCT

Thanks Richard but unfortunately I cannot use DISTINCT in that way, since it affects all the selected columns and they are MANY. Staffan On Sun, Jan 11, 2015 at 12:43 AM, Richard Hipp <d...@sqlite.org> wrote: > On 1/10/15, Staffan Tylen <staffan.ty...@gmail.com> wrote: > >

[sqlite] aggregate functions with DISTINCT

The documentation for aggregate functions states that "In any aggregate function that takes a single argument, that argument can be preceded by the keyword DISTINCT". My questions are: Why is DISTINCT only allowed with a single argument in a function like GROUP_CONCAT? Can that limitation be

Re: [sqlite] Row filtering prior to aggregate function execution

Would this work? SELECT SUM(...),COUNT(...), ... FROM ... WHERE ... GROUP BY ... HAVING security(...) ... Staffan On Fri, Dec 19, 2014 at 7:47 AM, Hick Gunter wrote: > > SELECT ...,sum(...),count() FROM ... WHERE security(...) ... > > With a user defined function security().

Re: [sqlite] PRAGMA table_info incomplete

My apologies, I was accessing the wrong database :( Thanks. Staffan On Fri, Nov 28, 2014 at 12:29 PM, Clemens Ladisch <clem...@ladisch.de> wrote: > Staffan Tylen wrote: > > I've just found out that the column data returned by PRAGMA table_info > does > > not include c

[sqlite] PRAGMA table_info incomplete

I've just found out that the column data returned by PRAGMA table_info does not include columns added using ALTER COLUMN ADD COLUMN. I guess this is either a bug or the documentation is incomplete. If it's a bug I would welcome a fix. If on the other hand it's 'by design', what method is

Re: [sqlite] Clarification on sqlite handling of mixed aggregate and non-aggregate columns

But if several rows have the same a value as the max value then the b value will be arbitrary, or? Staffan On Wed, Oct 29, 2014 at 7:43 PM, Richard Hipp wrote: > On Wed, Oct 29, 2014 at 2:38 PM, Baruch Burstein > wrote: > > > Hi, > > > > If I have a

Re: [sqlite] An Explain Query Plan that I cannot explain

Thanks for the tip, ANALYZE is the cause. Without it the index will be searched. Staffan On Thu, Jul 17, 2014 at 7:38 PM, Simon Slavin <slav...@bigfraud.org> wrote: > > On 17 Jul 2014, at 6:34pm, Staffan Tylen <staffan.ty...@gmail.com> wrote: > > > I'm unable to un

[sqlite] An Explain Query Plan that I cannot explain

I've got a view VJU covering 8 tables T1-T8. They all share a column C1 which is indexed in each table: CREATE VIEW VJU AS SELECT * FROM T1 LEFT JOIN T2 USING (C1) LEFT JOIN T3 USING (C1) LEFT JOIN T4 USING (C1) LEFT JOIN T5 USING (C1) LEFT JOIN T6 USING (C1) LEFT JOIN T7 USING (C1) LEFT JOIN T8

Re: [sqlite] WITH syntax error

ents. Knowing this I'll use your "last option" as that seems to do what I'm looking for. Thanks. Staffan On Sat, Jul 12, 2014 at 8:26 PM, RSmith <rsm...@rsweb.co.za> wrote: > > On 2014/07/12 19:29, Staffan Tylen wrote: > >> The following statement i

Re: [sqlite] WITH syntax error

According to sqlite3 I'm on 3.8.3: SQLite version 3.8.3 2014-02-03 14:04:11 On Sat, Jul 12, 2014 at 8:06 PM, Dan Kennedy <danielk1...@gmail.com> wrote: > On 07/13/2014 12:29 AM, Staffan Tylen wrote: > >> The following statement is flagged as invalid, so what's the correct w

[sqlite] WITH syntax error

The following statement is flagged as invalid, so what's the correct way of coding it? WITH A AS (SELECT 'A'), B AS (SELECT 'B') SELECT * FROM A UNION SELECT * FROM B ; Staffan ___ sqlite-users mailing list sqlite-users@sqlite.org

Re: [sqlite] Dynamic SELECT result column names

Thanks both for the tips. Well, it looks like I'm barking up the wrong tree here, the suggestions might work in principle but unfortunately wont handle changes to the underlying database without requiring changes to whatever solution is applied. > But this will be ugly SQL, it will be convoluted

Re: [sqlite] Dynamic SELECT result column names

I fully agree with this. The fact is that I'm using a database from a third party. TBL1 PFX1_COL1 PFX1_COL2 PFX3_COL3 etc TBL2 PFX2_COL1 PFX2_COL2 PFX3_COL3 etc. The first column has the name of the table. The next x number of columns contain data universal to all tables, such as date and

Re: [sqlite] Dynamic SELECT result column names

Thanks, that was what I suspected. Well, well, life is not perfect ;) On Mon, May 26, 2014 at 4:50 PM, Igor Tandetnik <i...@tandetnik.org> wrote: > On 5/26/2014 10:09 AM, Staffan Tylen wrote: > >> What I want to happen here is that the values in column A1 ('AA1' in this &g

[sqlite] Dynamic SELECT result column names

I'm struggling with a solution that allows me to dynamically define the name of a result column. Consider the following: CREATE TABLE TAB1 (A1,B1); INSERT INTO TAB1 VALUES('AA1','BB1'); WITH PREFIX(PFX) AS (SELECT 'A') SELECT (SELECT PFX||'1' FROM PREFIX) FROM TAB1; What I want to happen here is

[sqlite] SELECT alias name limitation

Why is p not valid here: SELECT CASE WHEN SEX='M' THEN 'MALE' WHEN SEX='F' THEN 'FEMALE' ELSE 'OTHER' END AS p, NAME||' - '||p AS 'NAME' FROM DB; when it's valid here: SELECT CASE WHEN SEX='M' THEN 'MALE' WHEN SEX='F' THEN 'FEMALE' ELSE 'OTHER' END AS p, NAME FROM DB ORDER BY p;

Re: [sqlite] getting more context on SQL parse errors

I second that. Staffan On Tue, Oct 8, 2013 at 7:25 PM, Nelson, Erik - 2 < erik.l.nel...@bankofamerica.com> wrote: > When a SQL parsing error happens, the message returned by sqlite3_errmsg() > is pretty terse... is there some way to retrieve more context, so that the > user has more than one

Re: [sqlite] Bug in sqlite3.exe?

Many who solved this, thank you all. Staffan On Tue, Sep 24, 2013 at 7:11 PM, Yuriy Kaminskiy <yum...@gmail.com> wrote: > Staffan Tylen wrote: > > On Tue, Sep 24, 2013 at 6:50 PM, Simon Slavin <slav...@bigfraud.org> > wrote: > > > >> On 24 Sep 201

Re: [sqlite] Bug in sqlite3.exe?

; > On 24 Sep 2013, at 5:35pm, Staffan Tylen <staffan.ty...@gmail.com> wrote: > > > sqlite> .tables > > CityCountry Languages > > Country Country Official Languages > > Country CapitalsCountryLanguage

[sqlite] Bug in sqlite3.exe?

SQLite version 3.7.16.2 2013-04-12 11:52:43 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> .tables CityCountry Languages Country Country Official Languages Country CapitalsCountryLanguage sqlite> select

Re: [sqlite] Scan table in explain query. Question.

Reading this thread I decided to run an explain query plan for a query that I have with multiple selects combined with union. The (simplified) result is: SEARCH TABLE UB USING COVERING INDEX UBS (UBS=?) (~5108 rows) USE TEMP B-TREE FOR GROUP BY USE TEMP B-TREE FOR ORDER BY SEARCH TABLE UB USING

Re: [sqlite] Suspected bug in SELECT EXCEPT ORDER COLLATE

I just wish to confirm that the workaround shown by Nico works fine for me which makes the issue low priority from my point of view. Thanks all. Staffan On Tue, May 7, 2013 at 5:00 AM, Keith Medcalf wrote: > > So you are saying that: > > Select a from t1 except select a

Re: [sqlite] Suspected bug in SELECT EXCEPT ORDER COLLATE

Richard's script seems to confirm that something is buggy, so I hold fire trying to produce something else that only shows the same thing. Staffan On Mon, May 6, 2013 at 5:00 PM, Richard Hipp wrote: > On Mon, May 6, 2013 at 10:56 AM, Simon Slavin >

[sqlite] Suspected bug in SELECT EXCEPT ORDER COLLATE

I've got a SELECT clause in the following format: SELECT a FROM t1 EXCEPT SELECT a FROM t2 ORDER BY a COLLATE myorder; All a's in t1 get selected (not expected). SELECT a FROM t1 EXCEPT SELECT a FROM t2 ORDER BY a; All a's in t1 except the a's in t2 get selected (as expected). I haven't

[sqlite] LIKE and GLOB questions

I have the following two questions to share: First, assume two tables t1 and t2 where t1 has a text column a with data and t2 has a text column p with patterns in LIKE format. For each a in t1 I want to find all matching patterns p in t2. Is this possible using a single SELECT clause? I've been

Re: [sqlite] pragma table_info(database.table) not supported

>PRAGMA table_info(database.tablename) > > Any chance that SQLite4 might change this, or perhaps accept both forms ? > > In addition, how about a pragma to get the tables in a database? We currently have pragma(database_list), pragma(index_list), and pragma(table_info), but no

Re: [sqlite] pragma table_info(database.table) not supported

Ooops! Thank :) On Wed, Mar 20, 2013 at 12:03 PM, Richard Hipp <d...@sqlite.org> wrote: > On Wed, Mar 20, 2013 at 6:54 AM, Staffan Tylen <staffan.ty...@gmail.com > >wrote: > > > I've just found out that adding a database name as a table prefix is not > > s

[sqlite] pragma table_info(database.table) not supported

I've just found out that adding a database name as a table prefix is not supported by the pragma table_info function, giving a syntax error. The documentation of the ATTACH DATABASE commands says: Tables in an attached database can be referred to using the syntax * database-name.table-name*.

Re: [sqlite] to find table exists or not

SELECT CASE WHEN EXISTS (...) END Staffan On Thu, Dec 6, 2012 at 5:47 PM, Durga D wrote: > I have situation in which I want to read particular record if table exists. > Based on that record information, I have to execute some logic on other > tables of the same

[sqlite] Suspected bug in SQLite shell: .mode switch

I've just discovered that if (within the same shell session) I switch from .mode csv to .mode list, the output remains in csv format. If I instead (or then) switch to .mode column, the result is correct. If I then switch back to .mode list, the output reverts to csv format. It seems like one

[sqlite] Database design preferences

I'm looking for both administrative and technical advice on the pros and cons of either creating one single database table with many columns or creating multiple tables with fewer but related columns to be JOINed when needed. Assume that the data is all related 1-to-1, like name, home address,

Re: [sqlite] What is wrong with this update SQL

:) Thanks. On Thu, Nov 22, 2012 at 12:28 PM, Simon Davies <simon.james.dav...@gmail.com > wrote: > On 22 November 2012 11:20, Staffan Tylen <staffan.ty...@gmail.com> wrote: > > I run this (sql.txt): > > > > create table table1 (age_band); > > insert in

Re: [sqlite] What is wrong with this update SQL

I run this (sql.txt): create table table1 (age_band); insert into table1 values(31); UPDATE table1 SET age_band = (case when age_band <= 10 then ' 0 - 10' when age_band BETWEEN 11 AND 20 then ' 11 - 20' when age_band BETWEEN 21 AND 30 then ' 21 - 30' when age_band BETWEEN 31 AND 40 then ' 31 -

Re: [sqlite] What is wrong with this update SQL

Interesting. I run the same SQL (except I used table t1 instead of table1 'cos I'm lazy) and got this (SQLite 3.7.15): sqlite> delete from t1; sqlite> select * from t1; sqlite> insert into t1 values(1); sqlite> insert into t1 values(10); sqlite> insert into t1 values(11); sqlite> insert into t1