[sqlite] Sqlite incompatibility with Postgres
> Actually, MIN still is fundamentally a first-order itself. The dyadic > function call "x min y" returns either x or y depending on how they compare. > The list form is then repeated application of the binary min(). This is > directly comparable to your example of list plus/sum which is repetition of > the dyadic "x + y". 1. An ordered data type is one that implements "less than". Other comparisons (LE,GT,GE) are then implemented generically (all data types implement EQ). 2. Yes, MIN(x,y) or x MIN y is a first-order function as described that is well-defined on any ordered data type, and can also be implemented generically using LT. 3. [and BTW MAX(x,y) can also be implemented generically on any ordered type, using LT.] 4. The aggregation function MIN(X) of SQL is a second order function, equivalent to FOLD(MIN,X). That is, the function repeatedly applies the MIN function to pairs of values and returns a single value. 5. All aggregation functions (whether defined in SQL or elsewhere) rely on second order functions of the form FOLD(F,X) where F is the function to be repeatedly applied. Andl implements them as such, with complete genericity. 6. [COUNT is also a second order function, equivalent to FOLD(+,1).] > List MIN is NOT a repeated application of "x less than y". -- Agreed, see above. Regards David M Bennett FACS Andl - A New Database Language - andl.org
[sqlite] Sqlite incompatibility with Postgres
On Fri, 20 May 2016 14:17:25 +1000
"dandl" wrote:
> Every aggregation function is at least second order: a function that
> applies a function to the set. So for MIN the function is 'less
> than', for SUM() the function is 'plus' and so on. In Andl
> aggregation functions are provided by fold(), which takes a function
> as an argument.
I want you to know that you hijacked my Saturday. I was bothered about
what "first order" and "second order" mean, suspecting that we meant
different things. After an afternoon with the Oracle of All Knowledge,
I think we were talking about different things, and you had a better
handle on your end than I did on mine.
I was concerned that we were treading in territory outside first-order
predicate logic. On review, as Wikipedia explains, HOL deals in
another beast, namely the quantification of sets of sets.
You were talking about something much simpler, second-order *functions*.
The input is still a value -- an individual member of a set -- plus
another function. As you say, there are many such in SQL. In keeping
with the language's purpose, the primitive components are not exposed,
so it's not possible to reconstruct min as FOLD(MIN,X). We can do
similar things with subqueries, e.g.
select sum(N) from (select count(*) as N from T group by a) as A
One can imagine that restated as
select F(sum, count, t) from T
where F is defined as taking two functions and a value. I guess that
would make F a third-order function.
APL is instructive in this regard. What we usually call
operators -- + - x ? -- are termed *functions* in APL, in keeping
with their mathematical definition. A function that takes a function
is called an operator. One such is "/", the reduction operator; SUM(t)
could be expressed as
+/t
> > 2. Limit, as currently implemented, lies outside the theory
> > because it doesn't operate on sets.
>
> I'll put that one on hold pending a suitable reference or detailed
> mathematical treatment.
I think I can accept "first(N)" could be a set function, and if SQL
dealt in sets, LIMIT would be a deterministic function. But SQL deals
in bags, and with a couple of odd exceptions -- random(), now() -- all
its functions are determistic. LIMIT is not a deterministic
function. I'm not sure what happens to first order predicate logic in
the face of nondeterminism, but I'm sure it's not good.
> Sorry. Your nth() is a second order function
OK.
> The (single-pass) implementation would maintain a temporary table of
> rows that are 'minimum so far seen', to a maximum of N. It would be an
> implementers decision what to do with a row equal to one in that
> table once N has been reached: add it or ignore it?
nth() acts on a single column; it keeps the set of N smallest values, as
you say. The answer to your question is "ignore it" because a value
equal to one in the set is already a member. Given the input
C {1, 1, 2, 2, 2, 3}
min(C) = 1
nth(C, 1) = {1}
nth(C, 2) = {1, 2}
I'm not claiming any deep insight, only that nth() would be handy and
can be defined mathematically (even if I can't do it).
--jkl
[sqlite] Optimising max(datetime()) queries.
Hi List, I've come across a little curiousity and was wondering what was going on (and if there's an even better way). I have some DATETIME data: 2013-06-03 16:08:55+01:00 2013-06-03 16:46:22+00:00 2013-06-03 16:48:57+00:00 ... I want to get the the start of the day that was 28 days ago from the max() value. I can think of two ways to do this and as best I can tell they both give the same (correct) result: --Get the maximum datetime value (the inner "datetime()" is required so that the max() takes into account the timezones) and then work out the necessary time/date for 28 days ago for the one result. select datetime( max( datetime(my_timedate_column)) , '-28 day' , 'start of day' ) from my_table --Convert all datetimes to the date 28 days ago, then get the maximum from them. select max( datetime(my_timedate_column, '-28 day' , 'start of day' ) ) from my_table As far as I can tell from my non-thorough testing, the first of these is a smidgen (5-10%) faster than the second although the second is easier to read. Logically I can see why the second would be slower, but I was hoping the second would be optimised to the same thing as the first. Is there a reason for this? And is there a even more efficient way to do this? Cheers, Jonathan
[sqlite] SQL logic error or missing database no such table
Hi, We've a .Net Windows Service that uses SQLite 1.0.93.0 for database operations. The SQLite package contains two binaries, system.data.sqlite.dll and sqlite.interop.dll. The database is encrypted and the blob it contains is encrypted as well. SQLite is intermittently throwing following exceptions while retrieving data from the database. - *SQL logic error or missing database no such table*. The code is trying to execute a simple query which looks something like this: SELECT x, y from [tablename] This exception is raised while executing SQLiteCommand.ExecuteReader(). The stack trace points to at System.Data.SQLite.SQLite3.Prepare(SQLiteConnection cnn, String strSql, SQLiteStatement previous, UInt32 timeoutMS, String& strRemain) at System.Data.SQLite.SQLiteCommand.BuildNextCommand() at System.Data.SQLite.SQLiteDataReader.NextResult() * - Data Source cannot be empty. Use :memory: to open an in-memory database- unable to open database file * These two exceptions are raised while trying to open the connection. Any help or guidance would be appreciated. Thank you, Raj
[sqlite] Searching this mailing lsit
On 21 May 2016, at 10:07am, Cecil Westerhof wrote: > 2016-05-21 3:19 GMT+02:00 Kasajian, Kenneth < > Kenneth.Kasajian at schneider-electric.com>: > >> *** Confidentiality Notice: This e-mail, including any associated or >> attached files, is intended solely for the individual or entity to which it >> is addressed. This e-mail is confidential [snip] > > ?I would make sure that the above does not get send to mailing-lists. It is > quite annoying and does not serve any purpose. In the EU they have no effect. It was judged that it was an attempt to form a contract with the receiver of the email without any agreement from them. It can apply only if you've had a previous conversation where they agreed to keep the message secret. But it does do something under US law. It invalidates the text even on messages you /do/ want to be confidential. Because it states "this email is confidential" on a message which clearly isn't. The example used in court featured staff of the Public Relations department who used the same disclaimer as the rest of the company, and routinely sent out Press Releases and messages to the general public with no confidential information in. The judgement was that this showed that the company wasn't using that message only for confidential information, and that that contradicted the text in the message. So under US law the message may have some effect, but only if you use it only on messages that /do/ contain confidential information. If you use it for every message it means you don't mean it. I Am Not A Lawyer. If you want legal advice, consult a lawyer. Simon.
[sqlite] Searching this mailing lsit
2016-05-21 3:19 GMT+02:00 Kasajian, Kenneth < Kenneth.Kasajian at schneider-electric.com>: > *** Confidentiality Notice: This e-mail, including any associated or > attached files, is intended solely for the individual or entity to which it > is addressed. This e-mail is confidential and may well also be legally > privileged. If you have received it in error, you are on notice of its > status. Please notify the sender immediately by reply e-mail and then > delete this message from your system. Please do not copy it or use it for > any purposes, or disclose its contents to any other person. > ?I would make sure that the above does not get send to mailing-lists. It is quite annoying and does not serve any purpose. -- Cecil Westerhof
[sqlite] Searching this mailing lsit
On 21 May 2016 at 10:07, Cecil Westerhof wrote: > 2016-05-21 3:19 GMT+02:00 Kasajian, Kenneth < > Kenneth.Kasajian at schneider-electric.com>: > >> *** Confidentiality Notice: This e-mail, including any associated or ... > ?I would make sure that the above does not get send to mailing-lists. It is > quite annoying and does not serve any purpose. Quite right. I would edit the received mail to get rid of the nonsense, but there are too many mails to do that. I would appreciate it if they didn't appear at all. -- Cheers -- Tim
[sqlite] Multiple line command pasted to shell is split in history
On 20 May 2016, at 17:35, Jaromir Obr wrote: > Expected result: > > SELECT REPLACE(ingredients,'enriched_','') as item_ingredients > FROM baked_goods; > > Actual result: > > FROM baked_goods; > > Note: It works well in MariaDB shell, in the same terminal Either way has its advantages. I find keeping the original line-folding more convenient for both readability and making corrections than having the line wrapped (often splitting a token) at the edge of the window. I can also why you and others might prefer UP/RETURN to UP/UP/RETURN/UP/UP/RETURN. Best regards, Niall O'Reilly
[sqlite] Searching this mailing lsit
> 2016-05-21 3:19 GMT+02:00 Kasajian, Kenneth schneider-electric.com>: > > > *** Confidentiality Notice: This e-mail, including any associated or > > attached files, is intended solely for the individual or entity to which > > it is addressed. This e-mail is confidential and may well also be legally > > privileged. If you have received it in error, you are on notice of its > > status. Please notify the sender immediately by reply e-mail and then > > delete this message from your system. Please do not copy it or use it > > for any purposes, or disclose its contents to any other person. Thank-you for purchasing electron destruction services from us. Based on weight, your e-mail message contains over 400 million trillion electrons. This qualifies you for the bulk electron destruction rate of only $1.00 per trillion electrons. Unfortunately you do not have any credit history with us. Please submit your payment for $1.00 million dollars and we will be happy to carry out your request. In the interim, your electrons have been placed in a magnetic bottle for storage. The estimated cost of storage is $25.00 per hundred trillion electrons per day. In the event that you fail to pay the amounts invoiced for the services that you have ordered, legal action will be taken against you.
