[MDEV-30025] EXPLAIN for SELECT INTO OUTFILE does not report an error message Created: 2022-11-17  Updated: 2022-11-22

Status: Open
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 10.3, 10.4, 10.5, 10.6, 10.7, 10.8, 10.9, 10.10, 10.11
Fix Version/s: 10.3

Type: Bug Priority: Major
Reporter: Igor Babaev Assignee: Sergei Petrunia
Resolution: Unresolved Votes: 0
Labels: None


 Description   

If to add the statement

SELECT *
 
INTO OUTFILE '/tmp/t1.txt'
 
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\r\n'
 
FROM t1;

to the test case for the bug "Bug#5382 'explain select into outfile' crashes the server" from outfile.test
then execution of ./mtr main.outfile fails as follows:

main.outfile                             [ fail ]
 
        Test ended at 2022-11-16 21:57:57
 
 
 
CURRENT_TEST: main.outfile
 
mysqltest: At line 70: query 'SELECT *
 
INTO OUTFILE '/tmp/t1.txt'
 
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\r\n'
 
FROM t1' failed: 1290: The MariaDB server is running with the --secure-file-priv option so it cannot execute this statement
 
 
 
The result from queries just before the failure was:
 
< snip >
 
select load_file(concat(@tmpdir,"/outfile-test.not-exist"));
 
load_file(concat(@tmpdir,"/outfile-test.not-exist"))
 
NULL
 
drop table t1;
 
select load_file(concat(@tmpdir,"/outfile-test.4"));
 
load_file(concat(@tmpdir,"/outfile-test.4"))
 
1
 
 
 
CREATE TABLE t1 (a INT);
 
EXPLAIN
 
SELECT *
 
INTO OUTFILE '/tmp/t1.txt'
 
  FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\r\n'
 
  FROM t1;
 
id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
 
1       SIMPLE  t1      system  NULL    NULL    NULL    NULL    0       Const row not found
 
SELECT *
 
INTO OUTFILE '/tmp/t1.txt'
 
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\r\n'
 
FROM t1;

One can see that EXPLAIN just does not report an error that it should report as it the error is encountered at the prepare phase in JOIN::prepare().

This is a consequence of a serious defect of the code that processes EXPLAIN for those variants of SELECT that employ classes whose prepare virtual method differs from the method of select_result::prepare().



 Comments   
Comment by Igor Babaev [ 2022-11-17 ]

I tried the following change of the code of execute_sqlcom_select() of the current 10.11 and it worked fine for all test cases from the main test suite (except of those from outfile.test of course)

#if 0
 
      /*
 
        We always use select_send for EXPLAIN, even if it's an EXPLAIN
 
        for SELECT ... INTO OUTFILE: a user application should be able
 
        to prepend EXPLAIN to any query and receive output for it,
 
        even if the query itself redirects the output.
 
      */
 
      if (unlikely(!(result= new (thd->mem_root) select_send(thd))))
 
        return 1;                               /* purecov: inspected */
 
      thd->send_explain_fields(result, lex->describe, lex->analyze_stmt);
 
#else
 
      if (unlikely(!result && !(result= new (thd->mem_root) select_send(thd))))
 
        return 1;
 
#endif
 
        
      /*
 
        This will call optimize() for all parts of query. The query plan is
 
        printed out below.
 
      */
 
      res= mysql_explain_union(thd, &lex->unit, result);
 
 
 
#if 0   
      /* Print EXPLAIN only if we don't have an error */
 
      if (likely(!res))
 
      {
 
        /* 
          Do like the original select_describe did: remove OFFSET from the
 
          top-level LIMIT
 
        */
 
        result->remove_offset_limit();
 
        if (lex->explain_json)
 
        {
 
          lex->explain->print_explain_json(result, lex->analyze_stmt,
 
                                           false /* is_show_cmd */);
 
        }
 
        else
 
        {
 
          lex->explain->print_explain(result, thd->lex->describe,
 
                                      thd->lex->analyze_stmt);
 
          if (lex->describe & DESCRIBE_EXTENDED)
 
          {
 
            char buff[1024];
 
            String str(buff,(uint32) sizeof(buff), system_charset_info);
 
            str.length(0);
 
            /*
 
              The warnings system requires input in utf8, @see
 
              mysqld_show_warnings().
 
            */
 
            lex->unit.print(&str, QT_EXPLAIN_EXTENDED);
 
            push_warning(thd, Sql_condition::WARN_LEVEL_NOTE,
 
                         ER_YES, str.c_ptr_safe());
 
          }
 
        }
 
      }
 
      if (res)
 
        result->abort_result_set();
 
      else
 
        result->send_eof();
 
      delete result;
 
#else
 
      if (!res)
 
      {
 
        res= thd->lex->explain->send_explain(thd);
 
        if (lex->describe & DESCRIBE_EXTENDED)
 
        {
 
          char buff[1024];
 
          String str(buff,(uint32) sizeof(buff), system_charset_info);
 
          str.length(0);
 
          /*
 
            The warnings system requires input in utf8, @see
 
            mysqld_show_warnings().
 
          */
 
          lex->unit.print(&str, QT_EXPLAIN_EXTENDED);
 
          push_warning(thd, Sql_condition::WARN_LEVEL_NOTE,
 
                       ER_YES, str.c_ptr_safe());
 
        }
 
      }
 
      if (unlikely(res))
 
        result->abort_result_set();
 
      if (result != lex->result)
 
        delete result;
 
#endif

I do not provide diff in order one could play with both variants and see the difference when processing the reported query. My code processes EXPLAIN for SELECT variants in the same way as EXPLAIN is processed for multi-table UPDATE/DELETE.

Generated at Thu Feb 08 10:13:04 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.