summaryrefslogtreecommitdiffstats
path: root/mysql-test/suite/vcol/inc/vcol_select.inc
diff options
context:
space:
mode:
authorDaniel Baumann <daniel.baumann@progress-linux.org>2024-05-04 18:00:34 +0000
committerDaniel Baumann <daniel.baumann@progress-linux.org>2024-05-04 18:00:34 +0000
commit3f619478f796eddbba6e39502fe941b285dd97b1 (patch)
treee2c7b5777f728320e5b5542b6213fd3591ba51e2 /mysql-test/suite/vcol/inc/vcol_select.inc
parentInitial commit. (diff)
downloadmariadb-upstream.tar.xz
mariadb-upstream.zip
Adding upstream version 1:10.11.6.upstream/1%10.11.6upstream
Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>
Diffstat (limited to 'mysql-test/suite/vcol/inc/vcol_select.inc')
-rw-r--r--mysql-test/suite/vcol/inc/vcol_select.inc222
1 files changed, 222 insertions, 0 deletions
diff --git a/mysql-test/suite/vcol/inc/vcol_select.inc b/mysql-test/suite/vcol/inc/vcol_select.inc
new file mode 100644
index 00000000..56e1fc7a
--- /dev/null
+++ b/mysql-test/suite/vcol/inc/vcol_select.inc
@@ -0,0 +1,222 @@
+################################################################################
+# inc/vcol_select.inc #
+# #
+# Purpose: #
+# Testing different SELECTs. #
+# #
+# #
+#------------------------------------------------------------------------------#
+# Original Author: Andrey Zhakov #
+# Original Date: 2008-09-18 #
+# Change Author: Oleksandr Byelkin (Monty program Ab)
+# Date: 2009-03-24
+# Change: Syntax changed
+################################################################################
+
+# Table t1 is used below to test:
+# - Join type of ALL (sequential scan of the entire table)
+# - Join type of Index
+# - Join type of Range
+# - Join type of Ref_or_null
+create table t1 (a int,
+ b int as (-a),
+ c int as (-a) persistent,
+ index (c));
+insert into t1 (a) values (2), (1), (1), (3), (NULL);
+
+# Table t2 is used below to test:
+# - Join type of system and const
+create table t2 like t1;
+insert into t2 (a) values (1);
+
+# Table t3 is used below to test
+# - Join type of Eq_ref with a unique virtual column
+# - Join type of Const
+create table t3 (a int primary key,
+ b int as (-a),
+ c int as (-a) persistent unique);
+insert into t3 (a) values (2),(1),(3),(5),(4),(7);
+
+
+--echo # select_type=SIMPLE, type=system
+let $s = select * from t2;
+eval $s;
+eval explain $s;
+
+let $s = select * from t2 where c=-1;
+eval $s;
+eval explain $s;
+
+--echo # select_type=SIMPLE, type=ALL
+let $s = select * from t1 where b=-1;
+eval $s;
+eval explain $s;
+
+--echo # select_type=SIMPLE, type=const
+let $s = select * from t3 where a=1;
+eval $s;
+eval explain $s;
+
+--echo # select_type=SIMPLE, type=range
+let $s = select * from t3 where c>=-1;
+eval $s;
+eval explain $s;
+
+--echo # select_type=SIMPLE, type=ref
+let $s = select * from t1,t3 where t1.c=t3.c and t3.c=-1;
+eval $s;
+eval explain $s;
+
+--echo # select_type=PRIMARY, type=index,ALL
+let $s = select * from t1 where b in (select c from t3);
+eval $s;
+eval explain $s;
+
+--echo # select_type=PRIMARY, type=range,ref
+let $s = select * from t1 where c in (select c from t3 where c between -2 and -1);
+eval $s;
+eval explain $s;
+
+--echo # select_type=UNION, type=system
+--echo # select_type=UNION RESULT, type=<union1,2>
+let $s = select * from t1 union select * from t2;
+eval $s;
+eval explain $s;
+
+--echo # select_type=DERIVED, type=system
+
+set @tmp_optimizer_switch=@@optimizer_switch;
+set optimizer_switch='derived_merge=off,derived_with_keys=off';
+
+let $s = select * from (select a,b,c from t1) as t11;
+eval $s;
+eval explain $s;
+
+set optimizer_switch=@tmp_optimizer_switch;
+
+--echo ###
+--echo ### Using aggregate functions with/without DISTINCT
+--echo ###
+--echo # SELECT COUNT(*) FROM tbl_name
+let $s = select count(*) from t1;
+eval $s;
+eval explain $s;
+
+--echo # SELECT COUNT(DISTINCT <non-vcol>) FROM tbl_name
+let $s = select count(distinct a) from t1;
+eval $s;
+eval explain $s;
+
+--echo # SELECT COUNT(DISTINCT <non-stored vcol>) FROM tbl_name
+let $s = select count(distinct b) from t1;
+eval $s;
+eval explain $s;
+
+--echo # SELECT COUNT(DISTINCT <stored vcol>) FROM tbl_name
+let $s = select count(distinct c) from t1;
+eval $s;
+eval explain $s;
+
+--echo ###
+--echo ### filesort & range-based utils
+--echo ###
+--echo # SELECT * FROM tbl_name WHERE <vcol expr>
+let $s = select * from t3 where c >= -2;
+eval $s;
+eval explain $s;
+
+--echo # SELECT * FROM tbl_name WHERE <non-vcol expr>
+let $s = select * from t3 where a between 1 and 2;
+eval $s;
+eval explain $s;
+
+--echo # SELECT * FROM tbl_name WHERE <non-indexed vcol expr>
+let $s = select * from t3 where b between -2 and -1;
+eval $s;
+eval explain $s;
+
+--echo # SELECT * FROM tbl_name WHERE <indexed vcol expr>
+let $s = select * from t3 where c between -2 and -1;
+eval $s;
+eval explain $s;
+
+#### Remove for MyISAM due to a bug
+#### when all the three records are returned (a=1,2,3)
+#### instead of just two (a=1,2).
+#### This bug is presumably in base SQL routines as the same happens
+#### with this table:
+#### create table t4 (a int primary key, b int, c int unique);
+let $myisam_engine = `SELECT @@session.default_storage_engine='myisam'`;
+if (!$myisam_engine)
+{
+ --echo # SELECT * FROM tbl_name WHERE <non-vcol expr> ORDER BY <non-indexed vcol>
+ let $s = select * from t3 where a between 1 and 2 order by b;
+ eval $s;
+ eval explain $s;
+}
+
+--echo # SELECT * FROM tbl_name WHERE <non-vcol expr> ORDER BY <indexed vcol>
+let $s = select * from t3 where a between 1 and 2 order by c;
+eval $s;
+eval explain $s;
+
+--echo # SELECT * FROM tbl_name WHERE <non-indexed vcol expr> ORDER BY <non-vcol>
+let $s = select * from t3 where b between -2 and -1 order by a;
+eval $s;
+eval explain $s;
+
+#### Remove for MyISAM due to a bug
+#### when all the three records are returned (a=1,2,3)
+#### instead of just two (a=1,2).
+#### This bug is presumably in base SQL routines as the same happens
+#### with this table:
+#### create table t4 (a int primary key, b int, c int unique);
+let $innodb_engine = `SELECT @@session.default_storage_engine='innodb'`;
+if (!$innodb_engine)
+{
+ --echo # SELECT * FROM tbl_name WHERE <indexed vcol expr> ORDER BY <non-vcol>
+ let $s = select * from t3 where c between -2 and -1 order by a;
+ eval $s;
+ eval explain $s;
+}
+
+--echo # SELECT * FROM tbl_name WHERE <non-indexed vcol expr> ORDER BY <non-indexed vcol>
+let $s = select * from t3 where b between -2 and -1 order by b;
+eval $s;
+eval explain $s;
+
+--echo # SELECT * FROM tbl_name WHERE <indexed vcol expr> ORDER BY <non-indexed vcol>
+let $s = select * from t3 where c between -2 and -1 order by b;
+eval $s;
+eval explain $s;
+
+--echo # SELECT * FROM tbl_name WHERE <non-indexed vcol expr> ORDER BY <indexed vcol>
+let $s = select * from t3 where b between -2 and -1 order by c;
+eval $s;
+eval explain $s;
+
+--echo # SELECT * FROM tbl_name WHERE <indexed vcol expr> ORDER BY <indexed vcol>
+let $s = select * from t3 where c between -2 and -1 order by c;
+eval $s;
+eval explain $s;
+
+--echo # SELECT sum(<non-indexed vcol>) FROM tbl_name GROUP BY <non-indexed vcol>
+let $s = select sum(b) from t1 group by b;
+eval $s;
+eval explain $s;
+
+--echo # SELECT sum(<indexed vcol>) FROM tbl_name GROUP BY <indexed vcol>
+let $s = select sum(c) from t1 group by c;
+eval $s;
+eval explain $s;
+
+--echo # SELECT sum(<non-indexed vcol>) FROM tbl_name GROUP BY <indexed vcol>
+let $s = select sum(b) from t1 group by c;
+eval $s;
+eval explain $s;
+
+--echo # SELECT sum(<indexed vcol>) FROM tbl_name GROUP BY <non-indexed vcol>
+let $s = select sum(c) from t1 group by b;
+eval $s;
+eval explain $s;
+