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 <i...@tandetnik.org> > To: sqlite-users@mailinglists.sqlite.org > 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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
