From: "n.pettik" <korablev@tarantool.org> To: tarantool-patches@freelists.org Cc: Vladislav Shpilevoy <v.shpilevoy@tarantool.org> Subject: [tarantool-patches] Re: [PATCH v2 4/4] sql: rename changes() to row_count() Date: Wed, 14 Nov 2018 19:20:34 +0300 [thread overview] Message-ID: <256054A5-FFFE-46BA-A862-50A4C9A4AA18@tarantool.org> (raw) In-Reply-To: <acc21555-e76b-d3ca-4549-4eb655b68e82@tarantool.org> >> 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 = require('test_run').new() >> +--- >> +... >> +engine = test_run:get_cfg('engine') >> +--- >> +... >> +box.sql.execute('pragma sql_default_engine=\''..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] > > 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. >> 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.
next prev parent reply other threads:[~2018-11-14 16:20 UTC|newest] Thread overview: 13+ messages / expand[flat|nested] mbox.gz Atom feed top 2018-11-13 16:11 [tarantool-patches] [PATCH v2 0/4] Introduce row_count() function Nikita Pettik 2018-11-13 16:11 ` [tarantool-patches] [PATCH v2 1/4] sql: don't increment row count on FK creation within CREATE TABLE Nikita Pettik 2018-11-14 12:32 ` [tarantool-patches] " Vladislav Shpilevoy 2018-11-14 16:20 ` n.pettik 2018-11-13 16:11 ` [tarantool-patches] [PATCH v2 2/4] sql: account REPLACE as two row changes Nikita Pettik 2018-11-14 12:32 ` [tarantool-patches] " Vladislav Shpilevoy 2018-11-14 16:20 ` n.pettik 2018-11-13 16:11 ` [tarantool-patches] [PATCH v2 3/4] sql: remove total_changes() function Nikita Pettik 2018-11-13 16:11 ` [tarantool-patches] [PATCH v2 4/4] sql: rename changes() to row_count() Nikita Pettik 2018-11-14 12:32 ` [tarantool-patches] " Vladislav Shpilevoy 2018-11-14 16:20 ` n.pettik [this message] 2018-11-14 16:43 ` Vladislav Shpilevoy 2018-11-15 5:06 ` [tarantool-patches] Re: [PATCH v2 0/4] Introduce row_count() function Kirill Yukhin
Reply instructions: You may reply publicly to this message via plain-text email using any one of the following methods: * Save the following mbox file, import it into your mail client, and reply-to-all from there: mbox Avoid top-posting and favor interleaved quoting: https://en.wikipedia.org/wiki/Posting_style#Interleaved_style * Reply using the --to, --cc, and --in-reply-to switches of git-send-email(1): git send-email \ --in-reply-to=256054A5-FFFE-46BA-A862-50A4C9A4AA18@tarantool.org \ --to=korablev@tarantool.org \ --cc=tarantool-patches@freelists.org \ --cc=v.shpilevoy@tarantool.org \ --subject='[tarantool-patches] Re: [PATCH v2 4/4] sql: rename changes() to row_count()' \ /path/to/YOUR_REPLY https://kernel.org/pub/software/scm/git/docs/git-send-email.html * If your mail client supports setting the In-Reply-To header via mailto: links, try the mailto: link
This is a public inbox, see mirroring instructions for how to clone and mirror all data and code used for this inbox