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

[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, 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.

So, the COALESCE(x, ABS(-9223372036854775808)) construct is a hack to 
sanity-check queries.

Something like THROW_IF_NULL(x)
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[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 table_xinfo()) with a value of "2" always 
mean the column is a generated column?

It looks like I'd have to do SELECT sql FROM sqlite_master; and parse the SQL 
string to get information about generated columns but this sounds like a 
terrible and error-prone idea; I'd like to avoid it, if possible.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[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 COALESCE() does not short-circuit sometimes #233
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[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 correct statement by looking at the value of p->zSql.)  Then
> > do:
> > 
> >  set p->db->flags = p->db->flags | ((0x060)<<32)
> > 
> > That will turn on bytecode listing and tracing, and might provide
> > further clues.  Please record and send in the trace.
> 
> I will look into this. Thanks!

Does this help?

$ egdb --args svn up<
GNU gdb (GDB) 7.12.1
Copyright (C) 2017 Free Software Foundation, Inc.
License GPLv3+: GNU GPL version 3 or later 
This is free software: you are free to change and redistribute it.
There is NO WARRANTY, to the extent permitted by law.  Type "show copying"
and "show warranty" for details.
This GDB was configured as "sparc64-unknown-openbsd6.6".
Type "show configuration" for configuration details.
For bug reporting instructions, please see:
.
Find the GDB manual and other documentation resources online at:
.
For help, type "help".
Type "apropos word" to search for commands related to "word"...
Reading symbols from svn...done.
(gdb) br sqlite3.c:83210 if p->zSql && strncmp(p->zSql, "INSERT OR REPLACE 
INTO", 22) == 0
No source file named sqlite3.c.
Make breakpoint pending on future shared library load? (y or [n]) y
Breakpoint 1 (sqlite3.c:83210 if p->zSql && strncmp(p->zSql, "INSERT OR REPLACE 
INTO", 22) == 0) pending.
(gdb) run
Starting program: /usr/local/bin/svn up
Updating '.':

Breakpoint 1, sqlite3Step (p=0xd69cb26608) at sqlite3.c:83210
83210   rc = sqlite3VdbeExec(p);
(gdb) set p->db->flags = p->db->flags | (((unsigned long long)0x060)<<32)
(gdb) p /x p->db->flags
$1 = 0x60e004a0e0
(gdb) c
Continuing.
SQL: [INSERT OR REPLACE INTO nodes (   wc_id, local_relpath, op_depth, 
parent_relpath, repos_id, repos_path,   revision, presence, depth, kind, 
changed_revision, changed_date,   changed_author, checksum, properties, 
translated_size, last_mod_time,   dav_cache, symlink_target, file_external, 
moved_to, moved_here,   inherited_props) VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7, 
?8, ?9, ?10, ?11, ?12, ?13, ?14, ?15, ?16, ?17, ?18, ?19, ?20, ?21, 
?22, ?23) ]
VDBE Program Listing:
   0 Init 0  1400   00 Start at 140
   1 OpenWrite0   160 2300 root=16 iDb=0; NODES
   2 OpenWrite1   290 k(4)  00 root=29 iDb=0; I_NODES_MOVED
   3 OpenWrite2   270 k(5,) 00 root=27 iDb=0; I_NODES_PARENT
   4 OpenWrite3   170 k(3,,,)   00 root=17 iDb=0; 
sqlite_autoindex_NODES_1
   5 Variable 120 ?100 r[2]=parameter(1,?1)
   6 Variable 230 ?200 r[3]=parameter(2,?2)
   7 Variable 340 ?300 r[4]=parameter(3,?3)
   8 Variable 450 ?400 r[5]=parameter(4,?4)
   9 Variable 560 ?500 r[6]=parameter(5,?5)
  10 Variable 670 ?600 r[7]=parameter(6,?6)
  11 Variable 780 ?700 r[8]=parameter(7,?7)
  12 Variable 890 ?800 r[9]=parameter(8,?8)
  13 Variable22   100 ?22   00 r[10]=parameter(22,?22)
  14 Variable21   110 ?21   00 r[11]=parameter(21,?21)
  15 Variable10   120 ?10   00 r[12]=parameter(10,?10)
  16 Variable15   130 ?15   00 r[13]=parameter(15,?15)
  17 Variable 9   140 ?900 r[14]=parameter(9,?9)
  18 Variable14   150 ?14   00 r[15]=parameter(14,?14)
  19 Variable19   160 ?19   00 r[16]=parameter(19,?19)
  20 Variable11   170 ?11   00 r[17]=parameter(11,?11)
  21 Variable12   180 ?12   00 r[18]=parameter(12,?12)
  22 Variable13   190 ?13   00 r[19]=parameter(13,?13)
  23 Variable16   200 ?16   00 r[20]=parameter(16,?16)
  24 Variable17   210 ?17   00 r[21]=parameter(17,?17)
  25 Variable18   220 ?18   00 r[22]=parameter(18,?18)
  26 Variable20   230 ?20   00 r[23]=parameter(20,?20)
  27 Variable23   240 ?23   00 r[24]=parameter(23,?23)
  28 NewRowid 010   00 r[1]=rowid
  29 HaltIfNull129922 NODES.wc_id   01 if r[2]=null halt
  30 HaltIfNull129923 NODES.local_relpath 01 if r[3]=null halt
  31 HaltIfNull129924 NODES.op_depth 01 if r[4]=null halt
  32 HaltIfNull129929 NODES.presence 01 if r[9]=null halt
  33 HaltIfNull12992   12 

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

[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 correctly:
 8:32:20.56>sqlite3
SQLite version 3.24.0 2018-06-04 19:24:41
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> SELECT
   ...>   COALESCE(
   ...> NULL,
   ...> (SELECT SUM(9223372036854775807) FROM (SELECT NULL UNION ALL SELECT 
NULL))
   ...>   );
Error: integer overflow


> -- Query 2
> SELECT
>   COALESCE(
> (SELECT 'hello'),
> (SELECT SUM(9223372036854775807) FROM (SELECT NULL UNION ALL SELECT
> NULL))
>   );

Also, on 3.24.0. this worked correctly:
sqlite> SELECT
   ...>   COALESCE(
   ...> (SELECT 'hello'),
   ...> (SELECT SUM(9223372036854775807) FROM (SELECT NULL UNION ALL SELECT 
NULL))
   ...>   );
hello

> -- Query 3
> SELECT
>   COALESCE(
> (SELECT 'hello'),
> ABS(-9223372036854775808)
>   );

This one, above, was bad at 3.24.0 also.
sqlite> SELECT
   ...>   COALESCE(
   ...> (SELECT 'hello'),
   ...> ABS(-9223372036854775808)
   ...>   );
Error: integer overflow

> Query 1 should throw `integer overflow`.
> Query 2 should return 'hello'.
> Query 3 should return 'hello'.
>
> However, Query 3 throws `integer overflow`. It should short-circuit and
> not evaluate ABS()

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é
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[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 values
- load paramters
- open tables
LOOP:
- create next result row
IF end-of-results THEN EXIT ELSE GOTO LOOP
INIT:
- check schema version
- take required locks
- evaluate constant expressions
GOTO START

Which is quite nifty, as it allows stuff unknown until the end of parsing to be 
appended to the program

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Dominique Devienne
Gesendet: Mittwoch, 11. März 2020 13:19
An: SQLite mailing list 
Betreff: [EXTERNAL] Re: [sqlite] COALESCE() does not short-circuit sometimes

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 COALESCE() does not short-circuit sometimes #233 
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___
 Gunter Hick | Software Engineer | Scientific Games International GmbH | 
Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 
1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[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
you want, you can click on the "Tarball" link to get a complete
tarball of check-in 04885763c4cd00cb (which, by coincidence was the
first check-in *after* the 3.31.1 release), unpack the tarball, then
do "./configure && make sqlite3.c" to generate the "sqlitle3.c" and
"sqlite3.h" files.  Or, you can update the "sqlite3.c" file you are
currently using manually entering the 3-line patch shown.

Please let us know what you find.

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

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

[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

out when the problem starts.
I don't get the message in versions <= 3.30.1.

(from the applications log)
SQLite Version: 3.31.0
INFO: Database opened: /tmp/database.sqlite
WARNING: SQLITE error code: 14 cannot open file at line 36982 of
[3bfa9cc97d]
WARNING: SQLITE error code: 14 os_unix.c:36982: (40) 
openDirectory(/tmp) -



errno 40 is ELOOP, "Too many symbolic links encountered". open(2) says:

    ELOOP  Too many symbolic links were encountered in resolving
  pathname,  or  O_NOFOLLOW  was
   specified but pathname was a symbolic link.

Is your /tmp/ a symlink? Sqlite seems to use O_NOFOLLOW 
unconditionally in

openDirectory() since this checkin:

https://www.sqlite.org/src/info/6a64fb6a2da6c98f


/tmp/ is a directly mounted ramdisk.



Sorry I was wrong.

/tmp/ actually IS a symlink to /var/volatile/tmp

Best regards,


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

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

[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 NULL UNION ALL SELECT NULL))
  );

-- Query 3
SELECT
  COALESCE(
(SELECT 'hello'),
ABS(-9223372036854775808)
  );


Query 1 should throw `integer overflow`.
Query 2 should return 'hello'.
Query 3 should return 'hello'.

However, Query 3 throws `integer overflow`. It should short-circuit and not 
evaluate ABS()
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[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, please.

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

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

[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++)
{
if (sqlite3_value_type(argv[ii]) != SQLITE_NULL)
{
sqlite3_result_value(ctx,argv[ii]));
return;
}
}

sqlite3_result_error(ctx,"NULL or empty", SQLITE_TRANSIENT);
}

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Justin Ng
Gesendet: Mittwoch, 11. März 2020 16:24
An: sqlite-users@mailinglists.sqlite.org
Cc: 
vi1p195mb06545f03a24d50dd2785ac2bde...@vi1p195mb0654.eurp195.prod.outlook.com
Betreff: [EXTERNAL] Re: [sqlite] COALESCE() does not short-circuit sometimes

> 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, 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.

So, the COALESCE(x, ABS(-9223372036854775808)) construct is a hack to 
sanity-check queries.

Something like THROW_IF_NULL(x)
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___
 Gunter Hick | Software Engineer | Scientific Games International GmbH | 
Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 
1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[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 well might.  I believe there is a term for a programmer that uses 
sentinels and then forgets to handle them.  There is even a diagnostic code for 
it.  Some people offend themselves when they see the code, however, because it 
lets their secret out of the bag.


>So, the COALESCE(x, ABS(-9223372036854775808)) construct is a hack to
>sanity-check queries.

It is not a "hack" because it does not work.  It is what is called a "failed 
attempt" at a hack.

However, your assessment that coalesce is not "short-circuiting" is incorrect.  
If x is not null, then x is returned and the constant expression is not 
returned.  If the constant expression were a correlated expression, then it 
would not be evaluated in the candidate context.  If x were null and the 
expression was not constant (and thus required evaluation) then it would be 
evaluated within the current context and its result returned.

The documentation DOES NOT say that the arguments to coalesce will not be 
evaluated, merely that the first one that is not-null will be returned.

https://sqlite.org/lang_corefunc.html#coalesce

>Something like THROW_IF_NULL(x)

You could certainly write a function that did that.  It would certainly be 
better than a "hack" that does not even work.

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




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

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

[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 evaluate during the parse/prepare phase of the processing, not 
the execution.  There are similar problems in more traditional languages 
(especially scripting languages) that attempt to optimize out or pre-compute 
constant expressions.

If that’s the case, then the issue is not so much that the COALESCE() is 
failing to short-circuit, but rather than the SQL statement failing to 
“compiling” an invalid statement.

If you’re doing this in code as separate prepare/step/finalize, it would be 
interesting to see where it fails.  My guess is prepare, not step.

  -j

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

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

[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:
> https://www.sqlite.org/src/info/04885763c4cd00cb

Yes, this fixes it. Thank you!

I will get this patch merged into the OpenBSD port of sqlite.
I suppose once the next sqlite release comes around we will be able to
drop the patch again since it will already be included in the release.

Thanks a lot for your help :)
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[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 coalesce is not "short-circuiting" is incorrect.
> If x is not null, then x is returned and the constant expression is not 
> returned.
> If the constant expression were a correlated expression, then it would not be 
> evaluated in the candidate context.
> If x were null and the expression was not constant (and thus required 
> evaluation)
> then it would be evaluated within the current context and its result returned.
>
> The documentation DOES NOT say that the arguments to coalesce will not be 
> evaluated, merely that the first one that is not-null will be returned.
>
> https://sqlite.org/lang_corefunc.html#coalesce

You're right that the documentation doesn't say so.

I did assume COALESCE() would always short-circuit because the SQL standard 
says COALESCE() is
equivalent to CASE expressions.

And I was pretty sure CASE expressions should always short-circuit.

However, turns out, CASE expressions don't always short-circuit, either. 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.

So, I guess I learned something new today.

> > Something like THROW_IF_NULL(x)
>
> You could certainly write a function that did that.  It would certainly be 
> better than a "hack" that does not even work.

I'd rather keep the hack that does work than use a user-defined function, if I 
can help it.

I feel like user-defined functions should be a last resort.

Thanks for your input, anyway.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[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 are both the same issue.)

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 likely than not at this point.

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

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

[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 name =
'AColumnThatDoesNotExist';
And oddly, pragma_table_info() fails below if the Table TD is unadorned?
SELECT name FROM pragma_table_info(TD) WHERE name =
'AColumnThatDoesNotExist';

On Wed, Mar 11, 2020 at 2:20 PM Richard Hipp  wrote:

> 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 are both the same issue.)
>
> 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 likely than not at this point.
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[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 likely than not at this point.

I remember reporting a bug about COALESCE((... LIMIT 0), x) and you fixed it 
the very next day.

I forgot to thank you for that!

Thanks for the quick work, as always!

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

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

[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 coalesce is not "short-circuiting" is incorrect.
> If x is not null, then x is returned and the constant expression is not 
> returned.
> If the constant expression were a correlated expression, then it would not be 
> evaluated in the candidate context.
> If x were null and the expression was not constant (and thus required 
> evaluation)
> then it would be evaluated within the current context and its result returned.
>
> The documentation DOES NOT say that the arguments to coalesce will not be 
> evaluated, merely that the first one that is not-null will be returned.
>
> https://sqlite.org/lang_corefunc.html#coalesce

You're right that the documentation doesn't say so.

I did assume COALESCE() would always short-circuit because the SQL standard 
says COALESCE() is
equivalent to CASE expressions.

And I was pretty sure CASE expressions should always short-circuit.

However, turns out, CASE expressions don't always short-circuit, either. 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.

So, I guess I learned something new today.

> > Something like THROW_IF_NULL(x)
>
> You could certainly write a function that did that.  It would certainly be 
> better than a "hack" that does not even work.

I'd rather keep the hack that does work than use a user-defined function, if I 
can help it.

I feel like user-defined functions should be a last resort.

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