Comment 3 for bug 780386
Revision history for this message
| Oleksandr "Sanja" Byelkin (sanja-byelkin) wrote Re: Diverging results with join_cache_level=0, join_cache_level=3 in maria-5.3 with ALL() subquery + NULLs | #3 |
If reduce number of rows in table t2 and have NULL first in the table wrong result will be independent of join_cache_level:
...
INSERT IGNORE INTO t2 VALUES (NULL,NULL),(5,0);
...
+SET SESSION join_cache_level = 0;
+SELECT a1.f3 AS r, a1.f3 < ALL ( SELECT f3 FROM t3 WHERE f3 = 1 ) FROM t2 AS a1 , t1;
+r a1.f3 < ALL ( SELECT f3 FROM t3 WHERE f3 = 1 )
+NULL 1
+NULL 1
+5 1
+5 1
+SELECT a1.f3 AS r FROM t2 AS a1 , t1 WHERE a1.f3 < ALL ( SELECT f3 FROM t3 WHERE f3 = 1 ) ;
+r
+5
+5
+SET SESSION join_cache_level = 3;
+SELECT a1.f3 AS r, a1.f3 < ALL ( SELECT f3 FROM t3 WHERE f3 = 1 ) FROM t2 AS a1 , t1;
+r a1.f3 < ALL ( SELECT f3 FROM t3 WHERE f3 = 1 )
+NULL 1
+5 1
+NULL 1
+5 1
+SELECT a1.f3 AS r FROM t2 AS a1 , t1 WHERE a1.f3 < ALL ( SELECT f3 FROM t3 WHERE f3 = 1 ) ;
+r
+5
+5