Re: [sqlite] Partial Indexes and use of LIKE

On 11/1/16, Mark Lawrence wrote: > Hello all, > > The documentation for partial indexes (section 3.2) lists "=, <, >, <=, > >=, <>, or IN" as operators that will trigger the use of an > index WHERE c IS NOT NULL. > > It seems to me that LIKE should also be in that list, given

Re: [sqlite] Segmentation fault on OpenBSD

Thanks. I'm still unable to get SQLite 3.15.0 to fail, in any way, using your schema and data. Do you have any further hints on how I can cause the problem to be expressed? Is assertion fault reliably reproduced on your system? On 11/1/16, mark wrote: > On Tue Nov 01, 2016 at

Re: [sqlite] Segmentation fault on OpenBSD

On Tue Nov 01, 2016 at 03:15:43PM +, Simon Slavin wrote: > > On 1 Nov 2016, at 3:14pm, Rob Willett > wrote: > > > We use the Perl DBD module all the time. What I would expect to see > > is [... good stuff ...] > > Ah, thanks for the explanation. I always had

Re: [sqlite] Segmentation fault on OpenBSD

On 1 Nov 2016, at 3:14pm, Rob Willett wrote: > We use the Perl DBD module all the time. What I would expect to see is [... > good stuff ...] Ah, thanks for the explanation. I always had trouble understanding Perl's approach to anything. Hope the OP's problem

Re: [sqlite] Segmentation fault on OpenBSD

Simon, We use the Perl DBD module all the time. What I would expect to see is (IGNORE THE LINE BREAKS) my $sth = $dbh->prepare("INSERT INTO func_begin_change( author, author_contact, author_contact_method, author_shortname,

Re: [sqlite] Segmentation fault on OpenBSD

> On 1 Nov 2016, at 2:38pm, mark wrote: > >VALUES >( >?, >?, >?, >?, >?, >?, >?, >?, >? >) >; > > At least that is what I am passing to Perl's

Re: [sqlite] Segmentation fault on OpenBSD

On Tue Nov 01, 2016 at 09:21:04AM -0400, Richard Hipp wrote: > > The text of the SQL that is being prepared got cut off. Can you > please send me the complete SQL statement that is being run at the > point of the error? INSERT INTO func_begin_change( author,

Re: [sqlite] Autoincrement sequence not updated by UPDATE

sqlite> create table tbl1 ...> ( ...> id integer primary key autoincrement, ...> someOtherfield, ...> yetAnotherField ...> ); sqlite> create trigger trg_imInChargeAndSayNoAutoincrementUpdates ...> before update of id on tbl1 ...> begin ...> select raise(abort, 'Bad

Re: [sqlite] Autoincrement sequence not updated by UPDATE

Interesting. I am using an old version, 5.5.34 The database engine may also make a difference. I was using MyISAM. INNODB may be different. A quick scan of the MySQL documentation suggests INNODB is different. For example, it doesn't store the next value in the database. If you specify an

Re: [sqlite] Segmentation fault on OpenBSD

On 10/31/16, mark wrote: > #18 0x17d970826118 in sqlite3RunParser (pParse=0x7f7e9200, > zSql=0x17db9b16a600 "INSERT INTO\nfunc_begin_change(\n > author,\nauthor_contact,\nauthor_contact_method,\n > author_shortname,\nid,\n

Re: [sqlite] Autoincrement sequence not updated by UPDATE

Well, I got different results (so maybe it's version related). I tried it on MySQL v5.7.16-log: ++--+ | id | v| ++--+ | 2 | two | | 10 | one | ++--+ ++--+ | id | v| ++--+ | 10 | one | | 40 | two | ++--+ ++---+ | id | v |

Re: [sqlite] Segmentation fault on OpenBSD

On 10/31/16, mark wrote: > #3 0x17d9707fb288 in valueFromExpr (db=0x17dbbac8e808, > pExpr=0x17dc0252b288, enc=1 '\001', affinity=68 'D', > ppVal=0x7f7e7f08, > pCtx=0x7f7e7f70) at sqlite3.c:70020 > #4 0x17d9707fbaa0 in stat4ValueFromExpr

Re: [sqlite] Segmentation fault on OpenBSD

On Tue Nov 01, 2016 at 02:21:02AM +0100, mark wrote: > assertion "0" failed: file "sqlite3.c", line 70020, function > "valueFromExpr" I read the source code a little further and found that the NEVER at line 70020 is actually a macro: if( NEVER(op==TK_REGISTER) ) op = pExpr->op2;

Re: [sqlite] Partial Indexes and use of LIKE

Mark Lawrence wrote: > The documentation for partial indexes (section 3.2) lists "=, <, >, <=, > >=, <>, or IN" as operators that will trigger the use of an > index WHERE c IS NOT NULL. > > It seems to me that LIKE should also be in that list, given that it > will also only match when c is not

Re: [sqlite] Autoincrement sequence not updated by UPDATE

>> It remembers.. >Ah, neat. Thanks for the testing. As a slight aside. It also never resets the value. We had a problem where the number of inserts had incremented the AUTO INCREMENT value to MAXINT (it took a few years). It then stops. We fixed it by changing id to a BIGINT, but you can

Re: [sqlite] Autoincrement sequence not updated by UPDATE

On 1 Nov 2016, at 11:44am, Andy Ling wrote: > It remembers.. Ah, neat. Thanks for the testing. And the "show create table" command you used makes it clear that the engine keeps a record for the table. Apparently a single value for the table's primary key rather

Re: [sqlite] Autoincrement sequence not updated by UPDATE

It remembers.. mysql> create table tt (id int NOT NULL AUTO_INCREMENT, v TEXT, PRIMARY KEY(id)) ; Query OK, 0 rows affected (0.04 sec) mysql> insert into tt (v) VALUES("one"); Query OK, 1 row affected (0.00 sec) mysql> update tt set id=10 where v="one"; Query OK, 1 row affected (0.00 sec)

Re: [sqlite] Autoincrement sequence not updated by UPDATE

On 2016/11/01 1:01 PM, Simon Slavin wrote: On 1 Nov 2016, at 10:45am, R Smith wrote: D - Horrible if you up some key value significantly and then update it back down, because there is no way the Autoinc value should *EVER* be able/allowed to come back down. It's a

Re: [sqlite] Autoincrement sequence not updated by UPDATE

On 1 Nov 2016, at 11:14am, Andy Ling wrote: > MySQL lets you fiddle. I don't have MySQL. To satisfy my curiosity, could you try this: create table tt (id int NOT NULL AUTO_INCREMENT, v TEXT, PRIMARY KEY(id)) ; insert into tt (v) VALUES("one"); update tt set id=10

Re: [sqlite] Autoincrement sequence not updated by UPDATE

> Which, according to GB, is what some other SQL engines do: attempts to change > a value > in that column using UPDATE always generate an error. I didn't know that. > I looked it up. > Apparently Microsoft's SQLSERVER blocks it, but I was unable to find > anything mentioning > how any of

Re: [sqlite] Autoincrement sequence not updated by UPDATE

On 11/1/16 7:01 AM, Simon Slavin wrote: On 1 Nov 2016, at 10:45am, R Smith wrote: D - Horrible if you up some key value significantly and then update it back down, because there is no way the Autoinc value should *EVER* be able/allowed to come back down. It's a one-way

Re: [sqlite] Autoincrement sequence not updated by UPDATE

On 1 Nov 2016, at 10:45am, R Smith wrote: > D - Horrible if you up some key value significantly and then update it back > down, because there is no way the Autoinc value should *EVER* be able/allowed > to come back down. It's a one-way street. This is an additional

Re: [sqlite] Autoincrement sequence not updated by UPDATE

On 2016/11/01 11:52 AM, Simon Slavin wrote: Unfortunately the response is going to be along the lines of "We can't do this for compatibility reasons because there might be a program out there that does it.". And rightly so... There is no reason to use Autoincrement other to be sure newly

Re: [sqlite] Segmentation fault on OpenBSD

On Mon Oct 31, 2016 at 05:42:32PM -0400, Richard Hipp wrote: > On 10/31/16, mark wrote: > > On Mon Oct 31, 2016 at 04:04:00PM -0400, Richard Hipp wrote: > >> Is this reproducible? > > > > Yes... in that I can reliably get it to segfault. Duplicating the build > > and/or statements

Re: [sqlite] Autoincrement sequence not updated by UPDATE

On 1 Nov 2016, at 6:56am, GB wrote: > And that is why several SQL Engines prevent AUTOINCREMENT columns from being > UPDATEd. I think that would be a good fix in SQLite's case. If you're relying on SQLite to generate these unique numbers for you then you shouldn't then change

[sqlite] Partial Indexes and use of LIKE

Hello all, The documentation for partial indexes (section 3.2) lists "=, <, >, <=, >=, <>, or IN" as operators that will trigger the use of an index WHERE c IS NOT NULL. It seems to me that LIKE should also be in that list, given that it will also only match when c is not null. I can force the

Re: [sqlite] Autoincrement sequence not updated by UPDATE

On 28 Oct 2016, at 12:47, Simon Slavin wrote: It guess it comes down to what one wants from "INTEGER PRIMARY KEY AUTOINCREMENT". If the requirement is only-ever-increasing then this is a bug. The behaviour described at https://sqlite.org/autoinc.html seems to match this requirement:

Re: [sqlite] Autoincrement sequence not updated by UPDATE

And that is why several SQL Engines prevent AUTOINCREMENT columns from being UPDATEd. I think it should be fixed in the Docs, not in Code. Richard Hipp schrieb am 31.10.2016 um 18:56: Note that the implied purpose of AUTOINCREMENT is to generate a unique and immutable identifier. Running