Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Make sure a CAST to "NUMERIC" results in an integer if the value can be losslessly expressed as an integer, as the documentation requires. Ticket [dd6bffbfb6e61db9]. |
---|---|
Downloads: | Tarball | ZIP archive | SQL archive |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA3-256: |
c0c90961b4fa1c1185772d04fe1915bc |
User & Date: | drh 2019-06-10 18:33:16 |
Context
2019-06-10
| ||
19:07 | Improvements to the documentation for the xAccess method of the VFS. Ticket [5e0423b058fa5adf] (check-in: 7078d77e user: drh tags: trunk) | |
18:33 | Make sure a CAST to "NUMERIC" results in an integer if the value can be losslessly expressed as an integer, as the documentation requires. Ticket [dd6bffbfb6e61db9]. (check-in: c0c90961 user: drh tags: trunk) | |
15:34 | Handle renaming a column or table when the schema contains a (meaningless) index on the constant expression ('text' IN ()) or ('text' NOT IN()). Fix for [fd76310a]. (check-in: 567b1309 user: dan tags: trunk) | |
Changes
Changes to src/vdbe.c.
281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 |
** floating point value of rValue. Return true and set *piValue to the ** integer value if the string is in range to be an integer. Otherwise, ** return false. */ static int alsoAnInt(Mem *pRec, double rValue, i64 *piValue){ i64 iValue = (double)rValue; if( sqlite3RealSameAsInt(rValue,iValue) ){ testcase( iValue<-2251799813685248 ); testcase( iValue==-2251799813685248 ); testcase( iValue==-2251799813685247 ); testcase( iValue>-2251799813685247 && iValue<+2251799813685247 ); testcase( iValue==+2251799813685247 ); testcase( iValue==+2251799813685248 ); testcase( iValue>+2251799813685248 ); if( iValue > -2251799813685248 && iValue < 2251799813685248 ){ *piValue = iValue; return 1; } } return 0==sqlite3Atoi64(pRec->z, piValue, pRec->n, pRec->enc); } /* ** Try to convert a value into a numeric representation if we can ** do so without loss of information. In other words, if the string |
< < < < < < < < | | < |
281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 |
** floating point value of rValue. Return true and set *piValue to the ** integer value if the string is in range to be an integer. Otherwise, ** return false. */ static int alsoAnInt(Mem *pRec, double rValue, i64 *piValue){ i64 iValue = (double)rValue; if( sqlite3RealSameAsInt(rValue,iValue) ){ *piValue = iValue; return 1; } return 0==sqlite3Atoi64(pRec->z, piValue, pRec->n, pRec->enc); } /* ** Try to convert a value into a numeric representation if we can ** do so without loss of information. In other words, if the string |
Changes to src/vdbemem.c.
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
...
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
|
** ** For some versions of GCC on 32-bit machines, if you do the more obvious ** comparison of "r1==(double)i" you sometimes get an answer of false even ** though the r1 and (double)i values are bit-for-bit the same. */ int sqlite3RealSameAsInt(double r1, sqlite3_int64 i){ double r2 = (double)i; return memcmp(&r1, &r2, sizeof(r1))==0; } /* ** Convert pMem so that it has type MEM_Real or MEM_Int. ** Invalidate any prior representations. ** ** Every effort is made to force the conversion, even if the input ................................................................................ testcase( pMem->flags & MEM_Null ); if( (pMem->flags & (MEM_Int|MEM_Real|MEM_IntReal|MEM_Null))==0 ){ int rc; sqlite3_int64 ix; assert( (pMem->flags & (MEM_Blob|MEM_Str))!=0 ); assert( pMem->db==0 || sqlite3_mutex_held(pMem->db->mutex) ); rc = sqlite3AtoF(pMem->z, &pMem->u.r, pMem->n, pMem->enc); if( rc<=0 ){ if( rc==0 && sqlite3Atoi64(pMem->z, &ix, pMem->n, pMem->enc)<=1 ){ pMem->u.i = ix; MemSetTypeFlag(pMem, MEM_Int); }else{ MemSetTypeFlag(pMem, MEM_Real); } }else if( rc==1 && sqlite3Atoi64(pMem->z, &ix, pMem->n, pMem->enc)==0 ){ pMem->u.i = ix; MemSetTypeFlag(pMem, MEM_Int); }else{ MemSetTypeFlag(pMem, MEM_Real); } } assert( (pMem->flags & (MEM_Int|MEM_Real|MEM_IntReal|MEM_Null))!=0 ); |
|
>
<
|
<
<
<
<
<
<
>
>
|
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
...
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
|
** ** For some versions of GCC on 32-bit machines, if you do the more obvious ** comparison of "r1==(double)i" you sometimes get an answer of false even ** though the r1 and (double)i values are bit-for-bit the same. */ int sqlite3RealSameAsInt(double r1, sqlite3_int64 i){ double r2 = (double)i; return memcmp(&r1, &r2, sizeof(r1))==0 && i > -2251799813685248 && i < 2251799813685248; } /* ** Convert pMem so that it has type MEM_Real or MEM_Int. ** Invalidate any prior representations. ** ** Every effort is made to force the conversion, even if the input ................................................................................ testcase( pMem->flags & MEM_Null ); if( (pMem->flags & (MEM_Int|MEM_Real|MEM_IntReal|MEM_Null))==0 ){ int rc; sqlite3_int64 ix; assert( (pMem->flags & (MEM_Blob|MEM_Str))!=0 ); assert( pMem->db==0 || sqlite3_mutex_held(pMem->db->mutex) ); rc = sqlite3AtoF(pMem->z, &pMem->u.r, pMem->n, pMem->enc); if( ((rc==0 || rc==1) && sqlite3Atoi64(pMem->z, &ix, pMem->n, pMem->enc)<=1) || sqlite3RealSameAsInt(pMem->u.r, (ix = (i64)pMem->u.r)) ){ pMem->u.i = ix; MemSetTypeFlag(pMem, MEM_Int); }else{ MemSetTypeFlag(pMem, MEM_Real); } } assert( (pMem->flags & (MEM_Int|MEM_Real|MEM_IntReal|MEM_Null))!=0 ); |
Changes to test/cast.test.
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
...
410
411
412
413
414
415
416
417
418
|
# like a floating point value with an exponent, the exponent will be
# ignored because it is no part of the integer prefix.
# EVIDENCE-OF: R-24225-46995 For example, "(CAST '123e+5' AS INTEGER)"
# results in 123, not in 12300000.
do_execsql_test case-5.3 {
SELECT CAST('123e+5' AS INTEGER);
SELECT CAST('123e+5' AS NUMERIC);
} {123 12300000.0}
# The following does not have anything to do with the CAST operator,
# but it does deal with affinity transformations.
#
do_execsql_test case-6.1 {
DROP TABLE IF EXISTS t1;
................................................................................
} {-2851427734582196970}
do_execsql_test case-7.11 {
SELECT 0 - 2851427734582196970;
} {-2851427734582196970}
do_execsql_test case-7.12 {
SELECT '' - 1;
} {-1}
finish_test
|
>
|
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
|
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
...
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
|
# like a floating point value with an exponent, the exponent will be # ignored because it is no part of the integer prefix. # EVIDENCE-OF: R-24225-46995 For example, "(CAST '123e+5' AS INTEGER)" # results in 123, not in 12300000. do_execsql_test case-5.3 { SELECT CAST('123e+5' AS INTEGER); SELECT CAST('123e+5' AS NUMERIC); SELECT CAST('123e+5' AS REAL); } {123 12300000 12300000.0} # The following does not have anything to do with the CAST operator, # but it does deal with affinity transformations. # do_execsql_test case-6.1 { DROP TABLE IF EXISTS t1; ................................................................................ } {-2851427734582196970} do_execsql_test case-7.11 { SELECT 0 - 2851427734582196970; } {-2851427734582196970} do_execsql_test case-7.12 { SELECT '' - 1; } {-1} # 2019-06-10 # https://www.sqlite.org/src/info/dd6bffbfb6e61db9 # # EVIDENCE-OF: R-09295-61337 Casting a TEXT or BLOB value into NUMERIC # first does a forced conversion into REAL but then further converts the # result into INTEGER if and only if the conversion from REAL to INTEGER # is lossless and reversible. # do_execsql_test cast-7.20 { DROP TABLE IF EXISTS t0; CREATE TABLE t0 (c0 TEXT); INSERT INTO t0(c0) VALUES ('1.0'); SELECT CAST(c0 AS NUMERIC) FROM t0; } {1} finish_test |
Changes to test/e_expr.test.
1646
1647
1648
1649
1650
1651
1652
1653
1654
1655
1656
1657
1658
1659
1660
....
1705
1706
1707
1708
1709
1710
1711
1712
1713
1714
1715
1716
1717
1718
1719
1720
|
# EVIDENCE-OF: R-09295-61337 Casting a TEXT or BLOB value into NUMERIC # first does a forced conversion into REAL but then further converts the # result into INTEGER if and only if the conversion from REAL to INTEGER # is lossless and reversible. # do_expr_test e_expr-32.1.1 { CAST('45' AS NUMERIC) } integer 45 do_expr_test e_expr-32.1.2 { CAST('45.0' AS NUMERIC) } real 45.0 do_expr_test e_expr-32.1.3 { CAST('45.2' AS NUMERIC) } real 45.2 do_expr_test e_expr-32.1.4 { CAST('11abc' AS NUMERIC) } integer 11 do_expr_test e_expr-32.1.5 { CAST('11.1abc' AS NUMERIC) } real 11.1 # EVIDENCE-OF: R-30347-18702 Casting a REAL or INTEGER value to NUMERIC # is a no-op, even if a real value could be losslessly converted to an # integer. ................................................................................ integer 9223372036854775807 \ integer 9223372036854775807 \ integer 9223372036854775807 \ real 9.22337203685478e+18 \ real 9.22337203685478e+18 \ real 9.22337203685478e+18 \ real 9.22337203685478e+18 \ real -5.0 \ real -5.0 \ ] # EVIDENCE-OF: R-64550-29191 Note that the result from casting any # non-BLOB value into a BLOB and the result from casting any BLOB value # into a non-BLOB value may be different depending on whether the # database encoding is UTF-8, UTF-16be, or UTF-16le. # |
|
|
|
|
1646
1647
1648
1649
1650
1651
1652
1653
1654
1655
1656
1657
1658
1659
1660
....
1705
1706
1707
1708
1709
1710
1711
1712
1713
1714
1715
1716
1717
1718
1719
1720
|
# EVIDENCE-OF: R-09295-61337 Casting a TEXT or BLOB value into NUMERIC # first does a forced conversion into REAL but then further converts the # result into INTEGER if and only if the conversion from REAL to INTEGER # is lossless and reversible. # do_expr_test e_expr-32.1.1 { CAST('45' AS NUMERIC) } integer 45 do_expr_test e_expr-32.1.2 { CAST('45.0' AS NUMERIC) } integer 45 do_expr_test e_expr-32.1.3 { CAST('45.2' AS NUMERIC) } real 45.2 do_expr_test e_expr-32.1.4 { CAST('11abc' AS NUMERIC) } integer 11 do_expr_test e_expr-32.1.5 { CAST('11.1abc' AS NUMERIC) } real 11.1 # EVIDENCE-OF: R-30347-18702 Casting a REAL or INTEGER value to NUMERIC # is a no-op, even if a real value could be losslessly converted to an # integer. ................................................................................ integer 9223372036854775807 \ integer 9223372036854775807 \ integer 9223372036854775807 \ real 9.22337203685478e+18 \ real 9.22337203685478e+18 \ real 9.22337203685478e+18 \ real 9.22337203685478e+18 \ integer -5 \ integer -5 \ ] # EVIDENCE-OF: R-64550-29191 Note that the result from casting any # non-BLOB value into a BLOB and the result from casting any BLOB value # into a non-BLOB value may be different depending on whether the # database encoding is UTF-8, UTF-16be, or UTF-16le. # |