000001  # 2001 September 15
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 built-in functions.
000013  #
000014  
000015  set testdir [file dirname $argv0]
000016  source $testdir/tester.tcl
000017  set testprefix func
000018  
000019  # Create a table to work with.
000020  #
000021  do_test func-0.0 {
000022    execsql {CREATE TABLE tbl1(t1 text)}
000023    foreach word {this program is free software} {
000024      execsql "INSERT INTO tbl1 VALUES('$word')"
000025    }
000026    execsql {SELECT t1 FROM tbl1 ORDER BY t1}
000027  } {free is program software this}
000028  do_test func-0.1 {
000029    execsql {
000030       CREATE TABLE t2(a);
000031       INSERT INTO t2 VALUES(1);
000032       INSERT INTO t2 VALUES(NULL);
000033       INSERT INTO t2 VALUES(345);
000034       INSERT INTO t2 VALUES(NULL);
000035       INSERT INTO t2 VALUES(67890);
000036       SELECT * FROM t2;
000037    }
000038  } {1 {} 345 {} 67890}
000039  
000040  # Check out the length() function
000041  #
000042  do_test func-1.0 {
000043    execsql {SELECT length(t1) FROM tbl1 ORDER BY t1}
000044  } {4 2 7 8 4}
000045  do_test func-1.1 {
000046    set r [catch {execsql {SELECT length(*) FROM tbl1 ORDER BY t1}} msg]
000047    lappend r $msg
000048  } {1 {wrong number of arguments to function length()}}
000049  do_test func-1.2 {
000050    set r [catch {execsql {SELECT length(t1,5) FROM tbl1 ORDER BY t1}} msg]
000051    lappend r $msg
000052  } {1 {wrong number of arguments to function length()}}
000053  do_test func-1.3 {
000054    execsql {SELECT length(t1), count(*) FROM tbl1 GROUP BY length(t1)
000055             ORDER BY length(t1)}
000056  } {2 1 4 2 7 1 8 1}
000057  do_test func-1.4 {
000058    execsql {SELECT coalesce(length(a),-1) FROM t2}
000059  } {1 -1 3 -1 5}
000060  
000061  # Check out the substr() function
000062  #
000063  do_test func-2.0 {
000064    execsql {SELECT substr(t1,1,2) FROM tbl1 ORDER BY t1}
000065  } {fr is pr so th}
000066  do_test func-2.1 {
000067    execsql {SELECT substr(t1,2,1) FROM tbl1 ORDER BY t1}
000068  } {r s r o h}
000069  do_test func-2.2 {
000070    execsql {SELECT substr(t1,3,3) FROM tbl1 ORDER BY t1}
000071  } {ee {} ogr ftw is}
000072  do_test func-2.3 {
000073    execsql {SELECT substr(t1,-1,1) FROM tbl1 ORDER BY t1}
000074  } {e s m e s}
000075  do_test func-2.4 {
000076    execsql {SELECT substr(t1,-1,2) FROM tbl1 ORDER BY t1}
000077  } {e s m e s}
000078  do_test func-2.5 {
000079    execsql {SELECT substr(t1,-2,1) FROM tbl1 ORDER BY t1}
000080  } {e i a r i}
000081  do_test func-2.6 {
000082    execsql {SELECT substr(t1,-2,2) FROM tbl1 ORDER BY t1}
000083  } {ee is am re is}
000084  do_test func-2.7 {
000085    execsql {SELECT substr(t1,-4,2) FROM tbl1 ORDER BY t1}
000086  } {fr {} gr wa th}
000087  do_test func-2.8 {
000088    execsql {SELECT t1 FROM tbl1 ORDER BY substr(t1,2,20)}
000089  } {this software free program is}
000090  do_test func-2.9 {
000091    execsql {SELECT substr(a,1,1) FROM t2}
000092  } {1 {} 3 {} 6}
000093  do_test func-2.10 {
000094    execsql {SELECT substr(a,2,2) FROM t2}
000095  } {{} {} 45 {} 78}
000096  
000097  # Only do the following tests if TCL has UTF-8 capabilities
000098  #
000099  if {"\u1234"!="u1234"} {
000100  
000101  # Put some UTF-8 characters in the database
000102  #
000103  do_test func-3.0 {
000104    execsql {DELETE FROM tbl1}
000105    foreach word "contains UTF-8 characters hi\u1234ho" {
000106      execsql "INSERT INTO tbl1 VALUES('$word')"
000107    }
000108    execsql {SELECT t1 FROM tbl1 ORDER BY t1}
000109  } "UTF-8 characters contains hi\u1234ho"
000110  do_test func-3.1 {
000111    execsql {SELECT length(t1) FROM tbl1 ORDER BY t1}
000112  } {5 10 8 5}
000113  do_test func-3.2 {
000114    execsql {SELECT substr(t1,1,2) FROM tbl1 ORDER BY t1}
000115  } {UT ch co hi}
000116  do_test func-3.3 {
000117    execsql {SELECT substr(t1,1,3) FROM tbl1 ORDER BY t1}
000118  } "UTF cha con hi\u1234"
000119  do_test func-3.4 {
000120    execsql {SELECT substr(t1,2,2) FROM tbl1 ORDER BY t1}
000121  } "TF ha on i\u1234"
000122  do_test func-3.5 {
000123    execsql {SELECT substr(t1,2,3) FROM tbl1 ORDER BY t1}
000124  } "TF- har ont i\u1234h"
000125  do_test func-3.6 {
000126    execsql {SELECT substr(t1,3,2) FROM tbl1 ORDER BY t1}
000127  } "F- ar nt \u1234h"
000128  do_test func-3.7 {
000129    execsql {SELECT substr(t1,4,2) FROM tbl1 ORDER BY t1}
000130  } "-8 ra ta ho"
000131  do_test func-3.8 {
000132    execsql {SELECT substr(t1,-1,1) FROM tbl1 ORDER BY t1}
000133  } "8 s s o"
000134  do_test func-3.9 {
000135    execsql {SELECT substr(t1,-3,2) FROM tbl1 ORDER BY t1}
000136  } "F- er in \u1234h"
000137  do_test func-3.10 {
000138    execsql {SELECT substr(t1,-4,3) FROM tbl1 ORDER BY t1}
000139  } "TF- ter ain i\u1234h"
000140  do_test func-3.99 {
000141    execsql {DELETE FROM tbl1}
000142    foreach word {this program is free software} {
000143      execsql "INSERT INTO tbl1 VALUES('$word')"
000144    }
000145    execsql {SELECT t1 FROM tbl1}
000146  } {this program is free software}
000147  
000148  } ;# End \u1234!=u1234
000149  
000150  # Test the abs() and round() functions.
000151  #
000152  ifcapable !floatingpoint {
000153    do_test func-4.1 {
000154      execsql {
000155        CREATE TABLE t1(a,b,c);
000156        INSERT INTO t1 VALUES(1,2,3);
000157        INSERT INTO t1 VALUES(2,12345678901234,-1234567890);
000158        INSERT INTO t1 VALUES(3,-2,-5);
000159      }
000160      catchsql {SELECT abs(a,b) FROM t1}
000161    } {1 {wrong number of arguments to function abs()}}
000162  }
000163  ifcapable floatingpoint {
000164    do_test func-4.1 {
000165      execsql {
000166        CREATE TABLE t1(a,b,c);
000167        INSERT INTO t1 VALUES(1,2,3);
000168        INSERT INTO t1 VALUES(2,1.2345678901234,-12345.67890);
000169        INSERT INTO t1 VALUES(3,-2,-5);
000170      }
000171      catchsql {SELECT abs(a,b) FROM t1}
000172    } {1 {wrong number of arguments to function abs()}}
000173  }
000174  do_test func-4.2 {
000175    catchsql {SELECT abs() FROM t1}
000176  } {1 {wrong number of arguments to function abs()}}
000177  ifcapable floatingpoint {
000178    do_test func-4.3 {
000179      catchsql {SELECT abs(b) FROM t1 ORDER BY a}
000180    } {0 {2 1.2345678901234 2}}
000181    do_test func-4.4 {
000182      catchsql {SELECT abs(c) FROM t1 ORDER BY a}
000183    } {0 {3 12345.6789 5}}
000184  }
000185  ifcapable !floatingpoint {
000186    if {[working_64bit_int]} {
000187      do_test func-4.3 {
000188        catchsql {SELECT abs(b) FROM t1 ORDER BY a}
000189      } {0 {2 12345678901234 2}}
000190    }
000191    do_test func-4.4 {
000192      catchsql {SELECT abs(c) FROM t1 ORDER BY a}
000193    } {0 {3 1234567890 5}}
000194  }
000195  do_test func-4.4.1 {
000196    execsql {SELECT abs(a) FROM t2}
000197  } {1 {} 345 {} 67890}
000198  do_test func-4.4.2 {
000199    execsql {SELECT abs(t1) FROM tbl1}
000200  } {0.0 0.0 0.0 0.0 0.0}
000201  
000202  ifcapable floatingpoint {
000203    do_test func-4.5 {
000204      catchsql {SELECT round(a,b,c) FROM t1}
000205    } {1 {wrong number of arguments to function round()}}
000206    do_test func-4.6 {
000207      catchsql {SELECT round(b,2) FROM t1 ORDER BY b}
000208    } {0 {-2.0 1.23 2.0}}
000209    do_test func-4.7 {
000210      catchsql {SELECT round(b,0) FROM t1 ORDER BY a}
000211    } {0 {2.0 1.0 -2.0}}
000212    do_test func-4.8 {
000213      catchsql {SELECT round(c) FROM t1 ORDER BY a}
000214    } {0 {3.0 -12346.0 -5.0}}
000215    do_test func-4.9 {
000216      catchsql {SELECT round(c,a) FROM t1 ORDER BY a}
000217    } {0 {3.0 -12345.68 -5.0}}
000218    do_test func-4.10 {
000219      catchsql {SELECT 'x' || round(c,a) || 'y' FROM t1 ORDER BY a}
000220    } {0 {x3.0y x-12345.68y x-5.0y}}
000221    do_test func-4.11 {
000222      catchsql {SELECT round() FROM t1 ORDER BY a}
000223    } {1 {wrong number of arguments to function round()}}
000224    do_test func-4.12 {
000225      execsql {SELECT coalesce(round(a,2),'nil') FROM t2}
000226    } {1.0 nil 345.0 nil 67890.0}
000227    do_test func-4.13 {
000228      execsql {SELECT round(t1,2) FROM tbl1}
000229    } {0.0 0.0 0.0 0.0 0.0}
000230    do_test func-4.14 {
000231      execsql {SELECT typeof(round(5.1,1));}
000232    } {real}
000233    do_test func-4.15 {
000234      execsql {SELECT typeof(round(5.1));}
000235    } {real}
000236    do_test func-4.16 {
000237      catchsql {SELECT round(b,2.0) FROM t1 ORDER BY b}
000238    } {0 {-2.0 1.23 2.0}}
000239    # Verify some values reported on the mailing list.
000240    # Some of these fail on MSVC builds with 64-bit
000241    # long doubles, but not on GCC builds with 80-bit
000242    # long doubles.
000243    for {set i 1} {$i<999} {incr i} {
000244      set x1 [expr 40222.5 + $i]
000245      set x2 [expr 40223.0 + $i]
000246      do_test func-4.17.$i {
000247        execsql {SELECT round($x1);}
000248      } $x2
000249    }
000250    for {set i 1} {$i<999} {incr i} {
000251      set x1 [expr 40222.05 + $i]
000252      set x2 [expr 40222.10 + $i]
000253      do_test func-4.18.$i {
000254        execsql {SELECT round($x1,1);}
000255      } $x2
000256    }
000257    do_test func-4.20 {
000258      execsql {SELECT round(40223.4999999999);}
000259    } {40223.0}
000260    do_test func-4.21 {
000261      execsql {SELECT round(40224.4999999999);}
000262    } {40224.0}
000263    do_test func-4.22 {
000264      execsql {SELECT round(40225.4999999999);}
000265    } {40225.0}
000266    for {set i 1} {$i<10} {incr i} {
000267      do_test func-4.23.$i {
000268        execsql {SELECT round(40223.4999999999,$i);}
000269      } {40223.5}
000270      do_test func-4.24.$i {
000271        execsql {SELECT round(40224.4999999999,$i);}
000272      } {40224.5}
000273      do_test func-4.25.$i {
000274        execsql {SELECT round(40225.4999999999,$i);}
000275      } {40225.5}
000276    }
000277    for {set i 10} {$i<32} {incr i} {
000278      do_test func-4.26.$i {
000279        execsql {SELECT round(40223.4999999999,$i);}
000280      } {40223.4999999999}
000281      do_test func-4.27.$i {
000282        execsql {SELECT round(40224.4999999999,$i);}
000283      } {40224.4999999999}
000284      do_test func-4.28.$i {
000285        execsql {SELECT round(40225.4999999999,$i);}
000286      } {40225.4999999999}
000287    }
000288    do_test func-4.29 {
000289      execsql {SELECT round(1234567890.5);}
000290    } {1234567891.0}
000291    do_test func-4.30 {
000292      execsql {SELECT round(12345678901.5);}
000293    } {12345678902.0}
000294    do_test func-4.31 {
000295      execsql {SELECT round(123456789012.5);}
000296    } {123456789013.0}
000297    do_test func-4.32 {
000298      execsql {SELECT round(1234567890123.5);}
000299    } {1234567890124.0}
000300    do_test func-4.33 {
000301      execsql {SELECT round(12345678901234.5);}
000302    } {12345678901235.0}
000303    do_test func-4.34 {
000304      execsql {SELECT round(1234567890123.35,1);}
000305    } {1234567890123.4}
000306    do_test func-4.35 {
000307      execsql {SELECT round(1234567890123.445,2);}
000308    } {1234567890123.45}
000309    do_test func-4.36 {
000310      execsql {SELECT round(99999999999994.5);}
000311    } {99999999999995.0}
000312    do_test func-4.37 {
000313      execsql {SELECT round(9999999999999.55,1);}
000314    } {9999999999999.6}
000315    do_test func-4.38 {
000316      execsql {SELECT round(9999999999999.556,2);}
000317    } {9999999999999.56}
000318  }
000319  
000320  # Test the upper() and lower() functions
000321  #
000322  do_test func-5.1 {
000323    execsql {SELECT upper(t1) FROM tbl1}
000324  } {THIS PROGRAM IS FREE SOFTWARE}
000325  do_test func-5.2 {
000326    execsql {SELECT lower(upper(t1)) FROM tbl1}
000327  } {this program is free software}
000328  do_test func-5.3 {
000329    execsql {SELECT upper(a), lower(a) FROM t2}
000330  } {1 1 {} {} 345 345 {} {} 67890 67890}
000331  ifcapable !icu {
000332    do_test func-5.4 {
000333      catchsql {SELECT upper(a,5) FROM t2}
000334    } {1 {wrong number of arguments to function upper()}}
000335  }
000336  do_test func-5.5 {
000337    catchsql {SELECT upper(*) FROM t2}
000338  } {1 {wrong number of arguments to function upper()}}
000339  
000340  # Test the coalesce() and nullif() functions
000341  #
000342  do_test func-6.1 {
000343    execsql {SELECT coalesce(a,'xyz') FROM t2}
000344  } {1 xyz 345 xyz 67890}
000345  do_test func-6.2 {
000346    execsql {SELECT coalesce(upper(a),'nil') FROM t2}
000347  } {1 nil 345 nil 67890}
000348  do_test func-6.3 {
000349    execsql {SELECT coalesce(nullif(1,1),'nil')}
000350  } {nil}
000351  do_test func-6.4 {
000352    execsql {SELECT coalesce(nullif(1,2),'nil')}
000353  } {1}
000354  do_test func-6.5 {
000355    execsql {SELECT coalesce(nullif(1,NULL),'nil')}
000356  } {1}
000357  
000358  
000359  # Test the last_insert_rowid() function
000360  #
000361  do_test func-7.1 {
000362    execsql {SELECT last_insert_rowid()}
000363  } [db last_insert_rowid]
000364  
000365  # Tests for aggregate functions and how they handle NULLs.
000366  #
000367  ifcapable floatingpoint {
000368    do_test func-8.1 {
000369      ifcapable explain {
000370        execsql {EXPLAIN SELECT sum(a) FROM t2;}
000371      }
000372      execsql {
000373        SELECT sum(a), count(a), round(avg(a),2), min(a), max(a), count(*) FROM t2;
000374      }
000375    } {68236 3 22745.33 1 67890 5}
000376  }
000377  ifcapable !floatingpoint {
000378    do_test func-8.1 {
000379      ifcapable explain {
000380        execsql {EXPLAIN SELECT sum(a) FROM t2;}
000381      }
000382      execsql {
000383        SELECT sum(a), count(a), avg(a), min(a), max(a), count(*) FROM t2;
000384      }
000385    } {68236 3 22745.0 1 67890 5}
000386  }
000387  do_test func-8.2 {
000388    execsql {
000389      SELECT max('z+'||a||'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP') FROM t2;
000390    }
000391  } {z+67890abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP}
000392  
000393  ifcapable tempdb {
000394    do_test func-8.3 {
000395      execsql {
000396        CREATE TEMP TABLE t3 AS SELECT a FROM t2 ORDER BY a DESC;
000397        SELECT min('z+'||a||'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP') FROM t3;
000398      }
000399    } {z+1abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP}
000400  } else {
000401    do_test func-8.3 {
000402      execsql {
000403        CREATE TABLE t3 AS SELECT a FROM t2 ORDER BY a DESC;
000404        SELECT min('z+'||a||'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP') FROM t3;
000405      }
000406    } {z+1abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP}
000407  }
000408  do_test func-8.4 {
000409    execsql {
000410      SELECT max('z+'||a||'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP') FROM t3;
000411    }
000412  } {z+67890abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP}
000413  ifcapable compound {
000414    do_test func-8.5 {
000415      execsql {
000416        SELECT sum(x) FROM (SELECT '9223372036' || '854775807' AS x
000417                            UNION ALL SELECT -9223372036854775807)
000418      }
000419    } {0}
000420    do_test func-8.6 {
000421      execsql {
000422        SELECT typeof(sum(x)) FROM (SELECT '9223372036' || '854775807' AS x
000423                            UNION ALL SELECT -9223372036854775807)
000424      }
000425    } {integer}
000426    do_test func-8.7 {
000427      execsql {
000428        SELECT typeof(sum(x)) FROM (SELECT '9223372036' || '854775808' AS x
000429                            UNION ALL SELECT -9223372036854775807)
000430      }
000431    } {real}
000432  ifcapable floatingpoint {
000433    do_test func-8.8 {
000434      execsql {
000435        SELECT sum(x)>0.0 FROM (SELECT '9223372036' || '854775808' AS x
000436                            UNION ALL SELECT -9223372036850000000)
000437      }
000438    } {1}
000439  }
000440  ifcapable !floatingpoint {
000441    do_test func-8.8 {
000442      execsql {
000443        SELECT sum(x)>0 FROM (SELECT '9223372036' || '854775808' AS x
000444                            UNION ALL SELECT -9223372036850000000)
000445      }
000446    } {1}
000447  }
000448  }
000449  
000450  # How do you test the random() function in a meaningful, deterministic way?
000451  #
000452  do_test func-9.1 {
000453    execsql {
000454      SELECT random() is not null;
000455    }
000456  } {1}
000457  do_test func-9.2 {
000458    execsql {
000459      SELECT typeof(random());
000460    }
000461  } {integer}
000462  do_test func-9.3 {
000463    execsql {
000464      SELECT randomblob(32) is not null;
000465    }
000466  } {1}
000467  do_test func-9.4 {
000468    execsql {
000469      SELECT typeof(randomblob(32));
000470    }
000471  } {blob}
000472  do_test func-9.5 {
000473    execsql {
000474      SELECT length(randomblob(32)), length(randomblob(-5)),
000475             length(randomblob(2000))
000476    }
000477  } {32 1 2000}
000478  
000479  # The "hex()" function was added in order to be able to render blobs
000480  # generated by randomblob().  So this seems like a good place to test
000481  # hex().
000482  #
000483  ifcapable bloblit {
000484    do_test func-9.10 {
000485      execsql {SELECT hex(x'00112233445566778899aAbBcCdDeEfF')}
000486    } {00112233445566778899AABBCCDDEEFF}
000487  }
000488  set encoding [db one {PRAGMA encoding}]
000489  if {$encoding=="UTF-16le"} {
000490    do_test func-9.11-utf16le {
000491      execsql {SELECT hex(replace('abcdefg','ef','12'))}
000492    } {6100620063006400310032006700}
000493    do_test func-9.12-utf16le {
000494      execsql {SELECT hex(replace('abcdefg','','12'))}
000495    } {6100620063006400650066006700}
000496    do_test func-9.13-utf16le {
000497      execsql {SELECT hex(replace('aabcdefg','a','aaa'))}
000498    } {610061006100610061006100620063006400650066006700}
000499  } elseif {$encoding=="UTF-8"} {
000500    do_test func-9.11-utf8 {
000501      execsql {SELECT hex(replace('abcdefg','ef','12'))}
000502    } {61626364313267}
000503    do_test func-9.12-utf8 {
000504      execsql {SELECT hex(replace('abcdefg','','12'))}
000505    } {61626364656667}
000506    do_test func-9.13-utf8 {
000507      execsql {SELECT hex(replace('aabcdefg','a','aaa'))}
000508    } {616161616161626364656667}
000509  }
000510  do_execsql_test func-9.14 {
000511    WITH RECURSIVE c(x) AS (
000512       VALUES(1)
000513       UNION ALL
000514       SELECT x+1 FROM c WHERE x<1040
000515    )
000516    SELECT 
000517      count(*),
000518      sum(length(replace(printf('abc%.*cxyz',x,'m'),'m','nnnn'))-(6+x*4))
000519    FROM c;
000520  } {1040 0}
000521    
000522  # Use the "sqlite_register_test_function" TCL command which is part of
000523  # the text fixture in order to verify correct operation of some of
000524  # the user-defined SQL function APIs that are not used by the built-in
000525  # functions.
000526  #
000527  set ::DB [sqlite3_connection_pointer db]
000528  sqlite_register_test_function $::DB testfunc
000529  do_test func-10.1 {
000530    catchsql {
000531      SELECT testfunc(NULL,NULL);
000532    }
000533  } {1 {first argument should be one of: int int64 string double null value}}
000534  do_test func-10.2 {
000535    execsql {
000536      SELECT testfunc(
000537       'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ',
000538       'int', 1234
000539      );
000540    }
000541  } {1234}
000542  do_test func-10.3 {
000543    execsql {
000544      SELECT testfunc(
000545       'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ',
000546       'string', NULL
000547      );
000548    }
000549  } {{}}
000550  
000551  ifcapable floatingpoint {
000552    do_test func-10.4 {
000553      execsql {
000554        SELECT testfunc(
000555         'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ',
000556         'double', 1.234
000557        );
000558      }
000559    } {1.234}
000560    do_test func-10.5 {
000561      execsql {
000562        SELECT testfunc(
000563         'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ',
000564         'int', 1234,
000565         'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ',
000566         'string', NULL,
000567         'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ',
000568         'double', 1.234,
000569         'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ',
000570         'int', 1234,
000571         'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ',
000572         'string', NULL,
000573         'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ',
000574         'double', 1.234
000575        );
000576      }
000577    } {1.234}
000578  }
000579  
000580  # Test the built-in sqlite_version(*) SQL function.
000581  #
000582  do_test func-11.1 {
000583    execsql {
000584      SELECT sqlite_version(*);
000585    }
000586  } [sqlite3 -version]
000587  
000588  # Test that destructors passed to sqlite3 by calls to sqlite3_result_text()
000589  # etc. are called. These tests use two special user-defined functions
000590  # (implemented in func.c) only available in test builds. 
000591  #
000592  # Function test_destructor() takes one argument and returns a copy of the
000593  # text form of that argument. A destructor is associated with the return
000594  # value. Function test_destructor_count() returns the number of outstanding
000595  # destructor calls for values returned by test_destructor().
000596  #
000597  if {[db eval {PRAGMA encoding}]=="UTF-8"} {
000598    do_test func-12.1-utf8 {
000599      execsql {
000600        SELECT test_destructor('hello world'), test_destructor_count();
000601      }
000602    } {{hello world} 1}
000603  } else {
000604      ifcapable {utf16} {
000605        do_test func-12.1-utf16 {
000606          execsql {
000607            SELECT test_destructor16('hello world'), test_destructor_count();
000608          }
000609        } {{hello world} 1}
000610      }
000611  }
000612  do_test func-12.2 {
000613    execsql {
000614      SELECT test_destructor_count();
000615    }
000616  } {0}
000617  do_test func-12.3 {
000618    execsql {
000619      SELECT test_destructor('hello')||' world'
000620    }
000621  } {{hello world}}
000622  do_test func-12.4 {
000623    execsql {
000624      SELECT test_destructor_count();
000625    }
000626  } {0}
000627  do_test func-12.5 {
000628    execsql {
000629      CREATE TABLE t4(x);
000630      INSERT INTO t4 VALUES(test_destructor('hello'));
000631      INSERT INTO t4 VALUES(test_destructor('world'));
000632      SELECT min(test_destructor(x)), max(test_destructor(x)) FROM t4;
000633    }
000634  } {hello world}
000635  do_test func-12.6 {
000636    execsql {
000637      SELECT test_destructor_count();
000638    }
000639  } {0}
000640  do_test func-12.7 {
000641    execsql {
000642      DROP TABLE t4;
000643    }
000644  } {}
000645  
000646  
000647  # Test that the auxdata API for scalar functions works. This test uses
000648  # a special user-defined function only available in test builds,
000649  # test_auxdata(). Function test_auxdata() takes any number of arguments.
000650  do_test func-13.1 {
000651    execsql {
000652      SELECT test_auxdata('hello world');
000653    }
000654  } {0}
000655  
000656  do_test func-13.2 {
000657    execsql {
000658      CREATE TABLE t4(a, b);
000659      INSERT INTO t4 VALUES('abc', 'def');
000660      INSERT INTO t4 VALUES('ghi', 'jkl');
000661    }
000662  } {}
000663  do_test func-13.3 {
000664    execsql {
000665      SELECT test_auxdata('hello world') FROM t4;
000666    }
000667  } {0 1}
000668  do_test func-13.4 {
000669    execsql {
000670      SELECT test_auxdata('hello world', 123) FROM t4;
000671    }
000672  } {{0 0} {1 1}}
000673  do_test func-13.5 {
000674    execsql {
000675      SELECT test_auxdata('hello world', a) FROM t4;
000676    }
000677  } {{0 0} {1 0}}
000678  do_test func-13.6 {
000679    execsql {
000680      SELECT test_auxdata('hello'||'world', a) FROM t4;
000681    }
000682  } {{0 0} {1 0}}
000683  
000684  # Test that auxilary data is preserved between calls for SQL variables.
000685  do_test func-13.7 {
000686    set DB [sqlite3_connection_pointer db]
000687    set sql "SELECT test_auxdata( ? , a ) FROM t4;"
000688    set STMT [sqlite3_prepare $DB $sql -1 TAIL]
000689    sqlite3_bind_text $STMT 1 hello\000 -1
000690    set res [list]
000691    while { "SQLITE_ROW"==[sqlite3_step $STMT] } {
000692      lappend res [sqlite3_column_text $STMT 0]
000693    }
000694    lappend res [sqlite3_finalize $STMT]
000695  } {{0 0} {1 0} SQLITE_OK}
000696  
000697  # Test that auxiliary data is discarded when a statement is reset.
000698  do_execsql_test 13.8.1 {
000699    SELECT test_auxdata('constant') FROM t4;
000700  } {0 1}
000701  do_execsql_test 13.8.2 {
000702    SELECT test_auxdata('constant') FROM t4;
000703  } {0 1}
000704  db cache flush
000705  do_execsql_test 13.8.3 {
000706    SELECT test_auxdata('constant') FROM t4;
000707  } {0 1}
000708  set V "one"
000709  do_execsql_test 13.8.4 {
000710    SELECT test_auxdata($V), $V FROM t4;
000711  } {0 one 1 one}
000712  set V "two"
000713  do_execsql_test 13.8.5 {
000714    SELECT test_auxdata($V), $V FROM t4;
000715  } {0 two 1 two}
000716  db cache flush
000717  set V "three"
000718  do_execsql_test 13.8.6 {
000719    SELECT test_auxdata($V), $V FROM t4;
000720  } {0 three 1 three}
000721  
000722  
000723  # Make sure that a function with a very long name is rejected
000724  do_test func-14.1 {
000725    catch {
000726      db function [string repeat X 254] {return "hello"}
000727    } 
000728  } {0}
000729  do_test func-14.2 {
000730    catch {
000731      db function [string repeat X 256] {return "hello"}
000732    }
000733  } {1}
000734  
000735  do_test func-15.1 {
000736    catchsql {select test_error(NULL)}
000737  } {1 {}}
000738  do_test func-15.2 {
000739    catchsql {select test_error('this is the error message')}
000740  } {1 {this is the error message}}
000741  do_test func-15.3 {
000742    catchsql {select test_error('this is the error message',12)}
000743  } {1 {this is the error message}}
000744  do_test func-15.4 {
000745    db errorcode
000746  } {12}
000747  
000748  # Test the quote function for BLOB and NULL values.
000749  do_test func-16.1 {
000750    execsql {
000751      CREATE TABLE tbl2(a, b);
000752    }
000753    set STMT [sqlite3_prepare $::DB "INSERT INTO tbl2 VALUES(?, ?)" -1 TAIL]
000754    sqlite3_bind_blob $::STMT 1 abc 3
000755    sqlite3_step $::STMT
000756    sqlite3_finalize $::STMT
000757    execsql {
000758      SELECT quote(a), quote(b) FROM tbl2;
000759    }
000760  } {X'616263' NULL}
000761  
000762  # Correctly handle function error messages that include %.  Ticket #1354
000763  #
000764  do_test func-17.1 {
000765    proc testfunc1 args {error "Error %d with %s percents %p"}
000766    db function testfunc1 ::testfunc1
000767    catchsql {
000768      SELECT testfunc1(1,2,3);
000769    }
000770  } {1 {Error %d with %s percents %p}}
000771  
000772  # The SUM function should return integer results when all inputs are integer.
000773  #
000774  do_test func-18.1 {
000775    execsql {
000776      CREATE TABLE t5(x);
000777      INSERT INTO t5 VALUES(1);
000778      INSERT INTO t5 VALUES(-99);
000779      INSERT INTO t5 VALUES(10000);
000780      SELECT sum(x) FROM t5;
000781    }
000782  } {9902}
000783  ifcapable floatingpoint {
000784    do_test func-18.2 {
000785      execsql {
000786        INSERT INTO t5 VALUES(0.0);
000787        SELECT sum(x) FROM t5;
000788      }
000789    } {9902.0}
000790  }
000791  
000792  # The sum of nothing is NULL.  But the sum of all NULLs is NULL.
000793  #
000794  # The TOTAL of nothing is 0.0.
000795  #
000796  do_test func-18.3 {
000797    execsql {
000798      DELETE FROM t5;
000799      SELECT sum(x), total(x) FROM t5;
000800    }
000801  } {{} 0.0}
000802  do_test func-18.4 {
000803    execsql {
000804      INSERT INTO t5 VALUES(NULL);
000805      SELECT sum(x), total(x) FROM t5
000806    }
000807  } {{} 0.0}
000808  do_test func-18.5 {
000809    execsql {
000810      INSERT INTO t5 VALUES(NULL);
000811      SELECT sum(x), total(x) FROM t5
000812    }
000813  } {{} 0.0}
000814  do_test func-18.6 {
000815    execsql {
000816      INSERT INTO t5 VALUES(123);
000817      SELECT sum(x), total(x) FROM t5
000818    }
000819  } {123 123.0}
000820  
000821  # Ticket #1664, #1669, #1670, #1674: An integer overflow on SUM causes
000822  # an error. The non-standard TOTAL() function continues to give a helpful
000823  # result.
000824  #
000825  do_test func-18.10 {
000826    execsql {
000827      CREATE TABLE t6(x INTEGER);
000828      INSERT INTO t6 VALUES(1);
000829      INSERT INTO t6 VALUES(1<<62);
000830      SELECT sum(x) - ((1<<62)+1) from t6;
000831    }
000832  } 0
000833  do_test func-18.11 {
000834    execsql {
000835      SELECT typeof(sum(x)) FROM t6
000836    }
000837  } integer
000838  ifcapable floatingpoint {
000839    do_test func-18.12 {
000840      catchsql {
000841        INSERT INTO t6 VALUES(1<<62);
000842        SELECT sum(x) - ((1<<62)*2.0+1) from t6;
000843      }
000844    } {1 {integer overflow}}
000845    do_test func-18.13 {
000846      execsql {
000847        SELECT total(x) - ((1<<62)*2.0+1) FROM t6
000848      }
000849    } 0.0
000850  }
000851  ifcapable !floatingpoint {
000852    do_test func-18.12 {
000853      catchsql {
000854        INSERT INTO t6 VALUES(1<<62);
000855        SELECT sum(x) - ((1<<62)*2+1) from t6;
000856      }
000857    } {1 {integer overflow}}
000858    do_test func-18.13 {
000859      execsql {
000860        SELECT total(x) - ((1<<62)*2+1) FROM t6
000861      }
000862    } 0.0
000863  }
000864  if {[working_64bit_int]} {
000865    do_test func-18.14 {
000866      execsql {
000867        SELECT sum(-9223372036854775805);
000868      }
000869    } -9223372036854775805
000870  }
000871  ifcapable compound&&subquery {
000872  
000873  do_test func-18.15 {
000874    catchsql {
000875      SELECT sum(x) FROM 
000876         (SELECT 9223372036854775807 AS x UNION ALL
000877          SELECT 10 AS x);
000878    }
000879  } {1 {integer overflow}}
000880  if {[working_64bit_int]} {
000881    do_test func-18.16 {
000882      catchsql {
000883        SELECT sum(x) FROM 
000884           (SELECT 9223372036854775807 AS x UNION ALL
000885            SELECT -10 AS x);
000886      }
000887    } {0 9223372036854775797}
000888    do_test func-18.17 {
000889      catchsql {
000890        SELECT sum(x) FROM 
000891           (SELECT -9223372036854775807 AS x UNION ALL
000892            SELECT 10 AS x);
000893      }
000894    } {0 -9223372036854775797}
000895  }
000896  do_test func-18.18 {
000897    catchsql {
000898      SELECT sum(x) FROM 
000899         (SELECT -9223372036854775807 AS x UNION ALL
000900          SELECT -10 AS x);
000901    }
000902  } {1 {integer overflow}}
000903  do_test func-18.19 {
000904    catchsql {
000905      SELECT sum(x) FROM (SELECT 9 AS x UNION ALL SELECT -10 AS x);
000906    }
000907  } {0 -1}
000908  do_test func-18.20 {
000909    catchsql {
000910      SELECT sum(x) FROM (SELECT -9 AS x UNION ALL SELECT 10 AS x);
000911    }
000912  } {0 1}
000913  do_test func-18.21 {
000914    catchsql {
000915      SELECT sum(x) FROM (SELECT -10 AS x UNION ALL SELECT 9 AS x);
000916    }
000917  } {0 -1}
000918  do_test func-18.22 {
000919    catchsql {
000920      SELECT sum(x) FROM (SELECT 10 AS x UNION ALL SELECT -9 AS x);
000921    }
000922  } {0 1}
000923  
000924  } ;# ifcapable compound&&subquery
000925  
000926  # Integer overflow on abs()
000927  #
000928  if {[working_64bit_int]} {
000929    do_test func-18.31 {
000930      catchsql {
000931        SELECT abs(-9223372036854775807);
000932      }
000933    } {0 9223372036854775807}
000934  }
000935  do_test func-18.32 {
000936    catchsql {
000937      SELECT abs(-9223372036854775807-1);
000938    }
000939  } {1 {integer overflow}}
000940  
000941  # The MATCH function exists but is only a stub and always throws an error.
000942  #
000943  do_test func-19.1 {
000944    execsql {
000945      SELECT match(a,b) FROM t1 WHERE 0;
000946    }
000947  } {}
000948  do_test func-19.2 {
000949    catchsql {
000950      SELECT 'abc' MATCH 'xyz';
000951    }
000952  } {1 {unable to use function MATCH in the requested context}}
000953  do_test func-19.3 {
000954    catchsql {
000955      SELECT 'abc' NOT MATCH 'xyz';
000956    }
000957  } {1 {unable to use function MATCH in the requested context}}
000958  do_test func-19.4 {
000959    catchsql {
000960      SELECT match(1,2,3);
000961    }
000962  } {1 {wrong number of arguments to function match()}}
000963  
000964  # Soundex tests.
000965  #
000966  if {![catch {db eval {SELECT soundex('hello')}}]} {
000967    set i 0
000968    foreach {name sdx} {
000969      euler        E460
000970      EULER        E460
000971      Euler        E460
000972      ellery       E460
000973      gauss        G200
000974      ghosh        G200
000975      hilbert      H416
000976      Heilbronn    H416
000977      knuth        K530
000978      kant         K530
000979      Lloyd        L300
000980      LADD         L300
000981      Lukasiewicz  L222
000982      Lissajous    L222
000983      A            A000
000984      12345        ?000
000985    } {
000986      incr i
000987      do_test func-20.$i {
000988        execsql {SELECT soundex($name)}
000989      } $sdx
000990    }
000991  }
000992  
000993  # Tests of the REPLACE function.
000994  #
000995  do_test func-21.1 {
000996    catchsql {
000997      SELECT replace(1,2);
000998    }
000999  } {1 {wrong number of arguments to function replace()}}
001000  do_test func-21.2 {
001001    catchsql {
001002      SELECT replace(1,2,3,4);
001003    }
001004  } {1 {wrong number of arguments to function replace()}}
001005  do_test func-21.3 {
001006    execsql {
001007      SELECT typeof(replace("This is the main test string", NULL, "ALT"));
001008    }
001009  } {null}
001010  do_test func-21.4 {
001011    execsql {
001012      SELECT typeof(replace(NULL, "main", "ALT"));
001013    }
001014  } {null}
001015  do_test func-21.5 {
001016    execsql {
001017      SELECT typeof(replace("This is the main test string", "main", NULL));
001018    }
001019  } {null}
001020  do_test func-21.6 {
001021    execsql {
001022      SELECT replace("This is the main test string", "main", "ALT");
001023    }
001024  } {{This is the ALT test string}}
001025  do_test func-21.7 {
001026    execsql {
001027      SELECT replace("This is the main test string", "main", "larger-main");
001028    }
001029  } {{This is the larger-main test string}}
001030  do_test func-21.8 {
001031    execsql {
001032      SELECT replace("aaaaaaa", "a", "0123456789");
001033    }
001034  } {0123456789012345678901234567890123456789012345678901234567890123456789}
001035  
001036  ifcapable tclvar {
001037    do_test func-21.9 {
001038      # Attempt to exploit a buffer-overflow that at one time existed 
001039      # in the REPLACE function. 
001040      set ::str "[string repeat A 29998]CC[string repeat A 35537]"
001041      set ::rep [string repeat B 65536]
001042      execsql {
001043        SELECT LENGTH(REPLACE($::str, 'C', $::rep));
001044      }
001045    } [expr 29998 + 2*65536 + 35537]
001046  }
001047  
001048  # Tests for the TRIM, LTRIM and RTRIM functions.
001049  #
001050  do_test func-22.1 {
001051    catchsql {SELECT trim(1,2,3)}
001052  } {1 {wrong number of arguments to function trim()}}
001053  do_test func-22.2 {
001054    catchsql {SELECT ltrim(1,2,3)}
001055  } {1 {wrong number of arguments to function ltrim()}}
001056  do_test func-22.3 {
001057    catchsql {SELECT rtrim(1,2,3)}
001058  } {1 {wrong number of arguments to function rtrim()}}
001059  do_test func-22.4 {
001060    execsql {SELECT trim('  hi  ');}
001061  } {hi}
001062  do_test func-22.5 {
001063    execsql {SELECT ltrim('  hi  ');}
001064  } {{hi  }}
001065  do_test func-22.6 {
001066    execsql {SELECT rtrim('  hi  ');}
001067  } {{  hi}}
001068  do_test func-22.7 {
001069    execsql {SELECT trim('  hi  ','xyz');}
001070  } {{  hi  }}
001071  do_test func-22.8 {
001072    execsql {SELECT ltrim('  hi  ','xyz');}
001073  } {{  hi  }}
001074  do_test func-22.9 {
001075    execsql {SELECT rtrim('  hi  ','xyz');}
001076  } {{  hi  }}
001077  do_test func-22.10 {
001078    execsql {SELECT trim('xyxzy  hi  zzzy','xyz');}
001079  } {{  hi  }}
001080  do_test func-22.11 {
001081    execsql {SELECT ltrim('xyxzy  hi  zzzy','xyz');}
001082  } {{  hi  zzzy}}
001083  do_test func-22.12 {
001084    execsql {SELECT rtrim('xyxzy  hi  zzzy','xyz');}
001085  } {{xyxzy  hi  }}
001086  do_test func-22.13 {
001087    execsql {SELECT trim('  hi  ','');}
001088  } {{  hi  }}
001089  if {[db one {PRAGMA encoding}]=="UTF-8"} {
001090    do_test func-22.14 {
001091      execsql {SELECT hex(trim(x'c280e1bfbff48fbfbf6869',x'6162e1bfbfc280'))}
001092    } {F48FBFBF6869}
001093    do_test func-22.15 {
001094      execsql {SELECT hex(trim(x'6869c280e1bfbff48fbfbf61',
001095                               x'6162e1bfbfc280f48fbfbf'))}
001096    } {6869}
001097    do_test func-22.16 {
001098      execsql {SELECT hex(trim(x'ceb1ceb2ceb3',x'ceb1'));}
001099    } {CEB2CEB3}
001100  }
001101  do_test func-22.20 {
001102    execsql {SELECT typeof(trim(NULL));}
001103  } {null}
001104  do_test func-22.21 {
001105    execsql {SELECT typeof(trim(NULL,'xyz'));}
001106  } {null}
001107  do_test func-22.22 {
001108    execsql {SELECT typeof(trim('hello',NULL));}
001109  } {null}
001110  
001111  # This is to test the deprecated sqlite3_aggregate_count() API.
001112  #
001113  ifcapable deprecated {
001114    do_test func-23.1 {
001115      sqlite3_create_aggregate db
001116      execsql {
001117        SELECT legacy_count() FROM t6;
001118      }
001119    } {3}
001120  }
001121  
001122  # The group_concat() function.
001123  #
001124  do_test func-24.1 {
001125    execsql {
001126      SELECT group_concat(t1) FROM tbl1
001127    }
001128  } {this,program,is,free,software}
001129  do_test func-24.2 {
001130    execsql {
001131      SELECT group_concat(t1,' ') FROM tbl1
001132    }
001133  } {{this program is free software}}
001134  do_test func-24.3 {
001135    execsql {
001136      SELECT group_concat(t1,' ' || rowid || ' ') FROM tbl1
001137    }
001138  } {{this 2 program 3 is 4 free 5 software}}
001139  do_test func-24.4 {
001140    execsql {
001141      SELECT group_concat(NULL,t1) FROM tbl1
001142    }
001143  } {{}}
001144  do_test func-24.5 {
001145    execsql {
001146      SELECT group_concat(t1,NULL) FROM tbl1
001147    }
001148  } {thisprogramisfreesoftware}
001149  do_test func-24.6 {
001150    execsql {
001151      SELECT 'BEGIN-'||group_concat(t1) FROM tbl1
001152    }
001153  } {BEGIN-this,program,is,free,software}
001154  
001155  # Ticket #3179:  Make sure aggregate functions can take many arguments.
001156  # None of the built-in aggregates do this, so use the md5sum() from the
001157  # test extensions.
001158  #
001159  unset -nocomplain midargs
001160  set midargs {}
001161  unset -nocomplain midres
001162  set midres {}
001163  unset -nocomplain result
001164  for {set i 1} {$i<[sqlite3_limit db SQLITE_LIMIT_FUNCTION_ARG -1]} {incr i} {
001165    append midargs ,'/$i'
001166    append midres /$i
001167    set result [md5 \
001168       "this${midres}program${midres}is${midres}free${midres}software${midres}"]
001169    set sql "SELECT md5sum(t1$midargs) FROM tbl1"
001170    do_test func-24.7.$i {
001171       db eval $::sql
001172    } $result
001173  }
001174  
001175  # Ticket #3806.  If the initial string in a group_concat is an empty
001176  # string, the separator that follows should still be present.
001177  #
001178  do_test func-24.8 {
001179    execsql {
001180      SELECT group_concat(CASE t1 WHEN 'this' THEN '' ELSE t1 END) FROM tbl1
001181    }
001182  } {,program,is,free,software}
001183  do_test func-24.9 {
001184    execsql {
001185      SELECT group_concat(CASE WHEN t1!='software' THEN '' ELSE t1 END) FROM tbl1
001186    }
001187  } {,,,,software}
001188  
001189  # Ticket #3923.  Initial empty strings have a separator.  But initial
001190  # NULLs do not.
001191  #
001192  do_test func-24.10 {
001193    execsql {
001194      SELECT group_concat(CASE t1 WHEN 'this' THEN null ELSE t1 END) FROM tbl1
001195    }
001196  } {program,is,free,software}
001197  do_test func-24.11 {
001198    execsql {
001199     SELECT group_concat(CASE WHEN t1!='software' THEN null ELSE t1 END) FROM tbl1
001200    }
001201  } {software}
001202  do_test func-24.12 {
001203    execsql {
001204      SELECT group_concat(CASE t1 WHEN 'this' THEN ''
001205                            WHEN 'program' THEN null ELSE t1 END) FROM tbl1
001206    }
001207  } {,is,free,software}
001208  # Tests to verify ticket http://www.sqlite.org/src/tktview/55746f9e65f8587c0
001209  do_test func-24.13 {
001210    execsql {
001211      SELECT typeof(group_concat(x)) FROM (SELECT '' AS x);
001212    }
001213  } {text}
001214  do_test func-24.14 {
001215    execsql {
001216      SELECT typeof(group_concat(x,''))
001217        FROM (SELECT '' AS x UNION ALL SELECT '');
001218    }
001219  } {text}
001220  
001221  
001222  # Use the test_isolation function to make sure that type conversions
001223  # on function arguments do not effect subsequent arguments.
001224  #
001225  do_test func-25.1 {
001226    execsql {SELECT test_isolation(t1,t1) FROM tbl1}
001227  } {this program is free software}
001228  
001229  # Try to misuse the sqlite3_create_function() interface.  Verify that
001230  # errors are returned.
001231  #
001232  do_test func-26.1 {
001233    abuse_create_function db
001234  } {}
001235  
001236  # The previous test (func-26.1) registered a function with a very long
001237  # function name that takes many arguments and always returns NULL.  Verify
001238  # that this function works correctly.
001239  #
001240  do_test func-26.2 {
001241    set a {}
001242    for {set i 1} {$i<=$::SQLITE_MAX_FUNCTION_ARG} {incr i} {
001243      lappend a $i
001244    }
001245    db eval "
001246       SELECT nullx_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789([join $a ,]);
001247    "
001248  } {{}}
001249  do_test func-26.3 {
001250    set a {}
001251    for {set i 1} {$i<=$::SQLITE_MAX_FUNCTION_ARG+1} {incr i} {
001252      lappend a $i
001253    }
001254    catchsql "
001255       SELECT nullx_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789([join $a ,]);
001256    "
001257  } {1 {too many arguments on function nullx_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789}}
001258  do_test func-26.4 {
001259    set a {}
001260    for {set i 1} {$i<=$::SQLITE_MAX_FUNCTION_ARG-1} {incr i} {
001261      lappend a $i
001262    }
001263    catchsql "
001264       SELECT nullx_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789([join $a ,]);
001265    "
001266  } {1 {wrong number of arguments to function nullx_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789()}}
001267  do_test func-26.5 {
001268    catchsql "
001269       SELECT nullx_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_12345678a(0);
001270    "
001271  } {1 {no such function: nullx_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_12345678a}}
001272  do_test func-26.6 {
001273    catchsql "
001274       SELECT nullx_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789a(0);
001275    "
001276  } {1 {no such function: nullx_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789a}}
001277  
001278  do_test func-27.1 {
001279    catchsql {SELECT coalesce()}
001280  } {1 {wrong number of arguments to function coalesce()}}
001281  do_test func-27.2 {
001282    catchsql {SELECT coalesce(1)}
001283  } {1 {wrong number of arguments to function coalesce()}}
001284  do_test func-27.3 {
001285    catchsql {SELECT coalesce(1,2)}
001286  } {0 1}
001287  
001288  # Ticket 2d401a94287b5
001289  # Unknown function in a DEFAULT expression causes a segfault.
001290  #
001291  do_test func-28.1 {
001292    db eval {
001293      CREATE TABLE t28(x, y DEFAULT(nosuchfunc(1)));
001294    }
001295    catchsql {
001296      INSERT INTO t28(x) VALUES(1);
001297    }
001298  } {1 {unknown function: nosuchfunc()}}
001299  
001300  # Verify that the length() and typeof() functions do not actually load
001301  # the content of their argument.
001302  #
001303  do_test func-29.1 {
001304    db eval {
001305      CREATE TABLE t29(id INTEGER PRIMARY KEY, x, y);
001306      INSERT INTO t29 VALUES(1, 2, 3), (2, NULL, 4), (3, 4.5, 5);
001307      INSERT INTO t29 VALUES(4, randomblob(1000000), 6);
001308      INSERT INTO t29 VALUES(5, "hello", 7);
001309    }
001310    db close
001311    sqlite3 db test.db
001312    sqlite3_db_status db CACHE_MISS 1
001313    db eval {SELECT typeof(x), length(x), typeof(y) FROM t29 ORDER BY id}
001314  } {integer 1 integer null {} integer real 3 integer blob 1000000 integer text 5 integer}
001315  do_test func-29.2 {
001316    set x [lindex [sqlite3_db_status db CACHE_MISS 1] 1]
001317    if {$x<5} {set x 1}
001318    set x
001319  } {1}
001320  do_test func-29.3 {
001321    db close
001322    sqlite3 db test.db
001323    sqlite3_db_status db CACHE_MISS 1
001324    db eval {SELECT typeof(+x) FROM t29 ORDER BY id}
001325  } {integer null real blob text}
001326  if {[permutation] != "mmap"} {
001327    ifcapable !direct_read {
001328      do_test func-29.4 {
001329        set x [lindex [sqlite3_db_status db CACHE_MISS 1] 1]
001330        if {$x>100} {set x many}
001331        set x
001332      } {many}
001333    }
001334  }
001335  do_test func-29.5 {
001336    db close
001337    sqlite3 db test.db
001338    sqlite3_db_status db CACHE_MISS 1
001339    db eval {SELECT sum(length(x)) FROM t29}
001340  } {1000009}
001341  do_test func-29.6 {
001342    set x [lindex [sqlite3_db_status db CACHE_MISS 1] 1]
001343    if {$x<5} {set x 1}
001344    set x
001345  } {1}
001346  
001347  # The OP_Column opcode has an optimization that avoids loading content
001348  # for fields with content-length=0 when the content offset is on an overflow
001349  # page.  Make sure the optimization works.
001350  #
001351  do_execsql_test func-29.10 {
001352    CREATE TABLE t29b(a,b,c,d,e,f,g,h,i);
001353    INSERT INTO t29b 
001354     VALUES(1, hex(randomblob(2000)), null, 0, 1, '', zeroblob(0),'x',x'01');
001355    SELECT typeof(c), typeof(d), typeof(e), typeof(f),
001356           typeof(g), typeof(h), typeof(i) FROM t29b;
001357  } {null integer integer text blob text blob}
001358  do_execsql_test func-29.11 {
001359    SELECT length(f), length(g), length(h), length(i) FROM t29b;
001360  } {0 0 1 1}
001361  do_execsql_test func-29.12 {
001362    SELECT quote(f), quote(g), quote(h), quote(i) FROM t29b;
001363  } {'' X'' 'x' X'01'}
001364  
001365  # EVIDENCE-OF: R-29701-50711 The unicode(X) function returns the numeric
001366  # unicode code point corresponding to the first character of the string
001367  # X.
001368  #
001369  # EVIDENCE-OF: R-55469-62130 The char(X1,X2,...,XN) function returns a
001370  # string composed of characters having the unicode code point values of
001371  # integers X1 through XN, respectively.
001372  #
001373  do_execsql_test func-30.1 {SELECT unicode('$');} 36
001374  do_execsql_test func-30.2 [subst {SELECT unicode('\u00A2');}] 162
001375  do_execsql_test func-30.3 [subst {SELECT unicode('\u20AC');}] 8364
001376  do_execsql_test func-30.4 {SELECT char(36,162,8364);} [subst {$\u00A2\u20AC}]
001377  
001378  for {set i 1} {$i<0xd800} {incr i 13} {
001379    do_execsql_test func-30.5.$i {SELECT unicode(char($i))} $i
001380  }
001381  for {set i 57344} {$i<=0xfffd} {incr i 17} {
001382    if {$i==0xfeff} continue
001383    do_execsql_test func-30.5.$i {SELECT unicode(char($i))} $i
001384  }
001385  for {set i 65536} {$i<=0x10ffff} {incr i 139} {
001386    do_execsql_test func-30.5.$i {SELECT unicode(char($i))} $i
001387  }
001388  
001389  # Test char().
001390  #
001391  do_execsql_test func-31.1 { 
001392    SELECT char(), length(char()), typeof(char()) 
001393  } {{} 0 text}
001394  finish_test