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

Syntax error upon query with subquery from Spider table

Details

    Description

      # This may not work, e.g. for in-source builds, fix the path
      		 
      --source plugin/spider/spider/include/init_spider.inc
      		 
       
      		 
      SET spider_same_server_link= on;
      		 
      --eval create server s foreign data wrapper mysql options (host "127.0.0.1", database "test", user "root", port $MASTER_MYPORT);
      		 
       
      		 
      CREATE TABLE t (a INT);
      		 
      INSERT INTO t VALUES (1),(2);
      		 
      CREATE TABLE t_spider (a INT) ENGINE=SPIDER COMMENT = "wrapper 'mysql', srv 's', table 't'";
      		 
       
      		 
      SELECT a FROM t_spider WHERE a IN ( SELECT a FROM t_spider );
      		 
       
      		 
      # Cleanup
      		 
       
      		 
      DROP TABLE t_spider, t;
      		 
      DROP SERVER s;
      		 
       
      		 
      This may not work, e.g. for in-source builds, fix the path
      		 
      --source plugin/spider/spider/include/deinit_spider.inc

      10.4 f97f6955

      		 
      mysqltest: At line 12: query 'SELECT a FROM t_spider WHERE a IN ( SELECT a FROM t_spider )' failed: 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 '`test`.`t` t1 where 1' at line 1

      The complaint is of course about the query that Spider generates to access remote, which is

      select t0.`a` `a` from `test`.`t` t0`test`.`t` t1 where 1

      Attachments

        Issue Links

          is blocked by

          Bug - A problem which impairs or prevents the functions of the product. MDEV-26247 Spider: Valid LEFT JOIN results in ERROR 1064

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed
          is part of

          Bug - A problem which impairs or prevents the functions of the product. MDEV-26247 Spider: Valid LEFT JOIN results in ERROR 1064

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed
          relates to

          Task - A task that needs to be done. MDEV-32238 Add a switch to disable spider group by handler

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Activity

            Ascending order - Click to sort in descending order
            elenst Elena Stepanova created issue -
            elenst Elena Stepanova made changes -
            Field Original Value New Value
            Description {code:sql}
            # This may not work, e.g. for in-source builds
            --source plugin/spider/spider/include/init_spider.inc

            SET spider_same_server_link= on;
            --eval create server s foreign data wrapper mysql options (host "127.0.0.1", database "test", user "root", port $MASTER_MYPORT);

            CREATE TABLE t (a INT);
            INSERT INTO t VALUES (1),(2);
            SET spider_same_server_link= on;
            CREATE TABLE t_spider (a INT) ENGINE=SPIDER COMMENT = "wrapper 'mysql', srv 's', table 't'";

            SELECT a FROM t_spider WHERE a IN ( SELECT a FROM t_spider );

            # Cleanup

            DROP TABLE t_spider, t;
            DROP SERVER s;

            # This may not work, e.g. for in-source builds
            --source plugin/spider/spider/include/deinit_spider.inc
            {code}

            {code:sql|title=10.4 f97f6955}
            mysqltest: At line 12: query 'SELECT a FROM t_spider WHERE a IN ( SELECT a FROM t_spider )' failed: 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 '`test`.`t` t1 where 1' at line 1
            {code}

            The complaint is of course about the query that Spider generates to access remote, which is
            {code:sql}
            select t0.`a` `a` from `test`.`t` t0`test`.`t` t1 where 1
            {code}             		{code:sql}
            # This may not work, e.g. for in-source builds, fix the path
            --source plugin/spider/spider/include/init_spider.inc

            SET spider_same_server_link= on;
            --eval create server s foreign data wrapper mysql options (host "127.0.0.1", database "test", user "root", port $MASTER_MYPORT);

            CREATE TABLE t (a INT);
            INSERT INTO t VALUES (1),(2);
            SET spider_same_server_link= on;
            CREATE TABLE t_spider (a INT) ENGINE=SPIDER COMMENT = "wrapper 'mysql', srv 's', table 't'";

            SELECT a FROM t_spider WHERE a IN ( SELECT a FROM t_spider );

            # Cleanup

            DROP TABLE t_spider, t;
            DROP SERVER s;

            This may not work, e.g. for in-source builds, fix the path
            --source plugin/spider/spider/include/deinit_spider.inc
            {code}

            {code:sql|title=10.4 f97f6955}
            mysqltest: At line 12: query 'SELECT a FROM t_spider WHERE a IN ( SELECT a FROM t_spider )' failed: 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 '`test`.`t` t1 where 1' at line 1
            {code}

            The complaint is of course about the query that Spider generates to access remote, which is
            {code:sql}
            select t0.`a` `a` from `test`.`t` t0`test`.`t` t1 where 1
            {code}
            elenst Elena Stepanova made changes -
            Description {code:sql}
            # This may not work, e.g. for in-source builds, fix the path
            --source plugin/spider/spider/include/init_spider.inc

            SET spider_same_server_link= on;
            --eval create server s foreign data wrapper mysql options (host "127.0.0.1", database "test", user "root", port $MASTER_MYPORT);

            CREATE TABLE t (a INT);
            INSERT INTO t VALUES (1),(2);
            SET spider_same_server_link= on;
            CREATE TABLE t_spider (a INT) ENGINE=SPIDER COMMENT = "wrapper 'mysql', srv 's', table 't'";

            SELECT a FROM t_spider WHERE a IN ( SELECT a FROM t_spider );

            # Cleanup

            DROP TABLE t_spider, t;
            DROP SERVER s;

            This may not work, e.g. for in-source builds, fix the path
            --source plugin/spider/spider/include/deinit_spider.inc
            {code}

            {code:sql|title=10.4 f97f6955}
            mysqltest: At line 12: query 'SELECT a FROM t_spider WHERE a IN ( SELECT a FROM t_spider )' failed: 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 '`test`.`t` t1 where 1' at line 1
            {code}

            The complaint is of course about the query that Spider generates to access remote, which is
            {code:sql}
            select t0.`a` `a` from `test`.`t` t0`test`.`t` t1 where 1
            {code}             		{code:sql}
            # This may not work, e.g. for in-source builds, fix the path
            --source plugin/spider/spider/include/init_spider.inc

            SET spider_same_server_link= on;
            --eval create server s foreign data wrapper mysql options (host "127.0.0.1", database "test", user "root", port $MASTER_MYPORT);

            CREATE TABLE t (a INT);
            INSERT INTO t VALUES (1),(2);
            CREATE TABLE t_spider (a INT) ENGINE=SPIDER COMMENT = "wrapper 'mysql', srv 's', table 't'";

            SELECT a FROM t_spider WHERE a IN ( SELECT a FROM t_spider );

            # Cleanup

            DROP TABLE t_spider, t;
            DROP SERVER s;

            This may not work, e.g. for in-source builds, fix the path
            --source plugin/spider/spider/include/deinit_spider.inc
            {code}

            {code:sql|title=10.4 f97f6955}
            mysqltest: At line 12: query 'SELECT a FROM t_spider WHERE a IN ( SELECT a FROM t_spider )' failed: 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 '`test`.`t` t1 where 1' at line 1
            {code}

            The complaint is of course about the query that Spider generates to access remote, which is
            {code:sql}
            select t0.`a` `a` from `test`.`t` t0`test`.`t` t1 where 1
            {code}
            julien.fritsch Julien Fritsch made changes -
            Fix Version/s 10.7 [ 24805 ]
            julien.fritsch Julien Fritsch made changes -
            Fix Version/s 10.8 [ 26121 ]
            ycp Yuchen Pei made changes -
            Labels spider-gbh
            ycp Yuchen Pei made changes -
            ycp Yuchen Pei made changes -
            ycp Yuchen Pei made changes -
            ycp Yuchen Pei made changes -
            Fix Version/s 10.4.33 [ 29516 ]
            Fix Version/s 10.4 [ 22408 ]
            Fix Version/s 10.5 [ 23123 ]
            Fix Version/s 10.6 [ 24028 ]
            Fix Version/s 10.9 [ 26905 ]
            Fix Version/s 10.10 [ 27530 ]
            Resolution Fixed [ 1 ]
            Status Open [ 1 ] Closed [ 6 ]
            JIraAutomate JiraAutomate made changes -
            Fix Version/s 10.5.24 [ 29517 ]
            Fix Version/s 10.6.17 [ 29518 ]
            Fix Version/s 10.11.7 [ 29519 ]
            Fix Version/s 11.0.5 [ 29520 ]
            Fix Version/s 11.1.4 [ 29024 ]
            Fix Version/s 11.2.3 [ 29521 ]

            People

              ycp Yuchen Pei
              elenst Elena Stepanova
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

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