Re: [sqlite] Poor performance with nested query in outer join

[Simon Slavin]

On 10 Dec 2011, at 10:35pm, Nick Smallbone wrote:

> the difference between my query and your query is
> that I have a subquery (select * from b) and you don't.

I understand that SQLite handles VIEWs as if you had defined and saved a SELECT 
statement.  So if you JOIN with a VIEW, does SQLite handle it as as JOIN or a 
sub-SELECT ?

Simon.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] sqlite3_auto_extension() memory leaks

[刘以舟]

static void init_db(sqlite3* db, char** pzErrMsg, const struct 
sqlite3_api_routines* *pThunk)
{
.
}
 
BOOL CMyDlg::OnInitDialog()
{
.
sqlite3_auto_extension ((void (*)(void)) init_db);

}
 
 

Detected memory leaks!
Dumping objects ->
{428} normal block at 0x0039C2F8, 16 bytes long.
 Data: <  F > 08 00 00 00 00 00 00 00 E0 02 46 00 CD CD CD CD
Object dump complete.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Poor performance with nested query in outer join

[Nick Smallbone]

"Black, Michael (IS)" 
writes:

> Natural joins are generally considered to be evil.  Too many columns
> in common can be bad.
>
> If you just spell it out it works as expected
>
> sqlite> explain query plan select * from a left join b where a.id=1 and 
> b.id=a.id;
> 0|0|0|SEARCH TABLE a USING COVERING INDEX sqlite_autoindex_a_1 (id=?) (~1 
> rows)
> 0|1|1|SEARCH TABLE b USING COVERING INDEX sqlite_autoindex_b_1 (id=?)
> (~1 rows)

Thanks, but this is a red herring: it makes no difference whether you
use natural join or an explicit join here (and I would've been very
surprised if it had, because they are exactly the same
operation). Rather, the difference between my query and your query is
that I have a subquery (select * from b) and you don't.

Nick

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is a column name of 'AS' a restricted column name

[Account 69]

Thanks for the replies.

I can now work around the problem by either converting the column name 
AS to A_S and easily recode the application to use A_S in the SQL 
statements and data retrieval or just make sure I quote the column name.



Other aspects of my post, thanks for the REAL pointer for the data type. 
I just carried over DOUBLE as it was in the dropdown of SQLExpert when I 
created a few test tables.


The Date_TS is not unique, it is just the date in seconds since epoch 
and relates to a calendar month.


I shall also recode to strip the single quote from around the values if 
it is a number. I again just created a test table in SQLExpert and then 
loaded up a few lines of data and then exported out as a sql file and 
that was how the data from SQLexpert presented the data in the export 
file so I just took it as read.


Thanks for the web reference to the keywords, I looked but you can 
imagine how many results you get back looking for column name as in 
google etc.


Simon Slavin wrote On 11/12/2011 10:47:


On 11 Dec 2011, at 10:01am, Account 69 wrote:


Is a column name of "AS" a restricted column name in SQLlite? Can anyone point 
me to a webpage that species these restricted column names or is this an error on my part 
somewhere


No, you got it right:



The word 'AS' is used in commands like this:

SELECT (quantity * pricePerItem) AS totalPrice FROM invoiceItems

Allowing 'AS' as a column name would make parsing very difficult !

While I'm here, although your table definition will work in SQLite, it might be 
worth studying up on SQLite3 datatypes so you don't trick yourself into 
expecting behaviour SQLite doesn't provide.



It might be better to do

CREATE TABLE shopdata (
id INTEGER PRIMARY KEY,
date_ts INTEGER,
H_S REAL DEFAULT 0,
A_S REAL DEFAULT 0);

You did get 'INTEGER PRIMARY KEY' right, though, which most people don't.

Simon.


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is a column name of 'AS' a restricted column name

[Kees Nuyt]

On Sun, 11 Dec 2011 10:01:02 +, Account 69 
wrote:

> Hello,
>
> Just started to use SQLite3 instead of MSAccess within my programming 
> environment can hit a problem. I have searched the mailing list and also 
> other internet sites for help but could not find any.
>
> In my table I have (for simplicity) 4 columns (ID, Date, HS, AS)
> My data comes from an established hourly CSV file that I receive for the 
>  last 7 years. In the past I would use VB6 to process the CSV and then 
> via a SQL Insert statement I would insert into an MSAccess database (for 
> local PC usage) and also a MySQL database (for internet usage)
>
> The table is..
> CREATE TABLE [shopdata] (
> [ID] INTEGER PRIMARY KEY,
> [Date_TS] INTEGER,
> [HS] DOUBLE Default (0),
> [AS] DOUBLE Default (0));

If Date_TS is unique over all imported values, it is a candidate key and
can be used as the primary key, and you wouldn't need the ID column:

CREATE TABLE [shopdata] (
[Date_TS] INTEGER PRIMARY KEY,
[HS] DOUBLE default 0.0,
[AS] DOUBLE default 0.0
);

> When I use the following SQL in my application
>
> INSERT INTO [shopdata] (Date_TS, HS, AS) VALUES ('1012003200','15','9')
> The insert fails with an error of (near "AS": syntax error)

AS is a keyword.

Do not use quotes for numeric values in the VALUES clause to prevent
unnecessary conversions (although they will be stored as integer
anyway).

http://www.sqlite.org/datatype3.html § 2.3

INSERT INTO [shopdata] 
([Date_TS], [HS], [AS]) 
VALUES
(1012003200,15,9)

> This has had me stumped for a few days especially as I am getting to 
> grips with SQLlite and kept thinking I am doing something wrong.
>
> I messed around late last night and I changed the column name of "AS" to 
> "A_S" and the inset works fine and the data is stored in the database. 
> To recheck I had not changed the table structure in an other way I 
> reverted back to using a column name of "AS" and again I got the SQL 
> errors of (near "AS": syntax error)
>
> I tried this also in SQLExpert and the exact same happens. In MSAccess 
> and MySQL this column name of "AS" works fine (as has been for 7 years).

Every SQL implementation has its own quirks.

> Is a column name of "AS" a restricted column name in SQLlite? Can anyone 
> point me to a webpage that species these restricted column names or is 
> this an error on my part somewhere

Indeed, as pointed out by others, AS is a keyword. 
You can use it if you quote it with double quotes "" or backticks `` or
brackets [] in _all_ of your statements, as you did in the CREATE TABLE
statements. Avoiding keywords is easier, using A_S is a good solution.


> Thank you.
>
> PS, I do not have control over the incoming CSV, so I have to make any 
> changes during processing.


-- 
Regards,

Kees Nuyt

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is a column name of 'AS' a restricted column name

[Simon Slavin]

On 11 Dec 2011, at 10:01am, Account 69 wrote:

> Is a column name of "AS" a restricted column name in SQLlite? Can anyone 
> point me to a webpage that species these restricted column names or is this 
> an error on my part somewhere

No, you got it right:



The word 'AS' is used in commands like this:

SELECT (quantity * pricePerItem) AS totalPrice FROM invoiceItems

Allowing 'AS' as a column name would make parsing very difficult !

While I'm here, although your table definition will work in SQLite, it might be 
worth studying up on SQLite3 datatypes so you don't trick yourself into 
expecting behaviour SQLite doesn't provide.



It might be better to do

CREATE TABLE shopdata (
id INTEGER PRIMARY KEY,
date_ts INTEGER,
H_S REAL DEFAULT 0,
A_S REAL DEFAULT 0);

You did get 'INTEGER PRIMARY KEY' right, though, which most people don't.

Simon.


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is a column name of 'AS' a restricted column name

[Eric Pankoke]

AS is a key word, so I believe you either have to put it in single or double
quotes to use it as a column name:

INSERT INTO [shopdata] (Date_TS, HS, "AS")...

Hopefully I'm remembering correctly what I've read in the past.

Eric Pankoke
Mobile Games Reviewer
My Site: http://www.rustysabre.com/
Reviews: http://www.technobrains.com/, http://www.touchmyapps.com
Blog: http://www.iphonelife.com/blogs/eric-pankoke
Twitter: http://www.twitter.com/RustySabre/
 

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Account 69
Sent: Sunday, December 11, 2011 5:01 AM
To: sqlite-users@sqlite.org
Subject: [sqlite] Is a column name of 'AS' a restricted column name

Hello,

Just started to use SQLite3 instead of MSAccess within my programming 
environment can hit a problem. I have searched the mailing list and also 
other internet sites for help but could not find any.

In my table I have (for simplicity) 4 columns (ID, Date, HS, AS)
My data comes from an established hourly CSV file that I receive for the 
  last 7 years. In the past I would use VB6 to process the CSV and then 
via a SQL Insert statement I would insert into an MSAccess database (for 
local PC usage) and also a MySQL database (for internet usage)

The table is..
CREATE TABLE [shopdata] (
[ID] INTEGER PRIMARY KEY,
[Date_TS] INTEGER,
[HS] DOUBLE Default (0),
[AS] DOUBLE Default (0));

When I use the following SQL in my application

INSERT INTO [shopdata] (Date_TS, HS, AS) VALUES ('1012003200','15','9')

The insert fails with an error of (near "AS": syntax error)

This has had me stumped for a few days especially as I am getting to 
grips with SQLlite and kept thinking I am doing something wrong.

I messed around late last night and I changed the column name of "AS" to 
"A_S" and the inset works fine and the data is stored in the database. 
To recheck I had not changed the table structure in an other way I 
reverted back to using a column name of "AS" and again I got the SQL 
errors of (near "AS": syntax error)

I tried this also in SQLExpert and the exact same happens. In MSAccess 
and MySQL this column name of "AS" works fine (as has been for 7 years)

Is a column name of "AS" a restricted column name in SQLlite? Can anyone 
point me to a webpage that species these restricted column names or is 
this an error on my part somewhere

Thank you.

PS, I do not have control over the incoming CSV, so I have to make any 
changes during processing.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Is a column name of 'AS' a restricted column name

[Account 69]

Hello,

Just started to use SQLite3 instead of MSAccess within my programming 
environment can hit a problem. I have searched the mailing list and also 
other internet sites for help but could not find any.


In my table I have (for simplicity) 4 columns (ID, Date, HS, AS)
My data comes from an established hourly CSV file that I receive for the 
 last 7 years. In the past I would use VB6 to process the CSV and then 
via a SQL Insert statement I would insert into an MSAccess database (for 
local PC usage) and also a MySQL database (for internet usage)


The table is..
CREATE TABLE [shopdata] (
[ID] INTEGER PRIMARY KEY,
[Date_TS] INTEGER,
[HS] DOUBLE Default (0),
[AS] DOUBLE Default (0));

When I use the following SQL in my application

INSERT INTO [shopdata] (Date_TS, HS, AS) VALUES ('1012003200','15','9')

The insert fails with an error of (near "AS": syntax error)

This has had me stumped for a few days especially as I am getting to 
grips with SQLlite and kept thinking I am doing something wrong.


I messed around late last night and I changed the column name of "AS" to 
"A_S" and the inset works fine and the data is stored in the database. 
To recheck I had not changed the table structure in an other way I 
reverted back to using a column name of "AS" and again I got the SQL 
errors of (near "AS": syntax error)


I tried this also in SQLExpert and the exact same happens. In MSAccess 
and MySQL this column name of "AS" works fine (as has been for 7 years)


Is a column name of "AS" a restricted column name in SQLlite? Can anyone 
point me to a webpage that species these restricted column names or is 
this an error on my part somewhere


Thank you.

PS, I do not have control over the incoming CSV, so I have to make any 
changes during processing.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users