[tarantool-patches] Re: [PATCH v2 4/4] sql: rename changes() to row_count()

[n.pettik]

>> 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.