Re: sqlite-users Digest, Vol 119, Issue 12

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
1 message Options
Reply | Threaded
Open this post in threaded view
|

Re: sqlite-users Digest, Vol 119, Issue 12

Balaji Ramanathan
Thank you very much, Igor.  I knew there would be a simple explanation, but
I was focused on operand affinities instead of order of operations.  So, I
have to be careful about combining mathematical expressions with the
concatenation operator.  I am so used to thinking of exponentiation,
multiplication and division having higher precedence than anything else in
an expression (PEMDAS) that I got blind-sided by the fact that
concatenation has even higher precedence than those.  I will use
parentheses more liberally in the future to specify exactly what I want.
Thank you again.

Balaji Ramanathan

From: Igor Tandetnik <[hidden email]>

> To: [hidden email]
> Cc:
> Bcc:
> Date: Sat, 11 Nov 2017 21:17:24 -0500
> Subject: Re: [sqlite] Can someone explain these outputs for me?
> 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