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    do_test func-4.39 {
000319      string tolower [db eval {SELECT round(1e500), round(-1e500);}]
000320    } {inf -inf}
000321  }
000322  
000323  # Test the upper() and lower() functions
000324  #
000325  do_test func-5.1 {
000326    execsql {SELECT upper(t1) FROM tbl1}
000327  } {THIS PROGRAM IS FREE SOFTWARE}
000328  do_test func-5.2 {
000329    execsql {SELECT lower(upper(t1)) FROM tbl1}
000330  } {this program is free software}
000331  do_test func-5.3 {
000332    execsql {SELECT upper(a), lower(a) FROM t2}
000333  } {1 1 {} {} 345 345 {} {} 67890 67890}
000334  ifcapable !icu {
000335    do_test func-5.4 {
000336      catchsql {SELECT upper(a,5) FROM t2}
000337    } {1 {wrong number of arguments to function upper()}}
000338  }
000339  do_test func-5.5 {
000340    catchsql {SELECT upper(*) FROM t2}
000341  } {1 {wrong number of arguments to function upper()}}
000342  
000343  # Test the coalesce() and nullif() functions
000344  #
000345  do_test func-6.1 {
000346    execsql {SELECT coalesce(a,'xyz') FROM t2}
000347  } {1 xyz 345 xyz 67890}
000348  do_test func-6.2 {
000349    execsql {SELECT coalesce(upper(a),'nil') FROM t2}
000350  } {1 nil 345 nil 67890}
000351  do_test func-6.3 {
000352    execsql {SELECT coalesce(nullif(1,1),'nil')}
000353  } {nil}
000354  do_test func-6.4 {
000355    execsql {SELECT coalesce(nullif(1,2),'nil')}
000356  } {1}
000357  do_test func-6.5 {
000358    execsql {SELECT coalesce(nullif(1,NULL),'nil')}
000359  } {1}
000360  
000361  
000362  # Test the last_insert_rowid() function
000363  #
000364  do_test func-7.1 {
000365    execsql {SELECT last_insert_rowid()}
000366  } [db last_insert_rowid]
000367  
000368  # Tests for aggregate functions and how they handle NULLs.
000369  #
000370  ifcapable floatingpoint {
000371    do_test func-8.1 {
000372      ifcapable explain {
000373        execsql {EXPLAIN SELECT sum(a) FROM t2;}
000374      }
000375      execsql {
000376        SELECT sum(a), count(a), round(avg(a),2), min(a), max(a), count(*) FROM t2;
000377      }
000378    } {68236 3 22745.33 1 67890 5}
000379  }
000380  ifcapable !floatingpoint {
000381    do_test func-8.1 {
000382      ifcapable explain {
000383        execsql {EXPLAIN SELECT sum(a) FROM t2;}
000384      }
000385      execsql {
000386        SELECT sum(a), count(a), avg(a), min(a), max(a), count(*) FROM t2;
000387      }
000388    } {68236 3 22745.0 1 67890 5}
000389  }
000390  do_test func-8.2 {
000391    execsql {
000392      SELECT max('z+'||a||'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP') FROM t2;
000393    }
000394  } {z+67890abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP}
000395  
000396  ifcapable tempdb {
000397    do_test func-8.3 {
000398      execsql {
000399        CREATE TEMP TABLE t3 AS SELECT a FROM t2 ORDER BY a DESC;
000400        SELECT min('z+'||a||'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP') FROM t3;
000401      }
000402    } {z+1abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP}
000403  } else {
000404    do_test func-8.3 {
000405      execsql {
000406        CREATE TABLE t3 AS SELECT a FROM t2 ORDER BY a DESC;
000407        SELECT min('z+'||a||'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP') FROM t3;
000408      }
000409    } {z+1abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP}
000410  }
000411  do_test func-8.4 {
000412    execsql {
000413      SELECT max('z+'||a||'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP') FROM t3;
000414    }
000415  } {z+67890abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP}
000416  ifcapable compound {
000417    do_test func-8.5 {
000418      execsql {
000419        SELECT sum(x) FROM (SELECT '9223372036' || '854775807' AS x
000420                            UNION ALL SELECT -9223372036854775807)
000421      }
000422    } {0}
000423    do_test func-8.6 {
000424      execsql {
000425        SELECT typeof(sum(x)) FROM (SELECT '9223372036' || '854775807' AS x
000426                            UNION ALL SELECT -9223372036854775807)
000427      }
000428    } {integer}
000429    do_test func-8.7 {
000430      execsql {
000431        SELECT typeof(sum(x)) FROM (SELECT '9223372036' || '854775808' AS x
000432                            UNION ALL SELECT -9223372036854775807)
000433      }
000434    } {real}
000435  ifcapable floatingpoint {
000436    do_test func-8.8 {
000437      execsql {
000438        SELECT sum(x)>0.0 FROM (SELECT '9223372036' || '854775808' AS x
000439                            UNION ALL SELECT -9223372036850000000)
000440      }
000441    } {1}
000442  }
000443  ifcapable !floatingpoint {
000444    do_test func-8.8 {
000445      execsql {
000446        SELECT sum(x)>0 FROM (SELECT '9223372036' || '854775808' AS x
000447                            UNION ALL SELECT -9223372036850000000)
000448      }
000449    } {1}
000450  }
000451  }
000452  
000453  # How do you test the random() function in a meaningful, deterministic way?
000454  #
000455  do_test func-9.1 {
000456    execsql {
000457      SELECT random() is not null;
000458    }
000459  } {1}
000460  do_test func-9.2 {
000461    execsql {
000462      SELECT typeof(random());
000463    }
000464  } {integer}
000465  do_test func-9.3 {
000466    execsql {
000467      SELECT randomblob(32) is not null;
000468    }
000469  } {1}
000470  do_test func-9.4 {
000471    execsql {
000472      SELECT typeof(randomblob(32));
000473    }
000474  } {blob}
000475  do_test func-9.5 {
000476    execsql {
000477      SELECT length(randomblob(32)), length(randomblob(-5)),
000478             length(randomblob(2000))
000479    }
000480  } {32 1 2000}
000481  
000482  # The "hex()" function was added in order to be able to render blobs
000483  # generated by randomblob().  So this seems like a good place to test
000484  # hex().
000485  #
000486  ifcapable bloblit {
000487    do_test func-9.10 {
000488      execsql {SELECT hex(x'00112233445566778899aAbBcCdDeEfF')}
000489    } {00112233445566778899AABBCCDDEEFF}
000490  }
000491  set encoding [db one {PRAGMA encoding}]
000492  if {$encoding=="UTF-16le"} {
000493    do_test func-9.11-utf16le {
000494      execsql {SELECT hex(replace('abcdefg','ef','12'))}
000495    } {6100620063006400310032006700}
000496    do_test func-9.12-utf16le {
000497      execsql {SELECT hex(replace('abcdefg','','12'))}
000498    } {6100620063006400650066006700}
000499    do_test func-9.13-utf16le {
000500      execsql {SELECT hex(replace('aabcdefg','a','aaa'))}
000501    } {610061006100610061006100620063006400650066006700}
000502  } elseif {$encoding=="UTF-8"} {
000503    do_test func-9.11-utf8 {
000504      execsql {SELECT hex(replace('abcdefg','ef','12'))}
000505    } {61626364313267}
000506    do_test func-9.12-utf8 {
000507      execsql {SELECT hex(replace('abcdefg','','12'))}
000508    } {61626364656667}
000509    do_test func-9.13-utf8 {
000510      execsql {SELECT hex(replace('aabcdefg','a','aaa'))}
000511    } {616161616161626364656667}
000512  }
000513  do_execsql_test func-9.14 {
000514    WITH RECURSIVE c(x) AS (
000515       VALUES(1)
000516       UNION ALL
000517       SELECT x+1 FROM c WHERE x<1040
000518    )
000519    SELECT 
000520      count(*),
000521      sum(length(replace(printf('abc%.*cxyz',x,'m'),'m','nnnn'))-(6+x*4))
000522    FROM c;
000523  } {1040 0}
000524    
000525  # Use the "sqlite_register_test_function" TCL command which is part of
000526  # the text fixture in order to verify correct operation of some of
000527  # the user-defined SQL function APIs that are not used by the built-in
000528  # functions.
000529  #
000530  set ::DB [sqlite3_connection_pointer db]
000531  sqlite_register_test_function $::DB testfunc
000532  do_test func-10.1 {
000533    catchsql {
000534      SELECT testfunc(NULL,NULL);
000535    }
000536  } {1 {first argument should be one of: int int64 string double null value}}
000537  do_test func-10.2 {
000538    execsql {
000539      SELECT testfunc(
000540       'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ',
000541       'int', 1234
000542      );
000543    }
000544  } {1234}
000545  do_test func-10.3 {
000546    execsql {
000547      SELECT testfunc(
000548       'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ',
000549       'string', NULL
000550      );
000551    }
000552  } {{}}
000553  
000554  ifcapable floatingpoint {
000555    do_test func-10.4 {
000556      execsql {
000557        SELECT testfunc(
000558         'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ',
000559         'double', 1.234
000560        );
000561      }
000562    } {1.234}
000563    do_test func-10.5 {
000564      execsql {
000565        SELECT testfunc(
000566         'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ',
000567         'int', 1234,
000568         'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ',
000569         'string', NULL,
000570         'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ',
000571         'double', 1.234,
000572         'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ',
000573         'int', 1234,
000574         'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ',
000575         'string', NULL,
000576         'string', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ',
000577         'double', 1.234
000578        );
000579      }
000580    } {1.234}
000581  }
000582  
000583  # Test the built-in sqlite_version(*) SQL function.
000584  #
000585  do_test func-11.1 {
000586    execsql {
000587      SELECT sqlite_version(*);
000588    }
000589  } [sqlite3 -version]
000590  
000591  # Test that destructors passed to sqlite3 by calls to sqlite3_result_text()
000592  # etc. are called. These tests use two special user-defined functions
000593  # (implemented in func.c) only available in test builds. 
000594  #
000595  # Function test_destructor() takes one argument and returns a copy of the
000596  # text form of that argument. A destructor is associated with the return
000597  # value. Function test_destructor_count() returns the number of outstanding
000598  # destructor calls for values returned by test_destructor().
000599  #
000600  if {[db eval {PRAGMA encoding}]=="UTF-8"} {
000601    do_test func-12.1-utf8 {
000602      execsql {
000603        SELECT test_destructor('hello world'), test_destructor_count();
000604      }
000605    } {{hello world} 1}
000606  } else {
000607      ifcapable {utf16} {
000608        do_test func-12.1-utf16 {
000609          execsql {
000610            SELECT test_destructor16('hello world'), test_destructor_count();
000611          }
000612        } {{hello world} 1}
000613      }
000614  }
000615  do_test func-12.2 {
000616    execsql {
000617      SELECT test_destructor_count();
000618    }
000619  } {0}
000620  do_test func-12.3 {
000621    execsql {
000622      SELECT test_destructor('hello')||' world'
000623    }
000624  } {{hello world}}
000625  do_test func-12.4 {
000626    execsql {
000627      SELECT test_destructor_count();
000628    }
000629  } {0}
000630  do_test func-12.5 {
000631    execsql {
000632      CREATE TABLE t4(x);
000633      INSERT INTO t4 VALUES(test_destructor('hello'));
000634      INSERT INTO t4 VALUES(test_destructor('world'));
000635      SELECT min(test_destructor(x)), max(test_destructor(x)) FROM t4;
000636    }
000637  } {hello world}
000638  do_test func-12.6 {
000639    execsql {
000640      SELECT test_destructor_count();
000641    }
000642  } {0}
000643  do_test func-12.7 {
000644    execsql {
000645      DROP TABLE t4;
000646    }
000647  } {}
000648  
000649  
000650  # Test that the auxdata API for scalar functions works. This test uses
000651  # a special user-defined function only available in test builds,
000652  # test_auxdata(). Function test_auxdata() takes any number of arguments.
000653  do_test func-13.1 {
000654    execsql {
000655      SELECT test_auxdata('hello world');
000656    }
000657  } {0}
000658  
000659  do_test func-13.2 {
000660    execsql {
000661      CREATE TABLE t4(a, b);
000662      INSERT INTO t4 VALUES('abc', 'def');
000663      INSERT INTO t4 VALUES('ghi', 'jkl');
000664    }
000665  } {}
000666  do_test func-13.3 {
000667    execsql {
000668      SELECT test_auxdata('hello world') FROM t4;
000669    }
000670  } {0 1}
000671  do_test func-13.4 {
000672    execsql {
000673      SELECT test_auxdata('hello world', 123) FROM t4;
000674    }
000675  } {{0 0} {1 1}}
000676  do_test func-13.5 {
000677    execsql {
000678      SELECT test_auxdata('hello world', a) FROM t4;
000679    }
000680  } {{0 0} {1 0}}
000681  do_test func-13.6 {
000682    execsql {
000683      SELECT test_auxdata('hello'||'world', a) FROM t4;
000684    }
000685  } {{0 0} {1 0}}
000686  
000687  # Test that auxilary data is preserved between calls for SQL variables.
000688  do_test func-13.7 {
000689    set DB [sqlite3_connection_pointer db]
000690    set sql "SELECT test_auxdata( ? , a ) FROM t4;"
000691    set STMT [sqlite3_prepare $DB $sql -1 TAIL]
000692    sqlite3_bind_text $STMT 1 hello\000 -1
000693    set res [list]
000694    while { "SQLITE_ROW"==[sqlite3_step $STMT] } {
000695      lappend res [sqlite3_column_text $STMT 0]
000696    }
000697    lappend res [sqlite3_finalize $STMT]
000698  } {{0 0} {1 0} SQLITE_OK}
000699  
000700  # Test that auxiliary data is discarded when a statement is reset.
000701  do_execsql_test 13.8.1 {
000702    SELECT test_auxdata('constant') FROM t4;
000703  } {0 1}
000704  do_execsql_test 13.8.2 {
000705    SELECT test_auxdata('constant') FROM t4;
000706  } {0 1}
000707  db cache flush
000708  do_execsql_test 13.8.3 {
000709    SELECT test_auxdata('constant') FROM t4;
000710  } {0 1}
000711  set V "one"
000712  do_execsql_test 13.8.4 {
000713    SELECT test_auxdata($V), $V FROM t4;
000714  } {0 one 1 one}
000715  set V "two"
000716  do_execsql_test 13.8.5 {
000717    SELECT test_auxdata($V), $V FROM t4;
000718  } {0 two 1 two}
000719  db cache flush
000720  set V "three"
000721  do_execsql_test 13.8.6 {
000722    SELECT test_auxdata($V), $V FROM t4;
000723  } {0 three 1 three}
000724  
000725  
000726  # Make sure that a function with a very long name is rejected
000727  do_test func-14.1 {
000728    catch {
000729      db function [string repeat X 254] {return "hello"}
000730    } 
000731  } {0}
000732  do_test func-14.2 {
000733    catch {
000734      db function [string repeat X 256] {return "hello"}
000735    }
000736  } {1}
000737  
000738  do_test func-15.1 {
000739    catchsql {select test_error(NULL)}
000740  } {1 {}}
000741  do_test func-15.2 {
000742    catchsql {select test_error('this is the error message')}
000743  } {1 {this is the error message}}
000744  do_test func-15.3 {
000745    catchsql {select test_error('this is the error message',12)}
000746  } {1 {this is the error message}}
000747  do_test func-15.4 {
000748    db errorcode
000749  } {12}
000750  
000751  # Test the quote function for BLOB and NULL values.
000752  do_test func-16.1 {
000753    execsql {
000754      CREATE TABLE tbl2(a, b);
000755    }
000756    set STMT [sqlite3_prepare $::DB "INSERT INTO tbl2 VALUES(?, ?)" -1 TAIL]
000757    sqlite3_bind_blob $::STMT 1 abc 3
000758    sqlite3_step $::STMT
000759    sqlite3_finalize $::STMT
000760    execsql {
000761      SELECT quote(a), quote(b) FROM tbl2;
000762    }
000763  } {X'616263' NULL}
000764  
000765  # Correctly handle function error messages that include %.  Ticket #1354
000766  #
000767  do_test func-17.1 {
000768    proc testfunc1 args {error "Error %d with %s percents %p"}
000769    db function testfunc1 ::testfunc1
000770    catchsql {
000771      SELECT testfunc1(1,2,3);
000772    }
000773  } {1 {Error %d with %s percents %p}}
000774  
000775  # The SUM function should return integer results when all inputs are integer.
000776  #
000777  do_test func-18.1 {
000778    execsql {
000779      CREATE TABLE t5(x);
000780      INSERT INTO t5 VALUES(1);
000781      INSERT INTO t5 VALUES(-99);
000782      INSERT INTO t5 VALUES(10000);
000783      SELECT sum(x) FROM t5;
000784    }
000785  } {9902}
000786  ifcapable floatingpoint {
000787    do_test func-18.2 {
000788      execsql {
000789        INSERT INTO t5 VALUES(0.0);
000790        SELECT sum(x) FROM t5;
000791      }
000792    } {9902.0}
000793  }
000794  
000795  # The sum of nothing is NULL.  But the sum of all NULLs is NULL.
000796  #
000797  # The TOTAL of nothing is 0.0.
000798  #
000799  do_test func-18.3 {
000800    execsql {
000801      DELETE FROM t5;
000802      SELECT sum(x), total(x) FROM t5;
000803    }
000804  } {{} 0.0}
000805  do_test func-18.4 {
000806    execsql {
000807      INSERT INTO t5 VALUES(NULL);
000808      SELECT sum(x), total(x) FROM t5
000809    }
000810  } {{} 0.0}
000811  do_test func-18.5 {
000812    execsql {
000813      INSERT INTO t5 VALUES(NULL);
000814      SELECT sum(x), total(x) FROM t5
000815    }
000816  } {{} 0.0}
000817  do_test func-18.6 {
000818    execsql {
000819      INSERT INTO t5 VALUES(123);
000820      SELECT sum(x), total(x) FROM t5
000821    }
000822  } {123 123.0}
000823  
000824  # Ticket #1664, #1669, #1670, #1674: An integer overflow on SUM causes
000825  # an error. The non-standard TOTAL() function continues to give a helpful
000826  # result.
000827  #
000828  do_test func-18.10 {
000829    execsql {
000830      CREATE TABLE t6(x INTEGER);
000831      INSERT INTO t6 VALUES(1);
000832      INSERT INTO t6 VALUES(1<<62);
000833      SELECT sum(x) - ((1<<62)+1) from t6;
000834    }
000835  } 0
000836  do_test func-18.11 {
000837    execsql {
000838      SELECT typeof(sum(x)) FROM t6
000839    }
000840  } integer
000841  ifcapable floatingpoint {
000842    do_test func-18.12 {
000843      catchsql {
000844        INSERT INTO t6 VALUES(1<<62);
000845        SELECT sum(x) - ((1<<62)*2.0+1) from t6;
000846      }
000847    } {1 {integer overflow}}
000848    do_test func-18.13 {
000849      execsql {
000850        SELECT total(x) - ((1<<62)*2.0+1) FROM t6
000851      }
000852    } 0.0
000853  }
000854  ifcapable !floatingpoint {
000855    do_test func-18.12 {
000856      catchsql {
000857        INSERT INTO t6 VALUES(1<<62);
000858        SELECT sum(x) - ((1<<62)*2+1) from t6;
000859      }
000860    } {1 {integer overflow}}
000861    do_test func-18.13 {
000862      execsql {
000863        SELECT total(x) - ((1<<62)*2+1) FROM t6
000864      }
000865    } 0.0
000866  }
000867  if {[working_64bit_int]} {
000868    do_test func-18.14 {
000869      execsql {
000870        SELECT sum(-9223372036854775805);
000871      }
000872    } -9223372036854775805
000873  }
000874  ifcapable compound&&subquery {
000875  
000876  do_test func-18.15 {
000877    catchsql {
000878      SELECT sum(x) FROM 
000879         (SELECT 9223372036854775807 AS x UNION ALL
000880          SELECT 10 AS x);
000881    }
000882  } {1 {integer overflow}}
000883  if {[working_64bit_int]} {
000884    do_test func-18.16 {
000885      catchsql {
000886        SELECT sum(x) FROM 
000887           (SELECT 9223372036854775807 AS x UNION ALL
000888            SELECT -10 AS x);
000889      }
000890    } {0 9223372036854775797}
000891    do_test func-18.17 {
000892      catchsql {
000893        SELECT sum(x) FROM 
000894           (SELECT -9223372036854775807 AS x UNION ALL
000895            SELECT 10 AS x);
000896      }
000897    } {0 -9223372036854775797}
000898  }
000899  do_test func-18.18 {
000900    catchsql {
000901      SELECT sum(x) FROM 
000902         (SELECT -9223372036854775807 AS x UNION ALL
000903          SELECT -10 AS x);
000904    }
000905  } {1 {integer overflow}}
000906  do_test func-18.19 {
000907    catchsql {
000908      SELECT sum(x) FROM (SELECT 9 AS x UNION ALL SELECT -10 AS x);
000909    }
000910  } {0 -1}
000911  do_test func-18.20 {
000912    catchsql {
000913      SELECT sum(x) FROM (SELECT -9 AS x UNION ALL SELECT 10 AS x);
000914    }
000915  } {0 1}
000916  do_test func-18.21 {
000917    catchsql {
000918      SELECT sum(x) FROM (SELECT -10 AS x UNION ALL SELECT 9 AS x);
000919    }
000920  } {0 -1}
000921  do_test func-18.22 {
000922    catchsql {
000923      SELECT sum(x) FROM (SELECT 10 AS x UNION ALL SELECT -9 AS x);
000924    }
000925  } {0 1}
000926  
000927  } ;# ifcapable compound&&subquery
000928  
000929  # Integer overflow on abs()
000930  #
000931  if {[working_64bit_int]} {
000932    do_test func-18.31 {
000933      catchsql {
000934        SELECT abs(-9223372036854775807);
000935      }
000936    } {0 9223372036854775807}
000937  }
000938  do_test func-18.32 {
000939    catchsql {
000940      SELECT abs(-9223372036854775807-1);
000941    }
000942  } {1 {integer overflow}}
000943  
000944  # The MATCH function exists but is only a stub and always throws an error.
000945  #
000946  do_test func-19.1 {
000947    execsql {
000948      SELECT match(a,b) FROM t1 WHERE 0;
000949    }
000950  } {}
000951  do_test func-19.2 {
000952    catchsql {
000953      SELECT 'abc' MATCH 'xyz';
000954    }
000955  } {1 {unable to use function MATCH in the requested context}}
000956  do_test func-19.3 {
000957    catchsql {
000958      SELECT 'abc' NOT MATCH 'xyz';
000959    }
000960  } {1 {unable to use function MATCH in the requested context}}
000961  do_test func-19.4 {
000962    catchsql {
000963      SELECT match(1,2,3);
000964    }
000965  } {1 {wrong number of arguments to function match()}}
000966  
000967  # Soundex tests.
000968  #
000969  if {![catch {db eval {SELECT soundex('hello')}}]} {
000970    set i 0
000971    foreach {name sdx} {
000972      euler        E460
000973      EULER        E460
000974      Euler        E460
000975      ellery       E460
000976      gauss        G200
000977      ghosh        G200
000978      hilbert      H416
000979      Heilbronn    H416
000980      knuth        K530
000981      kant         K530
000982      Lloyd        L300
000983      LADD         L300
000984      Lukasiewicz  L222
000985      Lissajous    L222
000986      A            A000
000987      12345        ?000
000988    } {
000989      incr i
000990      do_test func-20.$i {
000991        execsql {SELECT soundex($name)}
000992      } $sdx
000993    }
000994  }
000995  
000996  # Tests of the REPLACE function.
000997  #
000998  do_test func-21.1 {
000999    catchsql {
001000      SELECT replace(1,2);
001001    }
001002  } {1 {wrong number of arguments to function replace()}}
001003  do_test func-21.2 {
001004    catchsql {
001005      SELECT replace(1,2,3,4);
001006    }
001007  } {1 {wrong number of arguments to function replace()}}
001008  do_test func-21.3 {
001009    execsql {
001010      SELECT typeof(replace("This is the main test string", NULL, "ALT"));
001011    }
001012  } {null}
001013  do_test func-21.4 {
001014    execsql {
001015      SELECT typeof(replace(NULL, "main", "ALT"));
001016    }
001017  } {null}
001018  do_test func-21.5 {
001019    execsql {
001020      SELECT typeof(replace("This is the main test string", "main", NULL));
001021    }
001022  } {null}
001023  do_test func-21.6 {
001024    execsql {
001025      SELECT replace("This is the main test string", "main", "ALT");
001026    }
001027  } {{This is the ALT test string}}
001028  do_test func-21.7 {
001029    execsql {
001030      SELECT replace("This is the main test string", "main", "larger-main");
001031    }
001032  } {{This is the larger-main test string}}
001033  do_test func-21.8 {
001034    execsql {
001035      SELECT replace("aaaaaaa", "a", "0123456789");
001036    }
001037  } {0123456789012345678901234567890123456789012345678901234567890123456789}
001038  
001039  ifcapable tclvar {
001040    do_test func-21.9 {
001041      # Attempt to exploit a buffer-overflow that at one time existed 
001042      # in the REPLACE function. 
001043      set ::str "[string repeat A 29998]CC[string repeat A 35537]"
001044      set ::rep [string repeat B 65536]
001045      execsql {
001046        SELECT LENGTH(REPLACE($::str, 'C', $::rep));
001047      }
001048    } [expr 29998 + 2*65536 + 35537]
001049  }
001050  
001051  # Tests for the TRIM, LTRIM and RTRIM functions.
001052  #
001053  do_test func-22.1 {
001054    catchsql {SELECT trim(1,2,3)}
001055  } {1 {wrong number of arguments to function trim()}}
001056  do_test func-22.2 {
001057    catchsql {SELECT ltrim(1,2,3)}
001058  } {1 {wrong number of arguments to function ltrim()}}
001059  do_test func-22.3 {
001060    catchsql {SELECT rtrim(1,2,3)}
001061  } {1 {wrong number of arguments to function rtrim()}}
001062  do_test func-22.4 {
001063    execsql {SELECT trim('  hi  ');}
001064  } {hi}
001065  do_test func-22.5 {
001066    execsql {SELECT ltrim('  hi  ');}
001067  } {{hi  }}
001068  do_test func-22.6 {
001069    execsql {SELECT rtrim('  hi  ');}
001070  } {{  hi}}
001071  do_test func-22.7 {
001072    execsql {SELECT trim('  hi  ','xyz');}
001073  } {{  hi  }}
001074  do_test func-22.8 {
001075    execsql {SELECT ltrim('  hi  ','xyz');}
001076  } {{  hi  }}
001077  do_test func-22.9 {
001078    execsql {SELECT rtrim('  hi  ','xyz');}
001079  } {{  hi  }}
001080  do_test func-22.10 {
001081    execsql {SELECT trim('xyxzy  hi  zzzy','xyz');}
001082  } {{  hi  }}
001083  do_test func-22.11 {
001084    execsql {SELECT ltrim('xyxzy  hi  zzzy','xyz');}
001085  } {{  hi  zzzy}}
001086  do_test func-22.12 {
001087    execsql {SELECT rtrim('xyxzy  hi  zzzy','xyz');}
001088  } {{xyxzy  hi  }}
001089  do_test func-22.13 {
001090    execsql {SELECT trim('  hi  ','');}
001091  } {{  hi  }}
001092  if {[db one {PRAGMA encoding}]=="UTF-8"} {
001093    do_test func-22.14 {
001094      execsql {SELECT hex(trim(x'c280e1bfbff48fbfbf6869',x'6162e1bfbfc280'))}
001095    } {F48FBFBF6869}
001096    do_test func-22.15 {
001097      execsql {SELECT hex(trim(x'6869c280e1bfbff48fbfbf61',
001098                               x'6162e1bfbfc280f48fbfbf'))}
001099    } {6869}
001100    do_test func-22.16 {
001101      execsql {SELECT hex(trim(x'ceb1ceb2ceb3',x'ceb1'));}
001102    } {CEB2CEB3}
001103  }
001104  do_test func-22.20 {
001105    execsql {SELECT typeof(trim(NULL));}
001106  } {null}
001107  do_test func-22.21 {
001108    execsql {SELECT typeof(trim(NULL,'xyz'));}
001109  } {null}
001110  do_test func-22.22 {
001111    execsql {SELECT typeof(trim('hello',NULL));}
001112  } {null}
001113  
001114  # This is to test the deprecated sqlite3_aggregate_count() API.
001115  #
001116  ifcapable deprecated {
001117    do_test func-23.1 {
001118      sqlite3_create_aggregate db
001119      execsql {
001120        SELECT legacy_count() FROM t6;
001121      }
001122    } {3}
001123  }
001124  
001125  # The group_concat() function.
001126  #
001127  do_test func-24.1 {
001128    execsql {
001129      SELECT group_concat(t1) FROM tbl1
001130    }
001131  } {this,program,is,free,software}
001132  do_test func-24.2 {
001133    execsql {
001134      SELECT group_concat(t1,' ') FROM tbl1
001135    }
001136  } {{this program is free software}}
001137  do_test func-24.3 {
001138    execsql {
001139      SELECT group_concat(t1,' ' || rowid || ' ') FROM tbl1
001140    }
001141  } {{this 2 program 3 is 4 free 5 software}}
001142  do_test func-24.4 {
001143    execsql {
001144      SELECT group_concat(NULL,t1) FROM tbl1
001145    }
001146  } {{}}
001147  do_test func-24.5 {
001148    execsql {
001149      SELECT group_concat(t1,NULL) FROM tbl1
001150    }
001151  } {thisprogramisfreesoftware}
001152  do_test func-24.6 {
001153    execsql {
001154      SELECT 'BEGIN-'||group_concat(t1) FROM tbl1
001155    }
001156  } {BEGIN-this,program,is,free,software}
001157  
001158  # Ticket #3179:  Make sure aggregate functions can take many arguments.
001159  # None of the built-in aggregates do this, so use the md5sum() from the
001160  # test extensions.
001161  #
001162  unset -nocomplain midargs
001163  set midargs {}
001164  unset -nocomplain midres
001165  set midres {}
001166  unset -nocomplain result
001167  for {set i 1} {$i<[sqlite3_limit db SQLITE_LIMIT_FUNCTION_ARG -1]} {incr i} {
001168    append midargs ,'/$i'
001169    append midres /$i
001170    set result [md5 \
001171       "this${midres}program${midres}is${midres}free${midres}software${midres}"]
001172    set sql "SELECT md5sum(t1$midargs) FROM tbl1"
001173    do_test func-24.7.$i {
001174       db eval $::sql
001175    } $result
001176  }
001177  
001178  # Ticket #3806.  If the initial string in a group_concat is an empty
001179  # string, the separator that follows should still be present.
001180  #
001181  do_test func-24.8 {
001182    execsql {
001183      SELECT group_concat(CASE t1 WHEN 'this' THEN '' ELSE t1 END) FROM tbl1
001184    }
001185  } {,program,is,free,software}
001186  do_test func-24.9 {
001187    execsql {
001188      SELECT group_concat(CASE WHEN t1!='software' THEN '' ELSE t1 END) FROM tbl1
001189    }
001190  } {,,,,software}
001191  
001192  # Ticket #3923.  Initial empty strings have a separator.  But initial
001193  # NULLs do not.
001194  #
001195  do_test func-24.10 {
001196    execsql {
001197      SELECT group_concat(CASE t1 WHEN 'this' THEN null ELSE t1 END) FROM tbl1
001198    }
001199  } {program,is,free,software}
001200  do_test func-24.11 {
001201    execsql {
001202     SELECT group_concat(CASE WHEN t1!='software' THEN null ELSE t1 END) FROM tbl1
001203    }
001204  } {software}
001205  do_test func-24.12 {
001206    execsql {
001207      SELECT group_concat(CASE t1 WHEN 'this' THEN ''
001208                            WHEN 'program' THEN null ELSE t1 END) FROM tbl1
001209    }
001210  } {,is,free,software}
001211  # Tests to verify ticket http://www.sqlite.org/src/tktview/55746f9e65f8587c0
001212  do_test func-24.13 {
001213    execsql {
001214      SELECT typeof(group_concat(x)) FROM (SELECT '' AS x);
001215    }
001216  } {text}
001217  do_test func-24.14 {
001218    execsql {
001219      SELECT typeof(group_concat(x,''))
001220        FROM (SELECT '' AS x UNION ALL SELECT '');
001221    }
001222  } {text}
001223  
001224  
001225  # Use the test_isolation function to make sure that type conversions
001226  # on function arguments do not effect subsequent arguments.
001227  #
001228  do_test func-25.1 {
001229    execsql {SELECT test_isolation(t1,t1) FROM tbl1}
001230  } {this program is free software}
001231  
001232  # Try to misuse the sqlite3_create_function() interface.  Verify that
001233  # errors are returned.
001234  #
001235  do_test func-26.1 {
001236    abuse_create_function db
001237  } {}
001238  
001239  # The previous test (func-26.1) registered a function with a very long
001240  # function name that takes many arguments and always returns NULL.  Verify
001241  # that this function works correctly.
001242  #
001243  do_test func-26.2 {
001244    set a {}
001245    for {set i 1} {$i<=$::SQLITE_MAX_FUNCTION_ARG} {incr i} {
001246      lappend a $i
001247    }
001248    db eval "
001249       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 ,]);
001250    "
001251  } {{}}
001252  do_test func-26.3 {
001253    set a {}
001254    for {set i 1} {$i<=$::SQLITE_MAX_FUNCTION_ARG+1} {incr i} {
001255      lappend a $i
001256    }
001257    catchsql "
001258       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 ,]);
001259    "
001260  } {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}}
001261  do_test func-26.4 {
001262    set a {}
001263    for {set i 1} {$i<=$::SQLITE_MAX_FUNCTION_ARG-1} {incr i} {
001264      lappend a $i
001265    }
001266    catchsql "
001267       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 ,]);
001268    "
001269  } {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()}}
001270  do_test func-26.5 {
001271    catchsql "
001272       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);
001273    "
001274  } {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}}
001275  do_test func-26.6 {
001276    catchsql "
001277       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);
001278    "
001279  } {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}}
001280  
001281  do_test func-27.1 {
001282    catchsql {SELECT coalesce()}
001283  } {1 {wrong number of arguments to function coalesce()}}
001284  do_test func-27.2 {
001285    catchsql {SELECT coalesce(1)}
001286  } {1 {wrong number of arguments to function coalesce()}}
001287  do_test func-27.3 {
001288    catchsql {SELECT coalesce(1,2)}
001289  } {0 1}
001290  
001291  # Ticket 2d401a94287b5
001292  # Unknown function in a DEFAULT expression causes a segfault.
001293  #
001294  do_test func-28.1 {
001295    db eval {
001296      CREATE TABLE t28(x, y DEFAULT(nosuchfunc(1)));
001297    }
001298    catchsql {
001299      INSERT INTO t28(x) VALUES(1);
001300    }
001301  } {1 {unknown function: nosuchfunc()}}
001302  
001303  # Verify that the length() and typeof() functions do not actually load
001304  # the content of their argument.
001305  #
001306  do_test func-29.1 {
001307    db eval {
001308      CREATE TABLE t29(id INTEGER PRIMARY KEY, x, y);
001309      INSERT INTO t29 VALUES(1, 2, 3), (2, NULL, 4), (3, 4.5, 5);
001310      INSERT INTO t29 VALUES(4, randomblob(1000000), 6);
001311      INSERT INTO t29 VALUES(5, "hello", 7);
001312    }
001313    db close
001314    sqlite3 db test.db
001315    sqlite3_db_status db CACHE_MISS 1
001316    db eval {SELECT typeof(x), length(x), typeof(y) FROM t29 ORDER BY id}
001317  } {integer 1 integer null {} integer real 3 integer blob 1000000 integer text 5 integer}
001318  do_test func-29.2 {
001319    set x [lindex [sqlite3_db_status db CACHE_MISS 1] 1]
001320    if {$x<5} {set x 1}
001321    set x
001322  } {1}
001323  do_test func-29.3 {
001324    db close
001325    sqlite3 db test.db
001326    sqlite3_db_status db CACHE_MISS 1
001327    db eval {SELECT typeof(+x) FROM t29 ORDER BY id}
001328  } {integer null real blob text}
001329  if {[permutation] != "mmap"} {
001330    ifcapable !direct_read {
001331      do_test func-29.4 {
001332        set x [lindex [sqlite3_db_status db CACHE_MISS 1] 1]
001333        if {$x>100} {set x many}
001334        set x
001335      } {many}
001336    }
001337  }
001338  do_test func-29.5 {
001339    db close
001340    sqlite3 db test.db
001341    sqlite3_db_status db CACHE_MISS 1
001342    db eval {SELECT sum(length(x)) FROM t29}
001343  } {1000009}
001344  do_test func-29.6 {
001345    set x [lindex [sqlite3_db_status db CACHE_MISS 1] 1]
001346    if {$x<5} {set x 1}
001347    set x
001348  } {1}
001349  
001350  # The OP_Column opcode has an optimization that avoids loading content
001351  # for fields with content-length=0 when the content offset is on an overflow
001352  # page.  Make sure the optimization works.
001353  #
001354  do_execsql_test func-29.10 {
001355    CREATE TABLE t29b(a,b,c,d,e,f,g,h,i);
001356    INSERT INTO t29b 
001357     VALUES(1, hex(randomblob(2000)), null, 0, 1, '', zeroblob(0),'x',x'01');
001358    SELECT typeof(c), typeof(d), typeof(e), typeof(f),
001359           typeof(g), typeof(h), typeof(i) FROM t29b;
001360  } {null integer integer text blob text blob}
001361  do_execsql_test func-29.11 {
001362    SELECT length(f), length(g), length(h), length(i) FROM t29b;
001363  } {0 0 1 1}
001364  do_execsql_test func-29.12 {
001365    SELECT quote(f), quote(g), quote(h), quote(i) FROM t29b;
001366  } {'' X'' 'x' X'01'}
001367  
001368  # EVIDENCE-OF: R-29701-50711 The unicode(X) function returns the numeric
001369  # unicode code point corresponding to the first character of the string
001370  # X.
001371  #
001372  # EVIDENCE-OF: R-55469-62130 The char(X1,X2,...,XN) function returns a
001373  # string composed of characters having the unicode code point values of
001374  # integers X1 through XN, respectively.
001375  #
001376  do_execsql_test func-30.1 {SELECT unicode('$');} 36
001377  do_execsql_test func-30.2 [subst {SELECT unicode('\u00A2');}] 162
001378  do_execsql_test func-30.3 [subst {SELECT unicode('\u20AC');}] 8364
001379  do_execsql_test func-30.4 {SELECT char(36,162,8364);} [subst {$\u00A2\u20AC}]
001380  
001381  for {set i 1} {$i<0xd800} {incr i 13} {
001382    do_execsql_test func-30.5.$i {SELECT unicode(char($i))} $i
001383  }
001384  for {set i 57344} {$i<=0xfffd} {incr i 17} {
001385    if {$i==0xfeff} continue
001386    do_execsql_test func-30.5.$i {SELECT unicode(char($i))} $i
001387  }
001388  for {set i 65536} {$i<=0x10ffff} {incr i 139} {
001389    do_execsql_test func-30.5.$i {SELECT unicode(char($i))} $i
001390  }
001391  
001392  # Test char().
001393  #
001394  do_execsql_test func-31.1 { 
001395    SELECT char(), length(char()), typeof(char()) 
001396  } {{} 0 text}
001397  
001398  # sqlite3_value_frombind()
001399  #
001400  do_execsql_test func-32.100 {
001401    SELECT test_frombind(1,2,3,4);
001402  } {0}
001403  do_execsql_test func-32.110 {
001404    SELECT test_frombind(1,2,?,4);
001405  } {4}
001406  do_execsql_test func-32.120 {
001407    SELECT test_frombind(1,(?),4,?+7);
001408  } {2}
001409  do_execsql_test func-32.130 {
001410    DROP TABLE IF EXISTS t1;
001411    CREATE TABLE t1(a,b,c,e,f);
001412    INSERT INTO t1 VALUES(1,2.5,'xyz',x'e0c1b2a3',null);
001413    SELECT test_frombind(a,b,c,e,f,$xyz) FROM t1;
001414  } {32}
001415  do_execsql_test func-32.140 {
001416    SELECT test_frombind(a,b,c,e,f,$xyz+f) FROM t1;
001417  } {0}
001418  do_execsql_test func-32.150 {
001419    SELECT test_frombind(x.a,y.b,x.c,:123,y.e,x.f,$xyz+y.f) FROM t1 x, t1 y;
001420  } {8}
001421  
001422  # 2019-08-15
001423  # Direct-only functions.
001424  #
001425  proc testdirectonly {x} {return [expr {$x*2}]}
001426  do_test func-33.1 {
001427    db func testdirectonly -directonly testdirectonly
001428    db eval {SELECT testdirectonly(15)}
001429  } {30}
001430  do_catchsql_test func-33.2 {
001431    CREATE VIEW v33(y) AS SELECT testdirectonly(15);
001432    SELECT * FROM v33;
001433  } {1 {testdirectonly() prohibited in triggers and views}}
001434  do_execsql_test func-33.3 {
001435    SELECT * FROM (SELECT testdirectonly(15)) AS v33;
001436  } {30}
001437  do_execsql_test func-33.4 {
001438    WITH c(x) AS (SELECT testdirectonly(15))
001439    SELECT * FROM c;
001440  } {30}
001441  do_catchsql_test func-33.5 {
001442    WITH c(x) AS (SELECT * FROM v33)
001443    SELECT * FROM c;
001444  } {1 {testdirectonly() prohibited in triggers and views}}
001445  do_execsql_test func-33.10 {
001446    CREATE TABLE t33a(a,b);
001447    CREATE TABLE t33b(x,y);
001448    CREATE TRIGGER r1 AFTER INSERT ON t33a BEGIN
001449      INSERT INTO t33b(x,y) VALUES(testdirectonly(new.a),new.b);
001450    END;
001451  } {}
001452  do_catchsql_test func-33.11 {
001453    INSERT INTO t33a VALUES(1,2);
001454  } {1 {testdirectonly() prohibited in triggers and views}}
001455  do_execsql_test func-33.20 {
001456    ALTER TABLE t33a RENAME COLUMN a TO aaa;
001457    SELECT sql FROM sqlite_master WHERE name='r1';
001458  } {{CREATE TRIGGER r1 AFTER INSERT ON t33a BEGIN
001459      INSERT INTO t33b(x,y) VALUES(testdirectonly(new.aaa),new.b);
001460    END}}
001461  
001462  
001463  finish_test