[MDEV-8860] Wrong result for WHERE 2016 < SOME (SELECT CAST(time_column AS DATETIME) FROM t1) - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Major
Resolution: Fixed
Affects Version/s: 10.0(EOL), 10.1(EOL)
Fix Version/s: 10.1.8
Component/s: Data Manipulation - Subquery, Temporal Types
Labels:
- upstream

Sprint:
10.1.8-4

Description

SET timestamp=UNIX_TIMESTAMP('2015-01-01 00:00:00');

DROP TABLE IF EXISTS t1;

CREATE TABLE t1 (a TIME);

INSERT INTO t1 VALUES ('00:00:00'),('00:01:00');

SELECT 1 FROM t1 WHERE 2016 > SOME (SELECT CAST(a AS DATETIME) FROM t1);

returns two rows with no warnings:

+---+

| 1 |

+---+

| 1 |

| 1 |

+---+

2 rows in set (0.01 sec)

Now let's check which of the rows in SOME actually generated true condition:

SELECT * FROM t1 WHERE 2016 > CAST(a AS DATETIME);

SHOW WARNINGS;

Opps. It returns empty set with a warning:

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

| Level   | Code | Message                          |

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

| Warning | 1292 | Incorrect datetime value: '2016' |

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

1 row in set (0.00 sec)

The result of the query with SOME is obviously wrong. It should have returned empty set with warnings.

Attachments

Activity

Ascending order - Click to sort in descending order

Alexander Barkov created issue - 2015-09-28 08:33

Alexander Barkov made changes - 2015-09-28 08:33

Field	Original Value	New Value
Description	{code} SET timestamp=UNIXTIME_TIMESTAMP('2015-01-01 00:00:00'); DROP TABLE IF EXISTS t1; CREATE TABLE t1 (a TIME); INSERT INTO t1 VALUES ('00:00:00'),('00:01:00'); SELECT 1 FROM t1 WHERE 2016 < SOME (SELECT CAST(a AS DATETIME) FROM t1); {code} returns two rows with no warnings: {noformat} +---+ \| 1 \| +---+ \| 1 \| \| 1 \| +---+ 2 rows in set (0.01 sec) {noformat} Now let's check which of the row in SOME actually generated true condition: {code} SELECT * FROM t1 WHERE 2016 > CAST(a AS DATETIME); SHOW WARNINGS; {code} Opps. It returns empty set with a warning: {noformat} +---------+------+----------------------------------+ \| Level \| Code \| Message \| +---------+------+----------------------------------+ \| Warning \| 1292 \| Incorrect datetime value: '2016' \| +---------+------+----------------------------------+ 1 row in set (0.00 sec) {noformat} The result of the query with SOME is obviously wrong. It should have returned empty set with warnings.	{code} SET timestamp=UNIXTIME_TIMESTAMP('2015-01-01 00:00:00'); DROP TABLE IF EXISTS t1; CREATE TABLE t1 (a TIME); INSERT INTO t1 VALUES ('00:00:00'),('00:01:00'); SELECT 1 FROM t1 WHERE 2016 < SOME (SELECT CAST(a AS DATETIME) FROM t1); {code} returns two rows with no warnings: {noformat} +---+ \| 1 \| +---+ \| 1 \| \| 1 \| +---+ 2 rows in set (0.01 sec) {noformat} Now let's check which of the rows in SOME actually generated true condition: {code} SELECT * FROM t1 WHERE 2016 > CAST(a AS DATETIME); SHOW WARNINGS; {code} Opps. It returns empty set with a warning: {noformat} +---------+------+----------------------------------+ \| Level \| Code \| Message \| +---------+------+----------------------------------+ \| Warning \| 1292 \| Incorrect datetime value: '2016' \| +---------+------+----------------------------------+ 1 row in set (0.00 sec) {noformat} The result of the query with SOME is obviously wrong. It should have returned empty set with warnings.

Alexander Barkov made changes - 2015-09-28 11:57

Description

{code}
SET timestamp=UNIXTIME_TIMESTAMP('2015-01-01 00:00:00');
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a TIME);
INSERT INTO t1 VALUES ('00:00:00'),('00:01:00');
SELECT 1 FROM t1 WHERE 2016 < SOME (SELECT CAST(a AS DATETIME) FROM t1);
{code}
returns two rows with no warnings:
{noformat}
+---+
| 1 |
+---+
| 1 |
| 1 |
+---+
2 rows in set (0.01 sec)
{noformat}

Now let's check which of the rows in SOME actually generated true condition:
{code}
SELECT * FROM t1 WHERE 2016 > CAST(a AS DATETIME);
SHOW WARNINGS;
{code}

Opps. It returns empty set with a warning:
{noformat}
+---------+------+----------------------------------+
| Level | Code | Message |
+---------+------+----------------------------------+
| Warning | 1292 | Incorrect datetime value: '2016' |
+---------+------+----------------------------------+
1 row in set (0.00 sec)
{noformat}

The result of the query with SOME is obviously wrong. It should have returned empty set with warnings.

{code}
SET timestamp=UNIXTIME_TIMESTAMP('2015-01-01 00:00:00');
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a TIME);
INSERT INTO t1 VALUES ('00:00:00'),('00:01:00');
SELECT 1 FROM t1 WHERE 2016 > SOME (SELECT CAST(a AS DATETIME) FROM t1);
{code}
returns two rows with no warnings:
{noformat}
+---+
| 1 |
+---+
| 1 |
| 1 |
+---+
2 rows in set (0.01 sec)
{noformat}

Now let's check which of the rows in SOME actually generated true condition:
{code}
SELECT * FROM t1 WHERE 2016 > CAST(a AS DATETIME);
SHOW WARNINGS;
{code}

Opps. It returns empty set with a warning:
{noformat}
+---------+------+----------------------------------+
| Level | Code | Message |
+---------+------+----------------------------------+
| Warning | 1292 | Incorrect datetime value: '2016' |
+---------+------+----------------------------------+
1 row in set (0.00 sec)
{noformat}

The result of the query with SOME is obviously wrong. It should have returned empty set with warnings.

Alexander Barkov made changes - 2015-09-30 11:55

Description

{code}
SET timestamp=UNIXTIME_TIMESTAMP('2015-01-01 00:00:00');
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a TIME);
INSERT INTO t1 VALUES ('00:00:00'),('00:01:00');
SELECT 1 FROM t1 WHERE 2016 > SOME (SELECT CAST(a AS DATETIME) FROM t1);
{code}
returns two rows with no warnings:
{noformat}
+---+
| 1 |
+---+
| 1 |
| 1 |
+---+
2 rows in set (0.01 sec)
{noformat}

Now let's check which of the rows in SOME actually generated true condition:
{code}
SELECT * FROM t1 WHERE 2016 > CAST(a AS DATETIME);
SHOW WARNINGS;
{code}

Opps. It returns empty set with a warning:
{noformat}
+---------+------+----------------------------------+
| Level | Code | Message |
+---------+------+----------------------------------+
| Warning | 1292 | Incorrect datetime value: '2016' |
+---------+------+----------------------------------+
1 row in set (0.00 sec)
{noformat}

The result of the query with SOME is obviously wrong. It should have returned empty set with warnings.

{code}
SET timestamp=UNIX_TIMESTAMP('2015-01-01 00:00:00');
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a TIME);
INSERT INTO t1 VALUES ('00:00:00'),('00:01:00');
SELECT 1 FROM t1 WHERE 2016 > SOME (SELECT CAST(a AS DATETIME) FROM t1);
{code}
returns two rows with no warnings:
{noformat}
+---+
| 1 |
+---+
| 1 |
| 1 |
+---+
2 rows in set (0.01 sec)
{noformat}

Now let's check which of the rows in SOME actually generated true condition:
{code}
SELECT * FROM t1 WHERE 2016 > CAST(a AS DATETIME);
SHOW WARNINGS;
{code}

Opps. It returns empty set with a warning:
{noformat}
+---------+------+----------------------------------+
| Level | Code | Message |
+---------+------+----------------------------------+
| Warning | 1292 | Incorrect datetime value: '2016' |
+---------+------+----------------------------------+
1 row in set (0.00 sec)
{noformat}

The result of the query with SOME is obviously wrong. It should have returned empty set with warnings.

Alexander Barkov made changes - 2015-09-30 13:21

Sprint

10.1.8-4 [ 16 ]

Alexander Barkov made changes - 2015-09-30 13:21

Rank

Ranked higher

Alexander Barkov made changes - 2015-09-30 13:22

Component/s		Temporal Types [ 11000 ]
Component/s	Data Definition - Temporary [ 10123 ]

Alexander Barkov made changes - 2015-09-30 13:23

Labels

upstream

Alexander Barkov added a comment - 2015-09-30 13:23

Also repeatable in MySQL-5.7.8

Alexander Barkov added a comment - 2015-09-30 13:23 Also repeatable in MySQL-5.7.8

Alexander Barkov made changes - 2015-09-30 15:08

Fix Version/s		10.1.8 [ 19605 ]
Fix Version/s	10.1 [ 16100 ]
Resolution		Fixed [ 1 ]
Status	Open [ 1 ]	Closed [ 6 ]

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

Workflow

MariaDB v3 [ 71801 ]

MariaDB v4 [ 149656 ]

People

Assignee:: Alexander Barkov

Reporter:: Alexander Barkov

Votes:: 0 Vote for this issue

Watchers:: 1 Start watching this issue

Dates

Created:: 2015-09-28 08:33

Updated:: 2015-09-30 15:08

Resolved:: 2015-09-30 15:08

Git Integration

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

MariaDB Server

Details

Description

Attachments

Activity

People

Dates

Git Integration