/ Check-in [ba6119d1]
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:Add tests for CAST expressions to e_expr.test. More to come.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: ba6119d1e9300de9ce7448cfa0acd579e8e69e41
User & Date: dan 2010-08-26 19:05:24
Context
2010-08-27
11:19
Further tests for CAST in e_expr.test. check-in: 9616df8c user: dan tags: trunk
2010-08-26
19:05
Add tests for CAST expressions to e_expr.test. More to come. check-in: ba6119d1 user: dan tags: trunk
16:46
Add EXTERN macros before entry points in tclsqlite.c. These EXTERN macros were inexplicably removed by [1f680cb37584baa106cee05] a few days ago. check-in: 8b2cf9d4 user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to test/e_expr.test.

  1374   1374       WHEN ceval(x)=w1 THEN r1 
  1375   1375       WHEN ceval(x)=w2 THEN r2 
  1376   1376       ELSE r3 END 
  1377   1377     FROM t2
  1378   1378   } {R1 R2 R3}
  1379   1379   do_test e_expr-26.1.6 { set ::evalcount } {5}
  1380   1380   
         1381  +
         1382  +#-------------------------------------------------------------------------
         1383  +# Test statements related to CAST expressions.
         1384  +#
         1385  +# EVIDENCE-OF: R-65079-31758 Application of a CAST expression is
         1386  +# different to application of a column affinity, as with a CAST
         1387  +# expression the storage class conversion is forced even if it is lossy
         1388  +# and irrreversible.
         1389  +#
         1390  +do_execsql_test e_expr-27.1.1 {
         1391  +  CREATE TABLE t3(a TEXT, b REAL, c INTEGER);
         1392  +  INSERT INTO t3 VALUES(X'555655', '1.23abc', 4.5);
         1393  +  SELECT typeof(a), a, typeof(b), b, typeof(c), c FROM t3;
         1394  +} {blob UVU text 1.23abc real 4.5}
         1395  +do_execsql_test e_expr-27.1.2 {
         1396  +  SELECT 
         1397  +    typeof(CAST(X'555655' as TEXT)), CAST(X'555655' as TEXT),
         1398  +    typeof(CAST('1.23abc' as REAL)), CAST('1.23abc' as REAL),
         1399  +    typeof(CAST(4.5 as INTEGER)), CAST(4.5 as INTEGER)
         1400  +} {text UVU real 1.23 integer 4}
         1401  +
         1402  +proc do_expr_test {tn expr type value} {
         1403  +  uplevel do_execsql_test $tn [list "SELECT typeof($expr), $expr"] [
         1404  +    list [list $type $value]
         1405  +  ]
         1406  +}
         1407  +proc do_qexpr_test {tn expr value} {
         1408  +  uplevel do_execsql_test $tn [list "SELECT quote($expr)"] [list $value]
         1409  +}
         1410  +
         1411  +# EVIDENCE-OF: R-27225-65050 If the value of <expr> is NULL, then
         1412  +# the result of the CAST expression is also NULL.
         1413  +#
         1414  +do_expr_test e_expr-27.2.1 { CAST(NULL AS integer) } null {}
         1415  +do_expr_test e_expr-27.2.2 { CAST(NULL AS text) }    null {}
         1416  +do_expr_test e_expr-27.2.3 { CAST(NULL AS blob) }    null {}
         1417  +do_expr_test e_expr-27.2.4 { CAST(NULL AS number) }  null {}
         1418  +
         1419  +# EVIDENCE-OF: R-31076-23575 Casting a value to a <type-name> with
         1420  +# no affinity causes the value to be converted into a BLOB.
         1421  +#
         1422  +do_expr_test e_expr-27.3.1 { CAST('abc' AS blob)       } blob abc
         1423  +do_expr_test e_expr-27.3.2 { CAST('def' AS shobblob_x) } blob def
         1424  +do_expr_test e_expr-27.3.3 { CAST('ghi' AS abbLOb10)   } blob ghi
         1425  +
         1426  +# EVIDENCE-OF: R-22956-37754 Casting to a BLOB consists of first casting
         1427  +# the value to TEXT in the encoding of the database connection, then
         1428  +# interpreting the resulting byte sequence as a BLOB instead of as TEXT.
         1429  +#
         1430  +do_qexpr_test e_expr-27.4.1 { CAST('ghi' AS blob) } X'676869'
         1431  +do_qexpr_test e_expr-27.4.2 { CAST(456 AS blob) }   X'343536'
         1432  +do_qexpr_test e_expr-27.4.3 { CAST(1.78 AS blob) }  X'312E3738'
         1433  +rename db db2
         1434  +sqlite3 db :memory:
         1435  +db eval { PRAGMA encoding = 'utf-16le' }
         1436  +do_qexpr_test e_expr-27.4.4 { CAST('ghi' AS blob) } X'670068006900'
         1437  +do_qexpr_test e_expr-27.4.5 { CAST(456 AS blob) }   X'340035003600'
         1438  +do_qexpr_test e_expr-27.4.6 { CAST(1.78 AS blob) }  X'31002E0037003800'
         1439  +db close
         1440  +sqlite3 db :memory:
         1441  +db eval { PRAGMA encoding = 'utf-16be' }
         1442  +do_qexpr_test e_expr-27.4.7 { CAST('ghi' AS blob) } X'006700680069'
         1443  +do_qexpr_test e_expr-27.4.8 { CAST(456 AS blob) }   X'003400350036'
         1444  +do_qexpr_test e_expr-27.4.9 { CAST(1.78 AS blob) }  X'0031002E00370038'
         1445  +db close
         1446  +rename db2 db
         1447  +
         1448  +# EVIDENCE-OF: R-04207-37981 To cast a BLOB value to TEXT, the sequence
         1449  +# of bytes that make up the BLOB is interpreted as text encoded using
         1450  +# the database encoding.
         1451  +#
         1452  +do_expr_test e_expr-28.1.1 { CAST (X'676869' AS text) } text ghi
         1453  +do_expr_test e_expr-28.1.2 { CAST (X'670068006900' AS text) } text g
         1454  +rename db db2
         1455  +sqlite3 db :memory:
         1456  +db eval { PRAGMA encoding = 'utf-16le' }
         1457  +do_expr_test e_expr-28.1.3 { CAST (X'676869' AS text) == 'ghi' } integer 0
         1458  +do_expr_test e_expr-28.1.4 { CAST (X'670068006900' AS text) } text ghi
         1459  +db close
         1460  +rename db2 db
         1461  +
         1462  +# EVIDENCE-OF: R-22235-47006 Casting an INTEGER or REAL value into TEXT
         1463  +# renders the value as if via sqlite3_snprintf() except that the
         1464  +# resulting TEXT uses the encoding of the database connection.
         1465  +#
         1466  +do_expr_test e_expr-28.2.1 { CAST (1 AS text)   }     text 1
         1467  +do_expr_test e_expr-28.2.2 { CAST (45 AS text)  }     text 45
         1468  +do_expr_test e_expr-28.2.3 { CAST (-45 AS text) }     text -45
         1469  +do_expr_test e_expr-28.2.4 { CAST (8.8 AS text)    }  text 8.8
         1470  +do_expr_test e_expr-28.2.5 { CAST (2.3e+5 AS text) }  text 230000.0
         1471  +do_expr_test e_expr-28.2.6 { CAST (-2.3e-5 AS text) } text -2.3e-05
         1472  +do_expr_test e_expr-28.2.7 { CAST (0.0 AS text) }     text 0.0
         1473  +do_expr_test e_expr-28.2.7 { CAST (0 AS text) }       text 0
         1474  +
         1475  +# EVIDENCE-OF: R-26346-36443 When casting a BLOB value to a REAL, the
         1476  +# value is first converted to TEXT.
         1477  +#
         1478  +do_expr_test e_expr-29.1.1 { CAST (X'312E3233' AS REAL) } real 1.23
         1479  +do_expr_test e_expr-29.1.2 { CAST (X'3233302E30' AS REAL) } real 230.0
         1480  +do_expr_test e_expr-29.1.3 { CAST (X'2D392E3837' AS REAL) } real -9.87
         1481  +do_expr_test e_expr-29.1.4 { CAST (X'302E30303031' AS REAL) } real 0.0001
         1482  +rename db db2
         1483  +sqlite3 db :memory:
         1484  +db eval { PRAGMA encoding = 'utf-16le' }
         1485  +do_expr_test e_expr-29.1.1 { 
         1486  +    CAST (X'31002E0032003300' AS REAL) } real 1.23
         1487  +do_expr_test e_expr-29.1.2 { 
         1488  +    CAST (X'3200330030002E003000' AS REAL) } real 230.0
         1489  +do_expr_test e_expr-29.1.3 { 
         1490  +    CAST (X'2D0039002E0038003700' AS REAL) } real -9.87
         1491  +do_expr_test e_expr-29.1.4 { 
         1492  +    CAST (X'30002E003000300030003100' AS REAL) } real 0.0001
         1493  +db close
         1494  +rename db2 db
         1495  +
  1381   1496   finish_test
         1497  +