1 # 2005 June 25

     2 #

     3 # The author disclaims copyright to this source code.  In place of

     4 # a legal notice, here is a blessing:

     5 #

     6 #    May you do good and not evil.

     7 #    May you find forgiveness for yourself and forgive others.

     8 #    May you share freely, never taking more than you give.

     9 #

    10 #***********************************************************************

    11 # This file implements regression tests for SQLite library.  The

    12 # focus of this file is testing the CAST operator.

    13 #

    14 # $Id: cast.test,v 1.9 2008/01/19 20:11:26 drh Exp $

    15 

    16 set testdir [file dirname $argv0]

    17 source $testdir/tester.tcl

    18 

    19 # Only run these tests if the build includes the CAST operator

    20 ifcapable !cast {

    21   finish_test

    22   return

    23 }

    24 

    25 # Tests for the CAST( AS blob), CAST( AS text) and CAST( AS numeric) built-ins

    26 #

    27 ifcapable bloblit {

    28   do_test cast-1.1 {

    29     execsql {SELECT x'616263'}

    30   } abc

    31   do_test cast-1.2 {

    32     execsql {SELECT typeof(x'616263')}

    33   } blob

    34   do_test cast-1.3 {

    35     execsql {SELECT CAST(x'616263' AS text)}

    36   } abc

    37   do_test cast-1.4 {

    38     execsql {SELECT typeof(CAST(x'616263' AS text))}

    39   } text

    40   do_test cast-1.5 {

    41     execsql {SELECT CAST(x'616263' AS numeric)}

    42   } 0

    43   do_test cast-1.6 {

    44     execsql {SELECT typeof(CAST(x'616263' AS numeric))}

    45   } integer

    46   do_test cast-1.7 {

    47     execsql {SELECT CAST(x'616263' AS blob)}

    48   } abc

    49   do_test cast-1.8 {

    50     execsql {SELECT typeof(CAST(x'616263' AS blob))}

    51   } blob

    52   do_test cast-1.9 {

    53     execsql {SELECT CAST(x'616263' AS integer)}

    54   } 0

    55   do_test cast-1.10 {

    56     execsql {SELECT typeof(CAST(x'616263' AS integer))}

    57   } integer

    58 }

    59 do_test cast-1.11 {

    60   execsql {SELECT null}

    61 } {{}}

    62 do_test cast-1.12 {

    63   execsql {SELECT typeof(NULL)}

    64 } null

    65 do_test cast-1.13 {

    66   execsql {SELECT CAST(NULL AS text)}

    67 } {{}}

    68 do_test cast-1.14 {

    69   execsql {SELECT typeof(CAST(NULL AS text))}

    70 } null

    71 do_test cast-1.15 {

    72   execsql {SELECT CAST(NULL AS numeric)}

    73 } {{}}

    74 do_test cast-1.16 {

    75   execsql {SELECT typeof(CAST(NULL AS numeric))}

    76 } null

    77 do_test cast-1.17 {

    78   execsql {SELECT CAST(NULL AS blob)}

    79 } {{}}

    80 do_test cast-1.18 {

    81   execsql {SELECT typeof(CAST(NULL AS blob))}

    82 } null

    83 do_test cast-1.19 {

    84   execsql {SELECT CAST(NULL AS integer)}

    85 } {{}}

    86 do_test cast-1.20 {

    87   execsql {SELECT typeof(CAST(NULL AS integer))}

    88 } null

    89 do_test cast-1.21 {

    90   execsql {SELECT 123}

    91 } {123}

    92 do_test cast-1.22 {

    93   execsql {SELECT typeof(123)}

    94 } integer

    95 do_test cast-1.23 {

    96   execsql {SELECT CAST(123 AS text)}

    97 } {123}

    98 do_test cast-1.24 {

    99   execsql {SELECT typeof(CAST(123 AS text))}

   100 } text

   101 do_test cast-1.25 {

   102   execsql {SELECT CAST(123 AS numeric)}

   103 } 123

   104 do_test cast-1.26 {

   105   execsql {SELECT typeof(CAST(123 AS numeric))}

   106 } integer

   107 do_test cast-1.27 {

   108   execsql {SELECT CAST(123 AS blob)}

   109 } {123}

   110 do_test cast-1.28 {

   111   execsql {SELECT typeof(CAST(123 AS blob))}

   112 } blob

   113 do_test cast-1.29 {

   114   execsql {SELECT CAST(123 AS integer)}

   115 } {123}

   116 do_test cast-1.30 {

   117   execsql {SELECT typeof(CAST(123 AS integer))}

   118 } integer

   119 do_test cast-1.31 {

   120   execsql {SELECT 123.456}

   121 } {123.456}

   122 do_test cast-1.32 {

   123   execsql {SELECT typeof(123.456)}

   124 } real

   125 do_test cast-1.33 {

   126   execsql {SELECT CAST(123.456 AS text)}

   127 } {123.456}

   128 do_test cast-1.34 {

   129   execsql {SELECT typeof(CAST(123.456 AS text))}

   130 } text

   131 do_test cast-1.35 {

   132   execsql {SELECT CAST(123.456 AS numeric)}

   133 } 123.456

   134 do_test cast-1.36 {

   135   execsql {SELECT typeof(CAST(123.456 AS numeric))}

   136 } real

   137 do_test cast-1.37 {

   138   execsql {SELECT CAST(123.456 AS blob)}

   139 } {123.456}

   140 do_test cast-1.38 {

   141   execsql {SELECT typeof(CAST(123.456 AS blob))}

   142 } blob

   143 do_test cast-1.39 {

   144   execsql {SELECT CAST(123.456 AS integer)}

   145 } {123}

   146 do_test cast-1.38 {

   147   execsql {SELECT typeof(CAST(123.456 AS integer))}

   148 } integer

   149 do_test cast-1.41 {

   150   execsql {SELECT '123abc'}

   151 } {123abc}

   152 do_test cast-1.42 {

   153   execsql {SELECT typeof('123abc')}

   154 } text

   155 do_test cast-1.43 {

   156   execsql {SELECT CAST('123abc' AS text)}

   157 } {123abc}

   158 do_test cast-1.44 {

   159   execsql {SELECT typeof(CAST('123abc' AS text))}

   160 } text

   161 do_test cast-1.45 {

   162   execsql {SELECT CAST('123abc' AS numeric)}

   163 } 123

   164 do_test cast-1.46 {

   165   execsql {SELECT typeof(CAST('123abc' AS numeric))}

   166 } integer

   167 do_test cast-1.47 {

   168   execsql {SELECT CAST('123abc' AS blob)}

   169 } {123abc}

   170 do_test cast-1.48 {

   171   execsql {SELECT typeof(CAST('123abc' AS blob))}

   172 } blob

   173 do_test cast-1.49 {

   174   execsql {SELECT CAST('123abc' AS integer)}

   175 } 123

   176 do_test cast-1.50 {

   177   execsql {SELECT typeof(CAST('123abc' AS integer))}

   178 } integer

   179 do_test cast-1.51 {

   180   execsql {SELECT CAST('123.5abc' AS numeric)}

   181 } 123.5

   182 do_test cast-1.53 {

   183   execsql {SELECT CAST('123.5abc' AS integer)}

   184 } 123

   185 

   186 do_test case-1.60 {

   187   execsql {SELECT CAST(null AS REAL)}

   188 } {{}}

   189 do_test case-1.61 {

   190   execsql {SELECT typeof(CAST(null AS REAL))}

   191 } {null}

   192 do_test case-1.62 {

   193   execsql {SELECT CAST(1 AS REAL)}

   194 } {1.0}

   195 do_test case-1.63 {

   196   execsql {SELECT typeof(CAST(1 AS REAL))}

   197 } {real}

   198 do_test case-1.64 {

   199   execsql {SELECT CAST('1' AS REAL)}

   200 } {1.0}

   201 do_test case-1.65 {

   202   execsql {SELECT typeof(CAST('1' AS REAL))}

   203 } {real}

   204 do_test case-1.66 {

   205   execsql {SELECT CAST('abc' AS REAL)}

   206 } {0.0}

   207 do_test case-1.67 {

   208   execsql {SELECT typeof(CAST('abc' AS REAL))}

   209 } {real}

   210 do_test case-1.68 {

   211   execsql {SELECT CAST(x'31' AS REAL)}

   212 } {1.0}

   213 do_test case-1.69 {

   214   execsql {SELECT typeof(CAST(x'31' AS REAL))}

   215 } {real}

   216 

   217 

   218 # Ticket #1662.  Ignore leading spaces in numbers when casting.

   219 #

   220 do_test cast-2.1 {

   221   execsql {SELECT CAST('   123' AS integer)}

   222 } 123

   223 do_test cast-2.2 {

   224   execsql {SELECT CAST('   -123.456' AS real)}

   225 } -123.456

   226 

   227 # ticket #2364.  Use full percision integers if possible when casting

   228 # to numeric.  Do not fallback to real (and the corresponding 48-bit

   229 # mantissa) unless absolutely necessary.

   230 #

   231 do_test cast-3.1 {

   232   execsql {SELECT CAST(9223372036854774800 AS integer)}

   233 } 9223372036854774800

   234 do_test cast-3.2 {

   235   execsql {SELECT CAST(9223372036854774800 AS numeric)}

   236 } 9223372036854774800

   237 do_test cast-3.3 {

   238   execsql {SELECT CAST(9223372036854774800 AS real)}

   239 } 9.22337203685477e+18

   240 do_test cast-3.4 {

   241   execsql {SELECT CAST(CAST(9223372036854774800 AS real) AS integer)}

   242 } 9223372036854774784

   243 do_test cast-3.5 {

   244   execsql {SELECT CAST(-9223372036854774800 AS integer)}

   245 } -9223372036854774800

   246 do_test cast-3.6 {

   247   execsql {SELECT CAST(-9223372036854774800 AS numeric)}

   248 } -9223372036854774800

   249 do_test cast-3.7 {

   250   execsql {SELECT CAST(-9223372036854774800 AS real)}

   251 } -9.22337203685477e+18

   252 do_test cast-3.8 {

   253   execsql {SELECT CAST(CAST(-9223372036854774800 AS real) AS integer)}

   254 } -9223372036854774784

   255 do_test cast-3.11 {

   256   execsql {SELECT CAST('9223372036854774800' AS integer)}

   257 } 9223372036854774800

   258 do_test cast-3.12 {

   259   execsql {SELECT CAST('9223372036854774800' AS numeric)}

   260 } 9223372036854774800

   261 do_test cast-3.13 {

   262   execsql {SELECT CAST('9223372036854774800' AS real)}

   263 } 9.22337203685477e+18

   264 ifcapable long_double {

   265   do_test cast-3.14 {

   266     execsql {SELECT CAST(CAST('9223372036854774800' AS real) AS integer)}

   267   } 9223372036854774784

   268 }

   269 do_test cast-3.15 {

   270   execsql {SELECT CAST('-9223372036854774800' AS integer)}

   271 } -9223372036854774800

   272 do_test cast-3.16 {

   273   execsql {SELECT CAST('-9223372036854774800' AS numeric)}

   274 } -9223372036854774800

   275 do_test cast-3.17 {

   276   execsql {SELECT CAST('-9223372036854774800' AS real)}

   277 } -9.22337203685477e+18

   278 ifcapable long_double {

   279   do_test cast-3.18 {

   280     execsql {SELECT CAST(CAST('-9223372036854774800' AS real) AS integer)}

   281   } -9223372036854774784

   282 }

   283 if {[db eval {PRAGMA encoding}]=="UTF-8"} {

   284   do_test cast-3.21 {

   285     execsql {SELECT CAST(x'39323233333732303336383534373734383030' AS integer)}

   286   } 9223372036854774800

   287   do_test cast-3.22 {

   288     execsql {SELECT CAST(x'39323233333732303336383534373734383030' AS numeric)}

   289   } 9223372036854774800

   290   do_test cast-3.23 {

   291     execsql {SELECT CAST(x'39323233333732303336383534373734383030' AS real)}

   292   } 9.22337203685477e+18

   293   ifcapable long_double {

   294     do_test cast-3.24 {

   295       execsql {

   296         SELECT CAST(CAST(x'39323233333732303336383534373734383030' AS real)

   297                     AS integer)

   298       }

   299     } 9223372036854774784

   300   }

   301 }

   302 do_test case-3.31 {

   303   execsql {SELECT CAST(NULL AS numeric)}

   304 } {{}}

   305 

   306 # Test to see if it is possible to trick SQLite into reading past 

   307 # the end of a blob when converting it to a number.

   308 do_test cast-3.32.1 {

   309   set blob "1234567890"

   310   set DB [sqlite3_connection_pointer db]

   311   set ::STMT [sqlite3_prepare $DB {SELECT CAST(? AS real)} -1 TAIL]

   312   sqlite3_bind_blob -static $::STMT 1 $blob 5

   313   sqlite3_step $::STMT

   314 } {SQLITE_ROW}

   315 do_test cast-3.32.2 {

   316   sqlite3_column_int $::STMT 0

   317 } {12345}

   318 do_test cast-3.32.3 {

   319   sqlite3_finalize $::STMT

   320 } {SQLITE_OK}

   321 

   322 finish_test