[sqlite] Sqlite incompatibility with Postgres

[J Decker]

Someone should wake the SQL standard committee and nominate sqlite
being the broadest used so should fill some gaps in specification with
it. :)  and conversely blame the others for their incompatibility :)

[sqlite] Sqlite incompatibility with Postgres

[Darren Duncan]

On 2016-05-14 11:30 AM, James K. Lowden wrote:
> I suggest the reason LIMIT hasn't been standardized is that it's
> contrary to the fundamental idea that rows in a table have no
> meaningful order.  SQL doesn't honor relational theory with complete
> fidelity, but at least that horse is still in the barn.
>
> The problem with LIMIT is it's not based in the data.  Cutting off
> results at some arbitrary N tells you *nothing* about the data other
> than that N or more rows met the criteria.  Note that predicate logic
> has constructs for "for all" and "there exists" , but not "are some"!

You seem to be forgetting the fact that LIMIT/OFFSET is not its own clause, 
rather it is an extension to the ORDER BY clause and only has meaning within 
the 
context of the ORDER BY it is part of.

That being said, one could argue that LIMIT by itself (no offset) could be 
standalone, but then without an ORDER BY all say "LIMIT N" means is "give me a 
random subset of size N of the rows", but then there probably is alternate 
syntax that may say this more explicitly, eg "PICK N".

-- Darren Duncan

[sqlite] Podcast with Dr Hipp: SQLite history, success and funding

[Simon Slavin]

Those interested in SQLite might like to listen to



Play on the page or download as an MP3.

Unusual information on Dr Hipp's early career, SQLite history, HWACI, and how 
come SQLite is free but the developers still manage to afford food and 
somewhere to sleep.

Question to ponder before you listen: Many of you know about tiny devices which 
incorporate SQLite but what do you think the biggest one is ?

Simon.

[sqlite] Time & between, midnight wrap around

[R Smith]

On 2016/05/14 8:28 PM, Daniel Polski wrote:
> Hello,
> BETWEEN doesn't give the result I would need when used with time 
> before & after midnight:
>
> SELECT time('23:00') BETWEEN time('22:00') AND time ('23:30');
> time
> 
> 1
>
> sqlite> SELECT time('23:00') BETWEEN time('22:00') AND time ('01:00');
> time
> 
> 0
>
> Any suggestion how to tweak the query to give "true" as the result 
> even with midnight wrap around?

You are asking the wrong thing to check in a wrong boundary. 23:00 is 
definitely NOT in between 22:00 and 01:00, unless you mean 01:00 the 
NEXT day - in which case, the moment you introduce DAY into the 
equation, you need a full date and the time is no longer enough by itself.

SELECT ( datetime( '2016-05-12 23:00:00' ) BETWEEN datetime( '2016-05-12 
22:00:00' ) AND datetime( '2016-05-13 01:00:00' ) ) AS time;
time

1

If you do not care about the dates, you can introduce a dummy date added 
to make things sensible.
Simply adding the standard strings '2000-01-01 ' and '2000-01-02 ' in 
front of any time (with the 02 where the time is past midnight) should 
sort out a fudge that will make things work, with the added 
inconvenience you have to keep track of when you talk about a time that 
is "past midnight" - programmatically easy, but not so easy in queries.

Best of luck!
Ryan

[sqlite] Time & between, midnight wrap around

[Daniel Polski]

Hello,
BETWEEN doesn't give the result I would need when used with time before 
& after midnight:

SELECT time('23:00') BETWEEN time('22:00') AND time ('23:30');
time

1

sqlite> SELECT time('23:00') BETWEEN time('22:00') AND time ('01:00');
time

0

Any suggestion how to tweak the query to give "true" as the result even 
with midnight wrap around?

[sqlite] Podcast with Dr Hipp: SQLite history, success and funding

[Stephen Chrzanowski]

Just finished listening to it as well.

Dr Hipp, I know you don't like to toot your own horn, but, really, I'd
really like to hear where and when you're talking to other people about
SQLite and your other projects.  Really interesting info.


On Sat, May 14, 2016 at 5:49 PM, Steve Schow  wrote:

> thanks for letting us know about that, thoroughly enjoyed listening?.
>
>
> On May 14, 2016, at 2:17 PM, Simon Slavin  wrote:
>
> > Those interested in SQLite might like to listen to
> >
> > 
> >
> > Play on the page or download as an MP3.
> >
> > Unusual information on Dr Hipp's early career, SQLite history, HWACI,
> and how come SQLite is free but the developers still manage to afford food
> and somewhere to sleep.
> >
> > Question to ponder before you listen: Many of you know about tiny
> devices which incorporate SQLite but what do you think the biggest one is ?
> >
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] 64bit DLL vs 32bit

[James K. Lowden]

On Thu, 12 May 2016 00:36:31 +1000
"dandl"  wrote:

> But I think if you compile code for the x64 processor chip and call
> it from x86 or vice versa then either it doesn't work or you pay a
> high price for thunking from one to the other. I think that's
> unavoidable regardless of OS.

Right: doesn't work.  There's no performance penalty because there's no
32-64 bit thunking layer.  

https://blogs.msdn.microsoft.com/oldnewthing/20081020-00/?p=20523

--jkl

[sqlite] Podcast with Dr Hipp: SQLite history, success and funding

[Steve Schow]

thanks for letting us know about that, thoroughly enjoyed listening?.


On May 14, 2016, at 2:17 PM, Simon Slavin  wrote:

> Those interested in SQLite might like to listen to
> 
> 
> 
> Play on the page or download as an MP3.
> 
> Unusual information on Dr Hipp's early career, SQLite history, HWACI, and how 
> come SQLite is free but the developers still manage to afford food and 
> somewhere to sleep.
> 
> Question to ponder before you listen: Many of you know about tiny devices 
> which incorporate SQLite but what do you think the biggest one is ?
> 

[sqlite] 64bit DLL vs 32bit

[James K. Lowden]

On Wed, 11 May 2016 11:30:34 +1000
"dandl"  wrote:

> > more about DLLs than it is about SQLite.
> 
> Actually, it's everyone using a language other than C/C++, plus a
> proportion of those too. I use C#, but if you want to call Sqlite
> from Java, Python, etc or even some generic C/C++ app that supports
> plug-ins, then at some point there is a DLL 

How does that follow?  Any higher-than-C language has its own binding
system, and SQLite is a module of some kind, where the C library is
wrapped in the module that exposes its own API.  If the module
statically links in libsqlite3.a -- as, arguably, it should -- then
there's no version ambiguity, no DLL, and no chance of conflict.  

--jkl

[sqlite] Sqlite incompatibility with Postgres

[James K. Lowden]

On Fri, 13 May 2016 15:13:01 +0100
Simon Slavin  wrote:

> On 13 May 2016, at 3:07pm, dandl  wrote:
> 
> > I have no deep knowledge of standard SQL.
> 
> I used to know SQL92 very well.  There's no facility for doing
> anything like LIMIT or OFFSET in it.  You had to use your programming
> language to work your way through all the results and skip the ones
> you didn't want.

I suggest the reason LIMIT hasn't been standardized is that it's
contrary to the fundamental idea that rows in a table have no
meaningful order.  SQL doesn't honor relational theory with complete
fidelity, but at least that horse is still in the barn.   

The problem with LIMIT is it's not based in the data.  Cutting off
results at some arbitrary N tells you *nothing* about the data other
than that N or more rows met the criteria.  Note that predicate logic
has constructs for "for all" and "there exists" , but not "are some"!  

I have yet to see a query using LIMIT 1 posted on this list that cannot
be expressed -- better, IMO -- with min().  Queries that limit the
results to "top N" to support things like pagination inevitably include
assumptions about transactionality (or lack thereof) that are either
invalid or ill-considered.  Every one would be better served either by
just fetching the needed rows as required (and letting pending rows
pend), or by supplying the last "high" value as a minimum for the
WHERE clause instead of an OFFSET.  Were I a fan of conspiracies, I'd
suspect the LIMIT-OFFSET constructs were invented by antilogicians to
prevent learning and hobble performance.  

By the way, i'm also a LIMIT club member, with limits.  I use it for
convenience on the command line while exploring data.  It's great for
that, in the absence of pager support.  Maybe keeping it a little
"weird" will help remind new developers to use it as a convenience
instead of a crutch.  

--jkl

[sqlite] Sqlite incompatibility with Postgres

[dandl]

Just what I needed. Ta muchly!

Regards
David M Bennett FACS

Andl - A New Database Language - andl.org


> -Original Message-
> From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-
> bounces at mailinglists.sqlite.org] On Behalf Of Darren Duncan
> Sent: Saturday, 14 May 2016 6:28 AM
> To: SQLite mailing list 
> Subject: Re: [sqlite] Sqlite incompatibility with Postgres
> 
> On 2016-05-13 7:07 AM, dandl wrote:
> > I checked a copy of the
> > 2003 standard and there doesn't seem to be anything similar. I don't
> > have anything later.
> 
> Whitemarsh is your friend.
> 
> http://www.wiscorp.com/SQLStandards.html
> 
> They have a copy of the SQL 2011/2 draft there, under the erroneous title
> "SQL:20nn Working Draft Documents".
> 
> The actual PDF files are datestamped 2011 Dec 22.
> 
> Unless you need something 100% perfect, those are for all intents and
> purposes the same as the official standard.
> 
> I've relied on the up to date texts of that website for the last 15 years
or
> so.
> 
> -- Darren Duncan
> 
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Summing values by date, returning 0 for missing dates

[R Smith]

On 2016/05/13 4:56 PM, Jonathan Moules wrote:
> Hi Simon,
>Sorry, maybe we're crossing wires, but I'm not sure to what you're 
> referring. How is defining the type as DATE impeding my attempt to get a 
> value of 0 for non-existent rows?
> Ryan's response with a CTE seems to probably be what I want (not had the 
> opportunity to test it yet - CTE's are entirely new to me), but looking at it 
> quickly now I see that the type is actually NUMERIC there rather than my DATE 
> - perhaps that's to what you're referring.

The CTE will work great and there is some literature about CTEs in 
general we could point you to.  If you just want a quick fix for your 
situation, just use my example, if you want to understand CTE in 
general, feel free to ask or google, it's really worth learning since it 
can do some real magic for you.

As to the date type in my CTE example - apologies, I hadn't even noticed 
you had it as DATE, I just used NUMERIC since that's what I always do 
for dates. You can still use it as type DATE and achieve the same 
results with the CTE (I'm 99% sure - haven't tested it).

Your confusion about what Simon said might be that (I think) perhaps 
Simon misunderstood what you found weird about the results in the 
original post and tried to explain why you see that weirdness while you 
were on about a different weirdness - so you are simply not on the same 
page.

Either way, good luck with the implementation. One note: The CTE 
solution will only work after SQLite version 8.3 I think, so if you are 
using a very old version, it might not work.


Cheers,
Ryan

[sqlite] Sqlite incompatibility with Postgres

[dandl]

> It would also be very minor to add "UNION -1" as a synonym for "UNION
ALL",
> but "being minor" is not an argument for doing so.
> While the mentorship of Postgres is undoubted, there is/was never a drive,
> nor a need for full (or even partial) compatibility with "Postgres" per
se,
> mostly care is applied to conform or be compatible with the SQL standard
as
> much as possible (much like PostGres'
> philosophy) - At least this is how I read the Dev's statements thus far.

As I said earlier, I just noticed that, although Richard Hipp had publicly
talked about Sqlite having Postgres compatibility, there is fact no common
subset of SQL dialect for this feature. The Andl Sql generator can handle
it, but I thought it warranted a question for confirmation.

> If you can show that the SQL standard likes the "LIMIT ALL" phrasing, or
> argue that it has in it's own right an advantage over "LIMIT -1", then you
> would have a much better case than just saying "But Postgres does it", and
> then it would make sense even if it isn't very minor.

I have no deep knowledge of standard SQL. This article
https://en.wikipedia.org/wiki/Select_%28SQL%29#FETCH_FIRST_clause is not
particularly helpful but does not list this syntax. I checked a copy of the
2003 standard and there doesn't seem to be anything similar. I don't have
anything later.
> 
> All that said, personally I do like the "LIMIT ALL" for clarity and
wouldn't
> mind seeing it implemented.

I agree, but it would be for Postgres compatibility rather than the
standard, I think.

Regards
David M Bennett FACS

Andl - A New Database Language - andl.org