diff options
author | Igor Babaev <igor@askmonty.org> | 2013-08-14 20:37:38 -0700 |
---|---|---|
committer | Igor Babaev <igor@askmonty.org> | 2013-08-14 20:37:38 -0700 |
commit | 7ba78277b4d76649501e85f05be2780e00ffc9c3 (patch) | |
tree | 69f83e400da760d365fde50f4dd5e921d339c8c7 | |
parent | edcae48734058eadd5ed29330b0a525544fa36ed (diff) | |
parent | 6dd9f049ab2c8771feabec447cff66b703392046 (diff) | |
download | mariadb-git-7ba78277b4d76649501e85f05be2780e00ffc9c3.tar.gz |
Merge 5.1->5.2
-rw-r--r-- | mysql-test/r/range.result | 44 | ||||
-rw-r--r-- | mysql-test/t/range.test | 38 | ||||
-rw-r--r-- | sql/spatial.cc | 88 | ||||
-rw-r--r-- | sql/spatial.h | 24 | ||||
-rw-r--r-- | sql/sql_select.cc | 11 |
5 files changed, 141 insertions, 64 deletions
diff --git a/mysql-test/r/range.result b/mysql-test/r/range.result index 8a416de66ee..55958e2c542 100644 --- a/mysql-test/r/range.result +++ b/mysql-test/r/range.result @@ -700,14 +700,14 @@ INSERT INTO t1 VALUES 'd8c4177d09f8b11f5.52725521'); EXPLAIN SELECT s.oxid FROM t1 v, t1 s -WHERE s.oxrootid = 'd8c4177d09f8b11f5.52725521' AND +WHERE v.oxrootid ='d8c4177d09f8b11f5.52725521' AND s.oxleft > v.oxleft AND s.oxleft < v.oxright; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE v ref OXLEFT,OXRIGHT,OXROOTID OXROOTID 34 const 5 Using where 1 SIMPLE s ALL OXLEFT NULL NULL NULL 6 Range checked for each record (index map: 0x4) SELECT s.oxid FROM t1 v, t1 s -WHERE s.oxrootid = 'd8c4177d09f8b11f5.52725521' AND +WHERE v.oxrootid ='d8c4177d09f8b11f5.52725521' AND s.oxleft > v.oxleft AND s.oxleft < v.oxright; oxid @@ -1884,6 +1884,46 @@ AAA AAA AAA AAAA AAAA AAAA AAAAA AAAAA AAAAA DROP TABLE t1; +# +# mdev-4894: Poor performance with unnecessary +# (bug#70021) 'Range checked for each record' +# +create table t1( key1 int not null, INDEX i1(key1) ); +insert into t1 values (1),(2),(3),(4),(5),(6),(7),(8); +insert into t1 select key1+8 from t1; +insert into t1 select key1+16 from t1; +insert into t1 select key1+32 from t1; +insert into t1 select key1+64 from t1; +insert into t1 select key1+128 from t1; +insert into t1 select key1+256 from t1; +insert into t1 select key1+512 from t1; +alter table t1 add key2 int not null, add index i2(key2); +update t1 set key2=key1; +analyze table t1; +Table Op Msg_type Msg_text +test.t1 analyze status OK +create table t2 (a int); +insert into t2 values (1),(2),(3),(4),(5),(6),(7),(8); +insert into t2 select a+16 from t2; +insert into t2 select a+32 from t2; +insert into t2 select a+64 from t2; +explain +select count(*) from t2 left join t1 on (t1.key1 < 3 or t1.key1 > 1020) and t1.key2 < 1000; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 64 +1 SIMPLE t1 range i1,i2 i1 4 NULL 78 Using where +select count(*) from t2 left join t1 on (t1.key1 < 3 or t1.key1 > 1020) and t1.key2 < 1000; +count(*) +128 +explain +select count(*) from t2 left join t1 on (t1.key1 < 3 or t1.key1 > 1020) and t1.key2 < t2.a; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 64 +1 SIMPLE t1 range i1,i2 i1 4 NULL 78 Using where +select count(*) from t2 left join t1 on (t1.key1 < 3 or t1.key1 > 1020) and t1.key2 < t2.a; +count(*) +126 +drop table t1,t2; End of 5.1 tests # # LP Bug #533117: Wrong use_count in SEL_ARG trees diff --git a/mysql-test/t/range.test b/mysql-test/t/range.test index c3f6aad4638..7fb9f21282a 100644 --- a/mysql-test/t/range.test +++ b/mysql-test/t/range.test @@ -557,12 +557,12 @@ INSERT INTO t1 VALUES EXPLAIN SELECT s.oxid FROM t1 v, t1 s - WHERE s.oxrootid = 'd8c4177d09f8b11f5.52725521' AND + WHERE v.oxrootid ='d8c4177d09f8b11f5.52725521' AND s.oxleft > v.oxleft AND s.oxleft < v.oxright; SELECT s.oxid FROM t1 v, t1 s - WHERE s.oxrootid = 'd8c4177d09f8b11f5.52725521' AND + WHERE v.oxrootid ='d8c4177d09f8b11f5.52725521' AND s.oxleft > v.oxleft AND s.oxleft < v.oxright; @@ -1468,6 +1468,40 @@ SELECT * FROM t1 IGNORE INDEX(PRIMARY) WHERE F1 BETWEEN 'A ' AND DROP TABLE t1; +--echo # +--echo # mdev-4894: Poor performance with unnecessary +--echo # (bug#70021) 'Range checked for each record' +--echo # + +create table t1( key1 int not null, INDEX i1(key1) ); +insert into t1 values (1),(2),(3),(4),(5),(6),(7),(8); +insert into t1 select key1+8 from t1; +insert into t1 select key1+16 from t1; +insert into t1 select key1+32 from t1; +insert into t1 select key1+64 from t1; +insert into t1 select key1+128 from t1; +insert into t1 select key1+256 from t1; +insert into t1 select key1+512 from t1; + +alter table t1 add key2 int not null, add index i2(key2); +update t1 set key2=key1; +analyze table t1; + +create table t2 (a int); +insert into t2 values (1),(2),(3),(4),(5),(6),(7),(8); +insert into t2 select a+16 from t2; +insert into t2 select a+32 from t2; +insert into t2 select a+64 from t2; + +explain +select count(*) from t2 left join t1 on (t1.key1 < 3 or t1.key1 > 1020) and t1.key2 < 1000; +select count(*) from t2 left join t1 on (t1.key1 < 3 or t1.key1 > 1020) and t1.key2 < 1000; +explain +select count(*) from t2 left join t1 on (t1.key1 < 3 or t1.key1 > 1020) and t1.key2 < t2.a; +select count(*) from t2 left join t1 on (t1.key1 < 3 or t1.key1 > 1020) and t1.key2 < t2.a; + +drop table t1,t2; + --echo End of 5.1 tests --echo # diff --git a/sql/spatial.cc b/sql/spatial.cc index 52110960f96..95579b5dea2 100644 --- a/sql/spatial.cc +++ b/sql/spatial.cc @@ -394,19 +394,18 @@ const char *Geometry::append_points(String *txt, uint32 n_points, const char *Geometry::get_mbr_for_points(MBR *mbr, const char *data, uint offset) const { - uint32 n_points; + uint32 points; /* read number of points */ if (no_data(data, 4)) return 0; - n_points= uint4korr(data); + points= uint4korr(data); data+= 4; - if (n_points > max_n_points || - no_data(data, (POINT_DATA_SIZE + offset) * n_points)) + if (not_enough_points(data, points, offset)) return 0; /* Calculate MBR for points */ - while (n_points--) + while (points--) { data+= offset; mbr->add_xy(data, data + SIZEOF_STORED_DOUBLE); @@ -485,12 +484,16 @@ const Geometry::Class_info *Gis_point::get_class_info() const uint32 Gis_line_string::get_data_size() const { - uint32 n_points, size; - if (no_data(m_data, 4) || - (n_points= uint4korr(m_data)) > max_n_points || - no_data(m_data, (size= 4 + n_points * POINT_DATA_SIZE))) + uint32 n_points; + if (no_data(m_data, 4)) return GET_SIZE_ERROR; - return size; + + n_points= uint4korr(m_data); + + if (not_enough_points(m_data + 4, n_points)) + return GET_SIZE_ERROR; + + return 4 + n_points * POINT_DATA_SIZE; } @@ -529,9 +532,8 @@ uint Gis_line_string::init_from_wkb(const char *wkb, uint len, const char *wkb_end; Gis_point p; - if (len < 4 || - (n_points= wkb_get_uint(wkb, bo)) < 1 || - n_points > max_n_points) + if (len < 4 || (n_points= wkb_get_uint(wkb, bo)) < 1 || + ((len - 4) / POINT_DATA_SIZE) < n_points) return 0; proper_length= 4 + n_points * POINT_DATA_SIZE; @@ -560,8 +562,8 @@ bool Gis_line_string::get_data_as_wkt(String *txt, const char **end) const n_points= uint4korr(data); data += 4; - if (n_points < 1 || n_points > max_n_points || - no_data(data, SIZEOF_STORED_DOUBLE * 2 * n_points) || + if (n_points < 1 || + not_enough_points(data, n_points) || txt->reserve(((MAX_DIGITS_IN_DOUBLE + 1)*2 + 1) * n_points)) return 1; @@ -598,8 +600,7 @@ int Gis_line_string::geom_length(double *len) const return 1; n_points= uint4korr(data); data+= 4; - if (n_points < 1 || n_points > max_n_points || - no_data(data, SIZEOF_STORED_DOUBLE * 2 * n_points)) + if (n_points < 1 || not_enough_points(data, n_points)) return 1; get_point(&prev_x, &prev_y, data); @@ -633,8 +634,7 @@ int Gis_line_string::is_closed(int *closed) const return 0; } data+= 4; - if (n_points == 0 || n_points > max_n_points || - no_data(data, SIZEOF_STORED_DOUBLE * 2 * n_points)) + if (n_points == 0 || not_enough_points(data, n_points)) return 1; /* Get first point */ @@ -669,8 +669,7 @@ int Gis_line_string::end_point(String *result) const if (no_data(m_data, 4)) return 1; n_points= uint4korr(m_data); - if (n_points == 0 || n_points > max_n_points || - no_data(m_data, POINT_DATA_SIZE * n_points)) + if (n_points == 0 || not_enough_points(m_data+4, n_points)) return 1; return create_point(result, m_data + 4 + (n_points - 1) * POINT_DATA_SIZE); } @@ -683,9 +682,7 @@ int Gis_line_string::point_n(uint32 num, String *result) const return 1; num--; n_points= uint4korr(m_data); - if (num >= n_points || - num > max_n_points || // means (num > n_points || num < 1) - no_data(m_data, num * POINT_DATA_SIZE)) + if (num >= n_points || not_enough_points(m_data+4, n_points)) return 1; return create_point(result, m_data + 4 + num*POINT_DATA_SIZE); @@ -713,7 +710,7 @@ uint32 Gis_polygon::get_data_size() const while (n_linear_rings--) { if (no_data(data, 4) || - (n_points= uint4korr(data)) > max_n_points) + not_enough_points(data+4, n_points= uint4korr(data))) return GET_SIZE_ERROR; data+= 4 + n_points*POINT_DATA_SIZE; } @@ -813,8 +810,7 @@ bool Gis_polygon::get_data_as_wkt(String *txt, const char **end) const return 1; n_points= uint4korr(data); data+= 4; - if (n_points > max_n_points || - no_data(data, (SIZEOF_STORED_DOUBLE*2) * n_points) || + if (not_enough_points(data, n_points) || txt->reserve(2 + ((MAX_DIGITS_IN_DOUBLE + 1) * 2 + 1) * n_points)) return 1; txt->qs_append('('); @@ -868,8 +864,8 @@ int Gis_polygon::area(double *ar, const char **end_of_data) const if (no_data(data, 4)) return 1; n_points= uint4korr(data); - if (n_points == 0 || n_points > max_n_points || - no_data(data, (SIZEOF_STORED_DOUBLE*2) * n_points)) + if (n_points == 0 || + not_enough_points(data, n_points)) return 1; get_point(&prev_x, &prev_y, data+4); data+= (4+SIZEOF_STORED_DOUBLE*2); @@ -905,8 +901,7 @@ int Gis_polygon::exterior_ring(String *result) const n_points= uint4korr(data); data+= 4; length= n_points * POINT_DATA_SIZE; - if (n_points > max_n_points || - no_data(data, length) || result->reserve(1+4+4+ length)) + if (not_enough_points(data, n_points) || result->reserve(1+4+4+ length)) return 1; result->q_append((char) wkb_ndr); @@ -952,7 +947,7 @@ int Gis_polygon::interior_ring_n(uint32 num, String *result) const n_points= uint4korr(data); points_size= n_points * POINT_DATA_SIZE; data+= 4; - if (no_data(data, points_size) || result->reserve(1+4+4+ points_size)) + if (not_enough_points(data, n_points) || result->reserve(1+4+4+ points_size)) return 1; result->q_append((char) wkb_ndr); @@ -989,8 +984,7 @@ int Gis_polygon::centroid_xy(double *x, double *y) const return 1; org_n_points= n_points= uint4korr(data); data+= 4; - if (n_points == 0 || n_points > max_n_points || - no_data(data, (SIZEOF_STORED_DOUBLE*2) * n_points)) + if (n_points == 0 || not_enough_points(data, n_points)) return 1; get_point(&prev_x, &prev_y, data); data+= (SIZEOF_STORED_DOUBLE*2); @@ -1050,13 +1044,12 @@ const Geometry::Class_info *Gis_polygon::get_class_info() const uint32 Gis_multi_point::get_data_size() const { uint32 n_points; - uint32 size; if (no_data(m_data, 4) || - (n_points= uint4korr(m_data)) > max_n_points || - no_data(m_data, (size= 4 + n_points*(POINT_DATA_SIZE + WKB_HEADER_SIZE)))) + not_enough_points(m_data+4, (n_points= uint4korr(m_data)), + WKB_HEADER_SIZE)) return GET_SIZE_ERROR; - return size; + return 4 + n_points * (POINT_DATA_SIZE + WKB_HEADER_SIZE); } @@ -1124,9 +1117,7 @@ bool Gis_multi_point::get_data_as_wkt(String *txt, const char **end) const return 1; n_points= uint4korr(m_data); - if (n_points > max_n_points || - no_data(m_data+4, - n_points * (SIZEOF_STORED_DOUBLE * 2 + WKB_HEADER_SIZE)) || + if (not_enough_points(m_data+4, n_points, WKB_HEADER_SIZE) || txt->reserve(((MAX_DIGITS_IN_DOUBLE + 1) * 2 + 1) * n_points)) return 1; *end= append_points(txt, n_points, m_data+4, WKB_HEADER_SIZE); @@ -1189,7 +1180,8 @@ uint32 Gis_multi_line_string::get_data_size() const while (n_line_strings--) { if (no_data(data, WKB_HEADER_SIZE + 4) || - (n_points= uint4korr(data + WKB_HEADER_SIZE)) > max_n_points) + not_enough_points(data + WKB_HEADER_SIZE+4, + (n_points= uint4korr(data + WKB_HEADER_SIZE)))) return GET_SIZE_ERROR; data+= (WKB_HEADER_SIZE + 4 + n_points*POINT_DATA_SIZE); } @@ -1286,8 +1278,7 @@ bool Gis_multi_line_string::get_data_as_wkt(String *txt, return 1; n_points= uint4korr(data + WKB_HEADER_SIZE); data+= WKB_HEADER_SIZE + 4; - if (n_points > max_n_points || - no_data(data, n_points * (SIZEOF_STORED_DOUBLE*2)) || + if (not_enough_points(data, n_points) || txt->reserve(2 + ((MAX_DIGITS_IN_DOUBLE + 1) * 2 + 1) * n_points)) return 1; txt->qs_append('('); @@ -1348,7 +1339,7 @@ int Gis_multi_line_string::geometry_n(uint32 num, String *result) const return 1; n_points= uint4korr(data + WKB_HEADER_SIZE); length= WKB_HEADER_SIZE + 4+ POINT_DATA_SIZE * n_points; - if (n_points > max_n_points || no_data(data, length)) + if (not_enough_points(data+WKB_HEADER_SIZE+4, n_points)) return 1; if (!--num) break; @@ -1448,7 +1439,7 @@ uint32 Gis_multi_polygon::get_data_size() const while (n_linear_rings--) { if (no_data(data, 4) || - (n_points= uint4korr(data)) > max_n_points) + not_enough_points(data+4, (n_points= uint4korr(data)))) return GET_SIZE_ERROR; data+= 4 + n_points * POINT_DATA_SIZE; } @@ -1552,8 +1543,7 @@ bool Gis_multi_polygon::get_data_as_wkt(String *txt, const char **end) const return 1; uint32 n_points= uint4korr(data); data+= 4; - if (n_points > max_n_points || - no_data(data, (SIZEOF_STORED_DOUBLE * 2) * n_points) || + if (not_enough_points(data, n_points) || txt->reserve(2 + ((MAX_DIGITS_IN_DOUBLE + 1) * 2 + 1) * n_points, 512)) return 1; @@ -1636,7 +1626,7 @@ int Gis_multi_polygon::geometry_n(uint32 num, String *result) const if (no_data(data, 4)) return 1; n_points= uint4korr(data); - if (n_points > max_n_points) + if (not_enough_points(data + 4, n_points)) return 1; data+= 4 + POINT_DATA_SIZE * n_points; } diff --git a/sql/spatial.h b/sql/spatial.h index d7632c11143..0b1b8afd35c 100644 --- a/sql/spatial.h +++ b/sql/spatial.h @@ -197,11 +197,6 @@ struct Geometry_buffer; class Geometry { public: - // Maximum number of points in feature that can fit into String - static const uint32 max_n_points= - (uint32) (INT_MAX32 - WKB_HEADER_SIZE - 4 /* n_points */) / - POINT_DATA_SIZE; -public: Geometry() {} /* Remove gcc warning */ virtual ~Geometry() {} /* Remove gcc warning */ static void *operator new(size_t size, void *buffer) @@ -326,6 +321,25 @@ protected: { return (cur_data + data_amount > m_data_end); } + + /** + Check if there're enough points remaining as requested + + Need to perform the calculation in logical units, since multiplication + can overflow the size data type. + + @arg data pointer to the begining of the points array + @arg expected_points number of points expected + @arg extra_point_space extra space for each point element in the array + @return true if there are not enough points + */ + inline bool not_enough_points(const char *data, uint32 expected_points, + uint32 extra_point_space = 0) const + { + return (m_data_end < data || + (expected_points > ((m_data_end - data) / + (POINT_DATA_SIZE + extra_point_space)))); + } const char *m_data; const char *m_data_end; }; diff --git a/sql/sql_select.cc b/sql/sql_select.cc index eaac6482f67..3641b14183b 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -6632,19 +6632,18 @@ make_join_select(JOIN *join,SQL_SELECT *select,COND *cond) else { sel->needed_reg=tab->needed_reg; - sel->quick_keys.clear_all(); } + sel->quick_keys= tab->table->quick_keys; if (!sel->quick_keys.is_subset(tab->checked_keys) || !sel->needed_reg.is_subset(tab->checked_keys)) { - tab->keys=sel->quick_keys; - tab->keys.merge(sel->needed_reg); tab->use_quick= (!sel->needed_reg.is_clear_all() && - (select->quick_keys.is_clear_all() || - (select->quick && - (select->quick->records >= 100L)))) ? + (sel->quick_keys.is_clear_all() || + (sel->quick && + (sel->quick->records >= 100L)))) ? 2 : 1; sel->read_tables= used_tables & ~current_map; + sel->quick_keys.clear_all(); } if (i != join->const_tables && tab->use_quick != 2) { /* Read with cache */ |