From e1e67abc5cf67f931aab1e471eda23d73f51d456 Mon Sep 17 00:00:00 2001 From: drh <> Date: Tue, 29 Aug 2023 15:24:41 +0000 Subject: [PATCH] Add support for the CONCAT() and CONCAT_WS() SQL functions, modeled after the PostgreSQL behavior. FossilOrigin-Name: 0b434ca7aa19eff4ad134a8c6f88f6a7ccab88864faa55e93579a2462d8ac3bc --- manifest | 15 +++++----- manifest.uuid | 2 +- src/func.c | 80 +++++++++++++++++++++++++++++++++++++++++++++++++ test/func9.test | 40 +++++++++++++++++++++++++ 4 files changed, 129 insertions(+), 8 deletions(-) create mode 100644 test/func9.test diff --git a/manifest b/manifest index 34defa2299..073b8e1220 100644 --- a/manifest +++ b/manifest @@ -1,5 +1,5 @@ -C Change\sa\svariable\sfrom\s"int"\sto\s"i64"\sto\smake\sit\seasier\sto\sprove\sthat\sit\scannot\soverflow. -D 2023-08-29T10:50:11.066 +C Add\ssupport\sfor\sthe\sCONCAT()\sand\sCONCAT_WS()\sSQL\sfunctions,\smodeled\safter\nthe\sPostgreSQL\sbehavior. +D 2023-08-29T15:24:41.645 F .fossil-settings/empty-dirs dbb81e8fc0401ac46a1491ab34a7f2c7c0452f2f06b54ebb845d024ca8283ef1 F .fossil-settings/ignore-glob 35175cdfcf539b2318cb04a9901442804be81cd677d8b889fcc9149c21f239ea F LICENSE.md df5091916dbb40e6e9686186587125e1b2ff51f022cc334e886c19a0e9982724 @@ -653,7 +653,7 @@ F src/delete.c cb766727c78e715f9fb7ec8a7d03658ed2a3016343ca687acfcec9083cdca500 F src/expr.c 1affe0cc049683ef0ef3545d9b6901508556b0ef7e2892a344c3df6d7288d79d F src/fault.c 460f3e55994363812d9d60844b2a6de88826e007 F src/fkey.c a7fcbf7e66d14dbb73cf49f31489ebf66d0e6006c62b95246924a3bae9f37b36 -F src/func.c f480d46974ecc84fefdd429377158981b974e0e33d656f1b0e919ba7c4bdd390 +F src/func.c c96c7f55b97493ce278937e6ab6578a866b7e5d6dd486d58b220e9d17e88a750 F src/global.c 29f56a330ed9d1b5cd9b79ac0ca36f97ac3afc730ff8bfa987b0db9e559d684d F src/hash.c 9ee4269fb1d6632a6fecfb9479c93a1f29271bddbbaf215dd60420bcb80c7220 F src/hash.h 3340ab6e1d13e725571d7cee6d3e3135f0779a7d8e76a9ce0a85971fa3953c51 @@ -1180,6 +1180,7 @@ F test/func5.test 863e6d1bd0013d09c17236f8a13ea34008dd857d87d85a13a673960e4c25d8 F test/func6.test 9cc9b1f43b435af34fe1416eb1e318c8920448ea7a6962f2121972f5215cb9b0 F test/func7.test adbfc910385a6ffd15dc47be3c619ef070c542fcb7488964badb17b2d9a4d080 F test/func8.test c4e2ecacf9f16e47a245e7a25fbabcc7e78f9c7c41a80f158527cdfdc6dd299d +F test/func9.test b32d313f679aa9698d52f39519d301c3941823cb72b4e23406c210eadd82c824 F test/fuzz-oss1.test 514dcabb24687818ea949fa6760229eaacad74ca70157743ef36d35bbe01ffb0 F test/fuzz.test 4608c1310cff4c3014a84bcced6278139743e080046e5f6784b0de7b069371d8 F test/fuzz2.test 76dc35b32b6d6f965259508508abce75a6c4d7e1 @@ -2108,8 +2109,8 @@ F vsixtest/vsixtest.tcl 6a9a6ab600c25a91a7acc6293828957a386a8a93 F vsixtest/vsixtest.vcxproj.data 2ed517e100c66dc455b492e1a33350c1b20fbcdc F vsixtest/vsixtest.vcxproj.filters 37e51ffedcdb064aad6ff33b6148725226cd608e F vsixtest/vsixtest_TemporaryKey.pfx e5b1b036facdb453873e7084e1cae9102ccc67a0 -P 6c83e31fa96f65b61377c0c801cc32b3c8ca27a0c8442f860364bec258c003cb -R 703966de64a45733a2ec15e9ba123f27 -U dan -Z cdc3674e70b09e1a4bfbc46bbf505365 +P 00a8b3a263f3537588063ce42fad6e21fa343dad850b086d0929ed1617eb44fc +R 73410e2beee6b3d9558e014cdb959353 +U drh +Z 75edcaca9693bb69bba026024423283c # Remove this line to create a well-formed Fossil manifest. diff --git a/manifest.uuid b/manifest.uuid index 4af2563b02..6cabfb5376 100644 --- a/manifest.uuid +++ b/manifest.uuid @@ -1 +1 @@ -00a8b3a263f3537588063ce42fad6e21fa343dad850b086d0929ed1617eb44fc \ No newline at end of file +0b434ca7aa19eff4ad134a8c6f88f6a7ccab88864faa55e93579a2462d8ac3bc \ No newline at end of file diff --git a/src/func.c b/src/func.c index 70fda85927..3332523486 100644 --- a/src/func.c +++ b/src/func.c @@ -1550,6 +1550,81 @@ static void trimFunc( sqlite3_result_text(context, (char*)zIn, nIn, SQLITE_TRANSIENT); } +/* The core implementation of the CONCAT(...) and CONCAT_WS(SEP,...) +** functions. +** +** Return a string value that is the concatenation of all non-null +** entries in argv[]. Use zSep as the separator. +*/ +static void concatFuncCore( + sqlite3_context *context, + int argc, + sqlite3_value **argv, + int nSep, + const char *zSep +){ + i64 j, k, n = 0; + int i; + char *z; + for(i=0; i0 ){ + const char *v = (const char*)sqlite3_value_text(argv[i]); + if( ALWAYS(v!=0) ){ + if( j>0 && nSep>0 ){ + memcpy(&z[j], zSep, nSep); + j += nSep; + } + memcpy(&z[j], v, k); + j += k; + } + } + } + z[j] = 0; + assert( j<=n ); + sqlite3_result_text64(context, z, n, sqlite3_free, SQLITE_UTF8); +} + +/* +** The CONCAT(...) function. Generate a string result that is the +** concatentation of all non-null arguments. +*/ +static void concatFunc( + sqlite3_context *context, + int argc, + sqlite3_value **argv +){ + concatFuncCore(context, argc, argv, 0, ""); +} + +/* +** The CONCAT_WS(separator, ...) function. +** +** Generate a string that is the concatenation of 2nd through the Nth +** argument. Use the first argument (which must be non-NULL) as the +** separator. +*/ +static void concatwsFunc( + sqlite3_context *context, + int argc, + sqlite3_value **argv +){ + int nSep = sqlite3_value_bytes(argv[0]); + const char *zSep = (const char*)sqlite3_value_text(argv[0]); + if( zSep==0 ) return; + concatFuncCore(context, argc-1, argv+1, nSep, zSep); +} + #ifdef SQLITE_ENABLE_UNKNOWN_SQL_FUNCTION /* @@ -2559,6 +2634,11 @@ void sqlite3RegisterBuiltinFunctions(void){ FUNCTION(hex, 1, 0, 0, hexFunc ), FUNCTION(unhex, 1, 0, 0, unhexFunc ), FUNCTION(unhex, 2, 0, 0, unhexFunc ), + FUNCTION(concat, -1, 0, 0, concatFunc ), + FUNCTION(concat, 0, 0, 0, 0 ), + FUNCTION(concat_ws, -1, 0, 0, concatwsFunc ), + FUNCTION(concat_ws, 0, 0, 0, 0 ), + FUNCTION(concat_ws, 1, 0, 0, 0 ), INLINE_FUNC(ifnull, 2, INLINEFUNC_coalesce, 0 ), VFUNCTION(random, 0, 0, 0, randomFunc ), VFUNCTION(randomblob, 1, 0, 0, randomBlob ), diff --git a/test/func9.test b/test/func9.test new file mode 100644 index 0000000000..6cf9fc31ec --- /dev/null +++ b/test/func9.test @@ -0,0 +1,40 @@ +# 2023-08-29 +# +# The author disclaims copyright to this source code. In place of +# a legal notice, here is a blessing: +# +# May you do good and not evil. +# May you find forgiveness for yourself and forgive others. +# May you share freely, never taking more than you give. +# +#************************************************************************* +# +# Test cases for SQL newer functions +# +set testdir [file dirname $argv0] +source $testdir/tester.tcl + +do_execsql_test func9-100 { + SELECT concat('abc',123,null,'xyz'); +} {abc123xyz} +do_execsql_test func9-110 { + SELECT typeof(concat(null)); +} {text} +do_catchsql_test func9-120 { + SELECT concat(); +} {1 {wrong number of arguments to function concat()}} +do_execsql_test func9-130 { + SELECT concat_ws(',',1,2,3,4,5,6,7,8,NULL,9,10,11,12); +} {1,2,3,4,5,6,7,8,9,10,11,12} +do_execsql_test func9-140 { + SELECT concat_ws(NULL,1,2,3,4,5,6,7,8,NULL,9,10,11,12); +} {{}} +do_catchsql_test func9-150 { + SELECT concat_ws(); +} {1 {wrong number of arguments to function concat_ws()}} +do_catchsql_test func9-160 { + SELECT concat_ws(','); +} {1 {wrong number of arguments to function concat_ws()}} + + +finish_test -- 2.47.2