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 ====