[MDEV-16020] SP variables inside GROUP BY..WITH ROLLUP break replication - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Major
Resolution: Fixed
Affects Version/s: 10.0(EOL), 10.1(EOL), 10.2(EOL), 10.3(EOL)
Fix Version/s: 10.3.7
Component/s: Parser, Replication
Labels:
None

Description

SP variable substitution does not work well in queries with the WITH ROLLUP clause.
As a result, a bad binary log is created and replication breaks.

I put this file into mysql-test/t/ directory:

--source include/not_embedded.inc

--source include/have_binlog_format_statement.inc

--disable_query_log

reset master; # get rid of previous tests binlog

--enable_query_log

CREATE TABLE t1 (d DATE);

INSERT INTO t1 VALUES ('1985-05-13'),('1989-12-24');

CREATE TABLE t2 (d DATE, c BIGINT);

DELIMITER $$;

BEGIN NOT ATOMIC

  DECLARE var INT;

  INSERT INTO t2 SELECT d, COUNT(*) FROM t1 GROUP BY d, var;

  INSERT INTO t2 SELECT d, COUNT(*) FROM t1 GROUP BY d, var WITH ROLLUP;

END;

$$

DELIMITER ;$$

DROP TABLE t1,t2;

--let $binlog_file = LAST

source include/show_binlog_events.inc;

and run "mtr" on it.

It produced the following output:

TEST                                      RESULT   TIME (ms) or COMMENT

--------------------------------------------------------------------------

worker[1] Using MTR_BUILD_THREAD 300, with reserved ports 16000..16019

CREATE TABLE t1 (d DATE);

INSERT INTO t1 VALUES ('1985-05-13'),('1989-12-24');

CREATE TABLE t2 (d DATE, c BIGINT);

BEGIN NOT ATOMIC

DECLARE var INT;

INSERT INTO t2 SELECT d, COUNT(*) FROM t1 GROUP BY d, var;

INSERT INTO t2 SELECT d, COUNT(*) FROM t1 GROUP BY d, var WITH ROLLUP;

END;

$$

DROP TABLE t1,t2;

include/show_binlog_events.inc

Log_name	Pos	Event_type	Server_id	End_log_pos	Info

master-bin.000001	#	Gtid	#	#	GTID #-#-#

master-bin.000001	#	Query	#	#	use `test`; CREATE TABLE t1 (d DATE)

master-bin.000001	#	Gtid	#	#	BEGIN GTID #-#-#

master-bin.000001	#	Query	#	#	use `test`; INSERT INTO t1 VALUES ('1985-05-13'),('1989-12-24')

master-bin.000001	#	Query	#	#	COMMIT

master-bin.000001	#	Gtid	#	#	GTID #-#-#

master-bin.000001	#	Query	#	#	use `test`; CREATE TABLE t2 (d DATE, c BIGINT)

master-bin.000001	#	Gtid	#	#	BEGIN GTID #-#-#

master-bin.000001	#	Query	#	#	use `test`; INSERT INTO t2 SELECT d, COUNT(*) FROM t1 GROUP BY d,  NAME_CONST('var',NULL)

master-bin.000001	#	Query	#	#	COMMIT

master-bin.000001	#	Gtid	#	#	BEGIN GTID #-#-#

master-bin.000001	#	Query	#	#	use `test`; INSERT INTO t2 SELECT d, COUNT(*) FROM t1 GROUP BY d, var  NAME_CONST('var',NULL) ROLLUP

master-bin.000001	#	Query	#	#	COMMIT

master-bin.000001	#	Gtid	#	#	GTID #-#-#

master-bin.000001	#	Query	#	#	use `test`; DROP TABLE `t1`,`t2` /* generated by server */

main.AAA                                 [ pass ]     54

--------------------------------------------------------------------------

Notice:

in the first INSERT query the variable var was correctly replaced to the NAME_CONST expression:
... GROUP BY d, NAME_CONST('var',NULL)
in the second INSERT query the variable was not removed and the NAME_CONST expression was put on a wrong place.
... GROUP BY d, var NAME_CONST('var',NULL) ROLLUP

The logged query for the second INSERT does not even get parsed:

MariaDB [test]> INSERT INTO t2 SELECT d, COUNT(*) FROM t1 GROUP BY d, var  NAME_CONST('var',NULL) ROLLUP;

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 'NAME_CONST('var',NULL) ROLLUP' at line 1

Attachments

Issue Links

relates to

MDEV-16095 Oracle-style placeholder inside GROUP BY..WITH ROLLUP breaks replication

Closed

MDEV-16117 SP with a single FOR statement creates but further fails to load

Closed

Activity

Ascending order - Click to sort in descending order

Alexander Barkov created issue - 2018-04-25 11:06

Alexander Barkov made changes - 2018-04-25 11:07

Field	Original Value	New Value
Description	SP variable substitution does not work well in queries with the {{WITH ROLLUP}} clause. As a result, a bad binary log is created and replication breaks. I put this file into mysql-test/t/ directory: {noformat} --source include/not_embedded.inc --source include/have_binlog_format_statement.inc --disable_query_log reset master; # get rid of previous tests binlog --enable_query_log CREATE TABLE t1 (d DATE); INSERT INTO t1 VALUES ('1985-05-13'),('1989-12-24'); CREATE TABLE t2 (d DATE, c BIGINT); DELIMITER $$; BEGIN NOT ATOMIC DECLARE var INT; INSERT INTO t2 SELECT d, COUNT() FROM t1 GROUP BY d, var; INSERT INTO t2 SELECT d, COUNT() FROM t1 GROUP BY d, var WITH ROLLUP; END; $$ DELIMITER ;$$ DROP TABLE t1,t2; --let $binlog_file = LAST source include/show_binlog_events.inc; {noformat} and run "mtr" on it. It produced the following output: {noformat} TEST RESULT TIME (ms) or COMMENT -------------------------------------------------------------------------- worker[1] Using MTR_BUILD_THREAD 300, with reserved ports 16000..16019 CREATE TABLE t1 (d DATE); INSERT INTO t1 VALUES ('1985-05-13'),('1989-12-24'); CREATE TABLE t2 (d DATE, c BIGINT); BEGIN NOT ATOMIC DECLARE var INT; INSERT INTO t2 SELECT d, COUNT() FROM t1 GROUP BY d, var; INSERT INTO t2 SELECT d, COUNT() FROM t1 GROUP BY d, var WITH ROLLUP; END; $$ DROP TABLE t1,t2; include/show_binlog_events.inc Log_name Pos Event_type Server_id End_log_pos Info master-bin.000001 # Gtid # # GTID #-#-# master-bin.000001 # Query # # use `test`; CREATE TABLE t1 (d DATE) master-bin.000001 # Gtid # # BEGIN GTID #-#-# master-bin.000001 # Query # # use `test`; INSERT INTO t1 VALUES ('1985-05-13'),('1989-12-24') master-bin.000001 # Query # # COMMIT master-bin.000001 # Gtid # # GTID #-#-# master-bin.000001 # Query # # use `test`; CREATE TABLE t2 (d DATE, c BIGINT) master-bin.000001 # Gtid # # BEGIN GTID #-#-# master-bin.000001 # Query # # use `test`; INSERT INTO t2 SELECT d, COUNT() FROM t1 GROUP BY d, NAME_CONST('var',NULL) master-bin.000001 # Query # # COMMIT master-bin.000001 # Gtid # # BEGIN GTID #-#-# master-bin.000001 # Query # # use `test`; INSERT INTO t2 SELECT d, COUNT() FROM t1 GROUP BY d, var NAME_CONST('var',NULL) ROLLUP master-bin.000001 # Query # # COMMIT master-bin.000001 # Gtid # # GTID #-#-# master-bin.000001 # Query # # use `test`; DROP TABLE `t1`,`t2` /* generated by server / main.AAA [ pass ] 54 -------------------------------------------------------------------------- {noformat} Notice: - in the first query the variable {{var}} was correctly replaced to the {{NAME_CONST}} expression - in the second query the variable was not removed and the {{NAME_CONST}} expression was put on a wrong place. This modified query does not even get parsed: {noformat} MariaDB [test]> INSERT INTO t2 SELECT d, COUNT() FROM t1 GROUP BY d, var NAME_CONST('var',NULL) ROLLUP; 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 'NAME_CONST('var',NULL) ROLLUP' at line 1 {noformat}	SP variable substitution does not work well in queries with the {{WITH ROLLUP}} clause. As a result, a bad binary log is created and replication breaks. I put this file into mysql-test/t/ directory: {noformat} --source include/not_embedded.inc --source include/have_binlog_format_statement.inc --disable_query_log reset master; # get rid of previous tests binlog --enable_query_log CREATE TABLE t1 (d DATE); INSERT INTO t1 VALUES ('1985-05-13'),('1989-12-24'); CREATE TABLE t2 (d DATE, c BIGINT); DELIMITER $$; BEGIN NOT ATOMIC DECLARE var INT; INSERT INTO t2 SELECT d, COUNT() FROM t1 GROUP BY d, var; INSERT INTO t2 SELECT d, COUNT() FROM t1 GROUP BY d, var WITH ROLLUP; END; $$ DELIMITER ;$$ DROP TABLE t1,t2; --let $binlog_file = LAST source include/show_binlog_events.inc; {noformat} and run "mtr" on it. It produced the following output: {noformat} TEST RESULT TIME (ms) or COMMENT -------------------------------------------------------------------------- worker[1] Using MTR_BUILD_THREAD 300, with reserved ports 16000..16019 CREATE TABLE t1 (d DATE); INSERT INTO t1 VALUES ('1985-05-13'),('1989-12-24'); CREATE TABLE t2 (d DATE, c BIGINT); BEGIN NOT ATOMIC DECLARE var INT; INSERT INTO t2 SELECT d, COUNT() FROM t1 GROUP BY d, var; INSERT INTO t2 SELECT d, COUNT() FROM t1 GROUP BY d, var WITH ROLLUP; END; $$ DROP TABLE t1,t2; include/show_binlog_events.inc Log_name Pos Event_type Server_id End_log_pos Info master-bin.000001 # Gtid # # GTID #-#-# master-bin.000001 # Query # # use `test`; CREATE TABLE t1 (d DATE) master-bin.000001 # Gtid # # BEGIN GTID #-#-# master-bin.000001 # Query # # use `test`; INSERT INTO t1 VALUES ('1985-05-13'),('1989-12-24') master-bin.000001 # Query # # COMMIT master-bin.000001 # Gtid # # GTID #-#-# master-bin.000001 # Query # # use `test`; CREATE TABLE t2 (d DATE, c BIGINT) master-bin.000001 # Gtid # # BEGIN GTID #-#-# master-bin.000001 # Query # # use `test`; INSERT INTO t2 SELECT d, COUNT() FROM t1 GROUP BY d, NAME_CONST('var',NULL) master-bin.000001 # Query # # COMMIT master-bin.000001 # Gtid # # BEGIN GTID #-#-# master-bin.000001 # Query # # use `test`; INSERT INTO t2 SELECT d, COUNT() FROM t1 GROUP BY d, var NAME_CONST('var',NULL) ROLLUP master-bin.000001 # Query # # COMMIT master-bin.000001 # Gtid # # GTID #-#-# master-bin.000001 # Query # # use `test`; DROP TABLE `t1`,`t2` /* generated by server / main.AAA [ pass ] 54 -------------------------------------------------------------------------- {noformat} Notice: - in the first {{INSERT}} query the variable {{var}} was correctly replaced to the {{NAME_CONST}} expression - in the second {{INSERT}} query the variable was not removed and the {{NAME_CONST}} expression was put on a wrong place. This modified query does not even get parsed: {noformat} MariaDB [test]> INSERT INTO t2 SELECT d, COUNT() FROM t1 GROUP BY d, var NAME_CONST('var',NULL) ROLLUP; 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 'NAME_CONST('var',NULL) ROLLUP' at line 1 {noformat}

Alexander Barkov made changes - 2018-04-25 11:08

Description

SP variable substitution does not work well in queries with the {{WITH ROLLUP}} clause.
As a result, a bad binary log is created and replication breaks.

I put this file into mysql-test/t/ directory:

{noformat}
--source include/not_embedded.inc
--source include/have_binlog_format_statement.inc

--disable_query_log
reset master; # get rid of previous tests binlog
--enable_query_log

CREATE TABLE t1 (d DATE);
INSERT INTO t1 VALUES ('1985-05-13'),('1989-12-24');
CREATE TABLE t2 (d DATE, c BIGINT);
DELIMITER $$;
BEGIN NOT ATOMIC
  DECLARE var INT;
  INSERT INTO t2 SELECT d, COUNT(*) FROM t1 GROUP BY d, var;
  INSERT INTO t2 SELECT d, COUNT(*) FROM t1 GROUP BY d, var WITH ROLLUP;
END;
$$
DELIMITER ;$$
DROP TABLE t1,t2;

--let $binlog_file = LAST
source include/show_binlog_events.inc;
{noformat}

and run "mtr" on it.

It produced the following output:
{noformat}
TEST RESULT TIME (ms) or COMMENT
--------------------------------------------------------------------------

worker[1] Using MTR_BUILD_THREAD 300, with reserved ports 16000..16019
CREATE TABLE t1 (d DATE);
INSERT INTO t1 VALUES ('1985-05-13'),('1989-12-24');
CREATE TABLE t2 (d DATE, c BIGINT);
BEGIN NOT ATOMIC
DECLARE var INT;
INSERT INTO t2 SELECT d, COUNT(*) FROM t1 GROUP BY d, var;
INSERT INTO t2 SELECT d, COUNT(*) FROM t1 GROUP BY d, var WITH ROLLUP;
END;
$$
DROP TABLE t1,t2;
include/show_binlog_events.inc
Log_name Pos Event_type Server_id End_log_pos Info
master-bin.000001 # Gtid # # GTID #-#-#
master-bin.000001 # Query # # use `test`; CREATE TABLE t1 (d DATE)
master-bin.000001 # Gtid # # BEGIN GTID #-#-#
master-bin.000001 # Query # # use `test`; INSERT INTO t1 VALUES ('1985-05-13'),('1989-12-24')
master-bin.000001 # Query # # COMMIT
master-bin.000001 # Gtid # # GTID #-#-#
master-bin.000001 # Query # # use `test`; CREATE TABLE t2 (d DATE, c BIGINT)
master-bin.000001 # Gtid # # BEGIN GTID #-#-#
master-bin.000001 # Query # # use `test`; INSERT INTO t2 SELECT d, COUNT(*) FROM t1 GROUP BY d, NAME_CONST('var',NULL)
master-bin.000001 # Query # # COMMIT
master-bin.000001 # Gtid # # BEGIN GTID #-#-#
master-bin.000001 # Query # # use `test`; INSERT INTO t2 SELECT d, COUNT(*) FROM t1 GROUP BY d, var NAME_CONST('var',NULL) ROLLUP
master-bin.000001 # Query # # COMMIT
master-bin.000001 # Gtid # # GTID #-#-#
master-bin.000001 # Query # # use `test`; DROP TABLE `t1`,`t2` /* generated by server */
main.AAA [ pass ] 54
--------------------------------------------------------------------------
{noformat}

Notice:
- in the first {{INSERT}} query the variable {{var}} was correctly replaced to the {{NAME_CONST}} expression
- in the second {{INSERT}} query the variable was not removed and the {{NAME_CONST}} expression was put on a wrong place.

This modified query does not even get parsed:
{noformat}
MariaDB [test]> INSERT INTO t2 SELECT d, COUNT(*) FROM t1 GROUP BY d, var NAME_CONST('var',NULL) ROLLUP;
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 'NAME_CONST('var',NULL) ROLLUP' at line 1
{noformat}

SP variable substitution does not work well in queries with the {{WITH ROLLUP}} clause.
As a result, a bad binary log is created and replication breaks.

I put this file into mysql-test/t/ directory:

{noformat}
--source include/not_embedded.inc
--source include/have_binlog_format_statement.inc

--disable_query_log
reset master; # get rid of previous tests binlog
--enable_query_log

CREATE TABLE t1 (d DATE);
INSERT INTO t1 VALUES ('1985-05-13'),('1989-12-24');
CREATE TABLE t2 (d DATE, c BIGINT);
DELIMITER $$;
BEGIN NOT ATOMIC
  DECLARE var INT;
  INSERT INTO t2 SELECT d, COUNT(*) FROM t1 GROUP BY d, var;
  INSERT INTO t2 SELECT d, COUNT(*) FROM t1 GROUP BY d, var WITH ROLLUP;
END;
$$
DELIMITER ;$$
DROP TABLE t1,t2;

--let $binlog_file = LAST
source include/show_binlog_events.inc;
{noformat}

and run "mtr" on it.

It produced the following output:
{noformat}
TEST RESULT TIME (ms) or COMMENT
--------------------------------------------------------------------------

worker[1] Using MTR_BUILD_THREAD 300, with reserved ports 16000..16019
CREATE TABLE t1 (d DATE);
INSERT INTO t1 VALUES ('1985-05-13'),('1989-12-24');
CREATE TABLE t2 (d DATE, c BIGINT);
BEGIN NOT ATOMIC
DECLARE var INT;
INSERT INTO t2 SELECT d, COUNT(*) FROM t1 GROUP BY d, var;
INSERT INTO t2 SELECT d, COUNT(*) FROM t1 GROUP BY d, var WITH ROLLUP;
END;
$$
DROP TABLE t1,t2;
include/show_binlog_events.inc
Log_name Pos Event_type Server_id End_log_pos Info
master-bin.000001 # Gtid # # GTID #-#-#
master-bin.000001 # Query # # use `test`; CREATE TABLE t1 (d DATE)
master-bin.000001 # Gtid # # BEGIN GTID #-#-#
master-bin.000001 # Query # # use `test`; INSERT INTO t1 VALUES ('1985-05-13'),('1989-12-24')
master-bin.000001 # Query # # COMMIT
master-bin.000001 # Gtid # # GTID #-#-#
master-bin.000001 # Query # # use `test`; CREATE TABLE t2 (d DATE, c BIGINT)
master-bin.000001 # Gtid # # BEGIN GTID #-#-#
master-bin.000001 # Query # # use `test`; INSERT INTO t2 SELECT d, COUNT(*) FROM t1 GROUP BY d, NAME_CONST('var',NULL)
master-bin.000001 # Query # # COMMIT
master-bin.000001 # Gtid # # BEGIN GTID #-#-#
master-bin.000001 # Query # # use `test`; INSERT INTO t2 SELECT d, COUNT(*) FROM t1 GROUP BY d, var NAME_CONST('var',NULL) ROLLUP
master-bin.000001 # Query # # COMMIT
master-bin.000001 # Gtid # # GTID #-#-#
master-bin.000001 # Query # # use `test`; DROP TABLE `t1`,`t2` /* generated by server */
main.AAA [ pass ] 54
--------------------------------------------------------------------------
{noformat}

Notice:
- in the first {{INSERT}} query the variable {{var}} was correctly replaced to the {{NAME_CONST}} expression:
{noformat}
... GROUP BY d, NAME_CONST('var',NULL)
{noformat}
- in the second {{INSERT}} query the variable was not removed and the {{NAME_CONST}} expression was put on a wrong place.
{noformat}
... GROUP BY d, var NAME_CONST('var',NULL) ROLLUP
{noformat}

This modified query does not even get parsed:
{noformat}
MariaDB [test]> INSERT INTO t2 SELECT d, COUNT(*) FROM t1 GROUP BY d, var NAME_CONST('var',NULL) ROLLUP;
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 'NAME_CONST('var',NULL) ROLLUP' at line 1
{noformat}

Alexander Barkov made changes - 2018-04-25 11:09

Description

SP variable substitution does not work well in queries with the {{WITH ROLLUP}} clause.
As a result, a bad binary log is created and replication breaks.

I put this file into mysql-test/t/ directory:

{noformat}
--source include/not_embedded.inc
--source include/have_binlog_format_statement.inc

--disable_query_log
reset master; # get rid of previous tests binlog
--enable_query_log

CREATE TABLE t1 (d DATE);
INSERT INTO t1 VALUES ('1985-05-13'),('1989-12-24');
CREATE TABLE t2 (d DATE, c BIGINT);
DELIMITER $$;
BEGIN NOT ATOMIC
  DECLARE var INT;
  INSERT INTO t2 SELECT d, COUNT(*) FROM t1 GROUP BY d, var;
  INSERT INTO t2 SELECT d, COUNT(*) FROM t1 GROUP BY d, var WITH ROLLUP;
END;
$$
DELIMITER ;$$
DROP TABLE t1,t2;

--let $binlog_file = LAST
source include/show_binlog_events.inc;
{noformat}

and run "mtr" on it.

It produced the following output:
{noformat}
TEST RESULT TIME (ms) or COMMENT
--------------------------------------------------------------------------

worker[1] Using MTR_BUILD_THREAD 300, with reserved ports 16000..16019
CREATE TABLE t1 (d DATE);
INSERT INTO t1 VALUES ('1985-05-13'),('1989-12-24');
CREATE TABLE t2 (d DATE, c BIGINT);
BEGIN NOT ATOMIC
DECLARE var INT;
INSERT INTO t2 SELECT d, COUNT(*) FROM t1 GROUP BY d, var;
INSERT INTO t2 SELECT d, COUNT(*) FROM t1 GROUP BY d, var WITH ROLLUP;
END;
$$
DROP TABLE t1,t2;
include/show_binlog_events.inc
Log_name Pos Event_type Server_id End_log_pos Info
master-bin.000001 # Gtid # # GTID #-#-#
master-bin.000001 # Query # # use `test`; CREATE TABLE t1 (d DATE)
master-bin.000001 # Gtid # # BEGIN GTID #-#-#
master-bin.000001 # Query # # use `test`; INSERT INTO t1 VALUES ('1985-05-13'),('1989-12-24')
master-bin.000001 # Query # # COMMIT
master-bin.000001 # Gtid # # GTID #-#-#
master-bin.000001 # Query # # use `test`; CREATE TABLE t2 (d DATE, c BIGINT)
master-bin.000001 # Gtid # # BEGIN GTID #-#-#
master-bin.000001 # Query # # use `test`; INSERT INTO t2 SELECT d, COUNT(*) FROM t1 GROUP BY d, NAME_CONST('var',NULL)
master-bin.000001 # Query # # COMMIT
master-bin.000001 # Gtid # # BEGIN GTID #-#-#
master-bin.000001 # Query # # use `test`; INSERT INTO t2 SELECT d, COUNT(*) FROM t1 GROUP BY d, var NAME_CONST('var',NULL) ROLLUP
master-bin.000001 # Query # # COMMIT
master-bin.000001 # Gtid # # GTID #-#-#
master-bin.000001 # Query # # use `test`; DROP TABLE `t1`,`t2` /* generated by server */
main.AAA [ pass ] 54
--------------------------------------------------------------------------
{noformat}

Notice:
- in the first {{INSERT}} query the variable {{var}} was correctly replaced to the {{NAME_CONST}} expression:
{noformat}
... GROUP BY d, NAME_CONST('var',NULL)
{noformat}
- in the second {{INSERT}} query the variable was not removed and the {{NAME_CONST}} expression was put on a wrong place.
{noformat}
... GROUP BY d, var NAME_CONST('var',NULL) ROLLUP
{noformat}

This modified query does not even get parsed:
{noformat}
MariaDB [test]> INSERT INTO t2 SELECT d, COUNT(*) FROM t1 GROUP BY d, var NAME_CONST('var',NULL) ROLLUP;
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 'NAME_CONST('var',NULL) ROLLUP' at line 1
{noformat}

SP variable substitution does not work well in queries with the {{WITH ROLLUP}} clause.
As a result, a bad binary log is created and replication breaks.

I put this file into mysql-test/t/ directory:

{noformat}
--source include/not_embedded.inc
--source include/have_binlog_format_statement.inc

--disable_query_log
reset master; # get rid of previous tests binlog
--enable_query_log

CREATE TABLE t1 (d DATE);
INSERT INTO t1 VALUES ('1985-05-13'),('1989-12-24');
CREATE TABLE t2 (d DATE, c BIGINT);
DELIMITER $$;
BEGIN NOT ATOMIC
  DECLARE var INT;
  INSERT INTO t2 SELECT d, COUNT(*) FROM t1 GROUP BY d, var;
  INSERT INTO t2 SELECT d, COUNT(*) FROM t1 GROUP BY d, var WITH ROLLUP;
END;
$$
DELIMITER ;$$
DROP TABLE t1,t2;

--let $binlog_file = LAST
source include/show_binlog_events.inc;
{noformat}

and run "mtr" on it.

It produced the following output:
{noformat}
TEST RESULT TIME (ms) or COMMENT
--------------------------------------------------------------------------

worker[1] Using MTR_BUILD_THREAD 300, with reserved ports 16000..16019
CREATE TABLE t1 (d DATE);
INSERT INTO t1 VALUES ('1985-05-13'),('1989-12-24');
CREATE TABLE t2 (d DATE, c BIGINT);
BEGIN NOT ATOMIC
DECLARE var INT;
INSERT INTO t2 SELECT d, COUNT(*) FROM t1 GROUP BY d, var;
INSERT INTO t2 SELECT d, COUNT(*) FROM t1 GROUP BY d, var WITH ROLLUP;
END;
$$
DROP TABLE t1,t2;
include/show_binlog_events.inc
Log_name Pos Event_type Server_id End_log_pos Info
master-bin.000001 # Gtid # # GTID #-#-#
master-bin.000001 # Query # # use `test`; CREATE TABLE t1 (d DATE)
master-bin.000001 # Gtid # # BEGIN GTID #-#-#
master-bin.000001 # Query # # use `test`; INSERT INTO t1 VALUES ('1985-05-13'),('1989-12-24')
master-bin.000001 # Query # # COMMIT
master-bin.000001 # Gtid # # GTID #-#-#
master-bin.000001 # Query # # use `test`; CREATE TABLE t2 (d DATE, c BIGINT)
master-bin.000001 # Gtid # # BEGIN GTID #-#-#
master-bin.000001 # Query # # use `test`; INSERT INTO t2 SELECT d, COUNT(*) FROM t1 GROUP BY d, NAME_CONST('var',NULL)
master-bin.000001 # Query # # COMMIT
master-bin.000001 # Gtid # # BEGIN GTID #-#-#
master-bin.000001 # Query # # use `test`; INSERT INTO t2 SELECT d, COUNT(*) FROM t1 GROUP BY d, var NAME_CONST('var',NULL) ROLLUP
master-bin.000001 # Query # # COMMIT
master-bin.000001 # Gtid # # GTID #-#-#
master-bin.000001 # Query # # use `test`; DROP TABLE `t1`,`t2` /* generated by server */
main.AAA [ pass ] 54
--------------------------------------------------------------------------
{noformat}

Notice:
- in the first {{INSERT}} query the variable {{var}} was correctly replaced to the {{NAME_CONST}} expression:
{noformat}
... GROUP BY d, NAME_CONST('var',NULL)
{noformat}
- in the second {{INSERT}} query the variable was not removed and the {{NAME_CONST}} expression was put on a wrong place.
{noformat}
... GROUP BY d, var NAME_CONST('var',NULL) ROLLUP
{noformat}

The logged query for the second {{INSERT}} does not even get parsed:
{noformat}
MariaDB [test]> INSERT INTO t2 SELECT d, COUNT(*) FROM t1 GROUP BY d, var NAME_CONST('var',NULL) ROLLUP;
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 'NAME_CONST('var',NULL) ROLLUP' at line 1
{noformat}

Alexander Barkov made changes - 2018-04-27 18:12

issue.field.resolutiondate

2018-04-27 18:12:18.0

2018-04-27 18:12:18.441

Alexander Barkov made changes - 2018-04-27 18:12

Fix Version/s		10.3.7 [ 23005 ]
Fix Version/s	10.3 [ 22126 ]
Resolution		Fixed [ 1 ]
Status	Open [ 1 ]	Closed [ 6 ]

Alexander Barkov made changes - 2018-05-06 09:08

Link

This issue relates to ~~MDEV-16095~~ [ ~~MDEV-16095~~ ]

Alexander Barkov made changes - 2018-05-08 06:16

Link

This issue relates to ~~MDEV-16117~~ [ ~~MDEV-16117~~ ]

Sergei Golubchik made changes - 2021-12-06 21:47

Workflow

MariaDB v3 [ 86783 ]

MariaDB v4 [ 154250 ]

People

Assignee:: Alexander Barkov

Reporter:: Alexander Barkov

Votes:: 0 Vote for this issue

Watchers:: 1 Start watching this issue

Dates

Created:: 2018-04-25 11:06

Updated:: 2018-05-08 06:16

Resolved:: 2018-04-27 18:12

Git Integration

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

MariaDB Server

Details

Description

Attachments

Issue Links

Activity

People

Dates

Git Integration