SQLite Forum

Fail to calculate long expression
Login
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
====