Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

[YSQL][SQLancer] Attribute value type does not match exception occurs on DELETE statement with WHERE clause with MONEY PK #11346

Closed
qvad opened this issue Feb 3, 2022 · 2 comments
Closed

[YSQL][SQLancer] Attribute value type does not match exception occurs on DELETE statement with WHERE clause with MONEY PK #11346

qvad opened this issue Feb 3, 2022 · 2 comments
Assignees
@m-iancu @myang2021
Labels
area/ysql Yugabyte SQL (YSQL)
Projects
YSQL
Milestone
2.13

Comments

@qvad
Copy link
Contributor

qvad commented Feb 3, 2022

Description

Reproduced in 2.11.3 and 2.11.2 versions.
Required conditions: PK, previuosuly inserted value and WHERE clause in delete statement.

sql

CREATE TABLE t0(c0 money, PRIMARY KEY(c0));
INSERT INTO t0(c0) VALUES(CAST(1.38073114E9 AS MONEY));
DELETE FROM t0 WHERE (((0.14198202)::MONEY)>=((0.14222479)::MONEY));

ysql output

yugabyte=# CREATE TABLE t0(c0 money, PRIMARY KEY(c0));
CREATE TABLE
yugabyte=# INSERT INTO t0(c0) VALUES(CAST(1.38073114E9 AS MONEY));
INSERT 0 1
yugabyte=# DELETE FROM t0 WHERE (((0.14198202)::MONEY)>=((0.14222479)::MONEY));
ERROR:  Corruption: Attribute value type does not match column type: 4 vs. 10
@qvad qvad added the area/ysql Yugabyte SQL (YSQL) label Feb 3, 2022
@sushantrmishra sushantrmishra added this to Backlog in YSQL via automation Feb 3, 2022
@myang2021 myang2021 self-assigned this Feb 9, 2022
@m-iancu m-iancu added this to the 2.13 milestone Feb 9, 2022
@myang2021 myang2021 mentioned this issue Feb 10, 2022
Closed
@myang2021
Copy link
Contributor

There is a bug in the function YBCIsSingleRowUpdateOrDelete:

bool YBCIsSingleRowUpdateOrDelete(ModifyTable *modifyTable)
{
    /* Support UPDATE and DELETE. */
    if (modifyTable->operation != CMD_UPDATE &&
        modifyTable->operation != CMD_DELETE)
        return false;

    /* Should only have one data source. */
    if (list_length(modifyTable->plans) != 1)
        return false;

    /* Verify the single data source is a Result node. */
    if (!IsA(linitial(modifyTable->plans), Result))
        return false;

    return true;
}

It assumes a single Result node must be a single row but that is not true. In this case we should return false because the update may involve many rows as the where clause always evaluates to true:

yugabyte=# select (((0.14198202)::MONEY)>=((0.14222479)::MONEY));
select (((0.14198202)::MONEY)>=((0.14222479)::MONEY));
 ?column?
----------
 t
(1 row)

@myang2021
Copy link
Contributor

As shown in #11298 , wrong result can also be produced in addition to ERROR.

myang2021 added a commit that referenced this issue Feb 11, 2022
@myang2021
[#11346] YSQL: Fix bug in YBCIsSingleRowUpdateOrDelete
63455b1 
Summary:
Example test case:

```
yugabyte=# CREATE TABLE double_row (k int primary key, v1 int, v2 int);
CREATE TABLE double_row (k int primary key, v1 int, v2 int);
CREATE TABLE
yugabyte=# INSERT INTO double_row VALUES (1, 1, 1);
INSERT INTO double_row VALUES (1, 1, 1);
INSERT 0 1
yugabyte=# INSERT INTO double_row VALUES (2, 2, 2);
INSERT INTO double_row VALUES (2, 2, 2);
INSERT 0 1
yugabyte=# EXPLAIN (COSTS FALSE) DELETE FROM double_row WHERE 2::MONEY <= 2::MONEY;
EXPLAIN (COSTS FALSE) DELETE FROM double_row WHERE 2::MONEY <= 2::MONEY;
                     QUERY PLAN
-----------------------------------------------------
 Delete on double_row
   ->  Result
         One-Time Filter: ((2)::money <= (2)::money)
         ->  Seq Scan on double_row
(4 rows)

yugabyte=# DELETE FROM double_row WHERE 2::MONEY <= 2::MONEY;
DELETE FROM double_row WHERE 2::MONEY <= 2::MONEY;
ERROR:  Corruption: Attribute value type does not match column type: 3 vs. 10

```

I found that YBCIsSingleRowUpdateOrDelete returned true for the DELETE statement which isn't right.
The table had two rows and both rows are deleted in postgres:

```
postgres=# CREATE TABLE double_row (k int primary key, v1 int, v2 int);
CREATE TABLE
postgres=# INSERT INTO double_row VALUES (1, 1, 1);
INSERT 0 1
postgres=# INSERT INTO double_row VALUES (2, 2, 2);
INSERT 0 1
postgres=# EXPLAIN (COSTS FALSE) DELETE FROM double_row WHERE 2::MONEY <= 2::MONEY;
                     QUERY PLAN
-----------------------------------------------------
 Delete on double_row
   ->  Result
         One-Time Filter: ((2)::money <= (2)::money)
         ->  Seq Scan on double_row
(4 rows)

postgres=# DELETE FROM double_row WHERE 2::MONEY <= 2::MONEY;
DELETE 2
```

I adjusted YBCIsSingleRowUpdateOrDelete to return false if the Result node has input plans.

Test Plan: ./yb_build.sh debug --java-test 'org.yb.pgsql.TestPgRegressDml'

Reviewers: zyu, amartsinchyk

Reviewed By: zyu, amartsinchyk

Subscribers: yql

Differential Revision: https://phabricator.dev.yugabyte.com/D15357
myang2021 added a commit that referenced this issue Feb 12, 2022
@myang2021
[BACKPORT 2.12] [#11346] YSQL: Fix bug in YBCIsSingleRowUpdateOrDelete
3abe5b4 
Summary:
Example test case:

```
yugabyte=# CREATE TABLE double_row (k int primary key, v1 int, v2 int);
CREATE TABLE double_row (k int primary key, v1 int, v2 int);
CREATE TABLE
yugabyte=# INSERT INTO double_row VALUES (1, 1, 1);
INSERT INTO double_row VALUES (1, 1, 1);
INSERT 0 1
yugabyte=# INSERT INTO double_row VALUES (2, 2, 2);
INSERT INTO double_row VALUES (2, 2, 2);
INSERT 0 1
yugabyte=# EXPLAIN (COSTS FALSE) DELETE FROM double_row WHERE 2::MONEY <= 2::MONEY;
EXPLAIN (COSTS FALSE) DELETE FROM double_row WHERE 2::MONEY <= 2::MONEY;
                     QUERY PLAN
-----------------------------------------------------
 Delete on double_row
   ->  Result
         One-Time Filter: ((2)::money <= (2)::money)
         ->  Seq Scan on double_row
(4 rows)

yugabyte=# DELETE FROM double_row WHERE 2::MONEY <= 2::MONEY;
DELETE FROM double_row WHERE 2::MONEY <= 2::MONEY;
ERROR:  Corruption: Attribute value type does not match column type: 3 vs. 10

```

I found that YBCIsSingleRowUpdateOrDelete returned true for the DELETE statement which isn't right.
The table had two rows and both rows are deleted in postgres:

```
postgres=# CREATE TABLE double_row (k int primary key, v1 int, v2 int);
CREATE TABLE
postgres=# INSERT INTO double_row VALUES (1, 1, 1);
INSERT 0 1
postgres=# INSERT INTO double_row VALUES (2, 2, 2);
INSERT 0 1
postgres=# EXPLAIN (COSTS FALSE) DELETE FROM double_row WHERE 2::MONEY <= 2::MONEY;
                     QUERY PLAN
-----------------------------------------------------
 Delete on double_row
   ->  Result
         One-Time Filter: ((2)::money <= (2)::money)
         ->  Seq Scan on double_row
(4 rows)

postgres=# DELETE FROM double_row WHERE 2::MONEY <= 2::MONEY;
DELETE 2
```

I adjusted YBCIsSingleRowUpdateOrDelete to return false if the Result node has input plans.

Original commit: D15357 / 63455b1

Test Plan:
Jenkins: rebase: 2.12

./yb_build.sh debug --java-test 'org.yb.pgsql.TestPgRegressDml'

Reviewers: amartsinchyk, zyu

Reviewed By: zyu

Subscribers: yql

Differential Revision: https://phabricator.dev.yugabyte.com/D15417
myang2021 added a commit that referenced this issue Feb 12, 2022
@myang2021
[BACKPORT 2.6][#11346] YSQL: Fix bug in YBCIsSingleRowUpdateOrDelete
693d231 
Summary:
Example test case:

```
yugabyte=# CREATE TABLE double_row (k int primary key, v1 int, v2 int);
CREATE TABLE double_row (k int primary key, v1 int, v2 int);
CREATE TABLE
yugabyte=# INSERT INTO double_row VALUES (1, 1, 1);
INSERT INTO double_row VALUES (1, 1, 1);
INSERT 0 1
yugabyte=# INSERT INTO double_row VALUES (2, 2, 2);
INSERT INTO double_row VALUES (2, 2, 2);
INSERT 0 1
yugabyte=# EXPLAIN (COSTS FALSE) DELETE FROM double_row WHERE 2::MONEY <= 2::MONEY;
EXPLAIN (COSTS FALSE) DELETE FROM double_row WHERE 2::MONEY <= 2::MONEY;
                     QUERY PLAN
-----------------------------------------------------
 Delete on double_row
   ->  Result
         One-Time Filter: ((2)::money <= (2)::money)
         ->  Seq Scan on double_row
(4 rows)

yugabyte=# DELETE FROM double_row WHERE 2::MONEY <= 2::MONEY;
DELETE FROM double_row WHERE 2::MONEY <= 2::MONEY;
ERROR:  Corruption: Attribute value type does not match column type: 3 vs. 10

```

I found that YBCIsSingleRowUpdateOrDelete returned true for the DELETE statement which isn't right.
The table had two rows and both rows are deleted in postgres:

```
postgres=# CREATE TABLE double_row (k int primary key, v1 int, v2 int);
CREATE TABLE
postgres=# INSERT INTO double_row VALUES (1, 1, 1);
INSERT 0 1
postgres=# INSERT INTO double_row VALUES (2, 2, 2);
INSERT 0 1
postgres=# EXPLAIN (COSTS FALSE) DELETE FROM double_row WHERE 2::MONEY <= 2::MONEY;
                     QUERY PLAN
-----------------------------------------------------
 Delete on double_row
   ->  Result
         One-Time Filter: ((2)::money <= (2)::money)
         ->  Seq Scan on double_row
(4 rows)

postgres=# DELETE FROM double_row WHERE 2::MONEY <= 2::MONEY;
DELETE 2
```

I adjusted YBCIsSingleRowUpdateOrDelete to return false if the Result node has input plans.

Original commit: D15357 / 63455b1

Test Plan:
Jenkins: rebase: 2.6
./yb_build.sh debug --java-test 'org.yb.pgsql.TestPgRegressDml'

Reviewers: amartsinchyk, zyu

Reviewed By: zyu

Subscribers: yql

Differential Revision: https://phabricator.dev.yugabyte.com/D15421
myang2021 added a commit that referenced this issue Feb 13, 2022
@myang2021
[BACKPORT 2.8][#11346] YSQL: Fix bug in YBCIsSingleRowUpdateOrDelete
8141284 
Summary:
Example test case:

```
yugabyte=# CREATE TABLE double_row (k int primary key, v1 int, v2 int);
CREATE TABLE double_row (k int primary key, v1 int, v2 int);
CREATE TABLE
yugabyte=# INSERT INTO double_row VALUES (1, 1, 1);
INSERT INTO double_row VALUES (1, 1, 1);
INSERT 0 1
yugabyte=# INSERT INTO double_row VALUES (2, 2, 2);
INSERT INTO double_row VALUES (2, 2, 2);
INSERT 0 1
yugabyte=# EXPLAIN (COSTS FALSE) DELETE FROM double_row WHERE 2::MONEY <= 2::MONEY;
EXPLAIN (COSTS FALSE) DELETE FROM double_row WHERE 2::MONEY <= 2::MONEY;
                     QUERY PLAN
-----------------------------------------------------
 Delete on double_row
   ->  Result
         One-Time Filter: ((2)::money <= (2)::money)
         ->  Seq Scan on double_row
(4 rows)

yugabyte=# DELETE FROM double_row WHERE 2::MONEY <= 2::MONEY;
DELETE FROM double_row WHERE 2::MONEY <= 2::MONEY;
ERROR:  Corruption: Attribute value type does not match column type: 3 vs. 10

```

I found that YBCIsSingleRowUpdateOrDelete returned true for the DELETE statement which isn't right.
The table had two rows and both rows are deleted in postgres:

```
postgres=# CREATE TABLE double_row (k int primary key, v1 int, v2 int);
CREATE TABLE
postgres=# INSERT INTO double_row VALUES (1, 1, 1);
INSERT 0 1
postgres=# INSERT INTO double_row VALUES (2, 2, 2);
INSERT 0 1
postgres=# EXPLAIN (COSTS FALSE) DELETE FROM double_row WHERE 2::MONEY <= 2::MONEY;
                     QUERY PLAN
-----------------------------------------------------
 Delete on double_row
   ->  Result
         One-Time Filter: ((2)::money <= (2)::money)
         ->  Seq Scan on double_row
(4 rows)

postgres=# DELETE FROM double_row WHERE 2::MONEY <= 2::MONEY;
DELETE 2
```

I adjusted YBCIsSingleRowUpdateOrDelete to return false if the Result node has input plans.

Original commit: D15357 / 63455b1

Test Plan:
Jenkins: rebase: 2.8

./yb_build.sh debug --java-test 'org.yb.pgsql.TestPgRegressDml'

Reviewers: amartsinchyk, zyu

Reviewed By: zyu

Subscribers: yql

Differential Revision: https://phabricator.dev.yugabyte.com/D15418
YSQL automation moved this from Backlog to Done Feb 14, 2022
jayant07-yb pushed a commit to jayant07-yb/yugabyte-db that referenced this issue Mar 8, 2022
@myang2021
[yugabyte#11346] YSQL: Fix bug in YBCIsSingleRowUpdateOrDelete
e590528 
Summary:
Example test case:

```
yugabyte=# CREATE TABLE double_row (k int primary key, v1 int, v2 int);
CREATE TABLE double_row (k int primary key, v1 int, v2 int);
CREATE TABLE
yugabyte=# INSERT INTO double_row VALUES (1, 1, 1);
INSERT INTO double_row VALUES (1, 1, 1);
INSERT 0 1
yugabyte=# INSERT INTO double_row VALUES (2, 2, 2);
INSERT INTO double_row VALUES (2, 2, 2);
INSERT 0 1
yugabyte=# EXPLAIN (COSTS FALSE) DELETE FROM double_row WHERE 2::MONEY <= 2::MONEY;
EXPLAIN (COSTS FALSE) DELETE FROM double_row WHERE 2::MONEY <= 2::MONEY;
                     QUERY PLAN
-----------------------------------------------------
 Delete on double_row
   ->  Result
         One-Time Filter: ((2)::money <= (2)::money)
         ->  Seq Scan on double_row
(4 rows)

yugabyte=# DELETE FROM double_row WHERE 2::MONEY <= 2::MONEY;
DELETE FROM double_row WHERE 2::MONEY <= 2::MONEY;
ERROR:  Corruption: Attribute value type does not match column type: 3 vs. 10

```

I found that YBCIsSingleRowUpdateOrDelete returned true for the DELETE statement which isn't right.
The table had two rows and both rows are deleted in postgres:

```
postgres=# CREATE TABLE double_row (k int primary key, v1 int, v2 int);
CREATE TABLE
postgres=# INSERT INTO double_row VALUES (1, 1, 1);
INSERT 0 1
postgres=# INSERT INTO double_row VALUES (2, 2, 2);
INSERT 0 1
postgres=# EXPLAIN (COSTS FALSE) DELETE FROM double_row WHERE 2::MONEY <= 2::MONEY;
                     QUERY PLAN
-----------------------------------------------------
 Delete on double_row
   ->  Result
         One-Time Filter: ((2)::money <= (2)::money)
         ->  Seq Scan on double_row
(4 rows)

postgres=# DELETE FROM double_row WHERE 2::MONEY <= 2::MONEY;
DELETE 2
```

I adjusted YBCIsSingleRowUpdateOrDelete to return false if the Result node has input plans.

Test Plan: ./yb_build.sh debug --java-test 'org.yb.pgsql.TestPgRegressDml'

Reviewers: zyu, amartsinchyk

Reviewed By: zyu, amartsinchyk

Subscribers: yql

Differential Revision: https://phabricator.dev.yugabyte.com/D15357
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees

@m-iancu m-iancu

@myang2021 myang2021

Labels
area/ysql Yugabyte SQL (YSQL)
Projects
YQL-beta
Status: Done
YSQL
  
Done
Milestone
2.13
Development

No branches or pull requests
3 participants
@m-iancu @qvad @myang2021