Re: [sqlite] column types and constraints

[Keith Medcalf]

I see no unsigned integer support anywhere ...

https://www.sqlite.org/datatype3.html

And there are no API functions dealing with unsigned integers, only standard 
2-s complement signed integers.

https://www.sqlite.org/c3ref/funclist.html

Declaring something an "unsigned integer" is the same as declaring it an 
"unsinged integer".  The substring "int" declares the affinity and the rest is 
ignored as line noise.

---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.


>-Original Message-
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of Warren Young
>Sent: Saturday, 30 June, 2018 18:09
>To: SQLite mailing list
>Subject: Re: [sqlite] column types and constraints
>
>On Jun 29, 2018, at 10:17 PM, Keith Medcalf 
>wrote:
>>
>> So what you really want is (a) to be able to error-out on SQL
>operations that do not use proper affinity words (ie, only accept
>INTEGER, DOUBLE, TEXT and BLOB), and; (b) to crash or return an error
>instead of converting the data that you put or at least, that you
>ask.
>
>That would work, too.
>
>There’s a schools of thought that says that “unsigned” should just go
>away, that it causes more trouble than it’s worth.
>
>I believe the only reason SQLite supports it is to ease conversion of
>schemas from other DBMSes.
>___
>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] column types and constraints

[Warren Young]

On Jun 29, 2018, at 10:17 PM, Keith Medcalf  wrote:
> 
> So what you really want is (a) to be able to error-out on SQL operations that 
> do not use proper affinity words (ie, only accept INTEGER, DOUBLE, TEXT and 
> BLOB), and; (b) to crash or return an error instead of converting the data 
> that you put or at least, that you ask.

That would work, too.

There’s a schools of thought that says that “unsigned” should just go away, 
that it causes more trouble than it’s worth.

I believe the only reason SQLite supports it is to ease conversion of schemas 
from other DBMSes.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] column types and constraints

[Igor Korot]

Hi, ALL,
I am not sure what are we talking about here and is discussed.
Just like on the paper the hard drive is storing the characters. It is
for us (humans, developers)
to interpret those characters as a TEXT, numeric value (be it INTEGER
or FLOAT/DOUBLE)
or some binary data.

As long as the client retrieves the exact set of characters that was
stored (and it is)
there is nothing to talk about. Because this is exactly what the
database is for - store
some data for the future use.

The strong typing in the DBMSes are there to simplify the life of the
people who creates
the library which is used by the actual developers to create client
application. Nothing more,
nothing less.
Just imagine if MS Access had the affinity paradigm instead of strong
data types. How hard would be
to write the VB for Access for MS devs.

So once again - it is exactly as on the paper - characters that should
be interpreted by people
(developers).

Thank you.

P.S.: I hope I'm right here and if not someone will surely correct me.


On Sat, Jun 30, 2018 at 3:57 PM, Keith Medcalf  wrote:
>
> In your case, yes.
>
> If you do not wish SQLite3 to "convert" to the requested storage type on 
> storage of a value, then do not specify a storage type (or specify a storage 
> type of BLOB).  Then whatever you request-to-store will be stored without 
> conversion.
>
> SQLite version 3.25.0 2018-06-21 23:53:54
> Enter ".help" for usage hints.
> Connected to a transient in-memory database.
> Use ".open FILENAME" to reopen on a persistent database.
> sqlite> create table x(x);
> sqlite> insert into x values ('3');
> sqlite> insert into x values ('3.0');
> sqlite> insert into x values (3);
> sqlite> insert into x values (3.0);
> sqlite> insert into x values (x'123456789084759301939875459381798754');
> sqlite> insert into x values (null);
> sqlite> select x, typeof(x) from x;
> 3|text
> 3.0|text
> 3|integer
> 3.0|real
>  4Vx��u� ��uE��y�T|blob
> |null
> sqlite>
>
> ---
> The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
> lot about anticipated traffic volume.
>
>
>>-Original Message-
>>From: sqlite-users [mailto:sqlite-users-
>>boun...@mailinglists.sqlite.org] On Behalf Of Thomas Kurz
>>Sent: Saturday, 30 June, 2018 14:10
>>To: SQLite mailing list
>>Subject: Re: [sqlite] column types and constraints
>>
>>> when in fact it was the third-party interface wrapper.
>>
>>The examples I provided were all taken from the current sqlite3.exe
>>cli with 3.24.0 library. It is not a third-party issue.
>>
>>___
>>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] column types and constraints

[Simon Slavin]

On 30 Jun 2018, at 8:58pm, Wout Mertens  wrote:

> Hmm, very odd. I'll need to check what happened again. 

Pay a lot of attention to the type you supplied when you created the table.  
The post you're responding to has this line:

> sqlite> create table x(x primary key);

This does not supply a type for the column.  There could be all sorts of weird 
things going on.  Do the same thing but with TEXT

sqlite> create table x(x TEXT primary key);

and you're more likely to be able to predict what happens.

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

Re: [sqlite] column types and constraints

[Keith Medcalf]

In your case, yes.

If you do not wish SQLite3 to "convert" to the requested storage type on 
storage of a value, then do not specify a storage type (or specify a storage 
type of BLOB).  Then whatever you request-to-store will be stored without 
conversion.

SQLite version 3.25.0 2018-06-21 23:53:54
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> create table x(x);
sqlite> insert into x values ('3');
sqlite> insert into x values ('3.0');
sqlite> insert into x values (3);
sqlite> insert into x values (3.0);
sqlite> insert into x values (x'123456789084759301939875459381798754');
sqlite> insert into x values (null);
sqlite> select x, typeof(x) from x;
3|text
3.0|text
3|integer
3.0|real
4Vx��u�
��uE��y�T|blob
|null
sqlite>

---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.


>-Original Message-
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of Thomas Kurz
>Sent: Saturday, 30 June, 2018 14:10
>To: SQLite mailing list
>Subject: Re: [sqlite] column types and constraints
>
>> when in fact it was the third-party interface wrapper.
>
>The examples I provided were all taken from the current sqlite3.exe
>cli with 3.24.0 library. It is not a third-party issue.
>
>___
>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] column types and constraints

[Thomas Kurz]

> when in fact it was the third-party interface wrapper.

The examples I provided were all taken from the current sqlite3.exe cli with 
3.24.0 library. It is not a third-party issue.

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

Re: [sqlite] column types and constraints

[Wout Mertens]

Hmm, very odd. I'll need to check what happened again. I use node-sqlite3,
but it also doesn't seem to convert (
https://github.com/mapbox/node-sqlite3/blob/master/src/statement.cc#L274)


On Sat, Jun 30, 2018, 9:44 PM Keith Medcalf  wrote:

>
> When you declare a column with no affinity (that is with blob or none
> affinity), the data is stored precisely and exactly as presented with no
> conversions performed by SQLite3.  You give it a character string, it
> stores a character string.  You give it an integer, it stores an integer.
> You give it a real it stores it as a real.  You give it a bag-o-bytes, it
> is stored as a bag-o-bytes.  If you give it something that can be converted
> (ie, a text representation of an integer) the data presented in stored (the
> text string).  It is not converted.
>
> sqlite> create table x(x primary key);
> sqlite> insert into x values ('1234567890');
> sqlite> insert into x values ('12345678901234567890');
> sqlite> insert into x values ('1');
> sqlite> insert into x values ('1.0');
> sqlite> insert into x values (1);
> sqlite> insert into x values (2.0);
> sqlite> select x, typeof(x) from x;
> 1234567890|text
> 12345678901234567890|text
> 1|text
> 1.0|text
> 1|integer
> 2.0|real
>
> You were being "helped" by something other than SQLite3 because SQLite3
> does not behave in the manner you described.
>
> See
> https://www.sqlite.org/datatype3.html#determination_of_column_affinity
> in particular rule #3
>
> See also the sentence immediately preceding that section:
>
> "A column with affinity BLOB does not prefer one storage class over
> another and no attempt is made to coerce data from one storage class into
> another."
>
> Nor is there an SQLite3 API which will permit you to retrieve the data by
> "magical types".  You ask for the datatype you want to receive and SQLite3
> will carry out the conversions necessary to meet your request, or; if you
> do not want any conversions, then you first ask what the data storage
> format of the item is, and then ask for the data to be returned in that
> format.
>
> Since you are not interacting directly with the SQLite3 C API, whomever
> wrote whatever it is that you are using included a bunch-o-magic which
> either is not documented, or that you did not read and therefore you
> assumed the issue you saw was SQLite3 when in fact it was the third-party
> interface wrapper.
>
> ---
> The fact that there's a Highway to Hell but only a Stairway to Heaven says
> a lot about anticipated traffic volume.
>
>
> >-Original Message-
> >From: sqlite-users [mailto:sqlite-users-
> >boun...@mailinglists.sqlite.org] On Behalf Of Wout Mertens
> >Sent: Saturday, 30 June, 2018 11:44
> >To: SQLite mailing list
> >Subject: Re: [sqlite] column types and constraints
> >
> >story time:
> >
> >I was storing rows with text id's and never bothered setting a type,
> >it was
> >"id PRIMARY KEY" and I always assumed that it gave me back what I
> >stored in
> >it via the nodejs binding.
> >
> >One day I was storing a string of numbers which happened to fit in a
> >64 bit
> >int, and so sqlite stored them as integers, and when retrieving them,
> >they
> >became floats (since JS doesn't have 64 bit ints (yet)).
> >
> >That was a headscratcher. Now I explicitly set the type, always.
> >
> >
> >On Sat, Jun 30, 2018, 11:19 AM Olivier Mascia  wrote:
> >
> >> > Le 30 juin 2018 à 09:04, Thomas Kurz  a
> >écrit :
> >> >
> >> > CREATE TABLE a (col1 STRING);
> >> > INSERT INTO a (col1) VALUES ("3.0");
> >> > SELECT * from a;
> >> > ---> 3// this should never happen!!
> >>
> >> SQLite type affinity rules clearly do not recognise STRING as TEXT:
> >it
> >> does so only when the type contains the words CHAR, TEXT or CLOB.
> >STRING,
> >> which you use for your example, is even specifically warned about
> >(being of
> >> NUMERIC affinity).
> >>
> >>
> >https://www.sqlite.org/datatype3.html#determination_of_column_affinit
> >y
> >>
> >> --
> >> Best Regards, Meilleures salutations, Met vriendelijke groeten,
> >> Olivier Mascia
> >>
> >>
> >> ___
> >> 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
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] column types and constraints

[Keith Medcalf]

When you declare a column with no affinity (that is with blob or none 
affinity), the data is stored precisely and exactly as presented with no 
conversions performed by SQLite3.  You give it a character string, it stores a 
character string.  You give it an integer, it stores an integer.  You give it a 
real it stores it as a real.  You give it a bag-o-bytes, it is stored as a 
bag-o-bytes.  If you give it something that can be converted (ie, a text 
representation of an integer) the data presented in stored (the text string).  
It is not converted.

sqlite> create table x(x primary key);
sqlite> insert into x values ('1234567890');
sqlite> insert into x values ('12345678901234567890');
sqlite> insert into x values ('1');
sqlite> insert into x values ('1.0');
sqlite> insert into x values (1);
sqlite> insert into x values (2.0);
sqlite> select x, typeof(x) from x;
1234567890|text
12345678901234567890|text
1|text
1.0|text
1|integer
2.0|real

You were being "helped" by something other than SQLite3 because SQLite3 does 
not behave in the manner you described.

See 
https://www.sqlite.org/datatype3.html#determination_of_column_affinity
in particular rule #3

See also the sentence immediately preceding that section:

"A column with affinity BLOB does not prefer one storage class over another and 
no attempt is made to coerce data from one storage class into another."

Nor is there an SQLite3 API which will permit you to retrieve the data by 
"magical types".  You ask for the datatype you want to receive and SQLite3 will 
carry out the conversions necessary to meet your request, or; if you do not 
want any conversions, then you first ask what the data storage format of the 
item is, and then ask for the data to be returned in that format.

Since you are not interacting directly with the SQLite3 C API, whomever wrote 
whatever it is that you are using included a bunch-o-magic which either is not 
documented, or that you did not read and therefore you assumed the issue you 
saw was SQLite3 when in fact it was the third-party interface wrapper.

---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.


>-Original Message-
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of Wout Mertens
>Sent: Saturday, 30 June, 2018 11:44
>To: SQLite mailing list
>Subject: Re: [sqlite] column types and constraints
>
>story time:
>
>I was storing rows with text id's and never bothered setting a type,
>it was
>"id PRIMARY KEY" and I always assumed that it gave me back what I
>stored in
>it via the nodejs binding.
>
>One day I was storing a string of numbers which happened to fit in a
>64 bit
>int, and so sqlite stored them as integers, and when retrieving them,
>they
>became floats (since JS doesn't have 64 bit ints (yet)).
>
>That was a headscratcher. Now I explicitly set the type, always.
>
>
>On Sat, Jun 30, 2018, 11:19 AM Olivier Mascia  wrote:
>
>> > Le 30 juin 2018 à 09:04, Thomas Kurz  a
>écrit :
>> >
>> > CREATE TABLE a (col1 STRING);
>> > INSERT INTO a (col1) VALUES ("3.0");
>> > SELECT * from a;
>> > ---> 3// this should never happen!!
>>
>> SQLite type affinity rules clearly do not recognise STRING as TEXT:
>it
>> does so only when the type contains the words CHAR, TEXT or CLOB.
>STRING,
>> which you use for your example, is even specifically warned about
>(being of
>> NUMERIC affinity).
>>
>>
>https://www.sqlite.org/datatype3.html#determination_of_column_affinit
>y
>>
>> --
>> Best Regards, Meilleures salutations, Met vriendelijke groeten,
>> Olivier Mascia
>>
>>
>> ___
>> 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] column types and constraints

[Thomas Kurz]

> H2, HSQL

Both are Java crap. SQLite is unfortunately the only embedded DBMS that gets 
along with a single file for both the library and the database files. (At least 
it's the only I know after scaning various systems.)

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

[sqlite] FTS4 content table

[Dudu Markovitz]

Hi

according to the documentation -
6.2.2. External Content FTS4 Tables

An "external content" FTS4 table is similar to a contentless table, except
that if evaluation of a query requires the value of a column other than
docid, FTS4 attempts to retrieve that value from a table (or view, or
virtual table) nominated by the user (hereafter referred to as the "content
table").

However I see no example for a query on the fts table that implicitly
retrieves values from the content table, nor could I find any in other
place.
All my attempts to write such a query ended up with "Error: no such
column".
Am I missing something or is it a problem in the documentation?

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

Re: [sqlite] column types and constraints

[Wout Mertens]

story time:

I was storing rows with text id's and never bothered setting a type, it was
"id PRIMARY KEY" and I always assumed that it gave me back what I stored in
it via the nodejs binding.

One day I was storing a string of numbers which happened to fit in a 64 bit
int, and so sqlite stored them as integers, and when retrieving them, they
became floats (since JS doesn't have 64 bit ints (yet)).

That was a headscratcher. Now I explicitly set the type, always.


On Sat, Jun 30, 2018, 11:19 AM Olivier Mascia  wrote:

> > Le 30 juin 2018 à 09:04, Thomas Kurz  a écrit :
> >
> > CREATE TABLE a (col1 STRING);
> > INSERT INTO a (col1) VALUES ("3.0");
> > SELECT * from a;
> > ---> 3// this should never happen!!
>
> SQLite type affinity rules clearly do not recognise STRING as TEXT: it
> does so only when the type contains the words CHAR, TEXT or CLOB.  STRING,
> which you use for your example, is even specifically warned about (being of
> NUMERIC affinity).
>
> https://www.sqlite.org/datatype3.html#determination_of_column_affinity
>
> --
> Best Regards, Meilleures salutations, Met vriendelijke groeten,
> Olivier Mascia
>
>
> ___
> 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] column types and constraints

[danap]

> Bob wrote:
> Affinity is only a hint and not an assurance of anything.
> I solve the problem by bloating the schema with checks like this:
>
>   foo integer default 1234
>  check (typeof(foo) == 'integer'),
>
> This enforces that someone can't put "Hello world" where an integer
> belongs.

I don't get to control others schema in providing a generic gui
for users.

> Simon wrote:
> The problem, as far as SQLite is concerned, is that the column
> should have been declared "TEXT' not STRING.  Then it works correctly:

> SQLite version 3.22.0 2017-12-05 15:00:17 [...]
> sqlite> CREATE TABLE a (col1 STRING);
> sqlite> INSERT INTO a (col1) VALUES ("3.0");
> sqlite> SELECT * from a;
> 3
> sqlite> DROP TABLE a;
> sqlite> CREATE TABLE a (col1 TEXT);
> sqlite> INSERT INTO a (col1) VALUES ("3.0");
> sqlite> SELECT * from a;
> 3.0

> But course the dev team cannot correct the understanding of 'STRING'
> for backward compatibility reasons.  And using affinities rather than
> types means that feeding a string into a numeric column does not
> generate an error.  So the programmer never figured out that using
> 'STRING' as a type didn't do the right thing.

> It's a problem with multiple causes.  And it cannot be fixed in SQLite3.

Nor should it! Ryan provided ample argument for a light DB engine that
SQLite seems to satisfy for millions with FLEXIBILITY! If you wish a
more constrained light RDBMS then H2, HSQL, and others are available.

I'm just happy at least I get a column affinity for type checking to
give some sanity. Thank you.

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

Re: [sqlite] GROUP BY [Re: unique values from a subset of data based on two fields]

[R Smith]

On 2018/06/30 3:12 PM, Luuk wrote:

Ok ,my highway to hell start here (regargind the use of SQL)

In SQLite3 you are allowed to do this:
SELECT a,b,c
FROM t1
GROUP BY a

The values of 'b' and 'c' will be taken from a 'random' row...

But if we rewrite this in SQL, i am getting something like this:
SELECT
    a,
    (SELECT MIN(b) FROM T1 WHERE a=t.a) AS B,
    (SELECT MIN(c) FROM T1 WHERE a=t.a) AS C
FROM t1 t
GROUP BY a

QUESTION: How does one get the proper relationship between 'B' and 'C'?,


This question comes up from time to time, and is sometimes misunderstood 
by SQL users everywhere (or from every Engine I should say).


There is no easy solution and it isn't really an aggregate Set-Algebra 
problem, so mostly not handled specifically by Engines for aggregate 
queries.


To just state the problem more fully:

Consider this Table "Fruit" in a Grocer's shop:
SELECT * FROM Fruit;
  -- |  | |  DaysOn-
  -- Name    |   BasketNo   | Qty |   Shelf
  -- --- |  | --- | -
  -- Apples  |   1  |  40 | 12
  -- Apples  |   2  |  40 | 14
  -- Apples  |   3  |  23 | 16
  -- Oranges |   4  |  40 | 12
  -- Oranges |   5  |  40 | 14
  -- Oranges |   6  |  11 | 16
  -- Apples  |   7  |  7  | 22

An unopened basket contains 40 fruit. We try to use the oldest first, etc.
Now suppose we want to know what is the oldest unopened basket for every 
kind of fruit. The simplest query in *SQLite* would be:


SELECT Name, MAX(DaysOnShelf) AS MaxAge, BasketNo
  FROM Fruit
 WHERE Qty = 40
 GROUP BY Name
;

  -- Name    | MaxAge |   BasketNo
  -- --- | -- | 
  -- Apples  |   14   |   2
  -- Oranges |   14   |   5

Note that this ONLY works due to a peculiarity in SQLite, and it is 
forewarned in the SQLite documents that the non-aggregate field could 
return ANY random value that qualifies to be in a selected row (i.e. any 
row that is chosen for aggregation by the WHERE clause filter).


To say exactly what that means: In the query, for the Apples aggregate, 
two rows "pass" the filter where Qty = 40, namely those with baskets 1 
and 2. MAX(DaysOnShelf) for those two rows correctly returns 14, but 
there is no guarantee that the returned BasketNo will be from THAT 
specific row, it could have returned (still mathematically correct) 
Basket 1 in stead of Basket 2.  And, in a next release, it might even do 
so. You cannot assume what it would be.


In fact, if we change the query to enclose BasketNo too in an aggregate 
function (MIN() in this case):

SELECT Name, MAX(DaysOnShelf) AS MaxAge, MIN(BasketNo) AS BasketNo
  FROM Fruit
 WHERE Qty = 40
 GROUP BY Name
;

  -- Name    | MaxAge |   BasketNo
  -- --- | -- | 
  -- Apples  |   14   |   1
  -- Oranges |   14   |   4

Now the 14 and 1 next to Apples are definitely NOT from the same row - 
which is the entire point of aggregate functions, we want to know the 
truth over the whole set for each function, it is not intended to pick 
out values.


Thing is, most developers are precisely interested in the specific row 
containing the qualifying aggregate.


There are two ways to solve this (using standard SQL in any SQL engine - 
there might be more ways in specific Engines, such as SQLite).


1 - First find the target aggregate, then look that up against the 
original (non aggregate) set of records.

2 - Construct a custom taxonomy and deconstruct after.

1 is by far the most used and the easiest to write. It's downside is 
that it requires a double look-up loop, which in most Engines is fairly 
efficient anyway.

An example of this:

SELECT X.Name, X.MaxAge, F.BasketNo
  FROM (
    SELECT Name, MAX(DaysOnShelf) AS MaxAge, MAX(Qty) AS Qty
      FROM Fruit
     WHERE Qty = 40
     GROUP BY Name
  ) AS X
  JOIN Fruit AS F ON F.Name = X.Name AND F.DaysOnShelf = X.MaxAge AND 
F.Qty = X.Qty

;
  -- Name    | MaxAge |   BasketNo
  -- --- | -- | 
  -- Apples  |   14   |   2
  -- Oranges |   14   |   5


-- The above example shows ALL rows that match (as is the nature of a 
JOIN).  To only show a single row, the following can work:


SELECT X.Name, X.MaxAge, (
   SELECT F.BasketNo FROM Fruit AS F WHERE F.Name = X.Name AND 
F.DaysOnShelf = X.MaxAge AND F.Qty = X.Qty LIMIT 1

   ) AS BasketNo
  FROM (
    SELECT Name, MAX(DaysOnShelf) AS MaxAge, MAX(Qty) AS Qty
      FROM Fruit
     WHERE Qty = 40
     GROUP BY Name
  ) AS X
;
  -- Name    | MaxAge |   BasketNo
  -- --- | -- | 
  -- Apples  |   14   |   2
  -- Oranges |   14   |   5


-- And lastly, an example of achieving the first query with a CTE:

WITH X(Name, MaxAge, Qty) AS (
    SELECT Name, MAX(DaysOnShelf), MAX(Qty)
      FROM Fruit
     WHERE Qty = 40
     GROUP BY Name
)
SELECT X.Name, X.MaxAge, F.BasketNo
  FROM X
  JOIN Fruit AS F ON F.Name = X.Name AND F.DaysOnShelf = X.MaxAge AND 

Re: [sqlite] column types and constraints

[Thomas Kurz]

> And yes, you might think (like me) that this is silly and they should 
> just fix it, warning people about the new way for a good few versions, 
> and then just roll it out. But, that would mean there will exist schemas 

Hmmm... but what kind of compatibility issues would you see if SQLite stored 
values exactly as provided and did the conversion upon reading? I.e. "INSERT 
... ('3.0')" would actually store a string. When reading this string with a 
numeric parameter, the conversion can still be done. This way, I would of 
course still have the same problem, but I had a chance to correct my mistake in 
existing databases reading with explicit CASTs.

But there are other issues where SQLite corrected mistakes that were done 
before. Think of the "pragma foreign_key_check" for example. One could fix data 
type issues in a similar way without affecting compatibility. And those who 
rely on a special interpretation of non-standard SQL (like putting strings and 
expecting them to be treated as numbers) will always have the option to stay on 
"their" SQLite version.

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

Re: [sqlite] GROUP BY [Re: unique values from a subset of data based on two fields]

[Luuk]

On 30-6-2018 15:45, Luuk wrote:
>
> In SQLite3 you are allowed to do this:
> SELECT a,b,c
> FROM t1
> GROUP BY a
>
> The values of 'b' and 'c' will be taken from a 'random' row...
>
> But if we rewrite this in SQL, i am getting something like this:
> SELECT
>a,
>(SELECT MIN(b) FROM T1 WHERE a=t.a) AS B,
>(SELECT MIN(c) FROM T1 WHERE a=t.a) AS C
> FROM t1 t
> GROUP BY a
>
> QUESTION: How does one get the proper relationship between 'B' and 'C'?,
> i mean how can one be use that both values are from the same row?
> This is not a problem to SQLite, because in SQLite the values of b and c
> seems to be originating from the same row, but what about *SQL* (if that
> exists...?)
>

I think i'll do this:

select x.a, t1.b, t1.c
from (select t1.a, min(t1.rowid)
    from t1
    group by t1.a) x
inner join t1 on x.rowid=t1.rowid;

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

Re: [sqlite] GROUP BY [Re: unique values from a subset of data based on two fields]

[Gerry Snyder]

If you want the row with the minimum B, and the row with a minimum C, then
the union of two queries would seem to be appropriate.

Gerry Snyder

On Sat, Jun 30, 2018, 6:45 AM Luuk  wrote:

>
>
> On 30-6-2018 15:39, Abroży Nieprzełoży wrote:
> >> SELECT
> >>a,
> >>(SELECT MIN(b) FROM T1 WHERE a=t.a) AS B,
> >>(SELECT MIN(c) FROM T1 WHERE a=t.a) AS C
> >> FROM t1 t
> >> GROUP BY a
> > Why not
> > select a, min(b) as b, min(c) as c from t1 group by a;
> > ?
>
> It still does not quarantee that the valuse show for b and c are comming
> from the same row...
>
>
> >
> > 2018-06-30 15:12 GMT+02:00, Luuk :
> >> On 30-6-2018 14:55, Keith Medcalf wrote:
> >>> Note that this is SQLite3 specific (and specific to Sybase of the era
> >>> where Microsoft SQL Server was actually just a rebranded Sybase, and
> >>> Microsoft re-writes of SQL Server up to about 2000).  Technically you
> >>> cannot do a query of the form:
> >>>
> >>> SELECT c1, c2
> >>>   FROM t1
> >>> GROUP BY c2;
> >>>
> >>> because each column in the select list must be either an aggregate or
> >>> listed in the GROUP BY clause.  SQLite3 allows c1 to be a bare column
> >>> however and the value returned is taken from "some random row" of the
> >>> group.  If there are multiple such columns, they all come from the same
> >>> row in the group.  Although documented as a "random" row of the group,
> it
> >>> is the first (or last) row visited in the group while solving the query
> >>> (and this is of course subject to change but within the same version of
> >>> SQLite3 will deterministically be the row either first or last in the
> >>> visitation order -- the actual row may of course change depending on
> use
> >>> of indexes, etc).  You can re-write this part so it will work in other
> SQL
> >>> dialects that strictly enforce the requirement for c1 to be either an
> >>> aggregate or listed in the group by clause.
> >>>
> >>> ---
> >>> The fact that there's a Highway to Hell but only a Stairway to Heaven
> says
> >>> a lot about anticipated traffic volume.
> >>>
> >> Ok ,my highway to hell start here (regargind the use of SQL)
> >>
> >> In SQLite3 you are allowed to do this:
> >> SELECT a,b,c
> >> FROM t1
> >> GROUP BY a
> >>
> >> The values of 'b' and 'c' will be taken from a 'random' row...
> >>
> >> But if we rewrite this in SQL, i am getting something like this:
> >> SELECT
> >>a,
> >>(SELECT MIN(b) FROM T1 WHERE a=t.a) AS B,
> >>(SELECT MIN(c) FROM T1 WHERE a=t.a) AS C
> >> FROM t1 t
> >> GROUP BY a
> >>
> >> QUESTION: How does one get the proper relationship between 'B' and 'C'?,
> >> i mean how can one be use that both values are from the same row?
> >> This is not a problem to SQLite, because in SQLite the values of b and c
> >> seems to be originating from the same row, but what about *SQL* (if that
> >> exists...?)
> >>
> >> --
> >> some test results:
> >> sqlite> insert into t1 values (1,1,2);
> >> sqlite> insert into t1 values (1,2,1);
> >> sqlite> insert into t1 values (2,2,1);
> >> sqlite> insert into t1 values (2,1,2);
> >> sqlite> select a,b,c from t1 group by a;
> >> 1|2|1
> >> 2|1|2
> >> sqlite> SELECT
> >>...>a,
> >>...>(SELECT MIN(b) FROM T1 WHERE a=t.a) AS B,
> >>...>(SELECT MIN(c) FROM T1 WHERE a=t.a) AS C
> >>...> FROM t1 t
> >>...> GROUP BY a;
> >> 1|1|1
> >> 2|1|1
> >> sqlite>
> >>
> >> ___
> >> 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
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] GROUP BY [Re: unique values from a subset of data based on two fields]

[Abroży Nieprzełoży]

>>> SELECT
>>>a,
>>>(SELECT MIN(b) FROM T1 WHERE a=t.a) AS B,
>>>(SELECT MIN(c) FROM T1 WHERE a=t.a) AS C
>>> FROM t1 t
>>> GROUP BY a
>> Why not
>> select a, min(b) as b, min(c) as c from t1 group by a;
>> ?
>
> It still does not quarantee that the valuse show for b and c are comming
> from the same row...

select a, b, min(c) as c from (select a, min(b) as b from t1 group by
a) join t1 using(a, b) group by a, b;

?

2018-06-30 15:45 GMT+02:00, Luuk :
>
>
> On 30-6-2018 15:39, Abroży Nieprzełoży wrote:
>>> SELECT
>>>a,
>>>(SELECT MIN(b) FROM T1 WHERE a=t.a) AS B,
>>>(SELECT MIN(c) FROM T1 WHERE a=t.a) AS C
>>> FROM t1 t
>>> GROUP BY a
>> Why not
>> select a, min(b) as b, min(c) as c from t1 group by a;
>> ?
>
> It still does not quarantee that the valuse show for b and c are comming
> from the same row...
>
>
>>
>> 2018-06-30 15:12 GMT+02:00, Luuk :
>>> On 30-6-2018 14:55, Keith Medcalf wrote:
 Note that this is SQLite3 specific (and specific to Sybase of the era
 where Microsoft SQL Server was actually just a rebranded Sybase, and
 Microsoft re-writes of SQL Server up to about 2000).  Technically you
 cannot do a query of the form:

 SELECT c1, c2
   FROM t1
 GROUP BY c2;

 because each column in the select list must be either an aggregate or
 listed in the GROUP BY clause.  SQLite3 allows c1 to be a bare column
 however and the value returned is taken from "some random row" of the
 group.  If there are multiple such columns, they all come from the same
 row in the group.  Although documented as a "random" row of the group,
 it
 is the first (or last) row visited in the group while solving the query
 (and this is of course subject to change but within the same version of
 SQLite3 will deterministically be the row either first or last in the
 visitation order -- the actual row may of course change depending on use
 of indexes, etc).  You can re-write this part so it will work in other
 SQL
 dialects that strictly enforce the requirement for c1 to be either an
 aggregate or listed in the group by clause.

 ---
 The fact that there's a Highway to Hell but only a Stairway to Heaven
 says
 a lot about anticipated traffic volume.

>>> Ok ,my highway to hell start here (regargind the use of SQL)
>>>
>>> In SQLite3 you are allowed to do this:
>>> SELECT a,b,c
>>> FROM t1
>>> GROUP BY a
>>>
>>> The values of 'b' and 'c' will be taken from a 'random' row...
>>>
>>> But if we rewrite this in SQL, i am getting something like this:
>>> SELECT
>>>a,
>>>(SELECT MIN(b) FROM T1 WHERE a=t.a) AS B,
>>>(SELECT MIN(c) FROM T1 WHERE a=t.a) AS C
>>> FROM t1 t
>>> GROUP BY a
>>>
>>> QUESTION: How does one get the proper relationship between 'B' and 'C'?,
>>> i mean how can one be use that both values are from the same row?
>>> This is not a problem to SQLite, because in SQLite the values of b and c
>>> seems to be originating from the same row, but what about *SQL* (if that
>>> exists...?)
>>>
>>> --
>>> some test results:
>>> sqlite> insert into t1 values (1,1,2);
>>> sqlite> insert into t1 values (1,2,1);
>>> sqlite> insert into t1 values (2,2,1);
>>> sqlite> insert into t1 values (2,1,2);
>>> sqlite> select a,b,c from t1 group by a;
>>> 1|2|1
>>> 2|1|2
>>> sqlite> SELECT
>>>...>a,
>>>...>(SELECT MIN(b) FROM T1 WHERE a=t.a) AS B,
>>>...>(SELECT MIN(c) FROM T1 WHERE a=t.a) AS C
>>>...> FROM t1 t
>>>...> GROUP BY a;
>>> 1|1|1
>>> 2|1|1
>>> sqlite>
>>>
>>> ___
>>> 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
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] GROUP BY [Re: unique values from a subset of data based on two fields]

[Luuk]

On 30-6-2018 15:39, Abroży Nieprzełoży wrote:
>> SELECT
>>a,
>>(SELECT MIN(b) FROM T1 WHERE a=t.a) AS B,
>>(SELECT MIN(c) FROM T1 WHERE a=t.a) AS C
>> FROM t1 t
>> GROUP BY a
> Why not
> select a, min(b) as b, min(c) as c from t1 group by a;
> ?

It still does not quarantee that the valuse show for b and c are comming
from the same row...


>
> 2018-06-30 15:12 GMT+02:00, Luuk :
>> On 30-6-2018 14:55, Keith Medcalf wrote:
>>> Note that this is SQLite3 specific (and specific to Sybase of the era
>>> where Microsoft SQL Server was actually just a rebranded Sybase, and
>>> Microsoft re-writes of SQL Server up to about 2000).  Technically you
>>> cannot do a query of the form:
>>>
>>> SELECT c1, c2
>>>   FROM t1
>>> GROUP BY c2;
>>>
>>> because each column in the select list must be either an aggregate or
>>> listed in the GROUP BY clause.  SQLite3 allows c1 to be a bare column
>>> however and the value returned is taken from "some random row" of the
>>> group.  If there are multiple such columns, they all come from the same
>>> row in the group.  Although documented as a "random" row of the group, it
>>> is the first (or last) row visited in the group while solving the query
>>> (and this is of course subject to change but within the same version of
>>> SQLite3 will deterministically be the row either first or last in the
>>> visitation order -- the actual row may of course change depending on use
>>> of indexes, etc).  You can re-write this part so it will work in other SQL
>>> dialects that strictly enforce the requirement for c1 to be either an
>>> aggregate or listed in the group by clause.
>>>
>>> ---
>>> The fact that there's a Highway to Hell but only a Stairway to Heaven says
>>> a lot about anticipated traffic volume.
>>>
>> Ok ,my highway to hell start here (regargind the use of SQL)
>>
>> In SQLite3 you are allowed to do this:
>> SELECT a,b,c
>> FROM t1
>> GROUP BY a
>>
>> The values of 'b' and 'c' will be taken from a 'random' row...
>>
>> But if we rewrite this in SQL, i am getting something like this:
>> SELECT
>>a,
>>(SELECT MIN(b) FROM T1 WHERE a=t.a) AS B,
>>(SELECT MIN(c) FROM T1 WHERE a=t.a) AS C
>> FROM t1 t
>> GROUP BY a
>>
>> QUESTION: How does one get the proper relationship between 'B' and 'C'?,
>> i mean how can one be use that both values are from the same row?
>> This is not a problem to SQLite, because in SQLite the values of b and c
>> seems to be originating from the same row, but what about *SQL* (if that
>> exists...?)
>>
>> --
>> some test results:
>> sqlite> insert into t1 values (1,1,2);
>> sqlite> insert into t1 values (1,2,1);
>> sqlite> insert into t1 values (2,2,1);
>> sqlite> insert into t1 values (2,1,2);
>> sqlite> select a,b,c from t1 group by a;
>> 1|2|1
>> 2|1|2
>> sqlite> SELECT
>>...>a,
>>...>(SELECT MIN(b) FROM T1 WHERE a=t.a) AS B,
>>...>(SELECT MIN(c) FROM T1 WHERE a=t.a) AS C
>>...> FROM t1 t
>>...> GROUP BY a;
>> 1|1|1
>> 2|1|1
>> sqlite>
>>
>> ___
>> 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] GROUP BY [Re: unique values from a subset of data based on two fields]

[Abroży Nieprzełoży]

> SELECT
>a,
>(SELECT MIN(b) FROM T1 WHERE a=t.a) AS B,
>(SELECT MIN(c) FROM T1 WHERE a=t.a) AS C
> FROM t1 t
> GROUP BY a

Why not
select a, min(b) as b, min(c) as c from t1 group by a;
?


2018-06-30 15:12 GMT+02:00, Luuk :
>
> On 30-6-2018 14:55, Keith Medcalf wrote:
>> Note that this is SQLite3 specific (and specific to Sybase of the era
>> where Microsoft SQL Server was actually just a rebranded Sybase, and
>> Microsoft re-writes of SQL Server up to about 2000).  Technically you
>> cannot do a query of the form:
>>
>> SELECT c1, c2
>>   FROM t1
>> GROUP BY c2;
>>
>> because each column in the select list must be either an aggregate or
>> listed in the GROUP BY clause.  SQLite3 allows c1 to be a bare column
>> however and the value returned is taken from "some random row" of the
>> group.  If there are multiple such columns, they all come from the same
>> row in the group.  Although documented as a "random" row of the group, it
>> is the first (or last) row visited in the group while solving the query
>> (and this is of course subject to change but within the same version of
>> SQLite3 will deterministically be the row either first or last in the
>> visitation order -- the actual row may of course change depending on use
>> of indexes, etc).  You can re-write this part so it will work in other SQL
>> dialects that strictly enforce the requirement for c1 to be either an
>> aggregate or listed in the group by clause.
>>
>> ---
>> The fact that there's a Highway to Hell but only a Stairway to Heaven says
>> a lot about anticipated traffic volume.
>>
> Ok ,my highway to hell start here (regargind the use of SQL)
>
> In SQLite3 you are allowed to do this:
> SELECT a,b,c
> FROM t1
> GROUP BY a
>
> The values of 'b' and 'c' will be taken from a 'random' row...
>
> But if we rewrite this in SQL, i am getting something like this:
> SELECT
>a,
>(SELECT MIN(b) FROM T1 WHERE a=t.a) AS B,
>(SELECT MIN(c) FROM T1 WHERE a=t.a) AS C
> FROM t1 t
> GROUP BY a
>
> QUESTION: How does one get the proper relationship between 'B' and 'C'?,
> i mean how can one be use that both values are from the same row?
> This is not a problem to SQLite, because in SQLite the values of b and c
> seems to be originating from the same row, but what about *SQL* (if that
> exists...?)
>
> --
> some test results:
> sqlite> insert into t1 values (1,1,2);
> sqlite> insert into t1 values (1,2,1);
> sqlite> insert into t1 values (2,2,1);
> sqlite> insert into t1 values (2,1,2);
> sqlite> select a,b,c from t1 group by a;
> 1|2|1
> 2|1|2
> sqlite> SELECT
>...>a,
>...>(SELECT MIN(b) FROM T1 WHERE a=t.a) AS B,
>...>(SELECT MIN(c) FROM T1 WHERE a=t.a) AS C
>...> FROM t1 t
>...> GROUP BY a;
> 1|1|1
> 2|1|1
> sqlite>
>
> ___
> 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] GROUP BY [Re: unique values from a subset of data based on two fields]

[Luuk]

On 30-6-2018 14:55, Keith Medcalf wrote:
> Note that this is SQLite3 specific (and specific to Sybase of the era where 
> Microsoft SQL Server was actually just a rebranded Sybase, and Microsoft 
> re-writes of SQL Server up to about 2000).  Technically you cannot do a query 
> of the form:
>
> SELECT c1, c2
>   FROM t1
> GROUP BY c2;
>
> because each column in the select list must be either an aggregate or listed 
> in the GROUP BY clause.  SQLite3 allows c1 to be a bare column however and 
> the value returned is taken from "some random row" of the group.  If there 
> are multiple such columns, they all come from the same row in the group.  
> Although documented as a "random" row of the group, it is the first (or last) 
> row visited in the group while solving the query (and this is of course 
> subject to change but within the same version of SQLite3 will 
> deterministically be the row either first or last in the visitation order -- 
> the actual row may of course change depending on use of indexes, etc).  You 
> can re-write this part so it will work in other SQL dialects that strictly 
> enforce the requirement for c1 to be either an aggregate or listed in the 
> group by clause.
>
> ---
> The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
> lot about anticipated traffic volume.
>
Ok ,my highway to hell start here (regargind the use of SQL)

In SQLite3 you are allowed to do this:
SELECT a,b,c
FROM t1
GROUP BY a

The values of 'b' and 'c' will be taken from a 'random' row...

But if we rewrite this in SQL, i am getting something like this:
SELECT
   a,
   (SELECT MIN(b) FROM T1 WHERE a=t.a) AS B,
   (SELECT MIN(c) FROM T1 WHERE a=t.a) AS C
FROM t1 t
GROUP BY a

QUESTION: How does one get the proper relationship between 'B' and 'C'?,
i mean how can one be use that both values are from the same row?
This is not a problem to SQLite, because in SQLite the values of b and c
seems to be originating from the same row, but what about *SQL* (if that
exists...?)

-- 
some test results:
sqlite> insert into t1 values (1,1,2);
sqlite> insert into t1 values (1,2,1);
sqlite> insert into t1 values (2,2,1);
sqlite> insert into t1 values (2,1,2);
sqlite> select a,b,c from t1 group by a;
1|2|1
2|1|2
sqlite> SELECT
   ...>    a,
   ...>    (SELECT MIN(b) FROM T1 WHERE a=t.a) AS B,
   ...>    (SELECT MIN(c) FROM T1 WHERE a=t.a) AS C
   ...> FROM t1 t
   ...> GROUP BY a;
1|1|1
2|1|1
sqlite>

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

Re: [sqlite] Determine collation associated with sort

[x]

>I think it's explaned in the docs here:
>https://sqlite.org/datatype3.html#collation

Thanks Luuk. That’s what I was looking for.


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

Re: [sqlite] Determine collation associated with sort

[Keith Medcalf]

SQLite will not select the collation based on the index -- it is exactly the 
opposite -- the collation requested is used to find an appropriate index.  

So if you do an order by that needs BINARY collation, and the only index 
available is a NOCASE collation index, that index cannot be used (for the 
purpose of ordering) and the rows must be sorted in a separate step.  Ascending 
and Descending is different.  You can use and index of a different order if the 
collation matches, you just have to do it backwards (ie, from end to start).

---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.


>-Original Message-
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of x
>Sent: Saturday, 30 June, 2018 06:06
>To: sqlite-users@mailinglists.sqlite.org
>Subject: [sqlite] Determine collation associated with sort
>
>Suppose I have a select such as
>
>‘select * from TblsAndJoins where Condns order by
>OrdCol1,OrdCol2,...,OrdColn’
>
>I want to know the collation associated with the sort. I know a
>COLLATE condition could be attached to the order by BUT suppose
>there’s none. How would I determine the collation associated with the
>sort?
>
>I’m familiar with https://sqlite.org/c3ref/table_column_metadata.html
>
>int sqlite3_table_column_metadata(
>  sqlite3 *db,/* Connection handle */
>  const char *zDbName,/* Database name or NULLtion sequence
>*/
>  const char *zTableName, /* Table name */
>  const char *zColumnName,/* Column name */
>  char const **pzDataType,/* OUTPUT: Declared data type */
> char const **pzCollSeq, /* OUTPUT: Collation sequence name */
>  int *pNotNull,  /* OUTPUT: True if NOT NULL constraint
>exists */
>  int *pPrimaryKey,   /* OUTPUT: True if column part of PK */
>  int *pAutoinc   /* OUTPUT: True if column is auto-
>increment */
>);
>
>which would tell me if a particular column of the sort has a
>collation sequence but what if it’s the index itself that has a
>collation attached? How would I cover all possible angles?
>
>
>
>
>
>
>
>___
>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] unique values from a subset of data based on two fields

[Keith Medcalf]

Note that this is SQLite3 specific (and specific to Sybase of the era where 
Microsoft SQL Server was actually just a rebranded Sybase, and Microsoft 
re-writes of SQL Server up to about 2000).  Technically you cannot do a query 
of the form:

SELECT c1, c2
  FROM t1
GROUP BY c2;

because each column in the select list must be either an aggregate or listed in 
the GROUP BY clause.  SQLite3 allows c1 to be a bare column however and the 
value returned is taken from "some random row" of the group.  If there are 
multiple such columns, they all come from the same row in the group.  Although 
documented as a "random" row of the group, it is the first (or last) row 
visited in the group while solving the query (and this is of course subject to 
change but within the same version of SQLite3 will deterministically be the row 
either first or last in the visitation order -- the actual row may of course 
change depending on use of indexes, etc).  You can re-write this part so it 
will work in other SQL dialects that strictly enforce the requirement for c1 to 
be either an aggregate or listed in the group by clause.

---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.

>-Original Message-
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of Paul Sanderson
>Sent: Saturday, 30 June, 2018 04:18
>To: SQLite mailing list
>Subject: Re: [sqlite] unique values from a subset of data based on
>two fields
>
>Easier and pretty obvious :) Thanks Keith
>
>
>
>Paul
>www.sandersonforensics.com
>SQLite Forensics Book 
>
>On 29 June 2018 at 23:20, Keith Medcalf  wrote:
>
>> >I want a query that returns all of the records with status = 1 and
>> >unique records, based on name, where the status =0 and the name is
>> >not in the list status=1
>>
>> Translation into SQL using English to SQL Translator, using the
>most
>> direct translation on the "problem statement" above directly into
>SQL:
>>
>> create table names (id int, status int, name text);
>> insert into names values (1, 1, 'paul');
>> insert into names values (2, 1, 'helen');
>> insert into names values (3, 0, 'steve');
>> insert into names values (4, 0, 'steve');
>> insert into names values (5, 0, 'pete');
>> insert into names values (6, 0, 'paul');
>>
>> -- I want a query that returns all of the records with status = 1
>>
>> SELECT id,
>>status,
>>name
>>   FROM names
>>  WHERE status == 1
>>
>> -- and
>>
>> UNION
>>
>> -- unique records, based on name, where the status = 0 and the name
>is not
>> in the list [of names where] status=1
>>
>> SELECT id,
>>status,
>>name
>>   FROM names
>>  WHERE status == 0
>>AND name NOT IN (SELECT name
>>   FROM names
>>  WHERE status == 1)
>> GROUP BY name;
>>
>> Returns the rows:
>>
>> 1|1|paul
>> 2|1|helen
>> 3|0|steve
>> 5|0|pete
>>
>> If the table is bigger than trivial (ie, contains more than the
>number of
>> rows you can count with your fingers) then you will need the
>appropriate
>> indexes to achieve performant results.
>>
>> ---
>> The fact that there's a Highway to Hell but only a Stairway to
>Heaven says
>> a lot about anticipated traffic volume.
>>
>>
>> >-Original Message-
>> >From: sqlite-users [mailto:sqlite-users-
>> >boun...@mailinglists.sqlite.org] On Behalf Of Paul Sanderson
>> >Sent: Friday, 29 June, 2018 09:50
>> >To: General Discussion of SQLite Database
>> >Subject: [sqlite] unique values from a subset of data based on two
>> >fields
>> >
>> >I have a table
>> >
>> >Create table names (id int, status int, name text)
>> >
>> >
>> >
>> >1, 1, 'paul'
>> >
>> >2, 1,'helen'
>> >
>> >3, 0, 'steve'
>> >
>> >4, 0, 'steve'
>> >
>> >5, 0, 'pete'
>> >
>> >6, 0, 'paul'
>> >
>> >
>> >
>> >I want a query that returns all of the records with status = 1 and
>> >unique
>> >records, based on name, where the status =0 and the name is not in
>> >the list
>> >status=1
>> >
>> >
>> >
>> >So from the above I would want to see
>> >
>> >
>> >
>> >1, 1, paul
>> >
>> >2, 1, helen
>> >
>> >3, 0, steve (or 4, 0, steve)
>> >
>> >5, 0, pete
>> >
>> >
>> >
>> >I could do something like
>> >
>> >
>> >
>> >Select * from names where status = 1 or name not in (select name
>from
>> >names
>> >where status = 1)
>> >
>> >
>> >
>> >But this gets both rows for steve, e.g.
>> >
>> >
>> >
>> >1, 1, paul
>> >
>> >2, 1, helen
>> >
>> >3, 0, steve
>> >
>> >4, 0, steve
>> >
>> >5, 0, pete
>> >
>> >while I am not bothered about which of the two steves I get back,
>I
>> >must
>> >have all occurences of names with status = 1
>> >
>> >I am probably missing somethng obvious
>> >
>> >Paul
>> >www.sandersonforensics.com
>> >SQLite Forensics Book
>
>> >___
>> >sqlite-users mailing list
>> >sqlite-users@mailinglists.sqlite.org
>> >http://mailinglist

Re: [sqlite] Determine collation associated with sort

[Luuk]

On 30-6-2018 14:05, x wrote:
> Suppose I have a select such as
>
> ‘select * from TblsAndJoins where Condns order by OrdCol1,OrdCol2,...,OrdColn’
>
> I want to know the collation associated with the sort. I know a COLLATE 
> condition could be attached to the order by BUT suppose there’s none. How 
> would I determine the collation associated with the sort?
>
> I’m familiar with https://sqlite.org/c3ref/table_column_metadata.html
>
> int sqlite3_table_column_metadata(
>   sqlite3 *db,/* Connection handle */
>   const char *zDbName,/* Database name or NULLtion sequence */
>   const char *zTableName, /* Table name */
>   const char *zColumnName,/* Column name */
>   char const **pzDataType,/* OUTPUT: Declared data type */
>  char const **pzCollSeq, /* OUTPUT: Collation sequence name */
>   int *pNotNull,  /* OUTPUT: True if NOT NULL constraint exists */
>   int *pPrimaryKey,   /* OUTPUT: True if column part of PK */
>   int *pAutoinc   /* OUTPUT: True if column is auto-increment */
> );
>
> which would tell me if a particular column of the sort has a collation 
> sequence but what if it’s the index itself that has a collation attached? How 
> would I cover all possible angles?
>
>

I think it's explaned in the docs here:
https://sqlite.org/datatype3.html#collation


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

Re: [sqlite] column types and constraints

[Olivier Mascia]

> Le 30 juin 2018 à 13:30, R Smith  a écrit :
> 
> PS: Anyone here that takes web servers and the like through upgrades to 
> different Apache, or PHP versions, or different MySQL/MariaDB versions will 
> know the effort of re-engineering your work from years ago to fit the new 
> upgrade. SQLite is the only "fire-and-forget" weapon out there, I feel like 
> that feature is worth a few TYPE pains - but that's just my feeling and it 
> might well be antiquated.

+1: fully shared feeling. :)

-- 
Best regards, Meilleures salutations, Met vriendelijke groeten,  
Olivier Mascia (from mobile device)

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

[sqlite] Determine collation associated with sort

[x]

Suppose I have a select such as

‘select * from TblsAndJoins where Condns order by OrdCol1,OrdCol2,...,OrdColn’

I want to know the collation associated with the sort. I know a COLLATE 
condition could be attached to the order by BUT suppose there’s none. How would 
I determine the collation associated with the sort?

I’m familiar with https://sqlite.org/c3ref/table_column_metadata.html

int sqlite3_table_column_metadata(
  sqlite3 *db,/* Connection handle */
  const char *zDbName,/* Database name or NULLtion sequence */
  const char *zTableName, /* Table name */
  const char *zColumnName,/* Column name */
  char const **pzDataType,/* OUTPUT: Declared data type */
 char const **pzCollSeq, /* OUTPUT: Collation sequence name */
  int *pNotNull,  /* OUTPUT: True if NOT NULL constraint exists */
  int *pPrimaryKey,   /* OUTPUT: True if column part of PK */
  int *pAutoinc   /* OUTPUT: True if column is auto-increment */
);

which would tell me if a particular column of the sort has a collation sequence 
but what if it’s the index itself that has a collation attached? How would I 
cover all possible angles?







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

Re: [sqlite] column types and constraints

[R Smith]

On 2018/06/30 9:04 AM, Thomas Kurz wrote:



COLUMN xy VARCHAR(50)
COLUMN xy MEDIUMTEXT
COLUMN xy BIT
COLUMN xy DECIMAL(5,2)
Should those raise errors? Because to SQLite those are the exact same
gibberish as:

I would appreciate if SQLite raised an error each time the declaration 
mismatches the interpretation. In your example, all declarations are well-known 
SQL. And, if I understand correctly, SQLite treats each of those as NUMERIC. So:

a) Yes, error, as I indent to store strings, but SQLite uses numbers.
b) Yes, error, same case.
c) Preferably yes, but not mandatory, as BIT is compatible to NUMERIC
d) No, as DECIMAL ist compatible to NUMERIC.


I'm sorry, upon re-reading I see the real point I tried to make got lost 
in that I concentrated too much on the physical examples. The real point 
is that, in c for example, SQLite is not understanding BIT as a type  
and it is not responsible for understanding all types used in all 
database engines. Imagine that you couldn't use BIT in MySQL, it's an 
MSSQL construct - why do you require SQLite to interpret it?  Moreover, 
what if a brand new type arise today? Let's call it CAT_MOUSE_DOG as an 
example, and let's say Oracle, MSSQL and Postgres ALL adopt this new type.


Do you still expect SQLite to error out when you declare a column with 
that type?


SQLite doesn't "know" which types/conventions will arise in future, 
hence, since its birth (before many of the modern types) it has allowed 
UNKNOWN types in the declaration, and then made an effort to guess the 
best affinity upon it, which works well mostly, but not always. More 
pertinently, once it does actually lock down a "meaning" upon a type or 
affinity, it can never go back. It's worth being under-zealous.


As to your statement about the problem with STRING - Yes, let's agree 
that SQLite dropped the ball on that one many many moons ago (so much so 
they have had enough backlash [just like your case] that they have 
amended the documentation to warn about that very specific case. By now 
there are billions of schemas in the world, many of which might have 
STRING declarations and have adopted a checking mechanism in their own 
code to deal with it (much like you might do now), so changing it may 
harm those implementations.


And yes, you might think (like me) that this is silly and they should 
just fix it, warning people about the new way for a good few versions, 
and then just roll it out. But, that would mean there will exist schemas 
in the World that will be interpreted differently by different versions, 
and the selfish comfort to me comes when I think: "If I make a DB today 
that works... it will work for all those years/upgrades to come, and I 
can trust that it will because if the SQLite Devs won't fix STRING, then 
they are unlikely to change anything that will harm MY systems."



Cheers!
Ryan

PS: Anyone here that takes web servers and the like through upgrades to 
different Apache, or PHP versions, or different MySQL/MariaDB versions 
will know the effort of re-engineering your work from years ago to fit 
the new upgrade. SQLite is the only "fire-and-forget" weapon out there, 
I feel like that feature is worth a few TYPE pains - but that's just my 
feeling and it might well be antiquated.




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

Re: [sqlite] unique values from a subset of data based on two fields

[Paul Sanderson]

Easier and pretty obvious :) Thanks Keith



Paul
www.sandersonforensics.com
SQLite Forensics Book 

On 29 June 2018 at 23:20, Keith Medcalf  wrote:

> >I want a query that returns all of the records with status = 1 and
> >unique records, based on name, where the status =0 and the name is
> >not in the list status=1
>
> Translation into SQL using English to SQL Translator, using the most
> direct translation on the "problem statement" above directly into SQL:
>
> create table names (id int, status int, name text);
> insert into names values (1, 1, 'paul');
> insert into names values (2, 1, 'helen');
> insert into names values (3, 0, 'steve');
> insert into names values (4, 0, 'steve');
> insert into names values (5, 0, 'pete');
> insert into names values (6, 0, 'paul');
>
> -- I want a query that returns all of the records with status = 1
>
> SELECT id,
>status,
>name
>   FROM names
>  WHERE status == 1
>
> -- and
>
> UNION
>
> -- unique records, based on name, where the status = 0 and the name is not
> in the list [of names where] status=1
>
> SELECT id,
>status,
>name
>   FROM names
>  WHERE status == 0
>AND name NOT IN (SELECT name
>   FROM names
>  WHERE status == 1)
> GROUP BY name;
>
> Returns the rows:
>
> 1|1|paul
> 2|1|helen
> 3|0|steve
> 5|0|pete
>
> If the table is bigger than trivial (ie, contains more than the number of
> rows you can count with your fingers) then you will need the appropriate
> indexes to achieve performant results.
>
> ---
> The fact that there's a Highway to Hell but only a Stairway to Heaven says
> a lot about anticipated traffic volume.
>
>
> >-Original Message-
> >From: sqlite-users [mailto:sqlite-users-
> >boun...@mailinglists.sqlite.org] On Behalf Of Paul Sanderson
> >Sent: Friday, 29 June, 2018 09:50
> >To: General Discussion of SQLite Database
> >Subject: [sqlite] unique values from a subset of data based on two
> >fields
> >
> >I have a table
> >
> >Create table names (id int, status int, name text)
> >
> >
> >
> >1, 1, 'paul'
> >
> >2, 1,'helen'
> >
> >3, 0, 'steve'
> >
> >4, 0, 'steve'
> >
> >5, 0, 'pete'
> >
> >6, 0, 'paul'
> >
> >
> >
> >I want a query that returns all of the records with status = 1 and
> >unique
> >records, based on name, where the status =0 and the name is not in
> >the list
> >status=1
> >
> >
> >
> >So from the above I would want to see
> >
> >
> >
> >1, 1, paul
> >
> >2, 1, helen
> >
> >3, 0, steve (or 4, 0, steve)
> >
> >5, 0, pete
> >
> >
> >
> >I could do something like
> >
> >
> >
> >Select * from names where status = 1 or name not in (select name from
> >names
> >where status = 1)
> >
> >
> >
> >But this gets both rows for steve, e.g.
> >
> >
> >
> >1, 1, paul
> >
> >2, 1, helen
> >
> >3, 0, steve
> >
> >4, 0, steve
> >
> >5, 0, pete
> >
> >while I am not bothered about which of the two steves I get back, I
> >must
> >have all occurences of names with status = 1
> >
> >I am probably missing somethng obvious
> >
> >Paul
> >www.sandersonforensics.com
> >SQLite Forensics Book 
> >___
> >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] column types and constraints

[Olivier Mascia]

> Le 30 juin 2018 à 09:04, Thomas Kurz  a écrit :
> 
> CREATE TABLE a (col1 STRING);
> INSERT INTO a (col1) VALUES ("3.0");
> SELECT * from a;
> ---> 3// this should never happen!!

SQLite type affinity rules clearly do not recognise STRING as TEXT: it does so 
only when the type contains the words CHAR, TEXT or CLOB.  STRING, which you 
use for your example, is even specifically warned about (being of NUMERIC 
affinity).

https://www.sqlite.org/datatype3.html#determination_of_column_affinity

-- 
Best Regards, Meilleures salutations, Met vriendelijke groeten,
Olivier Mascia


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

Re: [sqlite] column types and constraints

[Olivier Mascia]

> Le 30 juin 2018 à 09:04, Thomas Kurz  a écrit :
> 
>> a) COLUMN xy VARCHAR(50)
>> b) COLUMN xy MEDIUMTEXT
>> c) COLUMN xy BIT
>> d) COLUMN xy DECIMAL(5,2)
>> Should those raise errors? Because to SQLite those are the exact same 
>> gibberish as:
> 
> I would appreciate if SQLite raised an error each time the declaration 
> mismatches the interpretation. In your example, all declarations are 
> well-known SQL. And, if I understand correctly, SQLite treats each of those 
> as NUMERIC.

I think you don't.
The rules are simple: 
https://www.sqlite.org/datatype3.html#determination_of_column_affinity

> So:
> a) Yes, error, as I indent to store strings, but SQLite uses numbers.

Wrong. VARCHAR has CHAR in the name, SQLite uses TEXT affinity, which match 
perfectly.

> b) Yes, error, same case.

Wrong. MEDIUMTEXT has TEXT in the name, SQLite uses TEXT affinity, which match 
perfectly.

> c) Preferably yes, but not mandatory, as BIT is compatible to NUMERIC

If BIT was to be refused, then would DATE and BOOLEAN for instance. The three 
of them have the convenience to lean to NUMERIC storage thanks to affinity 
rules and it is perfectly fine when you have understood and agreed before 
choosing to use SQLite, that SQLite only uses 4 basic types (let's name them 
INT, REAL, TEXT, BLOB) and that any type wording used in declaration is 
meaningless except that it leads to a type affinity for the column.

> d) No, as DECIMAL ist compatible to NUMERIC.

Fine.

-- 
Best Regards, Meilleures salutations, Met vriendelijke groeten,
Olivier Mascia


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

Re: [sqlite] column types and constraints

[Simon Slavin]

On 30 Jun 2018, at 8:35am, Keith Medcalf  wrote:

> You "put" a ieee754 floating point double.

Keith, Are you referring to this ?

On 30 Jun 2018, at 8:04am, Thomas Kurz  wrote:

> CREATE TABLE a (col1 STRING);
> INSERT INTO a (col1) VALUES ("3.0");
> SELECT * from a;
> ---> 3// this should never happen!!

If so, then I disagree with you.  The column was defined as STRING.  A string 
was put into it.  There is nothing in the above to suggest that the programmer 
might one day want the value they supplied mutilated.

The problem, as far as SQLite is concerned, is that the column should have been 
declared "TEXT' not STRING.  Then it works correctly:

SQLite version 3.22.0 2017-12-05 15:00:17 [...]
sqlite> CREATE TABLE a (col1 STRING);
sqlite> INSERT INTO a (col1) VALUES ("3.0");
sqlite> SELECT * from a;
3
sqlite> DROP TABLE a;
sqlite> CREATE TABLE a (col1 TEXT);
sqlite> INSERT INTO a (col1) VALUES ("3.0");
sqlite> SELECT * from a;
3.0

But course the dev team cannot correct the understanding of 'STRING' for 
backward compatibility reasons.  And using affinities rather than types means 
that feeding a string into a numeric column does not generate an error.  So the 
programmer never figured out that using 'STRING' as a type didn't do the right 
thing.

It's a problem with multiple causes.  And it cannot be fixed in SQLite3.

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

Re: [sqlite] column types and constraints

[Thomas Kurz]

> You "put" a ieee754 floating point double.

No I don't, I put a string ;-)

I am not complaining that I can put anything anywhere. But the data type that I 
provide should be kept. When providing a string, it should be stored as such to 
have the possibility to get back the original value. When requesting an integer 
or float, it is perfectly ok to convert it. But it should be stored what is put.

> BTW, SQL delimits strings with single-quotes.  Double-quotes mean that what 
> is between them is an identifier.

Sorry, I thought it was the other way round. But it doesn't actually matter in 
this case.

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

Re: [sqlite] column types and constraints

[Keith Medcalf]

You "put" a ieee754 floating point double.  If you retrieved an ieee754 
floating point double, you would get back that which you put!  The fact that 
internally SQLite3 stored it as a 3 (integer, token, string, whatever) is 
irrelevant.  You "gets" what you "puts", as long as what you "putted" is the 
same as what you "asked" for.  Are you complaining that you can "puts" anything 
anywhere, or that once "putted" you can "ask" for whatever format you want?  Or 
are you complaining about how the "column type gibberish" is interpreted if you 
do not stick to correct affinities?  Or that data conversions occur on the 
"putted" value when it is stored or that it may occur if you "asks" for a 
different format than what is stored?

The rules for the parsing of the gibberish are here by the way:

https://www.sqlite.org/datatype3.html

The easy solution to those issues are "don't do that" ... and if you don't 
trust that someone else didn't "don't do that" to your applications database, 
then you better check what you are doing when you "asks" for data...

(And the command line shell is a bad way to do things because it does can only 
"puts" text (which must always be converted), and can only display text after 
conversion from whatever format the data was "putsed" and "stored" with).

BTW, SQL delimits strings with single-quotes.  Double-quotes mean that what is 
between them is an identifier.  For historical reasons if something which is 
double-quoted cannot be resolved to an identifier in the context it was used, 
the assumption is that the coder (cuz programmers would never make such an 
error) is a dodo and *meant* to use single-quotes but was typing in all-caps at 
the time and put in the wrong character ...

---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.

>-Original Message-
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of Thomas Kurz
>Sent: Saturday, 30 June, 2018 01:04
>To: SQLite mailing list
>Subject: Re: [sqlite] column types and constraints
>
>> I don't disagree, but this means we lose sight of the important
>point
>>that, if you distill the problem to just "INTEGER", then sure, it
>looks
>> silly, and sure, you can fix it with internal auto-CHECKing, but in
>> SQLite the type affinity INTEGER stands father to LONGINT,
>MEDIUMINT,
>
>Ok, I understand your argumentation now. But I don't think
>distinguishing different integer types or strings with different
>length declarations would be mandatory. IIRC, they were introduced to
>save memory in times when database files were actually just a
>sequence of fixed-length records/structs. SQLite on the other hand
>always uses the least memory-consuming way for storing data anyway.
>
>But I must admit that a disctinction between alternatives of the same
>"base type" was something I didn't have in mind, so consider me
>convinced ;-)
>
>> COLUMN xy VARCHAR(50)
>> COLUMN xy MEDIUMTEXT
>> COLUMN xy BIT
>> COLUMN xy DECIMAL(5,2)
>> Should those raise errors? Because to SQLite those are the exact
>same
>> gibberish as:
>
>I would appreciate if SQLite raised an error each time the
>declaration mismatches the interpretation. In your example, all
>declarations are well-known SQL. And, if I understand correctly,
>SQLite treats each of those as NUMERIC. So:
>
>a) Yes, error, as I indent to store strings, but SQLite uses numbers.
>b) Yes, error, same case.
>c) Preferably yes, but not mandatory, as BIT is compatible to NUMERIC
>d) No, as DECIMAL ist compatible to NUMERIC.
>
>Let me explain my problem, something which happens to me quite often
>as many programming languages name the thing "string" that DBMSs want
>to have named "text" (why the hell??):
>
>CREATE TABLE a (col1 STRING);
>INSERT INTO a (col1) VALUES ("3.0");
>SELECT * from a;
>---> 3// this should never happen!!
>
>Instead of raising an error on the column definition (which would be
>most elegant imho), it would be perfectly ok if SQLite treated the
>string ("text") that I provide in the insert-statement (double
>quotes) actually as a string and did not try to convert it. Or, more
>clearly spoken, if it recognized that the conversion is not
>reversible to the original string.
>
>Currently, there is no warning whatsoever for me to lose data after
>having used an invalid column declaration. Meanwhile I'm more aware
>of that, but the first time I had spent hours trying to figure out
>what's going wrong until I found the problem.
>
>INSERT INTO a (col1) VALUES (3.0);
>SELECT * from a;
>---> 3// I would accept that as I have provided a number
>
>Maybe, this could be a compromise?
>
>In your previous post, you wrote:
>
>> [...]
>> VARCHAR(3)
>> Truncate the string to "MAM" in MySQL, without an error at all,
>just kill some data and move on!
>
>My example is exactly the same. SQLite continues without an error
>losing me data. There is no way getting it back. Even this don't
>work:
>
>SELE

Re: [sqlite] column types and constraints

[Thomas Kurz]

> I don't disagree, but this means we lose sight of the important point 
>that, if you distill the problem to just "INTEGER", then sure, it looks 
> silly, and sure, you can fix it with internal auto-CHECKing, but in 
> SQLite the type affinity INTEGER stands father to LONGINT, MEDIUMINT, 

Ok, I understand your argumentation now. But I don't think distinguishing 
different integer types or strings with different length declarations would be 
mandatory. IIRC, they were introduced to save memory in times when database 
files were actually just a sequence of fixed-length records/structs. SQLite on 
the other hand always uses the least memory-consuming way for storing data 
anyway.

But I must admit that a disctinction between alternatives of the same "base 
type" was something I didn't have in mind, so consider me convinced ;-)

> COLUMN xy VARCHAR(50)
> COLUMN xy MEDIUMTEXT
> COLUMN xy BIT
> COLUMN xy DECIMAL(5,2)
> Should those raise errors? Because to SQLite those are the exact same 
> gibberish as:

I would appreciate if SQLite raised an error each time the declaration 
mismatches the interpretation. In your example, all declarations are well-known 
SQL. And, if I understand correctly, SQLite treats each of those as NUMERIC. So:

a) Yes, error, as I indent to store strings, but SQLite uses numbers.
b) Yes, error, same case.
c) Preferably yes, but not mandatory, as BIT is compatible to NUMERIC
d) No, as DECIMAL ist compatible to NUMERIC.

Let me explain my problem, something which happens to me quite often as many 
programming languages name the thing "string" that DBMSs want to have named 
"text" (why the hell??):

CREATE TABLE a (col1 STRING);
INSERT INTO a (col1) VALUES ("3.0");
SELECT * from a;
---> 3// this should never happen!!

Instead of raising an error on the column definition (which would be most 
elegant imho), it would be perfectly ok if SQLite treated the string ("text") 
that I provide in the insert-statement (double quotes) actually as a string and 
did not try to convert it. Or, more clearly spoken, if it recognized that the 
conversion is not reversible to the original string.

Currently, there is no warning whatsoever for me to lose data after having used 
an invalid column declaration. Meanwhile I'm more aware of that, but the first 
time I had spent hours trying to figure out what's going wrong until I found 
the problem.

INSERT INTO a (col1) VALUES (3.0);
SELECT * from a;
---> 3// I would accept that as I have provided a number

Maybe, this could be a compromise?

In your previous post, you wrote:

> [...]
> VARCHAR(3)
> Truncate the string to "MAM" in MySQL, without an error at all, just kill 
> some data and move on!

My example is exactly the same. SQLite continues without an error losing me 
data. There is no way getting it back. Even this don't work:

SELECT CAST(col1 AS TEXT) FROM a;
---> 3// <> "3.0" !!

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