Re: [sqlite] Unicode quote characters parsing in FTS5 queries

[Simon Slavin]

On 3 Feb 2020, at 9:30pm, David Guillen Fandos  wrote:

> However my queries where choking on some input that contained the U+2018 and 
> U+2019 characters. It seems like these chars are being treated like a regular 
> quote character

At least one version of SQL respects those characters for quoting strings.  
Can't remember which one at the moment.  I suspect that the problem you're 
encountering is intentional.

One test you can try is to double that character (I'm typing the code, you type 
the character)

'abcU+2018U+2018def'

and see whether this is still a syntax error, and whether you get zero, one or 
two of those characters in the output string.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Unicode quote characters parsing in FTS5 queries

[David Guillen Fandos]

Hello there,

I was running some queries using FTS5 in my database and I generally
sanitize the input by removing non-ascii characters, except for all the
unicode chars above 128 or so. That way I get rid of stuff like quotes,
double quotes, periods, etc.

However my queries where choking on some input that contained the U+2018
and U+2019 characters. It seems like these chars are being treated like
a regular quote character (I get the same error as if I replaced it with
a regular quote character). Is this intended behaviour? The error I'm
getting is "Result: fts5: phrase queries are not supported
(detail!=full" (and a SQLITE_ERROR calling step()).

I'm quite confused with the behaviour I'm seeing here. Any guidance is
appreciated.

Thanks

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

Re: [sqlite] Check constrain execution timing change? (Now a bug)

[Thomas Kurz]

> You say that you want to prevent the use of the string literal '123'
> for inserting into the integer field x.  That will no longer be
> possible in SQLite beginning with 3.32.0 (assuming the change
> currently on trunk goes through.)
> But, why do you want to do that?

You are right. I apologize for my first excitement. The new behavior is correct 
and consistent to other RDBMs. Sometimes one misses the forest for the trees :-)

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

Re: [sqlite] Table has different rootpage in EXPLAIN and sqlite_master

[x]

Hi David,

The two queries were as follows

explain select vCol,* from Race,Meta.vCols;
and
select * from meta.sqlite_master where type='table';

Sorry, I don’t have a copy of the unvacuumed db.



From: sqlite-users  on behalf of 
David Raymond 
Sent: Monday, February 3, 2020 6:19:31 PM
To: SQLite mailing list 
Subject: Re: [sqlite] Table has different rootpage in EXPLAIN and sqlite_master

They shouldn't be different, no. Do you have a copy of the weird version of the 
database still, or have a copy of the explain text and the sqlite_master 
contents? Was there another index or table whose root page is what was listed 
in the explain output? For example, were you expecting it to use the table, but 
it used a covering index instead?


-Original Message-
From: sqlite-users  On Behalf Of x
Sent: Monday, February 3, 2020 1:07 PM
To: Discussion of SQLite Database 
Subject: [sqlite] Table has different rootpage in EXPLAIN and sqlite_master

Differing by 1. I noticed this today for 1 table only but not always - 
sometimes they were equal. I’ve been unable to reproduce it after a vacuum. 
Does this indicate a corrupt db or is it a case of it can happen for some 
reason I’ve missed?
___
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] Table has different rootpage in EXPLAIN and sqlite_master

[David Raymond]

They shouldn't be different, no. Do you have a copy of the weird version of the 
database still, or have a copy of the explain text and the sqlite_master 
contents? Was there another index or table whose root page is what was listed 
in the explain output? For example, were you expecting it to use the table, but 
it used a covering index instead?


-Original Message-
From: sqlite-users  On Behalf Of x
Sent: Monday, February 3, 2020 1:07 PM
To: Discussion of SQLite Database 
Subject: [sqlite] Table has different rootpage in EXPLAIN and sqlite_master

Differing by 1. I noticed this today for 1 table only but not always - 
sometimes they were equal. I’ve been unable to reproduce it after a vacuum. 
Does this indicate a corrupt db or is it a case of it can happen for some 
reason I’ve missed?
___
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] Table has different rootpage in EXPLAIN and sqlite_master

[x]

Differing by 1. I noticed this today for 1 table only but not always - 
sometimes they were equal. I’ve been unable to reproduce it after a vacuum. 
Does this indicate a corrupt db or is it a case of it can happen for some 
reason I’ve missed?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] "Standard SQL" ?

[James K. Lowden]

On Sun, 2 Feb 2020 10:05:11 +0100
Markus Winand  wrote:

> When you say ?many standards? do you mean the different releases
> those standards have?

Yes. 

> IMHO, there is only one SQL standard, namely ISO/IEC 9075. The
> current and technically only valid version is that of 2016 (even
> though an extension was added in 2019).

That's a defensible proposition.  And that's one way to measure an
implementation.  

I think of standards more as accreting.  The longer a feature has been
standardiized, the more succeeding versions of the standard include it,
the "more standard" it is.  

--jkl


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

Re: [sqlite] Check constrain execution timing change? (Now a bug)

[James K. Lowden]

On Mon, 3 Feb 2020 10:45:50 +0100
Dominique Devienne  wrote:

> On Sun, Feb 2, 2020 at 12:50 AM Richard Hipp  wrote:
> 
> > On 2/1/20, Thomas Kurz  wrote:
> > > Does this mean there will be no possibility to prevent inserting
> > > a string into an integer column anymore?
> > >
> > > create table x (x integer check (typeof(x) == 'integer'));
> > > insert into x values ('1');
> > >
> > > --> will pass in future versions???
> >
> > I think that is what it means.  yes.
> 
> Wow... I haven't caught up on this thread, but that's really really
> bad IMHO 
...
> the fact we can no longer do that would be a real shame. I wonder
> where this is coming from... --DD

It's a good thing, really!  The rule would be that the provided value is
converted to the column's type before inserting.  If it can't be
converted, it's still an error.  If it can, great.  I think you'll find
that's the behavior of most SQL DBMS implementations. 

After all, of what significance is the type of the provided argument?
Do you want to force applications to "pre-convert" values the DBMS can
convert implicitly?  Do you want binding choices in the application to
drive the datatype in the database, or do you want the database to
enforce types?  

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

Re: [sqlite] Check constrain execution timing change? (Now a bug)

[Dominique Devienne]

On Mon, Feb 3, 2020 at 5:35 PM Richard Hipp  wrote:
> On 2/3/20, Dominique Devienne  wrote:
> > On Sun, Feb 2, 2020 at 12:50 AM Richard Hipp  wrote:
>
> This is the SQL:
>
>   CREATE TABLE t1(x INT CHECK(typeof(x)=='integer'));
>   INSERT INTO t1(x) VALUES('123');
>
> You say that you want to prevent the use of the string literal '123'
> for inserting into the integer field x.  That will no longer be
> possible in SQLite beginning with 3.32.0 (assuming the change
> currently on trunk goes through.)
>
> But, why do you want to do that?  How do you prevent the use of a
> string literal to initialize an integer field in MySQL, PosgreSQL, SQL
> Server, and Oracle - all of which accept and run the SQL above
> (without the CHECK constraint) with no errors?

Right. Implicit conversion also happen in these other DBs (I just
checked Oracle,
but I trust you're way more qualified to assert that me).

> If your goal is to prevent an actual string from being stored in the
> "x" column, then the legacy CHECK constraint still works for that.
> The following insert still fails:
>
>INSERT INTO t1(x) VALUES('xyzzy');

Right again. It fails with "ORA-01722: invalid number" on Oracle.
(no need for a CHECK constraint of course)

> But, you will no longer be allowed to prevent the type coercion that
> forces the '123' value into an integer 123, I think.  At least, I do
> not see a way to do that on trunk right now.

OK. I was more thinking of the '123' staying as text-typed in the DB.
But if it is coerced into the column's type (well, "affinity", not type per se),
then whether the value is bound as a string or a integer should be immaterial.

I still think my code shouldn't be binding values of a type different
than the column's,
and would still greatly prefer "strong *static* typing", which I
emulated with CHECK typeof(),
since it smells like a bug in the code IMHO, but as long as the stored
value is "OK", sure
that makes little differences in the end.

So now that I understand the better, so be it I guess.
I'm sure you have a good reason to make that change, despite the
surprising break in BC for SQLite.

Thanks for taking the time to spell it out for me. --DD

PS: I still wish for a pragma for strong static typing (no need for
CHECK typeof()),
  and now also wish for that to happen even before implicit
conversions. But I've long
  accepted this is unlikely to ever happen :(
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] shell.c compilation error when built with SQLITE_OMIT_AUTHORIZATION defined

[Cameron, Jonathan]

There's usage of sqlite3_set_authorizer within shell.c that's not wrapped with 
the test for if SQLITE_OMIT_AUTHORIZATION is defined that prevents compilation 
of the shell.c if the configuration includes this define.

I've provided a diff with a fix below:

$ git diff
diff --git a/shell.c b/shell.c
index 053180c..3a3b8b4 100644
--- a/shell.c
+++ b/shell.c
@@ -8460,10 +8460,12 @@ sqlite3expert *sqlite3_expert_new(sqlite3 *db, char 
**pzErrmsg){
 rc = idxCreateVtabSchema(pNew, pzErrmsg);
   }

+#ifndef SQLITE_OMIT_AUTHORIZATION
   /* Register the auth callback with dbv */
   if( rc==SQLITE_OK ){
 sqlite3_set_authorizer(pNew->dbv, idxAuthCallback, (void*)pNew);
   }
+#endif

   /* If an error has occurred, free the new object and reutrn NULL. Otherwise,
   ** return the new sqlite3expert handle.  */

Thanks,
Jonathan



CONFIDENTIALITY NOTICE: This email and any attachments are for the sole use of 
the intended recipient(s) and contain information that may be Garmin 
confidential and/or Garmin legally privileged. If you have received this email 
in error, please notify the sender by reply email and delete the message. Any 
disclosure, copying, distribution or use of this communication (including 
attachments) by someone other than the intended recipient is prohibited. Thank 
you.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Issue (not bug) with generated columns

[Tony Papadimitriou]

Consider this:

.print Issue with generated columns

create table a(n,s as (n+1));
insert into a values(1),(2),(3);
select * from a;

create table b as select * from a;
.print table b converted the generated column into a regular column
select * from b;

delete from a;
insert into a select * from b;
select * from a;

.print Fails as there are two real columns in b but only one in a
-- Error: near line 12: table a has 1 columns but 2 values were supplied


It’s common practice (for some of us, at least) to copy a table to another for 
manipulation,
and then copy the finished work back to the original table.

With generated columns the new table gets the generated columns as regular 
columns (no complains).
Now, copying back to the original table produces an error as there is a 
mismatch in the number of real columns.

Of course one may specify each and every column manually (and there could many 
of them).

Suggestions:

1. It’d be nice to have some way to specify `select *` that would ignore 
generated columns.
Maybe, `select real *` or something of that sort to indicate we want `*` to 
select only actual columns, not virtual.

2. Another possibility would be, when copying over virtual columns, the column 
to be counted (for position) but data ignored as it’s read-only.

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

[sqlite] Issue (not bug) with generated columns

[Tony Papadimitriou]

Consider this:

.print Issue with generated columns

create table a(n,s as (n+1));
insert into a values(1),(2),(3);
select * from a;

create table b as select * from a;
.print table b converted the generated column into a regular column
select * from b;

delete from a;
insert into a select * from b;
select * from a;

.print Fails as there are two real columns in b but only one in a
-- Error: near line 12: table a has 1 columns but 2 values were supplied


It’s common practice (for some of us, at least) to copy a table to another for 
manipulation,
and then copy the finished work back to the original table.

With generated columns the new table gets the generated columns as regular 
columns (no complains).
Now, copying back to the original table produces an error as there is a 
mismatch in the number of real columns.

Of course one may specify each and every column manually (and there could many 
of them).

Suggestions:

1. It’d be nice to have some way to specify `select *` that would ignore 
generated columns.
Maybe, `select real *` or something of that sort to indicate we want `*` to 
select only actual columns, not virtual.

2. Another possibility would be, when copying over virtual columns, the column 
to be counted (for position) but data ignored as it’s read-only.

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

[sqlite] bug report (INSTR() ignores NOCASE on columns)

[Stephan Senzel]

INSTR() ignores NOCASE on columns

---

example:

SELECT * FROM table WHERE INSTR(column, ' castle ') > 0

returns datasets with 'castle' only, without 'Castle', even if the 
column is set to NOCASE


---

LIKE doesn't have this problem, works well

SELECT * FROM table WHERE column LIKE '% castle %'

returns 'castle' and 'Castle' when column is set to NOCASE
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Check constrain execution timing change? (Now a bug)

[Richard Hipp]

On 2/3/20, Dominique Devienne  wrote:
> On Sun, Feb 2, 2020 at 12:50 AM Richard Hipp  wrote:
>
>> On 2/1/20, Thomas Kurz  wrote:
>> >
>> > create table x (x integer check (typeof(x) == 'integer'));
>> > insert into x values ('1');
>> >
>> > --> will pass in future versions???
>>
>> I think that is what it means.  yes.
>
> Wow... I haven't caught up on this thread, but that's really really bad
> IMHO,
> and would consider that a serious regression. I've been enforcing
> "strong-typing",
> (or "inflexible-typing" if you prefer Richard) for many schemas, and
> the fact we can
> no longer do that would be a real shame. I wonder where this is coming
> from... --DD

This is the SQL:

  CREATE TABLE t1(x INT CHECK(typeof(x)=='integer'));
  INSERT INTO t1(x) VALUES('123');

You say that you want to prevent the use of the string literal '123'
for inserting into the integer field x.  That will no longer be
possible in SQLite beginning with 3.32.0 (assuming the change
currently on trunk goes through.)

But, why do you want to do that?  How do you prevent the use of a
string literal to initialize an integer field in MySQL, PosgreSQL, SQL
Server, and Oracle - all of which accept and run the SQL above
(without the CHECK constraint) with no errors?

If your goal is to prevent an actual string from being stored in the
"x" column, then the legacy CHECK constraint still works for that.
The following insert still fails:

   INSERT INTO t1(x) VALUES('xyzzy');

But, you will no longer be allowed to prevent the type coercion that
forces the '123' value into an integer 123, I think.  At least, I do
not see a way to do that on trunk right now.

I have put a "Pre-release Snapshot" of the latest code on the Download
page to try to make it easier for people to try out this new change.

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

Re: [sqlite] single table data collapse with constraints

[Golding Robert]

Thank you to everyone who has provided help in resolving this problem for me.


Regards,

Rob




Rob Golding 
Geospatial Specialist (Geospatial Analytics Team)

Asset Information Services: inspiring and enabling through the power of data
Willen, The Quadrant: MK, Elder Gate, Milton Keynes, MK9 1EN.

Intermal: 085 76537
External: 01908 722537
E-mail: robert.gold...@networkrail.co.uk

Network Rail – working for you


Visit the new AIS Hub site for information, user guides, key contacts, and more 
on all our services.  

Advanced notice of leave:

-Original Message-
From: sqlite-users  On Behalf Of 
Jean-Luc Hainaut
Sent: 01 February 2020 19:01
To: SQLite mailing list 
Subject: Re: [sqlite] single table data collapse with constraints

Hi Robert,

I'm a bit late: I missed your post in the recent tsunami (of posts)!

Your question relates to one of the basic primitives of temporal databases, 
"coalescing". Basic but far from simple if you want to express it in pure SQL. 
On the contrary, the answer is much simpler if you code it as a loop in any 
host language.

You could consult this tutorial about the concepts of temporal databases:
https://staff.info.unamur.be/dbm/Documents/Tutorials/SQLfast/SQLfast-Case09-Temporal-DB(1).pdf


... and this one about temporal data manipulation, including coalescing:
https://staff.info.unamur.be/dbm/Documents/Tutorials/SQLfast/SQLfast-Case10-Temporal-DB(2).pdf


Regards

Jean-Luc Hainaut


On 29/01/2020 16:02, Golding Robert wrote:
> Hi,
>
> I am emailing to ask if someone could advise me how to create a query or 
> queries which will collapse some data based on a limited number of 
> constraints.
> I am currently attempting to complete this task using DB Browser for SQLite. 
> I  have tried to write a WITH RECLUSIVE statement as I think this is the 
> requirement but am struggling with both the abstraction and syntax.
>
>
> I have data contained with a single table of structure:
> CLS1 field (text)
> CLS2 field (integer)
> START field (integer)
> END field (integer
>
> I need to collapse the data based on the matching of values in fields CLS1 
> and CLS2; the final constraint is that if END and START values are continuous 
> of the another record then they can be collapsed. Therefore records should 
> only be collapsed if gaps do not exist. The results then need to written to a 
> new table, leaving the original data as is.
>
> Input data: assumptions
>
>1.  Data may or may not be ordered
>2.  Duplicates may or may not exist
>3.  Start and end values could be the same
>4.  Start values are normally lower that the end value, however the high 
> value could be in the start field
>5.  Assume that there is no overlap in terms of start and end 
> values (namely if they can be joined then one will stop where the next 
> starts)
>
> Input data:  example
> CLS1,CLS2,START,END
> ABC1,100,0,1
> ABC1,100,1,1 (start and end values could be the same, in the first 
> instance assume that they may be dissolved if possible, if they cannot 
> the record need to be retained)
> ABC1,100,1,3
> ABC1,100,1,3 (duplicates may or may not be present, if present then 
> they can be dissolved into a single instance)
> ABC1,100,3,4
> ABC1,100,4,3
> ABC1,100,5,6
> ABC1,100,6,20
> ABC1,100,6,20(duplicates may or may not be present, if present then 
> they can be dissolved into a single instance)
> ABC1,500,4,19
> ABC1,500,19,4 (start and end values could be inverted where Start is 
> high and End id low (this is not the norm but it is legitimate) in 
> this case start and end values may be inverted, and in this case 
> dissolved into a single instance)
> ABC2,300,4,4 (start and end values could be the same, in the first 
> instance assume that they may be dissolved if possible, if they cannot 
> the record need to be retained)
>
>
>
> Output data: collapsed/merged expected output CLS1,CLS2,START,END
> ABC1,100,0,4
> ABC1,100,5,20
> ABC1,500,4,19
> ABC2,300,4,4
>
>
> I would be extremely grateful if anybody could help me with this issue.
>
>
> Regards,
>
> Rob
>
>
>
> 
>
> The content of this email (and any attachment) is confidential. It may also 
> be legally privileged or otherwise protected from disclosure.
> This email should not be used by anyone who is not an original intended 
> recipient, nor may it be copied or disclosed to anyone who is not an original 
> intended recipient.
>
> If you have received this email by mistake please notify us by emailing the 
> sender, and then delete the email and any copies from your system.
>
> Liability cannot be accepted for statements made which are clearly the 
> sender's own and

Re: [sqlite] Check constrain execution timing change? (Now a bug)

[Dominique Devienne]

On Sun, Feb 2, 2020 at 12:50 AM Richard Hipp  wrote:

> On 2/1/20, Thomas Kurz  wrote:
> > Does this mean there will be no possibility to prevent inserting a string
> > into an integer column anymore?
> >
> > create table x (x integer check (typeof(x) == 'integer'));
> > insert into x values ('1');
> >
> > --> will pass in future versions???
>
> I think that is what it means.  yes.

Wow... I haven't caught up on this thread, but that's really really bad IMHO,
and would consider that a serious regression. I've been enforcing
"strong-typing",
(or "inflexible-typing" if you prefer Richard) for many schemas, and
the fact we can
no longer do that would be a real shame. I wonder where this is coming
from... --DD
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users