More tests with bit & blob

2 files changed, 112 insertions, 0 deletions

diff --git a/mysql-test/main/cte_cycle.result b/mysql-test/main/cte_cycle.result
index 889330f0737..e66d090b3c7 100644
--- a/mysql-test/main/cte_cycle.result
+++ b/mysql-test/main/cte_cycle.result
@@ -85,3 +85,72 @@ depth	from_	to_
 1	1	NULL
 drop view v1;
 drop table t1;
+#
+# A simple blob case
+#
+create table t1 (a int, b text);
+insert into t1 values (1, "a");
+WITH RECURSIVE cte AS (
+SELECT a, b  from t1 UNION ALL
+SELECT a, b FROM cte WHERE a IS NOT NULL)
+CYCLE b RESTRICT
+SELECT * FROM cte;
+a	b
+1	a
+drop table t1;
+#
+# check bit types
+#
+create table t1 (from_ bit(3), to_ bit(3));
+insert into t1 values (1,2), (1,7), (2,3), (3,4), (4,1);
+WITH RECURSIVE cte (depth, from_, to_) as (
+SELECT 0,1,1
+UNION
+SELECT depth+1, t1.from_, t1.to_
+FROM t1, cte WHERE t1.from_ = cte.to_
+) CYCLE from_, to_ RESTRICT
+select * from cte;
+depth	from_	to_
+0	1	1
+1	1	2
+1	1	7
+2	2	3
+3	3	4
+4	4	1
+drop table t1;
+#
+# check bit types with BLOBs (TEXT)
+#
+create table t1 (from_ bit(3), to_ bit(3), load_ text);
+insert into t1 values (1,2,"A"), (1,7,"A"), (2,3,"A"), (3,4,"A"), (4,1,"A");
+WITH RECURSIVE cte (depth, from_, to_, load_) as (
+SELECT 0,1,1,"A"
+   UNION
+SELECT depth+1, t1.from_, t1.to_, t1.load_
+FROM t1, cte WHERE t1.from_ = cte.to_
+) CYCLE from_, to_, load_ RESTRICT
+select * from cte;
+depth	from_	to_	load_
+0	1	1	A
+1	1	2	A
+1	1	7	A
+2	2	3	A
+3	3	4	A
+4	4	1	A
+insert into t1 values (4,1,"B");
+WITH RECURSIVE cte (depth, from_, to_, load_) as (
+SELECT 0,1,1,"A"
+   UNION
+SELECT depth+1, t1.from_, t1.to_, t1.load_
+FROM t1, cte WHERE t1.from_ = cte.to_
+) CYCLE from_, to_, load_ RESTRICT
+select * from cte;
+depth	from_	to_	load_
+0	1	1	A
+1	1	2	A
+1	1	7	A
+2	2	3	A
+3	3	4	A
+4	4	1	A
+4	4	1	B
+drop table t1;
diff --git a/mysql-test/main/cte_cycle.test b/mysql-test/main/cte_cycle.test
index b1dadcbe8f2..505980353fd 100644
--- a/mysql-test/main/cte_cycle.test
+++ b/mysql-test/main/cte_cycle.test
@@ -98,3 +98,46 @@ WITH RECURSIVE cte AS (
 SELECT * FROM cte;
 
 drop table t1;
+
+--echo #
+--echo # check bit types
+--echo #
+
+create table t1 (from_ bit(3), to_ bit(3));
+insert into t1 values (1,2), (1,7), (2,3), (3,4), (4,1);
+
+WITH RECURSIVE cte (depth, from_, to_) as (
+   SELECT 0,1,1
+   UNION
+   SELECT depth+1, t1.from_, t1.to_
+      FROM t1, cte WHERE t1.from_ = cte.to_
+) CYCLE from_, to_ RESTRICT
+select * from cte;
+drop table t1;
+
+--echo #
+--echo # check bit types with BLOBs (TEXT)
+--echo #
+
+create table t1 (from_ bit(3), to_ bit(3), load_ text);
+insert into t1 values (1,2,"A"), (1,7,"A"), (2,3,"A"), (3,4,"A"), (4,1,"A");
+
+WITH RECURSIVE cte (depth, from_, to_, load_) as (
+   SELECT 0,1,1,"A"
+   UNION
+   SELECT depth+1, t1.from_, t1.to_, t1.load_
+      FROM t1, cte WHERE t1.from_ = cte.to_
+) CYCLE from_, to_, load_ RESTRICT
+select * from cte;
+insert into t1 values (4,1,"B");
+WITH RECURSIVE cte (depth, from_, to_, load_) as (
+   SELECT 0,1,1,"A"
+   UNION
+   SELECT depth+1, t1.from_, t1.to_, t1.load_
+      FROM t1, cte WHERE t1.from_ = cte.to_
+) CYCLE from_, to_, load_ RESTRICT
+select * from cte;
+
+drop table t1;
+
+