[MDEV-34123] CONCAT Function Returns Unexpected Empty Set in Query - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Major
Resolution: Fixed
Affects Version/s: 10.11.7
Fix Version/s: 10.6.20, 10.11.10, 11.2.6, 11.4.4
Component/s: OTHER
Labels:
- compat80

Description

CREATE TABLE IF NOT EXISTS t0 (

    c0_1 VARCHAR(200),

    c0_2 TEXT

);

INSERT INTO t0 VALUES ('0.67882431850', '97966');

MariaDB [test]> select * from t0;

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

| c0_1          | c0_2  |

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

| 0.67882431850 | 97966 |

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

1 row in set (0.002 sec)

Execute the query:

SELECT (CONCAT(t0.c0_1, t0.c0_2)) AS ref0 FROM t0 WHERE (CONCAT(t0.c0_1, t0.c0_2));

Expected Result:
The query should return a single row with the value 0.6788243185097966 in the ref0 column.

Actual Result:
The query returns an empty set.

Attachments

Issue Links

causes

MDEV-35288 Assertion `!is_cond()' failed in virtual longlong Item_bool_func::val_int()

Stalled

is duplicated by

MDEV-34592 There are some problems with the keyword 'where if'

Closed

relates to

MDEV-35135 Assertion `!is_cond()' failed in Item_bool_func::val_int / do_select

Closed

MDEV-33999 Unexpected result when casting decimal in WHERE clause

Confirmed

MDEV-34114 Incorrect Query Result when Adding Columns

Open

MDEV-34189 Unexpected error on `WHERE inet6col`

Closed

(1 relates to)

Activity

Ascending order - Click to sort in descending order

View 5 older comments

Alexander Barkov added a comment - 2024-05-13 07:10

In case of the column, normalize_cond() rewrites this statement:

SELECT * FROM t1 WHERE c;

as:

SELECT * FROM t1 WHERE c<>0;

The rewritten condition is evaluated as DECIMAL:

The string '0.6' gets converted to 0.6
Decimal '0.6 <> 0' evaluates as TRUE

In case of functions, the condition does not get replaced, the conditions is evaluated as is:

evaluate_join_record() calls Item_func_xxx::val_int(), which is :

longlong Item_str_func::val_int()

  DBUG_ASSERT(fixed());

  StringBuffer<22> tmp;

  String *res= val_str(&tmp);

  return res ? longlong_from_string_with_check(res) : 0;

longlong_from_string_with_check() converts '0.6' to 0 (without rounding).

Alexander Barkov added a comment - 2024-05-13 07:10 In case of the column, normalize_cond() rewrites this statement: SELECT * FROM t1 WHERE c; as: SELECT * FROM t1 WHERE c<>0; The rewritten condition is evaluated as DECIMAL: The string '0.6' gets converted to 0.6 Decimal '0.6 <> 0' evaluates as TRUE In case of functions, the condition does not get replaced, the conditions is evaluated as is: evaluate_join_record() calls Item_func_xxx::val_int(), which is : longlong Item_str_func::val_int() { DBUG_ASSERT(fixed()); StringBuffer<22> tmp; String *res= val_str(&tmp); return res ? longlong_from_string_with_check(res) : 0; } longlong_from_string_with_check() converts '0.6' to 0 (without rounding).

Alexander Barkov added a comment - 2024-05-14 05:25 - edited

The problem is repeatable with the DECIMAL data type:

CREATE OR REPLACE TABLE t1 (a DECIMAL(10,1));

INSERT INTO t1 VALUES (0.1);

SELECT * FROM t1 WHERE a; -- this correctly returns one row

SELECT * FROM t1 WHERE COALESCE(a); -- this incorrectly returns empty set

The problem is repeatable with the DOUBLE data type:

CREATE OR REPLACE TABLE t1 (a DOUBLE);

INSERT INTO t1 VALUES (0.1);

SELECT * FROM t1 WHERE a; -- this correctly returns one row

SELECT * FROM t1 WHERE COALESCE(a); -- this incorrectly returns empty set

Alexander Barkov added a comment - 2024-05-14 05:25 - edited The problem is repeatable with the DECIMAL data type: CREATE OR REPLACE TABLE t1 (a DECIMAL (10,1)); INSERT INTO t1 VALUES (0.1); SELECT * FROM t1 WHERE a; -- this correctly returns one row SELECT * FROM t1 WHERE COALESCE (a); -- this incorrectly returns empty set The problem is repeatable with the DOUBLE data type: CREATE OR REPLACE TABLE t1 (a DOUBLE ); INSERT INTO t1 VALUES (0.1); SELECT * FROM t1 WHERE a; -- this correctly returns one row SELECT * FROM t1 WHERE COALESCE (a); -- this incorrectly returns empty set

Alexander Barkov added a comment - 2024-05-14 05:29

The problem is also repeatable with DATETIME:

CREATE OR REPLACE TABLE t1 (a DATETIME(1));

INSERT INTO t1 VALUES ('0000-00-00 00:00:00.1');

SELECT * FROM t1 WHERE a; -- Correctly returns one row

SELECT * FROM t1 WHERE COALESCE(a); -- Incorrectly returns empty set

The problem is also repeatable with TIME:

CREATE OR REPLACE TABLE t1 (a TIME(1));

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

SELECT * FROM t1 WHERE a; -- Correctly returns one row

SELECT * FROM t1 WHERE COALESCE(a); -- Incorrectly returns empty set

Alexander Barkov added a comment - 2024-05-14 05:29 The problem is also repeatable with DATETIME: CREATE OR REPLACE TABLE t1 (a DATETIME(1)); INSERT INTO t1 VALUES ( '0000-00-00 00:00:00.1' ); SELECT * FROM t1 WHERE a; -- Correctly returns one row SELECT * FROM t1 WHERE COALESCE (a); -- Incorrectly returns empty set The problem is also repeatable with TIME: CREATE OR REPLACE TABLE t1 (a TIME (1)); INSERT INTO t1 VALUES ( '00:00:00.1' ); SELECT * FROM t1 WHERE a; -- Correctly returns one row SELECT * FROM t1 WHERE COALESCE (a); -- Incorrectly returns empty set

Oleksandr Byelkin added a comment - 2024-10-08 12:53

I reviewed, rebased and pushed it.

Oleksandr Byelkin added a comment - 2024-10-08 12:53 I reviewed, rebased and pushed it.

Ralf Gebhardt added a comment - 2024-10-28 11:42

bar explained to me that the real issue and description for this case is "In some contexts text strings containing numbers with the integer part equal to zero and the fractional part not equal to zero where erroneously evaluated as FALSE in WHERE condition."

Ralf Gebhardt added a comment - 2024-10-28 11:42 bar explained to me that the real issue and description for this case is "In some contexts text strings containing numbers with the integer part equal to zero and the fractional part not equal to zero where erroneously evaluated as FALSE in WHERE condition."

People

Assignee:: Alexander Barkov

Reporter:: chaos

Votes:: 0 Vote for this issue

Watchers:: 6 Start watching this issue

Dates

Created:: 2024-05-09 02:20

Updated:: 2024-10-30 11:25

Resolved:: 2024-10-08 12:53

Git Integration

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

MariaDB Server