WL#3502: enable and test non-materialized consistent-read-based cursors

Affects: Server-6.3   —   Status: Un-Assigned 

We support materialized cursors (at cursor OPEN time all rows from the SELECT
are retrieved and stored into a temp table).
This obeys the requirement that FETCH always returns the row as it was at OPEN
time, but is too slow for SAP R/3 (too much disk read/writes at OPEN).
An alternative has been developed by Konstantin Osipov and Innobase:
non-materialized, consistent-read cursors: at OPEN time nothing notable happens,
and at FETCH time the engine (InnoDB) uses its versioning to show the row as it
was at the moment of OPEN.
This alternative is currently disabled in release builds and MySQL AB
developers' builds; to enable it one needs to patch sql/ a bit. It is so not
tested at all in the tests included in our server code (mysql-test) (it has been
tested by the SAP team). This runs the risk of seeing the feature silently
degrade (bugs introduced and not caught by testing), which will require
debugging when we later want to test this feature with Falcon and Maria (Falcon
and Maria will need this feature, because R/3 will need it).

The proposed task is to enable this feature in all builds (release builds too),
and document it.
Enabling, has risks:
- receiving bug reports and having to fix them
- giving InnoDB a competitive edge as it is currently the only engine to have
support for it (i.e. to have a create_cursor_read_view in its handlerton).

To enable it, we need:
- to allow the user to choose, through the prepared statement API, between a
materialized cursor and a possibly (if engine allows) non-materialized one; one
proposal is to have
mysql_stmt_attr_set(stmt, STMT_ATTR_CURSOR_TYPE,
CURSOR_OPTIMIZE_FOR_FIRST_ROW); /* triggers non-materialized cursors */
CURSOR_OPTIMIZE_FOR_ALL_ROWS /* triggers materialized cursor, is the default */
(but these names are to be discussed, could also be CURSOR_ALWAYS_MATERIALIZE vs
CURSOR_TRY_NOT_MATERIALIZE, or something else).
To make it easy for a customer to easily switch his application between
materialized and non-materialized without recompiling his application (examples:
to test non-materialized cursors, also to be used by consultants to debug
performance or to debug wrong results), there could be a global+session variable
@@default_cursor_type (default: materialized),
then the existing type CURSOR_TYPE_READ_ONLY could pick the value of
@@session.default_cursor_type, while the two other types (CURSOR_OPTIMIZE*)
would force a type.
- to create a test for this feature (Guilhem has an ugly test which he would
need to clean up). One may is to modify mysql_client_test, but Guilhem would
prefer to add "open_cursor", "fetch_cursor", "close_cursor" commands to
mysqltest and have a test in mysql-test/t, that would allow reusing of the
capabilities of mysqltest (comparison of output to expected result, expectable
errors, multiple connections, all things doable in mysql_client_test but with
much more work); Konstantin seems to favor the mysqltest way too.
The test scenarios should first be the requirements from SAP
listed in the HLS of WL#1107 "Server side cursors". The tests should be done in
READ COMMITTED (default level of R/3) and in REPEATABLE READ; for example for
the test labelled "H" there, it could read in a .test file:
connection con1;
drop table if exists t1;
create table t1 (a int, val int);
insert into t1 (a) values (1,"one"), (2,"two");
set session.default_cursor_type=CURSOR_OPTIMIZE_FOR_FIRST_ROW;
open_cursor c select * from t1;
update t1 set val="three" where a=2;
delete from t1 where a=1;
fetch_cursor c;
fetch_cursor c;
open_cursor c2 select * from t1;
fetch_cursor c2;
fetch_cursor c2;
We can have a wrapper around this test, which just sets
session.default_cursor_type and includes the real test, this way we can easily
compare results of materialized and non-materialized. In the wrapper we can also
set the transaction isolation level as it is a relevant parameter.

Other subtasks:
- class Sensitive_cursor is in fact an insensitive non-materialized cursor (as
Guilhem has tested), rename this class.
- tests should have a way to make sure that their cursor is not materialized
(otherwise, if by mistake someone makes a change to the server code and causes
all cursors to be materialized, the tests will not notice it and it will appear
just as a performance issue later). One way is to maintain counts of
materialized/non-materialized cursors in SHOW STATUS, this can also be used by
our Services team to troubleshoot performance. Another way is to add a property
to the statement object in the MySQL API, which would tell if the cursor is
materialized or not.

If we go with mysqltest's extensions, it could look like this:
OPEN_CURSOR