From mboxrd@z Thu Jan 1 00:00:00 1970 Return-Path: Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id C5E962FBDB for ; Wed, 14 Nov 2018 11:43:58 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing.freelists.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id E0YDBbvhVyD1 for ; Wed, 14 Nov 2018 11:43:58 -0500 (EST) Received: from smtpng3.m.smailru.net (smtpng3.m.smailru.net [94.100.177.149]) (using TLSv1.2 with cipher ECDHE-RSA-AES256-GCM-SHA384 (256/256 bits)) (No client certificate requested) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTPS id 81DDF2FBDA for ; Wed, 14 Nov 2018 11:43:58 -0500 (EST) Subject: [tarantool-patches] Re: [PATCH v2 4/4] sql: rename changes() to row_count() References: <256054A5-FFFE-46BA-A862-50A4C9A4AA18@tarantool.org> From: Vladislav Shpilevoy Message-ID: <957abfbf-8f48-fe74-4f68-cf645e2422f9@tarantool.org> Date: Wed, 14 Nov 2018 19:43:54 +0300 MIME-Version: 1.0 In-Reply-To: <256054A5-FFFE-46BA-A862-50A4C9A4AA18@tarantool.org> Content-Type: text/plain; charset="utf-8"; format="flowed" Content-Language: en-US Content-Transfer-Encoding: 8bit Sender: tarantool-patches-bounce@freelists.org Errors-to: tarantool-patches-bounce@freelists.org Reply-To: tarantool-patches@freelists.org List-help: List-unsubscribe: List-software: Ecartis version 1.0.0 List-Id: tarantool-patches List-subscribe: List-owner: List-post: List-archive: To: "n.pettik" , tarantool-patches@freelists.org >> 1. As I understand, ROW_COUNT() should return > 0 only in a >> not empty transaction. Here you got rowcount > 0 after DDL >> transaction is committed. Also, twice in a row called >> row_count() returning different values looks weird. > > From first sight - yes, but it makes sense: > first call returns number of row count of preceding statement, > which is 1 since table was created. > second call again return number of row count of preceding statement, > but now it is 0 since last statement was SELECT and it always > return 0. > >> >> 'rowcount' should be returned as a metavalue from a DDL/DML >> when box.sql.execute is removed. > > I added this test on purpose so you can notice this behaviour. > I’ve checked MariaDB and MySQL as Peter suggested and > it works in this way (but row count is -1, not 0): > > https://mariadb.com/kb/en/library/information-functions-row_count/ > > • For statements which return a result set (such as SELECT, SHOW, DESC or HELP), > returns -1, even when the result set is empty. This is also true for administrativ > statements, such as OPTIMIZE. > > And ROW_COUNT() returns the number of updated row not only > within active transaction. Again from docs: > > "ROW_COUNT() returns the number of rows updated, inserted or deleted by the preceding statement. “ > > Only “preceding statement” is mentioned. > > The same behaviour we can observe in MS Server: > > https://docs.microsoft.com/ru-ru/sql/t-sql/functions/rowcount-transact-sql?view=sql-server-2017 > > “Every insert/update/select/set/delete statement resets the @@rowcount > to the rows affected by the executed statement.” > > And the same it works in DB2: > > https://www.ibm.com/support/knowledgecenter/en/SSEPEK_11.0.0/odbc/src/tpc/db2z_fnrowcount.html > > If SQLRowCount() is executed after the SQLExecDirect() or SQLExecute() > of an SQL statement other than INSERT, UPDATE, DELETE, or MERGE, > it results in return code 0 and pcrow is set to -1. > > So I guess current implementation now is quite close to others. Thanks for the investigation! Then ok, decent. The patchset LGTM. > >>> diff --git a/test/sql/row-count.test.lua b/test/sql/row-count.test.lua >>> new file mode 100644 >>> index 000000000..38d3520c2 >>> --- /dev/null >>> +++ b/test/sql/row-count.test.lua >>> +-- Clean-up. >>> +-- >>> +box.sql.execute("DROP TABLE t2;") >>> +box.sql.execute("DROP TABLE t3;") >>> +box.sql.execute("DROP TABLE t1;") >>> \ No newline at end of file >> >> 2. "No newline at end of file.” > > Fixed. >