Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
11.4.1, 10.9(EOL)
-
None
-
None
-
Ubuntu 22.04
Description
Description
Sql1 and sql2 should return the same result if condition f1 in sql2 is true. Otherwise,sql2 should return nothing.
However,sql1 and sql2 return different value.
SQL1:
SELECT f1 FROM (SELECT (t1.c0 - (-1^1)) AS f1 FROM t1) AS t; |
|
SQL2:
SELECT f1 FROM (SELECT (t1.c0 - (-1^1)) AS f1 FROM t1) AS t WHERE f1; |
How to repeat
CREATE TABLE t1(c0 VARCHAR(100)); |
INSERT INTO t1 VALUES ('1'); |
|
version |
|
|
SELECT version();
|
|
|
+---------------------------------------+
|
| version() |
|
+---------------------------------------+
|
| 11.4.1-MariaDB-1:11.4.1+maria~ubu2204 |
|
+---------------------------------------+
|
1 row in set (0.00 sec)
|
SELECT f1 FROM (SELECT (t1.c0 - (-1^1)) AS f1 FROM t1) AS t;--sql1 |
|
|
+------------------------+ |
| f1 |
|
+------------------------+ |
| -1.8446744073709552e19 |
|
+------------------------+ |
1 row in set (0.00 sec) |
SELECT f1 FROM (SELECT (t1.c0 - (-1^1)) AS f1 FROM t1) AS t WHERE f1; --sql2 |
|
|
+------+ |
| f1 |
|
+------+ |
| 3 |
|
+------+ |
1 row in set (0.00 sec) |
I execute two sqls in MySQL 8.0+ , both of them return -18446744073709552000
Attachments
Activity
| Field | Original Value | New Value |
|---|---|---|
| Labels | need_feedback |
| Status | Open [ 1 ] | Needs Feedback [ 10501 ] |
| Summary | Incorrect result may caused by type transformation | Incorrect result may caused by type transformation（different result with MySQL) |
| Description |
h1. Description
Sql1 and sql2 should return the same result if condition *f1* in sql2 is true. Otherwise,sql2 should return nothing. However,sql1 and sql2 return different value. SQL1: {code:sql} SELECT f1 FROM (SELECT (t1.c0 - (-1^1)) AS f1 FROM t1) AS t; {code} SQL2: {code:sql} SELECT f1 FROM (SELECT (t1.c0 - (-1^1)) AS f1 FROM t1) AS t WHERE f1; {code} h1. How to repeat {code:sql} CREATE TABLE t1(c0 VARCHAR(100)); INSERT INTO t1 VALUES ('1'); {code} {noformat:title=version} SELECT version(); +---------------------------------------+ | version() | +---------------------------------------+ | 11.4.1-MariaDB-1:11.4.1+maria~ubu2204 | +---------------------------------------+ 1 row in set (0.00 sec) {noformat} {code:sql} SELECT f1 FROM (SELECT (t1.c0 - (-1^1)) AS f1 FROM t1) AS t;--sql1 +------------------------+ | f1 | +------------------------+ | -1.8446744073709552e19 | +------------------------+ 1 row in set (0.00 sec) {code} {code:sql} SELECT f1 FROM (SELECT (t1.c0 - (-1^1)) AS f1 FROM t1) AS t WHERE f1; --sql2 +------+ | f1 | +------+ | 3 | +------+ 1 row in set (0.00 sec) {code} |
h1. Description
Sql1 and sql2 should return the same result if condition *f1* in sql2 is true. Otherwise,sql2 should return nothing. However,sql1 and sql2 return different value. SQL1: {code:sql} SELECT f1 FROM (SELECT (t1.c0 - (-1^1)) AS f1 FROM t1) AS t; {code} SQL2: {code:sql} SELECT f1 FROM (SELECT (t1.c0 - (-1^1)) AS f1 FROM t1) AS t WHERE f1; {code} h1. How to repeat {code:sql} CREATE TABLE t1(c0 VARCHAR(100)); INSERT INTO t1 VALUES ('1'); {code} {noformat:title=version} SELECT version(); +---------------------------------------+ | version() | +---------------------------------------+ | 11.4.1-MariaDB-1:11.4.1+maria~ubu2204 | +---------------------------------------+ 1 row in set (0.00 sec) {noformat} {code:sql} SELECT f1 FROM (SELECT (t1.c0 - (-1^1)) AS f1 FROM t1) AS t;--sql1 +------------------------+ | f1 | +------------------------+ | -1.8446744073709552e19 | +------------------------+ 1 row in set (0.00 sec) {code} {code:sql} SELECT f1 FROM (SELECT (t1.c0 - (-1^1)) AS f1 FROM t1) AS t WHERE f1; --sql2 +------+ | f1 | +------+ | 3 | +------+ 1 row in set (0.00 sec) {code} *I execute two sqls in MySQL 8.0+ , both of them return -18446744073709552000* |
| Status | Needs Feedback [ 10501 ] | Open [ 1 ] |