/ Check-in [c0c90961]
Login
SQLite training in Houston TX on 2019-11-05 (details)
Part of the 2019 Tcl Conference

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: c0c90961b4fa1c1185772d04fe1915bc1a1af27ed8ddb8db1c524bf90d68ccbf
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
Hide Diffs Unified Diffs Ignore Whitespace Patch

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.
#