Re: [sqlite] Generated columns and COLLATE in the AS parens

2020-01-31 Thread Richard Hipp
On 1/31/20, Markus Winand wrote: > > - Expressions also have a declared type. More elaborate: the result value of > an expression has a type that is statically determined (again the “declared > type”). SQLite is a generalization of "Standard SQL" that allows expressions that can have an

Re: [sqlite] Generated columns and COLLATE in the AS parens

2020-01-31 Thread Keith Medcalf
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 On >Behalf Of Markus Winand >Sent: Friday, 31 January, 2020 03:29 >To: SQLite mailing list >Subject: Re: [sqlite] Generated

Re: [sqlite] Generated columns and COLLATE in the AS parens

2020-01-31 Thread Markus Winand
Let me first explain how collations work in standard SQL. I think my answers below make more sense then. In the SQL standard…. - character string **types** have properties such as fix-length vs. variable length, the length (limit), character set and also the collation. - columns have types,

Re: [sqlite] Generated columns and COLLATE in the AS parens

2020-01-30 Thread Keith Medcalf
On: Wednesday, 29 January, 2020 06:45, Markus Winand wrote: >I think there might be a glitch in the way SQLite 3.31.x derives the >collation information from the expression of a generated column. >In particular, COLLATE inside the AS parens seems to be ignored, but it >is honoured after the

Re: [sqlite] Generated columns and COLLATE in the AS parens

2020-01-30 Thread Markus Winand
> On 30 Jan 2020, at 18:20, Richard Hipp wrote: > > On 1/30/20, Markus Winand wrote: >> >> Unfortunately, the “what would PostgreSQL do” approach doesn’t provide >> guidance here. > > Maybe it does. PostgreSQL doesn't allow typeless columns, but it does > allow columns with unspecified

Re: [sqlite] Generated columns and COLLATE in the AS parens

2020-01-30 Thread David Raymond
| s| C (9 rows) Time: 0.590 ms testing=> -Original Message----- From: sqlite-users On Behalf Of Richard Hipp Sent: Thursday, January 30, 2020 12:21 PM To: SQLite mailing list Subject: Re: [sqlite] Generated columns and COLLATE in the AS parens On 1/30/20, Markus Wina

Re: [sqlite] Generated columns and COLLATE in the AS parens

2020-01-30 Thread Richard Hipp
On 1/30/20, Markus Winand wrote: > > Unfortunately, the “what would PostgreSQL do” approach doesn’t provide > guidance here. Maybe it does. PostgreSQL doesn't allow typeless columns, but it does allow columns with unspecified collating sequences, does it not? What if you have a normal column X

Re: [sqlite] Generated columns and COLLATE in the AS parens

2020-01-30 Thread Markus Winand
> On 29 Jan 2020, at 14:59, Richard Hipp wrote: > > On 1/29/20, Markus Winand wrote: >> Hi! >> >> I think there might be a glitch in the way SQLite 3.31.x derives the >> collation information from the expression of a generated column. > > I think the current behavior is correct. > > If you

Re: [sqlite] Generated columns and COLLATE in the AS parens

2020-01-29 Thread Richard Hipp
On 1/29/20, Markus Winand wrote: > Hi! > > I think there might be a glitch in the way SQLite 3.31.x derives the > collation information from the expression of a generated column. I think the current behavior is correct. If you want a column to have a non-standard collating sequence, you should

[sqlite] Generated columns and COLLATE in the AS parens

2020-01-29 Thread Markus Winand
Hi! I think there might be a glitch in the way SQLite 3.31.x derives the collation information from the expression of a generated column. In particular, COLLATE inside the AS parens seems to be ignored, but it is honoured after the parens: SQLite version 3.31.1 2020-01-27 19:55:54 Enter