Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
None
Description
create table t1 (i int);
|
insert into t1 values (1);
|
ANALYZE SELECT ... INTO @var will not set @var:
analyze select * from t1 into @var;
|
+------+-------------+-------+------+---------------+------+---------+------+------+--------+----------+------------+-------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra |
|
+------+-------------+-------+------+---------------+------+---------+------+------+--------+----------+------------+-------+
|
| 1 | SIMPLE | t1 | ALL | NULL | NULL | NULL | NULL | 1 | 1 | 100.00 | 100.00 | |
|
+------+-------------+-------+------+---------------+------+---------+------+------+--------+----------+------------+-------+
|
1 row in set (0.00 sec)
|
|
|
MariaDB [j14]> select @var;
|
+------+
|
| @var |
|
+------+
|
| NULL |
|
+------+
|
1 row in set (0.00 sec)
|
If we assume that ANALYZE $stmt should make the action specified by $stmt, then @var should be set.
Attachments
Issue Links
- relates to
-
MDEV-406 ANALYZE $stmt
-
- Closed
-
Activity
Replacing the result output sink may have an effect on the result of the query:
MariaDB [j1]> select a from t1 into @var;
|
ERROR 1172 (42000): Result consisted of more than one row
|
MariaDB [j1]> analyze select a from t1 into @var;
|
+------+-------------+-------+------+---------------+------+---------+------+------+--------+----------+------------+-------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra |
|
+------+-------------+-------+------+---------------+------+---------+------+------+--------+----------+------------+-------+
|
| 1 | SIMPLE | t1 | ALL | NULL | NULL | NULL | NULL | 2 | 2 | 100.00 | 100.00 | |
|
+------+-------------+-------+------+---------------+------+---------+------+------+--------+----------+------------+-------+
|
Another example:
MariaDB [j1]> analyze insert into t2 select * from t1 where a >=2 ;
|
Query OK, 1 row affected (0.08 sec)
|
Records: 1 Duplicates: 0 Warnings: 0
|
it inserts rows (correct behavior) but doesn't produce EXPLAIN output (wrong).
EXPLAIN works for
- INSERT
- INSERT ... SELECT
- SELECT ... INTO @var
- SELECT INTO OUTFILE... (prints EXPLAIN into client, not into file)
EXPLAIN doesnt work for
CREATE TABLE AS SELECT
ANALYZE needs to work whenever SELECT works.
Output remover should work for select_send (so that it prevents any output from being sent to the client).
It should not work for:
- select_insert (select_create inherits)
- select_dumpvar
- select_dumpfile
these should work as usual, except that they should not call my_ok. (as this prevents us from printing ANALYZE stmt output).
Internally, it is clear - ANALYZE code captures and discards the output. Maybe, it should detect outputs that don't send to the client (like. select_dumpvar) and let them still capture the output.