1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
|
--echo #
--echo # Bug#30311271 RAND() < RAND() RESULTS IN IMPOSSIBLE WHERE
--echo #
--echo # See that the optimizer does not consider two instances of RAND() as
--echo # equal.
--replace_result 0.00..0.00 0.00
EXPLAIN FORMAT=tree SELECT 1 WHERE RAND() < RAND();
--echo #
--echo # Bug#33535379 Wrong value for RAND() expression when aggregating on empty table.
--echo #
CREATE TABLE t ( i1 INT, i2 INT);
# Use a simple random expression on an empty table.
ANALYZE TABLE t;
let $query= INSERT INTO t SELECT MAX(i1), FLOOR(RAND(0)*1000) FROM t;
eval EXPLAIN $query;
eval $query;
SELECT * FROM t;
# Use a simple random expression on a non-empty table.
INSERT INTO t VALUES (1,1),(2,2),(3,3);
ANALYZE TABLE t;
eval EXPLAIN $query;
eval $query;
SELECT * FROM t;
# Use a combined random and INNER_TABLE_BIT expression on an empty table.
DELETE FROM t;
ANALYZE TABLE t;
let $query= INSERT INTO t SELECT MAX(i1), FLOOR(RAND(0)*1000)
+ EXTRACT(YEAR FROM NOW()) DIV 1000 FROM t;
eval EXPLAIN $query;
eval $query;
SELECT * FROM t;
# Use a combined random and INNER_TABLE_BIT expression on a non-empty table.
INSERT INTO t VALUES (1,1),(2,2),(3,3);
ANALYZE TABLE t;
eval EXPLAIN $query;
eval $query;
SELECT * FROM t;
# Use a combined random and INNER_TABLE_BIT expression with GROUP BY
# on a non-empty table.
let $query= INSERT INTO t SELECT MAX(i1), FLOOR(RAND(0)*1000)
+ EXTRACT(YEAR FROM NOW()) DIV 1000 FROM t GROUP BY i2;
ANALYZE TABLE t;
eval EXPLAIN $query;
eval $query;
# It depends on the plan which i1 is paired with which i2, so check
# each column separately.
--sorted_result
SELECT i1 FROM t;
--sorted_result
SELECT i2 FROM t;
DROP TABLE t;
--echo #
--echo # Bug#33834514: 'RAND(0)' gives inconsistent results with hypergraph
--echo #
CREATE TABLE t (i INT);
INSERT INTO t VALUES (1), (2), (3);
# An uncorrelated derived table on the inner side of a join should
# only be materialized once. The hypergraph optimizer used to
# rematerialize it for every evaluation of the inner side if it was
# non-deterministic.
SELECT r FROM t LEFT JOIN (SELECT RAND(0) AS r) AS dt ON TRUE;
# If the inner side is a correlated derived table, it should be
# rematerialized for each row in the outer table. This was done
# correctly also before the fix. Verify that it still is.
--sorted_result
SELECT r FROM t LEFT JOIN LATERAL (SELECT i, RAND(0) AS r) AS dt ON TRUE;
DROP TABLE t;
|
