Fail to calculate long expression
(1) By Domingo (mingodad) on 2021-08-02 16:17:29 [link] [source]
After playing a bit with https://baturin.org/tools/bnfgen/ and creating a simplified text generator for a simple expression grammar I've got a long expression that PostgreSQL, Lua, Ruby, Python and Javascript can calculate but sqlite can't:
=====
select ( ((50.266))/2%((2816.31)/(996.7/8175.445-59.546)/((47))*((((93.614))+(((8*(19.6093/28))))))/(79.577-(178/82.7%(2020.26)-271.497%(7.495)*22.42/(892.4036)+((22)+(51.50-(((35%6062-25)-4.264))+5508/409.98)+(((((327.5845/(5296))-(6290.8)))%4+13.719)+146))*((((8877.8)+(5160)%3760-7058)%((62))))%93/(90.24%((993))+(9427)%303.97%(((((8))))/(((699))*(((25/1379.1))%9257.3-(5464+(30)-(7/((9.8))/3.655%9.3)))))-(27)%168-86-((((5.7326)))-((3412+(((11.8717))))*(75)+(899.1)*((541.7/595.1086+22.855+(9.8034+9.412)-(((7+322.7)))-7426*((18)-3782.5*67.144-2+((3.8)%((377)*8)/((56.9)))))))/((21)*664%(57.6974+9139.586+(((((336.4626))))+(((60-((((956)+(4.870+((5418))*(6/57)*(((1.4547+((457.1461)/((3066.6)*7156.5)+(3632)*((47.2*748%(((50.7320))))/(84.90))-6712.4084+(((2)))-69.11+(841+(22.6/925))%((492.43)*468)%93*(751)-(6%(12)%(((937.3850))*((11*((((6.410%7872))))*((5.1563/(7558%(8.16)+370+((((9064.16+4601%934.7722)%34.3146*2716.7-(42*((((21.67/(1115.3)%((68/((50.2))+(1.839*7))))))+639.1))/((8398.1)))%3222.93))%4655.2-28.19%((5896.9237%2/9987.49/((1*5)-(685)-2)%6-1+30.5884*839)))))%766%9681.9688*292.40+958.57*173%1426.305*(5.6*((8959)%57)*66/(((2.135))))*(((2661*43.8865+6.2-(5.87))))*(99.4726*23.254)+1+(((((1048/(60.87+(17)-8729)))/((8975))-762-855)))/(((((8168.14-82+(((10)*(((2762.1006))*(9094.95))-(733.7+(((694.5*83)-((85))*((7.2925)))))*30-((138))%330)))))))))-(((6082))+((6796))/85%1)))*(250))))/((3587.722)*(8-(16))-(503.2186)-((1))-((75.1284))%9984.3663*((875.1*43))-((((1)-28/((((9219.5261)))-3/111.874-((14.3*((566.57)+(932.3314))))*(63.8342)+568.681%(6)%(913)+(((156.28)+(9.7-(487.60))))%5514.738)%906.80))/3182+2.732-3307.9)*54%152-(((416.67-(22.7231)-5.2649)))*2))+8.1831+40.7/(99)-((((((((299.78/5.2*(3061.5-8451-((253.405+1-8.9*(((863.2/3202))-(4393*(44.1))/(8490)/(2-(22.535)*(((3.89)+4340.7750)))%6.2/204)/6653.5745/88+(3.8124)%952+(970.4977)%6438.6868/((9669.987)-15.8964)/(((7257)%(422.6)*(7.84-(2499.786))%(1)/9734.918%18/((((71)/(9606.6))%(7423/389.9548*(((3.98+3.3/((((((349)))))))%65)))))%(5402.73)*9.51*158.9-(524.26)%(3144)/(((1.300+4606+1.16/(6)))%(6.871+(2204))))-(2.7)%3014)))+8)+(6663)))%608.3)/4.404-(99)*520-568.526%5.9%11.811%61+(24)-((15+(25)-(5)*831+(((732)))%(9.226))*(851)/1.3%(1000*((449.360)+5055)-(332))-(396)-((310.809)))+(((7)-((50.9)/4325))-(38)+((297)+(5*9)/(8741-(((((27)-8843.11*(6/(((((908.3612))))+7-7+29)/3437.6*5166))-13.226)))/((((((21.5))))*(949.1150)%(((617%29%(((((1.5)/4560-7294/3078.7819*81+((7730))%1995/77.1%1156.44)-((4037*(1197.8/38.732)/(405.24+((479.1714%308.82)%2622.3+((7383.2244))-20.36*(6.31)%3336/9-4.643+5.909/66*(61.97)*6*(665.78)))*((9)))))/(6533)))*(13.9065)/38+(21))))-8.37-67.63))))+((((7)))*3150)*((812.7*((1.6*((64)))))+7656.380)%((((79.5592/(591))%(57%((48.8)-22/6.8))-7.444)%636%6/((6.68)*1)-(7.626/58.87)*(96%(8795*5585.1%7+1627.6/(6224)+6547%817*((3))/(((681.55)-494*(3.39)))*60)))))+9.26-(((54)))%((43.7689)/(6.4))/6103.18)-((9))/651.6%845.26))))-(5)+1%65/84.495+72.23-((((((634.1878)-293.77)+4))))/((67)))-411*(600))))-(2635))+370)))%(((4690.6))*((((3153.925%(832)))))/722+4830.750))*8564%9%(((88.6362*7924*(((((3727.652)))-79.9))/26)*67.360%(9888))))*53%(2203.7535)/7266+62.18/5.1894)-((((((7))%(((645.7678))))))*74))-4.51-460.4*825.986*5179.936-(9)*166*(927.8/90.45)-(56.1352)%567.47+((((10.4043*((31.7)+225.62-((3.8891*(1.4))))-8+((8532.7+(293.1/53.38/((1.1))+511.93+((93))-97%2035+3+(((3853.25)))+1000%(((((8.948)))*((1799.8993))-6.889*((((28.1))%1.3%17.1242-1846.940/4-(70)/((2.6%(((5.248)%(7841)))+1673%(5)%((((((1265)))*(814.7882)%7/1391+(33.875)/((7.5)))/92.96%(((533.1%(3035)/(((((4.6*(5832)+(72)-69.53*1)%((8))-((4))/64)))*95.46)-(335*4))))*(650))+(6)*70.520%6085.6723)-(61.91)/34-267*(45.3948-((8.2)))-(1/((10-243.770%262.83))%2.60%(55%6))%((745.82+((61.21)-27)-(52.2704)%8041-(((6)))%(7.181+9179)/25+143))+13+(96.297/84.21*((6879.4925)))%21%93.155+341.1+1230+(4)*(1.29)%((5829.8+(4)*4659.98*37))+(((7)))+6318.468*932/((75.8))/694+(3)%((19.9)/43/1846.887+3.5-(99.70)*4406+3615%(888.58+14/(7762%138.3/5642))/40.5-819-(73.2184)*(29.404-((3+5.129))%2489)%4874+5)-(1252)+(796.6401)-7.7461%((((127.3)))-7207+825.80)+((3756))%8770.816-((2.1911%4536.4))*(62.681)%9687.8*923.246+(94.834)-1164%(9)/(75.59)))%((960.4786)-4577.41)%9741.4/(4)-578.66-511/2+90.120))*345.3344+((4769.7))))%151))))))+98-32.1677-547/8)*5)-417%(962.3)/557.431%275+90)%(((2174.78)))-232-1-((233.40)%(4502)%(((7)))/415)) ) as expr;
=====
SQLite output:
=====
=====
PostgreSQL and Lua, Ruby, Javascript and Python:
=====
25.133
=====
Here is a simplified C generator:
=====
#include <stdio.h>
#include <stdlib.h>
#include <time.h>
static int xrandom(int low, int up) {
double r = (double)rand() * (1.0 / ((double)RAND_MAX + 1.0));
r *= (double)(up - low) + 1.0;
return ((int)r + low);
}
void expr_nzdigit() {
switch(xrandom(1,9)) {
case 1: printf("1"); break;
case 2: printf("2"); break;
case 3: printf("3"); break;
case 4: printf("4"); break;
case 5: printf("5"); break;
case 6: printf("6"); break;
case 7: printf("7"); break;
case 8: printf("8"); break;
case 9: printf("9"); break;
}
}
void expr_digit() {
switch(xrandom(0,9)) {
case 0: printf("0"); break;
case 1: printf("1"); break;
case 2: printf("2"); break;
case 3: printf("3"); break;
case 4: printf("4"); break;
case 5: printf("5"); break;
case 6: printf("6"); break;
case 7: printf("7"); break;
case 8: printf("8"); break;
case 9: printf("9"); break;
}
}
void expr_integer() {
expr_nzdigit();
int imax = xrandom(0,3);
for(int i=0; i < imax; ++i) expr_digit();
}
void expr_float() {
expr_integer();
printf(".");
expr_integer();
}
void expr_number() {
switch(xrandom(0,1)) {
case 0: expr_integer(); break;
case 1: expr_float(); break;
}
}
void expr_operations() {
switch(xrandom(0,4)) {
case 0: printf("+"); break;
case 1: printf("-"); break;
case 2: printf("*"); break;
case 3: printf("/"); break;
case 4: printf("%%"); break;
}
}
void expr_expr() {
switch(xrandom(0,2)) {
case 0: expr_expr(); expr_operations(); expr_expr(); break;
case 1: printf("("); expr_expr(); printf(")"); break;
case 2: expr_number(); break;
}
}
void expr_start() {
srand(clock());
expr_expr();
printf("\n");
}
int main(int argc, char *argv[]) {
expr_start();
return 0;
}
=====
For this grammar:
=====
<start> ::= <expr> ;
<expr> ::=
<expr> <operations> <expr>
| "(" <expr> ")"
| <number>
;
<operations> ::=
"+" | "-" | "*" | "/" | "%"
;
<nzdigit> ::= "1" | "2" | "3" | "4" | "5" | "6" | "7" | "8" | "9" ;
<digit> ::= "0" | <nzdigit> ;
<integer> ::= <nzdigit> <digit>{1, 3};
<float> ::= <nzdigit>{1, 3} "." <digit>{1, 3};
<number> ::= <integer> | <float>;
=====
(2) By Simon Slavin (slavin) on 2021-08-02 17:45:31 in reply to 1 [link] [source]
Verified in the CLI for SQLite version 3.36.0 2021-06-18 18:58:49 .
I removed some of that expression and it was still failing. Can you minimise it to the point where removing just one term, or one pair of brackets makes it succeed ?
(3) By RandomCoder on 2021-08-02 19:03:56 in reply to 2 [link] [source]
I'm not the OP, but, long story short: I'm pretty sure the issue boils down to how SQLite handles modulus operations on floating point values:
In Python (and a other languages I know of that allow % on a floating point value):
>>> 0.1 % 2.0
0.1
>>> 1.0 / (0.1 % 2.0)
10.0
Where as, in SQLite:
sqlite> select 0.1 % 2.0;
0.0
sqlite> select 1.0 / (0.1 % 2.0);
sqlite>
(4) By Larry Brasfield (larrybr) on 2021-08-02 21:21:56 in reply to 1 [link] [source]
... can calculate but sqlite can't:
... select ([humongous expression]); ...
(This reinforces RandomCoder's point:)
It appears SQLite has no trouble calculating that expression. If I insert a "typeof" (sans quotes) between the select and the next left parenthesis, the result is 'null'. If you were to eliminate all subexpressions which evaluate to NaN (aka "not a number"), you would find that a number was calculated. SQLite treats NaNs as NULL, or you could think of it as Not-a-Value.
P.S. Please note the spelling of SQLite.
(5) By Aloys (aloys) on 2021-08-02 21:35:50 in reply to 1 [link] [source]
The reason that the computation fails is how SQLite handles the modulo operator %. The SQLite documentation describes the operator as follows:
The % operator casts both of its operands to type INTEGER and then computes the remainder after dividing the left integer by the right integer.
So, as soon as the right operand is smaller than | 1 | it is truncated to 0, and alas a division by zero takes place, resulting in a NULL value.
In your long expression you have the subexpression 617%29%(operand) = 617%29%(-0.0317193865612212). That is, the third operand in this partial expression will be truncated to 0, and you have 617%29%0 = 8%0 = NULL. And this forces the whole expresssion to become NULL.
(7) By anonymous on 2021-08-03 09:25:26 in reply to 5 [link] [source]
I fail to compute this.
617 mod 29 = 8
HP-41 617 ENTER 29 XEQ MODULO returns 8 HP 50G also returns 8
So remains 8 mod -0.0317193865612212 results in -2.50047999836·10^-2
Why is the resulted truncated? If it accepts the floating point number, it should return a floating point result if the modulo function is not limited to integers only.
(8) By Harald Hanche-Olsen (hanche) on 2021-08-03 10:45:45 in reply to 7 [link] [source]
Read it again. It's not the result that is truncated, it's the operands, before the modulo operation happens. So SQLite computes 8 mod 0, which becomes a NULL.
As to why it does that, it is probably a decision made such a long time ago nobody remembers anymore. It can't easily be changed now, for backwards compatibility. I suppose such a change could be made, with a suitable pragma to turn on the new behaviour, but then you had to make a strong case for that.
(9) By anonymous on 2021-08-03 11:12:04 in reply to 8 [link] [source]
Calculating 8 mod 0 is undefined. Shouldn't that be captured when the statement is parsed before it's executed?
P.S. I was confused by the zero result.
(10) By Aloys (aloys) on 2021-08-03 12:55:23 in reply to 9 [link] [source]
Calculating 8 mod 0 is undefined.
That's why SQLite returns a NULL value.
Shouldn't that be captured when the statement is parsed before it's executed?
How? The operand 0 is an intermediate result while executing resp calculating the long expression. The intermediate result for the right operand of the % operator is -0.0317193865612212. Since this value is not an integer, it is truncated to 0 by SQLite.
At least the SQL-99 standard does not define the operator % - I have not checked newer standard versions. Therefore the implementor decides how the operator works, and SQLite made the decision to cast the operands to integer.
(6) By Domingo (mingodad) on 2021-08-03 08:29:11 in reply to 1 [link] [source]
So here is the languages I tested so far: PostgreSQL, MYSQL, Lua, Python, Ruby, Javascript, D: Output 25.133 SQLite: Output NULL GCC, CLANG, RUST, DAFNY, GO: Output error at compile time PHP: Output error at runtime
(11) By Domingo (mingodad) on 2021-08-06 10:55:55 in reply to 1 [link] [source]
SQLite also fail to compile this long expression with "Error: near line 1: parser stack overflow". Lua/Javascript/MYSQL gives 11.9393973 PHP/PostgreSQL/Python/Ruby/Java/D give NAN or division by ZERO ===== select ( (3.55-180.890/(((35)%5/913.17+((((50.59*(40.904)%9.30+((812%4))*(147*(400)/6+((40.8))+((7.90+(((834)/510%6))+5.600-30.70/7/8*630-((((600%652.62)*(((8)))))*7.20/(700.30%804-1-((608))+54/(402)))-3%1.98))*69.1/(92.4)*(400)-900*850/21*(7.308)%(((((((700)))+((2)-93.30/329-(50)*((703.5))*255.702))))-((10.804)))%95.50+(100.208)*((((141.9-90)))))%(((((((80.705*(((153)))+7/550*((((604)))/75.80%(9.600)*(380.53)-10.90-50%(43)))))))))*(((((((1+(20.7/(5.85/100.7*((6.2)*(95+20-(6)*6*870.7))+57/(64.70)-(44.5-7.50%((90.551)+((((6)))))/(80))%400-20.10)/828+6/80*10+((200))/((808.1-503-((100.904%920.900)))%1/(((((32.867*407.2))))))%(17)/((293.760)))-((2+((64*500)*44.78/(500-838))*((70.38)/120.98)+(1.19+(((200.6-307.900+4/((((2.800)))))%(969.404)*28/903.558-(((60.24)%((1-((14)))+((((8.70*6/560%(9)*290.10-9+73)/113+80+(909)))))))*((960.500)-8.500*((804.905)%3+60+((((((((208))))-560.730))))/(((((1%60.5/(3))))-120.45/8.620/30/((50)/50.30%24%((5.41))%((2.98)/(20.10))*(((((((50)))/(21))))-((510.3%600.330/7))+((10-(((660%670)-100%3-(203.16))))*(((((800))))))-4-20.133))))%(72))-((((((509.90)/(((309.402))%82.90-940.6*((770))))))))*20.700%(610.1)*(90.6)*31-10)))/((3.841))*80/100.50%2+(9+15.5)+(57.30*5)-(((((((2)))-(((10.411)-(1)/(3)))))*((505.409+(6.3)+(20.60+1.702-3.82/20/80.807%6.7+230.1+7.70/300*300.680/64.9%(602)*72.326+((((((((810.307-(((((52%90.187*20%710+300%907.1))))%19)-((((((1-92.30))-3)-46))%4/109)*6.601-(12)+337.87-(2.7)*288.860/((1.300)+70.86-40)*((((51.85+((70.35/60-98.80))%(600)))))+(4.30)-((((((366))))))*2/8/(70)+570.90)))))))*800.2)%(66*10)/184/240.1/(3.20))/500.406*((5))))))/((6.2*45.800)/62/(9.890)))/(80.3)%503)))%(((500)))))%1.40/((50*100.98)*610+3.100)*(404)-(890.60%830)*4.200%(500)-19.310+(10.2))*(44%(((((((221*(((((5.40/8)%(400-8.60)*(80.5)*(((((5.3))))))))*(59%16/((5))*9.2%((((620*((((80.5)-2)))%(52.402)))*(50)))+(((3.80)))-((600.3)))*309.405)))%(40)/502.590-(850)*41.2*1%92.60)%775+((((639.255))))/(600)-3%70*(805.109)*(404/9.9*304.20)+(((9.5%191/209.10/56.100+(((2.77)/80.82*3.8*740*((14*(45)-972+(50*((((((1.8)))+780.20/(70%900/((300-(((((378))+607.6)*(7.7)/14.40+(706.805%(902.8+(176.60+(637))+(((19.20/7.86-5.894%70%(((800.7)-(373%(903)*(680.120/500+(12.740)/(400)+((307.600+62+1.6*97.62*540.570))*(9)%(((1))-(802-3.19))/50%100.4+(1.240%91.9)+240.208)))-(600)%1.4+((27-566/7.4+208/(8)*10/200+51*((330.190)/800/48.719)*5.90*((9)%3.4+618/(((434.680))+9/853.6)%41/(((100*20/((((((2))))/70%(((47/61.200*32.60))+9.96)-((2.7*630.90*((1.40)*(9.1-900.36))%330.203*(465.574))))-(((39%60.27+2/1%5%((30.530)/94*9/541%895.21%((1%90.9%(30)-(((8.80)))%109*110.30*101.30)-300-900%605/((9.14))-(((3.6)))-9/(701.5*20.8*5.30-400/((801.306/706.82-600.40*63/((((((5.730)*(7.40)))))+36)))+((32/12+(((920))/((84.80)%1)*((((((5%(2)%(378)/10*640/(399.39+(700.207))%8.50-(54*4.90%2.58/(5.70*(((((((6.30%(96.100+(((202.2))))))%(((1.60-(590))*6.200))))*8.12)))%((9/64))%1*(4)*(2.4)%(60.306)-(209.70)-((((60-((7/(3.3)))%15)))))%58/349+4.191*490.609-(245.856)*((((((9)%87%(10.50))))/(99.19)/105.90))*(97.6)/10+190.60+600/3*(((((((((((780))%((40.6/7*(((2.750)))))%310.3+(1))%((300.5-440+((((((589.1%(102)/((42)+(((749)*(26.3%(8)-536/(200.24*3)%((80.700))))*(31.9)+(500)*(14%6/((((958.9)))-((60.6-(80.33)-(50.800)))%((2)*((9))/(76.9)))/3)+((((9)%(387))))))*(((6))-80.4-((410)-((570.37)))*6.2+3%(((48)))-8*(460+(710.810-78.300%92*214+400.363)/952)/90.12-((498.60*(427.8)))/50.60%90.58-304)+403*((((260))))-(((707*((72-91))%((400)*1.50)))-8)-60.5-460.70%42%7-((359*111.702%(282*640.7)-36.450/(12)+(60.1)-50.8/(((650)))+77.7+6.506*55%103.10+(2))*(660.11)*((67.12)*90.7+(9)-73+((2.110))+278.607-(30.23*((((705-8/60+((((50.80)+305.8*(508.3)/50.562%(((70.2*42.5%60.202%7.3)))/((700.904/((620))+((707.20)))+900*(3.560)))%82.4-500.51%300.6/((4*(160)-(((8))-(998)-70)/6%50)/11.97%((3.69)*9)-((((836.2/500.4)))))+303.5))%75.8+34.400/3.870*(4.85%7%5.3%4.40-(400%2-(((9)/((((((10+((6.1))))))))%(((26%(401)*(((((((70.9)))+(70))+501%4+68.203*79-370.923/313.9%(362.3+608/4.606)*((200.1)%38.50*612.4*2.1-165)+623.30/(770%90))))*(((40/42.800)*780-690.40-3.404*3.3))+(((506.4)*7.5))%92.8)))+6.38))*540-(603)%105+273+600-100.6+200.1-52)-4*93.5)-230.1*44+2))*500.70%5/15.6)%58.570))*96)+6/1.900-4)/90.70+400.60-8.9+23.8%20.909/498.87)))))/400.2)/896-591.86)))))))))+479.108)%500)-6.34-204)-127.94%5.81/8)))*22)%730.5))+73.900)/904)-807+61.400)+4.90/32.8-80.62))%30.30))-5.310-2.87)+407))/1.24))-64*60.7)*14.400)*10.5-103.727-30)%450.77+190.7%3.4)-444.1%9+437.4))+8)-404+90-5*902%6)))*100.203))*301.615)-7.9)*20))-3.2)-12.29)))+800.40/602.9-22-39)-7/5.390)/200))+409)/37.75-70*5.801))))))-493/86))%10.800)))*60.80)+8.460)))+9 ) as expr; =====
(13) By mzm2021 on 2021-08-06 11:43:47 in reply to 11 [link] [source]
The error message is self-explanatory. So if you have to deal with such deeply nested expressions, you'll have to increase the following constants:
(12.1) By Domingo (mingodad) on 2021-08-06 11:45:36 edited from 12.0 in reply to 1 [source]
And this expression shows that we have several disagreements between programming languages about what is the end result (including SQLite): ===== EXPR = (((((((788)*(8.46))))+8342*1.803-1))*4186.4*(15))*(((22%284/((7530/((2)*(((((25))-421))))))*597%2663)+7283.8-9.60+167.8644%((3))))+(8871) POSTGRESQL = 8291561284461.33301440 SQUILU = 8291561284461.3 JAVA = 8.291561284461331E12 D = 8.29156e+12 SQLITE = 8290383058308.3 PHP = 8035491468054 JAVSCRIPT = 8036090802426.098 MYSQL = 8036090802312.071 AMPL = 8.03609e+12 GAMS = 8.03609E+12 PYTHON = 1.1107636287e+13 RUBY = 11107636286950.146 LUA = 11665910614443 =====
(14) By mzm2021 on 2021-08-06 11:46:40 in reply to 12.0 [link] [source]
Each language defines its own set of casting rules and its operators semantics.
Your use of modulo (%) is what makes the results so different.
(15) By Domingo (mingodad) on 2021-08-06 12:09:25 in reply to 12.1 [link] [source]
And making all operands to modulo operator "%" integer: ===== EXPR = (((((((788)*(8.46))))+8342*1.803-1))*4186.4*(15))*(((22%284/((7530/((2)*(((((25))-421))))))*597%2663)+7283.8-9.60+167%((3))))+(8871) D = 8.29038e+12 JAVA = 8.290383058308305E12 MYSQL = 8034912576159.046 POSTGRESQL = 8290383058308.307200 SQLITE = 8290383058308.3 SQUILU = 8291561284461.3 JAVASCRIPT = 8034912576273.071 PHP = 8035491468054 AMPL = 8.03491e+12 GAMS = 8.03491E+12 PYTHON = 1.11064580608e+13 RUBY = 11106458060797.121 LUA = 11664732388290 ====
(16) By mzm2021 on 2021-08-06 12:59:17 in reply to 15 [link] [source]
Again, different languages ⇒ different operator rules.
SQLite: select 7530/(-396) gives -19
Python: 7530/(-396) gives -20
Python division is actually called floor division: a / b is floor(a div b)
To get the same result as Python's in SQLite: SELECT floor(1.0*7530/(-396))
(17) By Harald Hanche-Olsen (hanche) on 2021-08-06 13:14:20 in reply to 16 [link] [source]
That could fail with integers too large to be exactly represented with doubles, however. Numbers can be hard to work with.
(18) By Domingo (mingodad) on 2021-08-06 13:31:20 in reply to 15 [link] [source]
And after removing all modulo operations: ==== EXPR = (((((((788)*(8.46))))+8342*1.803-1))*4186.4*(15))*(((22/((7530/((2)*(((((25))-421))))))*597)+7283.8-9.60+167))+(8871) SQUILU = 8515287402650.3 SQLITE = 8515287402650.34 POSTGRESQL = 8515287402650.347200 D = 8.51529e+12 JAVA = 8.515287402650345E12 MYSQL = 8259816920501.086 JAVASCRIPT = 8259816920615.111 PHP = 8259816920615.1 LUA = 8259816920615.1 AMPL = 8.25982e+12 GAMS = 8.25982E+12 RUBY = 7701542593121.873 PYTHON = 7.70154259312e+12 ====
(19) By RandomCoder on 2021-08-06 14:08:31 in reply to 18 [link] [source]
PYTHON = 7.70154259312e+12
Nope
Python 3.9.6 (default, Jun 29 2021, 07:18:15)
[GCC 9.3.0] on linux
Type "help", "copyright", "credits" or "license" for more information.
>>> (((((((788)*(8.46))))+8342*1.803-1))*4186.4*(15))*(((22/((7530/((2)*(((((25))-421))))))*597)+7283.8-9.60+167))+(8871)
8259816920615.111
Of course, I'm not sure what any of this proves. Different tools calculate things differently. In this case, I think you're running Python 2 (why?) and between Python 2.x and Python 3.x the behavior for / was changed. Good luck tracking down all of the differences between all of the things, but this has very little to do with SQLite.
(20) By MBL (RoboManni) on 2021-08-06 14:15:51 in reply to 18 [link] [source]
Euler Math Toolbox = 8.25981692062e+12
(21) By mzm2021 on 2021-08-06 14:21:44 in reply to 18 [link] [source]
Now remove also the division since it does not obey to the same rules across languages...
(22) By Donald Griggs (dfgriggs) on 2021-08-06 14:28:15 in reply to 18 [link] [source]
The differences seem to be in integer arithmetic, is that right?
If one makes all the numbers floating point, then sqlite (and, I suspect others) join the 8.2598e+12 crowd.
select (((((((788.)(8.46))))+8342. * 1.803 -1.))*4186.4 *(15.))(((22. /((7530. /((2.)*(((((25.))-421.))))))*597.)+7283.8 - 9.60+167.))+(8871.); 8259816920615.11
So is there any evidence that any of the listed software is not working as documented?
(23) By Simon Slavin (slavin) on 2021-08-06 15:30:19 in reply to 22 [link] [source]
I haven't seen any such evidence. Different languages have differences in parsing numbers (how to handle '11' '11.' and '11.0' when looking at text) and differences in rules for division and modulus arithmetic. Every language is free to implement its own rules.
The original question includes at least one operation of division or modulus by 0. Division by zero is undefined in SQLite. SQLite returns NUL. Not a bug. Nothing to see here.
(31) By J.M. Aranda (JMAranda) on 2021-08-10 18:47:47 in reply to 18 [link] [source]
PARI/GP: ? (((((((788)*(8.46))))+8342*1.803-1))*4186.4*(15))*(((22/((7530/((2)*(((((25))-421))))))*597)+7283.8-9.60+167))+(8871) %1 = 8259816920615.113759362549801 Earlier I said that PARI/GP was an C.A.S. I was wrong. Je me suis trompé. PARI/GP est LE Système de Calcul Algébrique.
(24) By Domingo (mingodad) on 2021-08-07 10:24:48 in reply to 1 [link] [source]
And here is a bunch of different ways calculating the same expressions to show how type declaration influence calculation:
=====
select "-- literal expression, integer values types rounded to floor";
select (((((((788)*(8))))+8342*1-1))*4186*(15))*(((22%284/((7530/((2)*(((((25))-421))))))*597%2663)+7283-9+167%((3))))+(8871) as expr;
select (((((((788)*(8))))+8342*1-1))*4186*(15))*(((22/((7530/((2)*(((((25))-421))))))*597)+7283-9+167))+(8871) as expr;
select "-- literal expression, integer values types rounded to ceil";
select (((((((788)*(9))))+8342*2-1))*4187*(15))*(((22%284/((7530/((2)*(((((25))-421))))))*597%2663)+7284-10+168%((3))))+(8871) as expr;
select (((((((788)*(9))))+8342*2-1))*4187*(15))*(((22/((7530/((2)*(((((25))-421))))))*597)+7284-10+168))+(8871) as expr;
select "-- literal expression, mixed values types";
select (((((((788)*(8.46))))+8342*1.803-1))*4186.4*(15))*(((22%284/((7530/((2)*(((((25))-421))))))*597%2663)+7283.8-9.60+167.8644%((3))))+(8871) as expr;
select (((((((788)*(8.46))))+8342*1.803-1))*4186.4*(15))*(((22/((7530/((2)*(((((25))-421))))))*597)+7283.8-9.60+167.8644))+(8871) as expr;
select "-- json array expression, mixed values types";
select ( (((((((json_extract(js.js, "$[0]"))*(json_extract(js.js, "$[1]")))))+json_extract(js.js, "$[2]")*json_extract(js.js, "$[3]")-json_extract(js.js, "$[4]")))*json_extract(js.js, "$[5]")*(json_extract(js.js, "$[6]")))*(((json_extract(js.js, "$[7]")%json_extract(js.js, "$[8]")/((json_extract(js.js, "$[9]")/((json_extract(js.js, "$[10]"))*(((((json_extract(js.js, "$[11]")))-json_extract(js.js, "$[12]")))))))*json_extract(js.js, "$[13]")%json_extract(js.js, "$[14]"))+json_extract(js.js, "$[15]")-json_extract(js.js, "$[16]")+json_extract(js.js, "$[17]")%((json_extract(js.js, "$[18]")))))+(json_extract(js.js, "$[19]")) ) as expr
from (select "[788, 8.46, 8342, 1.803, 1, 4186.4, 15, 22, 284, 7530, 2, 25, 421, 597, 2663, 7283.8, 9.60, 167.8644, 3, 8871]" as js) as js;
select (((((((json_extract(js.js, "$[0]"))*(json_extract(js.js, "$[1]")))))+json_extract(js.js, "$[2]")*json_extract(js.js, "$[3]")-json_extract(js.js, "$[4]")))*json_extract(js.js, "$[5]")*(json_extract(js.js, "$[6]")))*(((json_extract(js.js, "$[7]")/((json_extract(js.js, "$[9]")/((json_extract(js.js, "$[10]"))*(((((json_extract(js.js, "$[11]")))-json_extract(js.js, "$[12]")))))))*json_extract(js.js, "$[13]"))+json_extract(js.js, "$[15]")-json_extract(js.js, "$[16]")+json_extract(js.js, "$[17]")))+(json_extract(js.js, "$[19]")) as expr
from (select "[788, 8.46, 8342, 1.803, 1, 4186.4, 15, 22, 284, 7530, 2, 25, 421, 597, 2663, 7283.8, 9.60, 167.8644, 3, 8871]" as js) as js;
select "-- json object expression, mixed values types";
select ( (((((((json_extract(js.js, "$.a"))*(json_extract(js.js, "$.b")))))+json_extract(js.js, "$.c")*json_extract(js.js, "$.d")-json_extract(js.js, "$.e")))*json_extract(js.js, "$.f")*(json_extract(js.js, "$.g")))*(((json_extract(js.js, "$.h")%json_extract(js.js, "$.i")/((json_extract(js.js, "$.j")/((json_extract(js.js, "$.k"))*(((((json_extract(js.js, "$.l")))-json_extract(js.js, "$.m")))))))*json_extract(js.js, "$.n")%json_extract(js.js, "$.o"))+json_extract(js.js, "$.p")-json_extract(js.js, "$.q")+json_extract(js.js, "$.r")%((json_extract(js.js, "$.s")))))+(json_extract(js.js, "$.t")) ) as expr
from (select '{"a":788, "b":8.46, "c":8342, "d":1.803, "e":1, "f":4186.4, "g":15, "h":22, "i":284, "j":7530, "k":2, "l":25, "m":421, "n":597, "o":2663, "p":7283.8, "q":9.60, "r":167.8644, "s":3, "t":8871}' as js) as js;
select ( (((((((json_extract(js.js, "$.a"))*(json_extract(js.js, "$.b")))))+json_extract(js.js, "$.c")*json_extract(js.js, "$.d")-json_extract(js.js, "$.e")))*json_extract(js.js, "$.f")*(json_extract(js.js, "$.g")))*(((json_extract(js.js, "$.h")/((json_extract(js.js, "$.j")/((json_extract(js.js, "$.k"))*(((((json_extract(js.js, "$.l")))-json_extract(js.js, "$.m")))))))*json_extract(js.js, "$.n"))+json_extract(js.js, "$.p")-json_extract(js.js, "$.q")+json_extract(js.js, "$.r")))+(json_extract(js.js, "$.t")) ) as expr
from (select '{"a":788, "b":8.46, "c":8342, "d":1.803, "e":1, "f":4186.4, "g":15, "h":22, "i":284, "j":7530, "k":2, "l":25, "m":421, "n":597, "o":2663, "p":7283.8, "q":9.60, "r":167.8644, "s":3, "t":8871}' as js) as js;
select "-- json object expression, text/mixed values types";
select ( (((((((json_extract(js.js, "$.a"))*(json_extract(js.js, "$.b")))))+json_extract(js.js, "$.c")*json_extract(js.js, "$.d")-json_extract(js.js, "$.e")))*json_extract(js.js, "$.f")*(json_extract(js.js, "$.g")))*(((json_extract(js.js, "$.h")%json_extract(js.js, "$.i")/((json_extract(js.js, "$.j")/((json_extract(js.js, "$.k"))*(((((json_extract(js.js, "$.l")))-json_extract(js.js, "$.m")))))))*json_extract(js.js, "$.n")%json_extract(js.js, "$.o"))+json_extract(js.js, "$.p")-json_extract(js.js, "$.q")+json_extract(js.js, "$.r")%((json_extract(js.js, "$.s")))))+(json_extract(js.js, "$.t")) ) as expr
from (select '{"a":"788", "b":"8.46", "c":"8342", "d":"1.803", "e":"1", "f":"4186.4", "g":"15", "h":"22", "i":"284", "j":"7530", "k":"2", "l":"25", "m":"421", "n":"597", "o":"2663", "p":"7283.8", "q":"9.60", "r":"167.8644", "s":"3", "t":"8871"}' as js) as js;
select ( (((((((json_extract(js.js, "$.a"))*(json_extract(js.js, "$.b")))))+json_extract(js.js, "$.c")*json_extract(js.js, "$.d")-json_extract(js.js, "$.e")))*json_extract(js.js, "$.f")*(json_extract(js.js, "$.g")))*(((json_extract(js.js, "$.h")/((json_extract(js.js, "$.j")/((json_extract(js.js, "$.k"))*(((((json_extract(js.js, "$.l")))-json_extract(js.js, "$.m")))))))*json_extract(js.js, "$.n"))+json_extract(js.js, "$.p")-json_extract(js.js, "$.q")+json_extract(js.js, "$.r")))+(json_extract(js.js, "$.t")) ) as expr
from (select '{"a":"788", "b":"8.46", "c":"8342", "d":"1.803", "e":"1", "f":"4186.4", "g":"15", "h":"22", "i":"284", "j":"7530", "k":"2", "l":"25", "m":"421", "n":"597", "o":"2663", "p":"7283.8", "q":"9.60", "r":"167.8644", "s":"3", "t":"8871"}' as js) as js;
select "-- no type declaration, mixed values";
create table ta(id integer primary key, a, b, c, d, e, f, g, h, i, j, k, l, m, n, o, p, q, r, s, t);
insert into ta(a, b, c, d, e, f, g, h, i, j, k, l, m, n, o, p, q, r, s, t)
values(788, 8.46, 8342, 1.803, 1, 4186.4, 15, 22, 284, 7530, 2, 25, 421, 597, 2663, 7283.8, 9.60, 167.8644, 3, 8871);
select (((((((a)*(b))))+c*d-e))*f*(g))*(((h%i/((j/((k)*(((((l))-m))))))*n%o)+p-q+r%((s))))+(t) as expr from ta;
select (((((((a)*(b))))+c*d-e))*f*(g))*(((h/((j/((k)*(((((l))-m))))))*n)+p-q+r))+(t) as expr from ta;
select "-- text type declaration, mixed values";
create table tb(id integer primary key, a text, b text, c text, d text, e text, f text, g text, h text, i text, j text, k text, l text, m text, n text, o text, p text, q text, r text, s text, t text);
insert into tb(a, b, c, d, e, f, g, h, i, j, k, l, m, n, o, p, q, r, s, t)
values(788, 8.46, 8342, 1.803, 1, 4186.4, 15, 22, 284, 7530, 2, 25, 421, 597, 2663, 7283.8, 9.60, 167.8644, 3, 8871);
select (((((((a)*(b))))+c*d-e))*f*(g))*(((h%i/((j/((k)*(((((l))-m))))))*n%o)+p-q+r%((s))))+(t) as expr from tb;
select (((((((a)*(b))))+c*d-e))*f*(g))*(((h/((j/((k)*(((((l))-m))))))*n)+p-q+r))+(t) as expr from tb;
select "-- text type declaration, text/mixed values";
create table tbt(id integer primary key, a text, b text, c text, d text, e text, f text, g text, h text, i text, j text, k text, l text, m text, n text, o text, p text, q text, r text, s text, t text);
insert into tbt(a, b, c, d, e, f, g, h, i, j, k, l, m, n, o, p, q, r, s, t)
values('788', '8.46', '8342', '1.803', '1', '4186.4', '15', '22', '284', '7530', '2', '25', '421', '597', '2663', '7283.8', '9.60', '167.8644', '3', '8871');
select (((((((a)*(b))))+c*d-e))*f*(g))*(((h%i/((j/((k)*(((((l))-m))))))*n%o)+p-q+r%((s))))+(t) as expr from tbt;
select (((((((a)*(b))))+c*d-e))*f*(g))*(((h/((j/((k)*(((((l))-m))))))*n)+p-q+r))+(t) as expr from tbt;
select "-- blob type declaration, mixed values";
create table tl(id integer primary key, a blob, b blob, c blob, d blob, e blob, f blob, g blob, h blob, i blob, j blob, k blob, l blob, m blob, n blob, o blob, p blob, q blob, r blob, s blob, t blob);
insert into tl(a, b, c, d, e, f, g, h, i, j, k, l, m, n, o, p, q, r, s, t)
values(788, 8.46, 8342, 1.803, 1, 4186.4, 15, 22, 284, 7530, 2, 25, 421, 597, 2663, 7283.8, 9.60, 167.8644, 3, 8871);
select (((((((a)*(b))))+c*d-e))*f*(g))*(((h%i/((j/((k)*(((((l))-m))))))*n%o)+p-q+r%((s))))+(t) as expr from tl;
select (((((((a)*(b))))+c*d-e))*f*(g))*(((h/((j/((k)*(((((l))-m))))))*n)+p-q+r))+(t) as expr from tl;
select "-- integer type declaration, mixed values";
create table tc(id integer primary key, a integer, b integer, c integer, d integer, e integer, f integer, g integer, h integer, i integer, j integer, k integer, l integer, m integer, n integer, o integer, p integer, q integer, r integer, s integer, t integer);
insert into tc(a, b, c, d, e, f, g, h, i, j, k, l, m, n, o, p, q, r, s, t)
values(788, 8.46, 8342, 1.803, 1, 4186.4, 15, 22, 284, 7530, 2, 25, 421, 597, 2663, 7283.8, 9.60, 167.8644, 3, 8871);
select (((((((a)*(b))))+c*d-e))*f*(g))*(((h%i/((j/((k)*(((((l))-m))))))*n%o)+p-q+r%((s))))+(t) as expr from tc;
select (((((((a)*(b))))+c*d-e))*f*(g))*(((h/((j/((k)*(((((l))-m))))))*n)+p-q+r))+(t) as expr from tc;
select "-- integer/real type declaration, integer/real values";
create table te(id integer primary key, a integer, b float, c integer, d float, e integer, f float, g integer, h integer, i integer, j integer, k integer, l integer, m integer, n integer, o integer, p float, q float, r float, s integer, t integer);
insert into te(a, b, c, d, e, f, g, h, i, j, k, l, m, n, o, p, q, r, s, t)
values(788, 8.46, 8342, 1.803, 1, 4186.4, 15, 22, 284, 7530, 2, 25, 421, 597, 2663, 7283.8, 9.60, 167.8644, 3, 8871);
select (((((((a)*(b))))+c*d-e))*f*(g))*(((h%i/((j/((k)*(((((l))-m))))))*n%o)+p-q+r%((s))))+(t) as expr from te;
select (((((((a)*(b))))+c*d-e))*f*(g))*(((h/((j/((k)*(((((l))-m))))))*n)+p-q+r))+(t) as expr from te;
select "-- integer type declaration, real values";
create table tf(id integer primary key, a integer, b integer, c integer, d integer, e integer, f integer, g integer, h integer, i integer, j integer, k integer, l integer, m integer, n integer, o integer, p integer, q integer, r integer, s integer, t integer);
insert into tf(a, b, c, d, e, f, g, h, i, j, k, l, m, n, o, p, q, r, s, t)
values(788.0, 8.46, 8342.0, 1.803, 1.0, 4186.4, 15.0, 22.0, 284.0, 7530.0, 2.0, 25.0, 421.0, 597.0, 2663.0, 7283.8, 9.60, 167.8644, 3.0, 8871.0);
select (((((((a)*(b))))+c*d-e))*f*(g))*(((h%i/((j/((k)*(((((l))-m))))))*n%o)+p-q+r%((s))))+(t) as expr from tf;
select (((((((a)*(b))))+c*d-e))*f*(g))*(((h/((j/((k)*(((((l))-m))))))*n)+p-q+r))+(t) as expr from tf;
select "-- literal expression, real values types";
select (((((((788.0)*(8.46))))+8342*1.803-1.0))*4186.4*(15.0))*(((22.0%284.0/((7530.0/((2.0)*(((((25.0))-421.0))))))*597.0%2663.0)+7283.8-9.60+167.8644%((3.0))))+(8871.0) as expr;
select (((((((788.0)*(8.46))))+8342*1.803-1.0))*4186.4*(15.0))*(((22.0/((7530.0/((2.0)*(((((25.0))-421.0))))))*597.0)+7283.8-9.60+167.8644))+(8871.0) as expr;
select "-- real type declaration, mixed values";
create table td(id integer primary key, a real, b real, c real, d real, e real, f real, g real, h real, i real, j real, k real, l real, m real, n real, o real, p real, q real, r real, s real, t real);
insert into td(a, b, c, d, e, f, g, h, i, j, k, l, m, n, o, p, q, r, s, t)
values(788, 8.46, 8342, 1.803, 1, 4186.4, 15, 22, 284, 7530, 2, 25, 421, 597, 2663, 7283.8, 9.60, 167.8644, 3, 8871);
select (((((((a)*(b))))+c*d-e))*f*(g))*(((h%i/((j/((k)*(((((l))-m))))))*n%o)+p-q+r%((s))))+(t) as expr from td;
select (((((((a)*(b))))+c*d-e))*f*(g))*(((h/((j/((k)*(((((l))-m))))))*n)+p-q+r))+(t) as expr from td;
select "-- real type declaration, real values";
create table td2(id integer primary key, a real, b real, c real, d real, e real, f real, g real, h real, i real, j real, k real, l real, m real, n real, o real, p real, q real, r real, s real, t real);
insert into td2(a, b, c, d, e, f, g, h, i, j, k, l, m, n, o, p, q, r, s, t)
values(788.0, 8.46, 8342.0, 1.803, 1.0, 4186.4, 15.0, 22.0, 284.0, 7530.0, 2.0, 25.0, 421.0, 597.0, 2663.0, 7283.8, 9.60, 167.8644, 3.0, 8871.0);
select (((((((a)*(b))))+c*d-e))*f*(g))*(((h%i/((j/((k)*(((((l))-m))))))*n%o)+p-q+r%((s))))+(t) as expr from td2;
select (((((((a)*(b))))+c*d-e))*f*(g))*(((h/((j/((k)*(((((l))-m))))))*n)+p-q+r))+(t) as expr from td2;
select "-- blob type declaration, real values";
create table tg(id integer primary key, a blob, b blob, c blob, d blob, e blob, f blob, g blob, h blob, i blob, j blob, k blob, l blob, m blob, n blob, o blob, p blob, q blob, r blob, s blob, t blob);
insert into tg(a, b, c, d, e, f, g, h, i, j, k, l, m, n, o, p, q, r, s, t)
values(788.0, 8.46, 8342.0, 1.803, 1.0, 4186.4, 15.0, 22.0, 284.0, 7530.0, 2.0, 25.0, 421.0, 597.0, 2663.0, 7283.8, 9.60, 167.8644, 3.0, 8871.0);
select (((((((a)*(b))))+c*d-e))*f*(g))*(((h%i/((j/((k)*(((((l))-m))))))*n%o)+p-q+r%((s))))+(t) as expr from tg;
select (((((((a)*(b))))+c*d-e))*f*(g))*(((h/((j/((k)*(((((l))-m))))))*n)+p-q+r))+(t) as expr from tg;
select "-- text type declaration, text/real values";
create table tbtf(id integer primary key, a text, b text, c text, d text, e text, f text, g text, h text, i text, j text, k text, l text, m text, n text, o text, p text, q text, r text, s text, t text);
insert into tbtf(a, b, c, d, e, f, g, h, i, j, k, l, m, n, o, p, q, r, s, t)
values('788.0', '8.46', '8342', '1.803', '1.0', '4186.4', '15.0', '22.0', '284.0', '7530.0', '2.0', '25.0', '421.0', '597.0', '2663.0', '7283.8', '9.60', '167.8644', '3.0', '8871.0');
select (((((((a)*(b))))+c*d-e))*f*(g))*(((h%i/((j/((k)*(((((l))-m))))))*n%o)+p-q+r%((s))))+(t) as expr from tbtf;
select (((((((a)*(b))))+c*d-e))*f*(g))*(((h/((j/((k)*(((((l))-m))))))*n)+p-q+r))+(t) as expr from tbtf;
select "-- json array expression, real values types";
select ( (((((((json_extract(js.js, "$[0]"))*(json_extract(js.js, "$[1]")))))+json_extract(js.js, "$[2]")*json_extract(js.js, "$[3]")-json_extract(js.js, "$[4]")))*json_extract(js.js, "$[5]")*(json_extract(js.js, "$[6]")))*(((json_extract(js.js, "$[7]")%json_extract(js.js, "$[8]")/((json_extract(js.js, "$[9]")/((json_extract(js.js, "$[10]"))*(((((json_extract(js.js, "$[11]")))-json_extract(js.js, "$[12]")))))))*json_extract(js.js, "$[13]")%json_extract(js.js, "$[14]"))+json_extract(js.js, "$[15]")-json_extract(js.js, "$[16]")+json_extract(js.js, "$[17]")%((json_extract(js.js, "$[18]")))))+(json_extract(js.js, "$[19]")) ) as expr
from (select "[788.0, 8.46, 8342.0, 1.803, 1.0, 4186.4, 15.0, 22.0, 284.0, 7530.0, 2.0, 25.0, 421.0, 597.0, 2663.0, 7283.8, 9.60, 167.8644, 3.0, 8871.0]" as js) as js;
select (((((((json_extract(js.js, "$[0]"))*(json_extract(js.js, "$[1]")))))+json_extract(js.js, "$[2]")*json_extract(js.js, "$[3]")-json_extract(js.js, "$[4]")))*json_extract(js.js, "$[5]")*(json_extract(js.js, "$[6]")))*(((json_extract(js.js, "$[7]")/((json_extract(js.js, "$[9]")/((json_extract(js.js, "$[10]"))*(((((json_extract(js.js, "$[11]")))-json_extract(js.js, "$[12]")))))))*json_extract(js.js, "$[13]"))+json_extract(js.js, "$[15]")-json_extract(js.js, "$[16]")+json_extract(js.js, "$[17]")))+(json_extract(js.js, "$[19]")) as expr
from (select "[788.0, 8.46, 8342.0, 1.803, 1.0, 4186.4, 15.0, 22.0, 284.0, 7530.0, 2.0, 25.0, 421.0, 597.0, 2663.0, 7283.8, 9.60, 167.8644, 3.0, 8871.0]" as js) as js;
select "-- json object expression, real values types";
select ( (((((((json_extract(js.js, "$.a"))*(json_extract(js.js, "$.b")))))+json_extract(js.js, "$.c")*json_extract(js.js, "$.d")-json_extract(js.js, "$.e")))*json_extract(js.js, "$.f")*(json_extract(js.js, "$.g")))*(((json_extract(js.js, "$.h")%json_extract(js.js, "$.i")/((json_extract(js.js, "$.j")/((json_extract(js.js, "$.k"))*(((((json_extract(js.js, "$.l")))-json_extract(js.js, "$.m")))))))*json_extract(js.js, "$.n")%json_extract(js.js, "$.o"))+json_extract(js.js, "$.p")-json_extract(js.js, "$.q")+json_extract(js.js, "$.r")%((json_extract(js.js, "$.s")))))+(json_extract(js.js, "$.t")) ) as expr
from (select '{"a":788.0, "b":8.46, "c":8342.0, "d":1.803, "e":1.0, "f":4186.4, "g":15.0, "h":22.0, "i":284.0, "j":7530.0, "k":2.0, "l":25.0, "m":421.0, "n":597.0, "o":2663.0, "p":7283.8, "q":9.60, "r":167.8644, "s":3.0, "t":8871.0}' as js) as js;
select ( (((((((json_extract(js.js, "$.a"))*(json_extract(js.js, "$.b")))))+json_extract(js.js, "$.c")*json_extract(js.js, "$.d")-json_extract(js.js, "$.e")))*json_extract(js.js, "$.f")*(json_extract(js.js, "$.g")))*(((json_extract(js.js, "$.h")/((json_extract(js.js, "$.j")/((json_extract(js.js, "$.k"))*(((((json_extract(js.js, "$.l")))-json_extract(js.js, "$.m")))))))*json_extract(js.js, "$.n"))+json_extract(js.js, "$.p")-json_extract(js.js, "$.q")+json_extract(js.js, "$.r")))+(json_extract(js.js, "$.t")) ) as expr
from (select '{"a":788.0, "b":8.46, "c":8342.0, "d":1.803, "e":1.0, "f":4186.4, "g":15.0, "h":22.0, "i":284.0, "j":7530.0, "k":2.0, "l":25.0, "m":421.0, "n":597.0, "o":2663.0, "p":7283.8, "q":9.60, "r":167.8644, "s":3.0, "t":8871.0}' as js) as js;
select "-- json object expression, text/real values types";
select ( (((((((json_extract(js.js, "$.a"))*(json_extract(js.js, "$.b")))))+json_extract(js.js, "$.c")*json_extract(js.js, "$.d")-json_extract(js.js, "$.e")))*json_extract(js.js, "$.f")*(json_extract(js.js, "$.g")))*(((json_extract(js.js, "$.h")%json_extract(js.js, "$.i")/((json_extract(js.js, "$.j")/((json_extract(js.js, "$.k"))*(((((json_extract(js.js, "$.l")))-json_extract(js.js, "$.m")))))))*json_extract(js.js, "$.n")%json_extract(js.js, "$.o"))+json_extract(js.js, "$.p")-json_extract(js.js, "$.q")+json_extract(js.js, "$.r")%((json_extract(js.js, "$.s")))))+(json_extract(js.js, "$.t")) ) as expr
from (select '{"a":"788.0", "b":"8.46", "c":"8342.0", "d":"1.803", "e":"1.0", "f":"4186.4", "g":"15.0", "h":"22.0", "i":"284.0", "j":"7530.0", "k":"2.0", "l":"25.0", "m":"421.0", "n":"597.0", "o":"2663.0", "p":"7283.8", "q":"9.60", "r":"167.8644", "s":"3.0", "t":"8871.0"}' as js) as js;
select ( (((((((json_extract(js.js, "$.a"))*(json_extract(js.js, "$.b")))))+json_extract(js.js, "$.c")*json_extract(js.js, "$.d")-json_extract(js.js, "$.e")))*json_extract(js.js, "$.f")*(json_extract(js.js, "$.g")))*(((json_extract(js.js, "$.h")/((json_extract(js.js, "$.j")/((json_extract(js.js, "$.k"))*(((((json_extract(js.js, "$.l")))-json_extract(js.js, "$.m")))))))*json_extract(js.js, "$.n"))+json_extract(js.js, "$.p")-json_extract(js.js, "$.q")+json_extract(js.js, "$.r")))+(json_extract(js.js, "$.t")) ) as expr
from (select '{"a":"788.0", "b":"8.46", "c":"8342.0", "d":"1.803", "e":"1.0", "f":"4186.4", "g":"15.0", "h":"22.0", "i":"284.0", "j":"7530.0", "k":"2.0", "l":"25.0", "m":"421.0", "n":"597.0", "o":"2663.0", "p":"7283.8", "q":"9.60", "r":"167.8644", "s":"3.0", "t":"8871.0"}' as js) as js;
=====
(27) By Domingo (mingodad) on 2021-08-09 13:07:42 in reply to 24 [link] [source]
And here is the output of the previous message SQL with sqlite compiled with my changes and SQLITE_PEDANTIC defined: ===== sqlite3 < "text-expr.sql" -- literal expression, integer values types rounded to floor 5592761191971 5744488517721 -- literal expression, integer values types rounded to ceil 9078588368871 9329444099871 -- literal expression, mixed values types FP Remainder received non integer values 3.000000 :: 167.864400 8290383058308.3 8516465628803.37 -- json array expression, mixed values types FP Remainder received non integer values 3.000000 :: 167.864400 8290383058308.3 8516465628803.37 -- json object expression, mixed values types FP Remainder received non integer values 3.000000 :: 167.864400 8290383058308.3 8516465628803.37 -- json object expression, text/mixed values types FP Remainder received non integer values 3.000000 :: 167.864400 8290383058308.3 8516465628803.37 -- text type declaration, mixed values Affinity applied on make record 1 : 3 : B Affinity applied on make record 2 : 3 : B Affinity applied on make record 3 : 3 : B Affinity applied on make record 4 : 3 : B Affinity applied on make record 5 : 3 : B Affinity applied on make record 6 : 3 : B Affinity applied on make record 7 : 3 : B Affinity applied on make record 8 : 3 : B Affinity applied on make record 9 : 3 : B Affinity applied on make record 10 : 3 : B Affinity applied on make record 11 : 3 : B Affinity applied on make record 12 : 3 : B Affinity applied on make record 13 : 3 : B Affinity applied on make record 14 : 3 : B Affinity applied on make record 15 : 3 : B Affinity applied on make record 16 : 3 : B Affinity applied on make record 17 : 3 : B Affinity applied on make record 18 : 3 : B Affinity applied on make record 19 : 3 : B Affinity applied on make record 20 : 3 : B FP Remainder received non integer values 3.000000 :: 167.864400 8290383058308.3 8516465628803.37 -- text type declaration, text/mixed values FP Remainder received non integer values 3.000000 :: 167.864400 8290383058308.3 8516465628803.37 -- blob type declaration, mixed values FP Remainder received non integer values 3.000000 :: 167.864400 8290383058308.3 8516465628803.37 -- integer type declaration, mixed values Affinity applied on make record 2 : 2 : D Affinity applied on make record 4 : 2 : D Affinity applied on make record 6 : 2 : D Affinity applied on make record 16 : 2 : D Affinity applied on make record 17 : 2 : D Affinity applied on make record 18 : 2 : D FP Remainder received non integer values 3.000000 :: 167.864400 8290383058308.3 8516465628803.37 -- integer/real type declaration, integer/real values FP Remainder received non integer values 3.000000 :: 167.864400 8290383058308.3 8516465628803.37 -- integer type declaration, real values Affinity applied on make record 1 : 2 : D Affinity applied on make record 2 : 2 : D Affinity applied on make record 3 : 2 : D Affinity applied on make record 4 : 2 : D Affinity applied on make record 5 : 2 : D Affinity applied on make record 6 : 2 : D Affinity applied on make record 7 : 2 : D Affinity applied on make record 8 : 2 : D Affinity applied on make record 9 : 2 : D Affinity applied on make record 10 : 2 : D Affinity applied on make record 11 : 2 : D Affinity applied on make record 12 : 2 : D Affinity applied on make record 13 : 2 : D Affinity applied on make record 14 : 2 : D Affinity applied on make record 15 : 2 : D Affinity applied on make record 16 : 2 : D Affinity applied on make record 17 : 2 : D Affinity applied on make record 18 : 2 : D Affinity applied on make record 19 : 2 : D Affinity applied on make record 20 : 2 : D FP Remainder received non integer values 3.000000 :: 167.864400 8290383058308.3 8516465628803.37 -- literal expression, real values types 8035491468053.99 8260995146768.14 -- real type declaration, mixed values 8035491468053.99 8260995146768.14 -- real type declaration, real values 8035491468053.99 8260995146768.14 -- blob type declaration, real values 8035491468053.99 8260995146768.14 -- text type declaration, text/real values 8035491468053.99 8260995146768.14 -- json array expression, real values types 8035491468053.99 8260995146768.14 -- json object expression, real values types 8035491468053.99 8260995146768.14 -- json object expression, text/real values types 8035491468053.99 8260995146768.14 ====
(25.2) By Domingo (mingodad) on 2021-08-09 12:01:30 edited from 25.1 in reply to 1 [link] [source]
After this post I looked at how I could minimally enable warning/error messages on some of the problems that using mixed data types can lead to unexpected results and come with this small changes to "src/vdbe.c" that can be activated when defining SQLITE_PEDANTIC macro.
====
-static void applyAffinity(
+static int applyAffinity(
Mem *pRec, /* The value to apply affinity to */
char affinity, /* The affinity to be applied */
u8 enc /* Use this text encoding */
){
+ int rc = 0;
if( affinity>=SQLITE_AFF_NUMERIC ){
assert( affinity==SQLITE_AFF_INTEGER || affinity==SQLITE_AFF_REAL
|| affinity==SQLITE_AFF_NUMERIC );
if( (pRec->flags & MEM_Int)==0 ){ /*OPTIMIZATION-IF-FALSE*/
if( (pRec->flags & MEM_Real)==0 ){
- if( pRec->flags & MEM_Str ) applyNumericAffinity(pRec,1);
+ if( pRec->flags & MEM_Str ) {applyNumericAffinity(pRec,1); rc = 1;}
}else{
- sqlite3VdbeIntegerAffinity(pRec);
+ sqlite3VdbeIntegerAffinity(pRec); rc = 2;
}
}
}else if( affinity==SQLITE_AFF_TEXT ){
/* Only attempt the conversion to TEXT if there is an integer or real
** representation (blob and NULL do not get converted) but no string
** representation. It would be harmless to repeat the conversion if
** there is already a string rep, but it is pointless to waste those
** CPU cycles. */
if( 0==(pRec->flags&MEM_Str) ){ /*OPTIMIZATION-IF-FALSE*/
if( (pRec->flags&(MEM_Real|MEM_Int|MEM_IntReal)) ){
testcase( pRec->flags & MEM_Int );
testcase( pRec->flags & MEM_Real );
testcase( pRec->flags & MEM_IntReal );
- sqlite3VdbeMemStringify(pRec, enc, 1);
+ sqlite3VdbeMemStringify(pRec, enc, 1); rc = 3;
}
}
pRec->flags &= ~(MEM_Real|MEM_Int|MEM_IntReal);
}
+ return rc;
}
...
case OP_Add: /* same as TK_PLUS, in1, in2, out3 */
case OP_Subtract: /* same as TK_MINUS, in1, in2, out3 */
@@ -1699,16 +1701,24 @@
switch( pOp->opcode ){
case OP_Add: rB += rA; break;
case OP_Subtract: rB -= rA; break;
case OP_Multiply: rB *= rA; break;
case OP_Divide: {
+#ifdef SQLITE_PEDANTIC
+ if( ((type1 | type2) & MEM_Int)!=0 )
+ fprintf(stderr, "FP Division received non integer values %f :: %f\n", rA, rB);
+#endif
/* (double)0 In case of SQLITE_OMIT_FLOATING_POINT... */
if( rA==(double)0 ) goto arithmetic_result_is_null;
rB /= rA;
break;
}
default: {
+#ifdef SQLITE_PEDANTIC
+ if( ((type1 | type2) & MEM_Int)!=0 )
+ fprintf(stderr, "FP Remainder received non integer values %f :: %f\n", rA, rB);
+#endif
iA = sqlite3VdbeIntValue(pIn1);
iB = sqlite3VdbeIntValue(pIn2);
if( iA==0 ) goto arithmetic_result_is_null;
if( iA==-1 ) iA = 1;
rB = (double)(iB % iA);
@@ -1838,11 +1848,11 @@
break;
}
...
**
** Although sqlite3VdbeSerialGet() may read at most 8 bytes from the
** buffer passed to it, debugging function VdbeMemPrettyPrint() may
** read more. Use the global constant sqlite3CtypeMap[] as the array,
** as that array is 256 bytes long (plenty for VdbeMemPrettyPrint())
@@ -2958,22 +2968,25 @@
char *zAffinity; /* The affinity string for the record */
int file_format; /* File format to use for encoding */
u32 len; /* Length of a field */
u8 *zHdr; /* Where to write next byte of the header */
u8 *zPayload; /* Where to write next byte of the payload */
+#ifdef SQLITE_PEDANTIC
+ int rc_affinity;
+#endif
/* Assuming the record contains N fields, the record format looks
** like this:
**
** ------------------------------------------------------------------------
** | hdr-size | type 0 | type 1 | ... | type N-1 | data0 | ... | data N-1 |
** ------------------------------------------------------------------------
**
** Data(0) is taken from register P1. Data(1) comes from register P1+1
** and so forth.
**
- ** Each type field is a varint representing the serial type of the
+ ** Each type field is a varint representing the serial type of the
** corresponding data element (see sqlite3VdbeSerialType()). The
** hdr-size field is also a varint which is the offset from the beginning
** of the record to data0.
*/
nData = 0; /* Number of bytes of data space */
@@ -2996,11 +3009,18 @@
*/
assert( pData0<=pLast );
if( zAffinity ){
pRec = pData0;
do{
+#ifdef SQLITE_PEDANTIC
+ rc_affinity =
+#endif
applyAffinity(pRec, zAffinity[0], encoding);
+#ifdef SQLITE_PEDANTIC
+ if(rc_affinity && !((rc_affinity == 2) && (zAffinity[0] == SQLITE_AFF_REAL)))
+ fprintf(stderr, "Affinity applied on make record %d : %d : %c\n", (int)(pRec-pData0), rc_affinity, zAffinity[0]);
+#endif
if( zAffinity[0]==SQLITE_AFF_REAL && (pRec->flags & MEM_Int) ){
pRec->flags |= MEM_IntReal;
pRec->flags &= ~(MEM_Int);
}
REGISTER_TRACE((int)(pRec-aMem), pRec);
@@ -3160,11 +3180,11 @@
nHdr += nVarint;
if( nVarint<sqlite3VarintLen(nHdr) ) nHdr++;
}
nByte = nHdr+nData;
====
And here is a simple test:
====
select "23 % 3", 23 % 3;
select "12.3 % 3", 12.3 % 3;
select "12 % 2.5", 12 % 2.5;
select "23 / 3", 23 / 3;
select "12.3 / 3", 12.3 / 3;
select "12 / 2.5", 12 / 2.5;
create table ta(a text, b integer, c float);
select 'insert declared types == value types';
insert into ta(a,b,c) values('a', 1, 2.0);
select 'insert declared types != value types text';
insert into ta(a,b,c) values('b', '1', '2.0');
select 'insert declared types != value types';
insert into ta(a,b,c) values('c', 1.0, 2);
select 'update declared types == value types';
update ta set a = 'a' ,b = 1, c = 2.0 where a = 'a';
select 'update declared types != value types text';
update ta set a = 'a' ,b = '1', c = '2.0' where a = 'a';
select 'update declared types != value types';
update ta set a = 'a' ,b = 1.0, c = 2 where a = 'a';
select 'update one value declared types != value types';
update ta set b = 1.0 where a = 'a';
select 'update one value declared types != value types';
update ta set a = 49 where a = 'b';
====
Ouptut:
====
sqlite3 < test-affinity.sql
23 % 3|2
FP Remainder received non integer values 3.000000 :: 12.300000
12.3 % 3|0.0
FP Remainder received non integer values 2.500000 :: 12.000000
12 % 2.5|0.0
23 / 3|7
FP Division received non integer values 3.000000 :: 12.300000
12.3 / 3|4.1
FP Division received non integer values 2.500000 :: 12.000000
12 / 2.5|4.8
insert declared types == value types
insert declared types != value types text
Affinity applied on make record 1 : 1 : D
Affinity applied on make record 2 : 1 : E
insert declared types != value types
Affinity applied on make record 1 : 2 : D
update declared types == value types
update declared types != value types text
Affinity applied on make record 1 : 1 : D
Affinity applied on make record 2 : 1 : E
update declared types != value types
Affinity applied on make record 1 : 2 : D
update one value declared types != value types
Affinity applied on make record 1 : 2 : D
update one value declared types != value types
Affinity applied on make record 0 : 3 : B
====
(26) By Domingo (mingodad) on 2021-08-09 12:05:24 in reply to 25.2 [link] [source]
On my previous message showing changes to "src/vdbe.c" I found a small mistake and edited fixing it and extended the simple test case a bit. So if you plan to try it please check it on the sqlite forum.
(28) By Domingo (mingodad) on 2021-08-09 13:26:05 in reply to 25.2 [link] [source]
And here is the output of fossil compiled with my sqlite changes and SQLITE_PEDANTIC when executing "stash save". Ideally we should not rely on "implicit casts" (Affinity applied ...). Noticed the line showing that fossil is relying on an "implicit casting". Also I'm not sure why I've got the message about transactions "never commits". ===== fossil stash save mywork # Enter a description of what is being stashed. Lines beginning # with "#" are ignored. Stash comments are plain text except # newlines are not preserved. # # Since no default text editor is set using EDITOR or VISUAL # environment variables or the "fossil set editor" command, # and because no comment was specified using the "-m" or "-M" # command-line options, you will need to enter the comment below. # Type "." on a line by itself when you are done: testing my changes. . Affinity applied on make record 4 : 2 : C Transaction started at ./src/stash.c:573 never commits =====
(29.1) By J.M. Aranda (JMAranda) on 2021-08-10 17:19:22 edited from 29.0 in reply to 28 [link] [source]
Once I connected PARI/GP with SQLite, using C. PARI/GP is a Computer Algebra System. The program read a variable-length functional expression, evaluated it with PARI/GP, and stored it. Perhaps in these cases something similar can be done.
(30) By J.M. Aranda (JMAranda) on 2021-08-10 18:13:08 in reply to 1 [link] [source]
Online PARI/GP https://pari.math.u-bordeaux.fr/gp.html ? ( ((50.266))/2%((2816.31)/(996.7/8175.445-59.546)/((47))*((((93.614))+(((8*(19.6093/28))))))/(79.577-(178/82.7%(2020.26)-271.497%(7.495)*22.42/(892.4036)+((22)+ (...) ((4769.7))))%151))))))+98-32.1677-547/8)*5)-417%(962.3)/557.431%275+90)%(((2174.78)))-232-1-((233.40)%(4502)%(((7)))/415)) ) %1 = 25.13300000000000000000000000 The result is rational, Q. Mod function is defined over Z and Q. Mod function over R is..? Nice, really nice. https://en.wikipedia.org/wiki/Bordeaux_wine