Skip to content

Commit 24d148d

Browse files
author
[email protected]
committed
sql_select.cc:
 Fixed bug #11412. Reversed the patch of cs 1.1934 for the function create_tmp_table. Modified the function to support tem_ref objects created for view fields. item_buff.cc: Fixed bug #11412. Modified implementation of new_Cached_item to support cacheing of view fields. item.h: Fixed bug #11412. Changed implementation of Item_ref::get_tmp_table_field and added Item_ref::get_tmp_table_item to support Item_ref objects created for view fields. view.test, view.result: Added a test case for bug #11412.
1 parent d6d1a9a commit 24d148d

5 files changed

Lines changed: 79 additions & 30 deletions

File tree

‎mysql-test/r/view.result‎

Lines changed: 23 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2024,3 +2024,26 @@ f1 sb
20242024
2005-01-01 12:00:00 2005-01-01 10:58:59
20252025
drop view v1;
20262026
drop table t1;
2027+
CREATE TABLE t1 (
2028+
aid int PRIMARY KEY,
2029+
fn varchar(20) NOT NULL,
2030+
ln varchar(20) NOT NULL
2031+
);
2032+
CREATE TABLE t2 (
2033+
aid int NOT NULL,
2034+
pid int NOT NULL
2035+
);
2036+
INSERT INTO t1 VALUES(1,'a','b'), (2,'c','d');
2037+
INSERT INTO t2 values (1,1), (2,1), (2,2);
2038+
CREATE VIEW v1 AS SELECT t1.*,t2.pid FROM t1,t2 WHERE t1.aid = t2.aid;
2039+
SELECT pid,GROUP_CONCAT(CONCAT(fn,' ',ln) ORDER BY 1) FROM t1,t2
2040+
WHERE t1.aid = t2.aid GROUP BY pid;
2041+
pid GROUP_CONCAT(CONCAT(fn,' ',ln) ORDER BY 1)
2042+
1 a b,c d
2043+
2 c d
2044+
SELECT pid,GROUP_CONCAT(CONCAT(fn,' ',ln) ORDER BY 1) FROM v1 GROUP BY pid;
2045+
pid GROUP_CONCAT(CONCAT(fn,' ',ln) ORDER BY 1)
2046+
1 a b,c d
2047+
2 c d
2048+
DROP VIEW v1;
2049+
DROP TABLE t1,t2;

‎mysql-test/t/view.test‎

Lines changed: 25 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1862,4 +1862,28 @@ insert into t1 values('2005.01.01 12:0:0');
18621862
create view v1 as select f1, subtime(f1, '1:1:1') as sb from t1;
18631863
select * from v1;
18641864
drop view v1;
1865-
drop table t1;
1865+
drop table t1;
1866+
1867+
#
1868+
# Test for bug #11412: query over a multitable view with GROUP_CONCAT
1869+
#
1870+
CREATE TABLE t1 (
1871+
aid int PRIMARY KEY,
1872+
fn varchar(20) NOT NULL,
1873+
ln varchar(20) NOT NULL
1874+
);
1875+
CREATE TABLE t2 (
1876+
aid int NOT NULL,
1877+
pid int NOT NULL
1878+
);
1879+
INSERT INTO t1 VALUES(1,'a','b'), (2,'c','d');
1880+
INSERT INTO t2 values (1,1), (2,1), (2,2);
1881+
1882+
CREATE VIEW v1 AS SELECT t1.*,t2.pid FROM t1,t2 WHERE t1.aid = t2.aid;
1883+
1884+
SELECT pid,GROUP_CONCAT(CONCAT(fn,' ',ln) ORDER BY 1) FROM t1,t2
1885+
WHERE t1.aid = t2.aid GROUP BY pid;
1886+
SELECT pid,GROUP_CONCAT(CONCAT(fn,' ',ln) ORDER BY 1) FROM v1 GROUP BY pid;
1887+
1888+
DROP VIEW v1;
1889+
DROP TABLE t1,t2;

‎sql/item.h‎

Lines changed: 5 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -1464,7 +1464,10 @@ class Item_ref :public Item_ident
14641464
void save_org_in_field(Field *field) { (*ref)->save_org_in_field(field); }
14651465
enum Item_result result_type () const { return (*ref)->result_type(); }
14661466
enum_field_types field_type() const { return (*ref)->field_type(); }
1467-
Field *get_tmp_table_field() { return result_field; }
1467+
Field *get_tmp_table_field()
1468+
{ return result_field ? result_field : (*ref)->get_tmp_table_field(); }
1469+
Item *get_tmp_table_item(THD *thd)
1470+
{ return (*ref)->get_tmp_table_item(thd); }
14681471
table_map used_tables() const
14691472
{
14701473
return depended_from ? OUTER_REF_TABLE_BIT : (*ref)->used_tables();
@@ -1702,7 +1705,7 @@ class Cached_item_field :public Cached_item
17021705
public:
17031706
Cached_item_field(Item_field *item)
17041707
{
1705-
field=item->field;
1708+
field= item->field;
17061709
buff= (char*) sql_calloc(length=field->pack_length());
17071710
}
17081711
bool cmp(void);

‎sql/item_buff.cc‎

Lines changed: 3 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -25,9 +25,9 @@
2525

2626
Cached_item *new_Cached_item(THD *thd, Item *item)
2727
{
28-
if (item->type() == Item::FIELD_ITEM &&
29-
!(((Item_field *) item)->field->flags & BLOB_FLAG))
30-
return new Cached_item_field((Item_field *) item);
28+
if (item->real_item()->type() == Item::FIELD_ITEM &&
29+
!(((Item_field *) (item->real_item()))->field->flags & BLOB_FLAG))
30+
return new Cached_item_field((Item_field *) (item->real_item()));
3131
switch (item->result_type()) {
3232
case STRING_RESULT:
3333
return new Cached_item_str(thd, (Item_field *) item);

‎sql/sql_select.cc‎

Lines changed: 23 additions & 24 deletions
Original file line numberDiff line numberDiff line change
@@ -8026,6 +8026,12 @@ Field *create_tmp_field(THD *thd, TABLE *table,Item *item, Item::Type type,
80268026
bool table_cant_handle_bit_fields,
80278027
uint convert_blob_length)
80288028
{
8029+
Item *org_item= item;
8030+
if (item->real_item()->type() == Item::FIELD_ITEM)
8031+
{
8032+
item= item->real_item();
8033+
type= item->type();
8034+
}
80298035
switch (type) {
80308036
case Item::SUM_FUNC_ITEM:
80318037
{
@@ -8038,30 +8044,22 @@ Field *create_tmp_field(THD *thd, TABLE *table,Item *item, Item::Type type,
80388044
case Item::FIELD_ITEM:
80398045
case Item::DEFAULT_VALUE_ITEM:
80408046
{
8041-
Item_field *field= (Item_field*) item;
8042-
if (table_cant_handle_bit_fields && field->field->type() == FIELD_TYPE_BIT)
8043-
return create_tmp_field_from_item(thd, item, table, copy_func,
8044-
modify_item, convert_blob_length);
8045-
return create_tmp_field_from_field(thd, (*from_field= field->field),
8046-
item->name, table,
8047-
modify_item ? (Item_field*) item : NULL,
8048-
convert_blob_length);
8049-
}
8050-
case Item::REF_ITEM:
8051-
{
8052-
Item *tmp_item;
8053-
if ((tmp_item= item->real_item())->type() == Item::FIELD_ITEM)
8047+
if (org_item->type() != Item::REF_ITEM ||
8048+
!((Item_ref *)org_item)->depended_from)
80548049
{
8055-
Item_field *field= (Item_field*) tmp_item;
8056-
Field *new_field= create_tmp_field_from_field(thd,
8057-
(*from_field= field->field),
8058-
item->name, table,
8059-
NULL,
8060-
convert_blob_length);
8061-
if (modify_item)
8062-
item->set_result_field(new_field);
8063-
return new_field;
8050+
Item_field *field= (Item_field*) item;
8051+
if (table_cant_handle_bit_fields &&
8052+
field->field->type() == FIELD_TYPE_BIT)
8053+
return create_tmp_field_from_item(thd, item, table, copy_func,
8054+
modify_item, convert_blob_length);
8055+
return create_tmp_field_from_field(thd, (*from_field= field->field),
8056+
item->name, table,
8057+
modify_item ? (Item_field*) item :
8058+
NULL,
8059+
convert_blob_length);
80648060
}
8061+
else
8062+
item= org_item;
80658063
}
80668064
case Item::FUNC_ITEM:
80678065
case Item::COND_ITEM:
@@ -8074,6 +8072,7 @@ Field *create_tmp_field(THD *thd, TABLE *table,Item *item, Item::Type type,
80748072
case Item::REAL_ITEM:
80758073
case Item::DECIMAL_ITEM:
80768074
case Item::STRING_ITEM:
8075+
case Item::REF_ITEM:
80778076
case Item::NULL_ITEM:
80788077
case Item::VARBIN_ITEM:
80798078
return create_tmp_field_from_item(thd, item, table, copy_func, modify_item,
@@ -10904,12 +10903,12 @@ test_if_skip_sort_order(JOIN_TAB *tab,ORDER *order,ha_rows select_limit,
1090410903
usable_keys.set_all();
1090510904
for (ORDER *tmp_order=order; tmp_order ; tmp_order=tmp_order->next)
1090610905
{
10907-
if ((*tmp_order->item)->type() != Item::FIELD_ITEM)
10906+
if ((*tmp_order->item)->real_item()->type() != Item::FIELD_ITEM)
1090810907
{
1090910908
usable_keys.clear_all();
1091010909
DBUG_RETURN(0);
1091110910
}
10912-
usable_keys.intersect(((Item_field*) (*tmp_order->item))->
10911+
usable_keys.intersect(((Item_field*) (*tmp_order->item)->real_item())->
1091310912
field->part_of_sortkey);
1091410913
if (usable_keys.is_clear_all())
1091510914
DBUG_RETURN(0); // No usable keys

0 commit comments

Comments
 (0)