[MDEV-16454] Bad results for IN with ROW - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Major
Resolution: Fixed
Affects Version/s: 5.5(EOL), 10.0(EOL), 10.1(EOL), 10.2(EOL), 10.3(EOL), 10.4(EOL)
Fix Version/s: 10.4.0
Component/s: Data types
Labels:
None

Description

This scalar query respects unsigned_flag difference for 18446744073709551615 and -1, goes through val_decimal() and correctly returns 0:

SELECT 18446744073709551615 IN (18446744073709551614,-1);

+---------------------------------------------------+

| 18446744073709551615 IN (18446744073709551614,-1) |

+---------------------------------------------------+

|                                                 0 |

+---------------------------------------------------+

A similar query with ROW() erroneously returns 1:

SELECT (18446744073709551615,0) IN ((18446744073709551614,0),(-1,0));

+---------------------------------------------------------------+

| (18446744073709551615,0) IN ((18446744073709551614,0),(-1,0)) |

+---------------------------------------------------------------+

|                                                             1 |

+---------------------------------------------------------------+

It ignores unsigned_flag, goes through val_int() and treats 18446744073709551615 and -1 as same values.

This scalar query correctly returns an error:

SELECT 1=POINT(1,1);

ERROR 4078 (HY000): Illegal parameter data types int and geometry for operation '='

A similar ROW query erroneously returns a result with a warning:

SELECT (1,0) IN ((POINT(1,1),0),(0,0));

SHOW WARNINGS;

+---------------------------------+

| (1,0) IN ((POINT(1,1),0),(0,0)) |

+---------------------------------+

|                               0 |

+---------------------------------+

+---------+------+-------------------------------------------------------------------------------------------------------------------------------------+

| Level   | Code | Message                                                                                                                             |

+---------+------+-------------------------------------------------------------------------------------------------------------------------------------+

| Warning | 1292 | Truncated incorrect INTEGER value: '\x00\x00\x00\x00\x01\x01\x00\x00\x00\x00\x00\x00\x00\x00\x00\xF0?\x00\x00\x00\x00\x00\x00\xF0?' |

+---------+------+-------------------------------------------------------------------------------------------------------------------------------------+

It's wrong, the expected behavior is to return an error, like a similar scalar query does.

If I put POINT(1,1) to a deeper level, it also erroneously returns a result with the same warning:

SELECT (1,(0,0)) IN ((1,(POINT(1,1),0)),(0,(0,0)));

+---------------------------------------------+

| (1,(0,0)) IN ((1,(POINT(1,1),0)),(0,(0,0))) |

+---------------------------------------------+

|                                           1 |

+---------------------------------------------+

The expected behavior is to return an error.

Attachments

Issue Links

blocks

MDEV-4912 Data type plugin API version 1

Closed

relates to

MDEV-11511 Collation aggregation for IN works differenly for scalar and ROW

Open

Activity

Ascending order - Click to sort in descending order

Alexander Barkov created issue - 2018-06-10 03:33

Alexander Barkov made changes - 2018-06-10 03:33

Field	Original Value	New Value
Link		This issue blocks ~~MDEV-4912~~ [ ~~MDEV-4912~~ ]

Alexander Barkov made changes - 2018-06-10 03:33

Description

This scalar query respects {{unsigned_flag}} difference for {{18446744073709551615}} and {{-1}}, goes through {{val_decimal()}} and correctly returns {{0}}:
{code:sql}
SELECT 18446744073709551615 IN (18446744073709551614,-1);
{code}
{noformat}
+---------------------------------------------------+
| 18446744073709551615 IN (18446744073709551614,-1) |
+---------------------------------------------------+
| 0 |
+---------------------------------------------------+
{noformat}

A similar query with ROW() erroneously returns {{1}}:
{code:sql}
SELECT (18446744073709551615,0) IN ((18446744073709551614,0),(-1,0));
{code}
{noformat}
+---------------------------------------------------------------+
| (18446744073709551615,0) IN ((18446744073709551614,0),(-1,0)) |
+---------------------------------------------------------------+
| 1 |
+---------------------------------------------------------------+
{noformat}
It ignores {{unsigned_flag}}, goes through {{val_int()}} and treats {{18446744073709551615}} and {{-1}} as same values.

This scalar query correctly returns an error:
{code:sql}
SELECT 1=POINT(1,1);
{code}
{noformat}
ERROR 4078 (HY000): Illegal parameter data types int and geometry for operation '='
{noformat}

A similar ROW query erroneously returns a result with a warning:
{code:sql}
SELECT (1,0) IN ((POINT(1,1),0),(0,0));
SHOW WARNINGS;
{code}
{noformat}
+---------------------------------+
| (1,0) IN ((POINT(1,1),0),(0,0)) |
+---------------------------------+
| 0 |
+---------------------------------+
+---------+------+-------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+-------------------------------------------------------------------------------------------------------------------------------------+
| Warning | 1292 | Truncated incorrect INTEGER value: '\x00\x00\x00\x00\x01\x01\x00\x00\x00\x00\x00\x00\x00\x00\x00\xF0?\x00\x00\x00\x00\x00\x00\xF0?' |
+---------+------+-------------------------------------------------------------------------------------------------------------------------------------+
{noformat}
It's wrong, the expected behavior is to return an error, like a similar scalar query does.

If I put {{POINT(1,1)}} to a deeper level, it also erroneously returns a result with the same warning:
{code:sql}
SELECT (1,(0,0)) IN ((1,(POINT(1,1),0)),(0,(0,0)));
{code}
{noformat}
+---------------------------------------------+
| (1,(0,0)) IN ((1,(POINT(1,1),0)),(0,(0,0))) |
+---------------------------------------------+
| 1 |
+---------------------------------------------+
{noformat
The expected behavior is to return an error.

Alexander Barkov made changes - 2018-06-22 07:18

Link

This issue relates to MDEV-11511 [ MDEV-11511 ]

Alexander Barkov added a comment - 2018-06-27 07:09

A similar problem is repeatable when mixing a string and a number:

According to MariaDB comparison rules, a string and a number are compared as DOUBLE. This script correctly follows this rule:

SELECT '0x'=0;

SHOW WARNINGS;

+--------+

| '0x'=0 |

+--------+

|      1 |

+--------+

+---------+------+----------------------------------------+

| Level   | Code | Message                                |

+---------+------+----------------------------------------+

| Warning | 1292 | Truncated incorrect DOUBLE value: '0x' |

+---------+------+----------------------------------------+

Notice:

there is a warning because '0x' was trucated during string-to->double conversion
string '0x' was converted to double 0
the comparison returned TRUE

This script produced exactly the same output:

SELECT '0x' IN (0);

SHOW WARNINGS;

So does this script, using IN with one value:

SELECT '0x' IN (0);

SHOW WARNINGS;

So does this script, using IN with two values:

SELECT '0x' IN (0,1);

SHOW WARNINGS;

So does this script, using IN with ROW with one value:

SELECT ('0x',1) IN ((0,1));

Now if use IN with ROW with more than one values:

SELECT ('0x',1) IN ((0,1),(1,1));

+---------------------------+

| ('0x',1) IN ((0,1),(1,1)) |

+---------------------------+

|                         0 |

+---------------------------+

it returns FALSE with no warnings.

Alexander Barkov added a comment - 2018-06-27 07:09 A similar problem is repeatable when mixing a string and a number: According to MariaDB comparison rules, a string and a number are compared as DOUBLE. This script correctly follows this rule: SELECT '0x' =0; SHOW WARNINGS; +--------+ | '0x'=0 | +--------+ | 1 | +--------+ +---------+------+----------------------------------------+ | Level | Code | Message | +---------+------+----------------------------------------+ | Warning | 1292 | Truncated incorrect DOUBLE value: '0x' | +---------+------+----------------------------------------+ Notice: there is a warning because '0x' was trucated during string-to->double conversion string '0x' was converted to double 0 the comparison returned TRUE This script produced exactly the same output: SELECT '0x' IN (0); SHOW WARNINGS; So does this script, using IN with one value: SELECT '0x' IN (0); SHOW WARNINGS; So does this script, using IN with two values: SELECT '0x' IN (0,1); SHOW WARNINGS; So does this script, using IN with ROW with one value: SELECT ( '0x' ,1) IN ((0,1)); Now if use IN with ROW with more than one values: SELECT ( '0x' ,1) IN ((0,1),(1,1)); +---------------------------+ | ('0x',1) IN ((0,1),(1,1)) | +---------------------------+ | 0 | +---------------------------+ it returns FALSE with no warnings.

Alexander Barkov made changes - 2018-06-27 12:07

issue.field.resolutiondate

2018-06-27 12:07:53.0

2018-06-27 12:07:53.853

Alexander Barkov made changes - 2018-06-27 12:07

Fix Version/s		10.4.0 [ 23115 ]
Fix Version/s	10.4 [ 22408 ]
Resolution		Fixed [ 1 ]
Status	Open [ 1 ]	Closed [ 6 ]

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

Workflow

MariaDB v3 [ 87774 ]

MariaDB v4 [ 154492 ]

People

Assignee:: Alexander Barkov

Reporter:: Alexander Barkov

Votes:: 0 Vote for this issue

Watchers:: 1 Start watching this issue

Dates

Created:: 2018-06-10 03:33

Updated:: 2018-06-27 12:07

Resolved:: 2018-06-27 12:07

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