From mboxrd@z Thu Jan 1 00:00:00 1970 Return-Path: Received: from [87.239.111.99] (localhost [127.0.0.1]) by dev.tarantool.org (Postfix) with ESMTP id 03A9A6F873; Tue, 25 Jan 2022 01:08:59 +0300 (MSK) DKIM-Filter: OpenDKIM Filter v2.11.0 dev.tarantool.org 03A9A6F873 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/simple; d=tarantool.org; s=dev; t=1643062139; bh=rKpQmdGoBDdMai4uF0z4rygDJb0Y6vXBLUdetuCYQww=; h=Date:To:Cc:References:In-Reply-To:Subject:List-Id: List-Unsubscribe:List-Archive:List-Post:List-Help:List-Subscribe: From:Reply-To:From; b=qu0arfc/CP2wyzjhhNM5C5zk0AUprPGIta/P2lSl+isGvLWSQSEerl6JdfxJy0ddh 0n9wgkUGBkbYJxUqEcUxyFlSc5opKW3d2hqEmK3QIilXPXfwZF1LXY6qOJ6XBWcfhs AaagubUddqte70xqDDOiL2Yq2LukshDvSUpqtSgE= Received: from smtpng3.i.mail.ru (smtpng3.i.mail.ru [94.100.177.149]) (using TLSv1.2 with cipher ECDHE-RSA-AES256-GCM-SHA384 (256/256 bits)) (No client certificate requested) by dev.tarantool.org (Postfix) with ESMTPS id 7573A6F873 for ; Tue, 25 Jan 2022 01:08:51 +0300 (MSK) DKIM-Filter: OpenDKIM Filter v2.11.0 dev.tarantool.org 7573A6F873 Received: by smtpng3.m.smailru.net with esmtpa (envelope-from ) id 1nC7W6-00079g-LI; Tue, 25 Jan 2022 01:08:51 +0300 Message-ID: <035cf09f-f958-a39c-89db-8e01dce22814@tarantool.org> Date: Mon, 24 Jan 2022 23:08:49 +0100 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.15; rv:91.0) Gecko/20100101 Thunderbird/91.5.0 Content-Language: en-US To: Mergen Imeev Cc: tarantool-patches@dev.tarantool.org References: <04154369ec1ff8a1eaf7c9ea1ed37e1fcd1a7120.1642167504.git.imeevma@gmail.com> <0875aeb4-255d-ce3a-7244-69193cf2f334@tarantool.org> <20220123141755.GA103585@tarantool.org> In-Reply-To: <20220123141755.GA103585@tarantool.org> Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 7bit X-4EC0790: 10 X-7564579A: EEAE043A70213CC8 X-77F55803: 4F1203BC0FB41BD9AA78FDF62ECAE61F611616CC43A7A66C4D348DFB252A8B49182A05F538085040658D32FA888539E820DC5096A21C6604CE1FE65415ACBB99792F56CD2BC76AEF X-7FA49CB5: FF5795518A3D127A4AD6D5ED66289B5278DA827A17800CE746D93DAA4671895CEA1F7E6F0F101C67BD4B6F7A4D31EC0BCC500DACC3FED6E28638F802B75D45FF8AA50765F79006378A9F193E39E334918638F802B75D45FF36EB9D2243A4F8B5A6FCA7DBDB1FC311F39EFFDF887939037866D6147AF826D84D23BD7C98B34C50FEA564492EA9C7FE117882F4460429724CE54428C33FAD305F5C1EE8F4F765FCECADA55FE5B58BB7A471835C12D1D9774AD6D5ED66289B52BA9C0B312567BB23117882F44604297287769387670735201E561CDFBCA1751F28451B159A507268D2E47CDBA5A96583BA9C0B312567BB231DD303D21008E29813377AFFFEAFD269A417C69337E82CC2E827F84554CEF50127C277FBC8AE2E8BA83251EDC214901ED5E8D9A59859A8B6300D3B61E77C8D3B089D37D7C0E48F6C5571747095F342E88FB05168BE4CE3AF X-C1DE0DAB: 0D63561A33F958A5B2C7AB01D63DE8FC01251CB8EE1F3CB543E1E8EE0676B2EFD59269BC5F550898D99A6476B3ADF6B47008B74DF8BB9EF7333BD3B22AA88B938A852937E12ACA75B7BFB303F1C7DB4D8E8E86DC7131B365E7726E8460B7C23C X-C8649E89: 4E36BF7865823D7055A7F0CF078B5EC49A30900B95165D34315359784EE45103AD8CF407B15CCE2F97A32F94A56748356A1893D3C6C261DD362023178D8B93ED1D7E09C32AA3244C65D1BD391723B282DEE36C2818F7E1CE81560E2432555DBB729B2BEF169E0186 X-D57D3AED: 3ZO7eAau8CL7WIMRKs4sN3D3tLDjz0dLbV79QFUyzQ2Ujvy7cMT6pYYqY16iZVKkSc3dCLJ7zSJH7+u4VD18S7Vl4ZUrpaVfd2+vE6kuoey4m4VkSEu530nj6fImhcD4MUrOEAnl0W826KZ9Q+tr5ycPtXkTV4k65bRjmOUUP8cvGozZ33TWg5HZplvhhXbhDGzqmQDTd6OAevLeAnq3Ra9uf7zvY2zzsIhlcp/Y7m53TZgf2aB4JOg4gkr2biojPq2vZmbSbSS8NuS3P9m8EQ== X-Mailru-Sender: 689FA8AB762F739339CABD9B3CA9A7D69126EC55A5D1C16387D308AD5247391C3841015FED1DE5223CC9A89AB576DD93FB559BB5D741EB963CF37A108A312F5C27E8A8C3839CE0E25FEEDEB644C299C0ED14614B50AE0675 X-Mras: Ok Subject: Re: [Tarantool-patches] [PATCH v1 1/1] sql: introduce user-defined aggregate functions X-BeenThere: tarantool-patches@dev.tarantool.org X-Mailman-Version: 2.1.34 Precedence: list List-Id: Tarantool development patches List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , From: Vladislav Shpilevoy via Tarantool-patches Reply-To: Vladislav Shpilevoy Errors-To: tarantool-patches-bounces@dev.tarantool.org Sender: "Tarantool-patches" On 23.01.2022 15:17, Mergen Imeev wrote: > Hi! Thank you for the review! Diff, new patch and a patch that adds a new > utility function below. > > On Tue, Jan 18, 2022 at 01:10:04AM +0100, Vladislav Shpilevoy wrote: >> Hi! Thanks for the patch! >> >> What happened to the idea we had about SQL iterators? Then people would >> be able to iterate with yields and to make any kind of aggregation without >> having to create functions for that. >> > I think that this idea is actually very similar to the definition of CURSOR from > the standard. Here's what the standard says about CURSORs: > "A cursor is a mechanism by which the rows of a table may be acted on (e.g., > returned to a host programming language) one at a time." > > I will try to suggest this idea. > > However, current issue have quite strict deadline, which is actually quite near. > I believe that CURSOR injection is not a problem that can be solved in a few > weeks. > > Also, about my current implementation. I don't like it, because there are too > many changes made to BOX that are not related to BOX itself. I think there is a > much simpler implementation. I think you noticed that the current implementation > says that non-constant functions and C functions should define two > implementations named "" and "_finalize". Do you think this rule can > be made more general? I suggest creating two tuples in _func instead of one when > we create an aggregate function using box.schema.func.create(). In this case, > there will be almost no changes in alter.cc and BOX as a whole, and the logic > will become much simpler. Also, I think we won't be adding new opcodes to the > VDBE since we can just reuse OP_FunctionByName. That problem with this approach > is that "_finalize" will be occupied. Another issue is that people will need to delete this func explicitly when they drop the main one. That might be a blocker for this rework idea. Plausible alternatives are: 1. Postpone aggregation until cursors are implemented. Move the deadline. Then the aggregation might be not needed at all. 2. Allow to specify step and finalization functions in SQL query. For instance, -- SQL -- SELECT AGGREGATE({STEP = AvgFunc, END = AvgEnd}, [a, b]) FROM t; -- Lua -- function AvgFunc(ctx, a, b) if ctx == nil then return {s = a + b, n = 1} else ctx.s = ctx.s + a + b ctx.n = ctx.n + 1 end end function AvgEnd(ctx) return ctx.s / ctx.n end box.schema.func.create('AvgFunc', ...) box.schema.func.create('AvgEnd', ...) If you omit FIN, it is simply not called. The same with STEP. And later could introduce START. 3. Force people to create '..._finalize' function. Until it is created by their own hands, the aggregation will raise an error. > Also, this could potentially lead to > a different combination of STEP and FINALIZE functions, for example, STEP could > be constant but FINALIZE could be non-persistent. However, to create such > combinations, the user must insert tuples directly into _func. I am not sure, > if this a feature or a bug. What do you think? As for how the functions are implemented - I wouldn't care much. They must exist and be callable, that could be the only requirement. I didn't look at the new version of the patch yet. Only answering the main question now.