Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-10142 PL/SQL parser
  3. MDEV-10709

Expressions as parameters to Dynamic SQL

Details

    • 10.2.2-3, 10.2.2-1, 10.2.2-2, 10.2.2-4, 10.1.18

    Description

      MariaDB allows to use only user variables in EXECUTE..USING:

      EXECUTE stmt USING @a;

      Under terms of this task, we'll allow passing expressions as parameters to Dynamic SQL:

      PREPARE stmt FROM 'SELECT ? FROM t1';
      		 
      EXECUTE stmt USING 1+2;

      Note, these expression types should work as output parameters (in addition to user variables):

      • SP variables
      • Trigger NEW and OLD fields

      Note, stored functions and subselects as parameters will not be supported under terms of this task. Using stored functions and subselects would require some additional changes in table locking, SP cache and transaction handling (for the same reason, SET STATEMENT disallows stored functions and subselects as variable values). So the following scripts will return errors:

      PREPARE stmt FROM 'SELECT ? FROM DUAL';
      		 
      EXECUTE stmt USING (SELECT 1);


      CREATE FUNCTION f1() RETURNS VARCHAR(10) RETURN 'test';
      		 
      PREPARE stmt FROM 'SELECT ? FROM DUAL';
      		 
      EXECUTE stmt USING f1();

      Support for stored functions and subselects as parameters (as well as in SET STATEMENT variable values) will be added under terms of a separate task.

      Attachments

        Issue Links

          blocks

          Technical task - A technical task. MDEV-10585 EXECUTE IMMEDIATE statement

          • Major - Major loss of function.
          • Closed
          is blocked by

          Bug - A problem which impairs or prevents the functions of the product. MDEV-10702 Crash in SET STATEMENT FOR EXECUTE

          • Major - Major loss of function.
          • Closed

          Technical task - A technical task. MDEV-10772 Introduce Item_param::CONVERSION_INFO

          • Major - Major loss of function.
          • Closed
          relates to

          Task - A task that needs to be done. MDEV-11360 Dynamic SQL: DEFAULT as a bind parameter

          • Major - Major loss of function.
          • Closed

          Technical task - A technical task. MDEV-10801 sql_mode=ORACLE: Dynamic SQL placeholders

          • Major - Major loss of function.
          • Closed

          Technical task - A technical task. MDEV-11781 sql_mode=ORACLE: IN, OUT, IN OUT modes for dynamic SQL bind arguments

          • Major - Major loss of function.
          • Open

          Bug - A problem which impairs or prevents the functions of the product. MDEV-14270 Dynamic CREATE TABLE does not preserve the data type of SP variables with NULL value

          • Major - Major loss of function.
          • Open

          Bug - A problem which impairs or prevents the functions of the product. MDEV-14271 Dynamic SQL: TIMESTAMP parameter value is not preserved on timezone change

          • Major - Major loss of function.
          • Open

          Activity

            Ascending order - Click to sort in descending order
            bar Alexander Barkov created issue -
            bar Alexander Barkov made changes -
            Field Original Value New Value
            bar Alexander Barkov made changes -
            Summary Pass expressions as parameters to Dynamic SQL Expressions as parameters to Dynamic SQL
            bar Alexander Barkov made changes -
            Description MariaDB allows to use only user variables in {{EXECUTE..USING}}:
            {code:sql}
            EXECUTE stmt USING @a;
            {code}

            Under terms of this task, we'll allow passing expressions as parameters to Dynamic SQL:
            {code:sql}
            PREPARE stmt FROM 'SELECT ? FROM t1';
            EXECUTE stmt USING 1+2;
            {code}
            		MariaDB allows to use only user variables in {{EXECUTE..USING}}:
            {code:sql}
            EXECUTE stmt USING @a;
            {code}

            Under terms of this task, we'll allow passing expressions as parameters to Dynamic SQL:
            {code:sql}
            PREPARE stmt FROM 'SELECT ? FROM t1';
            EXECUTE stmt USING 1+2;
            {code}

            Note, these expression types should work as output parameters (in addition to user variables):
            - SP variables
            - Trigger NEW and OLD fields
            bar Alexander Barkov made changes -
            Status Open [ 1 ] In Progress [ 3 ]
            alvinr Alvin Richards (Inactive) made changes -
            Labels Compatibility
            bar Alexander Barkov made changes -
            Assignee Alexander Barkov [ bar ] Michael Widenius [ monty ]
            Status In Progress [ 3 ] In Review [ 10002 ]
            monty Michael Widenius made changes -
            Assignee Michael Widenius [ monty ] Alexander Barkov [ bar ]
            bar Alexander Barkov made changes -
            bar Alexander Barkov made changes -
            Assignee Alexander Barkov [ bar ] Michael Widenius [ monty ]
            bar Alexander Barkov made changes -
            bar Alexander Barkov made changes -
            bar Alexander Barkov made changes -
            Fix Version/s 10.3.0 [ 22127 ]
            Fix Version/s 10.2 [ 14601 ]
            Assignee Michael Widenius [ monty ] Alexander Barkov [ bar ]
            Resolution Fixed [ 1 ]
            Status In Review [ 10002 ] Closed [ 6 ]
            bar Alexander Barkov made changes -
            Description MariaDB allows to use only user variables in {{EXECUTE..USING}}:
            {code:sql}
            EXECUTE stmt USING @a;
            {code}

            Under terms of this task, we'll allow passing expressions as parameters to Dynamic SQL:
            {code:sql}
            PREPARE stmt FROM 'SELECT ? FROM t1';
            EXECUTE stmt USING 1+2;
            {code}

            Note, these expression types should work as output parameters (in addition to user variables):
            - SP variables
            - Trigger NEW and OLD fields
            		MariaDB allows to use only user variables in {{EXECUTE..USING}}:
            {code:sql}
            EXECUTE stmt USING @a;
            {code}

            Under terms of this task, we'll allow passing expressions as parameters to Dynamic SQL:
            {code:sql}
            PREPARE stmt FROM 'SELECT ? FROM t1';
            EXECUTE stmt USING 1+2;
            {code}

            Note, these expression types should work as output parameters (in addition to user variables):
            - SP variables
            - Trigger NEW and OLD fields

            Note, stored functions and subselects as parameters will not be supported under terms of this task. The following scripts will return errors:
            {code:sql}
            PREPARE stmt FROM 'SELECT ? FROM DUAL';
            EXECUTE stmt USING (SELECT 1);
            {code}
            {code:sql}
            CREATE FUNCTION f1() RETURNS VARCHAR(10) RETURN 'test';
            PREPARE stmt FROM 'SELECT ? FROM DUAL';
            EXECUTE stmt USING f1();
            {code}
            bar Alexander Barkov made changes -
            Description MariaDB allows to use only user variables in {{EXECUTE..USING}}:
            {code:sql}
            EXECUTE stmt USING @a;
            {code}

            Under terms of this task, we'll allow passing expressions as parameters to Dynamic SQL:
            {code:sql}
            PREPARE stmt FROM 'SELECT ? FROM t1';
            EXECUTE stmt USING 1+2;
            {code}

            Note, these expression types should work as output parameters (in addition to user variables):
            - SP variables
            - Trigger NEW and OLD fields

            Note, stored functions and subselects as parameters will not be supported under terms of this task. The following scripts will return errors:
            {code:sql}
            PREPARE stmt FROM 'SELECT ? FROM DUAL';
            EXECUTE stmt USING (SELECT 1);
            {code}
            {code:sql}
            CREATE FUNCTION f1() RETURNS VARCHAR(10) RETURN 'test';
            PREPARE stmt FROM 'SELECT ? FROM DUAL';
            EXECUTE stmt USING f1();
            {code}
            		MariaDB allows to use only user variables in {{EXECUTE..USING}}:
            {code:sql}
            EXECUTE stmt USING @a;
            {code}

            Under terms of this task, we'll allow passing expressions as parameters to Dynamic SQL:
            {code:sql}
            PREPARE stmt FROM 'SELECT ? FROM t1';
            EXECUTE stmt USING 1+2;
            {code}

            Note, these expression types should work as output parameters (in addition to user variables):
            - SP variables
            - Trigger NEW and OLD fields

            Note, stored functions and subselects as parameters will not be supported under terms of this task. The following scripts will return errors:
            {code:sql}
            PREPARE stmt FROM 'SELECT ? FROM DUAL';
            EXECUTE stmt USING (SELECT 1);
            {code}
            {code:sql}
            CREATE FUNCTION f1() RETURNS VARCHAR(10) RETURN 'test';
            PREPARE stmt FROM 'SELECT ? FROM DUAL';
            EXECUTE stmt USING f1();
            {code}
            Support for stored functions and subselects as parameters (as well as for {{SET STATEMENT}} parameters) will be added under terms of a separate task.
            bar Alexander Barkov made changes -
            Description MariaDB allows to use only user variables in {{EXECUTE..USING}}:
            {code:sql}
            EXECUTE stmt USING @a;
            {code}

            Under terms of this task, we'll allow passing expressions as parameters to Dynamic SQL:
            {code:sql}
            PREPARE stmt FROM 'SELECT ? FROM t1';
            EXECUTE stmt USING 1+2;
            {code}

            Note, these expression types should work as output parameters (in addition to user variables):
            - SP variables
            - Trigger NEW and OLD fields

            Note, stored functions and subselects as parameters will not be supported under terms of this task. The following scripts will return errors:
            {code:sql}
            PREPARE stmt FROM 'SELECT ? FROM DUAL';
            EXECUTE stmt USING (SELECT 1);
            {code}
            {code:sql}
            CREATE FUNCTION f1() RETURNS VARCHAR(10) RETURN 'test';
            PREPARE stmt FROM 'SELECT ? FROM DUAL';
            EXECUTE stmt USING f1();
            {code}
            Support for stored functions and subselects as parameters (as well as for {{SET STATEMENT}} parameters) will be added under terms of a separate task.
            		MariaDB allows to use only user variables in {{EXECUTE..USING}}:
            {code:sql}
            EXECUTE stmt USING @a;
            {code}

            Under terms of this task, we'll allow passing expressions as parameters to Dynamic SQL:
            {code:sql}
            PREPARE stmt FROM 'SELECT ? FROM t1';
            EXECUTE stmt USING 1+2;
            {code}

            Note, these expression types should work as output parameters (in addition to user variables):
            - SP variables
            - Trigger NEW and OLD fields

            Note, stored functions and subselects as parameters will not be supported under terms of this task. The following scripts will return errors:
            {code:sql}
            PREPARE stmt FROM 'SELECT ? FROM DUAL';
            EXECUTE stmt USING (SELECT 1);
            {code}
            {code:sql}
            CREATE FUNCTION f1() RETURNS VARCHAR(10) RETURN 'test';
            PREPARE stmt FROM 'SELECT ? FROM DUAL';
            EXECUTE stmt USING f1();
            {code}
            Support for stored functions and subselects as parameters (as well as in {{SET STATEMENT}} variable values) will be added under terms of a separate task.
            bar Alexander Barkov made changes -
            Description MariaDB allows to use only user variables in {{EXECUTE..USING}}:
            {code:sql}
            EXECUTE stmt USING @a;
            {code}

            Under terms of this task, we'll allow passing expressions as parameters to Dynamic SQL:
            {code:sql}
            PREPARE stmt FROM 'SELECT ? FROM t1';
            EXECUTE stmt USING 1+2;
            {code}

            Note, these expression types should work as output parameters (in addition to user variables):
            - SP variables
            - Trigger NEW and OLD fields

            Note, stored functions and subselects as parameters will not be supported under terms of this task. The following scripts will return errors:
            {code:sql}
            PREPARE stmt FROM 'SELECT ? FROM DUAL';
            EXECUTE stmt USING (SELECT 1);
            {code}
            {code:sql}
            CREATE FUNCTION f1() RETURNS VARCHAR(10) RETURN 'test';
            PREPARE stmt FROM 'SELECT ? FROM DUAL';
            EXECUTE stmt USING f1();
            {code}
            Support for stored functions and subselects as parameters (as well as in {{SET STATEMENT}} variable values) will be added under terms of a separate task.
            		MariaDB allows to use only user variables in {{EXECUTE..USING}}:
            {code:sql}
            EXECUTE stmt USING @a;
            {code}

            Under terms of this task, we'll allow passing expressions as parameters to Dynamic SQL:
            {code:sql}
            PREPARE stmt FROM 'SELECT ? FROM t1';
            EXECUTE stmt USING 1+2;
            {code}

            Note, these expression types should work as output parameters (in addition to user variables):
            - SP variables
            - Trigger NEW and OLD fields

            Note, stored functions and subselects as parameters will not be supported under terms of this task. Using stored functions and subselects would require some additional changes in table locking, SP cache and transaction handling. Note, for the same reason, {{SET STATEMENT}} disallows stored functions and subselects as variable values. The following scripts will return errors:
            {code:sql}
            PREPARE stmt FROM 'SELECT ? FROM DUAL';
            EXECUTE stmt USING (SELECT 1);
            {code}
            {code:sql}
            CREATE FUNCTION f1() RETURNS VARCHAR(10) RETURN 'test';
            PREPARE stmt FROM 'SELECT ? FROM DUAL';
            EXECUTE stmt USING f1();
            {code}
            Support for stored functions and subselects as parameters (as well as in {{SET STATEMENT}} variable values) will be added under terms of a separate task.
            bar Alexander Barkov made changes -
            Description MariaDB allows to use only user variables in {{EXECUTE..USING}}:
            {code:sql}
            EXECUTE stmt USING @a;
            {code}

            Under terms of this task, we'll allow passing expressions as parameters to Dynamic SQL:
            {code:sql}
            PREPARE stmt FROM 'SELECT ? FROM t1';
            EXECUTE stmt USING 1+2;
            {code}

            Note, these expression types should work as output parameters (in addition to user variables):
            - SP variables
            - Trigger NEW and OLD fields

            Note, stored functions and subselects as parameters will not be supported under terms of this task. Using stored functions and subselects would require some additional changes in table locking, SP cache and transaction handling. Note, for the same reason, {{SET STATEMENT}} disallows stored functions and subselects as variable values. The following scripts will return errors:
            {code:sql}
            PREPARE stmt FROM 'SELECT ? FROM DUAL';
            EXECUTE stmt USING (SELECT 1);
            {code}
            {code:sql}
            CREATE FUNCTION f1() RETURNS VARCHAR(10) RETURN 'test';
            PREPARE stmt FROM 'SELECT ? FROM DUAL';
            EXECUTE stmt USING f1();
            {code}
            Support for stored functions and subselects as parameters (as well as in {{SET STATEMENT}} variable values) will be added under terms of a separate task.
            		MariaDB allows to use only user variables in {{EXECUTE..USING}}:
            {code:sql}
            EXECUTE stmt USING @a;
            {code}

            Under terms of this task, we'll allow passing expressions as parameters to Dynamic SQL:
            {code:sql}
            PREPARE stmt FROM 'SELECT ? FROM t1';
            EXECUTE stmt USING 1+2;
            {code}

            Note, these expression types should work as output parameters (in addition to user variables):
            - SP variables
            - Trigger NEW and OLD fields

            Note, stored functions and subselects as parameters will not be supported under terms of this task. Using stored functions and subselects would require some additional changes in table locking, SP cache and transaction handling (for the same reason, {{SET STATEMENT}} disallows stored functions and subselects as variable values). So the following scripts will return errors:
            {code:sql}
            PREPARE stmt FROM 'SELECT ? FROM DUAL';
            EXECUTE stmt USING (SELECT 1);
            {code}
            {code:sql}
            CREATE FUNCTION f1() RETURNS VARCHAR(10) RETURN 'test';
            PREPARE stmt FROM 'SELECT ? FROM DUAL';
            EXECUTE stmt USING f1();
            {code}
            Support for stored functions and subselects as parameters (as well as in {{SET STATEMENT}} variable values) will be added under terms of a separate task.
            bar Alexander Barkov made changes -
            Fix Version/s 10.2.3 [ 22115 ]
            alvinr Alvin Richards (Inactive) made changes -
            Labels Compatibility Compatibility NRE-307517
            bar Alexander Barkov made changes -
            alvinr Alvin Richards (Inactive) made changes -
            Labels Compatibility NRE-307517 Approved Compatibility NRE-307517
            alvinr Alvin Richards (Inactive) made changes -
            NRE Projects NRE-307517
            alvinr Alvin Richards (Inactive) made changes -
            Labels Approved Compatibility NRE-307517 Approved Compatibility
            alvinr Alvin Richards (Inactive) made changes -
            NRE Approved Yes [ 10304 ]
            alvinr Alvin Richards (Inactive) made changes -
            Labels Approved Compatibility Compatibility
            bar Alexander Barkov made changes -
            bar Alexander Barkov made changes -
            Component/s Parser [ 10201 ]
            bar Alexander Barkov made changes -
            Component/s Prepared Statements [ 10804 ]
            Component/s OTHER [ 10125 ]
            bar Alexander Barkov made changes -
            bar Alexander Barkov made changes -
            serg Sergei Golubchik made changes -
            Workflow MariaDB v3 [ 76864 ] MariaDB v4 [ 150846 ]

            People

              bar Alexander Barkov
              bar Alexander Barkov
              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.