Re: [sqlite] sqlite 3.31.1 crashes in SVN on OpenBSD/sparc64

2020-03-11 Thread Stefan Sperling
On Wed, Mar 11, 2020 at 08:20:01AM -0400, Richard Hipp wrote: > On 3/11/20, Stefan Sperling wrote: > > > > Does this help? > > > > Studying the trace output makes me think this is the same problem that > came up on the s390 hardware and was fixed here: >

Re: [sqlite] COALESCE() does not short-circuit sometimes

2020-03-11 Thread Jay Kreibich
> On Mar 11, 2020, at 2:16 PM, Justin Ng wrote: > > They generally do short-circuit but there are edge cases where they don't. It > isn't entirely intuitive to me what the conditions are, though. > "ABS(-9223372036854775808)" is a constant expression, and as such, it makes sense that it is

Re: [sqlite] [EXTERNAL] Re: COALESCE() does not short-circuit sometimes

2020-03-11 Thread Justin Ng
> I have a patch to fix the problem on a branch > (https://www.sqlite.org/src/timeline?r=do-not-factor-functions) which > you can experiment with. More changes and analysis are needed prior to > landing on trunk. I cannot guarantee that such a landing will in fact > occur, though it seems more

Re: [sqlite] COALESCE() does not short-circuit sometimes

2020-03-11 Thread Justin Ng
> It is not a "hack" because it does not work. It is what is called a "failed > attempt" at a hack. Yeah, the ABS() hack does not work. Which led me to use `(SELECT SUM(9223372036854775807) FROM (SELECT NULL UNION ALL SELECT NULL))` which **does** work. > However, your assessment that

Re: [sqlite] COALESCE() does not short-circuit sometimes

2020-03-11 Thread Justin Ng
> It is not a "hack" because it does not work. It is what is called a "failed > attempt" at a hack. Yeah, the ABS() hack does not work. Which led me to use `(SELECT SUM(9223372036854775807) FROM (SELECT NULL UNION ALL SELECT NULL))` which **does** work. > However, your assessment that

Re: [sqlite] [EXTERNAL] Re: COALESCE() does not short-circuit sometimes

2020-03-11 Thread sky5walk
I recently tried using Coalesce() to check existence of a column name. SELECT DISTINCT COALESCE((SELECT 'AColumnThatDoesNotExist' FROM TD), -999) FROM TD; But, the SQL error code dominates? "no such column: AColumnThatDoesNotExist" Had to resort to: SELECT name FROM pragma_table_info('TD') WHERE

Re: [sqlite] [EXTERNAL] Re: COALESCE() does not short-circuit sometimes

2020-03-11 Thread Richard Hipp
On 3/11/20, Hick Gunter wrote: > While ORACLE does state that COALESCE will short circuit, A similar problem was reported on a ticket here: https://www.sqlite.org/src/tktview?name=3c9eadd2a6 (The problem reported on that ticket might not seem to be the same at first glance, but deep down they

Re: [sqlite] [EXTERNAL] Re: COALESCE() does not short-circuit sometimes

2020-03-11 Thread Hick Gunter
While ORACLE does state that COALESCE will short circuit, SQLite does not. May I suggest implementing your own user defined function to do this instead. void THROW_IF_NULL( sqlite3_context *ctx, int argc, sqlite3_value**argv) { int ii; for( ii == 0; ii < argc; ii++) {

Re: [sqlite] COALESCE() does not short-circuit sometimes

2020-03-11 Thread Keith Medcalf
On Wednesday, 11 March, 2020 09:24, Justin Ng wrote: >Sometimes, when querying data, rather than letting NULLs propagate, >it might be better to throw an error if NULLs are not expected from an >expression. >The presence of NULLs might indicate an error in logic, data, or both. Yes, it very

Re: [sqlite] COALESCE() does not short-circuit sometimes

2020-03-11 Thread Justin Ng
> Why do you think that that it should not evaluate ABS? It is there and you > asked for it. I believe it's a good idea to say, "hey, the amount you placed > here is out of boundary, think about what you are doing here." IMO, of > course. Thanks. > > josé Sometimes, when querying data,

Re: [sqlite] [EXTERNAL] Re: COALESCE() does not short-circuit sometimes

2020-03-11 Thread Hick Gunter
It is possible to infer, from the EXPLAIN output, that the SQLite program generator attempts to isolate constant expressions and evaluates them first, before it enters the COALESCE loop. From my experience in reading SQL Programs, the general structure is GOTO INIT START: - load constant

Re: [sqlite] COALESCE() does not short-circuit sometimes

2020-03-11 Thread Jose Isaias Cabrera
Justin Ng, on Wednesday, March 11, 2020 07:03 AM, wrote... > > This happens in SQLite 3.28 and 3.31. > > Consider the following queries, > > -- Query 1 > SELECT > COALESCE( > NULL, > (SELECT SUM(9223372036854775807) FROM (SELECT NULL UNION ALL SELECT > NULL)) > ); On 3.24.0, this worked

Re: [sqlite] sqlite 3.31.1 crashes in SVN on OpenBSD/sparc64

2020-03-11 Thread Richard Hipp
On 3/11/20, Stefan Sperling wrote: > > Does this help? > Studying the trace output makes me think this is the same problem that came up on the s390 hardware and was fixed here: https://www.sqlite.org/src/info/04885763c4cd00cb Stefan: Can you verify that the patch above fixes the problem? If

Re: [sqlite] COALESCE() does not short-circuit sometimes

2020-03-11 Thread Dominique Devienne
On Wed, Mar 11, 2020 at 12:03 PM Justin Ng wrote: > -- Query 3 > SELECT > COALESCE( > (SELECT 'hello'), > ABS(-9223372036854775808) > ); > [...]. It should short-circuit and not evaluate ABS() Interestingly, found this as well: https://github.com/AnyhowStep/tsql/issues/233 SQLite

Re: [sqlite] sqlite 3.31.1 crashes in SVN on OpenBSD/sparc64

2020-03-11 Thread Richard Hipp
On 3/11/20, Stefan Sperling wrote: > > Does this help? > It does help some. But I need a good copy of the database schema, which I don't have. Please bring up the database file in the "sqlite3" command-line shell, then enter the command ".fullschema". Save off the results and send them to me,

[sqlite] COALESCE() does not short-circuit sometimes

2020-03-11 Thread Justin Ng
This happens in SQLite 3.28 and 3.31. Consider the following queries, -- Query 1 SELECT COALESCE( NULL, (SELECT SUM(9223372036854775807) FROM (SELECT NULL UNION ALL SELECT NULL)) ); -- Query 2 SELECT COALESCE( (SELECT 'hello'), (SELECT SUM(9223372036854775807) FROM (SELECT

Re: [sqlite] sqlite 3.31.1 crashes in SVN on OpenBSD/sparc64

2020-03-11 Thread Stefan Sperling
On Tue, Mar 10, 2020 at 06:42:18PM +0100, Stefan Sperling wrote: > On Tue, Mar 10, 2020 at 01:21:34PM -0400, Richard Hipp wrote: > > Set a breakpoint on the sqlite3VdbeExec() function that fires when the > > statement that is crashing is first executed. (You can determine that > > it is the

[sqlite] Extracting metadata about generated columns with SQL only?

2020-03-11 Thread Justin Ng
SQLite 3.31 introduced generated columns. However, pragma table_xinfo() does not seem to give you the parenthesized expression of a generated column. Is there a version of "dflt_value" (in table_xinfo()) for generated columns? How would I access that? Also, does the "hidden" column (in

Re: [sqlite] Sqlite error code 14 when using 3.31.0+

2020-03-11 Thread Daniel Polski
Den 2020-03-10 kl. 12:31, skrev Daniel Polski: Den 2020-03-10 kl. 02:33, skrev Rowan Worth: On Mon, 9 Mar 2020 at 23:22, Daniel Polski wrote: Updated to 3.31.1 but my application started spitting out an error when opening the database, so I tested some earlier sqlite versions to figure