[sqlite] Performance problem LEFT OUTER JOIN and string data from right table

Hey everyone.   new to SQLite so please have patience with me having two tables and doing left outer join   A IDI INTEGER PRIMARY KEY Parent INTEGER INDEX Status INTEGER   B IDR UNIQUE INTEGER FOREIGN KEY IndexME.IDI Points TEXT (at average ~120 character string) (this table is primarily

Re: [sqlite] Performance problem LEFT OUTER JOIN and string data fromright table

Mira Suk wrote: > query written here is a lot simplified (for example "Points" column is > filtered using custom function) however main culprit seems > to be LEFT OUTER JOIN as accessing that same column in query which only has B > table in it is lightning fast. > > result of query is > just aro

Re: [sqlite] Performance problem LEFT OUTER JOIN and string datafromright table

  > Mira Suk wrote: >> query written here is a lot simplified (for example "Points" column is >> filtered using custom function) however main culprit seems >> to be LEFT OUTER JOIN as accessing that same column in query which only has >> B table in it is lightning fast. >> >> result of query is

[sqlite] INSERT syntax missing key SQL syntax

INSERT is supposed to handle multiple rows for VALUES. for example, INSERT INTO table(digit,dialpadstr) VALUES (2,'abc'), (3,'def'), (4,'ghi'), (5,'jkl'), (6,'mno'), (7,'pqrs'), (8,'tuv'), (9,'wxyz'); currently, sqlite only handles 1 row. INSERT INTO table(digit,dialpadstr) VALUES (2,'abc'); --

Re: [sqlite] UPDATE of field deletes record

On 9/15/2011 6:09 AM, Jean-Christophe Deschamps wrote: with an existing record, and a fieldname that is in a column that has a UNIQUE INDEX, if I do sqlite_exec("UPDATE database SET fieldname="&sqlite_escape("get calculator batteries")&" WHERE fieldname=sqlite_escape("get hp50g calc cells")&"

Re: [sqlite] INSERT syntax missing key SQL syntax

On Sep 16, 2011, at 9:27 PM, Jim Michaels wrote: > currently, sqlite only handles 1 row. > INSERT INTO table(digit,dialpadstr) VALUES > (2,'abc'); try: insert into foo ( bar ) select 1 as bar union all select 2 as bar etc... Please refer back to the fine manual: http://www.sqlite.org/lang_in

Re: [sqlite] INSERT syntax missing key SQL syntax

On Fri, 16 Sep 2011 12:27:35 -0700, Jim Michaels wrote: >INSERT is supposed to handle multiple rows for VALUES. >for example, >INSERT INTO table(digit,dialpadstr) VALUES >(2,'abc'), >(3,'def'), >(4,'ghi'), >(5,'jkl'), >(6,'mno'), >(7,'pqrs'), >(8,'tuv'), >(9,'wxyz'); With the preferred {

Re: [sqlite] Performance problem LEFT OUTER JOIN and stringdatafromright table

Mira Suk wrote: >> Mira Suk wrote: >>> query written here is a lot simplified (for example "Points" column is >>> filtered using custom function) however main culprit >>> seems to be LEFT OUTER JOIN as accessing that same column in query which >>> only has B table in it is lightning fast. >>>

Re: [sqlite] INSERT syntax missing key SQL syntax

Jim Michaels wrote: > INSERT is supposed to handle multiple rows for VALUES. Supposed by whom? What is the basis for this claim? -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/

Re: [sqlite] UPDATE of field deletes record

Jim Michaels wrote: > cancel the bug report. further testing revealed that it does NOT > exhibit a bug, except for the fact that I can't do multirow INSERTs > (that, unfortunately is not in the manual, and should be a standard > feature). Should it be? In which standard is this feature mandate

Re: [sqlite] INSERT syntax missing key SQL syntax

On 16 Sep 2011, at 8:27pm, Jim Michaels wrote: > INSERT is supposed to handle multiple rows for VALUES. > for example, > INSERT INTO table(digit,dialpadstr) VALUES > (2,'abc'), > (3,'def'), > (4,'ghi'), > (5,'jkl'), > (6,'mno'), > (7,'pqrs'), > (8,'tuv'), > (9,'wxyz'); Can you find support for t

Re: [sqlite] INSERT syntax missing key SQL syntax

On Sep 17, 2011, at 10:11 AM, Igor Tandetnik wrote: > Jim Michaels > > wrote: >> INSERT is supposed to handle multiple rows for VALUES. > > Supposed by whom? What is the basis for this claim? The multiline INSERT capability may not be a SQL standard, but it is not only highly convenient, it

Re: [sqlite] INSERT syntax missing key SQL syntax

On 17 Sep 2011, at 4:29pm, Mr. Puneet Kishor wrote: > The multiline INSERT capability may not be a SQL standard, but it is not only > highly convenient, it is also supported by Pg, the ostensible role model and > inspiration for SQLite. Not that I particularly care either way because I > almos

Re: [sqlite] INSERT syntax missing key SQL syntax

On Sep 17, 2011, at 5:11 PM, Igor Tandetnik wrote: > Supposed by whom? What is the basis for this claim? SQL-92: http://en.wikipedia.org/wiki/Insert_(SQL)#Multirow_inserts This feature is supported by DB2, SQL Server (since version 10.0 - i.e. 2008), PostgreSQL (since version 8.2), MySQL, an

Re: [sqlite] INSERT syntax missing key SQL syntax

On Sep 17, 2011, at 10:33 AM, Simon Slavin wrote: > > On 17 Sep 2011, at 4:29pm, Mr. Puneet Kishor wrote: > >> The multiline INSERT capability may not be a SQL standard, but it is not >> only highly convenient, it is also supported by Pg, the ostensible role >> model and inspiration for SQLit

Re: [sqlite] INSERT syntax missing key SQL syntax

On Sep 17, 2011, at 5:55 PM, Mr. Puneet Kishor wrote: > Essentially, if I had my druthers, I would support whatever Pg supports and > be done with it. For most part SQLite seems to follow "good for Pg good for > the gander" philosophy. I would much rather have a MERGE statement: http://en.wik

Re: [sqlite] INSERT syntax missing key SQL syntax

On 17 Sep 2011, at 4:52pm, Petite Abeille wrote: > On Sep 17, 2011, at 5:11 PM, Igor Tandetnik wrote: > >> Supposed by whom? What is the basis for this claim? > > SQL-92: > > http://en.wikipedia.org/wiki/Insert_(SQL)#Multirow_inserts Here's a draft of SQL-92. You can find the INSERT statemen

Re: [sqlite] INSERT syntax missing key SQL syntax

On Sat, 17 Sep 2011 16:23:23 +0100, Simon Slavin wrote: > >On 16 Sep 2011, at 8:27pm, Jim Michaels wrote: > >> INSERT is supposed to handle multiple rows for VALUES. >> for example, >> INSERT INTO table(digit,dialpadstr) VALUES >> (2,'abc'), >> (3,'def'), >> (4,'ghi'), >> (5,'jkl'), >> (6,'mno'),

Re: [sqlite] Performance problem LEFT OUTER JOIN andstringdatafromright table

>Ok then, show the result of prepending EXPLAIN QUERY PLAN to your statement. >-- >Igor Tandetnik First of all thanks for bearing with me :) functions TZB_MATCHRECURSIVE(int,int) - disabled for this test - always return 1. applies filter recursively TZB_ISCHILD(int) - bitmask check TZB_MATCHD

Re: [sqlite] INSERT syntax missing key SQL syntax

On Sep 17, 2011, at 6:04 PM, Simon Slavin wrote: > Can you find the syntax that allows These ANSI specifications are notoriously difficult to pinpoint :) DB2: http://publib.boulder.ibm.com/infocenter/db2luw/v8/index.jsp?topic=/com.ibm.db2.udb.doc/admin/r970.htm INSERT INTO DEPART

Re: [sqlite] INSERT syntax missing key SQL syntax

On 17 Sep 2011, at 6:08pm, Petite Abeille wrote: > On Sep 17, 2011, at 6:04 PM, Simon Slavin wrote: > >> Can you find the syntax that allows > > These ANSI specifications are notoriously difficult to pinpoint :) Not really, they just cost money. For instance:

Re: [sqlite] INSERT syntax missing key SQL syntax

On Sep 17, 2011, at 7:33 PM, Simon Slavin wrote: > As was clear from my post, I was referring to SQL standards. What various > implementation vendors choose to do is up to them. But the multi-spec syntax > referred to in the OP is not in any SQL standard I've seen. In BNF Grammar for ISO/IEC

Re: [sqlite] INSERT syntax missing key SQL syntax

On 17 Sep 2011, at 6:42pm, Petite Abeille wrote: > On Sep 17, 2011, at 7:33 PM, Simon Slavin wrote: > >> As was clear from my post, I was referring to SQL standards. What various >> implementation vendors choose to do is up to them. But the multi-spec >> syntax referred to in the OP is not i

Re: [sqlite] INSERT syntax missing key SQL syntax

On Sep 17, 2011, at 8:06 PM, Simon Slavin wrote: > ::=| | > > > ? Which one, and where is the expansion that allows for multiple sets of > brackets after "VALUES" ? If I'm reading this correctly, the constructor one. Step by step: (1) insert statement http://savage.net.au/SQL/s

Re: [sqlite] INSERT syntax missing key SQL syntax

On Sep 17, 2011, at 1:06 PM, Simon Slavin wrote: > > On 17 Sep 2011, at 6:42pm, Petite Abeille wrote: > >> On Sep 17, 2011, at 7:33 PM, Simon Slavin wrote: >> >>> As was clear from my post, I was referring to SQL standards. What various >>> implementation vendors choose to do is up to them.

Re: [sqlite] Performance problem LEFT OUTER JOINandstringdatafromright table

Mira Suk wrote: > test 1. > > query > SELECT [IndexME].[IDI], [IndexME].[Status], [IndexME].[Icon], [IndexME].[Text] > FROM [IndexME] LEFT OUTER JOIN [ItemsME] > ON [ItemsME].[IDR] = [IndexME].[IDI] WHERE > [IndexME].[Parent] = ?1 AND > (TZB_MATCHRECURSIVE([Ind

Re: [sqlite] INSERT syntax missing key SQL syntax

On Sep 17, 2011, at 8:43 PM, Mr. Puneet Kishor wrote: > All this chattering among us doesn't really matter (other than for academic > purposes). For the, hmmm, more scholarly inclined on the list, a marginally more readable ISO spec, ISO/IEC 9075-2:2003: http://synthesis.ipi.ac.ru/synthesis/s

[sqlite] julianday and unixepoch

Hello, Say I got an unix epoch, e.g. 1030561675: > select datetime( 1030561675, 'unixepoch', 'utc' ); 2002-08-28 19:07:55 Say I only need the date part of it: > select date( 1030561675, 'unixepoch', 'utc' ); 2002-08-28 Say, I would like to convert that date to a julian day: > select julianda

Re: [sqlite] julianday and unixepoch

Petite Abeille wrote: > Say, I would like to convert that date to a julian day: > >> select julianday( date( 1030561675, 'unixepoch', 'utc' ) ); > 2452514.5 > > Oooops... where does that .5 comes from? In Julian calendar, 0 represents noon, and .5 represents midnight. > I was more expecting t

Re: [sqlite] Performance problem LEFT OUTERJOINandstringdatafromright table

The difference is that #2 mentions only one field from ItemsME, namely IDR. The value of that field comes from the index, the table itself doesn't need to be read at all. It's not even clear why #2 bothers to join with ItemsME at all - it's a no-op. #1 uses more fields from ItemsME, so it nee

Re: [sqlite] julianday and unixepoch

On Sep 17, 2011, at 9:29 PM, Igor Tandetnik wrote: >> So how come does julianday take the time part of date into consideration >> even when it's not provided? > > It doesn't. Hmmm... http://en.wikipedia.org/wiki/Julian_day#Converting_Gregorian_calendar_date_to_Julian_Day_Number local math =

Re: [sqlite] INSERT syntax missing key SQL syntax

On 17 Sep 2011, at 7:28pm, Petite Abeille wrote: > (5) contextually typed row value expression list > > http://savage.net.au/SQL/sql-99.bnf.html#contextually%20typed%20row%20value%20expression%20list > > ::= > > [ { }... ] > > (6) contextually typed row value construct

Re: [sqlite] julianday and unixepoch

On Sat, Sep 17, 2011 at 3:52 PM, Petite Abeille wrote: > > > So how come does julianday take the time part of date into consideration > even when it's not provided? > > > > In SQLite: > > > select julianday( '2002-08-28' ); > 2452514.5 > *SNIP* > I was expecting a julian day number. SQLite's jul

Re: [sqlite] julianday and unixepoch

On Sep 17, 2011, at 10:42 PM, Kevin Benson wrote: > The julianday() function returns the Julian > day- the number of days... > > since noon in Greenwich on November 24, 4714 > B.C. (Proleptic > Gregorian > calendar

Re: [sqlite] julianday and unixepoch

On Sat, Sep 17, 2011 at 4:43 PM, Petite Abeille wrote: > > On Sep 17, 2011, at 10:42 PM, Kevin Benson wrote: > > > The julianday() function returns the Julian > > day- the number of days... > > > > since noon in Greenwich on November 24, 4714 > > B.C. (Pr

Re: [sqlite] julianday and unixepoch

On Sep 17, 2011, at 10:53 PM, Kevin Benson wrote: >> Your point is taken and most likely the documentation intended merely > to identify the result in terms of Julian days: > i.e. > The julianday() function returns the number of Julian > days since noon in Greenwich on November 24, 4714 B.C. (Pro