Using sqlplus and APEX SQL Commands with a 19.3.0.0.0 version database I was just playing around with simple expressions to make sure I understand operator precedence and implicit conversion and ran into this case that I cannot understand.
Why does
select 8 || 1 + 2 * 7 || 9 from dual;
return 959
but
select concat(8, 1 + 2 * 7) || 9 from dual;
returns 8159, which is the answer I would expect given my understanding of what I read in the SQL Language Reference.
also
select 8 || ( 1 + 2 * 7 ) || 9 from dual;
returns 8159
I tried the same on Live SQL and got the same results.
Regards and thanks for your time.
-John
From the SQL reference, precedence is:
So for 8 || 1 + 2 * 7 || 9
multiplication comes first, so:
8 || 1 + 14 || 9
But now we have an interesting conflict... All operators are now at equal precedence, but we have a data type issue, ie, do we convert to strings first so the concatenation will be allowed, or do we convert to numbers to allow the addition to occur.
The documentation keeps this nice and obtuse :-)
"Implicit Data Conversion
Oracle Database automatically converts a value from one data type to another when such a conversion makes sense.
Table 2-8 is a matrix of Oracle implicit conversions. The table shows all possible conversions, without regard to the direction of the conversion or the context in which it is made"
Your observation suggests we simply just go left to right, so: 8 || 1 becomes to_char(8) || to_char(1) giving '81'
Then '81' cannot be added to 14 (mismatch data types), so then its to_number(81)+14, giving 95
Numbers cannot be concatenated, so then its to_char(95) || to_char(9)