[sqlite] Comparing to NULL in prepared statment

Hi all, This is not really a sqlite specific question, but I was wondering if there might be a sqlite specific answer. I prepare the following statement: "SELECT col1 FROM table1 WHERE col2=:val" col2 is a textual string, and may sometimes be NULL. If I bind a string to :val it works fine. If I

Re: [sqlite] Comparing to NULL in prepared statment

Hi Baruch, in such cases I use one of the following methods: SELECT col1 FROM table1 WHERE col2=:val or (col2 is null and :val is null) SELECT col1 FROM table1 WHERE coalesce(col2, '#') = coalesce(:val, '#') where '#' is a value that i know is never used in col2 (this is a drawback) Yours

Re: [sqlite] Comparing to NULL in prepared statment

On Wed, Nov 5, 2014 at 10:52 AM, Martin Engelschalk < engelsch...@codeswift.com> wrote: > Hi Baruch, > > in such cases I use one of the following methods: > > SELECT col1 FROM table1 WHERE col2=:val or (col2 is null and :val is null) > > SELECT col1 FROM table1 WHERE coalesce(col2, '#') =

[sqlite] sqlite-3.8.7.1: create table as select with join creates corrupted results

Hi, I'm just upgrading from 3.8.5 to 3.8.7.1 and experience the following incorrect behavior (see commands below): - The where clause A <> B is expected to return only the first row, but returns all rows. - String concatenation on column B doesn't sometimes work (e. g. see end of

[sqlite] SQLite as a meta database

Hi all, Following a first discussion on sqlite-dev that was probably not the right place to post, I've been invited to repost here for a broader audience :) I am a developer on QGIS and I am investigating the possible use of SQLite / Spatialite to extend QGIS relational-oriented features. For

Re: [sqlite] sqlite-3.8.7.1: create table as select with join creates corrupted results

On Wed, Nov 5, 2014 at 4:03 AM, Nissl Reinhard wrote: > > I can provide the 17 kB bug.db if required. > Please do provide the database. Private email to me will be fine. -- D. Richard Hipp d...@sqlite.org ___ sqlite-users

Re: [sqlite] Comparing to NULL in prepared statment

Baruch Burstein wrote: > "SELECT col1 FROM table1 WHERE col2=:val" > > col2 is a textual string, and may sometimes be NULL. If I bind a string to > :val it works fine. If I bind a null-pointer the comparison fails since it > should be using ISNULL. Is there a way to do this correctly with a single

[sqlite] LEFT joins affect query plan for semantically equal queries

Hi! After playing a little with SQLite as a DBMS for Bugzilla, I've discovered that LEFT/INNER join affects query plan in a bad way even for semantically equal queries: SELECT * FROM bugs b INNER JOIN profiles p ON p.userid=b.assigned_to WHERE p.login_name='vita...@yourcmc.ru' Query plan:

Re: [sqlite] LEFT joins affect query plan for semantically equal queries

On 5 Nov 2014, at 12:13pm, vita...@yourcmc.ru wrote: > Which is of course very slow. Can you please run ANALYZE then try the plans again ? Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org

Re: [sqlite] LEFT joins affect query plan for semantically equal queries

On Wed, Nov 5, 2014 at 7:13 AM, wrote: > Hi! > > After playing a little with SQLite as a DBMS for Bugzilla, I've discovered > that LEFT/INNER join affects query plan in a bad way even for semantically > equal queries: > I'm not sure what you mean by "semantically equal", but

Re: [sqlite] sqlite3.8.7 crashes when creating an index with WAL mode disabled

>Perhaps this could be caught at compile time. The process that creates >the amalgamation could add a preprocessor macro defining e.g. >_SQLITE_AMALGAMATION_. The SQLITE_OMIT_* macros could test for that >definition and #pragma error if found. I think there is a much easier solution. Since

Re: [sqlite] LEFT joins affect query plan for semantically equal queries

vita...@yourcmc.ru wrote: > SELECT * FROM bugs b LEFT JOIN profiles p ON p.userid=b.assigned_to WHERE > p.login_name='vita...@yourcmc.ru' > > Query plan: > SCAN TABLE bugs AS b > SEARCH TABLE profiles AS p USING INTEGER PRIMARY KEY (rowid=?) > > Which is of course very slow. > > Maybe you'll

Re: [sqlite] LEFT joins affect query plan for semantically equal queries

On 2014/11/05 14:13, vita...@yourcmc.ru wrote: Hi! After playing a little with SQLite as a DBMS for Bugzilla, I've discovered that LEFT/INNER join affects query plan in a bad way even for semantically equal queries: SELECT * FROM bugs b INNER JOIN profiles p ON p.userid=b.assigned_to WHERE

Re: [sqlite] SQLite as a meta database

Hi, we have extensive experience with respect to the use of virtual tables in SQLite. In fact, the only native SQLite tables we use are in a configuration checking tool. We have "providers" from in-memory indexed tables, CTree (r) files, Oracle tables (read only), structured disk files,

Re: [sqlite] LEFT joins affect query plan for semantically equal queries

On Wed, Nov 5, 2014 at 8:12 AM, Clemens Ladisch wrote: > > The WHERE expression then makes the outer join meaningless, > Thank you, Clemens - I missed that detail. So the suggestion is that we should enhance the SQLite query planner to detect when the WHERE clause requires

Re: [sqlite] LEFT joins affect query plan for semantically equal queries

On Wed, 2014-11-05 at 15:13 +0200, RSmith wrote: > On 2014/11/05 14:13, vita...@yourcmc.ru wrote: > > Hi! > > > > After playing a little with SQLite as a DBMS for Bugzilla, I've discovered > > that LEFT/INNER join affects query plan in a bad way > > even for semantically equal queries: > > > >

Re: [sqlite] LEFT joins affect query plan for semantically equal queries

On 2014/11/05 15:26, David Woodhouse wrote: On Wed, 2014-11-05 at 15:13 +0200, RSmith wrote: I don't think it's anything to do with the table data being special, is it? Isn't it generically true that for any LEFT JOIN of a,b WHERE b.anything IS NOT NULL, the results are going to be equal with

Re: [sqlite] SQLite as a meta database

Hi, Le 05/11/2014 14:16, Hick Gunter a écrit : > Hi, > > we have extensive experience with respect to the use of virtual tables in > SQLite. In fact, the only native SQLite tables we use are in a configuration > checking tool. > > We have "providers" from in-memory indexed tables, CTree (r)

Re: [sqlite] LEFT joins affect query plan for semantically equal queries

On Wed, 2014-11-05 at 16:00 +0200, RSmith wrote: > On 2014/11/05 15:26, David Woodhouse wrote: > > On Wed, 2014-11-05 at 15:13 +0200, RSmith wrote: > > I don't think it's anything to do with the table data being special, > is it? Isn't it generically true that for any LEFT JOIN of > > a,b WHERE

Re: [sqlite] sqlite-3.8.7.1: create table as select with join creates corrupted results

On Wed, Nov 5, 2014 at 4:03 AM, Nissl Reinhard wrote: > Hi, > > I'm just upgrading from 3.8.5 to 3.8.7.1 and experience the following > incorrect behavior (see commands below): > Tracking the problem here:

Re: [sqlite] LEFT joins affect query plan for semantically equal queries

On Wednesday, 5 November, 2014 05:14, vita...@yourcmc.ru said: >After playing a little with SQLite as a DBMS for Bugzilla, I've >discovered that LEFT/INNER join affects query plan in a bad way even for >semantically equal queries: >SELECT * FROM bugs b INNER JOIN profiles p ON

Re: [sqlite] sqlite-3.8.7.1: create table as select with join creates corrupted results

On Wed, Nov 5, 2014 at 9:55 AM, Richard Hipp wrote: > > > On Wed, Nov 5, 2014 at 4:03 AM, Nissl Reinhard > wrote: > >> Hi, >> >> I'm just upgrading from 3.8.5 to 3.8.7.1 and experience the following >> incorrect behavior (see commands below): >> > >

Re: [sqlite] fixing time-warp

Thank you, that is helpful information. On Tue, Nov 4, 2014 at 11:52 PM, Stephan Beal wrote: > On Wed, Nov 5, 2014 at 7:07 AM, E. Timothy Uy wrote: > > > The problem is ultimately not time-warps. DRH can confirm - the problem > is > > actually inside

Re: [sqlite] fixing time-warp

How about looking into fossil test-shortest-path and see how it follows the path of revisions. ? On Wed, Nov 5, 2014 at 9:04 AM, E. Timothy Uy wrote: > Thank you, that is helpful information. > > On Tue, Nov 4, 2014 at 11:52 PM, Stephan Beal wrote:

Re: [sqlite] fixing time-warp

On Wed, Nov 5, 2014 at 6:24 PM, Andreas Kupries wrote: > How about looking into > > fossil test-shortest-path > > and see how it follows the path of revisions. ? > Oh, but were's the fun in rolling a wheel someone else already made round ;). (moments later...) It

Re: [sqlite] SQLite as a meta database

Wow! Please keep this discussion up-to-date because it is absolutely fascinating what all of you are doing. Thanks, dvn On Wed, Nov 5, 2014 at 8:36 AM, Hugo Mercier wrote: > Hi, > > Le 05/11/2014 14:16, Hick Gunter a écrit : > > Hi, > > > > we have extensive

Re: [sqlite] SQLite as a meta database

IIRC there's a GNOME interface to databases, libgda, that works this way. The fundamental problem with using SQLite3 virtual tables to access remote/high-latency resources is that the SQLite3 VM and the generated bytecode are very serial, therefore the VM can't make progress on other parts of a

Re: [sqlite] How to check if a record exists

Thanks for the reply, Darren. No need for parameters here. There's no risk of SQL injection attacks and speed is not a concern. Thanks again, -- Bill Drago Senior Engineer L3 Communications / Narda Microwave East 435 Moreland Road Hauppauge, NY 11788 631-272-5947 / william.dr...@l-3com.com >

Re: [sqlite] SQLite as a meta database

Hi SQLite Team: I second Nico's suggestion. We are finding new usage with SQLite especially with virtual tables. I understand the teams commitment to backwards compatibility. Maybe some time in the future a version 4 be started that incorporates many new advancement in the industry.

Re: [sqlite] How to check if a record exists

This is exactly what I was looking for. It works perfectly. Thanks, -- Bill Drago Senior Engineer L3 Communications / Narda Microwave East 435 Moreland Road Hauppauge, NY 11788 631-272-5947 / william.dr...@l-3com.com > -Original Message- > From: sqlite-users-boun...@sqlite.org

Re: [sqlite] LEFT joins affect query plan for semantically equal queries

Can you please run ANALYZE then try the plans again ? This was just after running ANALYZE :) the fact that they produce the exact same answer is simply by virtue of your WHERE clause being specifically that Of course, I understand, that's what I've meant - the plan shouldn't differ for

Re: [sqlite] How to check if a record exists

Cool website, James. I bookmarked it for future reference. Thanks for your help, it will come in handy... -- Bill Drago Senior Engineer L3 Communications / Narda Microwave East 435 Moreland Road Hauppauge, NY 11788 631-272-5947 / william.dr...@l-3com.com > -Original Message- > From:

[sqlite] Is sqlite thread-safety sufficient for use with "Go" language ?

I would like to use sqlite with "Go" (golang) language. There are already several drivers available in this language. But before using them, I would like to ensure it is really safe. The normal sequence to access a database is: sqlite3_prepare() loop sqlite3_step() sqlite3_column()

Re: [sqlite] SQLite as a meta database

On 5 Nov 2014, at 9:28pm, Edward Lau wrote: > Maybe some time in the future a version 4 be started that incorporates many > new advancement in the industry. Version 3 can still be continued for > backwards compatibility and version 4 will break some but set the stage for >

Re: [sqlite] Is sqlite thread-safety sufficient for use with "Go" language ?

On 5 Nov 2014, at 10:05pm, nicolas riesch wrote: > Even if the user writes a Go program with only one logical thread, he has > no control about which OS thread will process a function call. > > This means that EACH SUCCESSIVE function in the sequence above can be

Re: [sqlite] SQLite as a meta database

Hi, I am intruding into this thread, because I face a similar issue. At our company we have a "proprietary" database storing customer grocery transactions which basically only a C programmer can get to. It is by no stretch of the imagination a "relational" database. Basically, everything is an

Re: [sqlite] Is sqlite thread-safety sufficient for use with "Go" language ?

Pardon me, I will try to reformulate my question more clearly. My scenario: - sqlite is set to Multi-thread mode (SQLITE_THREADSAFE=2), or Serialized mode (SQLITE_THREADSAFE=1) - I create N logical threads in my "Go" program. - Each logical thread creates a database connection, for its

[sqlite] default ignore-glob for sqlite

Hi, I noticed that the ignore-glob for sqlite is empty. Is it just because fossil does not transfer this information? What should I be using? Thank you, Tim ___ sqlite-users mailing list sqlite-users@sqlite.org

Re: [sqlite] SQLite as a meta database

Exactly. Can someone put this link of the SQLite home page? I didn't know it existed. I will read it and post my comments later. Thanks. -Original Message- From: Simon Slavin To: General Discussion of SQLite Database Sent: Wed, Nov 5,

[sqlite] OT: Testers needed for full database encryption support

As a quick FYI, we've just added initial full database encryption support to DB Broweser for SQLite (using SQLCipher). We're looking for people using MacOS X, Linux, or FreeBSD to try it out and report success/failure/etc back to us (on GitHub). Initial MacOS X build:

Re: [sqlite] Is sqlite thread-safety sufficient for use with "Go" language ?

On Wed, Nov 5, 2014 at 7:10 PM, nicolas riesch wrote: > Pardon me, I will try to reformulate my question more clearly. > > My scenario: > > - sqlite is set to Multi-thread mode (SQLITE_THREADSAFE=2), or Serialized > mode (SQLITE_THREADSAFE=1) > - I create N logical

Re: [sqlite] Is sqlite thread-safety sufficient for use with "Go" language ?

Hello, Maxim, I use your excellent driver, indeed ;-) About my post, the poison of doubt has just crept into my mind when I read this: http://www.sqlite.org/cvstrac/wiki?p=MultiThreading There is a bug in some Linux implementations (RedHat9 is the canonical example) that prevents fcntl()

Re: [sqlite] LEFT joins affect query plan for semantically equal queries

On Wed, 05 Nov 2014 08:24:47 -0700 "Keith Medcalf" wrote: > The two queries are different. They may end up with the same result, > but you are asking different questions. In the first you are > returning only matching rows. In the later you are requesting a > projection