[MDEV-21639] DEFAULT(col) evaluates to a bad value in WHERE clause - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Critical
Resolution: Fixed
Affects Version/s: 10.2(EOL)
Fix Version/s: 10.2.42
Component/s: Server, Virtual Columns
Labels:
None

Description

I run this script:

DROP TABLE IF EXISTS t1;

CREATE TABLE t1 (a BIGINT NOT NULL DEFAULT (IF(false,UNIX_TIMESTAMP(),10)));

INSERT INTO t1 VALUES (10000);

SELECT

a,

  DEFAULT(a),

  CASE WHEN a THEN DEFAULT(a) END AS c,

  CASE WHEN a THEN DEFAULT(a) END = 10 AS ce

FROM t1;

It returns the following correct result:

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

| a     | DEFAULT(a) | c    | ce   |

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

| 10000 |         10 |   10 |    1 |

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

Looks good so far. The value of the CASE expression (result column c) is correctly evaluates to 10, and the comparison expression (result column ce) correctly evaluates to true.

Now I put the comparison expression into the WHERE clause:

SELECT a FROM t1 WHERE CASE WHEN a THEN DEFAULT(a) END=10;

Empty set (0.001 sec)

Looks wrong.

Now I put a breakpoint in Field_longlong::val_int() and rerun the last SQL query with the WHERE condition.
val_int() is called two times:

I skip the first call which is evaluation of WHEN a
I print the following values during the second call, which is evaluation of THEN DEFAULT(a):

(gdb) print ptr

$6 = (uchar *) 0x62b000001a99 "\245\245\245\245\245\245\245\245", '\217' <repeats 15 times>

(gdb) fin

Run till exit from #0  Field_longlong::val_int (this=0x62b0000019c8)

    at /home/bar/maria-git/server.10.2.asan/sql/field.cc:4514

Item_field::val_int_result (this=0x62b000000d88)

    at /home/bar/maria-git/server.10.2.asan/sql/item.cc:2898

2898	  return result_field->val_int();

Value returned is $7 = -6510615555426900571

Notice:

Field_longlong::ptr points to a non-initialized buffer
val_int() returns a non-relevant value instead of 10

Attachments

Issue Links

blocks

MDEV-21637 Assertion `!is_valid_datetime() || fraction_remainder(((item->decimals) < (6) ? (item->decimals) : (6))) == 0' failed in Datetime_truncation_not_needed::Datetime_truncation_not_needed

Closed

relates to

MDEV-24958 Server crashes in my_strtod / Value_source::Converter_strntod::Converter_strntod with DEFAULT(blob)

Closed

Activity

Ascending order - Click to sort in descending order

Aleksey Midenkov added a comment - 2021-10-01 10:37

Not reproducible in 10.2 (also tried some old version until 2020-02-03), 10.3.

10.3 debugging shows that val_int() returns 10.

Aleksey Midenkov added a comment - 2021-10-01 10:37 Not reproducible in 10.2 (also tried some old version until 2020-02-03), 10.3. 10.3 debugging shows that val_int() returns 10.

Alexander Barkov added a comment - 2021-12-29 09:54 - edited

Still repeatable in the current 10.2 as of fad1d15326651a92895c799829ff66edc37fc20f

Also repeatable with the following scripts testing other data types - they all return empty sets.

DROP TABLE IF EXISTS t1;

CREATE TABLE t1 (a DOUBLE NOT NULL DEFAULT (IF(false,UNIX_TIMESTAMP(),10)));

INSERT INTO t1 VALUES (10000);

SELECT

a,

  DEFAULT(a),

  CASE WHEN a THEN DEFAULT(a) END AS c,

  CASE WHEN a THEN DEFAULT(a) END = 10 AS ce

FROM t1;

SELECT a FROM t1 WHERE CASE WHEN a THEN DEFAULT(a) END=10;

DROP TABLE IF EXISTS t1;

CREATE TABLE t1 (a DECIMAL(10,0) NOT NULL DEFAULT (IF(false,UNIX_TIMESTAMP(),10)));

INSERT INTO t1 VALUES (10000);

SELECT

a,

  DEFAULT(a),

  CASE WHEN a THEN DEFAULT(a) END AS c,

  CASE WHEN a THEN DEFAULT(a) END = 10 AS ce

FROM t1;

SELECT a FROM t1 WHERE CASE WHEN a THEN DEFAULT(a) END=10;

DROP TABLE IF EXISTS t1;

CREATE TABLE t1 (a VARCHAR(32) NOT NULL DEFAULT (IF(false,UNIX_TIMESTAMP(),10)));

INSERT INTO t1 VALUES (10000);

SELECT

a,

  DEFAULT(a),

  CASE WHEN a THEN DEFAULT(a) END AS c,

  CASE WHEN a THEN DEFAULT(a) END = '10' AS ce

FROM t1;

SELECT a FROM t1 WHERE CASE WHEN a THEN DEFAULT(a) END='10';

DROP TABLE IF EXISTS t1;

CREATE TABLE t1 (a DATE NOT NULL DEFAULT (IF(false,UNIX_TIMESTAMP(),DATE'2001-01-01')));

INSERT INTO t1 VALUES ('2000-01-01');

SELECT

a,

  DEFAULT(a),

  CASE WHEN a THEN DEFAULT(a) END AS c,

  CASE WHEN a THEN DEFAULT(a) END = '2001-01-01' AS ce

FROM t1;

SELECT a FROM t1 WHERE CASE WHEN a THEN DEFAULT(a) END='2001-01-01';

DROP TABLE IF EXISTS t1;

CREATE TABLE t1 (a TIME NOT NULL DEFAULT (IF(false,TIME(UNIX_TIMESTAMP()%10),TIME'10:20:30')));

INSERT INTO t1 VALUES ('10:00:00');

SELECT

a,

  DEFAULT(a),

  CASE WHEN a THEN DEFAULT(a) END AS c,

  CASE WHEN a THEN DEFAULT(a) END = '10:20:30' AS ce

FROM t1;

SELECT a FROM t1 WHERE CASE WHEN a THEN DEFAULT(a) END='10:20:30';

DROP TABLE IF EXISTS t1;

CREATE TABLE t1 (a DATETIME NOT NULL DEFAULT (IF(false,UNIX_TIMESTAMP(),TIMESTAMP'2001-01-01 10:20:30')));

INSERT INTO t1 VALUES ('2000-01-01 10:00:00');

SELECT

a,

  DEFAULT(a),

  CASE WHEN a THEN DEFAULT(a) END AS c,

  CASE WHEN a THEN DEFAULT(a) END = '2001-01-01 10:20:30' AS ce

FROM t1;

SELECT a FROM t1 WHERE CASE WHEN a THEN DEFAULT(a) END='2001-01-01 10:20:30';

DROP TABLE IF EXISTS t1;

CREATE TABLE t1 (a INT NOT NULL DEFAULT (IF(false,UNIX_TIMESTAMP(),FALSE)));

INSERT INTO t1 VALUES (10);

SELECT

a,

  DEFAULT(a),

  CASE WHEN a THEN DEFAULT(a) END AS c,

  CASE WHEN a THEN DEFAULT(a) END IS FALSE AS ce

FROM t1;

SELECT a FROM t1 WHERE CASE WHEN a THEN DEFAULT(a) END IS FALSE;

Alexander Barkov added a comment - 2021-12-29 09:54 - edited Still repeatable in the current 10.2 as of fad1d15326651a92895c799829ff66edc37fc20f Also repeatable with the following scripts testing other data types - they all return empty sets. DROP TABLE IF EXISTS t1; CREATE TABLE t1 (a DOUBLE NOT NULL DEFAULT ( IF ( false ,UNIX_TIMESTAMP(),10))); INSERT INTO t1 VALUES (10000); SELECT a, DEFAULT (a), CASE WHEN a THEN DEFAULT (a) END AS c, CASE WHEN a THEN DEFAULT (a) END = 10 AS ce FROM t1; SELECT a FROM t1 WHERE CASE WHEN a THEN DEFAULT (a) END =10; DROP TABLE IF EXISTS t1; CREATE TABLE t1 (a DECIMAL (10,0) NOT NULL DEFAULT ( IF ( false ,UNIX_TIMESTAMP(),10))); INSERT INTO t1 VALUES (10000); SELECT a, DEFAULT (a), CASE WHEN a THEN DEFAULT (a) END AS c, CASE WHEN a THEN DEFAULT (a) END = 10 AS ce FROM t1; SELECT a FROM t1 WHERE CASE WHEN a THEN DEFAULT (a) END =10; DROP TABLE IF EXISTS t1; CREATE TABLE t1 (a VARCHAR (32) NOT NULL DEFAULT ( IF ( false ,UNIX_TIMESTAMP(),10))); INSERT INTO t1 VALUES (10000); SELECT a, DEFAULT (a), CASE WHEN a THEN DEFAULT (a) END AS c, CASE WHEN a THEN DEFAULT (a) END = '10' AS ce FROM t1; SELECT a FROM t1 WHERE CASE WHEN a THEN DEFAULT (a) END = '10' ; DROP TABLE IF EXISTS t1; CREATE TABLE t1 (a DATE NOT NULL DEFAULT ( IF ( false ,UNIX_TIMESTAMP(), DATE '2001-01-01' ))); INSERT INTO t1 VALUES ( '2000-01-01' ); SELECT a, DEFAULT (a), CASE WHEN a THEN DEFAULT (a) END AS c, CASE WHEN a THEN DEFAULT (a) END = '2001-01-01' AS ce FROM t1; SELECT a FROM t1 WHERE CASE WHEN a THEN DEFAULT (a) END = '2001-01-01' ; DROP TABLE IF EXISTS t1; CREATE TABLE t1 (a TIME NOT NULL DEFAULT ( IF ( false , TIME (UNIX_TIMESTAMP()%10), TIME '10:20:30' ))); INSERT INTO t1 VALUES ( '10:00:00' ); SELECT a, DEFAULT (a), CASE WHEN a THEN DEFAULT (a) END AS c, CASE WHEN a THEN DEFAULT (a) END = '10:20:30' AS ce FROM t1; SELECT a FROM t1 WHERE CASE WHEN a THEN DEFAULT (a) END = '10:20:30' ; DROP TABLE IF EXISTS t1; CREATE TABLE t1 (a DATETIME NOT NULL DEFAULT ( IF ( false ,UNIX_TIMESTAMP(), TIMESTAMP '2001-01-01 10:20:30' ))); INSERT INTO t1 VALUES ( '2000-01-01 10:00:00' ); SELECT a, DEFAULT (a), CASE WHEN a THEN DEFAULT (a) END AS c, CASE WHEN a THEN DEFAULT (a) END = '2001-01-01 10:20:30' AS ce FROM t1; SELECT a FROM t1 WHERE CASE WHEN a THEN DEFAULT (a) END = '2001-01-01 10:20:30' ; DROP TABLE IF EXISTS t1; CREATE TABLE t1 (a INT NOT NULL DEFAULT ( IF ( false ,UNIX_TIMESTAMP(), FALSE ))); INSERT INTO t1 VALUES (10); SELECT a, DEFAULT (a), CASE WHEN a THEN DEFAULT (a) END AS c, CASE WHEN a THEN DEFAULT (a) END IS FALSE AS ce FROM t1; SELECT a FROM t1 WHERE CASE WHEN a THEN DEFAULT (a) END IS FALSE ;

People

Assignee:: Alexander Barkov

Reporter:: Alexander Barkov

Votes:: 0 Vote for this issue

Watchers:: 2 Start watching this issue

Dates

Created:: 2020-02-03 10:39

Updated:: 2021-12-29 15:28

Resolved:: 2021-12-29 15:28

Git Integration

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

MariaDB Server