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

Syntax error thrown when SELECT executed inside a stored procedure, where a local variable is referenced in the WHERE clause.

    XMLWordPrintable

Details

    • Can result in unexpected behaviour

    Description

      Issue:

      --------------
      Syntax error thrown when SELECT executed inside a stored procedure, where a local variable is referenced in the WHERE clause.

      CALL p(0);
      ERROR 1030 (HY000): Got error 10000 "Unknown error 10000" from storage engine FEDERATED

      Query i.e captured on backend:
      ----------------

      select `m8`.`t1`.`a` AS `a`,`m8`.`t2`.`a` AS `a` from `m8`.`t1` join `m8`.`t2` where `m8`.`t1`.`a` > x@0;
      

      Executing the query on remote server:
      ------------------------

      MariaDB [m8]> select `m8`.`t1`.`a` AS `a`,`m8`.`t2`.`a` AS `a` from `m8`.`t1` join `m8`.`t2` where `m8`.`t1`.`a` > x@0;
      ERROR 1064 (42000): 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 '@0' at line 1
      

      Version used:
      -------------
      Branch : main
      commit: f4a85225ea17b2c77c20e761773090a08aa3eceb
      Version : 13.1.0

      How to repeat:

      Slave:

      create database m8;use m8;
      create user u3@localhost; grant all on *.* to u3@localhost;
      create table t1( a int);
      

      Master: Start server with federated engine enabled

      create table t1(a int)  ENGINE="FEDERATED" CONNECTION='mysql://u3@127.0.0.1:3306/m8/t1';
       
      set global federated_pushdown=ON;
       
      DELIMITER |
      CREATE PROCEDURE p(x int) BEGIN
      select * from t1,t2 WHERE t1.a>x;  
      END|
      DELIMITER ;
       
      CALL p(0);
      

      Attachments

        Activity

          People

            bsrikanth Srikanth Bondalapati
            mariadb-pavithrapandith Pavithra Pandith
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

              Created:
              Updated:

              Time Tracking

                Estimated:
                Original Estimate - 0d
                0d
                Remaining:
                Remaining Estimate - 2d
                2d
                Logged:
                Time Spent - Not Specified
                Not Specified

                Git Integration

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