[sqlite] Possible issue with 3.19.3

[Eric Sink]

Greetings,

Version 3.19.3 is yielding different results than previous releases for a
query in the Entity Framework Core test suite.

The query looks like this:

SELECT [e].[Id], [e].[Discriminator], [e].[Name], [e].[BaseId], [t].[Id],
[t].[BaseParentId], [t].[Discriminator], [t].[Name], [t0].[Id],
[t0].[Discriminator], [t0].[Name], [t0].[ParentCollectionId],
[t0].[ParentReferenceId]
FROM [BaseInheritanceRelationshipEntity] AS [e]
LEFT JOIN (
SELECT [e.BaseReferenceOnBase].*
FROM [BaseReferenceOnBase] AS [e.BaseReferenceOnBase]
WHERE [e.BaseReferenceOnBase].[Discriminator] IN
('DerivedReferenceOnBase', 'BaseReferenceOnBase')
) AS [t] ON [e].[Id] = [t].[BaseParentId]
LEFT JOIN (
SELECT [e.BaseReferenceOnBase.NestedReference].*
FROM [NestedReferenceBase] AS [e.BaseReferenceOnBase.NestedReference]
WHERE [e.BaseReferenceOnBase.NestedReference].[Discriminator] IN
('NestedReferenceDerived', 'NestedReferenceBase')
) AS [t0] ON [t].[Id] = [t0].[ParentReferenceId]
WHERE [e].[Discriminator] = 'DerivedInheritanceRelationshipEntity';

I have a 143 KB input database that demonstrates the problem.  On 3.16.0
and 3.18.0, this query results in 3 rows.  On 3.19.3, it results in 6 rows.

Would it be appropriate for me to send the database file to someone for
further investigation?

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

Re: [sqlite] SQLite does not support ARM platform?

[Eric Sink]

You are encountering a limitation of the compiler you are using, not a
limitation of SQLite (which compiles for ARM just fine).

The error message is telling you that Microsoft does not support desktop
applications compiled for ARM.

What environment are you trying to compile for?

--
E


On Mon, Mar 20, 2017 at 4:24 PM, Jaime Stuardo  wrote:

> Hello,
>
> I have finally given up. After almost all day trying to compile SQLite
> amalgamated source code files for ARM platform I got the conclusion that
> SQLite is not for ARM platform, even when documentation says the contrary.
> I have VS 2008 and I tried to compile some C++ wrappers out there that
> contains amalgamated files but with no success
>
> I tried several command line parameters, but nothing.
>
> Last try was, by using only amalgamated files (shell.c, sqlite3.c,
> sqlite3.h, sqlite3ext.h). There is no science with this. I open a VS2008
> command prompt and VS2012 ARM Command prompt and compile with:
>
> "cl sqlite3.c -link -dll -out:sqlite3.dll -machine:ARM"
>
> In both cases, I got this error:
>
> "crtdefs.h(338) : fatal error C1189: #error :  Compiling Desktop
> applications for the ARM platform is not supported."
>
> That took  me to the conclusion about SQLite is only for Desktop
> applications, which is very bad. Is there another alternative or trick to
> make it compile for ARM platform?
>
> Thanks
> Jaime
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Result code 5 from sqlite3_prepare_v2() in WAL mode?

[Eric Sink]

Excellent.  Thanks.

--
E


On Tue, Sep 13, 2016 at 3:05 PM, Richard Hipp <d...@sqlite.org> wrote:

> On 9/13/16, Eric Sink <e...@sourcegear.com> wrote:
> >
> > I can fit this into your explanation:
> >
> > "Another process might have opened the same database with
> > locking_mode=EXCLUSIVE"
> >
> > if I change the word "process" to "thread", and if I assume that
> > sqlite3_open_v2() on a WAL-mode file can [perhaps sometimes] involve an
> > exclusive lock, if even for a short time.
> >
> > Would this be a correct understanding?
>
> It's actually sqlite3_close() that gets the EXCLUSIVE lock for a very
> short time.  The last connection to close on a particular database
> gets an EXCLUSIVE lock on that database while it runs a final
> CHECKPOINT and then deletes the -shm and -wal files.
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Result code 5 from sqlite3_prepare_v2() in WAL mode?

[Eric Sink]

The app had several threads that were frequently doing this:

open the sqlite file
do something
close it

When I changed it to stop opening the file so much and re-use the
connections, the problem went away.

I can fit this into your explanation:

"Another process might have opened the same database with
locking_mode=EXCLUSIVE"

if I change the word "process" to "thread", and if I assume that
sqlite3_open_v2() on a WAL-mode file can [perhaps sometimes] involve an
exclusive lock, if even for a short time.

Would this be a correct understanding?

--
E


On Tue, Sep 13, 2016 at 9:27 AM, Eric Sink <e...@sourcegear.com> wrote:

>
> This is happening in an Android app.  No other process is involved, but
> the filesystem there is weird, so I'm focusing on the third possibility you
> mentioned.
>
> Thanks,
>
> --
> E
>
>
> On Mon, Sep 12, 2016 at 7:52 PM, Richard Hipp <d...@sqlite.org> wrote:
>
>> On 9/12/16, Eric Sink <e...@sourcegear.com> wrote:
>> > OK, this seems like a simple thing, but I'm stuck and looking for
>> > inspiration or clues.
>> >
>> > How can sqlite3_prepare_v2() return SQLITE_BUSY for a simple SELECT
>> > statement when in WAL mode?
>> >
>> > Immediately prior, a sqlite3_exec("BEGIN TRANSACTION") succeeded.
>> >
>> > The failing call is just sqlite3_prepare_v2(), and the SQL passed is
>> > nothing more than
>> >
>> > SELECT (explicit column list) FROM (table) WHERE (pk) = @Id
>> >
>> > So if WAL mode means writers don't block readers, it seems like
>> preparing a
>> > SELECT statement should not give me error code 5.  Ever?
>>
>> Another process might have opened the same database with
>> locking_mode=EXCLUSIVE
>> (https://www.sqlite.org/pragma.html#pragma_locking_mode).  If the
>> database is owned by Chrome or Firefox then that is likely the problem
>> because they both do that.
>>
>> Or, there might have been an abnormal shutdown and some other
>> processes has since opened the database and is now trying to recover.
>> Recovery is done while holding an exclusive lock.
>>
>> Or there might be some issue with your filesystem that is preventing
>> the processing from acquiring locks on the file.
>>
>> --
>> D. Richard Hipp
>> d...@sqlite.org
>> ___
>> sqlite-users mailing list
>> sqlite-users@mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Result code 5 from sqlite3_prepare_v2() in WAL mode?

[Eric Sink]

This is happening in an Android app.  No other process is involved, but the
filesystem there is weird, so I'm focusing on the third possibility you
mentioned.

Thanks,

--
E


On Mon, Sep 12, 2016 at 7:52 PM, Richard Hipp <d...@sqlite.org> wrote:

> On 9/12/16, Eric Sink <e...@sourcegear.com> wrote:
> > OK, this seems like a simple thing, but I'm stuck and looking for
> > inspiration or clues.
> >
> > How can sqlite3_prepare_v2() return SQLITE_BUSY for a simple SELECT
> > statement when in WAL mode?
> >
> > Immediately prior, a sqlite3_exec("BEGIN TRANSACTION") succeeded.
> >
> > The failing call is just sqlite3_prepare_v2(), and the SQL passed is
> > nothing more than
> >
> > SELECT (explicit column list) FROM (table) WHERE (pk) = @Id
> >
> > So if WAL mode means writers don't block readers, it seems like
> preparing a
> > SELECT statement should not give me error code 5.  Ever?
>
> Another process might have opened the same database with
> locking_mode=EXCLUSIVE
> (https://www.sqlite.org/pragma.html#pragma_locking_mode).  If the
> database is owned by Chrome or Firefox then that is likely the problem
> because they both do that.
>
> Or, there might have been an abnormal shutdown and some other
> processes has since opened the database and is now trying to recover.
> Recovery is done while holding an exclusive lock.
>
> Or there might be some issue with your filesystem that is preventing
> the processing from acquiring locks on the file.
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Result code 5 from sqlite3_prepare_v2() in WAL mode?

[Eric Sink]

OK, this seems like a simple thing, but I'm stuck and looking for
inspiration or clues.

How can sqlite3_prepare_v2() return SQLITE_BUSY for a simple SELECT
statement when in WAL mode?

Immediately prior, a sqlite3_exec("BEGIN TRANSACTION") succeeded.

The failing call is just sqlite3_prepare_v2(), and the SQL passed is
nothing more than

SELECT (explicit column list) FROM (table) WHERE (pk) = @Id

So if WAL mode means writers don't block readers, it seems like preparing a
SELECT statement should not give me error code 5.  Ever?

Am I missing something?

Thanks,

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

Re: [sqlite] SQLite in Android N

[Eric Sink]

FWIW, I wrote a blog entry about this issue to shine a bit more light on it:

http://ericsink.com/entries/sqlite_android_n.html

--
E


On Mon, Jun 13, 2016 at 3:51 PM, Richard Hipp <d...@sqlite.org> wrote:

> On 6/13/16, Eric Sink <e...@sourcegear.com> wrote:
> > "Richard (et al), you no doubt have some contact with the Android folks
> at
> > Google.  Do you have any information you can share about these issues?"
> >
> > I don't think I've seen any reply to this question.  It was a yes/no
> > question, so I *could* interpret radio silence as "no".  :-)
>
> I don't have any information on Android N, unfortunately.
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite in Android N

[Eric Sink]

"Richard (et al), you no doubt have some contact with the Android folks at
Google.  Do you have any information you can share about these issues?"

I don't think I've seen any reply to this question.  It was a yes/no
question, so I *could* interpret radio silence as "no".  :-)

But I think I'll cast the line into this water one more time.

Anybody know if the folks at Google/Android are even aware of the
possibility of corrupting a SQLite file by using multiple instances of the
library?

Anybody know if there is any likelihood they will decide to make libsqlite
part of the NDK?

--
E


On Mon, Jun 6, 2016 at 12:26 PM, Eric Sink <e...@sourcegear.com> wrote:

>
> It is my understanding that Android N will no longer allow apps to use the
> system-installed SQLite library (unless they go through the Android Java
> API, android.database.sqlite).
>
> This is unfortunate, as many existing Android apps do access libsqlite3
> directly and will crash on Android N.
>
> Apps which ONLY use the Java API will be unaffected.
>
> Apps that do NOT use the Java API will need to bundle their own SQLite
> library, if they are not doing so already.
>
> And then, there is a particularly unfortunate group:  Apps which do some
> of their SQLite operations through the Java API, but also do some things
> through direct access to libsqlite.
>
> Because of the POSIX locking issues, these apps cannot just bundle a
> SQLite and let the two sections of their code each use their own instance
> of the library.  The result will be a corrupt SQLite file.  So the only
> obvious fix for apps in this situation is to change their code to "ONLY use
> the Java API" or "NOT use the Java API".
>
> Of course, another option would be for Google to reconsider and make
> libsqlite part of the NDK public APIs.  :-)
>
> Richard (et al), you no doubt have some contact with the Android folks at
> Google.  Do you have any information you can share about these issues?
>
> Thanks,
>
> --
> E
>
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite in Android N

[Eric Sink]

Official, but slightly vague:

https://developer.android.com/preview/behavior-changes.html#ndk

>From Xamarin:

https://developer.xamarin.com/releases/android/xamarin.android_6/xamarin.android_6.0/

--
E


On Mon, Jun 6, 2016 at 12:40 PM, Jason H  wrote:

> > It is my understanding that Android N will no longer allow apps to use
> the
> > system-installed SQLite library (unless they go through the Android Java
> > API, android.database.sqlite).
> >
> > This is unfortunate, as many existing Android apps do access libsqlite3
> > directly and will crash on Android N.
> >
> > Apps which ONLY use the Java API will be unaffected.
> >
> > Apps that do NOT use the Java API will need to bundle their own SQLite
> > library, if they are not doing so already.
> >
> > And then, there is a particularly unfortunate group:  Apps which do some
> of
> > their SQLite operations through the Java API, but also do some things
> > through direct access to libsqlite.
> >
> > Because of the POSIX locking issues, these apps cannot just bundle a
> SQLite
> > and let the two sections of their code each use their own instance of the
> > library.  The result will be a corrupt SQLite file.  So the only obvious
> > fix for apps in this situation is to change their code to "ONLY use the
> > Java API" or "NOT use the Java API".
> >
> > Of course, another option would be for Google to reconsider and make
> > libsqlite part of the NDK public APIs.  :-)
>
> This would be problematic for me too. Is there a citation?
>
> Having used the Android API and the C API in the same app, I have to
> conclude that the Android API corrupts databases at a prolific rate. The
> Android API is also rather stupid, forcing the use of
> stupid "convenience" APIs like  update(java.lang.String table,
> android.content.ContentValues values, java.lang.String whereClause,
> java.lang.String[] whereArgs). (I was never able to use an UPDATE statement
> and not have things break.)
>
> I hope Google comes to their senses, or provides a proper interface.
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] SQLite in Android N

[Eric Sink]

It is my understanding that Android N will no longer allow apps to use the
system-installed SQLite library (unless they go through the Android Java
API, android.database.sqlite).

This is unfortunate, as many existing Android apps do access libsqlite3
directly and will crash on Android N.

Apps which ONLY use the Java API will be unaffected.

Apps that do NOT use the Java API will need to bundle their own SQLite
library, if they are not doing so already.

And then, there is a particularly unfortunate group:  Apps which do some of
their SQLite operations through the Java API, but also do some things
through direct access to libsqlite.

Because of the POSIX locking issues, these apps cannot just bundle a SQLite
and let the two sections of their code each use their own instance of the
library.  The result will be a corrupt SQLite file.  So the only obvious
fix for apps in this situation is to change their code to "ONLY use the
Java API" or "NOT use the Java API".

Of course, another option would be for Google to reconsider and make
libsqlite part of the NDK public APIs.  :-)

Richard (et al), you no doubt have some contact with the Android folks at
Google.  Do you have any information you can share about these issues?

Thanks,

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

[sqlite] Performance comparison between SQLite and SQL Server?

[Eric Sink]

Just for fun:

I know a friend who has a Ferrari.  It is faster than my Ford F-150.

Unless we are racing with both vehicles pulling a 7,000 pound trailer
uphill.  Then I would probably win.

Thousand-mile trip?  Take a sports car.

Moving a couch a thousand miles?  Use a pickup truck.

SQLite is kinda like a sports car.

SQL Server is kinda like a pickup truck.

And this car metaphor of mine is kinda like a motorcycle -- if you lean on
it too hard, it'll probably fall over.

--
E


On Mon, Feb 15, 2016 at 5:24 PM, Michael Falconer <
michael.j.falconer at gmail.com> wrote:

> Good thread,
>
> which absolutely nails the point 'dev decisions for app cases' make a
> developers world go round. I personally couldn't think of a greater waste
> of time than a benchmark comparison between client server rdbms's and
> sqlite. Do what benefits your case most. The above from Jim pretty much
> encapsulates my thoughts:
>
> "SQLite is not directly comparable to client/server SQL database engines
> > such as MySQL, Oracle, PostgreSQL, or SQL Server since SQLite is trying
> to
> > solve a different problem.   Client/server SQL database engines strive to
> > implement a shared repository of enterprise data. ...SQLite strives to
> > provide local data storage for individual applications and devices."
> >
>
> I could bang on about my own preferences and decisions I've made but they'd
> only be reiterating the points made above. They were based on system
> requirement specs and where local storage was involved it was a blindingly
> obvious decision to go with sqlite. Rob above made another excellent point
> often overlooked (usually an afterthought for many dev's):
>
> 4. The support is top notch. I have brought and paid for govt scale
> > databases for governments and to be honest the support for SQLite is just
> > as good, and to be honest I would say better than Big Red or Big Blue
> (and
> > I used to work for Big Blue).
> >
>
> It is another unique property of a great product. Support is not just
> sqlite specific either (a cop out on many a tech forum) and particularly on
> this list the topics can be rather broad. There is plenty of good quality
> feedback and many a good general SQL solution which just adds to the sqlite
> package as a whole.
>
>
> On 16 February 2016 at 09:42, Jim Callahan  >
> wrote:
>
> > SQLite would be most comparable to *SQL Server Express LocalDB* edition
> > which is introduced in this July 2011 blog post
> >
> >
> https://blogs.msdn.microsoft.com/sqlexpress/2011/07/12/introducing-localdb-an-improved-sql-express/
> >
> > More uptodate information about *SQL Server Express LocalDB* edition
> > is in this 2016 Microsoft Developer's Network (MSDN) article
> > https://msdn.microsoft.com/en-us/library/hh510202.aspx
> >
> > This page "*Appropriate Uses for SQLite*" (whentouse.html) describes BOTH
> > "*Situations Where SQLite Works Well*"
> >
> > and
> >
> > "*Situations Where A Client/Server RDBMS May Work Better*"
> > http://sqlite.org/whentouse.html
> >
> >
> > Opening lines of whentouse.html:
> >
> > "SQLite is not directly comparable to client/server SQL database engines
> > such as MySQL, Oracle, PostgreSQL, or SQL Server since SQLite is trying
> to
> > solve a different problem.   Client/server SQL database engines strive to
> > implement a shared repository of enterprise data. ...SQLite strives to
> > provide local data storage for individual applications and devices."
> >
> > Even Microsoft has adopted SQLite for some limited tasks (such as storing
> > state) within every shipping copy of Windows 10.
> > "SQLite is a unique case: it is an open source, externally developed
> > software that is used by core system components, and our flagship apps
> like
> > Cortana and Skype.  ...After shipping SQLite as a system component in
> July,
> > we wanted to include it in our SDK for November. With more than 20,000
> > Windows Apps and more than half of our top apps using SQLite, it made
> sense
> > to just make expose the system SQLite to app developers."
> > http://engineering.microsoft.com/2015/10/29/sqlite-in-windows-10/
> >
> >
> > There is a historical and unfair (specially compiled version of SQLite
> > against default settings of PostgreSQL) benchmark
> > available on this page, but now that you understand the use cases, this
> > particular benchmark is not that useful in addition
> > to being out of date and unfair.
> > https://www.sqlite.org/speed.html
> >
> > Jim Callahan
> > Data Scientist
> > https://www.linkedin.com/in/jamesbcallahan
> > Orlando, FL
> >
> > On Mon, Feb 15, 2016 at 4:54 PM, Simon Slavin 
> > wrote:
> >
> > >
> > > On 15 Feb 2016, at 9:41pm, James K. Lowden 
> > > wrote:
> > >
> > > > SQL Server has none of those restrictions, and probably keeps pace
> with
> > > > SQLite even on its home turf.  But the administration of SQL Server
> is
> > > > nontrivial.  For that reason alone, I would never use it in
> situations
> > > > where SQLite would do.
> > >
> > > That's the 

[sqlite] SQLite database becomes corrupt on iOS

[Eric Sink]

https://www.sqlite.org/howtocorrupt.html

I know you said you already checked this, so just ignore the following
remark:

iOS is one of the easiest platforms to accidentally end up with "Multiple
copies of SQLite linked into the same application".

Just sayin'.

--
E


On Thu, Aug 13, 2015 at 2:04 PM, Simon Slavin  wrote:

>
> On 13 Aug 2015, at 6:32pm, skywind mailing lists 
> wrote:
>
> > Before my app closes I close the database explicitly. Though I do not
> know if this happens always when the iDevice shuts down due to battery
> issues.
>
> iDevices shut down quite a time before they'd actually run out of power.
> Before your device shuts down your App will get notified that it's going to
> background, and then get notified that it's going to quit.  If you're
> handling those two notifications properly (i.e. closing all SQLite
> connections at one or the other) then you should not be getting database
> corruption.
>
> Things to check are running your App inside some sort of memory logger,
> e.g. valgrind .  I don't know if this is possible on the Xcode simulator, I
> think it's built in as a 'Tool'.  And also to check the values returned
> from /all/ your SQLite calls, even ones like _close() where if they fail
> there's nothing you can do about it.  If one of them is not SQLITE_OK then
> you should show an error message.  The call which corrupts the database is
> sometimes an apparently harmless call.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] What software is deployed more than SQLite?

[Eric Sink]

Last time I asked myself this question, I ended up in the same place you
did:  zlib, libpng and libjpeg may be the only candidates in the same
ballpark as SQLite.

See also:

https://news.ycombinator.com/item?id=4616740

--
E


On Sun, May 3, 2015 at 1:18 PM, Richard Hipp  wrote:

> I'm trying to update the "Most Deployed Database" page
> (https://www.sqlite.org/mostdeployed.html) in the SQLite documentation
> (which has not been touched in close to a decade) and I began to
> wonder what other software libraries (database or otherwise) might be
> deployed more than SQLite.  SQLite is in a lot of things.  My
> conjecture is that SQLite is in the top-10 most deployed software
> components in the world today.  Check my work, please.  SQLite is in:
>
>   *  Every Android phone and device
>   *  Every iPhone and iOS device
>   *  Every Mac within the past 10 years
>   *  Every Firefox, Chrome, or Safari browser
>   *  Every copy of Skype
>   *  Every copy of iTunes
>   *  Most Python and PHP installations
>   *  Every Dropbox client
>   *  Every TurboTax and QuickBooks
>
> And more.  But just from the list above, there are not too many
> computing devices that omit SQLite.  I'm wonder what other software
> components have a greater reach?
>
> The original Jean-loup Gailly and Mark Adler implementation of the
> zlib compression library might be deployed more.  Anything else?
>
> What about libjpeg and libpng?  I think there might be multiple
> independent implementations of libjpeg in circulation, but I am
> unclear on that point - perhaps a reader more knowledgable about this
> can correct me.  What about libpng?  Is there just the one original
> libpng library used everywhere, or are there competing
> implementations?
>
> There appear to be more deployments of SQLite than there are of Linux,
> since SQLite is on every Android device, and Android represents the
> bulk of Linux deployments.  SQLite is also on most Linux desktops by
> virtue of being included with Firefox and Chrome.  And it is on many
> Linux servers by virtue of being included in Python and PHP.  Some
> fraction of Linux machines may omit SQLite, but that fraction seems
> far smaller than (say) the number of iPhones that include SQLite, so
> SQLite still comes out numerically superior.
>
> There appear to be more deployments of SQLite than all Apple-built
> computing devices, since SQLite seems to be in all Apple products and
> SQLite is in many other products as well.
>
> SQLite is not in default Windows installations (historically - that is
> about to change with Windows 10 which uses SQLite as a core OS
> component) but many Windows desktops will include secondary software
> such as Firefox or Chrome or iTunes or Skype or Dropbox or something
> else that contains SQLite.  So perhaps most Windows desktops contain
> at least one copy of SQLite.  And in any event, I hear that the total
> number of smartphones now exceeds the total number of desktops (of any
> type, Windows or otherwise) and SQLite is in all of the smartphones.
>
> There are multiple competing implementation of libc, and (unless I am
> mistaken) Android and MacOS/iOS use completely independent libc
> implementations.  You could argue that various implementations of libc
> are collectively more widely deployed than SQLite.  But there is only
> one implementation of SQLite, so if we talk about single
> implementations rather than competing implementations  of the same
> interface, then SQLite seems to still come out on top.
>
> What am I overlooking?  Would it be overly brash to claim that SQLite
> is the second most widely deployed software component in the world
> today, after the Gailly/Adler zlib implementation?  Or maybe the
> third-most after zlib and libpng?
>
> Any input you can provide is appreciated!
> --
> D. Richard Hipp
> drh at sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

Re: [sqlite] MS open source Portable Class Library for SQLite

[Eric Sink]

I would describe the msopentech PCL wrapper as "slightly thick" (because
it's trying to be higher level than the sqlite3 API itself), and "quite
narrow" (because it covers only a small part of the underlying sqlite3 API).

Related and possibly of interest:  My own C# wrapper is a fork of the one
from msopentech.  It's called SQLitePCL.raw.

The README on the github page contains a bunch of info about my wrapper and
its differences:

https://github.com/ericsink/SQLitePCL.raw

There are some NuGet packages:

https://www.nuget.org/packages/SQLitePCL.raw_basic/

https://www.nuget.org/packages/SQLitePCL.raw_needy/

SQLitePCL.raw is currently being used by:

The PCL version of the SQLite-net (a lightweight ORM by Frank Krueger)
NuGet package:

https://www.nuget.org/packages/sqlite-net-pcl/1.0.9-alpha

And Akavache:

https://github.com/akavache/Akavache

And I've been trying to clear the obstacles so that SQLitePCL.raw could
maybe be used by Entity Framework 7:

https://github.com/aspnet/DataCommon.SQLite/issues/21

--
E



On Thu, Aug 14, 2014 at 3:41 PM, Simon Slavin  wrote:

>
> On 14 Aug 2014, at 9:14pm, a...@zator.com wrote:
>
> > May be that some know it already, but digging the Web to know about the
> client-side storage Web SQL Database, I've found this post of MS Open
> Technologies, that may be of interest to many of you who often ask about
> SQLite in Window Phone.
> >
> >
> http://msopentech.com/blog/2014/02/03/new-open-source-portable-class-library-sqlite/
>
> Thanks for the news, Adolfo.
>
> Would someone familiar with programming for this platform tell use how
> thick or thin this wrapper is ?
>
> 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] Cross-Platform ADO wrapper for iOS, Android and Win 8.1 WP 8.1?

[Eric Sink]

Short answer: no.

Longer answer:

Microsoft does not (yet?) support ADO on WinRT or WP8, much less on Xamarin
platforms.

But it looks like the future holds a glimmer of hope.  Their vNext project
seems to be heading toward portable ADO, and also includes SQLite support:

https://github.com/aspnet/DataCommon.SQLite

--
E


On Monday, August 11, 2014, Ken Wenyon  wrote:

> Is there ADO Support for SQLite using Windows 8.1 and Windows Phone 8.1?
>  I am looking for a Cross-Platform ADO wrapper for iOS, Android and Win 8.1
> WP 8.1?
>
>
> Ken Wenyon
> ___
> 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] SQLite version 3.8.6 coming soon

[Eric Sink]

FWIW, the Zumero test suite is fairly abusive and it passes all test cases
with 3.8.6 beta.

--
E



On Thu, Aug 7, 2014 at 11:59 AM, Richard Hipp  wrote:

> On Thu, Aug 7, 2014 at 10:01 AM, E.Pasma  wrote:
>
> > I have a case where a primary key index is no longer used where it was
> > used before.
> >
>
> Thank you for the test case!
>
> This problem should now be addressed on trunk and in the pre-release
> snapshots.  Please retry using the latest and let me know if you continue
> to see problems or if you see anything new.  Thanks.
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> 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] SQLite3_create_collation

[Eric Sink]

Does this mean all your interaction with SQLite is happening through Core
Data?

E
 On Apr 2, 2014 1:05 PM, "Donald Steele"  wrote:

> I am working in iOS (aka ObjC) so I am using it's built in framework for
> all my SQLite calls.
>
>
> On Apr 2, 2014, at 10:58 AM, Simon Slavin  wrote:
>
> >
> > On 2 Apr 2014, at 6:56pm, Donald Steele  wrote:
> >
> >> Registering of the custom collation is far from intuitive (for me) and
> thus the reason for my question.
> >
> > What programming language is your collation function written in ?
> >
> > How are you calling your SQLite functions ?  Are you doing C calls to
> the C API, or are you calling a SQLite library or framework from another
> language ?
> >
> > 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
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Should System.Data.SQLite.EF6.SQLiteProviderServices be public?

[Eric Sink]

After reading this:

http://msdn.microsoft.com/en-us/data/jj680699.aspx

about Code-based Configuration in EF6, I wonder if

System.Data.SQLite.EF6.SQLiteProviderServices

needs to be public (instead of internal), so that people could do something 
like this:

public class MyConfiguration : DbConfiguration
{
public MyConfiguration()
{
this.SetProviderServices("System.Data.SQLite", 
System.Data.SQLite.EF6.SQLiteProviderServices.Instance);
}
}

--
E

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

Re: [sqlite] to encrypt sqlite db

[Eric Sink]

SQLCipher is free.

But it's not compatible with WinRT.

I'm pretty sure that right now there is nothing that meets both of your 
requirements.

--
E


On Aug 31, 2013, at 8:52 AM, dd  wrote:

> Thank you for your quick response.
> 
> I am looking for freeware. If freeware not available, I have to implement
> encryption support for sqlite on winrt.
> 
> What is the procedure to implement encryption support on winrt?
> 
> Thanks,
> dd
> 
> 
> On Sat, Aug 31, 2013 at 6:34 PM, Stephan Beal  wrote:
> 
>> On Sat, Aug 31, 2013 at 2:59 PM, Mohit Sindhwani  wrote:
>> 
>>> Adding on to Paolo's answer, see this: http://www.hwaci.com/sw/**
>>> sqlite/prosupport.html 
>>> See SEE and CEROD on that page.
>> 
>> @devs: minor typo on that page:
>> 
>> "The SQLite software free and it works great."
>> 
>> missing "is"
>> 
>> --
>> - stephan beal
>> http://wanderinghorse.net/home/stephan/
>> http://gplus.to/sgbeal
>> ___
>> 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-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite patch contribution

[Eric Sink]

You could include us as another group who would like to see this functionality 
added to virtual tables.

--
E


On Jul 22, 2013, at 8:46 AM, Richard Hipp  wrote:

> On Mon, Jul 22, 2013 at 10:27 AM, Dušan Paulovič  wrote:
> 
>> So, in other words, there is no way for programmers from EU to contribute
>> on SQLite?
>> Is there any other option?
>> Is there a plan to solve xBestIndex collation issue?
> 
> Actually implementing such a patch is trivial.  We'll do that for you.  Not
> a problem.
> 
> The real work comes in (1) testing the new features (2) documenting the new
> feature, and especially (3) supporting the new feature moving forward.
> These three items, and especially the third item, involve orders of
> magnitude more time and effort than actually implementing the patch.
> 
> So if having the ability to use collating sequences in virtual tables is
> something that is important to you, then you have to sell the idea.  You
> have to convince the core team that your new feature is important and
> useful and ought to be supported.
> 
> Submitting a "proof of concept" patch might be part of your sales pitch, as
> a way of showing that the feature is feasible and does not impact
> performance and will be easy to support.  But the patch is only a small
> part of your sales pitch.  You still need to convince the core team that
> what you are trying to do is necessary and important and will be of benefit
> to a large number of users.
> 
> 
> -- 
> D. Richard Hipp
> d...@sqlite.org
> ___
> 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] SQLite database on Dropbox, Google Drive, MS SkyDrive, Ubuntu One or SAMBA share

[Eric Sink]

FYI:  For a different approach, check out Zumero (http://zumero.com) which 
synchronizes SQLite changes within the db rather than the whole db as a file.

Disclosure:  I am a co-founder of Zumero, which is commercial, proprietary, 
non-open-source, etc.  I mention this only because we often describe Zumero as 
"like Dropbox, except for data instead of files", so it might be relevant for 
the participants of this thread.  Apologies if this note is out of line.  Feel 
free to ignore me.  If your initials are DRH, feel free to scold me.

--
E


On Jul 3, 2013, at 9:27 AM, Gerry Snyder  wrote:

> On 6/27/2013 12:38 PM, joe.fis...@tanguaylab.com wrote:
>> Anyone,
>> 
>> Does anyone have good or bad experiences using a SQLite database in a shared 
>> folder?
>> The 'Dropbox / Drive / SkyDrive / One' 
> 
> I use Dropbox for SQLite files a lot. If I am not careful to be making 
> changes to a file on only one PC at a time, I can get a "Conflicted copy" on 
> one of the machines, but by and large it has worked very well.
> 
> I can not think of any way Dropbox could be handling things better.
> 
> 
> Gerry
> ___
> 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] BEGIN IMMEDIATE and the busy handler

[Eric Sink]

From reading sqlite3.c and the comments therein, it would appear that 
BEGIN IMMEDIATE TRANSACTION never invokes the busy handler.


Is that correct?

--
E

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

[sqlite] System.Data.SQLite and sqlite3_prepare()

[Eric Sink]

It looks like System.Data.SQLite uses sqlite3_prepare() instead of 
sqlite3_prepare_v2().


Is there a technical reason for this?

Just curious.

--
E

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

[sqlite] Announcing Zumero (commercial venture, built on SQLite)

[Eric Sink]

I'll keep this short and low-key:

Zumero is a sync solution for SQLite, designed for mobile devices.

http://zumero.com/

Here's my blog post on it:

http://ericsink.com/entries/announcing_zumero.html

--
E


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

Re: [sqlite] SQLite 3.7.16 beta

[Eric Sink]

FWIW:  I am working on a project which uses SQLite extensively.  We 
generally use 3.7.11 on our client side and 3.7.15.2 on our server. I 
just switched both to this 3.7.16 beta, and all our automated tests 
still pass.


--
E


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

[sqlite] Maybe bug with vtab xDestroy

[Eric Sink]

In xRename, if I set zErrMsg and return SQLITE_ERROR, the error message 
gets through.


In xDestroy, using the same code, the error message seems to get eaten.

Bug?

My code:

static int my_Destroy(sqlite3_vtab *tab)
{
struct my_vtab* pvtab = (struct my_vtab*) tab;
if (pvtab->zErrMsg) sqlite3_free(pvtab->zErrMsg);
pvtab->zErrMsg = sqlite3_mprintf("whatever");
return SQLITE_ERROR;
}

This is with 3.7.15.2

--
E


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

Re: [sqlite] Return Value from sqlite3_exec()

[Eric Sink]

In your example, the only way SQLite can do what you expect is to notice 
that your UPDATE didn't modify any rows.


But you don't want an UPDATE statement throwing errors simply because it 
didn't modify any rows.  There are many situations where that happens 
and is considered normal behavior.


UPDATE table1 SET site='Site1' WHERE site='garbage';

In your example, you know what garbage looks like.  SQLite does not.  
Mere absence of that value from the column does not qualify it as garbage.


OTOH, I do sometimes like to put this line (or something like it):

assert(1 == sqlite3_changes(db));

after an UPDATE when I know that it should have changed exactly one row.

--
E


On 2/22/13 3:15 PM, Frederick Wasti wrote:

[A "newbie to SQLite" here...]

>From the documentation on sqlite3_exec(), it seems as if it should return
SQLITE_OK (=0) upon processing a successful SQL query. However, I was a
bit surprised to see that an SQL statement such as "UPDATE table1 SET
site='Site1' WHERE site='garbage' (where garbage really is garbage, as in
not being present in the database) results in an SQLITE_OK return.

I guess there are two ways of looking at this: On the one hand, the SQL
statement cannot succeed, so the return should not be SQLITE_OK. On the
other hand, an SQL statement which would be impossible to process, but
which would nonetheless be handled with aplomb by sqlite3_exec(), should
return SQLITE_OK after all (in the sense that sqlite3_exec() did do its job
OK). (???)

So, my question is: Is it correct for sqlite3_exec() to return SQLITE_OK if
the SQL query is doomed to failure (but is otherwise properly formed)?

Thanks.

Fred

___
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] From SQLITE_CONSTRAINT to something more specific

[Eric Sink]

I'm trying to use information from sqlite3_errmsg() to figure out what 
*kind* of SQLITE_CONSTRAINT happened.


Cruising the archives of this mailing list, I see past discussions about 
the desire for really complete error information, including the name of 
the specific constraint that failed (and ideally in a format which does 
not need to be parsed or is designed to be parsed).  I add my vote for 
how cool that functionality would be. For now, I'm just looking for a 
way to distinguish between the major types of constraint violations, 
(unique, check, foreign key, etc).


I would welcome remarks from the SQLite developers on the code snippet 
below.


Specific questions:

(1)  What is the likelihood of future changes to the phrasing of 
sqlite3_errmsg() strings?


(2)  Is there any situation where (0 == strcmp(sqlite3_errmsg(db), 
"constraint failed")) and the violation was NOT a CHECK constraint?


(3)  Dare I hold out hope for extended error codes like the following?

#define SQLITE_CONSTRAINT_UNIQUE  (SQLITE_CONSTRAINT | (1<<8) )
#define SQLITE_CONSTRAINT_FOREIGN_KEY (SQLITE_CONSTRAINT | (2<<8) )
#define SQLITE_CONSTRAINT_CHECK   (SQLITE_CONSTRAINT | (3<<8) )
#define SQLITE_CONSTRAINT_NOT_NULL(SQLITE_CONSTRAINT | (4<<8) )

My code snippet:


if (SQLITE_CONSTRAINT == rc)
{
const char* psz_errmsg = sqlite3_errmsg(psql);

// We're on thin ice here.  sqlite doesn't make any promises about
// what the errmsg string will contain for various constraint 
violations.
// Nonetheless, this works.  If sqlite changes the errmsgs in the 
future,

// this will break.  We've got test cases to detect this.

if (strstr(psz_errmsg, "not unique"))
{
// TODO should occur at the end of the string
...
}
else if (strstr(psz_errmsg, "foreign key"))
{
// TODO could check for exact strcmp match to "foreign key 
constraint failed"


...
}
else if (strstr(psz_errmsg, "constraint failed"))
{
// TODO could check for exact strcmp match to "constraint failed"
// TODO riskiest one, since sqlite doesn't say it was a check 
constraint
// TODO but it seems to use a more specific phrase in all other 
cases except check constraints


...
}
}


Thanks!

--
E

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

Re: [sqlite] sqlite3_initialize() before sqlite3_randomness()

[Eric Sink]

Yeah, I did read that page, but not well enough.

I saw this:

"Workstation applications using SQLite normally do not need to invoke 
either of these routines."


And this (all-caps emphasis mine):

"The sqlite3_initialize() routine is called internally by MANY other 
SQLite interfaces so that an application usually does not need to invoke 
sqlite3_initialize() directly."


But I missed this:

"it is recommended that applications always invoke sqlite3_initialize() 
directly prior to using any other SQLite interface"


So I ended up thinking maybe the current behavior was unknown or unintended.

Anyway.  Nothing to see here...

--
E




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

[sqlite] sqlite3_initialize() before sqlite3_randomness()

[Eric Sink]

So it appears that if the very first SQLite function you call is 
sqlite3_randomness(), it crashes.


And if you call sqlite3_initialize() first, it does not crash.

Just thought I should let somebody know...

--
E

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

Re: [sqlite] Two-line patch to fix memory leak

[Eric Sink]

Oh darn.

If the leak is not obvious to you, and if my patch would cause an undesirable 
side effect, then the leak may somehow be the fault of our code.

I'll have to dig into this a bit further.  Sending you our whole tree would be 
somewhat less than constructive.  Maybe I can narrow down to a small test case.

For now, I can confirm that we have made no other changes to our copy of 3.7.11.

Thanks,

--
E


On May 11, 2012, at 12:50 PM, Dan Kennedy <danielk1...@gmail.com> wrote:

> On 05/11/2012 11:28 PM, Eric Sink wrote:
>> 
>> In sqlite3Fts3Matchinfo():
>> 
>> Near the end of the function, I added two lines:
>> 
>> if( rc!=SQLITE_OK ){
>> sqlite3_result_error_code(pContext, rc);
>> }else{
>> int n = pCsr->nMatchinfo * sizeof(u32);
>> sqlite3_result_blob(pContext, pCsr->aMatchinfo, n, SQLITE_TRANSIENT);
>> ADDED: sqlite3_free(pCsr->aMatchinfo);
>> ADDED: pCsr->aMatchinfo = 0;
>> }
> 
> Thanks for posting this.
> 
> Looking at the code, I can't see how the memory leak occurs. Do
> you have any idea how to trigger it? Other than this patch, are
> you using unmodified 3.7.11 FTS code?
> 
> We can't apply the patch as is, even though it is safe (does not
> introduce any bugs), because it makes queries that use the 'x'
> format specifier with matchinfo() much less efficient.
> 
> Dan.
> ___
> 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] Two-line patch to fix memory leak

[Eric Sink]

In sqlite3Fts3Matchinfo():

Near the end of the function, I added two lines:

  if( rc!=SQLITE_OK ){
sqlite3_result_error_code(pContext, rc);
  }else{
int n = pCsr->nMatchinfo * sizeof(u32);
sqlite3_result_blob(pContext, pCsr->aMatchinfo, n, SQLITE_TRANSIENT);
ADDED:sqlite3_free(pCsr->aMatchinfo);
ADDED:   pCsr->aMatchinfo = 0;
  }

Notes:

1.  I added these two lines while obtaining zero actual understanding of 
the internals of sqlite.  Like a monkey typing random characters, I 
opened vi and hit keys until the leak went away.  I have no reason to 
believe these lines are correct.  All I know is that our test suite 
shows some memory leaks unless these two lines are present.


2.  I first did this fix back when 3.7.5 was released.  And then I 
procrastinated about submitting the patch to the mailing list, because I 
wanted to wait until I could say something insightful about sqlite 
internals, to make me look smarter than the aforementioned monkey.  
Anyway, we've got over a year of experience running with "sqlite 3.7.5 
plus this patch", and no problems.


3.  This week we upgraded to 3.7.11 and the leaks showed up again.  So I 
reapplied this little patch, and the leaks went away again.  And this 
time, I have decided not to let my cluelessness prevent me from letting 
you folks know about it.


So, FWIW, there it is.  :-)

--
E

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

Re: [sqlite] Details on New Features

[Eric Sink]

Is this new syntax likely to perform any better than the traditional way of 
writing the query?

--
E


On May 4, 2012, at 11:42 AM, Nico Williams  wrote:

> On Fri, May 4, 2012 at 9:20 AM, Richard Hipp  wrote:
>>> Queries of the form: "SELECT max(x), y FROM table" returns the
>>> value of y on the same row that contains the maximum x value.
>>> 
>>> Is that standard SQL behavior?  I'd have expected that to return one row
>>> for every row in the table.  To get the behavior described above, I'd use
>>> "SELECT x, y FROM table WHERE x = (SELECT max(x) FROM table)".
>> 
>> It is definitely NOT standard behavior.  The standard behavior is
>> undefined.  Or (with many SQL engines) it will throw an error if you have a
>> term in the result set that is not part of an aggregate function or an
>> element of the GROUP BY clause.  But lots of newbies expect SQL to work as
>> described in the 3.7.11 release comments, and we used to get support
>> questions because it did not.  And so rather than continue to answer the
>> questions over and over, I figured it would be easier to tweak SQLite to
>> reliably do what newbies expect.  I never anticipated that this change
>> would be so controversial or confusing.
> 
> This is very clever.  I'm not sure that an informative error message
> wouldn't have been better, but I think you made the right choice given
> SQLite3's previous behavior.  A pragma by which to cause SQLite3 to
> return an error instead might be useful, but then, it's SQL_Lite_.
> 
> Is there any way to define aggregate functions that pick a row for
> providing column values in non-aggregate expressions?  E.g., you could
> have a median() or mode(), no?  I don't think this is important, am
> just curious.
> 
> Nico
> --
> ___
> 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