81 lines
2.3 KiB
Text
81 lines
2.3 KiB
Text
--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;
|