/ Check-in [491f0f9b]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:As a special case, casting '-0.0' into numeric should yield 0. Fix for ticket [674385aeba91c774].
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: 491f0f9bbddb6302536d99abd1ea481fd747ddcf6c6eaaacc0338d147b119081
User & Date: drh 2019-06-12 20:51:38
Context
2019-06-12
22:46
Adjust requirements marks and add new requirements tests. check-in: ebb81dad user: drh tags: trunk
20:51
As a special case, casting '-0.0' into numeric should yield 0. Fix for ticket [674385aeba91c774]. check-in: 491f0f9b user: drh tags: trunk
13:49
Handle expressions like "expr IS TRUE COLLATE xyz" in the same way as "expr IS TRUE". Fix for [4d01eda8115b10d1]. check-in: 5c6146b5 user: dan tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/vdbemem.c.

   688    688     pMem->u.r = sqlite3VdbeRealValue(pMem);
   689    689     MemSetTypeFlag(pMem, MEM_Real);
   690    690     return SQLITE_OK;
   691    691   }
   692    692   
   693    693   /* Compare a floating point value to an integer.  Return true if the two
   694    694   ** values are the same within the precision of the floating point value.
          695  +**
          696  +** This function assumes that i was obtained by assignment from r1.
   695    697   **
   696    698   ** For some versions of GCC on 32-bit machines, if you do the more obvious
   697    699   ** comparison of "r1==(double)i" you sometimes get an answer of false even
   698    700   ** though the r1 and (double)i values are bit-for-bit the same.
   699    701   */
   700    702   int sqlite3RealSameAsInt(double r1, sqlite3_int64 i){
   701    703     double r2 = (double)i;
   702         -  return memcmp(&r1, &r2, sizeof(r1))==0
   703         -      && i >= -2251799813685248 && i < 2251799813685248;
          704  +  return r1==0.0
          705  +      || (memcmp(&r1, &r2, sizeof(r1))==0
          706  +          && i >= -2251799813685248 && i < 2251799813685248);
   704    707   }
   705    708   
   706    709   /*
   707    710   ** Convert pMem so that it has type MEM_Real or MEM_Int.
   708    711   ** Invalidate any prior representations.
   709    712   **
   710    713   ** Every effort is made to force the conversion, even if the input

Changes to test/cast.test.

   179    179   do_test cast-1.51 {
   180    180     execsql {SELECT CAST('123.5abc' AS numeric)}
   181    181   } 123.5
   182    182   do_test cast-1.53 {
   183    183     execsql {SELECT CAST('123.5abc' AS integer)}
   184    184   } 123
   185    185   
   186         -do_test case-1.60 {
          186  +do_test cast-1.60 {
   187    187     execsql {SELECT CAST(null AS REAL)}
   188    188   } {{}}
   189         -do_test case-1.61 {
          189  +do_test cast-1.61 {
   190    190     execsql {SELECT typeof(CAST(null AS REAL))}
   191    191   } {null}
   192         -do_test case-1.62 {
          192  +do_test cast-1.62 {
   193    193     execsql {SELECT CAST(1 AS REAL)}
   194    194   } {1.0}
   195         -do_test case-1.63 {
          195  +do_test cast-1.63 {
   196    196     execsql {SELECT typeof(CAST(1 AS REAL))}
   197    197   } {real}
   198         -do_test case-1.64 {
          198  +do_test cast-1.64 {
   199    199     execsql {SELECT CAST('1' AS REAL)}
   200    200   } {1.0}
   201         -do_test case-1.65 {
          201  +do_test cast-1.65 {
   202    202     execsql {SELECT typeof(CAST('1' AS REAL))}
   203    203   } {real}
   204         -do_test case-1.66 {
          204  +do_test cast-1.66 {
   205    205     execsql {SELECT CAST('abc' AS REAL)}
   206    206   } {0.0}
   207         -do_test case-1.67 {
          207  +do_test cast-1.67 {
   208    208     execsql {SELECT typeof(CAST('abc' AS REAL))}
   209    209   } {real}
   210         -do_test case-1.68 {
          210  +do_test cast-1.68 {
   211    211     execsql {SELECT CAST(x'31' AS REAL)}
   212    212   } {1.0}
   213         -do_test case-1.69 {
          213  +do_test cast-1.69 {
   214    214     execsql {SELECT typeof(CAST(x'31' AS REAL))}
   215    215   } {real}
   216    216   
   217    217   
   218    218   # Ticket #1662.  Ignore leading spaces in numbers when casting.
   219    219   #
   220    220   do_test cast-2.1 {
................................................................................
   295    295         execsql {
   296    296           SELECT CAST(CAST(x'39323233333732303336383534373734383030' AS real)
   297    297                       AS integer)
   298    298         }
   299    299       } 9223372036854774784
   300    300     }
   301    301   }
   302         -do_test case-3.31 {
          302  +do_test cast-3.31 {
   303    303     execsql {SELECT CAST(NULL AS numeric)}
   304    304   } {{}}
   305    305   
   306    306   # Test to see if it is possible to trick SQLite into reading past 
   307    307   # the end of a blob when converting it to a number.
   308    308   do_test cast-3.32.1 {
   309    309     set blob "1234567890"
................................................................................
   364    364   } {-9223372036854775808 -9223372036854775808 -9223372036854775808}
   365    365   
   366    366   # EVIDENCE-OF: R-33990-33527 When casting to INTEGER, if the text looks
   367    367   # like a floating point value with an exponent, the exponent will be
   368    368   # ignored because it is no part of the integer prefix.
   369    369   # EVIDENCE-OF: R-24225-46995 For example, "(CAST '123e+5' AS INTEGER)"
   370    370   # results in 123, not in 12300000.
   371         -do_execsql_test case-5.3 {
          371  +do_execsql_test cast-5.3 {
   372    372     SELECT CAST('123e+5' AS INTEGER);
   373    373     SELECT CAST('123e+5' AS NUMERIC);
   374    374     SELECT CAST('123e+5' AS REAL);
   375    375   } {123 12300000 12300000.0}
   376    376   
   377    377   
   378    378   # The following does not have anything to do with the CAST operator,
   379    379   # but it does deal with affinity transformations.
   380    380   #
   381         -do_execsql_test case-6.1 {
          381  +do_execsql_test cast-6.1 {
   382    382     DROP TABLE IF EXISTS t1;
   383    383     CREATE TABLE t1(a NUMERIC);
   384    384     INSERT INTO t1 VALUES
   385    385        ('9000000000000000001'),
   386    386        ('9000000000000000001 '),
   387    387        (' 9000000000000000001'),
   388    388        (' 9000000000000000001 ');
   389    389     SELECT * FROM t1;
   390    390   } {9000000000000000001 9000000000000000001 9000000000000000001 9000000000000000001}
   391    391   
   392    392   # 2019-06-07
   393    393   # https://www.sqlite.org/src/info/4c2d7639f076aa7c
   394         -do_execsql_test case-7.1 {
          394  +do_execsql_test cast-7.1 {
   395    395     SELECT CAST('-' AS NUMERIC);
   396    396   } {0}
   397         -do_execsql_test case-7.2 {
          397  +do_execsql_test cast-7.2 {
   398    398     SELECT CAST('-0' AS NUMERIC);
   399    399   } {0}
   400         -do_execsql_test case-7.3 {
          400  +do_execsql_test cast-7.3 {
   401    401     SELECT CAST('+' AS NUMERIC);
   402    402   } {0}
   403         -do_execsql_test case-7.4 {
          403  +do_execsql_test cast-7.4 {
   404    404     SELECT CAST('/' AS NUMERIC);
   405    405   } {0}
   406    406   
   407    407   # 2019-06-07
   408    408   # https://www.sqlite.org/src/info/e8bedb2a184001bb
   409         -do_execsql_test case-7.10 {
          409  +do_execsql_test cast-7.10 {
   410    410     SELECT '' - 2851427734582196970;
   411    411   } {-2851427734582196970}
   412         -do_execsql_test case-7.11 {
          412  +do_execsql_test cast-7.11 {
   413    413     SELECT 0 - 2851427734582196970;
   414    414   } {-2851427734582196970}
   415         -do_execsql_test case-7.12 {
          415  +do_execsql_test cast-7.12 {
   416    416     SELECT '' - 1;
   417    417   } {-1}
   418    418   
   419    419   # 2019-06-10
   420    420   # https://www.sqlite.org/src/info/dd6bffbfb6e61db9
   421    421   #
   422    422   # EVIDENCE-OF: R-09295-61337 Casting a TEXT or BLOB value into NUMERIC
................................................................................
   442    442   } 0
   443    443   do_execsql_test cast-7.32 {
   444    444     SELECT CAST('.' AS numeric);
   445    445   } 0
   446    446   do_execsql_test cast-7.33 {
   447    447     SELECT -CAST('.' AS numeric);
   448    448   } 0
          449  +
          450  +# 2019-06-12
          451  +# https://www.sqlite.org/src/info/674385aeba91c774
          452  +#
          453  +do_execsql_test cast-7.40 {
          454  +  SELECT CAST('-0.0' AS numeric);
          455  +} 0
          456  +do_execsql_test cast-7.41 {
          457  +  SELECT CAST('0.0' AS numeric);
          458  +} 0
          459  +do_execsql_test cast-7.42 {
          460  +  SELECT CAST('+0.0' AS numeric);
          461  +} 0
          462  +do_execsql_test cast-7.43 {
          463  +  SELECT CAST('-1.0' AS numeric);
          464  +} -1
          465  +
   449    466   
   450    467   
   451    468   finish_test