Bug in MariaDB

[akretschmer]

Falls jemand MariaDB nutzt:

test=*# select * from t1;
 id   
----   
  1   
  2   
  3   
(3 rows)   

Time: 0,173 ms
test=*# select * from t2;
 id   
----   
  4   
  5   
  6   
(3 rows)   

Time: 0,167 ms
test=*# select x.id, message from (select id from t1) x left join (select id, 1 as message from t2) y on x.id=y.id where coalesce(message,0) <> 0;   
 id | message   
----+---------   
(0 rows)   

Time: 0,359 ms

MySQL bringt dasselbe Resultat, MariaDB als Fork von MySQL was anderes ;-)

 

[akretschmer]

Falls jemand MariaDB nutzt:
MySQL bringt dasselbe Resultat, MariaDB als Fork von MySQL was anderes ;-)

Mein Freund Erkan hat es auch schon reportet: https://mariadb.atlassian.net/browse/MDEV-6892

 

[BerndB]

Wo ist das Problem ? ( Falls einer MariDB nutzt )

MariaDB [tmp]> select Version();
+-----------------+
| Version()       |
+-----------------+
| 10.0.12-MariaDB |
+-----------------+
1 row in set (0.00 sec)

MariaDB [tmp]> select * from t1;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
+----+
3 rows in set (0.00 sec)

MariaDB [tmp]> select * from t2;
+----+
| id |
+----+
|  4 |
|  5 |
|  6 |
+----+
3 rows in set (0.00 sec)

MariaDB [tmp]> select x.id, message from (select id from t1) x left join (select id, 1 as message from t2) y on x.id=y.id where coalesce(message,0) <> 0;
+----+---------+
| id | message |
+----+---------+
|  1 |       1 |
|  2 |       1 |
|  3 |       1 |
+----+---------+
3 rows in set (0.00 sec)

MariaDB [tmp]>

 

[akretschmer]

Wo ist das Problem ? ( Falls einer MariDB nutzt )

Das es falsch ist.

 

[akretschmer]

Das es falsch ist.

Hint: erweitere die Select-Liste um coalesce(message,0) und schau, ob die Spalte der Bedingungung <> 0 genügt.

test=*# select x.id, coalesce(message,0), message from (select id from t1) x left join (select id, 1 as message from t2)y on x.id=y.id;
 id | coalesce | message
----+----------+---------
  1 |  0 |
  2 |  0 |
  3 |  0 |
(3 rows)

Die Maria meint also daß 0 <> 0 ist. Meine jüngste Tochter, erste Klasse, erkennt den Fehler ;-)

 

[BerndB]

Darum meine Frage.

MariaDB [tmp]> select x.id, coalesce(message,0), message from (select id from t1) x left join (select id, 1 as message from t2)y on x.id=y.id;
+----+---------------------+---------+
| id | coalesce(message,0) | message |
+----+---------------------+---------+
|  1 |                   1 |       1 |
|  2 |                   1 |       1 |
|  3 |                   1 |       1 |
+----+---------------------+---------+
3 rows in set (0.00 sec)

 

[akretschmer]

Darum meine Frage.

MariaDB [tmp]> select x.id, coalesce(message,0), message from (select id from t1) x left join (select id, 1 as message from t2)y on x.id=y.id;
+----+---------------------+---------+
| id | coalesce(message,0) | message |
+----+---------------------+---------+
|  1 |                   1 |       1 |
|  2 |                   1 |       1 |
|  3 |                   1 |       1 |
+----+---------------------+---------+
3 rows in set (0.00 sec)

Dann sitzt der Fehler ja noch tiefer. Was es nicht wirklich besser macht.