Re: [sqlite] Date time input

On 10/10/2019 07:50, J Decker wrote: It's 'ite' as in 'stalagmite' or 'meteorite' as in rock solid... https://changelog.com/podcast/201 " RICHARD HIPP How do I pronounce the name of the product? I say S-Q-L-ite, like a mineral. So probably

Re: [sqlite] [EXTERNAL] unexpected error with "GROUP BY 0"

ame, period, day + hour / 24.0 -- output column identifier x2 + "any other expression" -Original Message- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Mark Brand Sent: Mittwoch, 30. Mai 2018 16:22 To: sqlite-users@mailinglists.sqlite.org Sub

Re: [sqlite] [EXTERNAL] unexpected error with "GROUP BY 0"

output column identifiers and "any other expression" as terms for GROUP BY. If the expression evalutes to a constant value, you will have only one output row. -Ursprüngliche Nachricht- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Mark Brand

Re: [sqlite] [EXTERNAL] unexpected error with "GROUP BY 0"

SELECT 0 GROUP BY 31" would be. -Ursprüngliche Nachricht- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Mark Brand Gesendet: Mittwoch, 30. Mai 2018 11:32 An: SQLite mailing list Betreff: [EXTERNAL] [sqlite] unexpected error with "GROUP BY

[sqlite] unexpected error with "GROUP BY 0"

Hi, Is there a good reason for this error: sqlite> SELECT  0 GROUP BY 0; Error: 1st GROUP BY term out of range - should be between 1 and 1 sqlite> SELECT 0 GROUP BY 1; 0 ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org

Re: [sqlite] feature request: MIN() and MAX() of set of row values

On 13/04/18 14:12, Simon Slavin wrote: On 13 Apr 2018, at 8:40am, Mark Brand <mabr...@mabrand.nl> wrote: It also occurs to me that COUNT() should work (but doesn't) over sets of row values: sqlite> select count((1,2)); Error: row value misused I would expect it

Re: [sqlite] feature request: MIN() and MAX() of set of row values

On 13/04/18 09:32, Mark Brand wrote: On 30/03/18 18:55, Igor Tandetnik wrote: Row values support less-than comparison, so it kind of makes sense to expect MIN to work on them, too. That's what I was thinking too. One would expect aggregate MIN() and MAX() to work over row values. While

Re: [sqlite] feature request: MIN() and MAX() of set of row values

On 30/03/18 18:55, Igor Tandetnik wrote: Row values support less-than comparison, so it kind of makes sense to expect MIN to work on them, too. That's what I was thinking too. One would expect aggregate MIN() and MAX() to work over row values. While we're on the subject of row values,

[sqlite] MIN() and MAX() of set of row values

Hi, Row values make life easier in so many ways, but I was just wondering if there is (or should be or could be) a way to use aggregate MIN() and MAX() on a set of row values. Mark CREATE TABLE T (a, b); INSERT INTO T (a, b) VALUES (1, 2), (1, 3), (2, 3); SELECT MIN((a,b)) = (1, 2) ok 

Re: [sqlite] missing subquery flattening

On 26/01/18 19:35, Clemens Ladisch wrote: Mark Brand wrote: Shouldn't we expect subquery flattening to happen in V2 below? -- no flattening CREATE VIEW V2 AS SELECT * FROM X LEFT JOIN ( SELECT * FROM X LEFT JOIN Y ON Y.a = X.a ) Z ON Z.a = X.a

[sqlite] missing subquery flattening

Hi, Shouldn't we expect subquery flattening to happen in V2 below? Mark CREATE TABLE X ( a PRIMARY KEY, b ) WITHOUT ROWID; CREATE TABLE Y ( a PRIMARY KEY ) WITHOUT ROWID; /* WITH RECURSIVE Q AS (     SELECT 1 a     UNION ALL SELECT a + 1 FROM Q     WHERE a < 10 ) INSERT INTO X (a, b)

[sqlite] documentation edits needed

Here are some suggested improvements for the constraints listed under:     https://www.sqlite.org/optoverview.html, section  10. Subquery flattening 3. The subquery is not the right operand of a LEFT JOIN then the subquery may not be a join, the FROM clause of the subquery may not contain

[sqlite] unexpected row value error

Hi, The 6th SELECT example below throws an error. This seems unexpected, especially given the contrast with example 3, which differs only in lacking a seemingly unrelated JOIN.  Am I overlooking something? Removing the PRIMARY KEY from table x also avoids the error somehow. Seen on version

Re: [sqlite] Syntax Restrictions On UPDATE, DELETE, and INSERT Statements Within Triggers

For non-TEMP triggers, the table to be modified or queried must exist in the same database as the table or view to which the trigger is attached. TEMP triggers are not subject to the same-database rule. A TEMP trigger is allowed to query or modify any table in any ATTACH

Re: [sqlite] Syntax Restrictions On UPDATE, DELETE, and INSERT Statements Within Triggers

On 09/06/17 14:47, Richard Hipp wrote: The documentation has been updated to clarify the ambiguity and to hopefully make it easier to understand. Thanks. The exception for non-TEMP triggers is something I was hoping for too: For non-TEMP triggers, the table to be modified or queried must

[sqlite] Syntax Restrictions On UPDATE, DELETE, and INSERT Statements Within Triggers

According to the documentation: The name of the table to be modified in an UPDATE, DELETE, or INSERT statement must be an unqualified table name. In other words, one must use just "tablename" not "database.tablename" when specifying the table. *The table to be modified must exist in

Re: [sqlite] BUG?: operation unexpectedly applied to both main and temp

On 30/03/17 21:06, David Raymond wrote: Close. It rests on the backs of 4 elephants, who in turn stand on the back of the Great A'Tuin I don't know but I've been told it's turtles all the way down. ___ sqlite-users mailing list

Re: [sqlite] BUG?: operation unexpectedly applied to both main and temp

On 30/03/17 19:10, Simon Slavin wrote: On 30 Mar 2017, at 5:53pm, Mark Brand <mabr...@mabrand.nl> wrote: The documentation actually says "*least* recently attached". https://www.sqlite.org/lang_attach.html, paragraph 4. I’m unsure whether you can legitimately say that the

Re: [sqlite] BUG?: operation unexpectedly applied to both main and temp

On 29/03/17 15:29, Olivier Mascia wrote: Le 29 mars 2017 à 02:38, Simon Slavin a écrit : It seems sqlite look first if there is a temp.table before main.table and without qualification temp.table is used. You got it. It’s not obvious that this is what SQLite would

Re: [sqlite] operation unexpectedly applied to both main and temp?

On 29/03/17 02:38, Simon Slavin wrote: On 29 Mar 2017, at 1:34am, Domingo Alvarez Duarte wrote: It seems sqlite look first if there is a temp.table before main.table and without qualification temp.table is used. You got it. It’s not obvious that this is what SQLite

Re: [sqlite] BUG?: operation unexpectedly applied to both main and temp

On 29/03/17 02:12, Mark Brand wrote: On 29/03/17 01:35, Simon Slavin wrote: On 28 Mar 2017, at 11:02pm, Mark Brand <mabr...@mabrand.nl> wrote: create temp table t (db, val); insert into t select 'temp', 'original'; create table t (db, val); insert into t select 'main', 'or

Re: [sqlite] BUG?: operation unexpectedly applied to both main and temp

On 29/03/17 01:35, Simon Slavin wrote: On 28 Mar 2017, at 11:02pm, Mark Brand <mabr...@mabrand.nl> wrote: create temp table t (db, val); insert into t select 'temp', 'original'; create table t (db, val); insert into t select 'main', 'original'; Here’s your problem: SQLite version

[sqlite] BUG?: operation unexpectedly applied to both main and temp

HI, Something seems to go wrong in this example where an operation unexpectedly gets applied both main and temp. The order of table creation seems to be one crucial factor. I ran into this while trying to get my head around the use of temporary triggers, which seems to be the other

[sqlite] 'start of day' modifier and Julian day timestring

Hi, I am wondering why the 'start of day' and 'start of year' modifiers don't seem to work on Julian day timestrings. What is the explanation for the NULLs in examples 2 and 3 below? SELECT example, timestring, strftime('%J', timestring), strftime('%Y-%m-%d %H:%M',

[sqlite] SQL logic anomaly

Hi, Using SQLite version 3.15.2, the following SQL returns 0 rows, whereas I believe it should return 1 row. Any of the commented out alternatives produces the expected 1 row. Mark CREATE VIEW W AS SELECT 0 show_a; CREATE VIEW X AS SELECT 'A' a, 1 v UNION SELECT

[sqlite] row values with BETWEEN

Hi, i hope I'm not overlooking something obvious, but the last result below is not what I would expect. Just wondering if this could somehow be correct and, if so, how to understand it. regards, Mark $ sqlite3 SQLite version 3.15.1 2016-11-04 12:08:49 Enter ".help" for usage hints.

Re: [sqlite] when to expect "no such table"

On 23/06/16 16:08, Richard Hipp wrote: On Thu, Jun 23, 2016 at 9:54 AM, Mark Brand <mabr...@mabrand.nl> wrote: Hi, Just wondering about the apparent variation among sqlite3 versions and/or configurations with respect to the "no such table" error. In some cases it's enforced

[sqlite] when to expect "no such table"

Hi, Just wondering about the apparent variation among sqlite3 versions and/or configurations with respect to the "no such table" error. In some cases it's enforced at view creation and in others at execution. Mark SQLite version 3.8.7.1 2014-10-29 13:59:56 Enter ".help" for usage hints.

[sqlite] Documentation Error: sqlite3_set_authorizer() interface

tempt..". Sorry about creating noise. On 31/08/15 13:17, Mark Brand wrote: > According to the documentations of The Authorizer Action Codes > (http://www.sqlite.org/c3ref/c_alter_table.html): > > Authorizer Action Codes > > /

[sqlite] Documentation Error: sqlite3_set_authorizer() interface

According to the documentations of The Authorizer Action Codes (http://www.sqlite.org/c3ref/c_alter_table.html): Authorizer Action Codes /*** 3rd 4th ***/ #define SQLITE_SELECT 21 /* NULLNULL

[sqlite] BUG: sqlite 3.8.11.x left join

Just realized that this simpler case shows the same problem: SELECT * FROM ( SELECT 'apple' fruit UNION ALL SELECT 'banana' ) a LEFT JOIN ( SELECT 1 isyellow ) c ON a.fruit='banana' ; On 22/08/15 00:58, Mark Brand wrote: > Hi, > > For the query below, versions

[sqlite] BUG: sqlite 3.8.11.x left join

Hi, For the query below, versions 3.8.11.0 and 3.8.11.1 return only 1 row. For some reason, the LEFT JOIN seems to behave like a JOIN. Older versions (tested 3.8.7.4 and 3.8.10.2) correctly return 2 rows. SELECT * FROM ( SELECT 'apple' fruit UNION ALL SELECT 'banana' ) a JOIN (

[sqlite] sqlite 3.8.* EXPLAIN QUERY PLAN leaves out LEFT JOIN table

Hi, I notice that EXPLAIN QUERY PLAN in sqlite 3.8.* doesn't mention the LEFT JOIN table in examples like the following. Sqlite 3.3.17 does mention it. Is this by design, or is something going wrong? CREATE TABLE aaa (a INTEGER PRIMARY KEY); CREATE TABLE bbb (b INTEGER PRIMARY KEY);

[sqlite] JOIN failure in sqlite 3.8.1

Hi, I noticed that some of my views were not working with sqlite 3.8.1. I managed to isolate the problem in the simple test case below. Then I discovered that the 3.8.2 pre-release seems to do it right. I suspect it was fixed along with https://www.sqlite.org/src/tktview?name=c620261b5b but I

[sqlite] spelling in sqlite3.c version 3.7.16

For what it's worth, I thought I'd forward the spelling errors reported by qt-project's automatic review system. The ones with the asterisk depend on which national spelling standard you follow. src/3rdparty/sqlite/sqlite3.c: sytem -> system (occurs twice) existance -> existence

Re: [sqlite] "natrual" join (not "natural")

Roger Binns wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 01/11/12 18:25, Mark Brand wrote: You mentioned that this extra AS might help avoid ambiguities and errors. The only example of this you mentioned was where aliases names with spaces are not quoted. Do you have cases

Re: [sqlite] "natrual" join (not "natural")

The misspelling of NATURAL would be caught by a warning for JOIN without ON constraint. True. And other possible errors could be caught by missing AS. Really? In my opinion, it's actually a good idea to leave out the AS in table aliases since SQL-92 and many or most implementations do not

Re: [sqlite] "natrual" join (not "natural")

Nico Williams wrote: On Thu, Nov 1, 2012 at 5:18 PM, Mark Brand <mabr...@mabrand.nl> wrote: Why should the "missing" AS even be a warning in lint mode? SQL-92 does not mandate it. One of the most important implementations does not even accept it. Do you know of any SQ

Re: [sqlite] "natrual" join (not "natural")

On 01/11/12 14:32, Mark Brand wrote: My point is that the proposed lint mode should worry more about 2) than 1). I didn't think they are mutually exclusive. The class of probable errors detected overlaps. But the "missing" AS is not a probable error. It's standard and well-establi

Re: [sqlite] "natrual" join (not "natural")

The optionality of "AS" when aliasing column names is not strange. Oops. I see that I did not mean "column names". This is about table aliases. You are missing the point :-) I am not arguing for syntax changes or anything else in the core of SQLite or SQL. No, I don't don't think I am

Re: [sqlite] "natrual" join (not "natural")

Roger Binns wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 31/10/12 07:27, Jonas Malaco Filho wrote: Maybe there could be a strict switch. There is a ticket for a "lint" mode that would help catch common issues where there are possibly problems. This is another example of something

Re: [sqlite] [BUG?] unexpected effect of view nesting on type conversion

>>> I've run into a phenomenon I don't understand where view nesting affects >>> types. >>> Seen in sqlite 3.7.6.3 >> sqlite-3.6.23 does NOT show this behavior. > After further reflection, I am going to go out on a limb and suggest > that the behavior described in the OP is a regression. The

Re: [sqlite] [BUG?] unexpected effect of view nesting on type conversion

>> I've run into a phenomenon I don't understand where view nesting affects >> types. >> Seen in sqlite 3.7.6.3 > sqlite-3.6.23 does NOT show this behavior. After further reflection, I am going to go out on a limb and suggest that the behavior described in the OP is a regression. The behavior

Re: [sqlite] unexpected effect of view nesting on type conversion

> I've run into a phenomenon I don't understand where view nesting affects > types. > Seen in sqlite 3.7.6.3. > sqlite-3.6.23 does NOT show this behavior. I don't know though when the change happened. Mark ___ sqlite-users mailing list

[sqlite] unexpected effect of view nesting on type conversion

Hi, I've run into a phenomenon I don't understand where view nesting affects types. Seen in sqlite 3.7.6.3. The following is my attempt at a minimal case that produces this behavior. Here a real is unexpectedly converted to int. CREATE TABLE customer ( id INT, PRIMARY KEY (id) );

Re: [sqlite] tarball and directory name

On 12/08/2010 09:30 AM, Mark Brand wrote: > Hi, > > Just noticed that the format of the version in the tarball name changed, > as in "sqlite-autoconf-3070400.tar.gz". However, this unpacks to a > directory called "sqlite-3.7.4". This makes it difficult for

[sqlite] tarball and directory name

Hi, Just noticed that the format of the version in the tarball name changed, as in "sqlite-autoconf-3070400.tar.gz". However, this unpacks to a directory called "sqlite-3.7.4". This makes it difficult for automated build systems that want to have a predictable directory name. Would it be

Re: [sqlite] Last 3 items per ID

> Simplified there is a table like this: > > create table xxx( > [entry_id] integer primary_key, > [person_id] integer) > > Now I need to retrieve the rows with the 3 highest entry_id numbers > for each person_id. > > so for example (in reality entry_id can have gaps): > > entry_id

Re: [sqlite] What's the problem with my INSERT clause?

> insert into tb_appointment(GroupCalendarID, ObjectID, Subject) values( > ( > select ugc.GroupCalendarID > from TB_User_GroupCalendar ugc > join TB_User u on u.objectID = ugc.UserID > join TB_GroupCalendar gc on gc.ObjectID = ugc.GroupCalendarID > where calendarname='DEFAULT CALENDAR FOR ' ||

Re: [sqlite] unexpected behavior with CROSS and LEFT JOIN together

> It's pretty strange how you try to join with some table not even > mentioning any column of that table in the joining condition. I bet > behavior is not defined for such cases in SQL standard and you're > getting some interpretation of such query. > I'm not aware of any requirement that a

[sqlite] unexpected behavior with CROSS and LEFT JOIN together

Hi, I've run into some puzzling behavior. I've tried to distill it to a minimal case. In the final SELECT query below, the last LEFT JOIN clause seems have the effect of an INNER JOIN in that its condition limits the rows returned. I can rewrite the query to get the desired result using a UNION