Can someone explain these outputs for me?

[Balaji Ramanathan]

I am not sure what exactly is going on here.  When an expression starts
with a string and I try to append the results of a mathematical operation
to it, sometimes it works, sometimes, it doesn't, and I can't find the
pattern behind what works and what doesn't.

1.    Simple mathematical expression by itself works
SQLite> select cast(-1.5 as integer)*-1
1

2.    Adding a string in front of a simple cast by itself works
SQLite> select '-  '||cast(-1.5 as integer)
-  -1

3.    When there is a mathematical expression after the string, I get a 0.
My string is nowhere to be seen in the output
SQLite> select '-  '||cast(-1.5 as integer)*-1
0

4.    But when I add 1 instead of multiplying, it produces output that
seems to evaluate everything before the addition to zero
SQLite> select '- '||cast(-1.5 as integer)+1
1

5.    Enclosing the mathematical expression in a printf produces the
correct output
SQLite> select '- '|| printf(cast(-1.5 as integer)*-1)
-  1

6.    If the output starts with a number, then it doesn't seem to matter
what follows.  Notice that the last part of the expression below is the
same as the expression in query number 3 above, but it works fine now
whereas previously it produced a zero as the output
SQLite> select cast(1.5 as integer)||'-'||(cast(-1.5 as integer)*-1)
1-1

I am sure it has something to do with order of operations and the affinity
of the operands, but can someone give me a summary that I can understand
readily?  The only mentions of the "||" operator on the SQLite website (
https://sqlite.org/lang_expr.html) don't really explain what is going on in
the above examples.

Any help would be much appreciated.  Thank you.

Balaji Ramanathan
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[Igor Tandetnik-2]

On 11/11/2017 8:55 PM, Balaji Ramanathan wrote:
> 3.    When there is a mathematical expression after the string, I get a 0.
> My string is nowhere to be seen in the output
> SQLite> select '-  '||cast(-1.5 as integer)*-1
> 0

|| has the highest precedence. Your expression is interpreted as ( '-  '||cast(-1.5 as integer) ) * -1 . The string produced by the stuff in parentheses doesn't look like a valid number, and so becomes 0 when coerced to the same. Basically, you are doing

select 'foobar' * -1

> 4.    But when I add 1 instead of multiplying, it produces output that
> seems to evaluate everything before the addition to zero
> SQLite> select '- '||cast(-1.5 as integer)+1
> 1

0 * -1 == 0
0 + 1 == 1

> 5.    Enclosing the mathematical expression in a printf produces the
> correct output
> SQLite> select '- '|| printf(cast(-1.5 as integer)*-1)
> -  1

So would enclosing in parentheses. The point is not printf() call, but changing the order of evaluation.

> 6.    If the output starts with a number, then it doesn't seem to matter
> what follows.  Notice that the last part of the expression below is the
> same as the expression in query number 3 above, but it works fine now
> whereas previously it produced a zero as the output
> SQLite> select cast(1.5 as integer)||'-'||(cast(-1.5 as integer)*-1)
> 1-1

The last part is parenthesized here, whereas it wasn't in prior examples. That makes all the difference.

> I am sure it has something to do with order of operations and the affinity
> of the operands, but can someone give me a summary that I can understand
> readily?  The only mentions of the "||" operator on the SQLite website (
> https://sqlite.org/lang_expr.html) don't really explain what is going on in
> the above examples.

The part of the article you quote that you seem to overlook is "in order from highest to lowest precedence"
--
Igor Tandetnik


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users