SQLite

  sqlite3_context *context,
  int argc,
  sqlite3_value **argv
){
  assert( argc==1 );
  sqlite3_result_value(context, argv[0]);
}

/*
** Implementation of the multitype_text() function.
**
** The function returns its argument.  The result will always have a
** TEXT value.  But if the original input is numeric, it will also
** have that numeric value.
*/
static void multitypeTextFunc(
  sqlite3_context *context,
  int argc,
  sqlite3_value **argv
){
  assert( argc==1 );
  (void)argc;
  (void)sqlite3_value_text(argv[0]);
  sqlite3_result_value(context, argv[0]);
}

#ifdef _WIN32
__declspec(dllexport)
#endif
int sqlite3_noop_init(
  sqlite3 *db, 
  char **pzErrMsg, 

  rc = sqlite3_create_function(db, "noop_do", 1,
                     SQLITE_UTF8 | SQLITE_DETERMINISTIC | SQLITE_DIRECTONLY,
                     0, noopfunc, 0, 0);
  if( rc ) return rc;
  rc = sqlite3_create_function(db, "noop_nd", 1,
                     SQLITE_UTF8,
                     0, noopfunc, 0, 0);
  if( rc ) return rc;
  rc = sqlite3_create_function(db, "multitype_text", 1,
                     SQLITE_UTF8,
                     0, multitypeTextFunc, 0, 0);
  return rc;
}

  int argc,  
  sqlite3_value **argv
){
  sqlite3_int64 v = sqlite3_value_int64(argv[0]);
  sqlite3_result_int64(context, v);
  sqlite3_test_control(SQLITE_TESTCTRL_RESULT_INTREAL, context);
}

/*
** These SQL functions attempt to return a value (their first argument)
** that has been modified to have multiple datatypes.  For example both
** TEXT and INTEGER.
*/
static void addTextTypeFunction(
  sqlite3_context *context, 
  int argc,  
  sqlite3_value **argv
){
  (void)sqlite3_value_text(argv[0]);
  (void)argc;
  sqlite3_result_value(context, argv[0]);
}
static void addIntTypeFunction(
  sqlite3_context *context, 
  int argc,  
  sqlite3_value **argv
){
  (void)sqlite3_value_int64(argv[0]);
  (void)argc;
  sqlite3_result_value(context, argv[0]);
}
static void addRealTypeFunction(
  sqlite3_context *context, 
  int argc,  
  sqlite3_value **argv
){
  (void)sqlite3_value_double(argv[0]);
  (void)argc;
  sqlite3_result_value(context, argv[0]);
}

/*
** SQL function:  strtod(X)
**
** Use the C-library strtod() function to convert string X into a double.
** Used for comparing the accuracy of SQLite's internal text-to-float conversion
** routines against the C-library.

  /* The intreal() function converts its argument to an integer and returns
  ** it as a MEM_IntReal.
  */
  if( rc==SQLITE_OK ){
    rc = sqlite3_create_function(db, "intreal", 1, SQLITE_UTF8,
          0, intrealFunction, 0, 0);
  }

  /* The add_text_type(), add_int_type(), and add_real_type() functions
  ** attempt to return a value that has multiple datatypes.
  */
  if( rc==SQLITE_OK ){
    rc = sqlite3_create_function(db, "add_text_type", 1, SQLITE_UTF8,
          0, addTextTypeFunction, 0, 0);
  }
  if( rc==SQLITE_OK ){
    rc = sqlite3_create_function(db, "add_int_type", 1, SQLITE_UTF8,
          0, addIntTypeFunction, 0, 0);
  }
  if( rc==SQLITE_OK ){
    rc = sqlite3_create_function(db, "add_real_type", 1, SQLITE_UTF8,
          0, addRealTypeFunction, 0, 0);
  }

  /* Functions strtod() and dtostr() work as in the shell.  These routines
  ** use the standard C library to convert between floating point and
  ** text.  This is used to compare SQLite's internal conversion routines
  ** against the standard library conversion routines.
  **
  ** Both routines copy/pasted from the shell.c.in implementation

          flags3 = pIn3->flags;
        }
        if( (flags3 & (MEM_Int|MEM_IntReal|MEM_Real|MEM_Str))==MEM_Str ){
          applyNumericAffinity(pIn3,0);
        }
      }
    }else if( affinity==SQLITE_AFF_TEXT && ((flags1 | flags3) & MEM_Str)!=0 ){
      if( (flags1 & MEM_Str)!=0 ){
        pIn1->flags &= ~(MEM_Int|MEM_Real|MEM_IntReal);
      }else if( (flags1&(MEM_Int|MEM_Real|MEM_IntReal))!=0 ){
        testcase( pIn1->flags & MEM_Int );
        testcase( pIn1->flags & MEM_Real );
        testcase( pIn1->flags & MEM_IntReal );
        sqlite3VdbeMemStringify(pIn1, encoding, 1);
        testcase( (flags1&MEM_Dyn) != (pIn1->flags&MEM_Dyn) );
        flags1 = (pIn1->flags & ~MEM_TypeMask) | (flags1 & MEM_TypeMask);
        if( NEVER(pIn1==pIn3) ) flags3 = flags1 | MEM_Str;
      }
      if( (flags3 & MEM_Str)!=0 ){
        pIn3->flags &= ~(MEM_Int|MEM_Real|MEM_IntReal);
      }else if( (flags3&(MEM_Int|MEM_Real|MEM_IntReal))!=0 ){
        testcase( pIn3->flags & MEM_Int );
        testcase( pIn3->flags & MEM_Real );
        testcase( pIn3->flags & MEM_IntReal );
        sqlite3VdbeMemStringify(pIn3, encoding, 1);
        testcase( (flags3&MEM_Dyn) != (pIn3->flags&MEM_Dyn) );
        flags3 = (pIn3->flags & ~MEM_TypeMask) | (flags3 & MEM_TypeMask);
      }

#    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 interaction of SQLite manifest types
# with Tcl dual-representations.
#



set testdir [file dirname $argv0]
source $testdir/tester.tcl

# A variable with only a string representation comes in as TEXT
do_test types3-1.1 {
  set V {}

  set V [db one {SELECT '1234567890123456.0'}]
  tcl_variable_type V
} {}
do_test types3-2.6 {
  set V [db one {SELECT NULL}]
  tcl_variable_type V
} {}

# See https://sqlite.org/forum/forumpost/3776b48e71
#
# On a text-affinity comparison of two values where one of
# the values has both MEM_Str and a numeric type like MEM_Int,
# make sure that only the MEM_Str representation is used.
#
sqlite3_create_function db
do_execsql_test types3-3.1 {
  DROP TABLE IF EXISTS t1;
  CREATE TABLE t1(x TEXT PRIMARY KEY);
  INSERT INTO t1 VALUES('1');
  SELECT * FROM t1 WHERE NOT x=upper(1);
} {}
do_execsql_test types3-3.2 {
  SELECT * FROM t1 WHERE NOT x=add_text_type(1);
} {}
do_execsql_test types3-3.3 {
  SELECT * FROM t1 WHERE NOT x=add_int_type('1');
} {}
do_execsql_test types3-3.4 {
  DELETE FROM t1;
  INSERT INTO t1 VALUES(1.25);
  SELECT * FROM t1 WHERE NOT x=add_real_type('1.25');
} {}
do_execsql_test types3-3.5 {
  SELECT * FROM t1 WHERE NOT x=add_text_type(1.25);
} {}

finish_test