[MDEV-6422] More testing for ANALYZE stmt and JSON Created: 2014-07-07  Updated: 2022-09-21  Resolved: 2022-09-19

Status: Closed
Project: MariaDB Server
Component/s: Tests
Fix Version/s: N/A

Type: Task Priority: Minor
Reporter: Sergei Petrunia Assignee: Elena Stepanova
Resolution: Done Votes: 0
Labels: analyze-stmt

Issue Links:
Blocks
is blocked by MDEV-7024 Assertion `! is_set()' failed in Diag... Closed
Relates
relates to MDEV-7025 ANALYZE SELECT/INSERT/UPDATE/DELETE f... Closed
relates to MDEV-7027 ANALYZE SELECT/INSERT/UPDATE/DELETE f... Closed
relates to MDEV-7115 ANALYZE SELECT FROM empty table produ... Open
relates to MDEV-7117 ANALYZE SELECT produces strange r_fil... Confirmed
relates to MDEV-7927 Server crashes in in Time_and_counter... Closed
relates to MDEV-8829 Assertion `0' failed in Explain_table... Closed
relates to MDEV-8830 Weird output in the error log Closed
relates to MDEV-406 ANALYZE $stmt Closed
relates to MDEV-7023 Error 2027: Malformed packet and asse... Closed

 Description   

Development tasks:
MDEV-406 ANALYZE $stmt
MDEV-6388 ANALYZE $stmt output in the slow query log
MDEV-6382 ANALYZE $stmt and security
MDEV-6109 EXPLAIN JSON

Need to check also for

  • Replication (ANALYZE may have an effect. Is it properly binlogged. Can a slave thread run ANALYZE stmt command?)
  • Nested VIEWs and single-table UPDATE/DELETE (check EXPLAINs)

    <spetrunia>: there was a problem with printing table names in ANALYZE
    <spetrunia>: it was resolved
    <spetrunia>: but I still had doubts.. so, it would be nice to make a testcase that would use nested update-able VIEW
    <spetrunia>: then run EXPLAIN UPDATE/DELETE on it
    <spetrunia>: then run ANALYZE UPDATE/DELETE
    <spetrunia>: and check that the ouptuts match
    <spetrunia>: the problem was : it used to print table aliases (as defined by the VIEW)
    <spetrunia>: and not table names
    <spetrunia>: or was it vice versa
    <spetrunia>: anyhow, it was not what EXPLAIN printed
    <spetrunia>: I don't think it's needed to spend much time on this..
    <spetrunia>: create a test case; let view definitions use table aliases
    <spetrunia>: which are different from the table names
    <spetrunia>: if it works, ok

  • PS (binary protocol)
  • Get testcases for MDEV-6382
  • Whether the fact that even basic statements, like "INSERT INTO tbl VALUES ('foo')" will have their "EXPLAIN" printed into the slow query log. This is a consequence of them having EXPLAINs. Do we need a feature that avoids printing 'degenerate' query plans into the slow query log?


 Comments   
Comment by Elena Stepanova [ 2014-11-05 ]

Replication (ANALYZE may have an effect. Is it properly binlogged. Can a slave thread run ANALYZE stmt command?)

No obvious issues with replication.
In statement mode ANALYZE INSERT/UPDATE/DELETE is written to the binary log as is and is executed by the slave properly (of course it will cause problems on old slaves, but it's expected).
If the underlying INSERT/UPDATE/DELETE is unsafe for SBR, ANALYZE causes the warning just like the naked statement does.
In row mode the result of the underlying statement is written and replicated as row events, ANALYZE does not play a role.
In mixed mode, the format is chosen accordingly.

Comment by Elena Stepanova [ 2014-11-05 ]

PS (binary protocol)

As of the current tree (43f185e171eecdce41e71c548ce0bc2bd6969c0f), blocked by MDEV-7024

Comment by Elena Stepanova [ 2014-11-05 ]

Whether the fact that even basic statements, like "INSERT INTO tbl VALUES ('foo')" will have their "EXPLAIN" printed into the slow query log. This is a consequence of them having EXPLAINs. Do we need a feature that avoids printing 'degenerate' query plans into the slow query log?

My vote is that disabling degenerate plans might be a nice-to-have feature, but of a very low priority. Probably it makes most sense to leave it for now and to see if we have requests for that from actual users.

Comment by Elena Stepanova [ 2014-11-14 ]

Attached test case to MDEV-6382

Comment by Elena Stepanova [ 2014-11-14 ]

Nested VIEWs and single-table UPDATE/DELETE (check EXPLAINs)

I am not getting any difference at table alias names between EXPLAIN and ANALYZE:

MariaDB [test]> show create table t1 \G
 
*************************** 1. row ***************************
 
       Table: t1
 
Create Table: CREATE TABLE `t1` (
 
  `a` int(11) DEFAULT NULL,
 
  `b` int(11) DEFAULT NULL,
 
  KEY `b` (`b`)
 
) ENGINE=MyISAM DEFAULT CHARSET=latin1
 
1 row in set (0.00 sec)
 
 
 
MariaDB [test]> create view v1 as select * from t1 alias1;
 
Query OK, 0 rows affected (0.06 sec)
 
 
 
MariaDB [test]> create view v2 as select * from v1 alias2;
 
Query OK, 0 rows affected (0.07 sec)
 
 
 
MariaDB [test]> create view v3 as select alias3.* from t1 alias3, t2 where alias3.b = t2.b;
 
Query OK, 0 rows affected (0.13 sec)
 

MariaDB [test]> explain select * from v1;
 
+------+-------------+--------+------+---------------+------+---------+------+------+-------+
 
| id   | select_type | table  | type | possible_keys | key  | key_len | ref  | rows | Extra |
 
+------+-------------+--------+------+---------------+------+---------+------+------+-------+
 
|    1 | SIMPLE      | alias1 | ALL  | NULL          | NULL | NULL    | NULL | 1024 |       |
 
+------+-------------+--------+------+---------------+------+---------+------+------+-------+
 
1 row in set (0.01 sec)
 
 
 
MariaDB [test]> analyze select * from v1;
 
+------+-------------+--------+------+---------------+------+---------+------+------+--------+----------+------------+-------+
 
| id   | select_type | table  | type | possible_keys | key  | key_len | ref  | rows | r_rows | filtered | r_filtered | Extra |
 
+------+-------------+--------+------+---------------+------+---------+------+------+--------+----------+------------+-------+
 
|    1 | SIMPLE      | alias1 | ALL  | NULL          | NULL | NULL    | NULL | 1024 |   1024 |   100.00 |     100.00 |       |
 
+------+-------------+--------+------+---------------+------+---------+------+------+--------+----------+------------+-------+
 
1 row in set (0.01 sec)
 

MariaDB [test]> explain select * from v2;
 
+------+-------------+--------+------+---------------+------+---------+------+------+-------+
 
| id   | select_type | table  | type | possible_keys | key  | key_len | ref  | rows | Extra |
 
+------+-------------+--------+------+---------------+------+---------+------+------+-------+
 
|    1 | SIMPLE      | alias1 | ALL  | NULL          | NULL | NULL    | NULL | 1024 |       |
 
+------+-------------+--------+------+---------------+------+---------+------+------+-------+
 
1 row in set (0.00 sec)
 
 
 
MariaDB [test]> analyze select * from v2;
 
+------+-------------+--------+------+---------------+------+---------+------+------+--------+----------+------------+-------+
 
| id   | select_type | table  | type | possible_keys | key  | key_len | ref  | rows | r_rows | filtered | r_filtered | Extra |
 
+------+-------------+--------+------+---------------+------+---------+------+------+--------+----------+------------+-------+
 
|    1 | SIMPLE      | alias1 | ALL  | NULL          | NULL | NULL    | NULL | 1024 |   1024 |   100.00 |     100.00 |       |
 
+------+-------------+--------+------+---------------+------+---------+------+------+--------+----------+------------+-------+
 
1 row in set (0.00 sec)
 

MariaDB [test]> explain select * from v3;
 
+------+-------------+--------+-------+---------------+------+---------+-----------+------+--------------------------+
 
| id   | select_type | table  | type  | possible_keys | key  | key_len | ref       | rows | Extra                    |
 
+------+-------------+--------+-------+---------------+------+---------+-----------+------+--------------------------+
 
|    1 | SIMPLE      | t2     | index | b             | b    | 5       | NULL      |  100 | Using where; Using index |
 
|    1 | SIMPLE      | alias3 | ref   | b             | b    | 5       | test.t2.b |   64 |                          |
 
+------+-------------+--------+-------+---------------+------+---------+-----------+------+--------------------------+
 
2 rows in set (0.00 sec)
 
 
 
MariaDB [test]> analyze select * from v3;
 
+------+-------------+--------+-------+---------------+------+---------+-----------+------+--------+----------+------------+--------------------------+
 
| id   | select_type | table  | type  | possible_keys | key  | key_len | ref       | rows | r_rows | filtered | r_filtered | Extra                    |
 
+------+-------------+--------+-------+---------------+------+---------+-----------+------+--------+----------+------------+--------------------------+
 
|    1 | SIMPLE      | t2     | index | b             | b    | 5       | NULL      |  100 |    100 |   100.00 |     100.00 | Using where; Using index |
 
|    1 | SIMPLE      | alias3 | ref   | b             | b    | 5       | test.t2.b |   64 |    256 |   100.00 |     100.00 |                          |
 
+------+-------------+--------+-------+---------------+------+---------+-----------+------+--------+----------+------------+--------------------------+
 
2 rows in set (0.05 sec)
 

MariaDB [test]> explain update v1 set a = 1 where b = 100;
 
+------+-------------+--------+------+---------------+------+---------+------+------+-------------+
 
| id   | select_type | table  | type | possible_keys | key  | key_len | ref  | rows | Extra       |
 
+------+-------------+--------+------+---------------+------+---------+------+------+-------------+
 
|    1 | SIMPLE      | alias1 | ALL  | b             | NULL | NULL    | NULL | 1024 | Using where |
 
+------+-------------+--------+------+---------------+------+---------+------+------+-------------+
 
1 row in set (0.01 sec)
 
 
 
MariaDB [test]> analyze update v1 set a = 1 where b = 100;
 
+------+-------------+--------+------+---------------+------+---------+------+------+--------+----------+------------+-------------+
 
| id   | select_type | table  | type | possible_keys | key  | key_len | ref  | rows | r_rows | filtered | r_filtered | Extra       |
 
+------+-------------+--------+------+---------------+------+---------+------+------+--------+----------+------------+-------------+
 
|    1 | SIMPLE      | alias1 | ALL  | b             | NULL | NULL    | NULL | 1024 |   1024 |   100.00 |      25.00 | Using where |
 
+------+-------------+--------+------+---------------+------+---------+------+------+--------+----------+------------+-------------+
 
1 row in set (0.00 sec)
 

MariaDB [test]> explain update v2 set a = 1 where b = 100;
 
+------+-------------+--------+------+---------------+------+---------+------+------+-------------+
 
| id   | select_type | table  | type | possible_keys | key  | key_len | ref  | rows | Extra       |
 
+------+-------------+--------+------+---------------+------+---------+------+------+-------------+
 
|    1 | SIMPLE      | alias1 | ALL  | b             | NULL | NULL    | NULL | 1024 | Using where |
 
+------+-------------+--------+------+---------------+------+---------+------+------+-------------+
 
1 row in set (0.00 sec)
 
 
 
MariaDB [test]> analyze update v2 set a = 1 where b = 100;
 
+------+-------------+--------+------+---------------+------+---------+------+------+--------+----------+------------+-------------+
 
| id   | select_type | table  | type | possible_keys | key  | key_len | ref  | rows | r_rows | filtered | r_filtered | Extra       |
 
+------+-------------+--------+------+---------------+------+---------+------+------+--------+----------+------------+-------------+
 
|    1 | SIMPLE      | alias1 | ALL  | b             | NULL | NULL    | NULL | 1024 |   1024 |   100.00 |      25.00 | Using where |
 
+------+-------------+--------+------+---------------+------+---------+------+------+--------+----------+------------+-------------+
 
1 row in set (0.00 sec)
 

MariaDB [test]> explain update v3 set a = 1 where b = 100;
 
+------+-------------+--------+------+---------------+------+---------+-------+------+-------------+
 
| id   | select_type | table  | type | possible_keys | key  | key_len | ref   | rows | Extra       |
 
+------+-------------+--------+------+---------------+------+---------+-------+------+-------------+
 
|    1 | SIMPLE      | t2     | ref  | b             | b    | 5       | const |   13 | Using index |
 
|    1 | SIMPLE      | alias3 | ref  | b             | b    | 5       | const |  293 |             |
 
+------+-------------+--------+------+---------------+------+---------+-------+------+-------------+
 
2 rows in set (0.00 sec)
 
 
 
MariaDB [test]> analyze update v3 set a = 1 where b = 100;
 
+------+-------------+--------+------+---------------+------+---------+-------+------+--------+----------+------------+-------------+
 
| id   | select_type | table  | type | possible_keys | key  | key_len | ref   | rows | r_rows | filtered | r_filtered | Extra       |
 
+------+-------------+--------+------+---------------+------+---------+-------+------+--------+----------+------------+-------------+
 
|    1 | SIMPLE      | t2     | ref  | b             | b    | 5       | const |   13 |     25 |   100.00 |     100.00 | Using index |
 
|    1 | SIMPLE      | alias3 | ref  | b             | b    | 5       | const |  293 |    256 |   100.00 |     100.00 |             |
 
+------+-------------+--------+------+---------------+------+---------+-------+------+--------+----------+------------+-------------+
 
2 rows in set (0.02 sec)
 

MariaDB [test]> explain delete from v1 where b = 100;
 
+------+-------------+--------+------+---------------+------+---------+------+------+-------------+
 
| id   | select_type | table  | type | possible_keys | key  | key_len | ref  | rows | Extra       |
 
+------+-------------+--------+------+---------------+------+---------+------+------+-------------+
 
|    1 | SIMPLE      | alias1 | ALL  | b             | NULL | NULL    | NULL | 1024 | Using where |
 
+------+-------------+--------+------+---------------+------+---------+------+------+-------------+
 
1 row in set (0.00 sec)
 
 
 
MariaDB [test]> analyze delete from v1 where b = 100;
 
+------+-------------+--------+------+---------------+------+---------+------+------+--------+----------+------------+-------------+
 
| id   | select_type | table  | type | possible_keys | key  | key_len | ref  | rows | r_rows | filtered | r_filtered | Extra       |
 
+------+-------------+--------+------+---------------+------+---------+------+------+--------+----------+------------+-------------+
 
|    1 | SIMPLE      | alias1 | ALL  | b             | NULL | NULL    | NULL | 1024 |   1024 |   100.00 |      25.00 | Using where |
 
+------+-------------+--------+------+---------------+------+---------+------+------+--------+----------+------------+-------------+
 
1 row in set (0.01 sec)
 

MariaDB [test]> explain delete from v2 where b = 200;
 
+------+-------------+--------+------+---------------+------+---------+------+------+-------------+
 
| id   | select_type | table  | type | possible_keys | key  | key_len | ref  | rows | Extra       |
 
+------+-------------+--------+------+---------------+------+---------+------+------+-------------+
 
|    1 | SIMPLE      | alias1 | ALL  | b             | NULL | NULL    | NULL |  768 | Using where |
 
+------+-------------+--------+------+---------------+------+---------+------+------+-------------+
 
1 row in set (0.00 sec)
 
 
 
MariaDB [test]> analyze delete from v2 where b = 200;
 
+------+-------------+--------+------+---------------+------+---------+------+------+--------+----------+------------+-------------+
 
| id   | select_type | table  | type | possible_keys | key  | key_len | ref  | rows | r_rows | filtered | r_filtered | Extra       |
 
+------+-------------+--------+------+---------------+------+---------+------+------+--------+----------+------------+-------------+
 
|    1 | SIMPLE      | alias1 | ALL  | b             | NULL | NULL    | NULL |  768 |    768 |   100.00 |      33.33 | Using where |
 
+------+-------------+--------+------+---------------+------+---------+------+------+--------+----------+------------+-------------+
 
1 row in set (0.00 sec)

Comment by Elena Stepanova [ 2014-11-14 ]

To be resumed after MDEV-7024 has been fixed.

Generated at Thu Feb 08 07:11:47 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.