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

Same query cannot be executed using CONNECT Engine

    Details

      Description

      CREATE TABLE `forum_main_post` (
        `id` bigint(20) NOT NULL,
        `group_id` int(11) DEFAULT NULL,
        `user_id` bigint(20) DEFAULT NULL,
        `title` varchar(300) DEFAULT NULL,
        `level` int(11) NOT NULL DEFAULT '0' COMMENT '??',
        `last_update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
        `type` int(11) DEFAULT '0' COMMENT '1?? 2?? 4??',
        `order_weight` int(11) DEFAULT '0' COMMENT '????',
        `beauty` tinyint(4) NOT NULL DEFAULT '0' COMMENT '???????1??????,2????',
        `score` double DEFAULT '0' COMMENT '排序score',
        PRIMARY KEY (`id`),
        KEY `user_id` (`user_id`,`type`),
        KEY `post_list_idx` (`score`,`group_id`,`type`),
        KEY `post_list_idx2` (`type`,`score`),
        KEY `post_list_idx3` (`id`,`type`,`score`),
        KEY `post_list_idx4` (`id`,`score`,`user_id`,`type`)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

      CREATE TABLE `forum_post_maria` (
        `id` int(20) NOT NULL,
        `root_id` int(20) NOT NULL DEFAULT '0' COMMENT '??ID',
        `user_id` int(20) NOT NULL,
        `parent_id` int(20) NOT NULL DEFAULT '0' COMMENT '????????id',
        `level` int(11) NOT NULL DEFAULT '0' COMMENT '??',
        `status` int(11) NOT NULL DEFAULT '1' COMMENT '0?????1:????;2:?????,?????-1??????-2??????-3????;-4 ???;-5 ????',
        `path` varchar(4096) NOT NULL DEFAULT '' COMMENT '???????pid???',
        `create_time` datetime NOT NULL DEFAULT '2014-01-01 00:00:00' COMMENT '????',
        `auditor` int(11) NOT NULL DEFAULT '0' COMMENT '?????',
        `audit_time` datetime DEFAULT NULL COMMENT '????',
        `last_update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
        PRIMARY KEY (`root_id`),
        KEY `id` (`id`),
        KEY `create_time` (`create_time`),
        KEY `root_id_uid` (`root_id`,`status`,`user_id`),
        KEY `status` (`status`),
        KEY `user_id` (`user_id`)
      ) ENGINE=CONNECT `table_type`=MYSQL `dbname`='medusa' `tabname`='forum_post_%s' `option_list`='user=medusa,host=localhost,password=medusa123,quoted=1'
      /*!50100 PARTITION BY HASH (root_id)
      PARTITIONS 10 */;

      CREATE TABLE `forum_post` (
        `id` bigint(20) NOT NULL AUTO_INCREMENT,
        `root_id` bigint(20) NOT NULL DEFAULT '0' COMMENT '??ID',
        `user_id` bigint(20) DEFAULT NULL,
        `parent_id` bigint(20) NOT NULL DEFAULT '0' COMMENT '????????id',
        `level` int(11) NOT NULL DEFAULT '0' COMMENT '??',
        `status` int(11) NOT NULL DEFAULT '1' COMMENT '0?????1:????;2:?????,?????-1??????-2??????-3????;-4 ???;-5 ????',
        `path` varchar(4096) NOT NULL DEFAULT '' COMMENT '???????pid???',
        `create_time` datetime DEFAULT NULL COMMENT '????',
        `auditor` int(11) NOT NULL DEFAULT '0' COMMENT '?????',
        `audit_time` datetime NOT NULL DEFAULT '1970-01-01 00:00:00',
        `last_update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
        PRIMARY KEY (`id`),
        KEY `create_time` (`create_time`),
        KEY `root_id_uid` (`root_id`,`status`,`user_id`),
        KEY `status` (`status`),
        KEY `user_id` (`user_id`)
      ) ENGINE=InnoDB AUTO_INCREMENT=99314692 DEFAULT CHARSET=utf8
        /*!50100 PARTITION BY HASH (id)
      PARTITIONS 100 */;

      SELECT
        fmp.id,
        fmp.user_id,
        fmp.title,
        fmp.last_update_time,
        fmp.type,
        fmp.order_weight,
        fmp.beauty,
        fmp.score
      FROM forum_main_post fmp
        LEFT JOIN forum_post_maria fp ON fp.id = fmp.id
      WHERE fmp.type = 2
      ORDER BY fmp.score DESC
      LIMIT 0, 1000;

      When I execute the query, it says ERROR 1296 (HY000): Got error 122 '(1064) You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'WHERE (`id` = 6621139)' at line 1 [SELECT `id` FROM `for' from CONNECT;
      But if I change the forum_post_maria to forum_post it works fine.The definitions of two tables are almost the same.

      MariaDB [medusa]> SELECT fmp.id, fmp.user_id, fmp.title, fmp.last_update_time, fmp.type, fmp.order_weight, fmp.beauty, fmp.score FROM forum_main_post fmp LEFT JOIN forum_post_maria fp ON fp.id = fmp.id WHERE fmp.type = 2 ORDER BY fmp.score DESC LIMIT 0, 1000;
      ERROR 1296 (HY000): Got error 122 '(1064) You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'WHERE (`id` = 6621139)' at line 1 [SELECT `id` FROM `for' from CONNECT

        Attachments

          Activity

            People

            • Assignee:
              bertrandop Olivier Bertrand
              Reporter:
              tramchamploo tramchamploo
            • Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: