You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
79 lines
2.1 KiB
79 lines
2.1 KiB
#
|
|
# Bug with distinct and INSERT INTO
|
|
# Bug with group by and not used fields
|
|
#
|
|
|
|
--echo #
|
|
--echo # Bug#13335170 - ASSERT IN
|
|
--echo # PLAN_CHANGE_WATCHDOG::~PLAN_CHANGE_WATCHDOG() ON SELECT DISTINCT
|
|
--echo #
|
|
|
|
CREATE TABLE t1 (
|
|
col_int_key int(11) NOT NULL,
|
|
col_time_key time NOT NULL,
|
|
col_datetime_key datetime NOT NULL,
|
|
KEY col_int_key (col_int_key),
|
|
KEY col_time_key (col_time_key),
|
|
KEY col_datetime_key (col_datetime_key)
|
|
) ENGINE=InnoDB;
|
|
|
|
INSERT INTO t1 VALUES (7,'06:17:39','2003-08-21 00:00:00');
|
|
--source include/turn_off_only_full_group_by.inc
|
|
|
|
SELECT DISTINCT col_int_key
|
|
FROM t1
|
|
WHERE col_int_key IN ( 18, 6, 84, 4, 0, 2, 8, 3, 7, 9, 1 )
|
|
AND col_datetime_key BETWEEN '2001-08-04' AND '2003-06-13'
|
|
ORDER BY col_time_key
|
|
LIMIT 3;
|
|
|
|
--source include/restore_sql_mode_after_turn_off_only_full_group_by.inc
|
|
DROP TABLE t1;
|
|
|
|
--echo
|
|
--echo # BUG#13581713 ONLY_FULL_GROUP_BY DOES NOT BLOCK "SELECT
|
|
--echo # DISTINCT A ORDER BY B"
|
|
--echo
|
|
|
|
create table t1(a int, b int, c int) engine=InnoDB;
|
|
create table t2(a int, b int, c int) engine=InnoDB;
|
|
insert into t2 values();
|
|
analyze table t2;
|
|
|
|
--echo # Test when selecting from base table
|
|
let $source=t1;
|
|
let $source_no_alias=t1;
|
|
--source include/bug13581713.inc
|
|
|
|
--echo # Test when selecting from view
|
|
create view v1 as select t1.* from t1 left join t2 on 1;
|
|
let $source=v1;
|
|
let $source_no_alias=v1;
|
|
--source include/bug13581713.inc
|
|
drop view v1;
|
|
|
|
--echo # Test when selecting from view, again
|
|
create view v1 as select t1.a*2 as a, t1.b*2 as b, t1.c*2 as c from t1;
|
|
let $source=v1;
|
|
let $source_no_alias=v1;
|
|
--source include/bug13581713.inc
|
|
drop view v1;
|
|
|
|
--echo # Test when selecting from derived table
|
|
let $source=(SELECT t1.* FROM t1 left join t2 on 1) AS derived;
|
|
let $source_no_alias=(SELECT t1.* FROM t1 left join t2 on 1);
|
|
--source include/bug13581713.inc
|
|
|
|
--error ER_FIELD_IN_ORDER_NOT_SELECT
|
|
select distinct t1_outer.a from t1 t1_outer
|
|
order by t1_outer.b;
|
|
--error ER_FIELD_IN_ORDER_NOT_SELECT
|
|
select distinct t1_outer.a from t1 t1_outer
|
|
order by (select max(t1_outer.b+t1_inner.b) from t1 t1_inner);
|
|
select
|
|
(select distinct 1 from t1 t1_inner
|
|
group by t1_inner.a order by max(t1_outer.b))
|
|
from t1 t1_outer;
|
|
|
|
drop table t1, t2;
|
|
|
|
|