Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-37775

spider inconsistent pushdown of STRAIGHT_JOIN as a hint vs as join operators

    XMLWordPrintable

Details

    Description

      Tested on main but probably affecting 10.6+

      spider prints STRAIGHT_JOIN as join operators in gbh, but not as an optimizer hint, see the testcase below (note the output lines starting with select t0.`c`...):

      CREATE SERVER srv FOREIGN DATA WRAPPER mysql
      OPTIONS (SOCKET "$MASTER_1_MYSOCK", DATABASE 'test',user 'root');
      create table t2 (c int, index i(c));
      create table t1 (c int, index i(c)) ENGINE=Spider
      COMMENT='WRAPPER "mysql", srv "srv",TABLE "t2"';
      Warnings:
      Warning	138	Spider table params in COMMENT or CONNECTION strings have been deprecated and will be removed in a future release. Please use table options instead.
      explain select STRAIGHT_JOIN * from t1 t3, t1 t4 where t3.c > t4.c;
      id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
      1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Storage engine handles GROUP BY
      select STRAIGHT_JOIN * from t1 t3, t1 t4 where t3.c > t4.c;
      c	c
      explain select * from t1 t3 STRAIGHT_JOIN t1 t4 on t3.c > t4.c;
      id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
      1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Storage engine handles GROUP BY
      select * from t1 t3 STRAIGHT_JOIN t1 t4 on t3.c > t4.c;
      c	c
      SELECT argument FROM mysql.general_log WHERE argument LIKE 'select %';
      argument
      select STRAIGHT_JOIN * from t1 t3, t1 t4 where t3.c > t4.c
      select t0.`c` `c`,t1.`c` `c` from `test`.`t2` t0 join `test`.`t2` t1 where (t0.`c` > t1.`c`)
      select * from t1 t3 STRAIGHT_JOIN t1 t4 on t3.c > t4.c
      select t0.`c` `c`,t1.`c` `c` from `test`.`t2` t0 straight_join `test`.`t2` t1 where (t0.`c` > t1.`c`)
      SELECT argument FROM mysql.general_log WHERE argument LIKE 'select %'
      

      A possible place to fix it is spider_mbase_handler::append_select, where other optimizer hints are pushed down.

      Attachments

        Issue Links

          Activity

            People

              ycp Yuchen Pei
              ycp Yuchen Pei
              Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

              Dates

                Created:
                Updated:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.