# 2005 June 25

#

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

# a legal notice, here is a blessing:

#

#    May you do good and not evil.

#    May you find forgiveness for yourself and forgive others.

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

#

#***********************************************************************

# This file implements regression tests for SQLite library.  The

# focus of this file is testing the CAST operator.

#

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

set testdir [file dirname $argv0]

source $testdir/tester.tcl

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

ifcapable !cast {

  finish_test

  return

}

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

#

ifcapable bloblit {

  do_test cast-1.1 {

    execsql {SELECT x'616263'}

  } abc

  do_test cast-1.2 {

    execsql {SELECT typeof(x'616263')}

  } blob

  do_test cast-1.3 {

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

  } abc

  do_test cast-1.4 {

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

  } text

  do_test cast-1.5 {

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

  } 0

  do_test cast-1.6 {

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

  } integer

  do_test cast-1.7 {

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

  } abc

  do_test cast-1.8 {

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

  } blob

  do_test cast-1.9 {

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

  } 0

  do_test cast-1.10 {

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

  } integer

}

do_test cast-1.11 {

  execsql {SELECT null}

} {{}}

do_test cast-1.12 {

  execsql {SELECT typeof(NULL)}

} null

do_test cast-1.13 {

  execsql {SELECT CAST(NULL AS text)}

} {{}}

do_test cast-1.14 {

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

} null

do_test cast-1.15 {

  execsql {SELECT CAST(NULL AS numeric)}

} {{}}

do_test cast-1.16 {

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

} null

do_test cast-1.17 {

  execsql {SELECT CAST(NULL AS blob)}

} {{}}

do_test cast-1.18 {

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

} null

do_test cast-1.19 {

  execsql {SELECT CAST(NULL AS integer)}

} {{}}

do_test cast-1.20 {

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

} null

do_test cast-1.21 {

  execsql {SELECT 123}

} {123}

do_test cast-1.22 {

  execsql {SELECT typeof(123)}

} integer

do_test cast-1.23 {

  execsql {SELECT CAST(123 AS text)}

} {123}

do_test cast-1.24 {

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

} text

do_test cast-1.25 {

  execsql {SELECT CAST(123 AS numeric)}

} 123

do_test cast-1.26 {

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

} integer

do_test cast-1.27 {

  execsql {SELECT CAST(123 AS blob)}

} {123}

do_test cast-1.28 {

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

} blob

do_test cast-1.29 {

  execsql {SELECT CAST(123 AS integer)}

} {123}

do_test cast-1.30 {

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

} integer

do_test cast-1.31 {

  execsql {SELECT 123.456}

} {123.456}

do_test cast-1.32 {

  execsql {SELECT typeof(123.456)}

} real

do_test cast-1.33 {

  execsql {SELECT CAST(123.456 AS text)}

} {123.456}

do_test cast-1.34 {

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

} text

do_test cast-1.35 {

  execsql {SELECT CAST(123.456 AS numeric)}

} 123.456

do_test cast-1.36 {

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

} real

do_test cast-1.37 {

  execsql {SELECT CAST(123.456 AS blob)}

} {123.456}

do_test cast-1.38 {

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

} blob

do_test cast-1.39 {

  execsql {SELECT CAST(123.456 AS integer)}

} {123}

do_test cast-1.38 {

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

} integer

do_test cast-1.41 {

  execsql {SELECT '123abc'}

} {123abc}

do_test cast-1.42 {

  execsql {SELECT typeof('123abc')}

} text

do_test cast-1.43 {

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

} {123abc}

do_test cast-1.44 {

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

} text

do_test cast-1.45 {

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

} 123

do_test cast-1.46 {

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

} integer

do_test cast-1.47 {

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

} {123abc}

do_test cast-1.48 {

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

} blob

do_test cast-1.49 {

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

} 123

do_test cast-1.50 {

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

} integer

do_test cast-1.51 {

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

} 123.5

do_test cast-1.53 {

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

} 123

do_test case-1.60 {

  execsql {SELECT CAST(null AS REAL)}

} {{}}

do_test case-1.61 {

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

} {null}

do_test case-1.62 {

  execsql {SELECT CAST(1 AS REAL)}

} {1.0}

do_test case-1.63 {

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

} {real}

do_test case-1.64 {

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

} {1.0}

do_test case-1.65 {

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

} {real}

do_test case-1.66 {

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

} {0.0}

do_test case-1.67 {

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

} {real}

do_test case-1.68 {

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

} {1.0}

do_test case-1.69 {

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

} {real}

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

#

do_test cast-2.1 {

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

} 123

do_test cast-2.2 {

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

} -123.456

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

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

# mantissa) unless absolutely necessary.

#

do_test cast-3.1 {

  execsql {SELECT CAST(9223372036854774800 AS integer)}

} 9223372036854774800

do_test cast-3.2 {

  execsql {SELECT CAST(9223372036854774800 AS numeric)}

} 9223372036854774800

do_test cast-3.3 {

  execsql {SELECT CAST(9223372036854774800 AS real)}

} 9.22337203685477e+18

do_test cast-3.4 {

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

} 9223372036854774784

do_test cast-3.5 {

  execsql {SELECT CAST(-9223372036854774800 AS integer)}

} -9223372036854774800

do_test cast-3.6 {

  execsql {SELECT CAST(-9223372036854774800 AS numeric)}

} -9223372036854774800

do_test cast-3.7 {

  execsql {SELECT CAST(-9223372036854774800 AS real)}

} -9.22337203685477e+18

do_test cast-3.8 {

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

} -9223372036854774784

do_test cast-3.11 {

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

} 9223372036854774800

do_test cast-3.12 {

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

} 9223372036854774800

do_test cast-3.13 {

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

} 9.22337203685477e+18

ifcapable long_double {

  do_test cast-3.14 {

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

  } 9223372036854774784

}

do_test cast-3.15 {

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

} -9223372036854774800

do_test cast-3.16 {

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

} -9223372036854774800

do_test cast-3.17 {

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

} -9.22337203685477e+18

ifcapable long_double {

  do_test cast-3.18 {

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

  } -9223372036854774784

}

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

  do_test cast-3.21 {

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

  } 9223372036854774800

  do_test cast-3.22 {

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

  } 9223372036854774800

  do_test cast-3.23 {

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

  } 9.22337203685477e+18

  ifcapable long_double {

    do_test cast-3.24 {

      execsql {

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

                    AS integer)

      }

    } 9223372036854774784

  }

}

do_test case-3.31 {

  execsql {SELECT CAST(NULL AS numeric)}

} {{}}

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

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

do_test cast-3.32.1 {

  set blob "1234567890"

  set DB [sqlite3_connection_pointer db]

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

  sqlite3_bind_blob -static $::STMT 1 $blob 5

  sqlite3_step $::STMT

} {SQLITE_ROW}

do_test cast-3.32.2 {

  sqlite3_column_int $::STMT 0

} {12345}

do_test cast-3.32.3 {

  sqlite3_finalize $::STMT

} {SQLITE_OK}

finish_test