000001  # 2005 June 25
000002  #
000003  # The author disclaims copyright to this source code.  In place of
000004  # a legal notice, here is a blessing:
000005  #
000006  #    May you do good and not evil.
000007  #    May you find forgiveness for yourself and forgive others.
000008  #    May you share freely, never taking more than you give.
000009  #
000010  #***********************************************************************
000011  # This file implements regression tests for SQLite library.  The
000012  # focus of this file is testing the CAST operator.
000013  #
000014  # $Id: cast.test,v 1.10 2008/11/06 15:33:04 drh Exp $
000015  
000016  set testdir [file dirname $argv0]
000017  source $testdir/tester.tcl
000018  
000019  # Only run these tests if the build includes the CAST operator
000020  ifcapable !cast {
000021    finish_test
000022    return
000023  }
000024  
000025  # Tests for the CAST( AS blob), CAST( AS text) and CAST( AS numeric) built-ins
000026  #
000027  ifcapable bloblit {
000028    do_test cast-1.1 {
000029      execsql {SELECT x'616263'}
000030    } abc
000031    do_test cast-1.2 {
000032      execsql {SELECT typeof(x'616263')}
000033    } blob
000034    do_test cast-1.3 {
000035      execsql {SELECT CAST(x'616263' AS text)}
000036    } abc
000037    do_test cast-1.4 {
000038      execsql {SELECT typeof(CAST(x'616263' AS text))}
000039    } text
000040    do_test cast-1.5 {
000041      execsql {SELECT CAST(x'616263' AS numeric)}
000042    } 0
000043    do_test cast-1.6 {
000044      execsql {SELECT typeof(CAST(x'616263' AS numeric))}
000045    } integer
000046    do_test cast-1.7 {
000047      execsql {SELECT CAST(x'616263' AS blob)}
000048    } abc
000049    do_test cast-1.8 {
000050      execsql {SELECT typeof(CAST(x'616263' AS blob))}
000051    } blob
000052    do_test cast-1.9 {
000053      execsql {SELECT CAST(x'616263' AS integer)}
000054    } 0
000055    do_test cast-1.10 {
000056      execsql {SELECT typeof(CAST(x'616263' AS integer))}
000057    } integer
000058  }
000059  do_test cast-1.11 {
000060    execsql {SELECT null}
000061  } {{}}
000062  do_test cast-1.12 {
000063    execsql {SELECT typeof(NULL)}
000064  } null
000065  do_test cast-1.13 {
000066    execsql {SELECT CAST(NULL AS text)}
000067  } {{}}
000068  do_test cast-1.14 {
000069    execsql {SELECT typeof(CAST(NULL AS text))}
000070  } null
000071  do_test cast-1.15 {
000072    execsql {SELECT CAST(NULL AS numeric)}
000073  } {{}}
000074  do_test cast-1.16 {
000075    execsql {SELECT typeof(CAST(NULL AS numeric))}
000076  } null
000077  do_test cast-1.17 {
000078    execsql {SELECT CAST(NULL AS blob)}
000079  } {{}}
000080  do_test cast-1.18 {
000081    execsql {SELECT typeof(CAST(NULL AS blob))}
000082  } null
000083  do_test cast-1.19 {
000084    execsql {SELECT CAST(NULL AS integer)}
000085  } {{}}
000086  do_test cast-1.20 {
000087    execsql {SELECT typeof(CAST(NULL AS integer))}
000088  } null
000089  do_test cast-1.21 {
000090    execsql {SELECT 123}
000091  } {123}
000092  do_test cast-1.22 {
000093    execsql {SELECT typeof(123)}
000094  } integer
000095  do_test cast-1.23 {
000096    execsql {SELECT CAST(123 AS text)}
000097  } {123}
000098  do_test cast-1.24 {
000099    execsql {SELECT typeof(CAST(123 AS text))}
000100  } text
000101  do_test cast-1.25 {
000102    execsql {SELECT CAST(123 AS numeric)}
000103  } 123
000104  do_test cast-1.26 {
000105    execsql {SELECT typeof(CAST(123 AS numeric))}
000106  } integer
000107  do_test cast-1.27 {
000108    execsql {SELECT CAST(123 AS blob)}
000109  } {123}
000110  do_test cast-1.28 {
000111    execsql {SELECT typeof(CAST(123 AS blob))}
000112  } blob
000113  do_test cast-1.29 {
000114    execsql {SELECT CAST(123 AS integer)}
000115  } {123}
000116  do_test cast-1.30 {
000117    execsql {SELECT typeof(CAST(123 AS integer))}
000118  } integer
000119  do_test cast-1.31 {
000120    execsql {SELECT 123.456}
000121  } {123.456}
000122  do_test cast-1.32 {
000123    execsql {SELECT typeof(123.456)}
000124  } real
000125  do_test cast-1.33 {
000126    execsql {SELECT CAST(123.456 AS text)}
000127  } {123.456}
000128  do_test cast-1.34 {
000129    execsql {SELECT typeof(CAST(123.456 AS text))}
000130  } text
000131  do_test cast-1.35 {
000132    execsql {SELECT CAST(123.456 AS numeric)}
000133  } 123.456
000134  do_test cast-1.36 {
000135    execsql {SELECT typeof(CAST(123.456 AS numeric))}
000136  } real
000137  do_test cast-1.37 {
000138    execsql {SELECT CAST(123.456 AS blob)}
000139  } {123.456}
000140  do_test cast-1.38 {
000141    execsql {SELECT typeof(CAST(123.456 AS blob))}
000142  } blob
000143  do_test cast-1.39 {
000144    execsql {SELECT CAST(123.456 AS integer)}
000145  } {123}
000146  do_test cast-1.38 {
000147    execsql {SELECT typeof(CAST(123.456 AS integer))}
000148  } integer
000149  do_test cast-1.41 {
000150    execsql {SELECT '123abc'}
000151  } {123abc}
000152  do_test cast-1.42 {
000153    execsql {SELECT typeof('123abc')}
000154  } text
000155  do_test cast-1.43 {
000156    execsql {SELECT CAST('123abc' AS text)}
000157  } {123abc}
000158  do_test cast-1.44 {
000159    execsql {SELECT typeof(CAST('123abc' AS text))}
000160  } text
000161  do_test cast-1.45 {
000162    execsql {SELECT CAST('123abc' AS numeric)}
000163  } 123
000164  do_test cast-1.46 {
000165    execsql {SELECT typeof(CAST('123abc' AS numeric))}
000166  } integer
000167  do_test cast-1.47 {
000168    execsql {SELECT CAST('123abc' AS blob)}
000169  } {123abc}
000170  do_test cast-1.48 {
000171    execsql {SELECT typeof(CAST('123abc' AS blob))}
000172  } blob
000173  do_test cast-1.49 {
000174    execsql {SELECT CAST('123abc' AS integer)}
000175  } 123
000176  do_test cast-1.50 {
000177    execsql {SELECT typeof(CAST('123abc' AS integer))}
000178  } integer
000179  do_test cast-1.51 {
000180    execsql {SELECT CAST('123.5abc' AS numeric)}
000181  } 123.5
000182  do_test cast-1.53 {
000183    execsql {SELECT CAST('123.5abc' AS integer)}
000184  } 123
000185  
000186  do_test cast-1.60 {
000187    execsql {SELECT CAST(null AS REAL)}
000188  } {{}}
000189  do_test cast-1.61 {
000190    execsql {SELECT typeof(CAST(null AS REAL))}
000191  } {null}
000192  do_test cast-1.62 {
000193    execsql {SELECT CAST(1 AS REAL)}
000194  } {1.0}
000195  do_test cast-1.63 {
000196    execsql {SELECT typeof(CAST(1 AS REAL))}
000197  } {real}
000198  do_test cast-1.64 {
000199    execsql {SELECT CAST('1' AS REAL)}
000200  } {1.0}
000201  do_test cast-1.65 {
000202    execsql {SELECT typeof(CAST('1' AS REAL))}
000203  } {real}
000204  do_test cast-1.66 {
000205    execsql {SELECT CAST('abc' AS REAL)}
000206  } {0.0}
000207  do_test cast-1.67 {
000208    execsql {SELECT typeof(CAST('abc' AS REAL))}
000209  } {real}
000210  do_test cast-1.68 {
000211    execsql {SELECT CAST(x'31' AS REAL)}
000212  } {1.0}
000213  do_test cast-1.69 {
000214    execsql {SELECT typeof(CAST(x'31' AS REAL))}
000215  } {real}
000216  
000217  
000218  # Ticket #1662.  Ignore leading spaces in numbers when casting.
000219  #
000220  do_test cast-2.1 {
000221    execsql {SELECT CAST('   123' AS integer)}
000222  } 123
000223  do_test cast-2.2 {
000224    execsql {SELECT CAST('   -123.456' AS real)}
000225  } -123.456
000226  
000227  # ticket #2364.  Use full percision integers if possible when casting
000228  # to numeric.  Do not fallback to real (and the corresponding 48-bit
000229  # mantissa) unless absolutely necessary.
000230  #
000231  do_test cast-3.1 {
000232    execsql {SELECT CAST(9223372036854774800 AS integer)}
000233  } 9223372036854774800
000234  do_test cast-3.2 {
000235    execsql {SELECT CAST(9223372036854774800 AS numeric)}
000236  } 9223372036854774800
000237  do_realnum_test cast-3.3 {
000238    execsql {SELECT CAST(9223372036854774800 AS real)}
000239  } 9.22337203685477e+18
000240  do_test cast-3.4 {
000241    execsql {SELECT CAST(CAST(9223372036854774800 AS real) AS integer)}
000242  } 9223372036854774784
000243  do_test cast-3.5 {
000244    execsql {SELECT CAST(-9223372036854774800 AS integer)}
000245  } -9223372036854774800
000246  do_test cast-3.6 {
000247    execsql {SELECT CAST(-9223372036854774800 AS numeric)}
000248  } -9223372036854774800
000249  do_realnum_test cast-3.7 {
000250    execsql {SELECT CAST(-9223372036854774800 AS real)}
000251  } -9.22337203685477e+18
000252  do_test cast-3.8 {
000253    execsql {SELECT CAST(CAST(-9223372036854774800 AS real) AS integer)}
000254  } -9223372036854774784
000255  do_test cast-3.11 {
000256    execsql {SELECT CAST('9223372036854774800' AS integer)}
000257  } 9223372036854774800
000258  do_test cast-3.12 {
000259    execsql {SELECT CAST('9223372036854774800' AS numeric)}
000260  } 9223372036854774800
000261  do_realnum_test cast-3.13 {
000262    execsql {SELECT CAST('9223372036854774800' AS real)}
000263  } 9.22337203685477e+18
000264  ifcapable long_double {
000265    do_test cast-3.14 {
000266      execsql {SELECT CAST(CAST('9223372036854774800' AS real) AS integer)}
000267    } 9223372036854774784
000268  }
000269  do_test cast-3.15 {
000270    execsql {SELECT CAST('-9223372036854774800' AS integer)}
000271  } -9223372036854774800
000272  do_test cast-3.16 {
000273    execsql {SELECT CAST('-9223372036854774800' AS numeric)}
000274  } -9223372036854774800
000275  do_realnum_test cast-3.17 {
000276    execsql {SELECT CAST('-9223372036854774800' AS real)}
000277  } -9.22337203685477e+18
000278  ifcapable long_double {
000279    do_test cast-3.18 {
000280      execsql {SELECT CAST(CAST('-9223372036854774800' AS real) AS integer)}
000281    } -9223372036854774784
000282  }
000283  if {[db eval {PRAGMA encoding}]=="UTF-8"} {
000284    do_test cast-3.21 {
000285      execsql {SELECT CAST(x'39323233333732303336383534373734383030' AS integer)}
000286    } 9223372036854774800
000287    do_test cast-3.22 {
000288      execsql {SELECT CAST(x'39323233333732303336383534373734383030' AS numeric)}
000289    } 9223372036854774800
000290    do_realnum_test cast-3.23 {
000291      execsql {SELECT CAST(x'39323233333732303336383534373734383030' AS real)}
000292    } 9.22337203685477e+18
000293    ifcapable long_double {
000294      do_test cast-3.24 {
000295        execsql {
000296          SELECT CAST(CAST(x'39323233333732303336383534373734383030' AS real)
000297                      AS integer)
000298        }
000299      } 9223372036854774784
000300    }
000301  }
000302  do_test cast-3.31 {
000303    execsql {SELECT CAST(NULL AS numeric)}
000304  } {{}}
000305  
000306  # Test to see if it is possible to trick SQLite into reading past 
000307  # the end of a blob when converting it to a number.
000308  do_test cast-3.32.1 {
000309    set blob "1234567890"
000310    set DB [sqlite3_connection_pointer db]
000311    set ::STMT [sqlite3_prepare $DB {SELECT CAST(? AS real)} -1 TAIL]
000312    sqlite3_bind_blob -static $::STMT 1 $blob 5
000313    sqlite3_step $::STMT
000314  } {SQLITE_ROW}
000315  do_test cast-3.32.2 {
000316    sqlite3_column_int $::STMT 0
000317  } {12345}
000318  do_test cast-3.32.3 {
000319    sqlite3_finalize $::STMT
000320  } {SQLITE_OK}
000321  
000322  
000323  do_test cast-4.1 {
000324    db eval {
000325      CREATE TABLE t1(a);
000326      INSERT INTO t1 VALUES('abc');
000327      SELECT a, CAST(a AS integer) FROM t1;
000328    }
000329  } {abc 0}
000330  do_test cast-4.2 {
000331    db eval {
000332      SELECT CAST(a AS integer), a FROM t1;
000333    }
000334  } {0 abc}
000335  do_test cast-4.3 {
000336    db eval {
000337      SELECT a, CAST(a AS integer), a FROM t1;
000338    }
000339  } {abc 0 abc}
000340  do_test cast-4.4 {
000341    db eval {
000342      SELECT CAST(a AS integer), a, CAST(a AS real), a FROM t1;
000343    }
000344  } {0 abc 0.0 abc}
000345  
000346  # Added 2018-01-26
000347  #
000348  # EVIDENCE-OF: R-48741-32454 If the prefix integer is greater than
000349  # +9223372036854775807 then the result of the cast is exactly
000350  # +9223372036854775807.
000351  do_execsql_test cast-5.1 {
000352    SELECT CAST('9223372036854775808' AS integer);
000353    SELECT CAST('  +000009223372036854775808' AS integer);
000354    SELECT CAST('12345678901234567890123' AS INTEGER);
000355  } {9223372036854775807 9223372036854775807 9223372036854775807}
000356  
000357  # EVIDENCE-OF: R-06028-16857 Similarly, if the prefix integer is less
000358  # than -9223372036854775808 then the result of the cast is exactly
000359  # -9223372036854775808.
000360  do_execsql_test cast-5.2 {
000361    SELECT CAST('-9223372036854775808' AS integer);
000362    SELECT CAST('-9223372036854775809' AS integer);
000363    SELECT CAST('-12345678901234567890123' AS INTEGER);
000364  } {-9223372036854775808 -9223372036854775808 -9223372036854775808}
000365  
000366  # EVIDENCE-OF: R-33990-33527 When casting to INTEGER, if the text looks
000367  # like a floating point value with an exponent, the exponent will be
000368  # ignored because it is no part of the integer prefix.
000369  # EVIDENCE-OF: R-24225-46995 For example, "(CAST '123e+5' AS INTEGER)"
000370  # results in 123, not in 12300000.
000371  do_execsql_test cast-5.3 {
000372    SELECT CAST('123e+5' AS INTEGER);
000373    SELECT CAST('123e+5' AS NUMERIC);
000374    SELECT CAST('123e+5' AS REAL);
000375  } {123 12300000 12300000.0}
000376  
000377  
000378  # The following does not have anything to do with the CAST operator,
000379  # but it does deal with affinity transformations.
000380  #
000381  do_execsql_test cast-6.1 {
000382    DROP TABLE IF EXISTS t1;
000383    CREATE TABLE t1(a NUMERIC);
000384    INSERT INTO t1 VALUES
000385       ('9000000000000000001'),
000386       ('9000000000000000001 '),
000387       (' 9000000000000000001'),
000388       (' 9000000000000000001 ');
000389    SELECT * FROM t1;
000390  } {9000000000000000001 9000000000000000001 9000000000000000001 9000000000000000001}
000391  
000392  # 2019-06-07
000393  # https://www.sqlite.org/src/info/4c2d7639f076aa7c
000394  do_execsql_test cast-7.1 {
000395    SELECT CAST('-' AS NUMERIC);
000396  } {0}
000397  do_execsql_test cast-7.2 {
000398    SELECT CAST('-0' AS NUMERIC);
000399  } {0}
000400  do_execsql_test cast-7.3 {
000401    SELECT CAST('+' AS NUMERIC);
000402  } {0}
000403  do_execsql_test cast-7.4 {
000404    SELECT CAST('/' AS NUMERIC);
000405  } {0}
000406  
000407  # 2019-06-07
000408  # https://www.sqlite.org/src/info/e8bedb2a184001bb
000409  do_execsql_test cast-7.10 {
000410    SELECT '' - 2851427734582196970;
000411  } {-2851427734582196970}
000412  do_execsql_test cast-7.11 {
000413    SELECT 0 - 2851427734582196970;
000414  } {-2851427734582196970}
000415  do_execsql_test cast-7.12 {
000416    SELECT '' - 1;
000417  } {-1}
000418  
000419  # 2019-06-10
000420  # https://www.sqlite.org/src/info/dd6bffbfb6e61db9
000421  #
000422  # EVIDENCE-OF: R-55084-10555 Casting a TEXT or BLOB value into NUMERIC
000423  # yields either an INTEGER or a REAL result.
000424  #
000425  do_execsql_test cast-7.20 {
000426    DROP TABLE IF EXISTS t0;
000427    CREATE TABLE t0 (c0 TEXT);
000428    INSERT INTO t0(c0) VALUES ('1.0');
000429    SELECT CAST(c0 AS NUMERIC) FROM t0;
000430  } {1}
000431  
000432  # 2019-06-10
000433  # https://sqlite.org/src/info/27de823723a41df45af3
000434  #
000435  do_execsql_test cast-7.30 {
000436    SELECT -'.';
000437  } 0
000438  do_execsql_test cast-7.31 {
000439    SELECT '.'+0;
000440  } 0
000441  do_execsql_test cast-7.32 {
000442    SELECT CAST('.' AS numeric);
000443  } 0
000444  do_execsql_test cast-7.33 {
000445    SELECT -CAST('.' AS numeric);
000446  } 0
000447  
000448  # 2019-06-12
000449  # https://www.sqlite.org/src/info/674385aeba91c774
000450  #
000451  do_execsql_test cast-7.40 {
000452    SELECT CAST('-0.0' AS numeric);
000453  } 0
000454  do_execsql_test cast-7.41 {
000455    SELECT CAST('0.0' AS numeric);
000456  } 0
000457  do_execsql_test cast-7.42 {
000458    SELECT CAST('+0.0' AS numeric);
000459  } 0
000460  do_execsql_test cast-7.43 {
000461    SELECT CAST('-1.0' AS numeric);
000462  } -1
000463  
000464  ifcapable utf16 {
000465    reset_db
000466    execsql { PRAGMA encoding='utf16' }
000467  
000468    do_execsql_test cast-8.1 {
000469      SELECT quote(X'310032003300')==quote(substr(X'310032003300', 1))
000470    } 1
000471    do_execsql_test cast-8.2 {
000472      SELECT CAST(X'310032003300' AS TEXT)
000473           ==CAST(substr(X'310032003300', 1) AS TEXT)
000474    } 1
000475  }
000476  
000477  reset_db
000478  do_execsql_test cast-9.0 {
000479    CREATE TABLE t0(c0);
000480    INSERT INTO t0(c0) VALUES (0);
000481    CREATE VIEW v1(c0, c1) AS 
000482      SELECT CAST(0.0 AS NUMERIC), COUNT(*) OVER () FROM t0;
000483    SELECT v1.c0 FROM v1, t0 WHERE v1.c0=0; 
000484  } {0.0}
000485  
000486  # Set the 2022-12-10 "reopen" of ticket [https://sqlite.org/src/tktview/57c47526c3]
000487  #
000488  do_execsql_test cast-9.1 {
000489    CREATE TABLE dual(dummy TEXT);
000490    INSERT INTO dual VALUES('X');
000491    SELECT CAST(4 AS NUMERIC);
000492  } {4}
000493  do_execsql_test cast-9.2 {
000494    SELECT CAST(4.0 AS NUMERIC);
000495  } {4.0}
000496  do_execsql_test cast-9.3 {
000497    SELECT CAST(4.5 AS NUMERIC);
000498  } {4.5}
000499  do_execsql_test cast-9.4 {
000500    SELECT x, typeof(x) FROM (SELECT CAST(4 AS NUMERIC) AS x) JOIN dual;
000501  } {4 integer}
000502  do_execsql_test cast-9.5 {
000503    SELECT x, typeof(x) FROM dual CROSS JOIN (SELECT CAST(4 AS NUMERIC) AS x);
000504  } {4 integer}
000505  do_execsql_test cast-9.10 {
000506    SELECT x, typeof(x) FROM (SELECT CAST(4.0 AS NUMERIC) AS x) JOIN dual;
000507  } {4.0 real}
000508  do_execsql_test cast-9.11 {
000509    SELECT x, typeof(x) FROM dual CROSS JOIN (SELECT CAST(4.0 AS NUMERIC) AS x);
000510  } {4.0 real}
000511  do_execsql_test cast-9.12 {
000512    SELECT x, typeof(x) FROM (SELECT CAST(4.5 AS NUMERIC) AS x) JOIN dual;
000513  } {4.5 real}
000514  do_execsql_test cast-9.13 {
000515    SELECT x, typeof(x) FROM dual CROSS JOIN (SELECT CAST(4.5 AS NUMERIC) AS x);
000516  } {4.5 real}
000517  
000518  # 2022-12-15 dbsqlfuzz c9ee6f9a0a8b8fefb02cf69de2a8b67ca39525c8
000519  #
000520  # Added a new SQLITE_AFF_FLEXNUM that does not try to convert int to real or
000521  # real to int.
000522  #
000523  do_execsql_test cast-10.1 {
000524    VALUES(CAST(44 AS REAL)),(55);
000525  } {44.0 55}
000526  do_execsql_test cast-10.2 {
000527    SELECT CAST(44 AS REAL) AS 'm' UNION ALL SELECT 55;
000528  } {44.0 55}
000529  do_execsql_test cast-10.3 {
000530    SELECT * FROM (VALUES(CAST(44 AS REAL)),(55));
000531  } {44.0 55}
000532  do_execsql_test cast-10.4 {
000533    SELECT * FROM (SELECT CAST(44 AS REAL) AS 'm' UNION ALL SELECT 55);
000534  } {44.0 55}
000535  do_execsql_test cast-10.5 {
000536    SELECT * FROM dual CROSS JOIN (VALUES(CAST(44 AS REAL)),(55));
000537  } {X 44.0 X 55}
000538  do_execsql_test cast-10.6 {
000539    SELECT * FROM dual CROSS JOIN (SELECT CAST(44 AS REAL) AS 'm'
000540                                   UNION ALL SELECT 55);
000541  } {X 44.0 X 55}
000542  ifcapable vtab {
000543    do_execsql_test cast-10.7 {
000544      DROP VIEW v1;
000545      CREATE VIEW v1 AS SELECT CAST(44 AS REAL) AS 'm' UNION ALL SELECT 55;
000546      SELECT name, type FROM pragma_table_info('v1');
000547    } {m NUM}
000548    do_execsql_test cast-10.8 {
000549      CREATE VIEW v2 AS VALUES(CAST(44 AS REAL)),(55);
000550      SELECT type FROM pragma_table_info('v2');
000551    } {NUM}
000552    do_execsql_test cast-10.9 {
000553      SELECT * FROM v1;
000554    } {44.0 55}
000555    do_execsql_test cast-10.10 {
000556      SELECT * FROM v2;
000557    } {44.0 55}
000558  }
000559  
000560  finish_test