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 AB3DF2FCAD for ; Wed, 14 Nov 2018 11:20:36 -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 9rQBgbdYw5DS for ; Wed, 14 Nov 2018 11:20:36 -0500 (EST) Received: from smtp3.mail.ru (smtp3.mail.ru [94.100.179.58]) (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 681602A570 for ; Wed, 14 Nov 2018 11:20:36 -0500 (EST) Content-Type: text/plain; charset=utf-8 Mime-Version: 1.0 (Mac OS X Mail 12.0 \(3445.100.39\)) Subject: [tarantool-patches] Re: [PATCH v2 4/4] sql: rename changes() to row_count() From: "n.pettik" In-Reply-To: Date: Wed, 14 Nov 2018 19:20:34 +0300 Content-Transfer-Encoding: quoted-printable Message-Id: <256054A5-FFFE-46BA-A862-50A4C9A4AA18@tarantool.org> References: 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: tarantool-patches@freelists.org Cc: Vladislav Shpilevoy >> diff --git a/test/sql/row-count.result b/test/sql/row-count.result >> new file mode 100644 >> index 000000000..7577d2795 >> --- /dev/null >> +++ b/test/sql/row-count.result >> @@ -0,0 +1,157 @@ >> +test_run =3D require('test_run').new() >> +--- >> +... >> +engine =3D test_run:get_cfg('engine') >> +--- >> +... >> +box.sql.execute('pragma sql_default_engine=3D\''..engine..'\'') >> +--- >> +... >> +-- Test cases concerning row count calculations. >> +-- >> +box.sql.execute("CREATE TABLE t1 (s1 CHAR(10) PRIMARY KEY);") >> +--- >> +... >> +box.sql.execute("SELECT ROW_COUNT();") >> +--- >> +- - [1] >> +... >> +box.sql.execute("SELECT ROW_COUNT();") >> +--- >> +- - [0] >=20 > 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. =46rom 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. >=20 > '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=E2=80=99ve 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/ =E2=80=A2 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. =E2=80=9C Only =E2=80=9Cpreceding statement=E2=80=9D 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=3Dsql-server-2017 =E2=80=9CEvery insert/update/select/set/delete statement resets the = @@rowcount to the rows affected by the executed statement.=E2=80=9D 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. >> 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 >=20 > 2. "No newline at end of file.=E2=80=9D Fixed.