| 1 |
# pushed_join.inc |
| 2 |
# |
| 3 |
# SUMMARY |
| 4 |
# |
| 5 |
# Run the $push_query and verify it's push properties |
| 6 |
# according to the given arguments by looking at the handler |
| 7 |
# status variables and examining the EXPLAIN output. |
| 8 |
# |
| 9 |
# USAGE |
| 10 |
# |
| 11 |
# let $push_query= <select statement>; |
| 12 |
# let $push_expected= <expected number of pushed joins>; |
| 13 |
# [let $push_message= <message>;] |
| 14 |
# --source pushed_join_check_pushed.inc |
| 15 |
# |
| 16 |
# PARAMETERS |
| 17 |
# $push_query=<select statement> |
| 18 |
# The select query to evaluate for pushed join |
| 19 |
# |
| 20 |
# $push_expected=<number of pushed joins> |
| 21 |
# The expected number of pushed joins executed by query |
| 22 |
# |
| 23 |
# $push_message=<message> |
| 24 |
# Expect the query to contain the given $message |
| 25 |
# in EXPLAIN or SHOW WARNINGS output |
| 26 |
# |
| 27 |
# |
| 28 |
|
| 29 |
if (!$push_query) |
| 30 |
{ |
| 31 |
die Need $push_query as parameter to pushed_join_check_pushed.inc; |
| 32 |
} |
| 33 |
|
| 34 |
# Save ndb_pushed_* before query |
| 35 |
let $defined_before = |
| 36 |
`select VARIABLE_VALUE from performance_schema.session_status |
| 37 |
where variable_name like 'ndb_pushed_queries_defined'`; |
| 38 |
let $executed_before = |
| 39 |
`select VARIABLE_VALUE from performance_schema.session_status |
| 40 |
where variable_name like 'ndb_pushed_queries_executed'`; |
| 41 |
let $dropped_before = |
| 42 |
`select VARIABLE_VALUE from performance_schema.session_status |
| 43 |
where variable_name like 'ndb_pushed_queries_dropped'`; |
| 44 |
let $reads_before = |
| 45 |
`select VARIABLE_VALUE from performance_schema.session_status |
| 46 |
where variable_name like 'ndb_pushed_reads'`; |
| 47 |
|
| 48 |
# Run the query |
| 49 |
--disable_result_log ONCE |
| 50 |
eval $push_query; |
| 51 |
|
| 52 |
# Save ndb_pushed_* after query |
| 53 |
let $defined_after = |
| 54 |
`select VARIABLE_VALUE from performance_schema.session_status |
| 55 |
where variable_name like 'ndb_pushed_queries_defined'`; |
| 56 |
let $executed_after = |
| 57 |
`select VARIABLE_VALUE from performance_schema.session_status |
| 58 |
where variable_name like 'ndb_pushed_queries_executed'`; |
| 59 |
let $dropped_after = |
| 60 |
`select VARIABLE_VALUE from performance_schema.session_status |
| 61 |
where variable_name like 'ndb_pushed_queries_dropped'`; |
| 62 |
let $reads_after = |
| 63 |
`select VARIABLE_VALUE from performance_schema.session_status |
| 64 |
where variable_name like 'ndb_pushed_reads'`; |
| 65 |
|
| 66 |
# Calculate number of pushed queries generated by the query |
| 67 |
let $defined = `select $defined_after - $defined_before`; |
| 68 |
let $executed = `select $executed_after - $executed_before`; |
| 69 |
let $dropped = `select $dropped_after - $dropped_before`; |
| 70 |
let $reads = `select $reads_after - $reads_before`; |
| 71 |
|
| 72 |
#echo defined: $defined; |
| 73 |
#echo executed: $executed; |
| 74 |
#echo dropped: $dropped; |
| 75 |
#echo reads: $reads; |
| 76 |
|
| 77 |
#eval EXPLAIN $push_query; |
| 78 |
|
| 79 |
if ($push_expected) |
| 80 |
{ |
| 81 |
# |
| 82 |
# The query is expected to be pushed, check that counters have |
| 83 |
# been incremented appropriately |
| 84 |
# |
| 85 |
|
| 86 |
# More than one pushed query must have been defined |
| 87 |
if (!$defined) |
| 88 |
{ |
| 89 |
--echo !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! |
| 90 |
--echo - push_query: '$push_query' |
| 91 |
--echo - defined: $defined |
| 92 |
--echo - executed: $executed |
| 93 |
--echo - dropped: $dropped |
| 94 |
--echo !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! |
| 95 |
--die No pushed join defined for expected push! |
| 96 |
} |
| 97 |
|
| 98 |
# More than one pushed query must have been executed unless |
| 99 |
# dropped |
| 100 |
if (!$executed) |
| 101 |
{ |
| 102 |
if (!$dropped) |
| 103 |
{ |
| 104 |
--echo !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! |
| 105 |
--echo - push_query: '$push_query' |
| 106 |
--echo - defined: $defined |
| 107 |
--echo - executed: $executed |
| 108 |
--echo - dropped: $dropped |
| 109 |
--echo - reads: $reads |
| 110 |
--echo !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! |
| 111 |
--die No pushed join executed for expected push! |
| 112 |
} |
| 113 |
} |
| 114 |
|
| 115 |
|
| 116 |
if ($dropped) |
| 117 |
{ |
| 118 |
# Print a message to log telling about the drop |
| 119 |
--echo Dropped $dropped pushed joins |
| 120 |
} |
| 121 |
|
| 122 |
# The number of reads should be greater then zero unless |
| 123 |
# all pushed joins where dropped |
| 124 |
if ($reads == 0) |
| 125 |
{ |
| 126 |
# No reads (although queries was defined and executed) |
| 127 |
|
| 128 |
# Allow zero read when all defined queries have been dropped |
| 129 |
if ($defined != $dropped) |
| 130 |
{ |
| 131 |
--echo !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! |
| 132 |
--echo - push_query: '$push_query' |
| 133 |
--echo - defined: $defined |
| 134 |
--echo - executed: $executed |
| 135 |
--echo - dropped: $dropped |
| 136 |
--echo - reads: $reads |
| 137 |
--echo !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! |
| 138 |
--die No pushed reads although join was pushed! |
| 139 |
} |
| 140 |
|
| 141 |
# Print a message to log telling about this condition |
| 142 |
--echo No pushed reads since all defined were dropped |
| 143 |
} |
| 144 |
} |
| 145 |
|
| 146 |
|
| 147 |
if (!$push_expected) |
| 148 |
{ |
| 149 |
# |
| 150 |
# The query is expected to not be pushed, check that counters have |
| 151 |
# been incremented appropriately |
| 152 |
# |
| 153 |
|
| 154 |
if ($defined) |
| 155 |
{ |
| 156 |
# There was a pushed join defined |
| 157 |
--echo !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! |
| 158 |
--echo - push_query: '$push_query' |
| 159 |
--echo - defined: $defined |
| 160 |
--echo - executed: $executed |
| 161 |
--echo - dropped: $dropped |
| 162 |
--echo - reads: $reads |
| 163 |
--echo !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! |
| 164 |
--die Found defined pushes when push down was not expected! |
| 165 |
} |
| 166 |
|
| 167 |
# There should not be any executed when none defined |
| 168 |
if ($executed) |
| 169 |
{ |
| 170 |
# There was a pushed join executed |
| 171 |
--echo !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! |
| 172 |
--echo - push_query: '$push_query' |
| 173 |
--echo - defined: $defined |
| 174 |
--echo - executed: $executed |
| 175 |
--echo - dropped: $dropped |
| 176 |
--echo !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! |
| 177 |
--die Found executed pushes when push down was not expected! |
| 178 |
} |
| 179 |
|
| 180 |
# There should not be any dropped when none defined |
| 181 |
if ($executed) |
| 182 |
{ |
| 183 |
# There was a pushed join dropped |
| 184 |
--echo !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! |
| 185 |
--echo - push_query: '$push_query' |
| 186 |
--echo - defined: $defined |
| 187 |
--echo - executed: $executed |
| 188 |
--echo - dropped: $dropped |
| 189 |
--echo !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! |
| 190 |
--die Found dropped pushes when push down was not expected! |
| 191 |
} |
| 192 |
|
| 193 |
# There should not be any reads when none defined |
| 194 |
if ($reads) |
| 195 |
{ |
| 196 |
# There was pushed join reads |
| 197 |
--echo !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! |
| 198 |
--echo - push_query: '$push_query' |
| 199 |
--echo - defined: $defined |
| 200 |
--echo - executed: $executed |
| 201 |
--echo - dropped: $dropped |
| 202 |
--echo !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! |
| 203 |
--die Found pushed reads when push down was not expected! |
| 204 |
} |
| 205 |
} |
| 206 |
|
| 207 |
if ($push_message) |
| 208 |
{ |
| 209 |
# |
| 210 |
# Check that EXPLAIN or the warnings contains the expected message |
| 211 |
# - dump the EXPLAIN to a file and load it back into a table |
| 212 |
# which then is queried with a REGEXP |
| 213 |
# |
| 214 |
--disable_query_log |
| 215 |
CREATE TEMPORARY TABLE messages(txt varchar(1024)); |
| 216 |
|
| 217 |
# Dump EXPLAIN to file |
| 218 |
let $dump_file = $MYSQLTEST_VARDIR/tmp/explain.txt; |
| 219 |
--output $dump_file |
| 220 |
eval EXPLAIN FORMAT=TRADITIONAL $push_query; |
| 221 |
|
| 222 |
eval LOAD DATA INFILE '$dump_file' INTO TABLE messages |
| 223 |
FIELDS TERMINATED BY '\n'; |
| 224 |
--remove_file $dump_file |
| 225 |
|
| 226 |
#SELECT * FROM messages; |
| 227 |
|
| 228 |
# check if $message contains the expected message |
| 229 |
if (!`SELECT count(txt) FROM messages WHERE txt REGEXP $push_message`) |
| 230 |
{ |
| 231 |
--echo !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! |
| 232 |
--echo Could not find expected message in EXPLAIN or SHOW WARNINGS! |
| 233 |
--echo - expected: $push_message |
| 234 |
--echo - found: |
| 235 |
SELECT * FROM messages; |
| 236 |
--die Did not find expected push message in EXPLAIN |
| 237 |
} |
| 238 |
DROP TABLE messages; |
| 239 |
--enable_query_log |
| 240 |
|
| 241 |
--echo Expected push message found in EXPLAIN; |
| 242 |
} |
| 243 |
# Add empty new line |
| 244 |
--echo |
| 245 |
|
| 246 |
# Reset the argument variables to detect missing assignment |
| 247 |
let $push_query=; |
| 248 |
let $push_expected=; |
| 249 |
let $push_message=; |
| 250 |
|