[MDEV-26845] For unsupported statements ROW_NUMBER shows different values depending on previous events - Jira

Details

Type: Bug
Status: Open (View Workflow)
Priority: Major
Resolution: Unresolved
Affects Version/s: 10.7(EOL)
Fix Version/s: 10.11
Component/s: Server
Labels:
None

Description

--source include/have_innodb.inc

--disable_abort_on_error

create or replace table t (a tinyint) engine=InnoDB;

backup stage end;

get diagnostics condition 1 @n1 = row_number;

insert into t values (1),(2),(1000);

backup stage end;

get diagnostics condition 1 @n2 = row_number;

select 1;

backup stage end;

get diagnostics condition 1 @n3 = row_number;

select @n1, @n2, @n3;

bb-10.7-row_number cb9002bee36
MariaDB [test]> select @n1, @n2, @n3;
+------+------+------+
\| @n1 \| @n2 \| @n3 \|
+------+------+------+
\| 0 \| 3 \| 1 \|
+------+------+------+
1 row in set (0.000 sec)

It should probably be always 0.

Same applies to various other statements, e.g. the same test case returns the same result if backup stage end is replaced by a failing drop database, or grant, etc.

Attachments

Issue Links

is caused by

MDEV-10075 Provide index of error causing error in array INSERT

Closed

Activity

Ascending order - Click to sort in descending order

Elena Stepanova created issue - 2021-10-15 18:40

Elena Stepanova made changes - 2021-10-15 18:40

Field	Original Value	New Value
Link		This issue is caused by ~~MDEV-10075~~ [ ~~MDEV-10075~~ ]

Elena Stepanova made changes - 2021-10-15 18:51

Summary

Failed BACKUP STAGE sets ROW_NUMBER to 1

Failed BACKUP STAGE sets ROW_NUMBER to 1 or returns the previous value

Elena Stepanova made changes - 2021-10-15 18:57

Description

{code:sql}
BACKUP STAGE END;
get diagnostics condition 1 @n = row_number;
select @n;
{code}

{code:sql|title=bb-10.7-row_number cb9002bee36}
MariaDB [test]> show warnings;
+-------+------+-------------------------------------------------+
| Level | Code | Message |
+-------+------+-------------------------------------------------+
| Error | 4146 | You must start backup with "BACKUP STAGE START" |
+-------+------+-------------------------------------------------+
1 row in set (0.000 sec)

MariaDB [test]> get diagnostics condition 1 @n = row_number;
Query OK, 0 rows affected (0.000 sec)

MariaDB [test]> select @n;
+------+
| @n |
+------+
| 1 |
+------+
1 row in set (0.001 sec)
{code}

It should probably be 0.

{code:sql}
--disable_abort_on_error

create or replace table t (a tinyint);
backup stage end;
get diagnostics condition 1 @n1 = row_number;
insert into t values (1),(2),(1000);
backup stage end;
get diagnostics condition 1 @n2 = row_number;
select 1;
backup stage end;
get diagnostics condition 1 @n3 = row_number;
select @n1, @n2, @n3;
{code}

{code:sql|title=bb-10.7-row_number cb9002bee36}
MariaDB [test]> select @n1, @n2, @n3;
+------+------+------+
| @n1 | @n2 | @n3 |
+------+------+------+
| 0 | 3 | 1 |
+------+------+------+
1 row in set (0.000 sec)
{code}

It should probably be always 0.

Elena Stepanova made changes - 2021-10-15 18:57

Summary

Failed BACKUP STAGE sets ROW_NUMBER to 1 or returns the previous value

Failed BACKUP STAGE sets ROW_NUMBER to different values depending on previous events

Elena Stepanova made changes - 2021-10-15 18:59

Description

{code:sql}
--source include/have_innodb.inc
--disable_abort_on_error

create or replace table t (a tinyint) engine=InnoDB;
backup stage end;
get diagnostics condition 1 @n1 = row_number;
insert into t values (1),(2),(1000);
backup stage end;
get diagnostics condition 1 @n2 = row_number;
select 1;
backup stage end;
get diagnostics condition 1 @n3 = row_number;
select @n1, @n2, @n3;
{code}

{code:sql|title=bb-10.7-row_number cb9002bee36}
MariaDB [test]> select @n1, @n2, @n3;
+------+------+------+
| @n1 | @n2 | @n3 |
+------+------+------+
| 0 | 3 | 1 |
+------+------+------+
1 row in set (0.000 sec)
{code}

It should probably be always 0.

Elena Stepanova made changes - 2021-10-15 19:00

Priority

Trivial [ 5 ]

Minor [ 4 ]

Elena Stepanova made changes - 2021-10-15 19:10

Component/s	Backup [ 13902 ]
Description	{code:sql} --source include/have_innodb.inc --disable_abort_on_error create or replace table t (a tinyint) engine=InnoDB; backup stage end; get diagnostics condition 1 @n1 = row_number; insert into t values (1),(2),(1000); backup stage end; get diagnostics condition 1 @n2 = row_number; select 1; backup stage end; get diagnostics condition 1 @n3 = row_number; select @n1, @n2, @n3; {code} {code:sql\|title=bb-10.7-row_number cb9002bee36} MariaDB [test]> select @n1, @n2, @n3; +------+------+------+ \| @n1 \| @n2 \| @n3 \| +------+------+------+ \| 0 \| 3 \| 1 \| +------+------+------+ 1 row in set (0.000 sec) {code} It should probably be always 0.	{code:sql} --source include/have_innodb.inc --disable_abort_on_error create or replace table t (a tinyint) engine=InnoDB; backup stage end; get diagnostics condition 1 @n1 = row_number; insert into t values (1),(2),(1000); backup stage end; get diagnostics condition 1 @n2 = row_number; select 1; backup stage end; get diagnostics condition 1 @n3 = row_number; select @n1, @n2, @n3; {code} {code:sql\|title=bb-10.7-row_number cb9002bee36} MariaDB [test]> select @n1, @n2, @n3; +------+------+------+ \| @n1 \| @n2 \| @n3 \| +------+------+------+ \| 0 \| 3 \| 1 \| +------+------+------+ 1 row in set (0.000 sec) {code} It should probably be always 0. Same applies to various other statements, e.g. the same test case returns the same result if {{backup stage end}} is replaced by a failing {{drop database}}, or {{grant}}, etc.
Summary	Failed BACKUP STAGE sets ROW_NUMBER to different values depending on previous events	Non-table statements set ROW_NUMBER to different values depending on previous events

Elena Stepanova added a comment - 2021-10-15 19:18

Also applies to at least some ALTER TABLEs, e.g.

alter table t add key(a(0));

I give up on trying to make the summary accurate, please adjust it as needed after analysis.

Elena Stepanova added a comment - 2021-10-15 19:18 Also applies to at least some ALTER TABLEs, e.g. alter table t add key (a(0)); I give up on trying to make the summary accurate, please adjust it as needed after analysis.

Elena Stepanova added a comment - 2021-10-15 21:44

Given the variety of affected statements, I'm raising it to Major, but since apparently it concerns (mainly?) statements which are out of the scope of ~~MDEV-10075~~, I'm keeping it off 10.7 "must do" list.

Elena Stepanova added a comment - 2021-10-15 21:44 Given the variety of affected statements, I'm raising it to Major, but since apparently it concerns (mainly?) statements which are out of the scope of MDEV-10075 , I'm keeping it off 10.7 "must do" list.

Elena Stepanova made changes - 2021-10-15 21:44

Priority	Minor [ 4 ]	Major [ 3 ]
Summary	Non-table statements set ROW_NUMBER to different values depending on previous events	For unsupported statements ROW_NUMBER shows different values depending on previous events

Elena Stepanova made changes - 2021-10-16 10:29

Assignee

Rucha Deodhar [ rucha174 ]

Elena Stepanova [ elenst ]

Elena Stepanova made changes - 2021-10-16 10:43

Comment

[ I didn't realize that ~~MDEV-26635~~ hadn't yet been pushed when I was filing this, so maybe it will fix everything. I'll re-check. ]

Elena Stepanova made changes - 2021-10-16 10:43

Assignee

Elena Stepanova [ elenst ]

Rucha Deodhar [ rucha174 ]

Elena Stepanova added a comment - 2021-10-18 13:26

Somewhat more obvious case

SELECT 1 INTO OUTFILE 'f';

SELECT COUNT(*) FROM information_schema.tables;

--error ER_FILE_EXISTS_ERROR

SELECT 1 INTO OUTFILE 'f';

GET DIAGNOSTICS CONDITION 1 @n = ROW_NUMBER, @m = MESSAGE_TEXT;

SELECT @n, @m;

bb-10.7-row_number c27f04ede
COUNT(*)
265
SELECT 1 INTO OUTFILE 'f';
ERROR HY000: File 'f' already exists
GET DIAGNOSTICS CONDITION 1 @n = ROW_NUMBER, @m = MESSAGE_TEXT;
SELECT @n, @m;
@n @m
266 File 'f' already exists

Elena Stepanova added a comment - 2021-10-18 13:26 Somewhat more obvious case SELECT 1 INTO OUTFILE 'f' ; SELECT COUNT (*) FROM information_schema.tables; --error ER_FILE_EXISTS_ERROR SELECT 1 INTO OUTFILE 'f' ; GET DIAGNOSTICS CONDITION 1 @n = ROW_NUMBER, @m = MESSAGE_TEXT; SELECT @n, @m; bb-10.7-row_number c27f04ede COUNT (*) 265 SELECT 1 INTO OUTFILE 'f' ; ERROR HY000: File 'f' already exists GET DIAGNOSTICS CONDITION 1 @n = ROW_NUMBER, @m = MESSAGE_TEXT; SELECT @n, @m; @n @m 266 File 'f' already exists

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

Workflow

MariaDB v3 [ 126299 ]

MariaDB v4 [ 143258 ]

Rucha Deodhar made changes - 2023-03-09 11:59

Fix Version/s		10.8 [ 26121 ]
Fix Version/s	10.7 [ 24805 ]

Rucha Deodhar made changes - 2023-04-14 08:36

Fix Version/s		10.9 [ 26905 ]
Fix Version/s	10.8 [ 26121 ]

Julien Fritsch made changes - 2023-11-28 10:48

Fix Version/s

10.11 [ 27614 ]

Julien Fritsch made changes - 2023-11-28 10:55

Fix Version/s

10.9 [ 26905 ]

People

Assignee:: Rucha Deodhar

Reporter:: Elena Stepanova

Votes:: 0 Vote for this issue

Watchers:: 2 Start watching this issue

Dates

Created:: 2021-10-15 18:40

Updated:: 2023-11-28 10:55

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