[sqlite] Why does a query run 50x slower across a network?

[dandl]

We have an application we converted from Access to Sqlite. Mostly it's been a 
great success, but we have two queries that runs 50x slower across a gigabit 
LAN than on a local file system and we don't know why. Performance on Access 
was perfectly acceptable, and on Sqlite is not and we can't figure out why. 
Customers are complaining, and with good reason.

We're using System.Data.Sqlite and the file is being opened as a shared UNC 
pathname. The network can transfer at upwards of 250 Mbps on file copies, but 
the SQL query runs at around 10 Mbps (Windows Perfmon). The database is about 
90MB. The queries takes 100ms on local file system and 5s on network share. 
[With customer data it can run into minutes.]

I'm hoping we've done something really dumb and obvious, but we can't see it. 
Details follow. Anyone who can shed light very much appreciated.

The query looks like this:
Query1:

SELECT  Max([date]) AS LastOfdate FROM order_header WHERE 
(((transaction_type)=1) AND ((status_code)=-1) AND ((sale_type_id)=1 Or 
(sale_type_id)=2 Or (sale_type_id)=14)) GROUP BY billToCardGuid, date([date]) 
HAVING billToCardGuid=X'A426D7165BBF0ECA3276555D32B6E385' ORDER BY date([date]) 
DESC limit 3

Query2:

SELECT  order_header.order_id AS maxID FROM order_header WHERE 
(((order_header.transaction_type)=1) AND ((order_header.status_code)=-1) AND 
((order_header.sale_type_id)=1 Or (order_header.sale_type_id)=2 Or 
(order_header.sale_type_id)=14)) AND 
(order_header.billToCardGuid=X'A426D7165BBF0ECA3276555D32B6E385') ORDER BY 
[date] DESC, order_id desc limit 1

The schema looks like this:
CREATE TABLE IF NOT EXISTS "order_header" (
"order_id" INTEGER DEFAULT 0,
"user_name" VARCHAR(31) COLLATE NOCASE ,
"number" INTEGER DEFAULT 0,
"confirmation_number" VARCHAR(9) COLLATE NOCASE ,
"creation_date" DATETIME,
"modification_date" DATETIME,
"transaction_type" SMALLINT DEFAULT 0,
"customer_billto_last_name" VARCHAR(31) COLLATE NOCASE ,
"customer_billto_first_name" VARCHAR(31) COLLATE NOCASE ,
"customer_billto_company" VARCHAR(50) COLLATE NOCASE ,
"customer_billto_address" VARCHAR(63) COLLATE NOCASE ,
"customer_billto_city" VARCHAR(31) COLLATE NOCASE ,
"customer_billto_state" VARCHAR(31) COLLATE NOCASE ,
"customer_billto_zip" VARCHAR(31) COLLATE NOCASE ,
"customer_shipto_last_name" VARCHAR(31) COLLATE NOCASE ,
"customer_shipto_first_name" VARCHAR(31) COLLATE NOCASE ,
"customer_shipto_company" VARCHAR(50) COLLATE NOCASE ,
"customer_shipto_address" VARCHAR(63) COLLATE NOCASE ,
"customer_shipto_city" VARCHAR(31) COLLATE NOCASE ,
"customer_shipto_state" VARCHAR(31) COLLATE NOCASE ,
"customer_shipto_zip" VARCHAR(31) COLLATE NOCASE ,
"customer_fax" VARCHAR(31) COLLATE NOCASE ,
"customer_ar_balance" REAL DEFAULT 0,
"customer_bill_rate" REAL DEFAULT 0,
"customer_tel" VARCHAR(31) COLLATE NOCASE ,
"date" DATETIME,
"status_description" VARCHAR(31) COLLATE NOCASE ,
"status_code" SMALLINT DEFAULT 0,
"order_comment" TEXT,
"payment_comment" VARCHAR(63) COLLATE NOCASE ,
"terms_description" VARCHAR(31) COLLATE NOCASE ,
"shipmethod_description" VARCHAR(31) COLLATE NOCASE ,
"shipmethod_amount" REAL DEFAULT 0,
"shipmethod_tax_rate" REAL DEFAULT 0,
"shipmethod_tax_code" VARCHAR(3) COLLATE NOCASE ,
"tax_total" REAL DEFAULT 0,
"ex_tax_total" REAL DEFAULT 0,
"grand_total" REAL DEFAULT 0,
"pay_amount" REAL DEFAULT 0,
"balance" REAL DEFAULT 0,
"card" VARCHAR(19) COLLATE NOCASE ,
"exp" VARCHAR(4) COLLATE NOCASE ,
"po" VARCHAR(15) COLLATE NOCASE ,
"payment_date" DATETIME,
"printed_name" VARCHAR(31) COLLATE NOCASE ,
"signature" BLOB,
"line_item_count" SMALLINT DEFAULT 0,
"flags" SMALLINT DEFAULT 0,
"employeeGuid" GUID,
"employee_bill_rate" REAL DEFAULT 0,
"employee_name" VARCHAR(31) COLLATE NOCASE ,
"date_hotsynced" DATETIME,
"date_exported_to_myob" DATETIME,
"export_status" SMALLINT DEFAULT 0,
"export_error_no" INTEGER DEFAULT 0,
"attempt_export" BOOL NOT NULL DEFAULT 1,
"invoice_status" CHAR(1) DEFAULT 'I',
"sale_type_id" INTEGER DEFAULT 1,
"export_Error_Guid" GUID,
"validated" BOOL NOT NULL DEFAULT 0,
"reconciled" BOOL NOT NULL DEFAULT 0,
"txnGuid" GUID,
"cardGuid" GUID,
"billToCardGuid" GUID,
"shipToCardGuid" GUID,
"locationFromCardGuid" GUID,
"locationToCardGuid" GUID,
"unidentified_chunks" BLOB,
"toDoGuid" GUID,
"uom_pick_mode" BOOL NOT NULL DEFAULT 0,
"validationGuid" GUID,
"territoryGuid" GUID,
"territoryGroupGuid" GUID,
"hasTerritory" BOOL NOT NULL DEFAULT 0,
"parentTranGuid" GUID,
"cartonQuantity" REAL,
"pickInstructions" VARCHAR(64) COLLATE NOCASE ,
"creator" INTEGER,
"POSMode" BOOL NOT NULL DEFAULT 0,
"Locked" BOOL NOT NULL DEFAULT 0,
"relatedTransactionGuid" GUID,
"displayMode" INTEGER,
"signature_date" DATETIME,
"freezerFull" BOOL NOT NULL DEFAULT 0,
"sortOrder" INTEGER,
"handheldViewed" BOOL NOT NULL DEFAULT 0,
"managerGuid" GUID,
"templateTranGuid" GUID,
"approved" BOOL NOT NULL DEFAULT 0, [pay_amount_exported] REAL,
CONSTRAINT "order_header_order_id" PRIMARY KEY("order_id"));
CREATE UNIQUE INDEX "order_header_txnGuid" ON "order_header" 

Re: [sqlite] BUG: Illegal initialization in icu.c : sqlite3IcuInit

[dandl]

From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of James K. Lowden

>>>Despite the fact that the Windows API is defined in terms of C, Microsoft 
>>>evidently and probably correctly has decided its market does not demand a 
>>>modern C compiler.  

The Microsoft Windows API is defined very much in terms of C89. As an interface 
standard, I approve.

But you are correct. I haven't heard of anyone using C for new Windows 
development for many years, since C++ is almost essential for accessing any of 
the newer Microsoft technologies (eg COM, DirectX), and provides a vastly 
superior programming environment. 

We need to be clear about the difference between a piece of software than can 
be built on every possible kind of hardware and O/S, as against API standards 
and programming convenience on a specific platform. As I said previously, there 
are trade-offs.

>>>Last year there was much rejoicing when Microsoft decided to bundle SQLite 
>>>with Windows.  That leaves me with a new question: if SQLite announced its 
>>>intention to move to C11 in 2018, would that perhaps influence Microsoft's 
>>>timeline to update its compiler?  

Hypothetical question, won't happen, no it wouldn't.

Regards
David M Bennett FACS

Andl - A New Database Language - andl.org





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

[sqlite] Sqlite on C99/C11 (was: RE: BUG: Illegal initialization in icu.c : sqlite3IcuInit)

[dandl]

From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of James K. Lowden

 Basing source on "ANSI C" (as much as possible) just gives you the 
 biggest possible distribution / compatibility.
>>>
>>>Yes, but it also limits you to C as it stood 20 years ago.  And counting.  
>>>Is there no point at which a more recent standard should be adopted?  Among 
>>>features of C11 I use:

This is the trade-off. As with many of us I've been living this for decades. 
This is the genesis of the 'breaking change', which is a killer for those of us 
supporting older software.

>>>SQLite would benefit from all of those, plus UTF-8 string constants.  
>>>
>>>I full well understand the workarounds for integer sizes and Booleans are 
>>>baked into SQLite, and there's no real need to adopt the new (er,
>>>new-ish) forms.  VLAs and designated initializers should not be 
>>>underestimated, though.  There are many places in SQLite where VLAs could 
>>>replace local malloc/free pairs, and SQLite code is rife with structures 
>>>that can be more succintly initialized with the modern syntax.  

UTF-8 perhaps but I don't think VLA's are to be considered. Without going into 
the history, VLA's are (a) not part of C++ (b) not guaranteed to be supported 
by all compliant C compilers (c) optional in C11 (see _ _STDC_NO_VLA_ _) (d) 
not guaranteed to be required by future C standards. They're a lovely feature 
if you know exactly which compiler(s) you're working with.

>>>Nowawdays, once again making a living writing C code and having the daily 
>>>opportunity to experiment the the new language features, I've come to like 
>>>them.  C is a sharp-edged language, and has become a bit fussy as the 
>>>compilers writers have adopted, as we've discussed here, pedantic notions of 
>>>UB.  OTOH, it's an improvement to relieve the language of some of its 
>>>ancient constraints, such as static array sizes and the prohibition on 
>>>type-punning in unions (which began as SOP, then was UB, and is now OK 
>>>again).  

I would never use C if C++ is available. If I have to, I choose a conservative 
feature set. There may be good reasons for Sqlite to move to C99 (the main one 
being that it's already a 15yo standard), but it's not a decision to be taken 
lightly or piecemeal. C11 has barely taken off its training wheels.


Regards
David M Bennett FACS

Andl - A New Database Language - andl.org






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

Re: [sqlite] BUG: Illegal initialization in icu.c : sqlite3IcuInit

[dandl]

From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Hick Gunter

>>>Integer promotion is usually ok between integers of the same signedness.

For some definition of 'OK'.

>>>However, in
unsigned char uns = 0xff;
long val = uns;
>>>what should be the (32 Bit size assumed) value of val? Should it be 
>>>0x00ff (promotion before conversion) or 0x (promotion after 
>>>conversion)? Or flagged as "assignment between variables of different 
>>>signedness"?

The standard is clear about this case (as it is about nearly all cases). Value 
is always preserved in integer promotion if possible. The value is simply 255 
in any integer large enough to hold it.

>>>Or for example, how often is the loop executed?
uint16_t count = 0x;
int16_t ii;
for (ii = 0; II < count, ii++) { ... }

>>> Integer demotion (i.e. copying a value from a larger size to a smaller size 
>>> integer) should be flagged by the compiler (unless the compiler is clever 
>>> enough to figure out that the value cannot be out of range).

The standard does not define 'demotion'. This loop will not terminate because 
incrementing the loop variable will cause integer overflow (usually ignored) 
and the test will always be true. The conversions for the test preserve value.

>>>long val = 0x65;
char xxx = val;

Valid C, but typically causes a warning.

>>>However

struct mystruct *ptr1 = '\0';
struct mystruct *ptr2 = 0;

>>>is "making a pointer from an integer of a different size without a cast" 
>>>(the first on all architectures, the second on LP64 architectures).

I have no idea what you intend by this, but this too is valid C. No warnings.

>>>Of course this is nitpicking and the examples are trivial, but compilers 
>>>need to guess if the programmer is just being stupid or downright 
>>>brilliant...

I'm still waiting for the nits. The C/C++ standards are written precisely to 
cover these and other things that those maintaining mature source code really 
care about.

Regards
David M Bennett FACS

Andl - A New Database Language - andl.org





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


___
 Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: h...@scigames.at

This communication (including any attachments) is intended for the use of the 
intended recipient(s) only and may contain information that is confidential, 
privileged or legally protected. Any unauthorized use or dissemination of this 
communication is strictly prohibited. If you have received this communication 
in error, please immediately notify the sender by return e-mail message and 
delete all copies of the original communication. Thank you for your cooperation.


___
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] BUG: Illegal initialization in icu.c : sqlite3IcuInit

[dandl]

From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of James K. Lowden

>>>Sticking with C90 is perfectly rational if you're still running Windows 98 
>>>on a Pentium III at 500 Mhz with 256 MB RAM.  Else, really, it's not too 
>>>soon to adopt a 6-year old standard, C11.  

There are plenty of older C compilers used in various niche applications: 
embedded, pricey vendor dev kit not updated, etc. Obviously not many of them 
using Sqlite, but you never know...

Regards
David M Bennett FACS

Andl - A New Database Language - andl.org





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

Re: [sqlite] BUG: Illegal initialization in icu.c : sqlite3IcuInit

[dandl]

From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of David Empson

>>>The ANSI/ISO C 1990 standard states this in section 6.5.7, under Constraints:
>>>“All the expressions in an initializer for an object that has static storage 
>>>duration or in an initializer list for an object that has aggregate or union 
>>>type shall be constant expressions.”

>>>In this case the code is trying to initialize a field of an auto struct 
>>>using the db parameter passed to the function. That is not a constant 
>>>expression, and it is in an initializer list for an object that has 
>>>aggregate type (whether or not the object has static storage duration), so 
>>>is disallowed under ANSI/ISO C 1990.

>>>Later versions of the C standard removed the bit about aggregate or union 
>>>types, leaving only the static restriction, e.g. from section 6.7.8 of the 
>>>draft C99 standard:
>>>"All the expressions in an initializer for an object that has static storage 
>>>duration shall be constant expressions or string literals.”

I can confirm that. So in summary, the Sqlite code is not valid ANSI C (1990) 
but it is valid according to the C99 standard. It's only broken for old 
compilers, not new ones.

1. Why was VS the first compiler to detect this?
2. Is there an authoritative view on which standard Sqlite should comply with?

Regards
David M Bennett FACS

Andl - A New Database Language - andl.org





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

Re: [sqlite] BUG: Illegal initialization in icu.c : sqlite3IcuInit

[dandl]

>>>From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
>>>Behalf Of Ziemowit Laski
Sent: Thursday, 26 January 2017 7:36 AM
To: sqlite-users@mailinglists.sqlite.org
Subject: [sqlite] BUG: Illegal initialization in icu.c : sqlite3IcuInit
>>>
>>>Visual C++ correctly catches this.  The fragment
>>>
>>>  struct IcuScalar {
>>>const char *zName;/* Function name */
>>>int nArg; /* Number of arguments */
>>>int enc;  /* Optimal text encoding */
>>>void *pContext;   /* sqlite3_user_data() context 
>>> */
>>>void (*xFunc)(sqlite3_context*,int,sqlite3_value**);
>>>  } scalars[] = {
>>>{"regexp", 2, SQLITE_ANY|SQLITE_DETERMINISTIC,  0, 
>>> icuRegexpFunc},
>>>
>>>{"lower",  1, SQLITE_UTF16|SQLITE_DETERMINISTIC,0, 
>>> icuCaseFunc16},
>>>{"lower",  2, SQLITE_UTF16|SQLITE_DETERMINISTIC,0, 
>>> icuCaseFunc16},
>>>{"upper",  1, SQLITE_UTF16|SQLITE_DETERMINISTIC, (void*)1, 
>>> icuCaseFunc16},
>>>{"upper",  2, SQLITE_UTF16|SQLITE_DETERMINISTIC, (void*)1, 
>>> icuCaseFunc16},
>>>
>>>{"lower",  1, SQLITE_UTF8|SQLITE_DETERMINISTIC, 0, 
>>> icuCaseFunc16},
>>>{"lower",  2, SQLITE_UTF8|SQLITE_DETERMINISTIC, 0, 
>>> icuCaseFunc16},
>>>{"upper",  1, SQLITE_UTF8|SQLITE_DETERMINISTIC,  (void*)1, 
>>> icuCaseFunc16},
>>>{"upper",  2, SQLITE_UTF8|SQLITE_DETERMINISTIC,  (void*)1, 
>>> icuCaseFunc16},
>>>
>>>{"like",   2, SQLITE_UTF8|SQLITE_DETERMINISTIC, 0, icuLikeFunc},
>>>{"like",   3, SQLITE_UTF8|SQLITE_DETERMINISTIC, 0, icuLikeFunc},
>>>
>>>{"icu_load_collation",  2, SQLITE_UTF8, (void*)db, icuLoadCollation},
>>>  };
>>>
>>>  int rc = SQLITE_OK;
>>>  int i;
>>>
>>>should read
>>>
>>>   struct IcuScalar {
>>>  const char *zName;/* Function name */
>>>  int nArg; /* Number of 
>>> arguments */
>>>  int enc;  /* Optimal text 
>>> encoding */
>>>  void *pContext;   /* 
>>> sqlite3_user_data() context */
>>>  void(*xFunc)(sqlite3_context*, int, sqlite3_value**);
>>>   } scalars[] = {
>>>  { "regexp", 2, SQLITE_ANY | SQLITE_DETERMINISTIC,  0, 
>>> icuRegexpFunc },
>>>
>>>  { "lower",  1, SQLITE_UTF16 | SQLITE_DETERMINISTIC,0, 
>>> icuCaseFunc16 },
>>>  { "lower",  2, SQLITE_UTF16 | SQLITE_DETERMINISTIC,0, 
>>> icuCaseFunc16 },
>>>  { "upper",  1, SQLITE_UTF16 | SQLITE_DETERMINISTIC, (void*)1, 
>>> icuCaseFunc16 },
>>>  { "upper",  2, SQLITE_UTF16 | SQLITE_DETERMINISTIC, (void*)1, 
>>> icuCaseFunc16 },
>>>
>>>  { "lower",  1, SQLITE_UTF8 | SQLITE_DETERMINISTIC, 0, 
>>> icuCaseFunc16 },
>>>  { "lower",  2, SQLITE_UTF8 | SQLITE_DETERMINISTIC, 0, 
>>> icuCaseFunc16 },
>>>  { "upper",  1, SQLITE_UTF8 | SQLITE_DETERMINISTIC,  (void*)1, 
>>> icuCaseFunc16 },
>>>  { "upper",  2, SQLITE_UTF8 | SQLITE_DETERMINISTIC,  (void*)1, 
>>> icuCaseFunc16 },
>>>
>>>  { "like",   2, SQLITE_UTF8 | SQLITE_DETERMINISTIC, 0, 
>>> icuLikeFunc },
>>>  { "like",   3, SQLITE_UTF8 | SQLITE_DETERMINISTIC, 0, 
>>> icuLikeFunc },
>>>
>>>  { "icu_load_collation",  2, SQLITE_UTF8, 0, icuLoadCollation }
>>>   };
>>>
>>>   int rc = SQLITE_OK;
>>>   int i;
>>>
>>>   scalars[11].pContext = (void*)db;
>>>

Why would you say that? What error message are you getting on what compiler, 
and why would that change produce any different result? What type is 'db'?

Can you quote some specified reference to the C standard document in support of 
your contention?

Regards
David M Bennett FACS

Andl - A New Database Language - andl.org





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

Re: [sqlite] warning on glob [was: SQLite3 Tutorial error]

[dandl]

From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Will Parsons

>>>I dug up some old references to investigate this further:

>>>a) The UNIX C Shell Field Guide (1986): Ranges in the pattern
   [lower-upper] mentioned, no mention of negation of pattern.
   (Presumably even a pattern like "[1-9xyz]" wouldn't be valid
   either, though this is not explicit.)

Try this one: https://www.bell-labs.com/usr/dmr/www/pdfs/man71.pdf. 
In 1971 there is glob, but only for ? and *.

>>>What I take away from this is that relying on [^1-9] to mean the same thing 
>>>as it would in a regular expression is non-portable.  If this gets 
>>>documented, then I think there should be a warning to this effect.
>>>As this thread has exhibited, supporting it can even be seen a misfeature, 
>>>as it encourages confusion between glob patterns and regular expressions.

Particularly since modern users might well expect it to be Posix compliant, 
which it is not.

Regards
David M Bennett FACS

Andl - A New Database Language - andl.org





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

Re: [sqlite] SQLite3 Tutorial error

[dandl]

From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Simon Slavin

> How about adding the above to the permanent docs?

>>>SQLite docs do not usually include examples or tutorial information.  It 
>>>would be more consistent just to take the text which is currently comments 
>>>in the source code file and include it in the docs.  You can see those 
>>>comments (or possibly an old version of them) here:

I agree. That's pretty clear, but not so easy to access where it is.

Regards
David M Bennett FACS

Andl - A New Database Language - andl.org





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

Re: [sqlite] SQLite3 Tutorial error

[dandl]

From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Keith Medcalf

>>>SQLite does not use the glob function from the standard library -- the 
>>>function is defined in func.c
>>>
>>>Both "glob" and "like" call the same function, likeFunc with different sets 
>>>of user_data.  likeFunc does a bunch of validation then calls patternCompare 
>>>which actually implements the like and glob functionality.  How like and 
>>>glob work are documented in the preface to patternCompare.
>>>
>>>like implements the standard sql like using % (0 or more) and _ (exactly 1 
>>>char) as wildcard matches.
>>>
>>>glob implements unix globbing using * (0 or more) and ? (exactly 1) as 
>>>wildcard matches. "sets" of characters are indicated by  squockets (square 
>>>brackets -- []).  Different from the standard unix glob however, it uses ^ 
>>>to invert the sense of a set rather than an !.  Since it is unicode, a 
>>>character is [\u-\u10].  [^1-7] is equivalent to a match of any of 
>>>the remaining unicode characters.
>>>
>>>thus in unix/linux one may pronounce "match anything where one character is 
>>>not the digits 1 through 7" as *[!1-7]* one would pronounce the same request 
>>>to SQLite as *[^1-7]*
>>>
>>>This of course would match any string that was not composed entirely of only 
>>>the characters 1 through 7 (not that there are no characters 1 through 7 in 
>>>the string) -- and must be at least 1 character long.
>>>
>>>If one wanted to match strings that contained a 1 through 7 anywhere within, 
>>>then one would pronounce *[1-7]* on both unix/linux and to SQLite
>>>
>>>Were one to want a glob that excluded all strings that contained the digits 
>>>1 though 7 anywhere within, then one would pronounce, in SQLite, WHERE NOT x 
>>>GLOB '*[1-7]*' -- though this would also now match 0 length strings.
>>>
>>>There is no way to "invert" the match-sense of a glob pattern within the 
>>>pattern itself.  That is, one cannot use '^*[1-7]*' as an equivalent to the 
>>>above inversion of the results of a positive match.  GLOB patterns only 
>>>search for a positive match, not an exclusion.  The [^stuf] excludes the 
>>>characters or range provided from the characters matched by a ? -- [^stuf] 
>>>is not an exclusion of the characters stuf but rather a match for any of the 
>>>other unicode characters except stuf -- in other words a "somewhat limited 
>>>?".

How about adding the above to the permanent docs? AFAIK there is currently no 
way to know all this stuff other than reading source.

Regards
David M Bennett FACS

Andl - A New Database Language - andl.org





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

Re: [sqlite] SQLite3 Tutorial error

[dandl]

From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Simon Slavin

>>>They’re probably using the external function interface to override the 
>>>internal globbing function.  And by the look of the results at least one of 
>>>the programmers involved thinks that GLOB and REGEX do the same thing.

I think you're right. One of the contributing problems is that the behaviour of 
GLOB is not defined in the documentation. Here is all it says:

"The GLOB operator is similar to LIKE but uses the Unix file globbing syntax 
for its wildcards. Also, GLOB is case sensitive, unlike LIKE. Both GLOB and 
LIKE may be preceded by the NOT keyword to invert the sense of the test. The 
infix GLOB operator is implemented by calling the function glob(Y,X) and can be 
modified by overriding that function."

Unix globbing for Linux is defined here: 
http://man7.org/linux/man-pages/man7/glob.7.html. AFAICT Sqlite does not 
implement this behaviour.

Perhaps some accurate documentation for GLOB in Sqlite would help to clarify 
things?

Regards
David M Bennett FACS

Andl - A New Database Language - andl.org





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

Re: [sqlite] SQLite3 Tutorial error

[dandl]

A question: this appears to be the Posix standard for globbing ie Patterns Used 
for Filename Expansion:
http://pubs.opengroup.org/onlinepubs/007908799/xcu/chap2.html#tag_001_013_003

And this is Linux:
https://linux.die.net/man/7/glob

Is this what Sqlite intends to conform to? Because the convention here is 
[!0-9] and not [^0-9].

Regards
David M Bennett FACS

Andl - A New Database Language - andl.org


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

Re: [sqlite] SQLite3 Tutorial error

[dandl]

From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Keith Medcalf

>>>Yes.  The GLOB was invented on Unix.  I posted an example of the Unix 
>>>filename globbing (which has not changed, to my knowledge, since the 60's), 
>>>which works exactly the same as the GLOB operator in SQLite 3.9.0 through 
>>>the current head of trunk.  Perhaps there were minor changes, but nothing 
>>>that affects the output of the *[1-9]* or *[^1-9]* patterns when applied to 
>>>the same data used in the Linux demonstration.  However, I did not try and 
>>>build every single version of SQLite between 3.9.0 to 3.17.0 to see if one 
>>>of them happened to be broken.  The two ends and a sampling from the middle 
>>>all worked the same.

I believe file system globbing originated on Unix in around 1969. It was not 
then thought to bear any particular relationship to regular expressions AFAIK.

>>>And by the way, GLOB predates REGEX by about 15 years.  REGEX borrowed (and 
>>>modified) GLOB syntax.

I believe the computer science underlying regex dates from work by Kleene in 
1956. I don't have the paper, but my impression is that it had marked 
similarities to modern usage. There were competing ideas in early languages 
(anyone remember Snobol?) but Thompson provided an implementation of Kleene's 
regex in the Unix text editor 'ed' in around 1969, based on even earlier work 
at IBM. Using regex in compilers (like lex) came later.

AFAIK glob and regex appeared in Unix at more or less the same time ie very 
early, but they were always distinct.

>>>(in case you have never used a Linux/Unix system with an ll command alias, 
>>>the command to create it is:  alias ll='ls -l')

>>>Are you ABSOLUTELY SURE that the authors of the third-party tools have not 
>>>provided their own GLOB function that works differently, perhaps in 
>>>accordance with their Dim Sum because their little hearts did not desire the 
>>>built in one?

I would be very disappointed to find that someone was implementing regex and 
calling it glob. That would be a mistake. Glob is glob, and Sqlite has it right 
IMHO.

Regards
David M Bennett FACS

Andl - A New Database Language - andl.org





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

Re: [sqlite] Frequent database corruptions on Windows 10

[dandl]

> I think that the System.Data.SQLite is compiled in the multi-thread
> mode and I did not find a function or option to switch it to
> serialized mode, but on the other hand all the different programs
> showed no problems in the last years and only started to make trouble
> after my upgrade from Windows 8.1 to Windows 10 (same hardware).

Is it possible that your Windows 10 installation is using different disk 
protocols?

In particular, SMB 3.1 can be associated with the kinds of problems you are 
experiencing.

Basic reading here: https://en.wikipedia.org/wiki/Server_Message_Block. After 
that...?

Regards
David M Bennett FACS

Andl - A New Database Language - andl.org





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

Re: [sqlite] Bug: SQLite's include guards are reserved identifiers

[dandl]

> We change internal-use #defines and other internal-use symbols on just
about
> every release.  This has never before caused breakage that we are aware
of.
> Or if it has caused breakage, the developers doing such things are aware
that
> what they are doing is not allowed and have the good sense to not
complain.
> 
> The guard #defines were changed here:
> https://www.sqlite.org/src/info/5471aca0158851d3

These changes address the issue first raised by Daniel Seither on 9/07,
which I supported on 10/7 and subsequently. The changes made are as I would
recommend (using a prefix of SQLITE_ for all guards). I note that the
changes were made extremely quickly (9/07, within 24 hours) regardless of
the debate then raging. Good call.

Regards
David M Bennett FACS

Andl - A New Database Language - andl.org





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

Re: [sqlite] Bug: SQLite's include guards are reserved identifiers

[dandl]

> Good point, though personally I would weigh this in a different class of
> backward compatibility changes. 

I agree, but I have no evidence either way. You never really know what a
customer is doing until you change something they were using.

> I don't think changing this would be bad.
> But I also don't think a change is necessary.
> 
> Driving 51 in a zone posted 50 is also not in compliance (with a different
> standard, of course). Yet we violate that standard all the time.
> 
> The reality is that I'm not aware of a single platform that will fail to
> generate correct code for this (outside of treat all warnings as errors).

Again I agree, although again one never really knows. 

> Yes, it is a standards violation in the strictest sense. Yet it's not a
big
> deal given the number of successful deployments.
> 
> If anyone can demonstrate an implementation that uses this identifier in a
> way that is incompatible with SQLite, then it should be changed. Otherwise
it
> seems to me that disabling treat all warning as errors (or this one
warning
> in the impacted projects and files) is the least disruptive change for all
> concerned.

This is the core of this post, and the only part I disagree with. There are
corporate environments in which extremely tight control is maintained over
compliance and standards, and in which a decision as to how to treating
various warnings and errors raised by various development tools involves
multiple parties and multiple processes and multiple levels of approvals. It
is simply not possible to form a blanket opinion on exactly how disruptive
such a change would be in all possible users' environments; nor should one
try.

Regards
David M Bennett FACS

Andl - A New Database Language - andl.org






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

Re: [sqlite] Bug: SQLite's include guards are reserved identifiers

[dandl]

> Try to write a fully standard compliant standard library without using any
of
> the reserved namespace!!!

This is the key point that has been missed so far. The C/C++ standards do
not provide a mechanism by which the supplier of a library can reserve or
sequester some range of identifiers, for both historic and possible future
use, and at the same time be guaranteed to conflict with neither the
provider of an implementation nor the user of the library. Therefore a
choice has to be made.

Intruding on the implementation space in violation of the standard is not
the best solution to the problem.

The safest solution (and one widely adopted) is to choose a prefix of
sufficient length and starting with a letter. It should be one that is
highly likely to be unique to the enterprise and then be applied to all
visible identifiers. The ownership of that prefix could be backed up by
ownership of a matching domain, trademark, registered company name, etc.
[The Java domain thing is just plain silly, and widely breached.]

In fact Sqlite has done an excellent job of exactly that, with one
exception: they chose to add an unnecessary underscore before the guard
prefix and thereby became non-compliant. It should simply be removed (or
have been removed -- perhaps it's now too late).

> By the standard, a header defined by the standard is only allow to define
the
> symbols it is EXPLICITLY defined to, and the symbols in the appropriate
> reserved namespaces. ANY other symbol is allowed to be used/defined by the
> user.
> 
> Your suggest has a couple of problems. First, it handles the include
guard,
> but not a bunch of other things that need symbols. The second, what is to
be
> used by a programmer who doesn't own a domain? Owning a domain is not a
> requirement for writing software.

Tell that to the Java guys.

Regards
David M Bennett FACS

Andl - A New Database Language - andl.org





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

Re: [sqlite] Bug: SQLite's include guards are reserved identifiers

[dandl]

> Obviously the standard is broken/incorrect or your interpretation of it is
> broken/incorrect.

No, and the standard was very carefully written to say this, and it's easy
to find references to back up this interpretation if you care to look for
them. Or ask a question on SO.

> Most API headers do the same thing.  

Yes, this is quite a common breach of the standard. That doesn't make it
right.

> Even the standard library does it, in
> most compilers.  

Almost universally I would say. That is the entire point: these identifiers
are reserved 'for the implementation', that is for the standard library to
use, and no-one else.

> Not all of them add the trailing _, but several do.  Whether
> and particular one does or not seems to depend on whether the entropy of
the
> multiverse was odd or even at the time the API was generated.

Irrelevant. The use of leading underscore followed by upper-case letter is
in violation of the C standard S 7.1.3. And the standard has been unchanged
since its first release, which is well before Sqlite was even thought of. 

Regards
David M Bennett FACS

Andl - A New Database Language - andl.org


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

Re: [sqlite] Bug: SQLite's include guards are reserved identifiers

[dandl]

> Using clang 3.8 with -Wreserved-id-macro (enabled by -Weverything), I just
> noticed that SQLite uses include guards with a leading underscore, for
> example _SQLITE3_H_ in the amalgamation. According to the C standard, this
is
> a reserved identifier, leading to undefined behavior:
> 
> > All identifiers that begin with an underscore and either an uppercase
> > letter or another underscore are always reserved for any use. [...] If
> > the program declares or defines an identifier in a context in which it
> > is reserved (other than as allowed by 7.1.4), or defines a reserved
> > identifier as a macro name, the behavior is undefined.
> 
> (Source: the C11 standard, Section 7.1.3, see [1] for the latest draft of
C11
> before publication, which should be identical to the finished text)
> 
> I guess that means that the include guards should be changed to no longer
use
> leading underscores. Any opinions on that?

Yes, that's how I read it.

There are I believe two potential theoretical problems. 
1. An unusual but fully conforming compiler implementation could use an
identifier such as this for its own purposes; the behaviour in that case
would indeed be "undefined". Such an implementation could not be used "out
of the box" with Sqlite.

2. A fully conforming compiler implementation might treat the use of such an
identifier as a defect and issue a diagnostic. Although this would most
likely be at a warning level, such a compiler could not be used with Sqlite
in an environment where all warnings are automatically treated as errors.

The first of these is quite unlikely; the second is quite likely (and may be
the situation OP has encountered). Obviously there are workarounds in both
cases. 

Nevertheless I would tend to regard this as a bug.

Regards
David M Bennett FACS

Andl - A New Database Language - andl.org





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

Re: [sqlite] Question about C# with SQLite

[dandl]

Did you mean: SQLite.Intero.dll or SQLite.Interop.dll?

Did you put it in the right place?

Also consider the question of 32 vs 64 bit.

Did you try Stack Overflow? There are lots of hits over there.

Regards
David M Bennett FACS

Andl - A New Database Language - andl.org



> -Original Message-
> From: sqlite-users-boun...@mailinglists.sqlite.org [mailto:sqlite-users-
> boun...@mailinglists.sqlite.org] On Behalf Of J Decker
> Sent: Tuesday, 5 July 2016 12:57 PM
> To: SQLite mailing list 
> Subject: Re: [sqlite] Question about C# with SQLite
> 
> if it was built with debug mode; probably the debug runtime doesn't exist
> there.  Otherwise it's because the visual studio runtime required isn't
> available.
> 
> On Mon, Jul 4, 2016 at 6:52 AM, Shouwei Li  wrote:
> 
> > Hi there,
> >
> > I have a project developed with .net 2015 and C#. I use SQLite as the
> > server-less database. It works very well in my workstation. But it can
> > not run on other PC. The error indicates:
> >
> > Unable to load DLL "SQLite.Intero.dll": The specified module could not
> > be found.
> >
> > I already attach this dll with my program.
> >
> > I want to ask is there a manual talk about how to deploy a program to
> > customer when we use the SQLite database.
> >
> > Thanks for your reading.
> >
> > --
> > Best Regards!
> > Shouwei Li
> > ___
> > 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-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Locking semantics are broken?

[dandl]

Fair comment.

We have seen problems:
1. In all versions of Windows based on the 95 kernel, and especially Windows
ME (but not the NT kernel since 3.5)
2. At any time if the network infrastructure is unreliable (too many errors
or retries)
3. At any time if a client machine misbehaves eg crashes while holding a
lock, or attempts its own file accesses etc
4. Recently, apparently related to SMB 3.0+, due to more aggressive
performance optimisations.

But if everything is configured right and working right and nothing bad
happens then it is highly reliable over very large volumes of transactions.

I guess my plea would be to emphasise the need to pay attention to all the
details and to warn that there is still a risk of uncontrolled data loss,
rather than just branding it as 'broken'.

Regards
David M Bennett FACS

Andl - A New Database Language - andl.org



> -Original Message-
> From: sqlite-users-boun...@mailinglists.sqlite.org [mailto:sqlite-users-
> boun...@mailinglists.sqlite.org] On Behalf Of Simon Slavin
> Sent: Tuesday, 28 June 2016 7:28 PM
> To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org>
> Subject: Re: [sqlite] Locking semantics are broken?
> 
> 
> On 28 Jun 2016, at 9:07am, dandl <da...@andl.org> wrote:
> 
> >> Do not use SQLite for concurrent access over a network connection.
> >> Locking semantics are broken for most network filesystems, so you
> >> will have corruption issues that are no fault of SQLite.
> >
> > I have seen this comment made more than once on this list. Is there any
> reliable evidence to support this for a Windows-based network?
> 
> Actually, the problem that causes causes people to make that warning
occurs
> in POSIX.  See the beginning of section 6.0 in this page:
> 
> <https://www.sqlite.org/lockingv3.html>
> 
> However, while the above is a definitely known, verifiable problem, with
> every implementation of POSIX, we have had occasional reports about
locking
> problems with Windows as documented in section 9.1 here:
> 
> <https://www.sqlite.org/atomiccommit.html>
> 
> The problem is that with the numerous versions of Windows, File System and
> Network system, nobody has come up with a fault which can be reproduced by
> the developers.  But we do get enough vague reports of problems with
Windows
> to make us believe that there is something wrong somewhere.
> 
> Simon.
> ___
> 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] Locking semantics are broken?

[dandl]

> Do not use SQLite for concurrent access over a network connection. Locking
> semantics are broken for most network filesystems, so you will have
> corruption issues that are no fault of SQLite.

I have seen this comment made more than once on this list. Is there any 
reliable evidence to support this for a Windows-based network?

Disclosure: we wrote and maintain an ISAM-based multi-user database product 
which relies on network locking. We have conducted exhaustive tests over many 
years and in our opinion, locking and multi-user semantics on Windows XP and 
later networks are reliable and free of errors, if performed correctly by the 
client software.

[We use the same semantics for a Linux or Unix-based system with multiple 
terminals, but not on any Unix-based network. This is only about Windows.]

If Sqlite has a problem then perhaps it can be fixed.

Regards
David M Bennett FACS

Andl - A New Database Language - andl.org


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

Re: [sqlite] Postgres vs MySQL (was Re: Messages posted on Nabble not getting to list)

[dandl]

Good summary, agree 100%.

>From experience, Postgres is amazingly configurable, if you ever want to do
something weird. Sqlite is too, but only if you access it directly in C and
don't really need a server.

And the guys working on the internals (both) are the smartest bunch you're
likely to run across any time soon. And they answer questions and fix bugs.

Both are highly recommended, if you don't have a compelling (vendor) reason
to use Oracle, MSSQL or DB/2. It's not obvious why the others exist.

Regards
David M Bennett FACS

Andl - A New Database Language - andl.org



> -Original Message-
> From: sqlite-users-boun...@mailinglists.sqlite.org [mailto:sqlite-users-
> boun...@mailinglists.sqlite.org] On Behalf Of Darren Duncan
> Sent: Monday, 30 May 2016 1:45 PM
> To: SQLite mailing list 
> Subject: [sqlite] Postgres vs MySQL (was Re: Messages posted on Nabble not
> getting to list)
> 
> On 2016-05-28 12:49 PM, r.a.n...@gmail.com wrote:
> > @Daren
> >
> > Any reasons for the thumbs down on MySQL? Their workbench is better that
> Toad ...
> >
> >> On May 27, 2016, at 10:00 PM, Darren Duncan 
> wrote:
> >>
> >>> On 2016-05-27 2:28 PM, Balaji Ramanathan wrote:
> >>> But when I was debating between MySQL and SQLite for my project, I
> >>> almost didn't choose SQLite because of the archaic look and feel of
> >>> the sqlite.org website and support options available there.
> >>
> >> For the love of all that's good, don't choose MySQL for anything.  If
> >> you want something bigger than SQLite, look at Postgres instead of
> >> MySQL.  As a bonus, the Postgres documentation is much better. --
> >> Darren Duncan
> 
> r.a.nagy,
> 
> My judgement is based primarily on the DBMS server itself, which is the
> product being compared, not on separate client programs.  (And each DBMS
has
> multiple clients that work with it.)
> 
> For practical decision purposes, Postgres and MySQL both compete in the
same
> space as each other, multi-user client-server DBMSs.  This is a different
> space than SQLite competes in.  If you're doing a task appropriate to the
> space SQLite is in, I recommend using SQLite.  If you're doing a task
> appropriate to a multi-user client-server DBMS, I recommend for Postgres
and
> against MySQL.
> 
> Comparing the two...
> 
> Postgres is a high-quality project with a semi-regular predictable release
> schedule and has a strong emphasis on good quality and good security,
being
> as bug-free as possible and in not losing data.  Postgres has an order of
> magnitude of more, useful, features, and gains a lot more that people can
> notice each year.  Its design decisions make more practical sense and it
> strives to be a lot more compatible with the SQL standard where that makes
> sense.  In summary, it has tons more features people actually use and find
> valuable, and it has a strong emphasis on keeping the quality up.  When
> relatively rare bugs or security issues do occur, they are fixed promptly
and
> clear documentation is given on how to mitigate or recover from the
problem.
> Postgres also has better general user documentation.  Postgres even has
> useful features that Oracle doesn't have.  Postgres also has expert level
> support from multiple companies, and its BSD license (almost like public
> domain but not quite) means it can be used in any applications without a
> special license.
> 
> MySQL is a lower-quality project that has historically focused more on
user
> bases that don't put as much importance on the quality or persistence of
> their data and want to use the DBMS more as a dumb data store with most
work
> done application-side.  MySQL has an order of magnitude fewer useful
> features, often lacking things that are quite valuable in practice, and a
lot
> of the features it does have carry various gotchas or strange behaviors.
As
> such, performing a lot of tasks is more difficult or not possible without
> excessive circumlocution, and users are more likely to see wrong answers
or
> data loss due to either unexpected behaviors or bugs.  MySQL is notorious
for
> shipping half-baked code to production, see version 5.1 in particular.
MySQL
> is also notorious for going a long time claiming that particular important
> features are not important.  This includes a number of features that even
> SQLite has long had.  MySQL also has gone a long time without perceptively
> adding new features with much significance.  Their main advance in
features
> was between versions 3 and 5.1, the last being a decade ago, and since
then
> have mostly talked more about things like speed enhancements and not much
on
> interesting features.  The MySQL documentation isn't as good.  MySQL has a
> twin GPL/proprietary license so you need to pay Oracle lots of money if
you
> want to use it in some applications, and Oracle doesn't have as much
> motivation to make it a contender when they have their other bread and
butter
> DBMS to 

Re: [sqlite] What could be the cause of a zero length database file?

[dandl]

> Having an sqlite connection open on a deleted file can also happen in
posix
> land... Section 2.4 of "How to Corrupt" claims it can't happen on windows,
I
> guess that's technically true as in this scenario the file was deleted
before
> being opened, but it might be worth mentioning anyway (assuming the
mechanism
> can be understood).

The interesting question is whether a sufficiently insightful piece of
software could do anything about it (or even detect that it is happening).

I really don't know, but if that's what's doing it I can only say it's
really annoying.

Regards
David M Bennett FACS

Andl - A New Database Language - andl.org





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

Re: [sqlite] Problem loading sqlite3.dll on Windows XP

[dandl]

> I am using the provided sqlite3.dll from the download section which causes
> problems in Windows XP.
> 
> Our application uses a dynamic dll which is statically linked to
sqlite3.dll
> (32 bit) using a lib generated from the dll and def file. On Windows 7
there
> is no problem, but on windows XP when the application tries to load the
> dynamic dll using LoadLibrary I get the error code 127
(ERROR_PROC_NOT_FOUND)
> and I have no idea what this means. Dependency Walker shows no problem
with
> sqlite3.dll.
> 
> Do you know if there are any issues regarding Windows XP?

Did you check all its dependencies? The commonest cause of that error is
some other DLL it couldn't load. Maybe the C runtime library, which has been
updated quite a bit since XP. It certainly wouldn't work on a clean XP
install. Look for MSVCRxx.DLL.

Regards
David M Bennett FACS

Andl - A New Database Language - andl.org





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

Re: [sqlite] What could be the cause of a zero length database file?

[dandl]

> I assume you've read  .

Thanks. Yes, I had read it but it was worth a refresh.

The very last item gave pause...race condition on Windows. See below.

> It's worth stating that a zero-length database causes no problem for
SQLite.
> It does not trigger any error message if you open that database again and
> start putting legit things in it.  It isn't mentioned in the documentation
> (as far as I can see) and it's an edge-case for what constitutes a
legitimate
> SQLite database file, but it does work the way you'd want it to work.

Yes, I knew about that. Not the problem here.
> 
> You can intentionally create a zero-length sqlite database by completing
no
> commands which create schema.  For example, using the shell tool to create
a
> new database file and then ...
> 
> SQLite version 3.8.10.2 2015-05-20 18:17:19 Enter ".help" for usage hints.
> sqlite> BEGIN;
> sqlite> CREATE TABLE myTable (myCol TEXT PRIMARY KEY); INSERT INTO
> sqlite> myTable VALUES ('zxc'); INSERT INTO myTable VALUES ('zxc');
> Error: UNIQUE constraint failed: myTable.myCol
> sqlite> ROLLBACK;
> sqlite> COMMIT;
> Error: cannot commit - no transaction is active
> sqlite> .quit
> 
> The above technically fits the description you gave in your original post.

In my case there were about 4 tables created and about 50 INSERTs into them,
and the final COMMIT did not trigger an error. I don't think this is it.

There is an interesting race condition in Windows that is not much talked
about: in the shell. This particular program is run as part of a series of
test cases, and the setup for the test involves deleting the database file
and then running the program in a batch file. Since about Windows 7 the
shell does not wait for a file to be deleted before launching the next
process, and if that process uses a file that is being deleted there is a
race condition to see what happens next. Specifically it seems to be
possible to open a "shadow copy" of the file which can persist while the
original file is deleted. I don't understand exactly what happens, but I
suspect this might be my problem.

I've also put some effort into a clean shutdown, but I don't think that was
the problem. It's stopped happening for now, but not really because I found
and fixed a bug!

Regards
David M Bennett FACS

Andl - A New Database Language - andl.org


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

Re: [sqlite] What could be the cause of a zero length database file?

[dandl]

At present I can't answer that. I can't reliably reproduce the problem (and
never in the debugger) so I don't yet really know what the critical factor
is.

What I'm trying to understand is what guarantees Sqlite makes. If we assume
a perfectly valid sequence of SQL and API calls ending with COMMIT, followed
immediately by a panic shutdown with no API calls to release any prepared
statements, handles or whatever, is the data saved? If not, what is the
minimum that must be done to ensure the data is written out and the database
is valid? Is there a timing element? Are threads involved? Is
nondeterministic behaviour possible?

I think these are important things to understand for an embedded database,
and I couldn't find much in the docs.

Regards
David M Bennett FACS

Andl - A New Database Language - andl.org



> -Original Message-
> From: sqlite-users-boun...@mailinglists.sqlite.org [mailto:sqlite-users-
> boun...@mailinglists.sqlite.org] On Behalf Of Simon Slavin
> Sent: Wednesday, 25 May 2016 10:46 AM
> To: SQLite mailing list 
> Subject: Re: [sqlite] What could be the cause of a zero length database
file?
> 
> Do you close the database connection properly when you're done with it ?
> 
> Simon.
> ___
> 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] What could be the cause of a zero length database file?

[dandl]

The sequence is:
* open a new database file
* issue a sequence of several SQL commands in a single transaction (one
BEGIN, one COMMIT at the end).

The database ends up as a zero length file. Is this
a) a normal consequence of the above if the single transaction is aborted
(say due to an SQL error)
b) should never happen, and indicates a faulty sequence of API calls
c) something else?

It just started happening and it would help to know where to look.

Regards
David M Bennett FACS

Andl - A New Database Language - andl.org


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

[sqlite] Sqlite incompatibility with Postgres

[dandl]

> > 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.

Yes, agreed. I was indeed talking about second order functions (a function
that takes a function as an argument). This is well down the scale form the
full 'set of sets' and lambda calculus, but extremely useful. Andl has it,
in this one limited form.

> 
>   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

Yes, both mentally and in writing I naturally think in terms of functions. I
use 'operator' only to comply with TTM, but I think it adds a layer of
confusion.

> 
>   +/t

Just so. APL is a good source of ideas for second order functions.

> 
> > > 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.

I was never arguing about the status of LIMIT wrt SQL as a non-relational
'bag' language. I was only ever defending LIMIT as a well-defined relational
operator, which implies SQL with DISTINCT in force.

> 
> > 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).

I don't like NTH() as a name, it's misleading. What is being discussed is a
LOWEST(attribute, N) function with a second argument that is how many. There
is also HIGHEST(attribute, N). It is easily implemented in Andl using FOLD()
and TAKE() [Andl name for LIMIT], but I don't see it as a primitive.

Regards
David M Bennett FACS

Andl - A New Database Language - andl.org

[sqlite] Sqlite incompatibility with Postgres

[dandl]

> 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

[dandl]

> That's an interesting perspective.  If you're dealing with genuine sets,
and
> you define your language in terms of second-order operations, then
something
> like LIMIT could be included.  Would have to be, I guess.

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.

> But that's not what SQL is, or what LIMIT is.

Debatable. Many dialects of SQL provide aggregation, which is second order.

> You were rather dismissive of my nth() function, but that approximates
what
> LIMIT does (approximation is all that's possible) with a first-order
> operation.

Sorry. Your nth() is a second order function that can be implemented in Andl
or any language that supports generic aggregation. I would argue that it
suffers from the same problem, depending on definition and implementation.

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?

> BTW, I still think you're agreeing with me.  I'm insisting on using the
> "values of the tuple", implicitly restricted to first-order operations.
> Cardinality, as you say, as a second order *function*, hardly a "value".
But
> at least I understand your argument now.
> 
> > To that you can successively add negation, recursion, higher order
> > functions and fixpoint/while. Each of those allows operations that
> > others do not, but there is disagreement about which should be
> > considered 'relational'.
> 
> OK, I see.  It's fitting that the debate is about the definition of the
set
> of relational operators.
> 
> I'm conservative in that regard.  I'm wary of the complexity that higher-
> order operations bring to the language.  Each higher level brings (I
suspect)
> more complexity than the prior, while solving fewer new problems.

True, but for SQL at least the complexity arises from faults in the
language. The aim of Andl is to do more in the database language layer and
less in the application, with a language that makes higher order operations
much easier. For that I think you really need aggregation functions, ordered
functions and while (recursion).

> I think recursion is a good extension, and a good example.  It permits the
> expression of hierarchies.  It's indispensable ... for maybe 1% of
queries.

When you need it you really need it. The main driver is graphs that have
been expressed as relations with self-joins, but it's also needed to
implement an algorithm that is intrinsically an iterated computation eg
Mandelbrot, Sudoku solver.

> I guess you could convince me it makes SQL Turing Complete, but that's a
very
> dense thicket.  Recursive structures are useful.  If they could be
> manipulated without making the language Turing Compiete, I'd consider that
a
> plus.

[A side-note: there are two models of computation: the other is the lambda
calculus; the two are of equivalent power but quite different in
construction. Datalog with negation has the same computational power, deals
better with some kinds of data but not necessarily relations.]

Many writers say the same: a query language should use the lowest language
level possible, but for Andl to reach its goals it must make that power
available and accessible for when needed.

> > Thank you for the reference -- I didn't have that one. I'm familiar
> > with the material.
> 
> You're welcome, and it shows.
> 
> I think we've managed to hash out some agreement:
> 
> 1.  Second order functions are "relational", or can be, depending on one's
> definition.  We have support for them already in SQL.
> 
> 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.

Regards
David M Bennett FACS

Andl - A New Database Language - andl.org

[sqlite] Sqlite incompatibility with Postgres

[dandl]

> Restriction is applied to the values of the tuple.  The number of tuples
is
> not a value of the tuple.

No, I can't agree. Restriction is a membership test, a function on members:
should this tuple be included in the result set or not? Cardinality of a set
is a second order function on the members of the set, obtainable simply by
examining all the tuples in the set at the same time. There is no a priori
reason not to use cardinality in a membership functions.

> Neither of us is stupid, David.  I've boiled this down to something very
> simple.  If you look at it algebraically, I think you'll come to the same
> conclusion I have.

AS far as I'm concerned I have already done so, but it seems we reach
different conclusions. To settle that we either need new facts or a higher
authority.

> I wouldn't persist except that you're worth convincing.  Andl holds
promise,
> and seeks higher ground than SQL holds.  Insofar as possible, if I can I
want
> to help you get it right.

Much appreciated. Really.

> > For this query: calculate the average of that set of numbers after
> > excluding the 2 largest and 2 smallest values. Again, a pure set
> > operation.
> >
> > A reasonable solution would be to use two subqueries with ORDER BY
> > ASC/DESC and LIMIT 2, followed by an aggregation.
> 
> Sadly, no.  If we're talking about a "pure set operation", and the set is
{1,
> 1, 2}, the "two smallest" is {1, 2} but LIMIT 2 would yield {1, 1}.

No, that isn't a set, it's a multiset. A set has no duplicates.

> Here again, my putative nth() function *does* give the right answer,
simply
> because it's a function of the values, and not of the number of values.
> 
> > > There's no debate about the relational operators.
> 
> By which I meant: there's no debate about what they do.
> 
> > You might be surprised to learn that there is considerable academic
> > uncertainty as to exactly which operators should be included.
> 
> There's no uncertainty.  Some operators are defined in terms of others.
No
> suprise: even under De Morgan you don't need OR if you have NOT and AND.
The
> redundancy makes the language more expressive.

Not what I meant: that only covers the conjunctive queries. To that you can
successively add negation, recursion, higher order functions and
fixpoint/while. Each of those allows operations that others do not, but
there is disagreement about which should be considered 'relational'. De
Morgan won't help you there.

> LIMIT doesn't belong in this part of the discussion, btw, because it is
not
> defined relationally.

Yes it is. But it does require a second order function.

> > For example, is CTE RECURSIVE relational, or not?
> 
> http://wiki.epfl.ch/provenance2011/documents/foundations%20of%20databases-
> abiteboul-1995.pdf
> 
> Cf. Chapter 14.  Adding recursion changes the language.  It adds power; if
> memory serves permits answering second-order queries.

Thank you for the reference -- I didn't have that one. I'm familiar with the
material.

No, recursion (similar to fixpoint/while) makes the language Turing
Complete. Second order functions are not enough.

> > What about LEAD and LAG in the windowing functions? What about string
> > concatenation as an aggregation operator?
> 
> AFAIK there's no debate about those, either.  They can be expressed in
terms
> of simpler operations, and exist for convenience, such as it is.

This is the same debate as for LIMIT, because they rely on ordering. Indeed
it's possible to construct one from the other with something like this:

SELECT * FROM ( 
  SELECT *, ROW_NUMBER() OVER (ORDER BY name) as row FROM sys.databases 
 ) a WHERE row > 5 and row <= 10

You can't do SQL windowing without some kind of ordering comparison, but the
use of ordering in making a selection does not automatically make the query
non-relationally. The result is still just a set of tuples, no matter how
you choose them.

BTW this is one very useful extension for Sqlite, which is otherwise full
book on the relational hierarchy.

Regards
David M Bennett FACS

Andl - A New Database Language - andl.org

[sqlite] Sqlite incompatibility with Postgres

[dandl]

> > Consider this set of integers: 1,3,5,7,42,99,83,11,83,83
> >
> > In this case, there is no subset S1 of size 3 that satisfies your
> criterion.  In an SQL query, the set returned by LIMIT 3 would not be
defined
> uniquely.
> 
> What you've both said is essentially the point I was trying to make.
> 
> 1.  If you want a deterministic portable result for all valid invocations
of
> LIMIT, you need to either constrain it to use with a totally ordered set
(it
> would be an error to use it on something with duplicates) in order to
> guarantee the number of rows specified in the LIMIT argument, or you need
to
> possibly return a different number of rows than the LIMIT argument.
> 
> 2.  Otherwise, if exactly the number of specified rows must be returned
> without other restrictions, then the result is possibly indeterminate.

I agree, with one tiny tweak. The SQL standard already notes that certain
queries of this kind are "implementation-dependent". Here is an example.

"If the  does not contain an , or contains
an  that
does not specify the order of the rows completely, then the rows of the
table have an order that is defined
only to the extent that the  specifies an order and is
otherwise implementation-dependent."

So in option 2 the result should be considered "implementation-dependent"
and might be deterministic (based on information that is not part of the
query) or not.

> The options with point 1 are not only deterministic but fully relational.

Absolutely.

Regards
David M Bennett FACS

Andl - A New Database Language - andl.org

[sqlite] Sqlite incompatibility with Postgres

[dandl]

> Or we'll answer my original question by breaking down one of the above two
> options.  The documentation for the implementation may simply say that the
> order will be consistent in any one database connection, without ever
saying
> what the order will be.

This is perfectly consistent with Sqlite behaviour in other comparable
situations, and consistent with what the standard says about ORDER BY:
"implementation defined".

> Numerous users of SQLite have assumed this over the years, since if you
> cannot make this assumption you cannot implement cursors or scrolling
windows
> the way they want to, by changing an OFFSET (or remembering the key values
> for the first and last lines) as the user presses line-up, line-down,
page-up
> or page-down.  It's a natural use of SQLite inside any device with a small
> display and I'm sure programmers would be very annoyed if it was difficult
to
> program.  SQLite does what they want even though there's no documentation
> that says it'll work.

Absolutely! Application programmers depend heavily on SQL to do the heavy
lifting in paging, and all the databases I use support it. Andl supports it
in a way that is entirely consistent with relational theory, but many other
purist relational projects have decided not to. [The application programmer
is of course free to further sort the data locally if desired.]

My only point for the original post was that when you are dealing with an
SQL construct that is not defined by the standard, it's helpful if product
maintainers make some attempt to informally implement a common subset;. In
this case Sqlite is mostly compatible with Postgres, but not for LIMIT -1 vs
LIMIT ALL.

> SQLite does break the consistency rule under one circumstance, though it's
> very unlikely that a programmer would trigger it by accident.  It happens
> when you change the setting for
> 
> PRAGMA reverse_unordered_selects = boolean

If you do that presumably you know to expect what you get!

Regards
David M Bennett FACS

Andl - A New Database Language - andl.org

[sqlite] Sqlite incompatibility with Postgres

[dandl]

> The "problem" is to produce 3 rows where, relationally, the only answers
have
> 2 or 4 rows.  There is no right answer to the problem because there is no
> answer to the problem.

Which is what I said. The solution with 3 rows is unambiguous. You either
resolve this the way the standard does by making it "implementation defined"
or by forcing the query to be unambiguous by adding all the ORDER BY columns
to the SELECT list.

Regards
David M Bennett FACS

Andl - A New Database Language - andl.org

[sqlite] Sqlite incompatibility with Postgres

[dandl]

> I am quite certain nevertheless that LIMIT has no relational basis.
> Nothing based on Order By could.  

Then you are mistaken. 
1. Consider the following set S of integers: 1,3,5,7,42,99,83,11.
2. Divide it into two subsets such that S1 is of size 3 and all members of
S1 are larger than those in S2.

A sort is unnecessary -- there are many algorithms that can do that purely
based on set logic, the ability to compare members and the ability to
determine the cardinality of a set.

Another query: calculate the average of that set of numbers after excluding
the 2 largest and 2 smallest values. Again, a pure set operation.

> You lost me at "subset S of N tuples".  Which relational operator takes N
as
> an argument?

Restriction determines whether a tuple should be included or not; you also
need cardinality and less than (for comparing members).

> > Not so. In standard SQL ORDER BY establishes a comparison function
> > between tuples and is part of the DECLARE CURSOR syntax, but the
> > cursor exists regardless.
> 
> Regarding Order By and cursors, I'm referencing CJ Date.  The reason Order
By
> cannot appear in a subquery is that its output is not a table, but a
cursor.
> Whether Order By "establishes a comparison function between tuples" is
> irrelevant; so too does Where.  It's the product that's different.

He's correct as far as that goes. But curiously, ORDER BY LIMIT N could
appear in a subquery because it merely selects a subset -- the actual
ordering is irrelevant.

For this query: calculate the average of that set of numbers after excluding
the 2 largest and 2 smallest values. Again, a pure set operation.

A reasonable solution would be to use two subqueries with ORDER BY ASC/DESC
and LIMIT 2, followed by an aggregation. I don't know if any dialect of SQL
would allow that, but it's relationally valid. BTW Andl does allow it.

> If you accept that Order By can appear only in the outermost query, it's
> clear that it has no relational role at all.  It affects only the order in
> which the rows are returned to the caller.  Other than syntax, how is that
> different from a cursor?

As defined in the SQL standard ORDER BY can only appear that's true, but the
standard does not include LIMIT.

> So it's just a tiny communication optimization?  After all, compared to a
> network round trip, sorting the result (in order to apply LIMIT
> rationally) is usually far more expensive.  I bet no study has ever shown
> LIMIT to improve performance measurably, not that that would justify its
> existence.

[This is a sidetrack but no, in most cases network round-trip is important
enough to warrant a solution, although LIMIT is not the only solution.]

> Then I think you mean you agree!  Because LIMIT is nonrelational, it's
> *undefined*.  We have a long thread here that might be titled "what should
> LIMIT do?"  There's no debate about the relational operators.

You might be surprised to learn that there is considerable academic
uncertainty as to exactly which operators should be included. For example,
is CTE RECURSIVE relational, or not? What about LEAD and LAG in the
windowing functions? What about string concatenation as an aggregation
operator?

There's lots more down this particular wormhole, if you want to pursue it.

Regards
David M Bennett FACS

Andl - A New Database Language - andl.org

[sqlite] Sqlite incompatibility with Postgres

[dandl]

> > > > first   second
> > > > -   --
> > > > MarkSpark
> > > > Emily   Spark
> > > > MarySoper
> > > > Brian   Soper
> > > >
> > > > SELECT first,second FROM members ORDER BY second LIMIT 3
> 
> First, hat tip to Simon for providing a motivating example.  :-)
> 
> The question illustrates what I mean when I say Limit is not "rooted in
the
> data": in this case, "3" is not in the data, and is not a function of the
> data.  Having introduced an extraneous arbitrary element, ambituity and
> contradiction are inevitable.  It's practically the definition of a hack,
> right?  Does the job, albeit incorrectly.

Not so. First: a couple of facts to avoid misunderstanding.

1. Relational theory is a theory of set operations on tuples. Any query that
can be expressed as a set operation is valid.
2. In order to perform the familiar operations of restriction (WHERE) and
join, scalar operations are allowed on values of attributes (columns). Those
operations include:
a) compare equal (all types)
b) compare greater/less than, if the value is of any ordered type
c) expression evaluation, to construct new values of any type.

Any attribute that can be compared greater/less for the purpose of
restriction can also be used in a query that finds the largest (1 or N) or
smallest (1 or N) of that attribute. This is pure relational theory, most of
it already known to Codd back in 1972. Any disagreement so far?

So the "3" is a perfectly valid argument for a set-oriented theory: find a
subset S of N tuples with the following test for set membership: that each
member of S is greater than each member not in S when compared by certain
attributes, for N = 3. Pure set logic with a membership function.

> > I would say that this is an invalid query. As already applies for
> > DISTINCT and GROUP BY, the query parser should require that every
> > column in the column list should appear in the ORDER BY list. If it
> > does not, then the result is indeterminate.
> 
> Order By does not requre Group By, and the Select list is a *superset* of
the
> Order By list.  I'm not sure where you got the notion that the the Select
and
> Order By sets are equal.  "Order by 1" is always valid.

By analogy, not because they're the same. In order to apply LIMIT 3 the
query parser should require a test of set membership that is fully
determined for every member. It can do that by either requiring all select
list columns to appear in the ORDER BY, or by applying other constraints
such as a unique key. If it does not, then the results of the query depend
on information that is not part of the query (ie not deterministic).

> David, permit me to elaborate on my indictment of LIMIT.  You said
> earlier:
> 
> > You can't sort the relation, but you can certainly apply an order when
> > performing a query. How else would MIN() work?
> 
> I'm not disputing that.  Window functions even require multiple sorts in
the
> same query.
> 
> Whether or not "LIMIT is perfectly relational", we do know relational
algebra
> has no Sort operator, and that Order By is never part of an input to a
> relational operation (because of course relatational operands have no
order).
> Order By just produces a cursor for convenient traversal of the results.

Not so. In standard SQL ORDER BY establishes a comparison function between
tuples and is part of the DECLARE CURSOR syntax, but the cursor exists
regardless.

In a query retrieved by an external API there is no requirement for a cursor
to ever exist (it's undefined, and not required by relational theory).

> I'd be perfectly fine with a function I'll invent here and now to replace
> LIMIT:  nth().  It's a generalization of min(); the
> construction nth(C, 1) is equivalent to min(C).   You use it this way:
> 
>   SELECT first,second
>   FROM members
>   where second < nth(second, 2)
> 
> That query is based in the data.  It's unambiguous.  Given Simon's input,
it
> produces 2 rows; with "< 3" it produces 4 rows.  It can be used without
Order
> By (for the same reason min() can).  While it
> *implies* a sort, it doesn't require one (because indexes), as LIMIT does.
> And, like min() and unlike Order By, it can be used in a subquery.

The issue is find the "top N". This does not solve the problem.

> LIMIT is a hack.  It's an "obvious" extension to SQL, so simple it needn't
> even be part of it, because the program reading the rows from the DBMS can
> always stop wherever it wants.  Simple things are always implemented
freely -
> - even if unnecessary or misbegotten, simply because they're easy to do
and
> understand -- and LIMIT was
> no exception.   

I disagree. The point of LIMIT is that it is a complete query; the rows can
be returned in a single network round trip; the result set can be discarded.

Ironically, though, seemingly simple things are very
> hard, sometimes impossible, to explain mathematically.  In that way, LIMIT
> shelters under the same roof as NULL and SQL's use of bags instead of
sets.

[sqlite] Sqlite incompatibility with Postgres

[dandl]

> > All true.  But it brings up a question.  Suppose the following:
> >
> > first   second
> > -   --
> > MarkSpark
> > Emily   Spark
> > MarySoper
> > Brian   Soper
> >
> > SELECT first,second FROM members ORDER BY second LIMIT 3
> >
> I think a proper solution for this then is to treat the LIMIT as
approximate
> rather than exact; it indicates a desire rather than a promise.
> 
> In the scenario you describe, the query should return either 2 rows or 4
> rows, so that ALL of the rows whose second field value of "Spark" are, or
are
> not, returned.  Projecting this to there not being an ORDER BY clause,
either
> all rows are returned or zero rows are returned.  Thus the result is
> deterministic.
> 
> Whether returning above or below the limit is done, is a separate thing to
> decide, though I suggest returning above is better.

I would say that this is an invalid query. As already applies for DISTINCT
and GROUP BY, the query parser should require that every column in the
column list should appear in the ORDER BY list. If it does not, then the
result is indeterminate.

Sqlite already permits indeterminate queries, but other SQL engines do not.

Regards
David M Bennett FACS

Andl - A New Database Language - andl.org

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

[dandl]

> > 1. Why SQLite is popular.
> 
> The answers to those question mentioned in the podcast may be good ones
but I
> think the main reason is that it's free.  Completely, unmistakably, free.

Necessary but not sufficient.

It's free, and the licence is as non-restrictive as it is possible to be.

> You could make many changes to SQLite and people would continue to use it
but
> the thing that would decrease its usage fastest would be to charge for it.

Imposing licence conditions would come a close second. I'm not going to
mention GPL (or AGPL) but there are many conditions found in licence
agreements that run more than a para or two that would make it impossible to
use in particular applications.

Free AND non-restrictive licence is the killer combo for getting software
used, especially when it can be embedded.

> Nobody seems to mention this as an answer to that question.

Nobody wants to speak ill of more restrictive licence agreements either.

Regards
David M Bennett FACS

Andl - A New Database Language - andl.org

[sqlite] 64bit DLL vs 32bit

[dandl]

> > 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

An interesting post, but not I think from someone with a deep understanding
of the matter.

IMHO it would be perfectly possible to chunk either way, within the
limitations of a 4GB address space. The WOW64 layer already provides the
means for x86 apps to call the x64 Windows API. A thunk can do anything,
unless it's physically impossible or blocked by policy.

Regards
David M Bennett FACS

Andl - A New Database Language - andl.org

[sqlite] Sqlite incompatibility with Postgres

[dandl]

> 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.

Point 1: I think you'll find plenty of bits of SQL that are not relational
-- that's not the reason.
Point 2: LIMIT is perfectly relational, as long as it is applied with ORDER
BY. While the data set has no order, that in no way prevents performing a
query that does. Such as:

"Show me the customers that are within the first 10 when ordered by name."

You can't sort the relation, but you can certainly apply an order when
performing a query. How else would MIN() work?

> 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.

Your criticism re LIMIT is mistaken. It is a perfectly reasonably way to
support pagination, and can actually be performed (rather laboriously) using
SQL Window functions (which Sqlite does not have). It can also be useful in
some algorithms where you need the top 2 or 3 or whatever.

Your criticism re OFFSET has some basis. It is usually better (as you
suggest) to provide a previous row value for pagination. It's just that once
you've done LIMIT, OFFSET is easy to implement and sometimes useful.

> 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.

I think most developers are just astonished at how much it differs from one
dialect to another. Astonished, but not impressed.

Regards
David M Bennett FACS

Andl - A New Database Language - andl.org

[sqlite] 64bit DLL vs 32bit

[dandl]

> > 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.

Then I think you misunderstood.

You dropped in a 'module' without mentioning that this would have to be
written in C/C++. There is absolutely no way to call Sqlite statically from
any language other than C (or one of the rare languages that implement a
C-compatible ABI). So:

[Here HLL means Java/C#/Python/Perl/etc]

HLL -> Sqlite, DLL
HLL -> C-module dynamically linked -> Sqlite, DLL
HLL -> C-module statically linked -> Sqlite, no DLL

The reason this matters is that every 'module' injects its own world view.
If you want to call Sqlite from a HLL and make it look like other databases
then you use a 'module'. But if you want to call Sqlite from a HLL and see
exactly the same API that libpq exposes then you call the libpq DLL.

This is what I do in Andl. I need to control the communications channel in
ways that your 'modules' do not allow, so I use the libpq DLL.

Regards
David M Bennett FACS

Andl - A New Database Language - andl.org

[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] 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

[sqlite] Sqlite incompatibility with Postgres

[dandl]

> > Richard Hipp seems to be on record as saying:
> > "SQLite can be thought of as a derivative of PostgreSQL. SQLite was
> > originally written from PostgreSQL 6.5 documentation, and the SQLite
> > developers still use PostgreSQL as a reference platform to verify that
> > SQLite is working correctly."
> >
> > Not a major problem for me, just an interesting footnote.
> >
> 
> The way I recall hearing it expressed in talks I've watched (recorded
after
> the fact, never live) is that when situations arise that need resolution,
the
> question is often asked "what does PostgreSQL do?" Sadly, the backward
> compatibility requirements prohibit SQLite from being a 100% feature /
> implementation match.

Absolutely so. Nevertheless, it would be pretty minor to add LIMIT ALL as a
synonym for LIMIT -1. If you wanted to enhance compatibility.

Regards
David M Bennett FACS

Andl - A New Database Language - andl.org

[sqlite] Sqlite incompatibility with Postgres

[dandl]

> > Sqlite accepts (but Postgres does not):
> > LIMIT -1 OFFSET nnn
> > LIMIT -1
> >
> > These all have the same meaning of no limit, but there is no common
> > ground in the syntax.
> 
> Yes and Yes.  Documented behaviour.  There are some strange situations if
the
> two clauses can't both be satisfied, but if you're not playing silly
tricks
> you can depend on negative limits.

Thanks. I ran across this:
http://www.pgcon.org/2014/schedule/events/736.en.html

Richard Hipp seems to be on record as saying:
"SQLite can be thought of as a derivative of PostgreSQL. SQLite was
originally written from PostgreSQL 6.5 documentation, and the SQLite
developers still use PostgreSQL as a reference platform to verify that
SQLite is working correctly."

Not a major problem for me, just an interesting footnote.

Regards
David M Bennett FACS

Andl - A New Database Language - andl.org

[sqlite] Sqlite incompatibility with Postgres

[dandl]

It's a minor point, but can someone confirm that:

Postgres accepts (but Sqlite does not)
LIMIT ALL
LIMIT ALL OFFSET nnn
OFFSET nnn

Sqlite accepts (but Postgres does not):
LIMIT -1 OFFSET nnn
LIMIT -1

These all have the same meaning of no limit, but there is no common ground
in the syntax.

Regards
David M Bennett FACS

Andl - A New Database Language - andl.org

[sqlite] 64bit DLL vs 32bit

[dandl]

> bounces at mailinglists.sqlite.org] On Behalf Of Simon Slavin


> There must be a Windows element in there, though.  On the Mac I can create
a
> create a project in Xcode which has C, C++, Objective-C, Java and Python
code
> in (probably other languages too) and they can all call functions in one-
> another and the project compiles into one application without any
libraries.
> You do have to know some function-name conventions (e.g. Objective-C
> functions really start with an invisible '@' and C++ functions end with an
> invisible '_') but once you've figured that out it works.

Of course. DLL means windows; on Unix it would be .so or something else.

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.

> From what you wrote about Windows uses the library calling convention as a
> standardised way for one code in one language to call code from another.

Correct. The very first release of Windows had DLLs with an table of entry
points and a mechanism to load them dynamically and call them using a
C-style API. That same mechanism is alive and well today. The file layout
still has the same MZ signature from Mark Zbikowski, an MS-DOS developer.

Regards
David M Bennett FACS

Andl - A New Database Language - andl.org

[sqlite] 64bit DLL vs 32bit

[dandl]

> bounces at mailinglists.sqlite.org] On Behalf Of Simon Slavin


> It's only a certain kind of Windows user who wants DLLs for everything.
If
> that's what you need you are going to have to make sure you get the right
> DLL.  But the fact that most SQLite programmers don't use a DLL is why
you're
> having trouble getting simple clear answers on this thread -- your problem
is
> 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 and if the OS is x64 and any of the tools are x86 then
there is a good chance of getting a combo that doesn't play. Add that to
impenetrable error messages and limited diagnostic tools and it's
hair-tearing-out time again.

It definitely isn't a problem caused by Sqlite, but being able to reliably
source both x86 and x64 DLLs (and a way to tell them apart) would certainly
help.

Regards
David M Bennett FACS

Andl - A New Database Language - andl.org

[sqlite] SELECT DISTINCT question

[dandl]

Thanks. That's helpful. "Last visited" would explain that query.

But now a new puzzle:

> select col1, col2 from table group by col2
> is identical to select col1, distinct col2 from table

The SELECT railroad diagram does not allow DISTINCT in that position, only
right after SELECT?

Should that be:?
select distinct col1, col2 from table

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 Keith Medcalf
> Sent: Tuesday, 10 May 2016 12:59 PM
> To: SQLite mailing list 
> Subject: Re: [sqlite] SELECT DISTINCT question
> 
> 
> select col1, aggregateFunction(col2) from table group by col3 order by
col1
> 
> returns the result of the aggregate function applied to all "col2" values
in
> the "col3" group.  The col1 value is the last visited row in the group
which
> triggered the aggregate, with a special case for MIN and MAX, where the
col1
> value is from the last visited row which contained the min or max value of
> col2.
> 
> order by is, of course, applied to the result set after it is generated,
and
> may or may not affect the visitation order.
> 
> select col1, col2 from table group by col2
> 
> is identical to select col1, distinct col2 from table
> 
> in that the table is sorted by col2 and each col2 value is reported only
> once.  The col1 value is from the last visited row in each group.  Order
by
> may be applied "after the fact" to order the result set.  Order by may or
may
> not affect the visitation order.
> 
> (Note that "last visited" is often stated as "some random row" because the
> visitation order is an implementation detail of the query planner and may
> change from query to query based on the "shape" of the data and the
> particulars of how the query is solved internally.)
> 
> 
> > -Original Message-
> > From: sqlite-users-bounces at mailinglists.sqlite.org
> > [mailto:sqlite-users- bounces at mailinglists.sqlite.org] On Behalf Of
> > dandl
> > Sent: Monday, 9 May, 2016 18:19
> > To: 'SQLite mailing list'
> > Subject: Re: [sqlite] SELECT DISTINCT question
> >
> > The interesting thing about this query is that you can drop any of
> > DISTINCT, GROUP BY or ORDER BY and get the same result.
> >
> > But my question was not "how can I rewrite my query?". It was: how
> > does Sqlite interpret this SQL, given that it's probably invalid?
> >
> > Andl generates code for both Sqlite and Postgres, and I need to know
> > what that code does.
> >
> > 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
> > > Scott Robison
> > > Sent: Monday, 9 May 2016 4:13 PM
> > > To: SQLite mailing list 
> > > Subject: Re: [sqlite] SELECT DISTINCT question
> > >
> > > On Sun, May 8, 2016 at 7:45 PM, dandl  wrote:
> > >
> > > > Just to add to the below:
> > > >
> > > > S#| SNAME | STATUS | CITY
> > > > ---
> > > > S1| Smith | 20 | London
> > > > S2| Jones | 10 | Paris
> > > > S3| Blake | 30 | Paris
> > > > S4| Clark | 20 | London
> > > > S5| Adams | 30 | Athens
> > > >
> > > > SELECT DISTINCT "CITY" FROM "S" GROUP BY "CITY"  ORDER BY "S#" ASC
> > > > ;
> > > >
> > > > CITY
> > > > --
> > > > Paris
> > > > London
> > > > Athens
> > > >
> > > > I don't find it easy to explain this result.
> > > >
> > >
> > > My guess based on the available data is that, since you don't
> > > specify
> > which
> > > "S#" you want associated with each city, it is picking the max of
> > > each (coincidentally). If you want the minimum S# value, this seems to
> work:
> > >
> > > select distinct city from s group by city order by min("S#") asc;
> > >
> > > I'm not sure if that *should* work per "standard" SQL, but it does
> > > with SQLite. I'd have expected something like this to be necessary:
> >

[sqlite] SELECT DISTINCT question

[dandl]

> bounces at mailinglists.sqlite.org] On Behalf Of Keith Medcalf

> Why are you using BOTH distinct and group by on the same column?  You only
> need one or the other.  If you are redundantly redundant I would hope that
> the optimizer makes redundant (as in gets rid of, for those that are not
> English) the redundancies ...

This is generated code. Since Andl does not allow any duplicate rows, every
SELECT gets a DISTINCT unless the query provably cannot generate duplicates.
You need both GROUP BY and DISTINCT in cases where there is an aggregate
function (and some others). Say:

SELECT DISTINCT SUM(X) AS Y FROM T GROUP BY Z;

There is no way to predict from the query how many rows this will generate.
Without DISTINCT it can generate duplicates.

My question was really about why Sqlite did not complain on what is actually
not a valid query. [Andl is still a work in progress.]

Regards
David M Bennett FACS

Andl - A New Database Language - andl.org

[sqlite] SELECT DISTINCT question

[dandl]

Can you point me to something in the docs? It actually isn't quite the same.

Yes, I have discovered the Sqlite behaviour with aggregate functions. In 
Postgres and standard SQL it's an error -- the rule is that every column has to 
be in the GROUP BY if not used in the SELECT list or as input to an aggregate 
function. Sqlite just punts.

I didn't know about DISTINCT. The standard rule here is a bit different: every 
column in the ORDER BY must be in the select list. Since Andl generates SQL it 
helps if I know what that SQL will do.

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 Hick Gunter
> Sent: Monday, 9 May 2016 4:29 PM
> To: 'SQLite mailing list' 
> Subject: Re: [sqlite] SELECT DISTINCT question
> 
> This is documented behaviour for SQLite:
> 
> SELECT a, MAX(b) table;
> 
> Will return (one of) the a value(s) that comes from the same row as the
> MAX(b).
> 
> If there are not exactly on of MIN or MAX aggregate functions, SQLite is free
> to pick any row (within a group) to return non-aggregated columns from. Thus:
> 
> Select a,SUM(b), c from table group by a;
> 
> will return one of the c values from each group of a values.
> 
> The same thing applies for DISTINCT.
> 
> 
> -Urspr?ngliche Nachricht-
> Von: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-
> bounces at mailinglists.sqlite.org] Im Auftrag von Scott Robison
> Gesendet: Montag, 09. Mai 2016 08:13
> An: SQLite mailing list
> Betreff: Re: [sqlite] SELECT DISTINCT question
> 
> On Sun, May 8, 2016 at 7:45 PM, dandl  wrote:
> 
> > Just to add to the below:
> >
> > S#| SNAME | STATUS | CITY
> > ---
> > S1| Smith | 20 | London
> > S2| Jones | 10 | Paris
> > S3| Blake | 30 | Paris
> > S4| Clark | 20 | London
> > S5| Adams | 30 | Athens
> >
> > SELECT DISTINCT "CITY" FROM "S" GROUP BY "CITY"  ORDER BY "S#" ASC ;
> >
> > CITY
> > --
> > Paris
> > London
> > Athens
> >
> > I don't find it easy to explain this result.
> >
> 
> My guess based on the available data is that, since you don't specify which
> "S#" you want associated with each city, it is picking the max of each
> (coincidentally). If you want the minimum S# value, this seems to work:
> 
> select distinct city from s group by city order by min("S#") asc;
> 
> I'm not sure if that *should* work per "standard" SQL, but it does with
> SQLite. I'd have expected something like this to be necessary:
> 
> select city, min("S#") as x from s group by city order by x asc;
> 
> And if you only want the city:
> 
> select city from (select city, min("S#") as x from s group by city order by x
> asc);
> 
> But I'm not a SQL master.
> 
> Distinct used with group by seems redundant, but again, I might just not
> understand how they are useful together.
> 
> --
> Scott Robison
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> 
> 
> ___
>  Gunter Hick
> Software Engineer
> Scientific Games International GmbH
> FN 157284 a, HG Wien
> Klitschgasse 2-4, A-1130 Vienna, Austria
> Tel: +43 1 80100 0
> E-Mail: hick at scigames.at
> 
> This communication (including any attachments) is intended for the use of the
> intended recipient(s) only and may contain information that is confidential,
> privileged or legally protected. Any unauthorized use or dissemination of
> this communication is strictly prohibited. If you have received this
> communication in error, please immediately notify the sender by return e-mail
> message and delete all copies of the original communication. Thank you for
> your cooperation.
> 
> 
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] SELECT DISTINCT question

[dandl]

The interesting thing about this query is that you can drop any of DISTINCT,
GROUP BY or ORDER BY and get the same result.

But my question was not "how can I rewrite my query?". It was: how does
Sqlite interpret this SQL, given that it's probably invalid?

Andl generates code for both Sqlite and Postgres, and I need to know what
that code does.

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 Scott Robison
> Sent: Monday, 9 May 2016 4:13 PM
> To: SQLite mailing list 
> Subject: Re: [sqlite] SELECT DISTINCT question
> 
> On Sun, May 8, 2016 at 7:45 PM, dandl  wrote:
> 
> > Just to add to the below:
> >
> > S#| SNAME | STATUS | CITY
> > ---
> > S1| Smith | 20 | London
> > S2| Jones | 10 | Paris
> > S3| Blake | 30 | Paris
> > S4| Clark | 20 | London
> > S5| Adams | 30 | Athens
> >
> > SELECT DISTINCT "CITY" FROM "S" GROUP BY "CITY"  ORDER BY "S#" ASC ;
> >
> > CITY
> > --
> > Paris
> > London
> > Athens
> >
> > I don't find it easy to explain this result.
> >
> 
> My guess based on the available data is that, since you don't specify
which
> "S#" you want associated with each city, it is picking the max of each
> (coincidentally). If you want the minimum S# value, this seems to work:
> 
> select distinct city from s group by city order by min("S#") asc;
> 
> I'm not sure if that *should* work per "standard" SQL, but it does with
> SQLite. I'd have expected something like this to be necessary:
> 
> select city, min("S#") as x from s group by city order by x asc;
> 
> And if you only want the city:
> 
> select city from (select city, min("S#") as x from s group by city order
by x
> asc);
> 
> But I'm not a SQL master.
> 
> Distinct used with group by seems redundant, but again, I might just not
> understand how they are useful together.
> 
> --
> Scott Robison
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] SELECT DISTINCT question

[dandl]

Just to add to the below:

S#| SNAME | STATUS | CITY
---
S1| Smith | 20 | London
S2| Jones | 10 | Paris
S3| Blake | 30 | Paris
S4| Clark | 20 | London
S5| Adams | 30 | Athens

SELECT DISTINCT "CITY" FROM "S" GROUP BY "CITY"  ORDER BY "S#" ASC ;

CITY
--
Paris
London
Athens

I don't find it easy to explain this result.

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 dandl
> Sent: Monday, 9 May 2016 11:28 AM
> To: 'SQLite mailing list' 
> Subject: [sqlite] SELECT DISTINCT question
> 
> I have the following query:
> 
> SELECT DISTINCT "EVALA112"("S#") AS "^" FROM "S"   ORDER BY "S#" ASC ;
> 
> [This is generated code, not hand-written. The table S is from CJ Date
sample
> data.]
> 
> This query appears to work correctly. The function is an aggregation, and
> requires the data to be sorted.
> 
> This same query fails in Postgres with: "for SELECT DISTINCT, ORDER BY
> expressions must appear in select list".
> 
> In effect the reason is that a query in this form requires two sort
> operations, and a single query can have only one. To get this to work
> correctly, I shall either drop the DISTINCT or turn the second part into a
> subselect. That I understand.
> 
> The question is: how does Sqlite interpret this query? Why is it not an
> error? Is it because the query returns a single result, which does not
> require a sort?
> 
> Regards
> David M Bennett FACS
> 
> Andl - A New Database Language - andl.org
> 
> 
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] SELECT DISTINCT question

[dandl]

I have the following query:

SELECT DISTINCT "EVALA112"("S#") AS "^" FROM "S"   ORDER BY "S#" ASC ;

[This is generated code, not hand-written. The table S is from CJ Date
sample data.]

This query appears to work correctly. The function is an aggregation, and
requires the data to be sorted.

This same query fails in Postgres with: "for SELECT DISTINCT, ORDER BY
expressions must appear in select list".

In effect the reason is that a query in this form requires two sort
operations, and a single query can have only one. To get this to work
correctly, I shall either drop the DISTINCT or turn the second part into a
subselect. That I understand.

The question is: how does Sqlite interpret this query? Why is it not an
error? Is it because the query returns a single result, which does not
require a sort?

Regards
David M Bennett FACS

Andl - A New Database Language - andl.org

[sqlite] SQLite vs GPLed software

[dandl]

>From a purely numerical point of view, the largest numbers would be found in
devices, eg phones, cars, TVs, clocks/timers, etc. Lots of GPL in there, but
also lots of other licences too. I have no idea how the GSM stack is
licensed, for example, but I think there are more GSM phones than instances
of Sqlite.

And I certainly can't think of any cases where a GPL licence has been of
specific benefit in achieving wider usage, as compared to the approach taken
by Sqlite.

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 Richard Hipp
> Sent: Tuesday, 3 May 2016 10:11 AM
> To: SQLite mailing list 
> Subject: Re: [sqlite] SQLite vs GPLed software
> 
> On 5/2/16, Scott Robison  wrote:
> > I don't have an answer, but given that each linux distro of dozens or
> > hundreds of independently sourced packages has many separate instances
> > of the GPL, that would begin to eat into SQLITE'S lead. But probably
> > not enough to win.
> 
> What GPLed packages are on Android?  Compared to Android, all other Linux
> distros are rounding error, I think (correct me if I'm wrong).
> 
> Also, what percentage of those other GPLed packages statically link
against
> SQLite?
> 
> --
> 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