MDEV-4958 Adding datatype UUID

43 files changed, 5334 insertions, 99 deletions

diff --git a/plugin/type_uuid/CMakeLists.txt b/plugin/type_uuid/CMakeLists.txt
new file mode 100644
index 00000000000..9a379abef04
--- /dev/null
+++ b/plugin/type_uuid/CMakeLists.txt
@@ -0,0 +1,18 @@
+# Copyright (c) 2019,2021, MariaDB Corporation
+#
+# This program is free software; you can redistribute it and/or modify
+# it under the terms of the GNU General Public License as published by
+# the Free Software Foundation; version 2 of the License.
+#
+# This program is distributed in the hope that it will be useful,
+# but WITHOUT ANY WARRANTY; without even the implied warranty of
+# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
+# GNU General Public License for more details.
+#
+# You should have received a copy of the GNU General Public License
+# along with this program; if not, write to the Free Software
+# Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1335  USA
+
+MYSQL_ADD_PLUGIN(type_uuid
+                 plugin.cc sql_type_uuid.cc item_uuidfunc.cc
+                 MANDATORY RECOMPILE_FOR_EMBEDDED)
diff --git a/plugin/type_uuid/item_uuidfunc.cc b/plugin/type_uuid/item_uuidfunc.cc
new file mode 100644
index 00000000000..0c79cbbd5b8
--- /dev/null
+++ b/plugin/type_uuid/item_uuidfunc.cc
@@ -0,0 +1,43 @@
+/* Copyright (c) 2019,2021, MariaDB Corporation
+
+   This program is free software; you can redistribute it and/or modify
+   it under the terms of the GNU General Public License as published by
+   the Free Software Foundation; version 2 of the License.
+
+   This program is distributed in the hope that it will be useful,
+   but WITHOUT ANY WARRANTY; without even the implied warranty of
+   MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
+   GNU General Public License for more details.
+
+   You should have received a copy of the GNU General Public License
+   along with this program; if not, write to the Free Software
+   Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1335  USA */
+
+#define MYSQL_SERVER
+#include "mariadb.h"
+#include "item_uuidfunc.h"
+#include "sql_type_uuid.h"
+
+class UUID_generated : public UUIDBundle::Fbt
+{
+public:
+  UUID_generated() { my_uuid((uchar *) m_buffer); }
+  bool to_string(String *to, bool with_separators) const
+  {
+    if (to->alloc(max_char_length() + 1))
+      return true;
+    to->set_charset(system_charset_info);
+    to->length(MY_UUID_BARE_STRING_LENGTH + with_separators*MY_UUID_SEPARATORS);
+    my_uuid2str((const uchar *) m_buffer, (char *) to->ptr(), with_separators);
+    return false;
+  }
+};
+
+String *Item_func_uuid::val_str(String *str)
+{
+  DBUG_ASSERT(fixed());
+  if (!UUID_generated().to_string(str, with_separators))
+    return str;
+  str->set("", 0, collation.collation);
+  return str;
+}
diff --git a/plugin/type_uuid/item_uuidfunc.h b/plugin/type_uuid/item_uuidfunc.h
new file mode 100644
index 00000000000..3756d70ba3d
--- /dev/null
+++ b/plugin/type_uuid/item_uuidfunc.h
@@ -0,0 +1,52 @@
+#ifndef ITEM_UUIDFUNC_INCLUDED
+#define ITEM_UUIDFUNC_INCLUDED
+
+/* Copyright (c) 2019,2021, MariaDB Corporation
+
+   This program is free software; you can redistribute it and/or modify
+   it under the terms of the GNU General Public License as published by
+   the Free Software Foundation; version 2 of the License.
+
+   This program is distributed in the hope that it will be useful,
+   but WITHOUT ANY WARRANTY; without even the implied warranty of
+   MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
+   GNU General Public License for more details.
+
+   You should have received a copy of the GNU General Public License
+   along with this program; if not, write to the Free Software
+   Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1335  USA */
+
+
+#include "item.h"
+
+class Item_func_uuid: public Item_str_func
+{
+  bool with_separators;
+public:
+  Item_func_uuid(THD *thd, bool with_separators_arg):
+    Item_str_func(thd), with_separators(with_separators_arg) {}
+  bool fix_length_and_dec()
+  {
+    collation.set(DTCollation_numeric());
+    fix_char_length(with_separators ? MY_UUID_STRING_LENGTH
+                                    : MY_UUID_BARE_STRING_LENGTH);
+    return FALSE;
+  }
+  bool const_item() const { return false; }
+  table_map used_tables() const { return RAND_TABLE_BIT; }
+  LEX_CSTRING func_name_cstring() const override
+  {
+    static LEX_CSTRING name1= {STRING_WITH_LEN("uuid") };
+    static LEX_CSTRING name2= {STRING_WITH_LEN("sys_guid") };
+    return with_separators ? name1 : name2;
+  }
+  String *val_str(String *);
+  bool check_vcol_func_processor(void *arg)
+  {
+    return mark_unsupported_function(func_name(), "()", arg, VCOL_NON_DETERMINISTIC);
+  }
+  Item *get_copy(THD *thd)
+  { return get_item_copy<Item_func_uuid>(thd, this); }
+};
+
+#endif // ITEM_UUIDFUNC_INCLUDED
diff --git a/plugin/type_uuid/mysql-test/type_uuid/binlog_stm_type_uuid.result b/plugin/type_uuid/mysql-test/type_uuid/binlog_stm_type_uuid.result
new file mode 100644
index 00000000000..a0e5a3af11c
--- /dev/null
+++ b/plugin/type_uuid/mysql-test/type_uuid/binlog_stm_type_uuid.result
@@ -0,0 +1,37 @@
+#
+# MDEV-4958 Adding datatype UUID
+#
+CREATE TABLE t1 (a UUID);
+INSERT INTO t1 VALUES ('00000000-0000-0000-0000-000000000000');
+INSERT INTO t1 VALUES ('ffffffff-ffff-ffff-ffff-ffffffffffff');
+PREPARE stmt FROM 'INSERT INTO t1 VALUES (?)';
+EXECUTE stmt USING CAST('00000000-0000-0000-0000-000000000001' AS UUID);
+EXECUTE stmt USING CAST(CONCAT(REPEAT(0x00,15), 0x02) AS UUID);
+DEALLOCATE PREPARE stmt;
+BEGIN NOT ATOMIC
+DECLARE a UUID DEFAULT '00000000-0000-0000-0000-000000000003';
+INSERT INTO t1 VALUES (a);
+END;
+$$
+DROP TABLE t1;
+include/show_binlog_events.inc
+Log_name	Pos	Event_type	Server_id	End_log_pos	Info
+master-bin.000001	#	Gtid	#	#	GTID #-#-#
+master-bin.000001	#	Query	#	#	use `test`; CREATE TABLE t1 (a UUID)
+master-bin.000001	#	Gtid	#	#	BEGIN GTID #-#-#
+master-bin.000001	#	Query	#	#	use `test`; INSERT INTO t1 VALUES ('00000000-0000-0000-0000-000000000000')
+master-bin.000001	#	Query	#	#	COMMIT
+master-bin.000001	#	Gtid	#	#	BEGIN GTID #-#-#
+master-bin.000001	#	Query	#	#	use `test`; INSERT INTO t1 VALUES ('ffffffff-ffff-ffff-ffff-ffffffffffff')
+master-bin.000001	#	Query	#	#	COMMIT
+master-bin.000001	#	Gtid	#	#	BEGIN GTID #-#-#
+master-bin.000001	#	Query	#	#	use `test`; INSERT INTO t1 VALUES ('00000000-0000-0000-0000-000000000001')
+master-bin.000001	#	Query	#	#	COMMIT
+master-bin.000001	#	Gtid	#	#	BEGIN GTID #-#-#
+master-bin.000001	#	Query	#	#	use `test`; INSERT INTO t1 VALUES ('00000000-0000-0000-0000-000000000002')
+master-bin.000001	#	Query	#	#	COMMIT
+master-bin.000001	#	Gtid	#	#	BEGIN GTID #-#-#
+master-bin.000001	#	Query	#	#	use `test`; INSERT INTO t1 VALUES ( NAME_CONST('a','00000000-0000-0000-0000-000000000003'))
+master-bin.000001	#	Query	#	#	COMMIT
+master-bin.000001	#	Gtid	#	#	GTID #-#-#
+master-bin.000001	#	Query	#	#	use `test`; DROP TABLE `t1` /* generated by server */
diff --git a/plugin/type_uuid/mysql-test/type_uuid/binlog_stm_type_uuid.test b/plugin/type_uuid/mysql-test/type_uuid/binlog_stm_type_uuid.test
new file mode 100644
index 00000000000..2c2dd95858d
--- /dev/null
+++ b/plugin/type_uuid/mysql-test/type_uuid/binlog_stm_type_uuid.test
@@ -0,0 +1,32 @@
+--source include/not_embedded.inc
+--source include/have_binlog_format_statement.inc
+
+--disable_query_log
+reset master; # get rid of previous tests binlog
+--enable_query_log
+
+--echo #
+--echo # MDEV-4958 Adding datatype UUID
+--echo #
+
+CREATE TABLE t1 (a UUID);
+INSERT INTO t1 VALUES ('00000000-0000-0000-0000-000000000000');
+INSERT INTO t1 VALUES ('ffffffff-ffff-ffff-ffff-ffffffffffff');
+
+PREPARE stmt FROM 'INSERT INTO t1 VALUES (?)';
+EXECUTE stmt USING CAST('00000000-0000-0000-0000-000000000001' AS UUID);
+EXECUTE stmt USING CAST(CONCAT(REPEAT(0x00,15), 0x02) AS UUID);
+DEALLOCATE PREPARE stmt;
+
+DELIMITER $$;
+BEGIN NOT ATOMIC
+  DECLARE a UUID DEFAULT '00000000-0000-0000-0000-000000000003';
+  INSERT INTO t1 VALUES (a);
+END;
+$$
+DELIMITER ;$$
+
+DROP TABLE t1;
+
+--let $binlog_file = LAST
+source include/show_binlog_events.inc;
diff --git a/plugin/type_uuid/mysql-test/type_uuid/binlog_table_map_optional_metadata_type_uuid.result b/plugin/type_uuid/mysql-test/type_uuid/binlog_table_map_optional_metadata_type_uuid.result
new file mode 100644
index 00000000000..3932470cf64
--- /dev/null
+++ b/plugin/type_uuid/mysql-test/type_uuid/binlog_table_map_optional_metadata_type_uuid.result
@@ -0,0 +1,60 @@
+#
+# Start of 10.5 tests
+#
+#
+# MDEV-4958 Adding datatype UUID
+#
+# Using DEFAULT_CHARSET format
+RESET MASTER;
+SET GLOBAL binlog_row_metadata = NO_LOG;
+CREATE TABLE t1 (a UUID);
+INSERT INTO t1 VALUES('123e4567-e89b-12d3-a456-426655440000');
+# Columns(BINARY(16))
+DROP TABLE t1;
+RESET MASTER;
+RESET MASTER;
+SET GLOBAL binlog_row_metadata = MINIMAL;
+CREATE TABLE t1 (a UUID);
+INSERT INTO t1 VALUES('12334567-8888-9999-aaaa-000000000000');
+# Columns(BINARY(16))
+DROP TABLE t1;
+RESET MASTER;
+RESET MASTER;
+SET GLOBAL binlog_row_metadata = FULL;
+CREATE TABLE t1 (a UUID);
+INSERT INTO t1 VALUES('12334567-8888-9999-aaaa-000000000000');
+# Columns(`a` BINARY(16))
+DROP TABLE t1;
+RESET MASTER;
+# Using COLUMN_CHARSET format
+RESET MASTER;
+SET GLOBAL binlog_row_metadata = NO_LOG;
+CREATE TABLE t1 (a UUID, b CHAR(16) CHARACTER SET latin1, c CHAR(16) CHARACTER SET utf8);
+INSERT INTO t1 VALUES('12334567-8888-9999-aaaa-000000000000','','');
+# Columns(BINARY(16),
+#         BINARY(16),
+#         BINARY(48))
+DROP TABLE t1;
+RESET MASTER;
+RESET MASTER;
+SET GLOBAL binlog_row_metadata = MINIMAL;
+CREATE TABLE t1 (a UUID, b CHAR(16) CHARACTER SET latin1, c CHAR(16) CHARACTER SET utf8);
+INSERT INTO t1 VALUES('12334567-8888-9999-aaaa-000000000000','','');
+# Columns(BINARY(16),
+#         CHAR(16) CHARSET latin1 COLLATE latin1_swedish_ci,
+#         CHAR(16) CHARSET utf8mb3 COLLATE utf8mb3_general_ci)
+DROP TABLE t1;
+RESET MASTER;
+RESET MASTER;
+SET GLOBAL binlog_row_metadata = FULL;
+CREATE TABLE t1 (a UUID, b CHAR(16) CHARACTER SET latin1, c CHAR(16) CHARACTER SET utf8);
+INSERT INTO t1 VALUES('12334567-8888-9999-aaaa-000000000000','','');
+# Columns(`a` BINARY(16),
+#         `b` CHAR(16) CHARSET latin1 COLLATE latin1_swedish_ci,
+#         `c` CHAR(16) CHARSET utf8mb3 COLLATE utf8mb3_general_ci)
+DROP TABLE t1;
+RESET MASTER;
+SET GLOBAL binlog_row_metadata = DEFAULT;
+#
+# End of 10.5 tests
+#
diff --git a/plugin/type_uuid/mysql-test/type_uuid/binlog_table_map_optional_metadata_type_uuid.test b/plugin/type_uuid/mysql-test/type_uuid/binlog_table_map_optional_metadata_type_uuid.test
new file mode 100644
index 00000000000..aa525123855
--- /dev/null
+++ b/plugin/type_uuid/mysql-test/type_uuid/binlog_table_map_optional_metadata_type_uuid.test
@@ -0,0 +1,72 @@
+--source include/have_debug.inc
+--source include/have_binlog_format_row.inc
+
+--let $MYSQLD_DATADIR= `select @@datadir`
+--let $binlog_file= $MYSQLD_DATADIR/master-bin.000001
+
+
+--echo #
+--echo # Start of 10.5 tests
+--echo #
+
+--echo #
+--echo # MDEV-4958 Adding datatype UUID
+--echo #
+
+--echo # Using DEFAULT_CHARSET format
+
+RESET MASTER;
+SET GLOBAL binlog_row_metadata = NO_LOG;
+CREATE TABLE t1 (a UUID);
+INSERT INTO t1 VALUES('123e4567-e89b-12d3-a456-426655440000');
+--source suite/binlog/include/print_optional_metadata.inc
+DROP TABLE t1;
+RESET MASTER;
+
+RESET MASTER;
+SET GLOBAL binlog_row_metadata = MINIMAL;
+CREATE TABLE t1 (a UUID);
+INSERT INTO t1 VALUES('12334567-8888-9999-aaaa-000000000000');
+--source suite/binlog/include/print_optional_metadata.inc
+DROP TABLE t1;
+RESET MASTER;
+
+RESET MASTER;
+SET GLOBAL binlog_row_metadata = FULL;
+CREATE TABLE t1 (a UUID);
+INSERT INTO t1 VALUES('12334567-8888-9999-aaaa-000000000000');
+--source suite/binlog/include/print_optional_metadata.inc
+DROP TABLE t1;
+RESET MASTER;
+
+--echo # Using COLUMN_CHARSET format
+
+RESET MASTER;
+SET GLOBAL binlog_row_metadata = NO_LOG;
+CREATE TABLE t1 (a UUID, b CHAR(16) CHARACTER SET latin1, c CHAR(16) CHARACTER SET utf8);
+INSERT INTO t1 VALUES('12334567-8888-9999-aaaa-000000000000','','');
+--source suite/binlog/include/print_optional_metadata.inc
+DROP TABLE t1;
+RESET MASTER;
+
+RESET MASTER;
+SET GLOBAL binlog_row_metadata = MINIMAL;
+CREATE TABLE t1 (a UUID, b CHAR(16) CHARACTER SET latin1, c CHAR(16) CHARACTER SET utf8);
+INSERT INTO t1 VALUES('12334567-8888-9999-aaaa-000000000000','','');
+--source suite/binlog/include/print_optional_metadata.inc
+DROP TABLE t1;
+RESET MASTER;
+
+RESET MASTER;
+SET GLOBAL binlog_row_metadata = FULL;
+CREATE TABLE t1 (a UUID, b CHAR(16) CHARACTER SET latin1, c CHAR(16) CHARACTER SET utf8);
+INSERT INTO t1 VALUES('12334567-8888-9999-aaaa-000000000000','','');
+--source suite/binlog/include/print_optional_metadata.inc
+DROP TABLE t1;
+RESET MASTER;
+
+SET GLOBAL binlog_row_metadata = DEFAULT;
+
+--echo #
+--echo # End of 10.5 tests
+--echo #
diff --git a/plugin/type_uuid/mysql-test/type_uuid/func_uuid_plugin.result b/plugin/type_uuid/mysql-test/type_uuid/func_uuid_plugin.result
new file mode 100644
index 00000000000..ac7b21b1719
--- /dev/null
+++ b/plugin/type_uuid/mysql-test/type_uuid/func_uuid_plugin.result
@@ -0,0 +1,35 @@
+#
+# Start of 10.5 tests
+#
+#
+# MDEV-4958 Adding datatype UUID
+#
+SELECT
+'----' AS `----`,
+PLUGIN_NAME,
+PLUGIN_VERSION,
+PLUGIN_STATUS,
+PLUGIN_TYPE,
+PLUGIN_AUTHOR,
+PLUGIN_DESCRIPTION,
+PLUGIN_LICENSE,
+PLUGIN_MATURITY,
+PLUGIN_AUTH_VERSION
+FROM INFORMATION_SCHEMA.PLUGINS
+WHERE PLUGIN_TYPE='FUNCTION'
+  AND PLUGIN_NAME IN
+('uuid')
+ORDER BY PLUGIN_NAME;
+----	----
+PLUGIN_NAME	uuid
+PLUGIN_VERSION	1.0
+PLUGIN_STATUS	ACTIVE
+PLUGIN_TYPE	FUNCTION
+PLUGIN_AUTHOR	MariaDB Corporation
+PLUGIN_DESCRIPTION	Function UUID()
+PLUGIN_LICENSE	GPL
+PLUGIN_MATURITY	Beta
+PLUGIN_AUTH_VERSION	1.0
+#
+# End of 10.5 tests
+#
diff --git a/plugin/type_uuid/mysql-test/type_uuid/func_uuid_plugin.test b/plugin/type_uuid/mysql-test/type_uuid/func_uuid_plugin.test
new file mode 100644
index 00000000000..ce8aba68979
--- /dev/null
+++ b/plugin/type_uuid/mysql-test/type_uuid/func_uuid_plugin.test
@@ -0,0 +1,30 @@
+--echo #
+--echo # Start of 10.5 tests
+--echo #
+
+--echo #
+--echo # MDEV-4958 Adding datatype UUID
+--echo #
+
+--vertical_results
+SELECT
+  '----' AS `----`,
+  PLUGIN_NAME,
+  PLUGIN_VERSION,
+  PLUGIN_STATUS,
+  PLUGIN_TYPE,
+  PLUGIN_AUTHOR,
+  PLUGIN_DESCRIPTION,
+  PLUGIN_LICENSE,
+  PLUGIN_MATURITY,
+  PLUGIN_AUTH_VERSION
+FROM INFORMATION_SCHEMA.PLUGINS
+WHERE PLUGIN_TYPE='FUNCTION'
+  AND PLUGIN_NAME IN
+    ('uuid')
+ORDER BY PLUGIN_NAME;
+--horizontal_results
+
+--echo #
+--echo # End of 10.5 tests
+--echo #
diff --git a/plugin/type_uuid/mysql-test/type_uuid/rpl_row_binary_to_uuid.result b/plugin/type_uuid/mysql-test/type_uuid/rpl_row_binary_to_uuid.result
new file mode 100644
index 00000000000..553c8c6eacd
--- /dev/null
+++ b/plugin/type_uuid/mysql-test/type_uuid/rpl_row_binary_to_uuid.result
@@ -0,0 +1,35 @@
+include/master-slave.inc
+[connection master]
+#
+# Start of 10.5 tests
+#
+#
+# MDEV-4958 Adding datatype UUID
+#
+CREATE TABLE t1 (a BINARY(16));
+connection slave;
+ALTER TABLE t1 MODIFY a UUID;
+connection master;
+INSERT INTO t1 VALUES (X'00000000000000000000000000000000');
+INSERT INTO t1 VALUES (X'00000000000000000000000000000001');
+INSERT INTO t1 VALUES (X'fffffffffffffffffffffffffffffffe');
+INSERT INTO t1 VALUES (X'ffffffffffffffffffffffffffffffff');
+SELECT HEX(a) FROM t1 ORDER BY a;
+HEX(a)
+00000000000000000000000000000000
+00000000000000000000000000000001
+FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFE
+FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF
+connection slave;
+SELECT * FROM t1 ORDER BY a;
+a
+00000000-0000-0000-0000-000000000000
+00000000-0000-0000-0000-000000000001
+ffffffff-ffff-ffff-ffff-fffffffffffe
+ffffffff-ffff-ffff-ffff-ffffffffffff
+connection master;
+DROP TABLE t1;
+#
+# End of 10.5 tests
+#
+include/rpl_end.inc
diff --git a/plugin/type_uuid/mysql-test/type_uuid/rpl_row_binary_to_uuid.test b/plugin/type_uuid/mysql-test/type_uuid/rpl_row_binary_to_uuid.test
new file mode 100644
index 00000000000..6f3f5caebc4
--- /dev/null
+++ b/plugin/type_uuid/mysql-test/type_uuid/rpl_row_binary_to_uuid.test
@@ -0,0 +1,33 @@
+--source include/have_binlog_format_row.inc
+--source include/master-slave.inc
+
+--echo #
+--echo # Start of 10.5 tests
+--echo #
+
+--echo #
+--echo # MDEV-4958 Adding datatype UUID
+--echo #
+
+CREATE TABLE t1 (a BINARY(16));
+
+--sync_slave_with_master
+ALTER TABLE t1 MODIFY a UUID;
+
+--connection master
+INSERT INTO t1 VALUES (X'00000000000000000000000000000000');
+INSERT INTO t1 VALUES (X'00000000000000000000000000000001');
+INSERT INTO t1 VALUES (X'fffffffffffffffffffffffffffffffe');
+INSERT INTO t1 VALUES (X'ffffffffffffffffffffffffffffffff');
+SELECT HEX(a) FROM t1 ORDER BY a;
+--sync_slave_with_master
+SELECT * FROM t1 ORDER BY a;
+
+--connection master
+DROP TABLE t1;
+
+--echo #
+--echo # End of 10.5 tests
+--echo #
+
+--source include/rpl_end.inc
diff --git a/plugin/type_uuid/mysql-test/type_uuid/rpl_row_uuid_to_binary.result b/plugin/type_uuid/mysql-test/type_uuid/rpl_row_uuid_to_binary.result
new file mode 100644
index 00000000000..a917a6830b0
--- /dev/null
+++ b/plugin/type_uuid/mysql-test/type_uuid/rpl_row_uuid_to_binary.result
@@ -0,0 +1,35 @@
+include/master-slave.inc
+[connection master]
+#
+# Start of 10.5 tests
+#
+#
+# MDEV-4958 Adding datatype UUID
+#
+CREATE TABLE t1 (a UUID);
+connection slave;
+ALTER TABLE t1 MODIFY a BINARY(16);
+connection master;
+INSERT INTO t1 VALUES ('00000000-0000-0000-0000-000000000000');
+INSERT INTO t1 VALUES ('00000000-0000-0000-0000-000000000001');
+INSERT INTO t1 VALUES ('ffffffff-ffff-ffff-ffff-fffffffffffe');
+INSERT INTO t1 VALUES ('ffffffff-ffff-ffff-ffff-ffffffffffff');
+SELECT a FROM t1 ORDER BY a;
+a
+00000000-0000-0000-0000-000000000000
+00000000-0000-0000-0000-000000000001
+ffffffff-ffff-ffff-ffff-fffffffffffe
+ffffffff-ffff-ffff-ffff-ffffffffffff
+connection slave;
+SELECT HEX(a) FROM t1 ORDER BY a;
+HEX(a)
+00000000000000000000000000000000
+00000000000000000000000000000001
+FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFE
+FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF
+connection master;
+DROP TABLE t1;
+#
+# End of 10.5 tests
+#
+include/rpl_end.inc
diff --git a/plugin/type_uuid/mysql-test/type_uuid/rpl_row_uuid_to_binary.test b/plugin/type_uuid/mysql-test/type_uuid/rpl_row_uuid_to_binary.test
new file mode 100644
index 00000000000..df927fafa02
--- /dev/null
+++ b/plugin/type_uuid/mysql-test/type_uuid/rpl_row_uuid_to_binary.test
@@ -0,0 +1,33 @@
+--source include/have_binlog_format_row.inc
+--source include/master-slave.inc
+
+--echo #
+--echo # Start of 10.5 tests
+--echo #
+
+--echo #
+--echo # MDEV-4958 Adding datatype UUID
+--echo #
+
+CREATE TABLE t1 (a UUID);
+
+--sync_slave_with_master
+ALTER TABLE t1 MODIFY a BINARY(16);
+
+--connection master
+INSERT INTO t1 VALUES ('00000000-0000-0000-0000-000000000000');
+INSERT INTO t1 VALUES ('00000000-0000-0000-0000-000000000001');
+INSERT INTO t1 VALUES ('ffffffff-ffff-ffff-ffff-fffffffffffe');
+INSERT INTO t1 VALUES ('ffffffff-ffff-ffff-ffff-ffffffffffff');
+SELECT a FROM t1 ORDER BY a;
+--sync_slave_with_master
+SELECT HEX(a) FROM t1 ORDER BY a;
+
+--connection master
+DROP TABLE t1;
+
+--echo #
+--echo # End of 10.5 tests
+--echo #
+
+--source include/rpl_end.inc
diff --git a/plugin/type_uuid/mysql-test/type_uuid/rpl_type_uuid.result b/plugin/type_uuid/mysql-test/type_uuid/rpl_type_uuid.result
new file mode 100644
index 00000000000..c3825570a5c
--- /dev/null
+++ b/plugin/type_uuid/mysql-test/type_uuid/rpl_type_uuid.result
@@ -0,0 +1,19 @@
+include/master-slave.inc
+[connection master]
+#
+# MDEV-4958 Adding datatype UUID
+#
+connection master;
+CREATE TABLE t1 (a UUID);
+INSERT INTO t1 VALUES
+('00000000-0000-0000-0000-000000000000'),
+('ffffffff-ffff-ffff-ffff-ffffffffffff');
+connection slave;
+SELECT HEX(a), a FROM t1;
+HEX(a)	a
+00000000000000000000000000000000	00000000-0000-0000-0000-000000000000
+FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF	ffffffff-ffff-ffff-ffff-ffffffffffff
+connection master;
+DROP TABLE t1;
+connection slave;
+include/rpl_end.inc
diff --git a/plugin/type_uuid/mysql-test/type_uuid/rpl_type_uuid.test b/plugin/type_uuid/mysql-test/type_uuid/rpl_type_uuid.test
new file mode 100644
index 00000000000..7651a0d5e02
--- /dev/null
+++ b/plugin/type_uuid/mysql-test/type_uuid/rpl_type_uuid.test
@@ -0,0 +1,18 @@
+--source include/master-slave.inc
+
+--echo #
+--echo # MDEV-4958 Adding datatype UUID
+--echo #
+
+connection master;
+CREATE TABLE t1 (a UUID);
+INSERT INTO t1 VALUES
+('00000000-0000-0000-0000-000000000000'),
+('ffffffff-ffff-ffff-ffff-ffffffffffff');
+sync_slave_with_master;
+SELECT HEX(a), a FROM t1;
+connection master;
+DROP TABLE t1;
+sync_slave_with_master;
+
+--source include/rpl_end.inc
diff --git a/plugin/type_uuid/mysql-test/type_uuid/suite.pm b/plugin/type_uuid/mysql-test/type_uuid/suite.pm
new file mode 100644
index 00000000000..ad21c8688de
--- /dev/null
+++ b/plugin/type_uuid/mysql-test/type_uuid/suite.pm
@@ -0,0 +1,7 @@
+package My::Suite::Type_uuid;
+
+@ISA = qw(My::Suite);
+
+sub is_default { 1 }
+
+bless { };
diff --git a/plugin/type_uuid/mysql-test/type_uuid/type_uuid-debug.result b/plugin/type_uuid/mysql-test/type_uuid/type_uuid-debug.result
new file mode 100644
index 00000000000..0416c39c6f9
--- /dev/null
+++ b/plugin/type_uuid/mysql-test/type_uuid/type_uuid-debug.result
@@ -0,0 +1,18 @@
+#
+# MDEV-4958 Adding datatype UUID
+#
+SET @old_debug_dbug=@@debug_dbug;
+SET debug_dbug="+d,frm_data_type_info";
+CREATE TABLE t1 (c01 UUID, c02 UUID);
+Warnings:
+Note	1105	build_frm_image: Field data type info length: 12
+Note	1105	DBUG: [0] name='c01' type_info='uuid'
+Note	1105	DBUG: [1] name='c02' type_info='uuid'
+SET debug_dbug=@old_debug_dbug;
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `c01` uuid DEFAULT NULL,
+  `c02` uuid DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+DROP TABLE t1;
diff --git a/plugin/type_uuid/mysql-test/type_uuid/type_uuid-debug.test b/plugin/type_uuid/mysql-test/type_uuid/type_uuid-debug.test
new file mode 100644
index 00000000000..59300d167c9
--- /dev/null
+++ b/plugin/type_uuid/mysql-test/type_uuid/type_uuid-debug.test
@@ -0,0 +1,14 @@
+--source include/have_debug.inc
+
+--echo #
+--echo # MDEV-4958 Adding datatype UUID
+--echo #
+
+SET @old_debug_dbug=@@debug_dbug;
+
+SET debug_dbug="+d,frm_data_type_info";
+CREATE TABLE t1 (c01 UUID, c02 UUID);
+SET debug_dbug=@old_debug_dbug;
+
+SHOW CREATE TABLE t1;
+DROP TABLE t1;
diff --git a/plugin/type_uuid/mysql-test/type_uuid/type_uuid.result b/plugin/type_uuid/mysql-test/type_uuid/type_uuid.result
new file mode 100644
index 00000000000..6a08ffe8c23
--- /dev/null
+++ b/plugin/type_uuid/mysql-test/type_uuid/type_uuid.result
@@ -0,0 +1,2059 @@
+#
+# MDEV-4958 Adding datatype UUID
+#
+#
+# Basic CREATE functionality, defaults, metadata
+#
+CREATE TABLE t1 (a UUID AUTO_INCREMENT);
+ERROR 42000: Incorrect column specifier for column 'a'
+CREATE TABLE t1 (a UUID);
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` uuid DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+DESCRIBE t1;
+Field	Type	Null	Key	Default	Extra
+a	uuid	YES		NULL	
+SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE table_schema='test' AND table_name='t1';
+TABLE_CATALOG	def
+TABLE_SCHEMA	test
+TABLE_NAME	t1
+COLUMN_NAME	a
+ORDINAL_POSITION	1
+COLUMN_DEFAULT	NULL
+IS_NULLABLE	YES
+DATA_TYPE	uuid
+CHARACTER_MAXIMUM_LENGTH	NULL
+CHARACTER_OCTET_LENGTH	NULL
+NUMERIC_PRECISION	NULL
+NUMERIC_SCALE	NULL
+DATETIME_PRECISION	NULL
+CHARACTER_SET_NAME	NULL
+COLLATION_NAME	NULL
+COLUMN_TYPE	uuid
+COLUMN_KEY	
+EXTRA	
+PRIVILEGES	#
+COLUMN_COMMENT	
+IS_GENERATED	NEVER
+GENERATION_EXPRESSION	NULL
+DROP TABLE t1;
+CREATE TABLE t1 (a UUID);
+INSERT INTO t1 VALUES ('00000000-0000-0000-0000-000000000001');
+SELECT * FROM t1;
+Catalog	Database	Table	Table_alias	Column	Column_alias	Type	Length	Max length	Is_null	Flags	Decimals	Charsetnr
+def	test	t1	t1	a	a	254 (type=uuid)	36	36	Y	160	0	8
+a
+00000000-0000-0000-0000-000000000001
+SELECT CAST('00000000-0000-0000-0000-000000000001' AS UUID) AS a;
+Catalog	Database	Table	Table_alias	Column	Column_alias	Type	Length	Max length	Is_null	Flags	Decimals	Charsetnr
+def					a	254 (type=uuid)	36	36	N	33	0	8
+a
+00000000-0000-0000-0000-000000000001
+DROP TABLE t1;
+CREATE TABLE t1 (
+c1 UUID DEFAULT 0x00000000000000000000000000000000,
+c2 UUID DEFAULT 0xFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF,
+c3 UUID DEFAULT '00000000-0000-0000-0000-000000000000',
+c4 UUID DEFAULT 'ffffffff-ffff-ffff-ffff-ffffffffffff',
+c5 UUID DEFAULT CAST(X'FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF' AS UUID)
+);
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `c1` uuid DEFAULT '00000000-0000-0000-0000-000000000000',
+  `c2` uuid DEFAULT 'ffffffff-ffff-ffff-ffff-ffffffffffff',
+  `c3` uuid DEFAULT '00000000-0000-0000-0000-000000000000',
+  `c4` uuid DEFAULT 'ffffffff-ffff-ffff-ffff-ffffffffffff',
+  `c5` uuid DEFAULT cast(X'ffffffffffffffffffffffffffffffff' as uuid)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+DESCRIBE t1;
+Field	Type	Null	Key	Default	Extra
+c1	uuid	YES		00000000-0000-0000-0000-000000000000	
+c2	uuid	YES		ffffffff-ffff-ffff-ffff-ffffffffffff	
+c3	uuid	YES		00000000-0000-0000-0000-000000000000	
+c4	uuid	YES		ffffffff-ffff-ffff-ffff-ffffffffffff	
+c5	uuid	YES		cast(X'ffffffffffffffffffffffffffffffff' as uuid)	
+SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE table_schema='test' AND table_name='t1';
+TABLE_CATALOG	def
+TABLE_SCHEMA	test
+TABLE_NAME	t1
+COLUMN_NAME	c1
+ORDINAL_POSITION	1
+COLUMN_DEFAULT	'00000000-0000-0000-0000-000000000000'
+IS_NULLABLE	YES
+DATA_TYPE	uuid
+CHARACTER_MAXIMUM_LENGTH	NULL
+CHARACTER_OCTET_LENGTH	NULL
+NUMERIC_PRECISION	NULL
+NUMERIC_SCALE	NULL
+DATETIME_PRECISION	NULL
+CHARACTER_SET_NAME	NULL
+COLLATION_NAME	NULL
+COLUMN_TYPE	uuid
+COLUMN_KEY	
+EXTRA	
+PRIVILEGES	#
+COLUMN_COMMENT	
+IS_GENERATED	NEVER
+GENERATION_EXPRESSION	NULL
+TABLE_CATALOG	def
+TABLE_SCHEMA	test
+TABLE_NAME	t1
+COLUMN_NAME	c2
+ORDINAL_POSITION	2
+COLUMN_DEFAULT	'ffffffff-ffff-ffff-ffff-ffffffffffff'
+IS_NULLABLE	YES
+DATA_TYPE	uuid
+CHARACTER_MAXIMUM_LENGTH	NULL
+CHARACTER_OCTET_LENGTH	NULL
+NUMERIC_PRECISION	NULL
+NUMERIC_SCALE	NULL
+DATETIME_PRECISION	NULL
+CHARACTER_SET_NAME	NULL
+COLLATION_NAME	NULL
+COLUMN_TYPE	uuid
+COLUMN_KEY	
+EXTRA	
+PRIVILEGES	#
+COLUMN_COMMENT	
+IS_GENERATED	NEVER
+GENERATION_EXPRESSION	NULL
+TABLE_CATALOG	def
+TABLE_SCHEMA	test
+TABLE_NAME	t1
+COLUMN_NAME	c3
+ORDINAL_POSITION	3
+COLUMN_DEFAULT	'00000000-0000-0000-0000-000000000000'
+IS_NULLABLE	YES
+DATA_TYPE	uuid
+CHARACTER_MAXIMUM_LENGTH	NULL
+CHARACTER_OCTET_LENGTH	NULL
+NUMERIC_PRECISION	NULL
+NUMERIC_SCALE	NULL
+DATETIME_PRECISION	NULL
+CHARACTER_SET_NAME	NULL
+COLLATION_NAME	NULL
+COLUMN_TYPE	uuid
+COLUMN_KEY	
+EXTRA	
+PRIVILEGES	#
+COLUMN_COMMENT	
+IS_GENERATED	NEVER
+GENERATION_EXPRESSION	NULL
+TABLE_CATALOG	def
+TABLE_SCHEMA	test
+TABLE_NAME	t1
+COLUMN_NAME	c4
+ORDINAL_POSITION	4
+COLUMN_DEFAULT	'ffffffff-ffff-ffff-ffff-ffffffffffff'
+IS_NULLABLE	YES
+DATA_TYPE	uuid
+CHARACTER_MAXIMUM_LENGTH	NULL
+CHARACTER_OCTET_LENGTH	NULL
+NUMERIC_PRECISION	NULL
+NUMERIC_SCALE	NULL
+DATETIME_PRECISION	NULL
+CHARACTER_SET_NAME	NULL
+COLLATION_NAME	NULL
+COLUMN_TYPE	uuid
+COLUMN_KEY	
+EXTRA	
+PRIVILEGES	#
+COLUMN_COMMENT	
+IS_GENERATED	NEVER
+GENERATION_EXPRESSION	NULL
+TABLE_CATALOG	def
+TABLE_SCHEMA	test
+TABLE_NAME	t1
+COLUMN_NAME	c5
+ORDINAL_POSITION	5
+COLUMN_DEFAULT	cast(X'ffffffffffffffffffffffffffffffff' as uuid)
+IS_NULLABLE	YES
+DATA_TYPE	uuid
+CHARACTER_MAXIMUM_LENGTH	NULL
+CHARACTER_OCTET_LENGTH	NULL
+NUMERIC_PRECISION	NULL
+NUMERIC_SCALE	NULL
+DATETIME_PRECISION	NULL
+CHARACTER_SET_NAME	NULL
+COLLATION_NAME	NULL
+COLUMN_TYPE	uuid
+COLUMN_KEY	
+EXTRA	
+PRIVILEGES	#
+COLUMN_COMMENT	
+IS_GENERATED	NEVER
+GENERATION_EXPRESSION	NULL
+DROP TABLE t1;
+CREATE TABLE t1 (c1 UUID DEFAULT 0x00);
+ERROR 42000: Invalid default value for 'c1'
+CREATE TABLE t1 (c1 UUID DEFAULT '');
+ERROR 42000: Invalid default value for 'c1'
+CREATE TABLE t1 (a UUID);
+INSERT INTO t1 VALUES ('x');
+ERROR 22007: Incorrect uuid value: 'x' for column `test`.`t1`.`a` at row 1
+INSERT INTO t1 VALUES (1);
+ERROR 22007: Incorrect uuid value: '1' for column `test`.`t1`.`a` at row 1
+INSERT INTO t1 VALUES (TIME'10:20:30');
+ERROR 22007: Incorrect uuid value: '10:20:30' for column `test`.`t1`.`a` at row 1
+INSERT INTO t1 VALUES (0x00);
+ERROR 22007: Incorrect uuid value: '\x00' for column `test`.`t1`.`a` at row 1
+DROP TABLE t1;
+#
+# CAST
+#
+SELECT CAST('garbage' AS UUID);
+CAST('garbage' AS UUID)
+NULL
+Warnings:
+Warning	1292	Incorrect uuid value: 'garbage'
+SELECT CAST(0x01 AS UUID);
+CAST(0x01 AS UUID)
+NULL
+Warnings:
+Warning	1292	Incorrect uuid value: '\x01'
+SELECT CAST(REPEAT(0x00,16) AS UUID);
+CAST(REPEAT(0x00,16) AS UUID)
+00000000-0000-0000-0000-000000000000
+SELECT CAST(REPEAT(0x11,16) AS UUID);
+CAST(REPEAT(0x11,16) AS UUID)
+11111111-1111-1111-1111-111111111111
+CREATE TABLE t1 AS SELECT CAST('00000000-0000-0000-0000-000000000000' AS UUID);
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `CAST('00000000-0000-0000-0000-000000000000' AS UUID)` uuid NOT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+DROP TABLE t1;
+#
+# Text and binary formats, comparison operators
+#
+CREATE TABLE t1 (a UUID);
+INSERT INTO t1 VALUES (0x00000000000000000000000000000000);
+INSERT INTO t1 VALUES (0x00000000000000000000000000000001);
+INSERT INTO t1 VALUES (0xFFFF0000000000000000000000000001);
+INSERT INTO t1 VALUES (0xFFFF0000000000000000000000000002);
+SELECT * FROM t1 ORDER BY a;
+a
+00000000-0000-0000-0000-000000000000
+00000000-0000-0000-0000-000000000001
+ffff0000-0000-0000-0000-000000000001
+ffff0000-0000-0000-0000-000000000002
+SELECT * FROM t1 ORDER BY a DESC;
+a
+ffff0000-0000-0000-0000-000000000002
+ffff0000-0000-0000-0000-000000000001
+00000000-0000-0000-0000-000000000001
+00000000-0000-0000-0000-000000000000
+SELECT HEX(a),a FROM t1 ORDER BY a;
+HEX(a)	a
+00000000000000000000000000000000	00000000-0000-0000-0000-000000000000
+00000000000000000000000000000001	00000000-0000-0000-0000-000000000001
+FFFF0000000000000000000000000001	ffff0000-0000-0000-0000-000000000001
+FFFF0000000000000000000000000002	ffff0000-0000-0000-0000-000000000002
+SELECT * FROM t1 WHERE a='00000000-0000-0000-0000-000000000000';
+a
+00000000-0000-0000-0000-000000000000
+SELECT * FROM t1 WHERE a='00000000-0000-0000-0000-000000000001';
+a
+00000000-0000-0000-0000-000000000001
+SELECT * FROM t1 WHERE a='ffff0000-0000-0000-0000-000000000001';
+a
+ffff0000-0000-0000-0000-000000000001
+SELECT * FROM t1 WHERE a='ffff0000-0000-0000-0000-000000000002';
+a
+ffff0000-0000-0000-0000-000000000002
+SELECT * FROM t1 WHERE a=0x00000000000000000000000000000000;
+a
+00000000-0000-0000-0000-000000000000
+SELECT * FROM t1 WHERE a=0x00000000000000000000000000000001;
+a
+00000000-0000-0000-0000-000000000001
+SELECT * FROM t1 WHERE a=0xffff0000000000000000000000000001;
+a
+ffff0000-0000-0000-0000-000000000001
+SELECT * FROM t1 WHERE a=0xffff0000000000000000000000000002;
+a
+ffff0000-0000-0000-0000-000000000002
+SELECT * FROM t1 WHERE a< '00000000-0000-0000-0000-000000000000';
+a
+SELECT * FROM t1 WHERE a<='00000000-0000-0000-0000-000000000000';
+a
+00000000-0000-0000-0000-000000000000
+SELECT * FROM t1 WHERE a>='ffff0000-0000-0000-0000-000000000002';
+a
+ffff0000-0000-0000-0000-000000000002
+SELECT * FROM t1 WHERE a> 'ffff0000-0000-0000-0000-000000000002';
+a
+SELECT * FROM t1 WHERE a IN
+(
+'00000000-0000-0000-0000-000000000000',
+'ffff0000-0000-0000-0000-000000000001'
+) ORDER BY a;
+a
+00000000-0000-0000-0000-000000000000
+ffff0000-0000-0000-0000-000000000001
+SELECT * FROM t1 WHERE a IN
+(
+'00000000-0000-0000-0000-000000000000',
+0xffff0000000000000000000000000002
+) ORDER BY a;
+a
+00000000-0000-0000-0000-000000000000
+ffff0000-0000-0000-0000-000000000002
+SELECT * FROM t1 WHERE a<'garbage';
+a
+Warnings:
+Warning	1292	Incorrect uuid value: 'garbage'
+SELECT * FROM t1 WHERE a<='garbage';
+a
+Warnings:
+Warning	1292	Incorrect uuid value: 'garbage'
+SELECT * FROM t1 WHERE a='garbage';
+a
+Warnings:
+Warning	1292	Incorrect uuid value: 'garbage'
+Warning	1292	Incorrect uuid value: 'garbage'
+SELECT * FROM t1 WHERE a>='garbage';
+a
+Warnings:
+Warning	1292	Incorrect uuid value: 'garbage'
+SELECT * FROM t1 WHERE a>'garbage';
+a
+Warnings:
+Warning	1292	Incorrect uuid value: 'garbage'
+SELECT * FROM t1 WHERE a<0x01;
+a
+Warnings:
+Warning	1292	Incorrect uuid value: '\x01'
+SELECT * FROM t1 WHERE a<=0x01;
+a
+Warnings:
+Warning	1292	Incorrect uuid value: '\x01'
+SELECT * FROM t1 WHERE a=0x01;
+a
+Warnings:
+Warning	1292	Incorrect uuid value: '\x01'
+Warning	1292	Incorrect uuid value: '\x01'
+SELECT * FROM t1 WHERE a>=0x01;
+a
+Warnings:
+Warning	1292	Incorrect uuid value: '\x01'
+SELECT * FROM t1 WHERE a>0x01;
+a
+Warnings:
+Warning	1292	Incorrect uuid value: '\x01'
+SELECT * FROM t1 WHERE a='00000000-0000-0000-0000-000000000000';
+a
+00000000-0000-0000-0000-000000000000
+SELECT * FROM t1 WHERE a='00-000000-0000-0000-0000-000000000000';
+a
+00000000-0000-0000-0000-000000000000
+SELECT * FROM t1 WHERE a='00-00-0000-0000-0000-0000-000000000000';
+a
+00000000-0000-0000-0000-000000000000
+SELECT * FROM t1 WHERE a='00-00-00-00-0000-0000-0000-000000000000';
+a
+00000000-0000-0000-0000-000000000000
+SELECT * FROM t1 WHERE a=0;
+ERROR HY000: Illegal parameter data types uuid and int for operation '='
+SELECT * FROM t1 WHERE a=0.0;
+ERROR HY000: Illegal parameter data types uuid and decimal for operation '='
+SELECT * FROM t1 WHERE a=0e0;
+ERROR HY000: Illegal parameter data types uuid and double for operation '='
+SELECT * FROM t1 WHERE a=TIME'10:20:30';
+ERROR HY000: Illegal parameter data types uuid and time for operation '='
+SELECT * FROM t1 WHERE a IN ('::', 10);
+ERROR HY000: Illegal parameter data types uuid and int for operation 'in'
+DROP TABLE t1;
+#
+# cmp_item_uuid: IN for non-constants
+#
+CREATE TABLE t1 (a UUID, b UUID);
+INSERT INTO t1 VALUES
+(
+'00000000-0000-0000-0000-000000000001',
+'00000000-0000-0000-0000-000000000002'
+);
+SELECT * FROM t1 WHERE '00000000-0000-0000-0000-000000000000' IN (a, b);
+a	b
+SELECT * FROM t1 WHERE '00000000-0000-0000-0000-000000000001' IN (a, b);
+a	b
+00000000-0000-0000-0000-000000000001	00000000-0000-0000-0000-000000000002
+SELECT * FROM t1 WHERE '00-000000-0000-0000-0000-000000000001' IN (a, b);
+a	b
+00000000-0000-0000-0000-000000000001	00000000-0000-0000-0000-000000000002
+SELECT * FROM t1 WHERE '00-00-0000-0000-0000-0000-000000000001' IN (a, b);
+a	b
+00000000-0000-0000-0000-000000000001	00000000-0000-0000-0000-000000000002
+DROP TABLE t1;
+#
+# cmp_item_uuid: DECODE_ORACLE
+#
+CREATE TABLE t1 (a UUID);
+INSERT INTO t1 VALUES
+(NULL),
+('00000000-0000-0000-0000-000000000001'),
+('00000000-0000-0000-0000-000000000002');
+SELECT a, DECODE_ORACLE(a, '00000000-0000-0000-0000-000000000002', '01') AS d FROM t1;
+a	d
+NULL	NULL
+00000000-0000-0000-0000-000000000001	NULL
+00000000-0000-0000-0000-000000000002	01
+SELECT
+a,
+DECODE_ORACLE(a, '00000000-0000-0000-0000-000000000001', '01') AS d0,
+DECODE_ORACLE(a, NULL, '<NULL>', '00000000-0000-0000-0000-000000000001', '01') AS d1,
+DECODE_ORACLE(a, 'garbage', '<NULL>', '00000000-0000-0000-0000-000000000001', '01') AS d2
+FROM t1;
+a	d0	d1	d2
+NULL	NULL	<NULL>	<NULL>
+00000000-0000-0000-0000-000000000001	01	01	01
+00000000-0000-0000-0000-000000000002	NULL	NULL	NULL
+Warnings:
+Warning	1292	Incorrect uuid value: 'garbage'
+Warning	1292	Incorrect uuid value: 'garbage'
+Warning	1292	Incorrect uuid value: 'garbage'
+DROP TABLE t1;
+#
+# CASE abbreviations
+#
+CREATE TABLE t1 (
+c UUID,
+c_char CHAR(32),
+c_varchar VARCHAR(32),
+c_tinytext TINYTEXT,
+c_text TEXT,
+c_mediumtext TEXT,
+c_longtext LONGTEXT
+);
+CREATE TABLE t2 AS SELECT
+COALESCE(c, c_char),
+COALESCE(c, c_varchar),
+COALESCE(c, c_tinytext),
+COALESCE(c, c_text),
+COALESCE(c, c_mediumtext),
+COALESCE(c, c_longtext)
+FROM t1;
+SHOW CREATE TABLE t2;
+Table	Create Table
+t2	CREATE TABLE `t2` (
+  `COALESCE(c, c_char)` uuid DEFAULT NULL,
+  `COALESCE(c, c_varchar)` uuid DEFAULT NULL,
+  `COALESCE(c, c_tinytext)` uuid DEFAULT NULL,
+  `COALESCE(c, c_text)` uuid DEFAULT NULL,
+  `COALESCE(c, c_mediumtext)` uuid DEFAULT NULL,
+  `COALESCE(c, c_longtext)` uuid DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+DROP TABLE t2;
+CREATE TABLE t2 AS SELECT
+LEAST(c, c_char),
+LEAST(c, c_varchar),
+LEAST(c, c_tinytext),
+LEAST(c, c_text),
+LEAST(c, c_mediumtext),
+LEAST(c, c_longtext)
+FROM t1;
+SHOW CREATE TABLE t2;
+Table	Create Table
+t2	CREATE TABLE `t2` (
+  `LEAST(c, c_char)` uuid DEFAULT NULL,
+  `LEAST(c, c_varchar)` uuid DEFAULT NULL,
+  `LEAST(c, c_tinytext)` uuid DEFAULT NULL,
+  `LEAST(c, c_text)` uuid DEFAULT NULL,
+  `LEAST(c, c_mediumtext)` uuid DEFAULT NULL,
+  `LEAST(c, c_longtext)` uuid DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+DROP TABLE t2;
+DROP TABLE t1;
+CREATE TABLE t1 (a UUID);
+INSERT INTO t1 VALUES
+(NULL),
+('00000000-0000-0000-0000-000000000001'),
+('00000000-0000-0000-0000-000000000002');
+SELECT COALESCE(a, '00000000-0000-0000-0000-000000000000') FROM t1 ORDER BY a;
+COALESCE(a, '00000000-0000-0000-0000-000000000000')
+00000000-0000-0000-0000-000000000000
+00000000-0000-0000-0000-000000000001
+00000000-0000-0000-0000-000000000002
+SELECT
+a,
+LEAST(a,'00000000-0000-0000-0000-000000000000'),
+LEAST(a,'00000000-0000-0000-0000-00000000000f')
+FROM t1 ORDER BY a;
+a	LEAST(a,'00000000-0000-0000-0000-000000000000')	LEAST(a,'00000000-0000-0000-0000-00000000000f')
+NULL	NULL	NULL
+00000000-0000-0000-0000-000000000001	00000000-0000-0000-0000-000000000000	00000000-0000-0000-0000-000000000001
+00000000-0000-0000-0000-000000000002	00000000-0000-0000-0000-000000000000	00000000-0000-0000-0000-000000000002
+SELECT
+a,
+GREATEST(a,'00000000-0000-0000-0000-000000000000'),
+GREATEST(a,'00000000-0000-0000-0000-00000000000f')
+FROM t1 ORDER BY a;
+a	GREATEST(a,'00000000-0000-0000-0000-000000000000')	GREATEST(a,'00000000-0000-0000-0000-00000000000f')
+NULL	NULL	NULL
+00000000-0000-0000-0000-000000000001	00000000-0000-0000-0000-000000000001	00000000-0000-0000-0000-00000000000f
+00000000-0000-0000-0000-000000000002	00000000-0000-0000-0000-000000000002	00000000-0000-0000-0000-00000000000f
+CREATE TABLE t2 AS SELECT
+COALESCE(a, '00000000-0000-0000-0000-000000000000'),
+LEAST(a,'00000000-0000-0000-0000-000000000000'),
+GREATEST(a,'00000000-0000-0000-0000-000000000000')
+FROM t1;
+SHOW CREATE TABLE t2;
+Table	Create Table
+t2	CREATE TABLE `t2` (
+  `COALESCE(a, '00000000-0000-0000-0000-000000000000')` uuid DEFAULT NULL,
+  `LEAST(a,'00000000-0000-0000-0000-000000000000')` uuid DEFAULT NULL,
+  `GREATEST(a,'00000000-0000-0000-0000-000000000000')` uuid DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+DROP TABLE t2;
+SELECT COALESCE(a, 0x00000000000000000000000000000000) FROM t1 ORDER BY a;
+COALESCE(a, 0x00000000000000000000000000000000)
+00000000-0000-0000-0000-000000000000
+00000000-0000-0000-0000-000000000001
+00000000-0000-0000-0000-000000000002
+SELECT
+a,
+LEAST(a, 0x00000000000000000000000000000000),
+LEAST(a, 0x0000000000000000000000000000000f)
+FROM t1 ORDER BY a;
+a	LEAST(a, 0x00000000000000000000000000000000)	LEAST(a, 0x0000000000000000000000000000000f)
+NULL	NULL	NULL
+00000000-0000-0000-0000-000000000001	00000000-0000-0000-0000-000000000000	00000000-0000-0000-0000-000000000001
+00000000-0000-0000-0000-000000000002	00000000-0000-0000-0000-000000000000	00000000-0000-0000-0000-000000000002
+SELECT
+a,
+GREATEST(a, 0x00000000000000000000000000000000),
+GREATEST(a, 0x0000000000000000000000000000000f)
+FROM t1 ORDER BY a;
+a	GREATEST(a, 0x00000000000000000000000000000000)	GREATEST(a, 0x0000000000000000000000000000000f)
+NULL	NULL	NULL
+00000000-0000-0000-0000-000000000001	00000000-0000-0000-0000-000000000001	00000000-0000-0000-0000-00000000000f
+00000000-0000-0000-0000-000000000002	00000000-0000-0000-0000-000000000002	00000000-0000-0000-0000-00000000000f
+CREATE TABLE t2 AS SELECT
+COALESCE(a, 0x00000000000000000000000000000000),
+LEAST(a,0x00000000000000000000000000000000),
+GREATEST(a,0x00000000000000000000000000000000)
+FROM t1;
+SHOW CREATE TABLE t2;
+Table	Create Table
+t2	CREATE TABLE `t2` (
+  `COALESCE(a, 0x00000000000000000000000000000000)` uuid DEFAULT NULL,
+  `LEAST(a,0x00000000000000000000000000000000)` uuid DEFAULT NULL,
+  `GREATEST(a,0x00000000000000000000000000000000)` uuid DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+DROP TABLE t2;
+SELECT COALESCE(a, 10) FROM t1;
+ERROR HY000: Illegal parameter data types uuid and int for operation 'coalesce'
+SELECT LEAST(a, 10) FROM t1;
+ERROR HY000: Illegal parameter data types uuid and int for operation 'least'
+SELECT GREATEST(a, 10) FROM t1;
+ERROR HY000: Illegal parameter data types uuid and int for operation 'greatest'
+DROP TABLE t1;
+SELECT COALESCE('garbage', CAST('::1' AS UUID));
+COALESCE('garbage', CAST('::1' AS UUID))
+NULL
+Warnings:
+Warning	1292	Incorrect uuid value: 'garbage'
+Warning	1292	Incorrect uuid value: '::1'
+SELECT COALESCE(0x01, CAST('::1' AS UUID));
+COALESCE(0x01, CAST('::1' AS UUID))
+NULL
+Warnings:
+Warning	1292	Incorrect uuid value: '\x01'
+Warning	1292	Incorrect uuid value: '::1'
+#
+# Uniqueness
+#
+CREATE TABLE t1 (a UUID NOT NULL PRIMARY KEY);
+INSERT INTO t1 VALUES
+('41000000-0000-0000-0000-000000000001'),
+('61000000-0000-0000-0000-000000000001');
+INSERT INTO t1 VALUES ('41000000-0000-0000-0000-000000000001');
+ERROR 23000: Duplicate entry '41000000-0000-0000-0000-000000000001' for key 'PRIMARY'
+SELECT * FROM t1;
+a
+41000000-0000-0000-0000-000000000001
+61000000-0000-0000-0000-000000000001
+DROP TABLE t1;
+#
+# Indexes
+#
+CREATE TABLE t1 (a UUID, KEY(a(1)));
+ERROR HY000: Incorrect prefix key; the used key part isn't a string, the used length is longer than the key part, or the storage engine doesn't support unique prefix keys
+#
+# Explicit CAST on INSERT
+#
+CREATE TABLE t1 (a UUID);
+INSERT INTO t1 VALUES (CAST('10000000-0000-0000-0000-000000000001' AS UUID));
+INSERT INTO t1 VALUES (CAST('10000000-0000-0000-0000-000000000002' AS UUID));
+INSERT INTO t1 VALUES (CAST('10000000-0000-0000-0000-000000000003' AS UUID));
+INSERT INTO t1 VALUES (CAST(CONCAT('2','0000000-0000-0000-0000-000000000001') AS UUID));
+INSERT INTO t1 VALUES (CAST(CONCAT('2','0000000-0000-0000-0000-000000000002') AS UUID));
+INSERT INTO t1 VALUES (CAST(CONCAT('2','0000000-0000-0000-0000-000000000003') AS UUID));
+SELECT * FROM t1 ORDER BY a;
+a
+10000000-0000-0000-0000-000000000001
+10000000-0000-0000-0000-000000000002
+10000000-0000-0000-0000-000000000003
+20000000-0000-0000-0000-000000000001
+20000000-0000-0000-0000-000000000002
+20000000-0000-0000-0000-000000000003
+DROP TABLE t1;
+#
+# Explicit CAST and implicit CAST on ALTER
+#
+CREATE TABLE t1 (a VARCHAR(64));
+INSERT INTO t1 VALUES
+('garbage'),
+('00000000-0000-0000-0000-000000000000'),
+('00000000-0000-0000-0000-000000000001'),
+('ffff0000-0000-0000-0000-000000000001'),
+('ffff0000-0000-0000-0000-000000000002');
+SELECT a, CAST(a AS UUID) FROM t1 ORDER BY a;
+a	CAST(a AS UUID)
+00000000-0000-0000-0000-000000000000	00000000-0000-0000-0000-000000000000
+00000000-0000-0000-0000-000000000001	00000000-0000-0000-0000-000000000001
+ffff0000-0000-0000-0000-000000000001	ffff0000-0000-0000-0000-000000000001
+ffff0000-0000-0000-0000-000000000002	ffff0000-0000-0000-0000-000000000002
+garbage	NULL
+Warnings:
+Warning	1292	Incorrect uuid value: 'garbage'
+SELECT a, CAST(a AS UUID) FROM t1 ORDER BY CAST(a AS UUID);
+a	CAST(a AS UUID)
+garbage	NULL
+00000000-0000-0000-0000-000000000000	00000000-0000-0000-0000-000000000000
+00000000-0000-0000-0000-000000000001	00000000-0000-0000-0000-000000000001
+ffff0000-0000-0000-0000-000000000001	ffff0000-0000-0000-0000-000000000001
+ffff0000-0000-0000-0000-000000000002	ffff0000-0000-0000-0000-000000000002
+Warnings:
+Warning	1292	Incorrect uuid value: 'garbage'
+Warning	1292	Incorrect uuid value: 'garbage'
+ALTER TABLE t1 MODIFY a UUID;
+ERROR 22007: Incorrect uuid value: 'garbage' for column `test`.`t1`.`a` at row 1
+SET sql_mode='';
+ALTER TABLE t1 MODIFY a UUID;
+Warnings:
+Warning	1292	Incorrect uuid value: 'garbage' for column `test`.`t1`.`a` at row 1
+SET sql_mode=DEFAULT;
+SELECT * FROM t1 ORDER BY a;
+a
+NULL
+00000000-0000-0000-0000-000000000000
+00000000-0000-0000-0000-000000000001
+ffff0000-0000-0000-0000-000000000001
+ffff0000-0000-0000-0000-000000000002
+DROP TABLE t1;
+CREATE TABLE t1 (a BINARY(16));
+INSERT INTO t1 VALUES (0x00000000000000000000000000000000);
+INSERT INTO t1 VALUES (0x00000000000000000000000000000001);
+INSERT INTO t1 VALUES (0xffff0000000000000000000000000001);
+INSERT INTO t1 VALUES (0xffff0000000000000000000000000002);
+SELECT HEX(a), CAST(a AS UUID) FROM t1 ORDER BY a;
+HEX(a)	CAST(a AS UUID)
+00000000000000000000000000000000	00000000-0000-0000-0000-000000000000
+00000000000000000000000000000001	00000000-0000-0000-0000-000000000001
+FFFF0000000000000000000000000001	ffff0000-0000-0000-0000-000000000001
+FFFF0000000000000000000000000002	ffff0000-0000-0000-0000-000000000002
+ALTER TABLE t1 MODIFY a UUID;
+SELECT * FROM t1 ORDER BY a;
+a
+00000000-0000-0000-0000-000000000000
+00000000-0000-0000-0000-000000000001
+ffff0000-0000-0000-0000-000000000001
+ffff0000-0000-0000-0000-000000000002
+DROP TABLE t1;
+#
+# INSERT..SELECT, same data types
+#
+CREATE TABLE t1 (a UUID);
+INSERT INTO t1 VALUES 
+('00000000-0000-0000-0000-000000000000'),
+('00000000-0000-0000-0000-000000000001'),
+('00000000-0000-0000-0000-000000000002');
+CREATE TABLE t2 (a UUID);
+INSERT INTO t2 SELECT a FROM t1;
+SELECT * FROM t2;
+a
+00000000-0000-0000-0000-000000000000
+00000000-0000-0000-0000-000000000001
+00000000-0000-0000-0000-000000000002
+DROP TABLE t1,t2;
+#
+# Implicit CAST on INSERT..SELECT, text format
+#
+CREATE TABLE t1 (a VARCHAR(64));
+INSERT INTO t1 VALUES
+('garbage'),
+('00000000-0000-0000-0000-000000000000'),
+('00000000-0000-0000-0000-000000000001'),
+('ffff0000-0000-0000-0000-000000000001'),
+('ffff0000-0000-0000-0000-000000000002');
+CREATE TABLE t2 (a UUID);
+INSERT INTO t2 SELECT a FROM t1;
+ERROR 22007: Incorrect uuid value: 'garbage' for column `test`.`t2`.`a` at row 1
+SET sql_mode='';
+INSERT INTO t2 SELECT a FROM t1;
+Warnings:
+Warning	1292	Incorrect uuid value: 'garbage' for column `test`.`t2`.`a` at row 1
+SELECT * FROM t2 ORDER BY a;
+a
+NULL
+00000000-0000-0000-0000-000000000000
+00000000-0000-0000-0000-000000000001
+ffff0000-0000-0000-0000-000000000001
+ffff0000-0000-0000-0000-000000000002
+SET sql_mode=DEFAULT;
+DROP TABLE t2;
+CREATE TABLE t2 (a UUID NOT NULL);
+INSERT INTO t2 SELECT a FROM t1;
+ERROR 22007: Incorrect uuid value: 'garbage' for column `test`.`t2`.`a` at row 1
+SET sql_mode='';
+INSERT INTO t2 SELECT a FROM t1;
+Warnings:
+Warning	1292	Incorrect uuid value: 'garbage' for column `test`.`t2`.`a` at row 1
+SELECT * FROM t2 ORDER BY a;
+a
+00000000-0000-0000-0000-000000000000
+00000000-0000-0000-0000-000000000000
+00000000-0000-0000-0000-000000000001
+ffff0000-0000-0000-0000-000000000001
+ffff0000-0000-0000-0000-000000000002
+SET sql_mode=DEFAULT;
+DROP TABLE t2;
+DROP TABLE t1;
+#
+# Implicit CAST on INSERT..SELECT, binary format
+#
+CREATE TABLE t1 (a BINARY(16));
+INSERT INTO t1 VALUES (0x00000000000000000000000000000000);
+INSERT INTO t1 VALUES (0x00000000000000000000000000000001);
+INSERT INTO t1 VALUES (0xffff0000000000000000000000000001);
+INSERT INTO t1 VALUES (0xffff0000000000000000000000000002);
+CREATE TABLE t2 (a UUID);
+INSERT INTO t2 SELECT a FROM t1;
+SELECT a FROM t2 ORDER BY a;
+a
+00000000-0000-0000-0000-000000000000
+00000000-0000-0000-0000-000000000001
+ffff0000-0000-0000-0000-000000000001
+ffff0000-0000-0000-0000-000000000002
+DROP TABLE t1,t2;
+#
+# CAST to other data types
+#
+SELECT CAST(CAST('00000000-0000-0000-0000-000000000000' AS UUID) AS DOUBLE);
+ERROR HY000: Illegal parameter data type uuid for operation 'double_typecast'
+SELECT CAST(CAST('00000000-0000-0000-0000-000000000000' AS UUID) AS FLOAT);
+ERROR HY000: Illegal parameter data type uuid for operation 'float_typecast'
+SELECT CAST(CAST('00000000-0000-0000-0000-000000000000' AS UUID) AS DECIMAL);
+ERROR HY000: Illegal parameter data type uuid for operation 'decimal_typecast'
+SELECT CAST(CAST('00000000-0000-0000-0000-000000000000' AS UUID) AS SIGNED);
+ERROR HY000: Illegal parameter data type uuid for operation 'cast_as_signed'
+SELECT CAST(CAST('00000000-0000-0000-0000-000000000000' AS UUID) AS UNSIGNED);
+ERROR HY000: Illegal parameter data type uuid for operation 'cast_as_unsigned'
+SELECT CAST(CAST('00000000-0000-0000-0000-000000000000' AS UUID) AS TIME);
+ERROR HY000: Illegal parameter data type uuid for operation 'cast_as_time'
+SELECT CAST(CAST('00000000-0000-0000-0000-000000000000' AS UUID) AS DATE);
+ERROR HY000: Illegal parameter data type uuid for operation 'cast_as_date'
+SELECT CAST(CAST('00000000-0000-0000-0000-000000000000' AS UUID) AS DATETIME);
+ERROR HY000: Illegal parameter data type uuid for operation 'cast_as_datetime'
+SELECT CAST(CAST('00000000-0000-0000-0000-000000000000' AS UUID) AS CHAR);
+CAST(CAST('00000000-0000-0000-0000-000000000000' AS UUID) AS CHAR)
+00000000-0000-0000-0000-000000000000
+CREATE TABLE t1 AS SELECT CAST(CAST('00000000-0000-0000-0000-000000000000' AS UUID) AS CHAR) AS a;
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` varchar(36) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+DROP TABLE t1;
+CREATE TABLE t1 (a UUID);
+INSERT INTO t1 VALUES ('ffffffff-ffff-ffff-ffff-ffffffffffff');
+CREATE TABLE t2 AS SELECT
+CAST(a AS CHAR),
+CAST(a AS CHAR(36)),
+CAST(a AS CHAR(530)),
+CAST(a AS CHAR(65535)),
+CAST(a AS CHAR(66000)),
+CAST(a AS CHAR(16777215)),
+CAST(a AS CHAR(16777216))
+FROM t1;
+SHOW CREATE TABLE t2;
+Table	Create Table
+t2	CREATE TABLE `t2` (
+  `CAST(a AS CHAR)` varchar(36) DEFAULT NULL,
+  `CAST(a AS CHAR(36))` varchar(36) DEFAULT NULL,
+  `CAST(a AS CHAR(530))` text DEFAULT NULL,
+  `CAST(a AS CHAR(65535))` text DEFAULT NULL,
+  `CAST(a AS CHAR(66000))` mediumtext DEFAULT NULL,
+  `CAST(a AS CHAR(16777215))` mediumtext DEFAULT NULL,
+  `CAST(a AS CHAR(16777216))` longtext DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+SELECT * FROM t2;
+CAST(a AS CHAR)	ffffffff-ffff-ffff-ffff-ffffffffffff
+CAST(a AS CHAR(36))	ffffffff-ffff-ffff-ffff-ffffffffffff
+CAST(a AS CHAR(530))	ffffffff-ffff-ffff-ffff-ffffffffffff
+CAST(a AS CHAR(65535))	ffffffff-ffff-ffff-ffff-ffffffffffff
+CAST(a AS CHAR(66000))	ffffffff-ffff-ffff-ffff-ffffffffffff
+CAST(a AS CHAR(16777215))	ffffffff-ffff-ffff-ffff-ffffffffffff
+CAST(a AS CHAR(16777216))	ffffffff-ffff-ffff-ffff-ffffffffffff
+DROP TABLE t2;
+DROP TABLE t1;
+CREATE TABLE t1 (a UUID);
+INSERT INTO t1 VALUES ('ffffffff-ffff-ffff-ffff-ffffffffffff');
+CREATE TABLE t2 AS SELECT
+CAST(a AS BINARY(4)) AS cb4,
+CAST(a AS BINARY) AS cb,
+CAST(a AS BINARY(16)) AS cb16,
+CAST(a AS BINARY(32)) AS cb32,
+CAST(a AS BINARY(530)) AS cb530,
+CAST(a AS BINARY(65535)) AS cb65535,
+CAST(a AS BINARY(66000)) AS cb66000,
+CAST(a AS BINARY(16777215)) AS cb16777215,
+CAST(a AS BINARY(16777216)) AS cb16777216
+FROM t1 LIMIT 0;
+SHOW CREATE TABLE t2;
+Table	Create Table
+t2	CREATE TABLE `t2` (
+  `cb4` binary(4) DEFAULT NULL,
+  `cb` binary(16) DEFAULT NULL,
+  `cb16` binary(16) DEFAULT NULL,
+  `cb32` binary(32) DEFAULT NULL,
+  `cb530` varbinary(530) DEFAULT NULL,
+  `cb65535` blob DEFAULT NULL,
+  `cb66000` mediumblob DEFAULT NULL,
+  `cb16777215` mediumblob DEFAULT NULL,
+  `cb16777216` longblob DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+DROP TABLE t2;
+CREATE TABLE t2 AS SELECT
+CAST(a AS BINARY(4)) AS cb4,
+CAST(a AS BINARY) AS cb,
+CAST(a AS BINARY(16)) AS cb16,
+CAST(a AS BINARY(32)) AS cb32,
+CAST(a AS BINARY(530)) AS cb530,
+CAST(a AS BINARY(65535)) AS cb65535
+FROM t1;
+SHOW CREATE TABLE t2;
+Table	Create Table
+t2	CREATE TABLE `t2` (
+  `cb4` binary(4) DEFAULT NULL,
+  `cb` binary(16) DEFAULT NULL,
+  `cb16` binary(16) DEFAULT NULL,
+  `cb32` binary(32) DEFAULT NULL,
+  `cb530` varbinary(530) DEFAULT NULL,
+  `cb65535` blob DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+SELECT
+HEX(cb4),
+HEX(cb),
+HEX(cb16),
+HEX(cb32),
+LENGTH(cb530),
+LENGTH(cb65535)
+FROM t2;
+HEX(cb4)	FFFFFFFF
+HEX(cb)	FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF
+HEX(cb16)	FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF
+HEX(cb32)	FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF00000000000000000000000000000000
+LENGTH(cb530)	530
+LENGTH(cb65535)	65535
+DROP TABLE t2;
+DROP TABLE t1;
+#
+# Implicit conversion to other types in INSERT
+#
+CREATE TABLE t1 (a INT);
+INSERT INTO t1 VALUES (CAST('00000000-0000-0000-0000-000000000000' AS UUID));
+ERROR 01000: Data truncated for column 'a' at row 1
+DROP TABLE t1;
+CREATE TABLE t1 (a DOUBLE);
+INSERT INTO t1 VALUES (CAST('00000000-0000-0000-0000-000000000000' AS UUID));
+ERROR 01000: Data truncated for column 'a' at row 1
+DROP TABLE t1;
+CREATE TABLE t1 (a DECIMAL(32,0));
+INSERT INTO t1 VALUES (CAST('00000000-0000-0000-0000-000000000000' AS UUID));
+ERROR 01000: Data truncated for column 'a' at row 1
+DROP TABLE t1;
+CREATE TABLE t1 (a VARCHAR(64));
+INSERT INTO t1 VALUES (CAST('00000000-0000-0000-0000-000000000000' AS UUID));
+DROP TABLE t1;
+CREATE TABLE t1 (a TEXT);
+INSERT INTO t1 VALUES (CAST('00000000-0000-0000-0000-000000000000' AS UUID));
+DROP TABLE t1;
+#
+# Boolean context
+#
+SELECT
+CAST('00000000-0000-0000-0000-000000000000' AS UUID) IS TRUE, 
+CAST('00000000-0000-0000-0000-000000000000' AS UUID) IS FALSE,
+CAST('00000000-0000-0000-0000-000000000001' AS UUID) IS TRUE,
+CAST('00000000-0000-0000-0000-000000000001' AS UUID) IS FALSE;
+CAST('00000000-0000-0000-0000-000000000000' AS UUID) IS TRUE	CAST('00000000-0000-0000-0000-000000000000' AS UUID) IS FALSE	CAST('00000000-0000-0000-0000-000000000001' AS UUID) IS TRUE	CAST('00000000-0000-0000-0000-000000000001' AS UUID) IS FALSE
+0	1	1	0
+CREATE TABLE t1 (a UUID);
+INSERT INTO t1 VALUES
+('00000000-0000-0000-0000-000000000000'),
+('00000000-0000-0000-0000-000000000001');
+SELECT a, a IS TRUE, a IS FALSE FROM t1 ORDER BY a;
+a	a IS TRUE	a IS FALSE
+00000000-0000-0000-0000-000000000000	0	1
+00000000-0000-0000-0000-000000000001	1	0
+DROP TABLE t1;
+CREATE TABLE t1 (a UUID);
+INSERT INTO t1 VALUES
+('00000000-0000-0000-0000-000000000000'),
+('00000000-0000-0000-0000-000000000001'),
+('00000000-0000-0000-0000-000000000002');
+SELECT * FROM t1 WHERE a;
+ERROR HY000: Illegal parameter data types uuid and bigint for operation '<>'
+DROP TABLE t1;
+#
+# GROUP BY
+#
+CREATE TABLE t1 (a UUID);
+INSERT INTO t1 VALUES
+('00000000-0000-0000-0000-000000000000'),
+('00000000-0000-0000-0000-0000-00000000');
+INSERT INTO t1 VALUES
+('00000000-0000-0000-0000-000000000001'),
+('00000000-0000-0000-0000-0000-00000001'),
+('00000000-0000-0000-0000-0000-0000-0001');
+INSERT INTO t1 VALUES
+('00000000-0000-0000-0000-000000000002'),
+('00000000-0000-0000-0000-000000000002'),
+('00000000-0000-0000-0000-0000-00000002'),
+('00000000-0000-0000-0000-0000-0000-0002');
+SELECT a, COUNT(*) FROM t1 GROUP BY a;
+a	COUNT(*)
+00000000-0000-0000-0000-000000000000	2
+00000000-0000-0000-0000-000000000001	3
+00000000-0000-0000-0000-000000000002	4
+DROP TABLE t1;
+#
+# Aggregate functions
+#
+CREATE TABLE t1 (a UUID);
+INSERT INTO t1 VALUES
+('00000000-0000-0000-0000-000000000000'),
+('00000000-0000-0000-0000-000000000000');
+INSERT INTO t1 VALUES
+('00000000-0000-0000-0000-000000000001'),
+('00000000-0000-0000-0000-0000-00000001'),
+('00000000-0000-0000-0000-0000-0000-0001');
+INSERT INTO t1 VALUES
+('00000000-0000-0000-0000-000000000002'),
+('00000000-0000-0000-0000-000000000002'),
+('00000000-0000-0000-0000-000000000002'),
+('00000000-0000-0000-0000-000000000002');
+SELECT MIN(a),MAX(a) FROM t1;
+MIN(a)	MAX(a)
+00000000-0000-0000-0000-000000000000	00000000-0000-0000-0000-000000000002
+CREATE TABLE t2 AS SELECT MIN(a), MAX(a) FROM t1;
+SHOW CREATE TABLE t2;
+Table	Create Table
+t2	CREATE TABLE `t2` (
+  `MIN(a)` uuid DEFAULT NULL,
+  `MAX(a)` uuid DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+SELECT * FROM t2;
+MIN(a)	MAX(a)
+00000000-0000-0000-0000-000000000000	00000000-0000-0000-0000-000000000002
+DROP TABLE t2;
+SELECT AVG(a) FROM t1;
+ERROR HY000: Illegal parameter data type uuid for operation 'avg('
+SELECT AVG(DISTINCT a) FROM t1;
+ERROR HY000: Illegal parameter data type uuid for operation 'avg(distinct '
+SELECT SUM(a) FROM t1;
+ERROR HY000: Illegal parameter data type uuid for operation 'sum('
+SELECT SUM(DISTINCT a) FROM t1;
+ERROR HY000: Illegal parameter data type uuid for operation 'sum(distinct '
+SELECT STDDEV(a) FROM t1;
+ERROR HY000: Illegal parameter data type uuid for operation 'std('
+SELECT GROUP_CONCAT(a ORDER BY a) FROM t1;
+GROUP_CONCAT(a ORDER BY a)
+00000000-0000-0000-0000-000000000000,00000000-0000-0000-0000-000000000000,00000000-0000-0000-0000-000000000001,00000000-0000-0000-0000-000000000001,00000000-0000-0000-0000-000000000001,00000000-0000-0000-0000-000000000002,00000000-0000-0000-0000-000000000002,00000000-0000-0000-0000-000000000002,00000000-0000-0000-0000-000000000002
+SELECT a, GROUP_CONCAT(a ORDER BY a) FROM t1 GROUP BY a;
+a	GROUP_CONCAT(a ORDER BY a)
+00000000-0000-0000-0000-000000000000	00000000-0000-0000-0000-000000000000,00000000-0000-0000-0000-000000000000
+00000000-0000-0000-0000-000000000001	00000000-0000-0000-0000-000000000001,00000000-0000-0000-0000-000000000001,00000000-0000-0000-0000-000000000001
+00000000-0000-0000-0000-000000000002	00000000-0000-0000-0000-000000000002,00000000-0000-0000-0000-000000000002,00000000-0000-0000-0000-000000000002,00000000-0000-0000-0000-000000000002
+DROP TABLE t1;
+#
+# Window functions
+#
+CREATE TABLE t1 (a UUID);
+INSERT INTO t1 VALUES
+('00000000-0000-0000-0000-000000000001'),
+('00000000-0000-0000-0000-000000000002'),
+('00000000-0000-0000-0000-000000000003'),
+('00000000-0000-0000-0000-000000000004');
+SELECT
+a,
+LAG(a) OVER (ORDER BY a),
+LEAD(a) OVER (ORDER BY a)
+FROM t1 ORDER BY a;
+a	LAG(a) OVER (ORDER BY a)	LEAD(a) OVER (ORDER BY a)
+00000000-0000-0000-0000-000000000001	NULL	00000000-0000-0000-0000-000000000002
+00000000-0000-0000-0000-000000000002	00000000-0000-0000-0000-000000000001	00000000-0000-0000-0000-000000000003
+00000000-0000-0000-0000-000000000003	00000000-0000-0000-0000-000000000002	00000000-0000-0000-0000-000000000004
+00000000-0000-0000-0000-000000000004	00000000-0000-0000-0000-000000000003	NULL
+SELECT
+a,
+FIRST_VALUE(a) OVER (ORDER BY a ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING),
+LAST_VALUE(a) OVER (ORDER BY a ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)
+FROM t1 ORDER BY a;
+a	FIRST_VALUE(a) OVER (ORDER BY a ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)	LAST_VALUE(a) OVER (ORDER BY a ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)
+00000000-0000-0000-0000-000000000001	00000000-0000-0000-0000-000000000001	00000000-0000-0000-0000-000000000002
+00000000-0000-0000-0000-000000000002	00000000-0000-0000-0000-000000000001	00000000-0000-0000-0000-000000000003
+00000000-0000-0000-0000-000000000003	00000000-0000-0000-0000-000000000002	00000000-0000-0000-0000-000000000004
+00000000-0000-0000-0000-000000000004	00000000-0000-0000-0000-000000000003	00000000-0000-0000-0000-000000000004
+DROP TABLE t1;
+#
+# Prepared statements
+#
+EXECUTE IMMEDIATE 'CREATE TABLE t1 AS SELECT ? AS a'
+  USING CAST('00000000-0000-0000-0000-000000000000' AS UUID);
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` uuid NOT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+EXECUTE IMMEDIATE 'INSERT INTO t1 VALUES (?)'
+  USING '00000000-0000-0000-0000-000000000001';
+EXECUTE IMMEDIATE 'INSERT INTO t1 VALUES (?)'
+  USING CAST('00000000-0000-0000-0000-000000000002' AS UUID);
+EXECUTE IMMEDIATE 'INSERT INTO t1 VALUES (?)'
+  USING 0x00000000000000000000000000000003;
+SELECT a FROM t1 ORDER BY a;
+a
+00000000-0000-0000-0000-000000000000
+00000000-0000-0000-0000-000000000001
+00000000-0000-0000-0000-000000000002
+00000000-0000-0000-0000-000000000003
+EXECUTE IMMEDIATE 'SELECT * FROM t1 WHERE a=?'
+  USING '00000000-0000-0000-0000-000000000001';
+a
+00000000-0000-0000-0000-000000000001
+EXECUTE IMMEDIATE 'SELECT * FROM t1 WHERE a=?'
+  USING CAST('00000000-0000-0000-0000-000000000002' AS UUID);
+a
+00000000-0000-0000-0000-000000000002
+EXECUTE IMMEDIATE 'SELECT * FROM t1 WHERE a=?'
+  USING 0x00000000000000000000000000000003;
+a
+00000000-0000-0000-0000-000000000003
+DROP TABLE t1;
+#
+# Character set and collation aggregation
+#
+CREATE TABLE t1 (a UUID);
+CREATE TABLE t2 AS SELECT
+CONCAT(a) AS c1,
+CONCAT(CAST('00000000-0000-0000-0000-000000000000' AS UUID)) AS c2
+FROM t1;
+SHOW CREATE TABLE t2;
+Table	Create Table
+t2	CREATE TABLE `t2` (
+  `c1` varchar(36) DEFAULT NULL,
+  `c2` varchar(36) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+DROP TABLE t2;
+CREATE TABLE t2 AS SELECT
+CONCAT(_utf8'1', LEFT(a,35)) AS c1,
+CONCAT(_utf8'1', LEFT(CAST('00000000-0000-0000-0000-000000000000' AS UUID),35)) AS c2,
+CONCAT(_utf8'1', LEFT(COALESCE(a),35)) AS c3
+FROM t1;
+SHOW CREATE TABLE t2;
+Table	Create Table
+t2	CREATE TABLE `t2` (
+  `c1` varchar(36) CHARACTER SET utf8mb3 DEFAULT NULL,
+  `c2` varchar(36) CHARACTER SET utf8mb3 DEFAULT NULL,
+  `c3` varchar(36) CHARACTER SET utf8mb3 DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+DROP TABLE t2;
+CREATE TABLE t2 AS SELECT
+CONCAT(_latin1'1', LEFT(a,35)) AS c1,
+CONCAT(_latin1'1', LEFT(CAST('00000000-0000-0000-0000-000000000000' AS UUID),35)) AS c2,
+CONCAT(_latin1'1', LEFT(COALESCE(a),35)) AS c3
+FROM t1;
+SHOW CREATE TABLE t2;
+Table	Create Table
+t2	CREATE TABLE `t2` (
+  `c1` varchar(36) DEFAULT NULL,
+  `c2` varchar(36) DEFAULT NULL,
+  `c3` varchar(36) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+DROP TABLE t2;
+DROP TABLE t1;
+#
+# UNION
+#
+CREATE TABLE t1 AS
+SELECT CAST('00000000-0000-0000-0000-000000000000' AS UUID) AS c
+UNION
+SELECT CAST('00000000-0000-0000-0000-000000000001' AS UUID);
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `c` uuid NOT NULL DEFAULT '00000000-0000-0000-0000-000000000000'
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+SELECT * FROM t1 ORDER BY c;
+c
+00000000-0000-0000-0000-000000000000
+00000000-0000-0000-0000-000000000001
+DROP TABLE t1;
+CREATE TABLE t1 AS
+SELECT CAST('00000000-0000-0000-0000-000000000000' AS UUID) AS c
+UNION
+SELECT '00000000-0000-0000-0000-000000000001';
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `c` uuid NOT NULL DEFAULT '00000000-0000-0000-0000-000000000000'
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+SELECT * FROM t1 ORDER BY c;
+c
+00000000-0000-0000-0000-000000000000
+00000000-0000-0000-0000-000000000001
+DROP TABLE t1;
+CREATE TABLE t1 AS
+SELECT '00000000-0000-0000-0000-000000000000' AS c
+UNION
+SELECT CAST('00000000-0000-0000-0000-000000000001' AS UUID);
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `c` uuid NOT NULL DEFAULT '00000000-0000-0000-0000-000000000000'
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+SELECT * FROM t1 ORDER BY c;
+c
+00000000-0000-0000-0000-000000000000
+00000000-0000-0000-0000-000000000001
+DROP TABLE t1;
+CREATE TABLE t1 AS
+SELECT CAST('00000000-0000-0000-0000-000000000000' AS UUID) AS c
+UNION
+SELECT 0x00000000000000000000000000000001;
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `c` uuid NOT NULL DEFAULT '00000000-0000-0000-0000-000000000000'
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+SELECT * FROM t1 ORDER BY c;
+c
+00000000-0000-0000-0000-000000000000
+00000000-0000-0000-0000-000000000001
+DROP TABLE t1;
+CREATE TABLE t1 AS
+SELECT CAST('00000000-0000-0000-0000-000000000000' AS UUID) AS c
+UNION
+SELECT 1;
+ERROR HY000: Illegal parameter data types uuid and int for operation 'UNION'
+#
+# Unary operators
+#
+SELECT -CAST('00000000-0000-0000-0000-000000000000' AS UUID);
+ERROR HY000: Illegal parameter data type uuid for operation '-'
+SELECT ABS(CAST('00000000-0000-0000-0000-000000000000' AS UUID));
+ERROR HY000: Illegal parameter data type uuid for operation 'abs'
+SELECT ROUND(CAST('00000000-0000-0000-0000-000000000000' AS UUID));
+ERROR HY000: Illegal parameter data type uuid for operation 'round'
+SELECT CEILING(CAST('00000000-0000-0000-0000-000000000000' AS UUID));
+ERROR HY000: Illegal parameter data type uuid for operation 'ceiling'
+SELECT FLOOR(CAST('00000000-0000-0000-0000-000000000000' AS UUID));
+ERROR HY000: Illegal parameter data type uuid for operation 'floor'
+#
+# Arithmetic operators
+#
+SELECT CAST('00000000-0000-0000-0000-000000000000' AS UUID) + 1;
+ERROR HY000: Illegal parameter data types uuid and int for operation '+'
+SELECT CAST('00000000-0000-0000-0000-000000000000' AS UUID) - 1;
+ERROR HY000: Illegal parameter data types uuid and int for operation '-'
+SELECT CAST('00000000-0000-0000-0000-000000000000' AS UUID) * 1;
+ERROR HY000: Illegal parameter data types uuid and int for operation '*'
+SELECT CAST('00000000-0000-0000-0000-000000000000' AS UUID) / 1;
+ERROR HY000: Illegal parameter data types uuid and int for operation '/'
+SELECT CAST('00000000-0000-0000-0000-000000000000' AS UUID) MOD 1;
+ERROR HY000: Illegal parameter data types uuid and int for operation 'MOD'
+#
+# Misc
+#
+SELECT RAND(CAST('00000000-0000-0000-0000-000000000000' AS UUID));
+ERROR HY000: Illegal parameter data type uuid for operation 'rand'
+SELECT FROM_UNIXTIME(CAST('00000000-0000-0000-0000-000000000000' AS UUID));
+ERROR HY000: Illegal parameter data type uuid for operation 'from_unixtime'
+SELECT HOUR(CAST('00000000-0000-0000-0000-000000000000' AS UUID));
+ERROR HY000: Illegal parameter data type uuid for operation 'hour'
+SELECT YEAR(CAST('00000000-0000-0000-0000-000000000000' AS UUID));
+ERROR HY000: Illegal parameter data type uuid for operation 'year'
+SELECT RELEASE_LOCK(CAST('00000000-0000-0000-0000-000000000000' AS UUID));
+ERROR HY000: Illegal parameter data type uuid for operation 'release_lock'
+SELECT JSON_LENGTH(CAST('00000000-0000-0000-0000-000000000000' AS UUID));
+JSON_LENGTH(CAST('00000000-0000-0000-0000-000000000000' AS UUID))
+1
+#
+# Virtual columns
+#
+CREATE TABLE t1 (
+a INT,
+b UUID GENERATED ALWAYS AS (CAST(CONCAT(RAND(),a) AS UUID)), INDEX(b)
+);
+ERROR HY000: Function or expression 'rand()' cannot be used in the GENERATED ALWAYS AS clause of `b`
+CREATE TABLE t1 (
+a INT,
+b UUID GENERATED ALWAYS AS (CAST(CONCAT('00000000-0000-0000-0000-00000000001',HEX(a)) AS UUID)), INDEX(b)
+);
+INSERT INTO t1 (a) VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15);
+SELECT * FROM t1;
+a	b
+0	00000000-0000-0000-0000-000000000010
+1	00000000-0000-0000-0000-000000000011
+2	00000000-0000-0000-0000-000000000012
+3	00000000-0000-0000-0000-000000000013
+4	00000000-0000-0000-0000-000000000014
+5	00000000-0000-0000-0000-000000000015
+6	00000000-0000-0000-0000-000000000016
+7	00000000-0000-0000-0000-000000000017
+8	00000000-0000-0000-0000-000000000018
+9	00000000-0000-0000-0000-000000000019
+10	00000000-0000-0000-0000-00000000001a
+11	00000000-0000-0000-0000-00000000001b
+12	00000000-0000-0000-0000-00000000001c
+13	00000000-0000-0000-0000-00000000001d
+14	00000000-0000-0000-0000-00000000001e
+15	00000000-0000-0000-0000-00000000001f
+DROP TABLE t1;
+#
+# VIEW
+#
+CREATE TABLE t1 (a INT DEFAULT 0);
+INSERT INTO t1 (a) VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15);
+SELECT * FROM t1 ORDER BY a;
+a
+0
+1
+2
+3
+4
+5
+6
+7
+8
+9
+10
+11
+12
+13
+14
+15
+CREATE VIEW v1 AS SELECT (CAST(CONCAT('00000000-0000-0000-0000-00000000001',HEX(a)) AS UUID)) AS c FROM t1;
+SELECT * FROM v1 ORDER BY c;
+c
+00000000-0000-0000-0000-000000000010
+00000000-0000-0000-0000-000000000011
+00000000-0000-0000-0000-000000000012
+00000000-0000-0000-0000-000000000013
+00000000-0000-0000-0000-000000000014
+00000000-0000-0000-0000-000000000015
+00000000-0000-0000-0000-000000000016
+00000000-0000-0000-0000-000000000017
+00000000-0000-0000-0000-000000000018
+00000000-0000-0000-0000-000000000019
+00000000-0000-0000-0000-00000000001a
+00000000-0000-0000-0000-00000000001b
+00000000-0000-0000-0000-00000000001c
+00000000-0000-0000-0000-00000000001d
+00000000-0000-0000-0000-00000000001e
+00000000-0000-0000-0000-00000000001f
+DROP VIEW v1;
+DROP TABLE t1;
+CREATE TABLE t1 (a UUID DEFAULT '00000000-0000-0000-0000-000000000000');
+CREATE VIEW v1 AS SELECT * FROM t1;
+SHOW CREATE VIEW v1;
+View	Create View	character_set_client	collation_connection
+v1	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`a` AS `a` from `t1`	latin1	latin1_swedish_ci
+DESCRIBE v1;
+Field	Type	Null	Key	Default	Extra
+a	uuid	YES		00000000-0000-0000-0000-000000000000	
+INSERT INTO v1 VALUES
+(DEFAULT),
+('00000000-0000-0000-0000-000000000001'),
+('00000000-0000-0000-0000-000000000002');
+SELECT * FROM t1;
+a
+00000000-0000-0000-0000-000000000000
+00000000-0000-0000-0000-000000000001
+00000000-0000-0000-0000-000000000002
+DROP VIEW v1;
+DROP TABLE t1;
+CREATE TABLE t1 (a UUID DEFAULT CAST('00000000-0000-0000-0000-000000000000' AS UUID));
+CREATE VIEW v1 AS SELECT * FROM t1;
+SHOW CREATE VIEW v1;
+View	Create View	character_set_client	collation_connection
+v1	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`a` AS `a` from `t1`	latin1	latin1_swedish_ci
+DESCRIBE v1;
+Field	Type	Null	Key	Default	Extra
+a	uuid	YES		cast('00000000-0000-0000-0000-000000000000' as uuid)	
+INSERT INTO v1 VALUES
+(DEFAULT),
+('00000000-0000-0000-0000-000000000001'),
+('00000000-0000-0000-0000-000000000002');
+SELECT * FROM t1;
+a
+00000000-0000-0000-0000-000000000000
+00000000-0000-0000-0000-000000000001
+00000000-0000-0000-0000-000000000002
+DROP VIEW v1;
+DROP TABLE t1;
+#
+# Subqueries
+#
+CREATE TABLE t1 (a UUID);
+INSERT INTO t1 VALUES
+('00000000-0000-0000-0000-000000000000'),
+('00000000-0000-0000-0000-000000000001'),
+('00000000-0000-0000-0000-000000000002');
+SELECT * FROM t1 WHERE a=(SELECT MIN(a) FROM t1) ORDER BY a;
+a
+00000000-0000-0000-0000-000000000000
+SELECT * FROM t1 WHERE a=(SELECT MAX(a) FROM t1) ORDER BY a;
+a
+00000000-0000-0000-0000-000000000002
+SELECT * FROM t1 WHERE a IN (SELECT a FROM t1 WHERE a>'00000000-0000-0000-0000-000000000000') ORDER BY a;
+a
+00000000-0000-0000-0000-000000000001
+00000000-0000-0000-0000-000000000002
+DROP TABLE t1;
+#
+# Stored routines
+#
+CREATE PROCEDURE p1(a UUID)
+BEGIN
+DECLARE b UUID DEFAULT CONCAT('1',SUBSTRING(a,2,36));
+SELECT a, b;
+END;
+$$
+CALL p1('00000000-0000-0000-0000-000000000001');
+a	b
+00000000-0000-0000-0000-000000000001	10000000-0000-0000-0000-000000000001
+CALL p1(CAST('00000000-0000-0000-0000-000000000002' AS UUID));
+a	b
+00000000-0000-0000-0000-000000000002	10000000-0000-0000-0000-000000000002
+DROP PROCEDURE p1;
+CREATE FUNCTION f1(a UUID) RETURNS UUID
+BEGIN
+RETURN CONCAT('1',SUBSTRING(a,2,36));
+END;
+$$
+SELECT f1('00000000-0000-0000-0000-000000000001');
+f1('00000000-0000-0000-0000-000000000001')
+10000000-0000-0000-0000-000000000001
+SELECT f1(CAST('00000000-0000-0000-0000-000000000002' AS UUID));
+f1(CAST('00000000-0000-0000-0000-000000000002' AS UUID))
+10000000-0000-0000-0000-000000000002
+DROP FUNCTION f1;
+#
+# Anchored data types in SP variables
+#
+CREATE TABLE t1 (a UUID);
+INSERT INTO t1 VALUES ('00000000-0000-0000-0000-000000000001');
+CREATE PROCEDURE p1()
+BEGIN
+DECLARE va TYPE OF t1.a;
+SELECT MAX(a) INTO va FROM t1;
+SELECT va;
+END;
+$$
+CALL p1;
+va
+00000000-0000-0000-0000-000000000001
+DROP PROCEDURE p1;
+DROP TABLE t1;
+CREATE TABLE t1 (a UUID, b UUID);
+INSERT INTO t1 VALUES
+(
+'00000000-0000-0000-0000-00000000000a',
+'00000000-0000-0000-0000-00000000000b'
+);
+CREATE PROCEDURE p1()
+BEGIN
+DECLARE va ROW TYPE OF t1;
+SELECT MAX(a), MAX(b) INTO va FROM t1;
+SELECT va.a, va.b;
+END;
+$$
+CALL p1;
+va.a	va.b
+00000000-0000-0000-0000-00000000000a	00000000-0000-0000-0000-00000000000b
+DROP PROCEDURE p1;
+DROP TABLE t1;
+#
+# Optimizer: make_const_item_for_comparison
+#
+CREATE TABLE t1 (id INT, a UUID);
+INSERT INTO t1 VALUES
+(1,'00000000-0000-0000-0000-000000000001'),
+(2,'00000000-0000-0000-0000-000000000002');
+EXPLAIN EXTENDED SELECT * FROM t1 WHERE a=COALESCE(CAST('00000000-0000-0000-0000-000000000001' AS UUID)) AND id>0;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
+Warnings:
+Note	1003	select `test`.`t1`.`id` AS `id`,`test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = UUID'00000000-0000-0000-0000-000000000001' and `test`.`t1`.`id` > 0
+DROP TABLE t1;
+#
+# Optimizer: equal field propagation
+#
+CREATE TABLE t1 (id INT, a UUID);
+INSERT INTO t1 VALUES
+(1,'00000000-0000-0000-0000-000000000001'),
+(2,'00000000-0000-0000-0000-000000000002');
+EXPLAIN EXTENDED SELECT * FROM t1
+WHERE a=COALESCE(CAST('00000000-0000-0000-0000-000000000001' AS UUID))
+AND LENGTH(CONCAT(a,RAND()))>1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
+Warnings:
+Note	1003	select `test`.`t1`.`id` AS `id`,`test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = UUID'00000000-0000-0000-0000-000000000001' and octet_length(concat(UUID'00000000-0000-0000-0000-000000000001',rand())) > 1
+EXPLAIN EXTENDED SELECT * FROM t1
+WHERE a=COALESCE(CAST('00000000-0000-0000-0000-000000000001' AS UUID))
+AND LENGTH(a)>1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
+Warnings:
+Note	1003	select `test`.`t1`.`id` AS `id`,`test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = UUID'00000000-0000-0000-0000-000000000001'
+DROP TABLE t1;
+#
+# Optimizer: equal expression propagation
+#
+CREATE TABLE t1 (id INT, a UUID);
+INSERT INTO t1 VALUES
+(1,'00000000-0000-0000-0000-000000000001'),
+(2,'00000000-0000-0000-0000-000000000002');
+EXPLAIN EXTENDED SELECT * FROM t1
+WHERE COALESCE(a)='00000000-0000-0000-0000-000000000001' AND COALESCE(a)=CONCAT(a);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
+Warnings:
+Note	1003	select `test`.`t1`.`id` AS `id`,`test`.`t1`.`a` AS `a` from `test`.`t1` where coalesce(`test`.`t1`.`a`) = '00000000-0000-0000-0000-000000000001' and concat(`test`.`t1`.`a`) = '00000000-0000-0000-0000-000000000001'
+DROP TABLE t1;
+#
+# Subquery materialization
+#
+CREATE TABLE t1 (a UUID, b VARCHAR(36), KEY (a), KEY(b)) ;
+INSERT INTO t1 VALUES
+(
+'00000000-0000-0000-0000-00000000000a',
+'00000000-0000-0000-0000-00000000000a'
+),
+(
+'00000000-0000-0000-0000-00000000000a',
+'00000000-0000-0000-0000-00000000000b'
+);
+SET @@optimizer_switch='semijoin=off,materialization=on,in_to_exists=off,subquery_cache=off';
+EXPLAIN SELECT * FROM t1 WHERE a IN (SELECT a AS a_inner FROM t1 GROUP BY a_inner);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	Using where
+2	MATERIALIZED	t1	index	NULL	a	17	NULL	2	Using index
+EXPLAIN SELECT * FROM t1 WHERE b IN (SELECT a AS a_inner FROM t1 GROUP BY a_inner);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	Using where
+2	DEPENDENT SUBQUERY	t1	index_subquery	a	a	17	func	2	Using index; Using where
+SET @@optimizer_switch=DEFAULT;
+DROP TABLE t1;
+#
+# ALTER from UUID to UUID
+#
+CREATE TABLE t1 (a UUID, b INT);
+INSERT INTO t1 VALUES ('00000000-0000-0000-0000-000000000001', 1);
+ALTER TABLE t1 MODIFY b DECIMAL(10,2);
+SELECT * FROM t1;
+a	b
+00000000-0000-0000-0000-000000000001	1.00
+DROP TABLE t1;
+#
+# ALTER to character string data types
+#
+CREATE OR REPLACE TABLE t1 (a UUID);
+INSERT INTO t1 VALUES ('00000000-0000-0000-0000-000000000001');
+SELECT CAST(a AS CHAR(36)) FROM t1;
+CAST(a AS CHAR(36))
+00000000-0000-0000-0000-000000000001
+ALTER TABLE t1 MODIFY a CHAR(39);
+SELECT * FROM t1;
+a
+00000000-0000-0000-0000-000000000001
+DROP TABLE t1;
+CREATE OR REPLACE TABLE t1 (a UUID);
+INSERT INTO t1 VALUES ('00000000-0000-0000-0000-000000000001');
+ALTER TABLE t1 MODIFY a VARCHAR(36);
+SELECT * FROM t1;
+a
+00000000-0000-0000-0000-000000000001
+DROP TABLE t1;
+CREATE OR REPLACE TABLE t1 (a UUID);
+INSERT INTO t1 VALUES ('00000000-0000-0000-0000-000000000001');
+ALTER TABLE t1 MODIFY a TINYTEXT;
+SELECT * FROM t1;
+a
+00000000-0000-0000-0000-000000000001
+DROP TABLE t1;
+CREATE OR REPLACE TABLE t1 (a UUID);
+INSERT INTO t1 VALUES ('00000000-0000-0000-0000-000000000001');
+ALTER TABLE t1 MODIFY a TEXT;
+SELECT * FROM t1;
+a
+00000000-0000-0000-0000-000000000001
+DROP TABLE t1;
+CREATE OR REPLACE TABLE t1 (a UUID);
+INSERT INTO t1 VALUES ('00000000-0000-0000-0000-000000000001');
+ALTER TABLE t1 MODIFY a MEDIUMTEXT;
+SELECT * FROM t1;
+a
+00000000-0000-0000-0000-000000000001
+DROP TABLE t1;
+CREATE OR REPLACE TABLE t1 (a UUID);
+INSERT INTO t1 VALUES ('00000000-0000-0000-0000-000000000001');
+ALTER TABLE t1 MODIFY a LONGTEXT;
+SELECT * FROM t1;
+a
+00000000-0000-0000-0000-000000000001
+DROP TABLE t1;
+#
+# ALTER from character string data types
+#
+CREATE OR REPLACE TABLE t1 (a CHAR(64));
+INSERT INTO t1 VALUES ('00000000-0000-0000-0000-000000000001');
+SELECT CAST(a AS UUID) FROM t1;
+CAST(a AS UUID)
+00000000-0000-0000-0000-000000000001
+ALTER TABLE t1 MODIFY a UUID;
+SELECT * FROM t1;
+a
+00000000-0000-0000-0000-000000000001
+DROP TABLE t1;
+CREATE OR REPLACE TABLE t1 (a VARCHAR(64));
+INSERT INTO t1 VALUES ('00000000-0000-0000-0000-000000000001');
+SELECT CAST(a AS UUID) FROM t1;
+CAST(a AS UUID)
+00000000-0000-0000-0000-000000000001
+ALTER TABLE t1 MODIFY a UUID;
+SELECT * FROM t1;
+a
+00000000-0000-0000-0000-000000000001
+DROP TABLE t1;
+CREATE OR REPLACE TABLE t1 (a TINYTEXT);
+INSERT INTO t1 VALUES ('00000000-0000-0000-0000-000000000001');
+SELECT CAST(a AS UUID) FROM t1;
+CAST(a AS UUID)
+00000000-0000-0000-0000-000000000001
+ALTER TABLE t1 MODIFY a UUID;
+SELECT * FROM t1;
+a
+00000000-0000-0000-0000-000000000001
+DROP TABLE t1;
+CREATE OR REPLACE TABLE t1 (a TEXT);
+INSERT INTO t1 VALUES ('00000000-0000-0000-0000-000000000001');
+SELECT CAST(a AS UUID) FROM t1;
+CAST(a AS UUID)
+00000000-0000-0000-0000-000000000001
+ALTER TABLE t1 MODIFY a UUID;
+SELECT * FROM t1;
+a
+00000000-0000-0000-0000-000000000001
+DROP TABLE t1;
+CREATE OR REPLACE TABLE t1 (a MEDIUMTEXT);
+INSERT INTO t1 VALUES ('00000000-0000-0000-0000-000000000001');
+SELECT CAST(a AS UUID) FROM t1;
+CAST(a AS UUID)
+00000000-0000-0000-0000-000000000001
+ALTER TABLE t1 MODIFY a UUID;
+SELECT * FROM t1;
+a
+00000000-0000-0000-0000-000000000001
+DROP TABLE t1;
+CREATE OR REPLACE TABLE t1 (a LONGTEXT);
+INSERT INTO t1 VALUES ('00000000-0000-0000-0000-000000000001');
+SELECT CAST(a AS UUID) FROM t1;
+CAST(a AS UUID)
+00000000-0000-0000-0000-000000000001
+ALTER TABLE t1 MODIFY a UUID;
+SELECT * FROM t1;
+a
+00000000-0000-0000-0000-000000000001
+DROP TABLE t1;
+#
+# ALTER to binary string data types
+#
+CREATE TABLE t1 (a UUID);
+INSERT INTO t1 VALUES ('00000000-0000-0000-0000-000000000001');
+ALTER TABLE t1 MODIFY a BINARY(16);
+SELECT HEX(a) FROM t1;
+HEX(a)
+00000000000000000000000000000001
+DROP TABLE t1;
+CREATE TABLE t1 (a UUID);
+INSERT INTO t1 VALUES ('00000000-0000-0000-0000-000000000001');
+ALTER TABLE t1 MODIFY a BINARY(17);
+SELECT HEX(a) FROM t1;
+HEX(a)
+0000000000000000000000000000000100
+DROP TABLE t1;
+CREATE TABLE t1 (a UUID);
+INSERT INTO t1 VALUES ('00000000-0000-0000-0000-000000000001');
+ALTER TABLE t1 MODIFY a BINARY(15);
+ERROR 22001: Data too long for column 'a' at row 1
+DROP TABLE t1;
+CREATE TABLE t1 (a UUID);
+INSERT INTO t1 VALUES ('00000000-0000-0000-0000-000000000001');
+ALTER TABLE t1 MODIFY a TINYBLOB;
+SELECT HEX(a) FROM t1;
+HEX(a)
+00000000000000000000000000000001
+DROP TABLE t1;
+CREATE TABLE t1 (a UUID);
+INSERT INTO t1 VALUES ('00000000-0000-0000-0000-000000000001');
+ALTER TABLE t1 MODIFY a BLOB;
+SELECT HEX(a) FROM t1;
+HEX(a)
+00000000000000000000000000000001
+DROP TABLE t1;
+CREATE TABLE t1 (a UUID);
+INSERT INTO t1 VALUES ('00000000-0000-0000-0000-000000000001');
+ALTER TABLE t1 MODIFY a MEDIUMBLOB;
+SELECT HEX(a) FROM t1;
+HEX(a)
+00000000000000000000000000000001
+DROP TABLE t1;
+CREATE TABLE t1 (a UUID);
+INSERT INTO t1 VALUES ('00000000-0000-0000-0000-000000000001');
+ALTER TABLE t1 MODIFY a LONGBLOB;
+SELECT HEX(a) FROM t1;
+HEX(a)
+00000000000000000000000000000001
+DROP TABLE t1;
+#
+# ALTER from binary string data types
+#
+CREATE TABLE t1 (a BINARY(16));
+INSERT INTO t1 VALUES (X'20010DB8000000000000FF0000428329');
+ALTER TABLE t1 MODIFY a UUID;
+SELECT a FROM t1;
+a
+20010db8-0000-0000-0000-ff0000428329
+DROP TABLE t1;
+CREATE TABLE t1 (a BINARY(17));
+INSERT INTO t1 VALUES (X'20010DB8000000000000FF000042832900');
+ALTER TABLE t1 MODIFY a UUID;
+ERROR 22007: Incorrect uuid value: ' \x01\x0D\xB8\x00\x00\x00\x00\x00\x00\xFF\x00\x00B\x83)\x00' for column `test`.`t1`.`a` at row 1
+DROP TABLE t1;
+CREATE TABLE t1 (a BINARY(15));
+INSERT INTO t1 VALUES (X'20010DB8000000000000FF00004283');
+ALTER TABLE t1 MODIFY a UUID;
+ERROR 22007: Incorrect uuid value: ' \x01\x0D\xB8\x00\x00\x00\x00\x00\x00\xFF\x00\x00B\x83' for column `test`.`t1`.`a` at row 1
+DROP TABLE t1;
+CREATE TABLE t1 (a TINYBLOB);
+INSERT INTO t1 VALUES (X'20010DB8000000000000FF0000428329');
+ALTER TABLE t1 MODIFY a UUID;
+SELECT a FROM t1;
+a
+20010db8-0000-0000-0000-ff0000428329
+DROP TABLE t1;
+CREATE TABLE t1 (a BLOB);
+INSERT INTO t1 VALUES (X'20010DB8000000000000FF0000428329');
+ALTER TABLE t1 MODIFY a UUID;
+SELECT a FROM t1;
+a
+20010db8-0000-0000-0000-ff0000428329
+DROP TABLE t1;
+CREATE TABLE t1 (a MEDIUMBLOB);
+INSERT INTO t1 VALUES (X'20010DB8000000000000FF0000428329');
+ALTER TABLE t1 MODIFY a UUID;
+SELECT a FROM t1;
+a
+20010db8-0000-0000-0000-ff0000428329
+DROP TABLE t1;
+CREATE TABLE t1 (a BLOB);
+INSERT INTO t1 VALUES (X'20010DB8000000000000FF0000428329');
+ALTER TABLE t1 MODIFY a UUID;
+SELECT a FROM t1;
+a
+20010db8-0000-0000-0000-ff0000428329
+DROP TABLE t1;
+#
+# SET from UUID to UUID
+#
+CREATE TABLE t1 (a UUID, b UUID);
+INSERT INTO t1 VALUES ('ffffffff-ffff-ffff-ffff-ffffffffffff', NULL);
+UPDATE t1 SET b=a;
+SELECT b FROM t1;
+b
+ffffffff-ffff-ffff-ffff-ffffffffffff
+DROP TABLE t1;
+#
+# SET from UUID to numeric
+#
+CREATE TABLE t1 (a UUID, b INT);
+INSERT INTO t1 VALUES ('ffffffff-ffff-ffff-ffff-ffffffffffff', NULL);
+UPDATE t1 SET b=a;
+ERROR 22007: Incorrect integer value: 'ffffffff-ffff-ffff-ffff-ffffffffffff' for column `test`.`t1`.`b` at row 1
+SELECT b FROM t1;
+b
+NULL
+DROP TABLE t1;
+CREATE TABLE t1 (a UUID, b DOUBLE);
+INSERT INTO t1 VALUES ('ffffffff-ffff-ffff-ffff-ffffffffffff', NULL);
+UPDATE t1 SET b=a;
+ERROR 22007: Incorrect double value: 'ffffffff-ffff-ffff-ffff-ffffffffffff' for column `test`.`t1`.`b` at row 1
+SELECT b FROM t1;
+b
+NULL
+DROP TABLE t1;
+CREATE TABLE t1 (a UUID, b DECIMAL(32,0));
+INSERT INTO t1 VALUES ('ffffffff-ffff-ffff-ffff-ffffffffffff', NULL);
+UPDATE t1 SET b=a;
+ERROR 22007: Incorrect decimal value: 'ffffffff-ffff-ffff-ffff-ffffffffffff' for column `test`.`t1`.`b` at row 1
+SELECT b FROM t1;
+b
+NULL
+DROP TABLE t1;
+CREATE TABLE t1 (a UUID, b YEAR);
+INSERT INTO t1 VALUES ('ffffffff-ffff-ffff-ffff-ffffffffffff', NULL);
+UPDATE t1 SET b=a;
+ERROR 22007: Incorrect integer value: 'ffffffff-ffff-ffff-ffff-ffffffffffff' for column `test`.`t1`.`b` at row 1
+SELECT b FROM t1;
+b
+NULL
+DROP TABLE t1;
+#
+# SET from numeric to UUID
+#
+CREATE TABLE t1 (a INT, b UUID);
+INSERT INTO t1 VALUES (1, NULL);
+UPDATE t1 SET b=a;
+ERROR 22007: Incorrect uuid value: '1' for column `test`.`t1`.`b` at row 1
+SELECT b FROM t1;
+b
+NULL
+DROP TABLE t1;
+CREATE TABLE t1 (a DOUBLE, b UUID);
+INSERT INTO t1 VALUES (1, NULL);
+UPDATE t1 SET b=a;
+ERROR 22007: Incorrect uuid value: '1' for column `test`.`t1`.`b` at row 1
+SELECT b FROM t1;
+b
+NULL
+DROP TABLE t1;
+CREATE TABLE t1 (a DECIMAL(32,0), b UUID);
+INSERT INTO t1 VALUES (1, NULL);
+UPDATE t1 SET b=a;
+ERROR 22007: Incorrect uuid value: '1' for column `test`.`t1`.`b` at row 1
+SELECT b FROM t1;
+b
+NULL
+DROP TABLE t1;
+CREATE TABLE t1 (a YEAR, b UUID);
+INSERT INTO t1 VALUES (1, NULL);
+UPDATE t1 SET b=a;
+ERROR 22007: Incorrect uuid value: '2001' for column `test`.`t1`.`b` at row 1
+SELECT b FROM t1;
+b
+NULL
+DROP TABLE t1;
+#
+# SET from UUID to temporal
+#
+CREATE TABLE t1 (a UUID, b TIME);
+INSERT INTO t1 VALUES ('ffffffff-ffff-ffff-ffff-ffffffffffff', NULL);
+UPDATE t1 SET b=a;
+ERROR 22007: Incorrect time value: 'ffffffff-ffff-ffff-ffff-ffffffffffff' for column `test`.`t1`.`b` at row 1
+SELECT b FROM t1;
+b
+NULL
+DROP TABLE t1;
+CREATE TABLE t1 (a UUID, b DATE);
+INSERT INTO t1 VALUES ('ffffffff-ffff-ffff-ffff-ffffffffffff', NULL);
+UPDATE t1 SET b=a;
+ERROR 22007: Incorrect date value: 'ffffffff-ffff-ffff-ffff-ffffffffffff' for column `test`.`t1`.`b` at row 1
+SELECT b FROM t1;
+b
+NULL
+DROP TABLE t1;
+CREATE TABLE t1 (a UUID, b DATETIME);
+INSERT INTO t1 VALUES ('ffffffff-ffff-ffff-ffff-ffffffffffff', NULL);
+UPDATE t1 SET b=a;
+ERROR 22007: Incorrect datetime value: 'ffffffff-ffff-ffff-ffff-ffffffffffff' for column `test`.`t1`.`b` at row 1
+SELECT b FROM t1;
+b
+NULL
+DROP TABLE t1;
+CREATE TABLE t1 (a UUID, b TIMESTAMP NULL DEFAULT NULL);
+INSERT INTO t1 VALUES ('ffffffff-ffff-ffff-ffff-ffffffffffff', NULL);
+UPDATE t1 SET b=a;
+ERROR 22007: Incorrect datetime value: 'ffffffff-ffff-ffff-ffff-ffffffffffff' for column `test`.`t1`.`b` at row 1
+SELECT b FROM t1;
+b
+NULL
+DROP TABLE t1;
+#
+# SET from temporal to UUID
+#
+CREATE TABLE t1 (a TIME, b UUID);
+INSERT INTO t1 VALUES ('00:00:00', NULL);
+UPDATE t1 SET b=a;
+ERROR 22007: Incorrect uuid value: '00:00:00' for column `test`.`t1`.`b` at row 1
+SELECT b FROM t1;
+b
+NULL
+DROP TABLE t1;
+CREATE TABLE t1 (a DATE, b UUID);
+INSERT INTO t1 VALUES ('2001-01:01', NULL);
+UPDATE t1 SET b=a;
+ERROR 22007: Incorrect uuid value: '2001-01-01' for column `test`.`t1`.`b` at row 1
+SELECT b FROM t1;
+b
+NULL
+DROP TABLE t1;
+CREATE TABLE t1 (a DATETIME, b UUID);
+INSERT INTO t1 VALUES ('2001-01-01 10:20:30', NULL);
+UPDATE t1 SET b=a;
+ERROR 22007: Incorrect uuid value: '2001-01-01 10:20:30' for column `test`.`t1`.`b` at row 1
+SELECT b FROM t1;
+b
+NULL
+DROP TABLE t1;
+CREATE TABLE t1 (a TIMESTAMP, b UUID);
+INSERT INTO t1 VALUES ('2001-01-01 10:20:30', NULL);
+UPDATE t1 SET b=a;
+ERROR 22007: Incorrect uuid value: '2001-01-01 10:20:30' for column `test`.`t1`.`b` at row 1
+SELECT b FROM t1;
+b
+NULL
+DROP TABLE t1;
+#
+# SET from UUID to character string
+#
+CREATE TABLE t1 (a UUID, b CHAR(39));
+INSERT INTO t1 VALUES ('ffffffff-ffff-ffff-ffff-ffffffffffff', NULL);
+UPDATE t1 SET b=a;
+SELECT b FROM t1;
+b
+ffffffff-ffff-ffff-ffff-ffffffffffff
+DROP TABLE t1;
+CREATE TABLE t1 (a UUID, b VARCHAR(39));
+INSERT INTO t1 VALUES ('ffffffff-ffff-ffff-ffff-ffffffffffff', NULL);
+UPDATE t1 SET b=a;
+SELECT b FROM t1;
+b
+ffffffff-ffff-ffff-ffff-ffffffffffff
+DROP TABLE t1;
+CREATE TABLE t1 (a UUID, b TEXT);
+INSERT INTO t1 VALUES ('ffffffff-ffff-ffff-ffff-ffffffffffff', NULL);
+UPDATE t1 SET b=a;
+SELECT b FROM t1;
+b
+ffffffff-ffff-ffff-ffff-ffffffffffff
+DROP TABLE t1;
+CREATE TABLE t1 (a UUID, b ENUM('ffffffff-ffff-ffff-ffff-ffffffffffff'));
+INSERT INTO t1 VALUES ('ffffffff-ffff-ffff-ffff-ffffffffffff', NULL);
+UPDATE t1 SET b=a;
+SELECT b FROM t1;
+b
+ffffffff-ffff-ffff-ffff-ffffffffffff
+DROP TABLE t1;
+CREATE TABLE t1 (a UUID, b SET('ffffffff-ffff-ffff-ffff-ffffffffffff'));
+INSERT INTO t1 VALUES ('ffffffff-ffff-ffff-ffff-ffffffffffff', NULL);
+UPDATE t1 SET b=a;
+SELECT b FROM t1;
+b
+ffffffff-ffff-ffff-ffff-ffffffffffff
+DROP TABLE t1;
+#
+# SET from character string to UUID
+#
+CREATE TABLE t1 (a CHAR(36), b UUID);
+INSERT INTO t1 VALUES ('ffffffff-ffff-ffff-ffff-ffffffffffff', NULL);
+UPDATE t1 SET b=a;
+SELECT b FROM t1;
+b
+ffffffff-ffff-ffff-ffff-ffffffffffff
+DROP TABLE t1;
+CREATE TABLE t1 (a VARCHAR(36), b UUID);
+INSERT INTO t1 VALUES ('ffffffff-ffff-ffff-ffff-ffffffffffff', NULL);
+UPDATE t1 SET b=a;
+SELECT b FROM t1;
+b
+ffffffff-ffff-ffff-ffff-ffffffffffff
+DROP TABLE t1;
+CREATE TABLE t1 (a TEXT, b UUID);
+INSERT INTO t1 VALUES ('ffffffff-ffff-ffff-ffff-ffffffffffff', NULL);
+UPDATE t1 SET b=a;
+SELECT b FROM t1;
+b
+ffffffff-ffff-ffff-ffff-ffffffffffff
+DROP TABLE t1;
+CREATE TABLE t1 (a ENUM('ffffffff-ffff-ffff-ffff-ffffffffffff'), b UUID);
+INSERT INTO t1 VALUES ('ffffffff-ffff-ffff-ffff-ffffffffffff', NULL);
+UPDATE t1 SET b=a;
+SELECT b FROM t1;
+b
+ffffffff-ffff-ffff-ffff-ffffffffffff
+DROP TABLE t1;
+CREATE TABLE t1 (a SET('ffffffff-ffff-ffff-ffff-ffffffffffff'), b UUID);
+INSERT INTO t1 VALUES ('ffffffff-ffff-ffff-ffff-ffffffffffff', NULL);
+UPDATE t1 SET b=a;
+SELECT b FROM t1;
+b
+ffffffff-ffff-ffff-ffff-ffffffffffff
+DROP TABLE t1;
+#
+# SET from UUID to binary
+#
+CREATE TABLE t1 (a UUID, b BINARY(16));
+INSERT INTO t1 VALUES ('ffffffff-ffff-ffff-ffff-ffffffffffff', NULL);
+UPDATE t1 SET b=a;
+SELECT HEX(b) FROM t1;
+HEX(b)
+FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF
+DROP TABLE t1;
+CREATE TABLE t1 (a UUID, b VARBINARY(39));
+INSERT INTO t1 VALUES ('ffffffff-ffff-ffff-ffff-ffffffffffff', NULL);
+UPDATE t1 SET b=a;
+SELECT HEX(b) FROM t1;
+HEX(b)
+FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF
+DROP TABLE t1;
+CREATE TABLE t1 (a UUID, b BLOB);
+INSERT INTO t1 VALUES ('ffffffff-ffff-ffff-ffff-ffffffffffff', NULL);
+UPDATE t1 SET b=a;
+SELECT HEX(b) FROM t1;
+HEX(b)
+FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF
+DROP TABLE t1;
+#
+# SET from binary to UUID
+#
+CREATE TABLE t1 (a BINARY(16), b UUID);
+INSERT INTO t1 VALUES (CONCAT(0xFFFF,REPEAT(0x0000,6),0xFFFF), NULL);
+UPDATE t1 SET b=a;
+SELECT b FROM t1;
+b
+ffff0000-0000-0000-0000-00000000ffff
+DROP TABLE t1;
+CREATE TABLE t1 (a VARBINARY(16), b UUID);
+INSERT INTO t1 VALUES (CONCAT(0xFFFF,REPEAT(0x0000,6),0xFFFF), NULL);
+UPDATE t1 SET b=a;
+SELECT b FROM t1;
+b
+ffff0000-0000-0000-0000-00000000ffff
+DROP TABLE t1;
+CREATE TABLE t1 (a BLOB, b UUID);
+INSERT INTO t1 VALUES (CONCAT(0xFFFF,REPEAT(0x0000,6),0xFFFF), NULL);
+UPDATE t1 SET b=a;
+SELECT b FROM t1;
+b
+ffff0000-0000-0000-0000-00000000ffff
+DROP TABLE t1;
+#
+# Limit clause parameter
+# TODO: this should fail.
+# The test for a valid data type should be moved
+# from parse time to fix_fields() time, and performed
+# for both Item_splocal and Item_param.
+#
+EXECUTE IMMEDIATE 'SELECT 1 FROM DUAL LIMIT ?' USING CAST('00000000-0000-0000-0000-000000000000' AS UUID);
+1
+#
+# ALTER from UUID to CHAR
+#
+CREATE OR REPLACE TABLE t1 (a UUID);
+INSERT INTO t1 VALUES ('00000000-0000-0000-0000-000000000001');
+SELECT CAST(a AS CHAR(36)) FROM t1;
+CAST(a AS CHAR(36))
+00000000-0000-0000-0000-000000000001
+ALTER TABLE t1 MODIFY a CHAR(36);
+SELECT * FROM t1;
+a
+00000000-0000-0000-0000-000000000001
+DROP TABLE t1;
+#
+# ALTER from UUID to BINARY(16)
+#
+CREATE OR REPLACE TABLE t1 (a UUID);
+INSERT INTO t1 VALUES ('00000000-0000-0000-0000-000000000001');
+ALTER TABLE t1 MODIFY a BINARY(16);
+SELECT HEX(a) FROM t1;
+HEX(a)
+00000000000000000000000000000001
+DROP TABLE t1;
+#
+# CAST(uuid AS BINARY)
+#
+CREATE OR REPLACE TABLE t1 (a UUID);
+INSERT INTO t1 VALUES ('00000000-0000-0000-0000-000000000001');
+SELECT HEX(CAST(a AS BINARY)) FROM t1;
+HEX(CAST(a AS BINARY))
+00000000000000000000000000000001
+SELECT HEX(CAST(a AS BINARY(16))) FROM t1;
+HEX(CAST(a AS BINARY(16)))
+00000000000000000000000000000001
+DROP TABLE t1;
+#
+# CAST from UUID to FLOAT
+#
+CREATE TABLE t1 (a UUID);
+INSERT INTO t1 VALUES ('00000000-0000-0000-0000-000000000000');
+SELECT CAST(a AS FLOAT) FROM t1;
+ERROR HY000: Illegal parameter data type uuid for operation 'float_typecast'
+DROP TABLE t1;
+#
+# CAST(UUID AS BINARY) - metadata
+#
+CREATE TABLE t1 (a UUID);
+SELECT
+CAST(a AS BINARY(0)),
+CAST(a AS BINARY(1)),
+CAST(a AS BINARY(16)),
+CAST(a AS BINARY(255)),
+CAST(a AS BINARY(256)),
+CAST(a AS BINARY(512)),
+CAST(a AS BINARY(513)),
+CAST(a AS BINARY(65532)),
+CAST(a AS BINARY(65533)),
+CAST(a AS BINARY(65534)),
+CAST(a AS BINARY(65535)),
+CAST(a AS BINARY(65536)),
+CAST(a AS BINARY(16777215)),
+CAST(a AS BINARY(16777216))
+FROM t1;
+Catalog	Database	Table	Table_alias	Column	Column_alias	Type	Length	Max length	Is_null	Flags	Decimals	Charsetnr
+def					CAST(a AS BINARY(0))	254	0	0	Y	128	0	63
+def					CAST(a AS BINARY(1))	254	1	0	Y	128	0	63
+def					CAST(a AS BINARY(16))	254	16	0	Y	128	0	63
+def					CAST(a AS BINARY(255))	254	255	0	Y	128	0	63
+def					CAST(a AS BINARY(256))	253	256	0	Y	128	0	63
+def					CAST(a AS BINARY(512))	253	512	0	Y	128	0	63
+def					CAST(a AS BINARY(513))	253	513	0	Y	128	0	63
+def					CAST(a AS BINARY(65532))	253	65532	0	Y	128	0	63
+def					CAST(a AS BINARY(65533))	252	65533	0	Y	128	0	63
+def					CAST(a AS BINARY(65534))	252	65534	0	Y	128	0	63
+def					CAST(a AS BINARY(65535))	252	65535	0	Y	128	0	63
+def					CAST(a AS BINARY(65536))	250	65536	0	Y	128	0	63
+def					CAST(a AS BINARY(16777215))	250	16777215	0	Y	128	0	63
+def					CAST(a AS BINARY(16777216))	251	16777216	0	Y	128	0	63
+CAST(a AS BINARY(0))	CAST(a AS BINARY(1))	CAST(a AS BINARY(16))	CAST(a AS BINARY(255))	CAST(a AS BINARY(256))	CAST(a AS BINARY(512))	CAST(a AS BINARY(513))	CAST(a AS BINARY(65532))	CAST(a AS BINARY(65533))	CAST(a AS BINARY(65534))	CAST(a AS BINARY(65535))	CAST(a AS BINARY(65536))	CAST(a AS BINARY(16777215))	CAST(a AS BINARY(16777216))
+DROP TABLE t1;
+#
+# MIN(uuid) with GROUP BY
+#
+CREATE TABLE t1 (id INT, a UUID) ENGINE=MyISAM;
+INSERT INTO t1 VALUES
+(1, '00000000-0000-0000-0000-000000000fff'),
+(1, '00000000-0000-0000-0000-000000008888');
+SELECT MIN(a), MAX(a) FROM t1 GROUP BY id;
+MIN(a)	MAX(a)
+00000000-0000-0000-0000-000000000fff	00000000-0000-0000-0000-000000008888
+DROP TABLE t1;
diff --git a/plugin/type_uuid/mysql-test/type_uuid/type_uuid.test b/plugin/type_uuid/mysql-test/type_uuid/type_uuid.test
new file mode 100644
index 00000000000..ae6d3d40d5b
--- /dev/null
+++ b/plugin/type_uuid/mysql-test/type_uuid/type_uuid.test
@@ -0,0 +1,1575 @@
+--echo #
+--echo # MDEV-4958 Adding datatype UUID
+--echo #
+
+--echo #
+--echo # Basic CREATE functionality, defaults, metadata
+--echo #
+
+--error ER_WRONG_FIELD_SPEC
+CREATE TABLE t1 (a UUID AUTO_INCREMENT);
+
+CREATE TABLE t1 (a UUID);
+SHOW CREATE TABLE t1;
+DESCRIBE t1;
+--vertical_results
+--replace_column 19 #
+SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE table_schema='test' AND table_name='t1';
+--horizontal_results
+DROP TABLE t1;
+
+CREATE TABLE t1 (a UUID);
+INSERT INTO t1 VALUES ('00000000-0000-0000-0000-000000000001');
+--enable_metadata
+SELECT * FROM t1;
+SELECT CAST('00000000-0000-0000-0000-000000000001' AS UUID) AS a;
+--disable_metadata
+DROP TABLE t1;
+
+
+CREATE TABLE t1 (
+  c1 UUID DEFAULT 0x00000000000000000000000000000000,
+  c2 UUID DEFAULT 0xFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF,
+  c3 UUID DEFAULT '00000000-0000-0000-0000-000000000000',
+  c4 UUID DEFAULT 'ffffffff-ffff-ffff-ffff-ffffffffffff',
+  c5 UUID DEFAULT CAST(X'FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF' AS UUID)
+);
+SHOW CREATE TABLE t1;
+DESCRIBE t1;
+--vertical_results
+--replace_column 19 #
+SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE table_schema='test' AND table_name='t1';
+--horizontal_results
+DROP TABLE t1;
+
+--error ER_INVALID_DEFAULT
+CREATE TABLE t1 (c1 UUID DEFAULT 0x00);
+--error ER_INVALID_DEFAULT
+CREATE TABLE t1 (c1 UUID DEFAULT '');
+
+
+CREATE TABLE t1 (a UUID);
+--error ER_TRUNCATED_WRONG_VALUE
+INSERT INTO t1 VALUES ('x');
+--error ER_TRUNCATED_WRONG_VALUE
+INSERT INTO t1 VALUES (1);
+--error ER_TRUNCATED_WRONG_VALUE
+INSERT INTO t1 VALUES (TIME'10:20:30');
+--error ER_TRUNCATED_WRONG_VALUE
+INSERT INTO t1 VALUES (0x00);
+DROP TABLE t1;
+
+--echo #
+--echo # CAST
+--echo #
+
+SELECT CAST('garbage' AS UUID);
+SELECT CAST(0x01 AS UUID);
+SELECT CAST(REPEAT(0x00,16) AS UUID);
+SELECT CAST(REPEAT(0x11,16) AS UUID);
+
+CREATE TABLE t1 AS SELECT CAST('00000000-0000-0000-0000-000000000000' AS UUID);
+SHOW CREATE TABLE t1;
+DROP TABLE t1;
+
+
+--echo #
+--echo # Text and binary formats, comparison operators
+--echo #
+
+CREATE TABLE t1 (a UUID);
+INSERT INTO t1 VALUES (0x00000000000000000000000000000000);
+INSERT INTO t1 VALUES (0x00000000000000000000000000000001);
+INSERT INTO t1 VALUES (0xFFFF0000000000000000000000000001);
+INSERT INTO t1 VALUES (0xFFFF0000000000000000000000000002);
+SELECT * FROM t1 ORDER BY a;
+SELECT * FROM t1 ORDER BY a DESC;
+SELECT HEX(a),a FROM t1 ORDER BY a;
+SELECT * FROM t1 WHERE a='00000000-0000-0000-0000-000000000000';
+SELECT * FROM t1 WHERE a='00000000-0000-0000-0000-000000000001';
+SELECT * FROM t1 WHERE a='ffff0000-0000-0000-0000-000000000001';
+SELECT * FROM t1 WHERE a='ffff0000-0000-0000-0000-000000000002';
+SELECT * FROM t1 WHERE a=0x00000000000000000000000000000000;
+SELECT * FROM t1 WHERE a=0x00000000000000000000000000000001;
+SELECT * FROM t1 WHERE a=0xffff0000000000000000000000000001;
+SELECT * FROM t1 WHERE a=0xffff0000000000000000000000000002;
+SELECT * FROM t1 WHERE a< '00000000-0000-0000-0000-000000000000';
+SELECT * FROM t1 WHERE a<='00000000-0000-0000-0000-000000000000';
+SELECT * FROM t1 WHERE a>='ffff0000-0000-0000-0000-000000000002';
+SELECT * FROM t1 WHERE a> 'ffff0000-0000-0000-0000-000000000002';
+SELECT * FROM t1 WHERE a IN
+(
+  '00000000-0000-0000-0000-000000000000',
+  'ffff0000-0000-0000-0000-000000000001'
+) ORDER BY a;
+SELECT * FROM t1 WHERE a IN
+(
+  '00000000-0000-0000-0000-000000000000',
+  0xffff0000000000000000000000000002
+) ORDER BY a;
+
+SELECT * FROM t1 WHERE a<'garbage';
+SELECT * FROM t1 WHERE a<='garbage';
+SELECT * FROM t1 WHERE a='garbage';
+SELECT * FROM t1 WHERE a>='garbage';
+SELECT * FROM t1 WHERE a>'garbage';
+
+SELECT * FROM t1 WHERE a<0x01;
+SELECT * FROM t1 WHERE a<=0x01;
+SELECT * FROM t1 WHERE a=0x01;
+SELECT * FROM t1 WHERE a>=0x01;
+SELECT * FROM t1 WHERE a>0x01;
+
+SELECT * FROM t1 WHERE a='00000000-0000-0000-0000-000000000000';
+SELECT * FROM t1 WHERE a='00-000000-0000-0000-0000-000000000000';
+SELECT * FROM t1 WHERE a='00-00-0000-0000-0000-0000-000000000000';
+SELECT * FROM t1 WHERE a='00-00-00-00-0000-0000-0000-000000000000';
+
+--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION
+SELECT * FROM t1 WHERE a=0;
+--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION
+SELECT * FROM t1 WHERE a=0.0;
+--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION
+SELECT * FROM t1 WHERE a=0e0;
+--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION
+SELECT * FROM t1 WHERE a=TIME'10:20:30';
+--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION
+SELECT * FROM t1 WHERE a IN ('::', 10);
+
+DROP TABLE t1;
+
+--echo #
+--echo # cmp_item_uuid: IN for non-constants
+--echo #
+
+CREATE TABLE t1 (a UUID, b UUID);
+INSERT INTO t1 VALUES
+(
+  '00000000-0000-0000-0000-000000000001',
+  '00000000-0000-0000-0000-000000000002'
+);
+SELECT * FROM t1 WHERE '00000000-0000-0000-0000-000000000000' IN (a, b);
+SELECT * FROM t1 WHERE '00000000-0000-0000-0000-000000000001' IN (a, b);
+SELECT * FROM t1 WHERE '00-000000-0000-0000-0000-000000000001' IN (a, b);
+SELECT * FROM t1 WHERE '00-00-0000-0000-0000-0000-000000000001' IN (a, b);
+DROP TABLE t1;
+
+
+--echo #
+--echo # cmp_item_uuid: DECODE_ORACLE
+--echo #
+
+CREATE TABLE t1 (a UUID);
+INSERT INTO t1 VALUES
+(NULL),
+('00000000-0000-0000-0000-000000000001'),
+('00000000-0000-0000-0000-000000000002');
+SELECT a, DECODE_ORACLE(a, '00000000-0000-0000-0000-000000000002', '01') AS d FROM t1;
+SELECT
+  a,
+  DECODE_ORACLE(a, '00000000-0000-0000-0000-000000000001', '01') AS d0,
+  DECODE_ORACLE(a, NULL, '<NULL>', '00000000-0000-0000-0000-000000000001', '01') AS d1,
+  DECODE_ORACLE(a, 'garbage', '<NULL>', '00000000-0000-0000-0000-000000000001', '01') AS d2
+FROM t1;
+DROP TABLE t1;
+
+
+--echo #
+--echo # CASE abbreviations
+--echo #
+
+CREATE TABLE t1 (
+  c UUID,
+  c_char CHAR(32),
+  c_varchar VARCHAR(32),
+  c_tinytext TINYTEXT,
+  c_text TEXT,
+  c_mediumtext TEXT,
+  c_longtext LONGTEXT
+);
+CREATE TABLE t2 AS SELECT
+  COALESCE(c, c_char),
+  COALESCE(c, c_varchar),
+  COALESCE(c, c_tinytext),
+  COALESCE(c, c_text),
+  COALESCE(c, c_mediumtext),
+  COALESCE(c, c_longtext)
+FROM t1;
+SHOW CREATE TABLE t2;
+DROP TABLE t2;
+CREATE TABLE t2 AS SELECT
+  LEAST(c, c_char),
+  LEAST(c, c_varchar),
+  LEAST(c, c_tinytext),
+  LEAST(c, c_text),
+  LEAST(c, c_mediumtext),
+  LEAST(c, c_longtext)
+FROM t1;
+SHOW CREATE TABLE t2;
+DROP TABLE t2;
+DROP TABLE t1;
+
+CREATE TABLE t1 (a UUID);
+INSERT INTO t1 VALUES
+(NULL),
+('00000000-0000-0000-0000-000000000001'),
+('00000000-0000-0000-0000-000000000002');
+
+SELECT COALESCE(a, '00000000-0000-0000-0000-000000000000') FROM t1 ORDER BY a;
+
+SELECT
+  a,
+  LEAST(a,'00000000-0000-0000-0000-000000000000'),
+  LEAST(a,'00000000-0000-0000-0000-00000000000f')
+FROM t1 ORDER BY a;
+
+SELECT
+  a,
+  GREATEST(a,'00000000-0000-0000-0000-000000000000'),
+  GREATEST(a,'00000000-0000-0000-0000-00000000000f')
+FROM t1 ORDER BY a;
+
+CREATE TABLE t2 AS SELECT
+  COALESCE(a, '00000000-0000-0000-0000-000000000000'),
+  LEAST(a,'00000000-0000-0000-0000-000000000000'),
+  GREATEST(a,'00000000-0000-0000-0000-000000000000')
+FROM t1;
+SHOW CREATE TABLE t2;
+DROP TABLE t2;
+
+SELECT COALESCE(a, 0x00000000000000000000000000000000) FROM t1 ORDER BY a;
+SELECT
+  a,
+  LEAST(a, 0x00000000000000000000000000000000),
+  LEAST(a, 0x0000000000000000000000000000000f)
+FROM t1 ORDER BY a;
+
+SELECT
+  a,
+  GREATEST(a, 0x00000000000000000000000000000000),
+  GREATEST(a, 0x0000000000000000000000000000000f)
+FROM t1 ORDER BY a;
+
+CREATE TABLE t2 AS SELECT
+  COALESCE(a, 0x00000000000000000000000000000000),
+  LEAST(a,0x00000000000000000000000000000000),
+  GREATEST(a,0x00000000000000000000000000000000)
+FROM t1;
+SHOW CREATE TABLE t2;
+DROP TABLE t2;
+
+--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION
+SELECT COALESCE(a, 10) FROM t1;
+--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION
+SELECT LEAST(a, 10) FROM t1;
+--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION
+SELECT GREATEST(a, 10) FROM t1;
+
+DROP TABLE t1;
+
+SELECT COALESCE('garbage', CAST('::1' AS UUID));
+SELECT COALESCE(0x01, CAST('::1' AS UUID));
+
+
+--echo #
+--echo # Uniqueness
+--echo #
+
+CREATE TABLE t1 (a UUID NOT NULL PRIMARY KEY);
+INSERT INTO t1 VALUES
+('41000000-0000-0000-0000-000000000001'),
+('61000000-0000-0000-0000-000000000001');
+--error ER_DUP_ENTRY
+INSERT INTO t1 VALUES ('41000000-0000-0000-0000-000000000001');
+SELECT * FROM t1;
+DROP TABLE t1;
+
+
+--echo #
+--echo # Indexes
+--echo #
+
+--error ER_WRONG_SUB_KEY
+CREATE TABLE t1 (a UUID, KEY(a(1)));
+
+
+--echo #
+--echo # Explicit CAST on INSERT
+--echo #
+
+CREATE TABLE t1 (a UUID);
+INSERT INTO t1 VALUES (CAST('10000000-0000-0000-0000-000000000001' AS UUID));
+INSERT INTO t1 VALUES (CAST('10000000-0000-0000-0000-000000000002' AS UUID));
+INSERT INTO t1 VALUES (CAST('10000000-0000-0000-0000-000000000003' AS UUID));
+INSERT INTO t1 VALUES (CAST(CONCAT('2','0000000-0000-0000-0000-000000000001') AS UUID));
+INSERT INTO t1 VALUES (CAST(CONCAT('2','0000000-0000-0000-0000-000000000002') AS UUID));
+INSERT INTO t1 VALUES (CAST(CONCAT('2','0000000-0000-0000-0000-000000000003') AS UUID));
+SELECT * FROM t1 ORDER BY a;
+DROP TABLE t1;
+
+
+--echo #
+--echo # Explicit CAST and implicit CAST on ALTER
+--echo #
+
+CREATE TABLE t1 (a VARCHAR(64));
+INSERT INTO t1 VALUES
+('garbage'),
+('00000000-0000-0000-0000-000000000000'),
+('00000000-0000-0000-0000-000000000001'),
+('ffff0000-0000-0000-0000-000000000001'),
+('ffff0000-0000-0000-0000-000000000002');
+SELECT a, CAST(a AS UUID) FROM t1 ORDER BY a;
+SELECT a, CAST(a AS UUID) FROM t1 ORDER BY CAST(a AS UUID);
+--error ER_TRUNCATED_WRONG_VALUE
+ALTER TABLE t1 MODIFY a UUID;
+SET sql_mode='';
+ALTER TABLE t1 MODIFY a UUID;
+SET sql_mode=DEFAULT;
+SELECT * FROM t1 ORDER BY a;
+DROP TABLE t1;
+
+
+CREATE TABLE t1 (a BINARY(16));
+INSERT INTO t1 VALUES (0x00000000000000000000000000000000);
+INSERT INTO t1 VALUES (0x00000000000000000000000000000001);
+INSERT INTO t1 VALUES (0xffff0000000000000000000000000001);
+INSERT INTO t1 VALUES (0xffff0000000000000000000000000002);
+SELECT HEX(a), CAST(a AS UUID) FROM t1 ORDER BY a;
+ALTER TABLE t1 MODIFY a UUID;
+SELECT * FROM t1 ORDER BY a;
+DROP TABLE t1;
+
+
+--echo #
+--echo # INSERT..SELECT, same data types
+--echo #
+
+CREATE TABLE t1 (a UUID);
+INSERT INTO t1 VALUES 
+('00000000-0000-0000-0000-000000000000'),
+('00000000-0000-0000-0000-000000000001'),
+('00000000-0000-0000-0000-000000000002');
+CREATE TABLE t2 (a UUID);
+INSERT INTO t2 SELECT a FROM t1;
+SELECT * FROM t2;
+DROP TABLE t1,t2;
+
+
+--echo #
+--echo # Implicit CAST on INSERT..SELECT, text format
+--echo #
+
+CREATE TABLE t1 (a VARCHAR(64));
+INSERT INTO t1 VALUES
+('garbage'),
+('00000000-0000-0000-0000-000000000000'),
+('00000000-0000-0000-0000-000000000001'),
+('ffff0000-0000-0000-0000-000000000001'),
+('ffff0000-0000-0000-0000-000000000002');
+
+CREATE TABLE t2 (a UUID);
+--error ER_TRUNCATED_WRONG_VALUE
+INSERT INTO t2 SELECT a FROM t1;
+SET sql_mode='';
+INSERT INTO t2 SELECT a FROM t1;
+SELECT * FROM t2 ORDER BY a;
+SET sql_mode=DEFAULT;
+DROP TABLE t2;
+
+CREATE TABLE t2 (a UUID NOT NULL);
+--error ER_TRUNCATED_WRONG_VALUE
+INSERT INTO t2 SELECT a FROM t1;
+SET sql_mode='';
+INSERT INTO t2 SELECT a FROM t1;
+SELECT * FROM t2 ORDER BY a;
+SET sql_mode=DEFAULT;
+DROP TABLE t2;
+
+DROP TABLE t1;
+
+
+--echo #
+--echo # Implicit CAST on INSERT..SELECT, binary format
+--echo #
+
+CREATE TABLE t1 (a BINARY(16));
+INSERT INTO t1 VALUES (0x00000000000000000000000000000000);
+INSERT INTO t1 VALUES (0x00000000000000000000000000000001);
+INSERT INTO t1 VALUES (0xffff0000000000000000000000000001);
+INSERT INTO t1 VALUES (0xffff0000000000000000000000000002);
+CREATE TABLE t2 (a UUID);
+INSERT INTO t2 SELECT a FROM t1;
+SELECT a FROM t2 ORDER BY a;
+DROP TABLE t1,t2;
+
+
+--echo #
+--echo # CAST to other data types
+--echo #
+
+--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
+SELECT CAST(CAST('00000000-0000-0000-0000-000000000000' AS UUID) AS DOUBLE);
+
+--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
+SELECT CAST(CAST('00000000-0000-0000-0000-000000000000' AS UUID) AS FLOAT);
+
+--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
+SELECT CAST(CAST('00000000-0000-0000-0000-000000000000' AS UUID) AS DECIMAL);
+
+--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
+SELECT CAST(CAST('00000000-0000-0000-0000-000000000000' AS UUID) AS SIGNED);
+
+--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
+SELECT CAST(CAST('00000000-0000-0000-0000-000000000000' AS UUID) AS UNSIGNED);
+
+--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
+SELECT CAST(CAST('00000000-0000-0000-0000-000000000000' AS UUID) AS TIME);
+
+--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
+SELECT CAST(CAST('00000000-0000-0000-0000-000000000000' AS UUID) AS DATE);
+
+--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
+SELECT CAST(CAST('00000000-0000-0000-0000-000000000000' AS UUID) AS DATETIME);
+
+SELECT CAST(CAST('00000000-0000-0000-0000-000000000000' AS UUID) AS CHAR);
+CREATE TABLE t1 AS SELECT CAST(CAST('00000000-0000-0000-0000-000000000000' AS UUID) AS CHAR) AS a;
+SHOW CREATE TABLE t1;
+DROP TABLE t1;
+
+CREATE TABLE t1 (a UUID);
+INSERT INTO t1 VALUES ('ffffffff-ffff-ffff-ffff-ffffffffffff');
+CREATE TABLE t2 AS SELECT
+  CAST(a AS CHAR),
+  CAST(a AS CHAR(36)),
+  CAST(a AS CHAR(530)),
+  CAST(a AS CHAR(65535)),
+  CAST(a AS CHAR(66000)),
+  CAST(a AS CHAR(16777215)),
+  CAST(a AS CHAR(16777216))
+FROM t1;
+SHOW CREATE TABLE t2;
+--vertical_results
+SELECT * FROM t2;
+--horizontal_results
+DROP TABLE t2;
+DROP TABLE t1;
+
+CREATE TABLE t1 (a UUID);
+INSERT INTO t1 VALUES ('ffffffff-ffff-ffff-ffff-ffffffffffff');
+CREATE TABLE t2 AS SELECT
+  CAST(a AS BINARY(4)) AS cb4,
+  CAST(a AS BINARY) AS cb,
+  CAST(a AS BINARY(16)) AS cb16,
+  CAST(a AS BINARY(32)) AS cb32,
+  CAST(a AS BINARY(530)) AS cb530,
+  CAST(a AS BINARY(65535)) AS cb65535,
+  CAST(a AS BINARY(66000)) AS cb66000,
+  CAST(a AS BINARY(16777215)) AS cb16777215,
+  CAST(a AS BINARY(16777216)) AS cb16777216
+FROM t1 LIMIT 0;
+SHOW CREATE TABLE t2;
+DROP TABLE t2;
+
+CREATE TABLE t2 AS SELECT
+  CAST(a AS BINARY(4)) AS cb4,
+  CAST(a AS BINARY) AS cb,
+  CAST(a AS BINARY(16)) AS cb16,
+  CAST(a AS BINARY(32)) AS cb32,
+  CAST(a AS BINARY(530)) AS cb530,
+  CAST(a AS BINARY(65535)) AS cb65535
+FROM t1;
+SHOW CREATE TABLE t2;
+--vertical_results
+SELECT
+  HEX(cb4),
+  HEX(cb),
+  HEX(cb16),
+  HEX(cb32),
+  LENGTH(cb530),
+  LENGTH(cb65535)
+FROM t2;
+--horizontal_results
+DROP TABLE t2;
+DROP TABLE t1;
+
+--echo #
+--echo # Implicit conversion to other types in INSERT
+--echo #
+
+CREATE TABLE t1 (a INT);
+--error WARN_DATA_TRUNCATED
+INSERT INTO t1 VALUES (CAST('00000000-0000-0000-0000-000000000000' AS UUID));
+DROP TABLE t1;
+
+CREATE TABLE t1 (a DOUBLE);
+--error WARN_DATA_TRUNCATED
+INSERT INTO t1 VALUES (CAST('00000000-0000-0000-0000-000000000000' AS UUID));
+DROP TABLE t1;
+
+CREATE TABLE t1 (a DECIMAL(32,0));
+--error WARN_DATA_TRUNCATED
+INSERT INTO t1 VALUES (CAST('00000000-0000-0000-0000-000000000000' AS UUID));
+DROP TABLE t1;
+
+CREATE TABLE t1 (a VARCHAR(64));
+INSERT INTO t1 VALUES (CAST('00000000-0000-0000-0000-000000000000' AS UUID));
+DROP TABLE t1;
+
+CREATE TABLE t1 (a TEXT);
+INSERT INTO t1 VALUES (CAST('00000000-0000-0000-0000-000000000000' AS UUID));
+DROP TABLE t1;
+
+
+
+--echo #
+--echo # Boolean context
+--echo #
+
+SELECT
+  CAST('00000000-0000-0000-0000-000000000000' AS UUID) IS TRUE, 
+  CAST('00000000-0000-0000-0000-000000000000' AS UUID) IS FALSE,
+  CAST('00000000-0000-0000-0000-000000000001' AS UUID) IS TRUE,
+  CAST('00000000-0000-0000-0000-000000000001' AS UUID) IS FALSE;
+
+CREATE TABLE t1 (a UUID);
+INSERT INTO t1 VALUES
+('00000000-0000-0000-0000-000000000000'),
+('00000000-0000-0000-0000-000000000001');
+SELECT a, a IS TRUE, a IS FALSE FROM t1 ORDER BY a;
+DROP TABLE t1;
+
+#
+# TODO: Error looks like a bug. This should return rows where a<>'00000000-0000-0000-0000-000000000000'.
+# The same problem is repeatable with GEOMETRY.
+#
+CREATE TABLE t1 (a UUID);
+INSERT INTO t1 VALUES
+('00000000-0000-0000-0000-000000000000'),
+('00000000-0000-0000-0000-000000000001'),
+('00000000-0000-0000-0000-000000000002');
+--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION
+SELECT * FROM t1 WHERE a;
+DROP TABLE t1;
+
+
+--echo #
+--echo # GROUP BY
+--echo #
+
+CREATE TABLE t1 (a UUID);
+INSERT INTO t1 VALUES
+('00000000-0000-0000-0000-000000000000'),
+('00000000-0000-0000-0000-0000-00000000');
+INSERT INTO t1 VALUES
+('00000000-0000-0000-0000-000000000001'),
+('00000000-0000-0000-0000-0000-00000001'),
+('00000000-0000-0000-0000-0000-0000-0001');
+INSERT INTO t1 VALUES
+('00000000-0000-0000-0000-000000000002'),
+('00000000-0000-0000-0000-000000000002'),
+('00000000-0000-0000-0000-0000-00000002'),
+('00000000-0000-0000-0000-0000-0000-0002');
+SELECT a, COUNT(*) FROM t1 GROUP BY a;
+DROP TABLE t1;
+
+--echo #
+--echo # Aggregate functions
+--echo #
+
+CREATE TABLE t1 (a UUID);
+INSERT INTO t1 VALUES
+('00000000-0000-0000-0000-000000000000'),
+('00000000-0000-0000-0000-000000000000');
+INSERT INTO t1 VALUES
+('00000000-0000-0000-0000-000000000001'),
+('00000000-0000-0000-0000-0000-00000001'),
+('00000000-0000-0000-0000-0000-0000-0001');
+INSERT INTO t1 VALUES
+('00000000-0000-0000-0000-000000000002'),
+('00000000-0000-0000-0000-000000000002'),
+('00000000-0000-0000-0000-000000000002'),
+('00000000-0000-0000-0000-000000000002');
+SELECT MIN(a),MAX(a) FROM t1;
+
+CREATE TABLE t2 AS SELECT MIN(a), MAX(a) FROM t1;
+SHOW CREATE TABLE t2;
+SELECT * FROM t2;
+DROP TABLE t2;
+
+--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
+SELECT AVG(a) FROM t1;
+--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
+SELECT AVG(DISTINCT a) FROM t1;
+--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
+SELECT SUM(a) FROM t1;
+--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
+SELECT SUM(DISTINCT a) FROM t1;
+--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
+SELECT STDDEV(a) FROM t1;
+SELECT GROUP_CONCAT(a ORDER BY a) FROM t1;
+SELECT a, GROUP_CONCAT(a ORDER BY a) FROM t1 GROUP BY a;
+DROP TABLE t1;
+
+--echo #
+--echo # Window functions
+--echo #
+
+CREATE TABLE t1 (a UUID);
+INSERT INTO t1 VALUES
+('00000000-0000-0000-0000-000000000001'),
+('00000000-0000-0000-0000-000000000002'),
+('00000000-0000-0000-0000-000000000003'),
+('00000000-0000-0000-0000-000000000004');
+SELECT
+  a,
+  LAG(a) OVER (ORDER BY a),
+  LEAD(a) OVER (ORDER BY a)
+FROM t1 ORDER BY a;
+
+SELECT
+  a,
+  FIRST_VALUE(a) OVER (ORDER BY a ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING),
+  LAST_VALUE(a) OVER (ORDER BY a ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)
+FROM t1 ORDER BY a;
+DROP TABLE t1;
+
+
+--echo #
+--echo # Prepared statements
+--echo #
+
+EXECUTE IMMEDIATE 'CREATE TABLE t1 AS SELECT ? AS a'
+  USING CAST('00000000-0000-0000-0000-000000000000' AS UUID);
+SHOW CREATE TABLE t1;
+
+EXECUTE IMMEDIATE 'INSERT INTO t1 VALUES (?)'
+  USING '00000000-0000-0000-0000-000000000001';
+
+EXECUTE IMMEDIATE 'INSERT INTO t1 VALUES (?)'
+  USING CAST('00000000-0000-0000-0000-000000000002' AS UUID);
+
+EXECUTE IMMEDIATE 'INSERT INTO t1 VALUES (?)'
+  USING 0x00000000000000000000000000000003;
+
+SELECT a FROM t1 ORDER BY a;
+EXECUTE IMMEDIATE 'SELECT * FROM t1 WHERE a=?'
+  USING '00000000-0000-0000-0000-000000000001';
+
+EXECUTE IMMEDIATE 'SELECT * FROM t1 WHERE a=?'
+  USING CAST('00000000-0000-0000-0000-000000000002' AS UUID);
+
+EXECUTE IMMEDIATE 'SELECT * FROM t1 WHERE a=?'
+  USING 0x00000000000000000000000000000003;
+DROP TABLE t1;
+
+
+--echo #
+--echo # Character set and collation aggregation
+--echo #
+
+CREATE TABLE t1 (a UUID);
+
+CREATE TABLE t2 AS SELECT
+  CONCAT(a) AS c1,
+  CONCAT(CAST('00000000-0000-0000-0000-000000000000' AS UUID)) AS c2
+FROM t1;
+SHOW CREATE TABLE t2;
+DROP TABLE t2;
+
+CREATE TABLE t2 AS SELECT
+  CONCAT(_utf8'1', LEFT(a,35)) AS c1,
+  CONCAT(_utf8'1', LEFT(CAST('00000000-0000-0000-0000-000000000000' AS UUID),35)) AS c2,
+  CONCAT(_utf8'1', LEFT(COALESCE(a),35)) AS c3
+FROM t1;
+SHOW CREATE TABLE t2;
+DROP TABLE t2;
+
+CREATE TABLE t2 AS SELECT
+  CONCAT(_latin1'1', LEFT(a,35)) AS c1,
+  CONCAT(_latin1'1', LEFT(CAST('00000000-0000-0000-0000-000000000000' AS UUID),35)) AS c2,
+  CONCAT(_latin1'1', LEFT(COALESCE(a),35)) AS c3
+FROM t1;
+SHOW CREATE TABLE t2;
+DROP TABLE t2;
+
+DROP TABLE t1;
+
+
+--echo #
+--echo # UNION
+--echo #
+
+CREATE TABLE t1 AS
+  SELECT CAST('00000000-0000-0000-0000-000000000000' AS UUID) AS c
+  UNION
+  SELECT CAST('00000000-0000-0000-0000-000000000001' AS UUID);
+SHOW CREATE TABLE t1;
+SELECT * FROM t1 ORDER BY c;
+DROP TABLE t1;
+
+CREATE TABLE t1 AS
+  SELECT CAST('00000000-0000-0000-0000-000000000000' AS UUID) AS c
+  UNION
+  SELECT '00000000-0000-0000-0000-000000000001';
+SHOW CREATE TABLE t1;
+SELECT * FROM t1 ORDER BY c;
+DROP TABLE t1;
+
+CREATE TABLE t1 AS
+  SELECT '00000000-0000-0000-0000-000000000000' AS c
+  UNION
+  SELECT CAST('00000000-0000-0000-0000-000000000001' AS UUID);
+SHOW CREATE TABLE t1;
+SELECT * FROM t1 ORDER BY c;
+DROP TABLE t1;
+
+CREATE TABLE t1 AS
+  SELECT CAST('00000000-0000-0000-0000-000000000000' AS UUID) AS c
+  UNION
+  SELECT 0x00000000000000000000000000000001;
+SHOW CREATE TABLE t1;
+SELECT * FROM t1 ORDER BY c;
+DROP TABLE t1;
+
+--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION
+CREATE TABLE t1 AS
+  SELECT CAST('00000000-0000-0000-0000-000000000000' AS UUID) AS c
+  UNION
+  SELECT 1;
+
+
+--echo #
+--echo # Unary operators
+--echo #
+
+--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
+SELECT -CAST('00000000-0000-0000-0000-000000000000' AS UUID);
+
+--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
+SELECT ABS(CAST('00000000-0000-0000-0000-000000000000' AS UUID));
+
+--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
+SELECT ROUND(CAST('00000000-0000-0000-0000-000000000000' AS UUID));
+
+--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
+SELECT CEILING(CAST('00000000-0000-0000-0000-000000000000' AS UUID));
+
+--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
+SELECT FLOOR(CAST('00000000-0000-0000-0000-000000000000' AS UUID));
+
+
+--echo #
+--echo # Arithmetic operators
+--echo #
+
+--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION
+SELECT CAST('00000000-0000-0000-0000-000000000000' AS UUID) + 1;
+--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION
+SELECT CAST('00000000-0000-0000-0000-000000000000' AS UUID) - 1;
+
+--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION
+SELECT CAST('00000000-0000-0000-0000-000000000000' AS UUID) * 1;
+--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION
+SELECT CAST('00000000-0000-0000-0000-000000000000' AS UUID) / 1;
+
+--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION
+SELECT CAST('00000000-0000-0000-0000-000000000000' AS UUID) MOD 1;
+
+
+--echo #
+--echo # Misc
+--echo #
+
+--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
+SELECT RAND(CAST('00000000-0000-0000-0000-000000000000' AS UUID));
+
+--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
+SELECT FROM_UNIXTIME(CAST('00000000-0000-0000-0000-000000000000' AS UUID));
+
+--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
+SELECT HOUR(CAST('00000000-0000-0000-0000-000000000000' AS UUID));
+
+--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
+SELECT YEAR(CAST('00000000-0000-0000-0000-000000000000' AS UUID));
+
+--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
+SELECT RELEASE_LOCK(CAST('00000000-0000-0000-0000-000000000000' AS UUID));
+
+SELECT JSON_LENGTH(CAST('00000000-0000-0000-0000-000000000000' AS UUID));
+
+--echo #
+--echo # Virtual columns
+--echo #
+
+--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
+CREATE TABLE t1 (
+  a INT,
+  b UUID GENERATED ALWAYS AS (CAST(CONCAT(RAND(),a) AS UUID)), INDEX(b)
+);
+
+CREATE TABLE t1 (
+  a INT,
+  b UUID GENERATED ALWAYS AS (CAST(CONCAT('00000000-0000-0000-0000-00000000001',HEX(a)) AS UUID)), INDEX(b)
+);
+INSERT INTO t1 (a) VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15);
+SELECT * FROM t1;
+DROP TABLE t1;
+
+--echo #
+--echo # VIEW
+--echo #
+
+CREATE TABLE t1 (a INT DEFAULT 0);
+INSERT INTO t1 (a) VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15);
+SELECT * FROM t1 ORDER BY a;
+CREATE VIEW v1 AS SELECT (CAST(CONCAT('00000000-0000-0000-0000-00000000001',HEX(a)) AS UUID)) AS c FROM t1;
+SELECT * FROM v1 ORDER BY c;
+DROP VIEW v1;
+DROP TABLE t1;
+
+CREATE TABLE t1 (a UUID DEFAULT '00000000-0000-0000-0000-000000000000');
+CREATE VIEW v1 AS SELECT * FROM t1;
+SHOW CREATE VIEW v1;
+DESCRIBE v1;
+INSERT INTO v1 VALUES
+(DEFAULT),
+('00000000-0000-0000-0000-000000000001'),
+('00000000-0000-0000-0000-000000000002');
+SELECT * FROM t1;
+DROP VIEW v1;
+DROP TABLE t1;
+
+CREATE TABLE t1 (a UUID DEFAULT CAST('00000000-0000-0000-0000-000000000000' AS UUID));
+CREATE VIEW v1 AS SELECT * FROM t1;
+SHOW CREATE VIEW v1;
+DESCRIBE v1;
+INSERT INTO v1 VALUES
+(DEFAULT),
+('00000000-0000-0000-0000-000000000001'),
+('00000000-0000-0000-0000-000000000002');
+SELECT * FROM t1;
+DROP VIEW v1;
+DROP TABLE t1;
+
+
+--echo #
+--echo # Subqueries
+--echo #
+
+CREATE TABLE t1 (a UUID);
+INSERT INTO t1 VALUES
+('00000000-0000-0000-0000-000000000000'),
+('00000000-0000-0000-0000-000000000001'),
+('00000000-0000-0000-0000-000000000002');
+SELECT * FROM t1 WHERE a=(SELECT MIN(a) FROM t1) ORDER BY a;
+SELECT * FROM t1 WHERE a=(SELECT MAX(a) FROM t1) ORDER BY a;
+SELECT * FROM t1 WHERE a IN (SELECT a FROM t1 WHERE a>'00000000-0000-0000-0000-000000000000') ORDER BY a;
+DROP TABLE t1;
+
+--echo #
+--echo # Stored routines
+--echo #
+
+DELIMITER $$;
+CREATE PROCEDURE p1(a UUID)
+BEGIN
+  DECLARE b UUID DEFAULT CONCAT('1',SUBSTRING(a,2,36));
+  SELECT a, b;
+END;
+$$
+DELIMITER ;$$
+CALL p1('00000000-0000-0000-0000-000000000001');
+CALL p1(CAST('00000000-0000-0000-0000-000000000002' AS UUID));
+DROP PROCEDURE p1;
+
+DELIMITER $$;
+CREATE FUNCTION f1(a UUID) RETURNS UUID
+BEGIN
+  RETURN CONCAT('1',SUBSTRING(a,2,36));
+END;
+$$
+DELIMITER ;$$
+SELECT f1('00000000-0000-0000-0000-000000000001');
+SELECT f1(CAST('00000000-0000-0000-0000-000000000002' AS UUID));
+DROP FUNCTION f1;
+
+--echo #
+--echo # Anchored data types in SP variables
+--echo #
+
+CREATE TABLE t1 (a UUID);
+INSERT INTO t1 VALUES ('00000000-0000-0000-0000-000000000001');
+DELIMITER $$;
+CREATE PROCEDURE p1()
+BEGIN
+  DECLARE va TYPE OF t1.a;
+  SELECT MAX(a) INTO va FROM t1;
+  SELECT va;
+END;
+$$
+DELIMITER ;$$
+CALL p1;
+DROP PROCEDURE p1;
+DROP TABLE t1;
+
+
+CREATE TABLE t1 (a UUID, b UUID);
+INSERT INTO t1 VALUES
+(
+ '00000000-0000-0000-0000-00000000000a',
+ '00000000-0000-0000-0000-00000000000b'
+);
+DELIMITER $$;
+CREATE PROCEDURE p1()
+BEGIN
+  DECLARE va ROW TYPE OF t1;
+  SELECT MAX(a), MAX(b) INTO va FROM t1;
+  SELECT va.a, va.b;
+END;
+$$
+DELIMITER ;$$
+CALL p1;
+DROP PROCEDURE p1;
+DROP TABLE t1;
+
+
+--echo #
+--echo # Optimizer: make_const_item_for_comparison
+--echo #
+
+CREATE TABLE t1 (id INT, a UUID);
+INSERT INTO t1 VALUES
+(1,'00000000-0000-0000-0000-000000000001'),
+(2,'00000000-0000-0000-0000-000000000002');
+EXPLAIN EXTENDED SELECT * FROM t1 WHERE a=COALESCE(CAST('00000000-0000-0000-0000-000000000001' AS UUID)) AND id>0;
+DROP TABLE t1;
+
+--echo #
+--echo # Optimizer: equal field propagation
+--echo #
+
+CREATE TABLE t1 (id INT, a UUID);
+INSERT INTO t1 VALUES
+(1,'00000000-0000-0000-0000-000000000001'),
+(2,'00000000-0000-0000-0000-000000000002');
+EXPLAIN EXTENDED SELECT * FROM t1
+WHERE a=COALESCE(CAST('00000000-0000-0000-0000-000000000001' AS UUID))
+  AND LENGTH(CONCAT(a,RAND()))>1;
+EXPLAIN EXTENDED SELECT * FROM t1
+WHERE a=COALESCE(CAST('00000000-0000-0000-0000-000000000001' AS UUID))
+  AND LENGTH(a)>1;
+DROP TABLE t1;
+
+
+--echo #
+--echo # Optimizer: equal expression propagation
+--echo #
+
+
+CREATE TABLE t1 (id INT, a UUID);
+INSERT INTO t1 VALUES
+(1,'00000000-0000-0000-0000-000000000001'),
+(2,'00000000-0000-0000-0000-000000000002');
+EXPLAIN EXTENDED SELECT * FROM t1
+WHERE COALESCE(a)='00000000-0000-0000-0000-000000000001' AND COALESCE(a)=CONCAT(a);
+DROP TABLE t1;
+
+--echo #
+--echo # Subquery materialization
+--echo #
+
+CREATE TABLE t1 (a UUID, b VARCHAR(36), KEY (a), KEY(b)) ;
+INSERT INTO t1 VALUES
+(
+ '00000000-0000-0000-0000-00000000000a',
+ '00000000-0000-0000-0000-00000000000a'
+),
+(
+ '00000000-0000-0000-0000-00000000000a',
+ '00000000-0000-0000-0000-00000000000b'
+);
+SET @@optimizer_switch='semijoin=off,materialization=on,in_to_exists=off,subquery_cache=off';
+EXPLAIN SELECT * FROM t1 WHERE a IN (SELECT a AS a_inner FROM t1 GROUP BY a_inner);
+EXPLAIN SELECT * FROM t1 WHERE b IN (SELECT a AS a_inner FROM t1 GROUP BY a_inner);
+SET @@optimizer_switch=DEFAULT;
+DROP TABLE t1;
+
+
+--echo #
+--echo # ALTER from UUID to UUID
+--echo #
+
+CREATE TABLE t1 (a UUID, b INT);
+INSERT INTO t1 VALUES ('00000000-0000-0000-0000-000000000001', 1);
+ALTER TABLE t1 MODIFY b DECIMAL(10,2);
+SELECT * FROM t1;
+DROP TABLE t1;
+
+
+--echo #
+--echo # ALTER to character string data types
+--echo #
+
+CREATE OR REPLACE TABLE t1 (a UUID);
+INSERT INTO t1 VALUES ('00000000-0000-0000-0000-000000000001');
+SELECT CAST(a AS CHAR(36)) FROM t1;
+ALTER TABLE t1 MODIFY a CHAR(39);
+SELECT * FROM t1;
+DROP TABLE t1;
+
+CREATE OR REPLACE TABLE t1 (a UUID);
+INSERT INTO t1 VALUES ('00000000-0000-0000-0000-000000000001');
+ALTER TABLE t1 MODIFY a VARCHAR(36);
+SELECT * FROM t1;
+DROP TABLE t1;
+
+CREATE OR REPLACE TABLE t1 (a UUID);
+INSERT INTO t1 VALUES ('00000000-0000-0000-0000-000000000001');
+ALTER TABLE t1 MODIFY a TINYTEXT;
+SELECT * FROM t1;
+DROP TABLE t1;
+
+CREATE OR REPLACE TABLE t1 (a UUID);
+INSERT INTO t1 VALUES ('00000000-0000-0000-0000-000000000001');
+ALTER TABLE t1 MODIFY a TEXT;
+SELECT * FROM t1;
+DROP TABLE t1;
+
+CREATE OR REPLACE TABLE t1 (a UUID);
+INSERT INTO t1 VALUES ('00000000-0000-0000-0000-000000000001');
+ALTER TABLE t1 MODIFY a MEDIUMTEXT;
+SELECT * FROM t1;
+DROP TABLE t1;
+
+CREATE OR REPLACE TABLE t1 (a UUID);
+INSERT INTO t1 VALUES ('00000000-0000-0000-0000-000000000001');
+ALTER TABLE t1 MODIFY a LONGTEXT;
+SELECT * FROM t1;
+DROP TABLE t1;
+
+
+--echo #
+--echo # ALTER from character string data types
+--echo #
+
+CREATE OR REPLACE TABLE t1 (a CHAR(64));
+INSERT INTO t1 VALUES ('00000000-0000-0000-0000-000000000001');
+SELECT CAST(a AS UUID) FROM t1;
+ALTER TABLE t1 MODIFY a UUID;
+SELECT * FROM t1;
+DROP TABLE t1;
+
+CREATE OR REPLACE TABLE t1 (a VARCHAR(64));
+INSERT INTO t1 VALUES ('00000000-0000-0000-0000-000000000001');
+SELECT CAST(a AS UUID) FROM t1;
+ALTER TABLE t1 MODIFY a UUID;
+SELECT * FROM t1;
+DROP TABLE t1;
+
+CREATE OR REPLACE TABLE t1 (a TINYTEXT);
+INSERT INTO t1 VALUES ('00000000-0000-0000-0000-000000000001');
+SELECT CAST(a AS UUID) FROM t1;
+ALTER TABLE t1 MODIFY a UUID;
+SELECT * FROM t1;
+DROP TABLE t1;
+
+CREATE OR REPLACE TABLE t1 (a TEXT);
+INSERT INTO t1 VALUES ('00000000-0000-0000-0000-000000000001');
+SELECT CAST(a AS UUID) FROM t1;
+ALTER TABLE t1 MODIFY a UUID;
+SELECT * FROM t1;
+DROP TABLE t1;
+
+CREATE OR REPLACE TABLE t1 (a MEDIUMTEXT);
+INSERT INTO t1 VALUES ('00000000-0000-0000-0000-000000000001');
+SELECT CAST(a AS UUID) FROM t1;
+ALTER TABLE t1 MODIFY a UUID;
+SELECT * FROM t1;
+DROP TABLE t1;
+
+CREATE OR REPLACE TABLE t1 (a LONGTEXT);
+INSERT INTO t1 VALUES ('00000000-0000-0000-0000-000000000001');
+SELECT CAST(a AS UUID) FROM t1;
+ALTER TABLE t1 MODIFY a UUID;
+SELECT * FROM t1;
+DROP TABLE t1;
+
+
+--echo #
+--echo # ALTER to binary string data types
+--echo #
+
+CREATE TABLE t1 (a UUID);
+INSERT INTO t1 VALUES ('00000000-0000-0000-0000-000000000001');
+ALTER TABLE t1 MODIFY a BINARY(16);
+SELECT HEX(a) FROM t1;
+DROP TABLE t1;
+
+CREATE TABLE t1 (a UUID);
+INSERT INTO t1 VALUES ('00000000-0000-0000-0000-000000000001');
+ALTER TABLE t1 MODIFY a BINARY(17);
+SELECT HEX(a) FROM t1;
+DROP TABLE t1;
+
+CREATE TABLE t1 (a UUID);
+INSERT INTO t1 VALUES ('00000000-0000-0000-0000-000000000001');
+--error ER_DATA_TOO_LONG
+ALTER TABLE t1 MODIFY a BINARY(15);
+DROP TABLE t1;
+
+CREATE TABLE t1 (a UUID);
+INSERT INTO t1 VALUES ('00000000-0000-0000-0000-000000000001');
+ALTER TABLE t1 MODIFY a TINYBLOB;
+SELECT HEX(a) FROM t1;
+DROP TABLE t1;
+
+CREATE TABLE t1 (a UUID);
+INSERT INTO t1 VALUES ('00000000-0000-0000-0000-000000000001');
+ALTER TABLE t1 MODIFY a BLOB;
+SELECT HEX(a) FROM t1;
+DROP TABLE t1;
+
+CREATE TABLE t1 (a UUID);
+INSERT INTO t1 VALUES ('00000000-0000-0000-0000-000000000001');
+ALTER TABLE t1 MODIFY a MEDIUMBLOB;
+SELECT HEX(a) FROM t1;
+DROP TABLE t1;
+
+CREATE TABLE t1 (a UUID);
+INSERT INTO t1 VALUES ('00000000-0000-0000-0000-000000000001');
+ALTER TABLE t1 MODIFY a LONGBLOB;
+SELECT HEX(a) FROM t1;
+DROP TABLE t1;
+
+
+--echo #
+--echo # ALTER from binary string data types
+--echo #
+
+CREATE TABLE t1 (a BINARY(16));
+INSERT INTO t1 VALUES (X'20010DB8000000000000FF0000428329');
+ALTER TABLE t1 MODIFY a UUID;
+SELECT a FROM t1;
+DROP TABLE t1;
+
+CREATE TABLE t1 (a BINARY(17));
+INSERT INTO t1 VALUES (X'20010DB8000000000000FF000042832900');
+--error ER_TRUNCATED_WRONG_VALUE
+ALTER TABLE t1 MODIFY a UUID;
+DROP TABLE t1;
+
+CREATE TABLE t1 (a BINARY(15));
+INSERT INTO t1 VALUES (X'20010DB8000000000000FF00004283');
+--error ER_TRUNCATED_WRONG_VALUE
+ALTER TABLE t1 MODIFY a UUID;
+DROP TABLE t1;
+
+CREATE TABLE t1 (a TINYBLOB);
+INSERT INTO t1 VALUES (X'20010DB8000000000000FF0000428329');
+ALTER TABLE t1 MODIFY a UUID;
+SELECT a FROM t1;
+DROP TABLE t1;
+
+CREATE TABLE t1 (a BLOB);
+INSERT INTO t1 VALUES (X'20010DB8000000000000FF0000428329');
+ALTER TABLE t1 MODIFY a UUID;
+SELECT a FROM t1;
+DROP TABLE t1;
+
+CREATE TABLE t1 (a MEDIUMBLOB);
+INSERT INTO t1 VALUES (X'20010DB8000000000000FF0000428329');
+ALTER TABLE t1 MODIFY a UUID;
+SELECT a FROM t1;
+DROP TABLE t1;
+
+CREATE TABLE t1 (a BLOB);
+INSERT INTO t1 VALUES (X'20010DB8000000000000FF0000428329');
+ALTER TABLE t1 MODIFY a UUID;
+SELECT a FROM t1;
+DROP TABLE t1;
+
+
+--echo #
+--echo # SET from UUID to UUID
+--echo #
+
+CREATE TABLE t1 (a UUID, b UUID);
+INSERT INTO t1 VALUES ('ffffffff-ffff-ffff-ffff-ffffffffffff', NULL);
+UPDATE t1 SET b=a;
+SELECT b FROM t1;
+DROP TABLE t1;
+
+--echo #
+--echo # SET from UUID to numeric
+--echo #
+
+CREATE TABLE t1 (a UUID, b INT);
+INSERT INTO t1 VALUES ('ffffffff-ffff-ffff-ffff-ffffffffffff', NULL);
+--error ER_TRUNCATED_WRONG_VALUE_FOR_FIELD
+UPDATE t1 SET b=a;
+SELECT b FROM t1;
+DROP TABLE t1;
+
+CREATE TABLE t1 (a UUID, b DOUBLE);
+INSERT INTO t1 VALUES ('ffffffff-ffff-ffff-ffff-ffffffffffff', NULL);
+--error ER_TRUNCATED_WRONG_VALUE_FOR_FIELD
+UPDATE t1 SET b=a;
+SELECT b FROM t1;
+DROP TABLE t1;
+
+CREATE TABLE t1 (a UUID, b DECIMAL(32,0));
+INSERT INTO t1 VALUES ('ffffffff-ffff-ffff-ffff-ffffffffffff', NULL);
+--error ER_TRUNCATED_WRONG_VALUE_FOR_FIELD
+UPDATE t1 SET b=a;
+SELECT b FROM t1;
+DROP TABLE t1;
+
+CREATE TABLE t1 (a UUID, b YEAR);
+INSERT INTO t1 VALUES ('ffffffff-ffff-ffff-ffff-ffffffffffff', NULL);
+--error ER_TRUNCATED_WRONG_VALUE_FOR_FIELD
+UPDATE t1 SET b=a;
+SELECT b FROM t1;
+DROP TABLE t1;
+
+
+--echo #
+--echo # SET from numeric to UUID
+--echo #
+
+CREATE TABLE t1 (a INT, b UUID);
+INSERT INTO t1 VALUES (1, NULL);
+--error ER_TRUNCATED_WRONG_VALUE
+UPDATE t1 SET b=a;
+SELECT b FROM t1;
+DROP TABLE t1;
+
+CREATE TABLE t1 (a DOUBLE, b UUID);
+INSERT INTO t1 VALUES (1, NULL);
+--error ER_TRUNCATED_WRONG_VALUE
+UPDATE t1 SET b=a;
+SELECT b FROM t1;
+DROP TABLE t1;
+
+CREATE TABLE t1 (a DECIMAL(32,0), b UUID);
+INSERT INTO t1 VALUES (1, NULL);
+--error ER_TRUNCATED_WRONG_VALUE
+UPDATE t1 SET b=a;
+SELECT b FROM t1;
+DROP TABLE t1;
+
+CREATE TABLE t1 (a YEAR, b UUID);
+INSERT INTO t1 VALUES (1, NULL);
+--error ER_TRUNCATED_WRONG_VALUE
+UPDATE t1 SET b=a;
+SELECT b FROM t1;
+DROP TABLE t1;
+
+
+--echo #
+--echo # SET from UUID to temporal
+--echo #
+
+CREATE TABLE t1 (a UUID, b TIME);
+INSERT INTO t1 VALUES ('ffffffff-ffff-ffff-ffff-ffffffffffff', NULL);
+--error ER_TRUNCATED_WRONG_VALUE
+UPDATE t1 SET b=a;
+SELECT b FROM t1;
+DROP TABLE t1;
+
+CREATE TABLE t1 (a UUID, b DATE);
+INSERT INTO t1 VALUES ('ffffffff-ffff-ffff-ffff-ffffffffffff', NULL);
+--error ER_TRUNCATED_WRONG_VALUE
+UPDATE t1 SET b=a;
+SELECT b FROM t1;
+DROP TABLE t1;
+
+CREATE TABLE t1 (a UUID, b DATETIME);
+INSERT INTO t1 VALUES ('ffffffff-ffff-ffff-ffff-ffffffffffff', NULL);
+--error ER_TRUNCATED_WRONG_VALUE
+UPDATE t1 SET b=a;
+SELECT b FROM t1;
+DROP TABLE t1;
+
+CREATE TABLE t1 (a UUID, b TIMESTAMP NULL DEFAULT NULL);
+INSERT INTO t1 VALUES ('ffffffff-ffff-ffff-ffff-ffffffffffff', NULL);
+--error ER_TRUNCATED_WRONG_VALUE
+UPDATE t1 SET b=a;
+SELECT b FROM t1;
+DROP TABLE t1;
+
+
+--echo #
+--echo # SET from temporal to UUID
+--echo #
+
+CREATE TABLE t1 (a TIME, b UUID);
+INSERT INTO t1 VALUES ('00:00:00', NULL);
+--error ER_TRUNCATED_WRONG_VALUE
+UPDATE t1 SET b=a;
+SELECT b FROM t1;
+DROP TABLE t1;
+
+CREATE TABLE t1 (a DATE, b UUID);
+INSERT INTO t1 VALUES ('2001-01:01', NULL);
+--error ER_TRUNCATED_WRONG_VALUE
+UPDATE t1 SET b=a;
+SELECT b FROM t1;
+DROP TABLE t1;
+
+CREATE TABLE t1 (a DATETIME, b UUID);
+INSERT INTO t1 VALUES ('2001-01-01 10:20:30', NULL);
+--error ER_TRUNCATED_WRONG_VALUE
+UPDATE t1 SET b=a;
+SELECT b FROM t1;
+DROP TABLE t1;
+
+CREATE TABLE t1 (a TIMESTAMP, b UUID);
+INSERT INTO t1 VALUES ('2001-01-01 10:20:30', NULL);
+--error ER_TRUNCATED_WRONG_VALUE
+UPDATE t1 SET b=a;
+SELECT b FROM t1;
+DROP TABLE t1;
+
+
+--echo #
+--echo # SET from UUID to character string
+--echo #
+
+CREATE TABLE t1 (a UUID, b CHAR(39));
+INSERT INTO t1 VALUES ('ffffffff-ffff-ffff-ffff-ffffffffffff', NULL);
+UPDATE t1 SET b=a;
+SELECT b FROM t1;
+DROP TABLE t1;
+
+CREATE TABLE t1 (a UUID, b VARCHAR(39));
+INSERT INTO t1 VALUES ('ffffffff-ffff-ffff-ffff-ffffffffffff', NULL);
+UPDATE t1 SET b=a;
+SELECT b FROM t1;
+DROP TABLE t1;
+
+CREATE TABLE t1 (a UUID, b TEXT);
+INSERT INTO t1 VALUES ('ffffffff-ffff-ffff-ffff-ffffffffffff', NULL);
+UPDATE t1 SET b=a;
+SELECT b FROM t1;
+DROP TABLE t1;
+
+CREATE TABLE t1 (a UUID, b ENUM('ffffffff-ffff-ffff-ffff-ffffffffffff'));
+INSERT INTO t1 VALUES ('ffffffff-ffff-ffff-ffff-ffffffffffff', NULL);
+UPDATE t1 SET b=a;
+SELECT b FROM t1;
+DROP TABLE t1;
+
+CREATE TABLE t1 (a UUID, b SET('ffffffff-ffff-ffff-ffff-ffffffffffff'));
+INSERT INTO t1 VALUES ('ffffffff-ffff-ffff-ffff-ffffffffffff', NULL);
+UPDATE t1 SET b=a;
+SELECT b FROM t1;
+DROP TABLE t1;
+
+
+--echo #
+--echo # SET from character string to UUID
+--echo #
+
+CREATE TABLE t1 (a CHAR(36), b UUID);
+INSERT INTO t1 VALUES ('ffffffff-ffff-ffff-ffff-ffffffffffff', NULL);
+UPDATE t1 SET b=a;
+SELECT b FROM t1;
+DROP TABLE t1;
+
+CREATE TABLE t1 (a VARCHAR(36), b UUID);
+INSERT INTO t1 VALUES ('ffffffff-ffff-ffff-ffff-ffffffffffff', NULL);
+UPDATE t1 SET b=a;
+SELECT b FROM t1;
+DROP TABLE t1;
+
+CREATE TABLE t1 (a TEXT, b UUID);
+INSERT INTO t1 VALUES ('ffffffff-ffff-ffff-ffff-ffffffffffff', NULL);
+UPDATE t1 SET b=a;
+SELECT b FROM t1;
+DROP TABLE t1;
+
+CREATE TABLE t1 (a ENUM('ffffffff-ffff-ffff-ffff-ffffffffffff'), b UUID);
+INSERT INTO t1 VALUES ('ffffffff-ffff-ffff-ffff-ffffffffffff', NULL);
+UPDATE t1 SET b=a;
+SELECT b FROM t1;
+DROP TABLE t1;
+
+CREATE TABLE t1 (a SET('ffffffff-ffff-ffff-ffff-ffffffffffff'), b UUID);
+INSERT INTO t1 VALUES ('ffffffff-ffff-ffff-ffff-ffffffffffff', NULL);
+UPDATE t1 SET b=a;
+SELECT b FROM t1;
+DROP TABLE t1;
+
+
+--echo #
+--echo # SET from UUID to binary
+--echo #
+
+CREATE TABLE t1 (a UUID, b BINARY(16));
+INSERT INTO t1 VALUES ('ffffffff-ffff-ffff-ffff-ffffffffffff', NULL);
+UPDATE t1 SET b=a;
+SELECT HEX(b) FROM t1;
+DROP TABLE t1;
+
+CREATE TABLE t1 (a UUID, b VARBINARY(39));
+INSERT INTO t1 VALUES ('ffffffff-ffff-ffff-ffff-ffffffffffff', NULL);
+UPDATE t1 SET b=a;
+SELECT HEX(b) FROM t1;
+DROP TABLE t1;
+
+CREATE TABLE t1 (a UUID, b BLOB);
+INSERT INTO t1 VALUES ('ffffffff-ffff-ffff-ffff-ffffffffffff', NULL);
+UPDATE t1 SET b=a;
+SELECT HEX(b) FROM t1;
+DROP TABLE t1;
+
+
+--echo #
+--echo # SET from binary to UUID
+--echo #
+
+CREATE TABLE t1 (a BINARY(16), b UUID);
+INSERT INTO t1 VALUES (CONCAT(0xFFFF,REPEAT(0x0000,6),0xFFFF), NULL);
+UPDATE t1 SET b=a;
+SELECT b FROM t1;
+DROP TABLE t1;
+
+CREATE TABLE t1 (a VARBINARY(16), b UUID);
+INSERT INTO t1 VALUES (CONCAT(0xFFFF,REPEAT(0x0000,6),0xFFFF), NULL);
+UPDATE t1 SET b=a;
+SELECT b FROM t1;
+DROP TABLE t1;
+
+CREATE TABLE t1 (a BLOB, b UUID);
+INSERT INTO t1 VALUES (CONCAT(0xFFFF,REPEAT(0x0000,6),0xFFFF), NULL);
+UPDATE t1 SET b=a;
+SELECT b FROM t1;
+DROP TABLE t1;
+
+
+--echo #
+--echo # Limit clause parameter
+--echo # TODO: this should fail.
+--echo # The test for a valid data type should be moved
+--echo # from parse time to fix_fields() time, and performed
+--echo # for both Item_splocal and Item_param.
+--echo #
+
+EXECUTE IMMEDIATE 'SELECT 1 FROM DUAL LIMIT ?' USING CAST('00000000-0000-0000-0000-000000000000' AS UUID);
+
+
+# TODO:
+# - Add hooks to run mysql_client_test with pluggable data types
+#
+# - This should fail with the "illegal data type" error:
+#SELECT CAST('00000000-0000-0000-0000-000000000000' AS UUID) DIV 1;
+#
+# - This should fail with the "illegal data type" error:
+# EXTRACT(MINUTE...)
+#
+
+
+--echo #
+--echo # ALTER from UUID to CHAR
+--echo #
+
+CREATE OR REPLACE TABLE t1 (a UUID);
+INSERT INTO t1 VALUES ('00000000-0000-0000-0000-000000000001');
+SELECT CAST(a AS CHAR(36)) FROM t1;
+ALTER TABLE t1 MODIFY a CHAR(36);
+SELECT * FROM t1;
+DROP TABLE t1;
+
+--echo #
+--echo # ALTER from UUID to BINARY(16)
+--echo #
+
+CREATE OR REPLACE TABLE t1 (a UUID);
+INSERT INTO t1 VALUES ('00000000-0000-0000-0000-000000000001');
+ALTER TABLE t1 MODIFY a BINARY(16);
+SELECT HEX(a) FROM t1;
+DROP TABLE t1;
+
+--echo #
+--echo # CAST(uuid AS BINARY)
+--echo #
+
+CREATE OR REPLACE TABLE t1 (a UUID);
+INSERT INTO t1 VALUES ('00000000-0000-0000-0000-000000000001');
+SELECT HEX(CAST(a AS BINARY)) FROM t1;
+SELECT HEX(CAST(a AS BINARY(16))) FROM t1;
+DROP TABLE t1;
+
+--echo #
+--echo # CAST from UUID to FLOAT
+--echo #
+
+CREATE TABLE t1 (a UUID);
+INSERT INTO t1 VALUES ('00000000-0000-0000-0000-000000000000');
+--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
+SELECT CAST(a AS FLOAT) FROM t1;
+DROP TABLE t1;
+
+# TODO: below does not work well
+#--echo #
+#--echo # Conversion from UUID to other types
+#--echo #
+#
+#CREATE TABLE t1 (a UUID, b INT);
+#INSERT INTO t1 (a) VALUES ('00000000-0000-0000-0000-000000000000');
+#--error WARN_DATA_TRUNCATED
+#UPDATE t1 SET b=a;
+#SELECT * FROM t1;
+#DROP TABLE t1;
+#
+#SET timestamp=UNIX_TIMESTAMP('2001-01-01 10:20:30');
+#CREATE TABLE t1 (a UUID, b TIMESTAMP);
+#INSERT INTO t1 (a) VALUES ('00000000-0000-0000-0000-000000000000');
+#--error WARN_DATA_TRUNCATED
+#UPDATE t1 SET b=a;
+#SELECT * FROM t1;
+#DROP TABLE t1;
+#SET timestamp=DEFAULT;
+#
+#CREATE OR REPLACE TABLE t1 (a UUID);
+#INSERT INTO t1 (a) VALUES ('00000000-0000-0000-0000-000000000000');
+#--error WARN_DATA_TRUNCATED
+#ALTER TABLE t1 MODIFY a DATE;
+#DROP TABLE t1;
+
+--echo #
+--echo # CAST(UUID AS BINARY) - metadata
+--echo #
+
+CREATE TABLE t1 (a UUID);
+--enable_metadata
+SELECT
+  CAST(a AS BINARY(0)),
+  CAST(a AS BINARY(1)),
+  CAST(a AS BINARY(16)),
+  CAST(a AS BINARY(255)),
+  CAST(a AS BINARY(256)),
+  CAST(a AS BINARY(512)),
+  CAST(a AS BINARY(513)),
+  CAST(a AS BINARY(65532)),
+  CAST(a AS BINARY(65533)),
+  CAST(a AS BINARY(65534)),
+  CAST(a AS BINARY(65535)),
+  CAST(a AS BINARY(65536)),
+  CAST(a AS BINARY(16777215)),
+  CAST(a AS BINARY(16777216))
+FROM t1;
+--disable_metadata
+DROP TABLE t1;
+
+--echo #
+--echo # MIN(uuid) with GROUP BY
+--echo #
+
+CREATE TABLE t1 (id INT, a UUID) ENGINE=MyISAM;
+INSERT INTO t1 VALUES
+(1, '00000000-0000-0000-0000-000000000fff'),
+(1, '00000000-0000-0000-0000-000000008888');
+SELECT MIN(a), MAX(a) FROM t1 GROUP BY id;
+DROP TABLE t1;
diff --git a/plugin/type_uuid/mysql-test/type_uuid/type_uuid_csv.result b/plugin/type_uuid/mysql-test/type_uuid/type_uuid_csv.result
new file mode 100644
index 00000000000..1ad371ec561
--- /dev/null
+++ b/plugin/type_uuid/mysql-test/type_uuid/type_uuid_csv.result
@@ -0,0 +1,92 @@
+#
+# Start of 10.5 tests
+#
+#
+# MDEV-4958 Adding datatype UUID
+#
+SET default_storage_engine=CSV;
+CREATE TABLE t1 (a UUID NOT NULL);
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` uuid NOT NULL
+) ENGINE=CSV DEFAULT CHARSET=latin1
+FOR i IN 0..255
+DO
+INSERT INTO t1 VALUES (CONCAT(REPEAT(0x00,15), UNHEX(HEX(i))));
+END FOR
+$$
+SELECT * FROM t1 WHERE a='00000000-0000-0000-0000-0000000000ff';
+a
+00000000-0000-0000-0000-0000000000ff
+SELECT * FROM t1 WHERE a>='00000000-0000-0000-0000-0000000000fe' ORDER BY a;
+a
+00000000-0000-0000-0000-0000000000fe
+00000000-0000-0000-0000-0000000000ff
+SELECT * FROM t1 WHERE a IN
+(
+'00000000-0000-0000-0000-000000000080',
+'00000000-0000-0000-0000-0000000000a0',
+'00000000-0000-0000-0000-0000000000ff'
+)
+ORDER BY a;
+a
+00000000-0000-0000-0000-000000000080
+00000000-0000-0000-0000-0000000000a0
+00000000-0000-0000-0000-0000000000ff
+SELECT * FROM t1
+WHERE a BETWEEN
+'00000000-0000-0000-0000-000000000080'
+  AND
+'00000000-0000-0000-0000-000000000081'
+ORDER BY a;
+a
+00000000-0000-0000-0000-000000000080
+00000000-0000-0000-0000-000000000081
+SELECT CONCAT('ffff',SUBSTRING(a, 5,256)) FROM t1 WHERE a LIKE '%a_';
+CONCAT('ffff',SUBSTRING(a, 5,256))
+ffff0000-0000-0000-0000-0000000000a0
+ffff0000-0000-0000-0000-0000000000a1
+ffff0000-0000-0000-0000-0000000000a2
+ffff0000-0000-0000-0000-0000000000a3
+ffff0000-0000-0000-0000-0000000000a4
+ffff0000-0000-0000-0000-0000000000a5
+ffff0000-0000-0000-0000-0000000000a6
+ffff0000-0000-0000-0000-0000000000a7
+ffff0000-0000-0000-0000-0000000000a8
+ffff0000-0000-0000-0000-0000000000a9
+ffff0000-0000-0000-0000-0000000000aa
+ffff0000-0000-0000-0000-0000000000ab
+ffff0000-0000-0000-0000-0000000000ac
+ffff0000-0000-0000-0000-0000000000ad
+ffff0000-0000-0000-0000-0000000000ae
+ffff0000-0000-0000-0000-0000000000af
+UPDATE t1 SET a=CONCAT('ffff',SUBSTRING(a, 5,256)) WHERE a LIKE '%a_';
+SELECT * FROM t1 WHERE a LIKE 'ffff%' ORDER BY a;
+a
+ffff0000-0000-0000-0000-0000000000a0
+ffff0000-0000-0000-0000-0000000000a1
+ffff0000-0000-0000-0000-0000000000a2
+ffff0000-0000-0000-0000-0000000000a3
+ffff0000-0000-0000-0000-0000000000a4
+ffff0000-0000-0000-0000-0000000000a5
+ffff0000-0000-0000-0000-0000000000a6
+ffff0000-0000-0000-0000-0000000000a7
+ffff0000-0000-0000-0000-0000000000a8
+ffff0000-0000-0000-0000-0000000000a9
+ffff0000-0000-0000-0000-0000000000aa
+ffff0000-0000-0000-0000-0000000000ab
+ffff0000-0000-0000-0000-0000000000ac
+ffff0000-0000-0000-0000-0000000000ad
+ffff0000-0000-0000-0000-0000000000ae
+ffff0000-0000-0000-0000-0000000000af
+DROP TABLE t1;
+CREATE TABLE t1 (a UUID NOT NULL) ENGINE=CSV;
+INSERT INTO t1 VALUES ('123e4567-e89b-12d3-a456-426655440000');
+SELECT * FROM t1;
+a
+123e4567-e89b-12d3-a456-426655440000
+DROP TABLE t1;
+#
+# End of 10.5 tests
+#
diff --git a/plugin/type_uuid/mysql-test/type_uuid/type_uuid_csv.test b/plugin/type_uuid/mysql-test/type_uuid/type_uuid_csv.test
new file mode 100644
index 00000000000..482fb6a0e43
--- /dev/null
+++ b/plugin/type_uuid/mysql-test/type_uuid/type_uuid_csv.test
@@ -0,0 +1,58 @@
+--source include/have_csv.inc
+
+--echo #
+--echo # Start of 10.5 tests
+--echo #
+
+--echo #
+--echo # MDEV-4958 Adding datatype UUID
+--echo #
+
+SET default_storage_engine=CSV;
+
+CREATE TABLE t1 (a UUID NOT NULL);
+SHOW CREATE TABLE t1;
+
+DELIMITER $$;
+FOR i IN 0..255
+DO
+  INSERT INTO t1 VALUES (CONCAT(REPEAT(0x00,15), UNHEX(HEX(i))));
+END FOR
+$$
+DELIMITER ;$$
+
+SELECT * FROM t1 WHERE a='00000000-0000-0000-0000-0000000000ff';
+
+SELECT * FROM t1 WHERE a>='00000000-0000-0000-0000-0000000000fe' ORDER BY a;
+
+SELECT * FROM t1 WHERE a IN
+(
+  '00000000-0000-0000-0000-000000000080',
+  '00000000-0000-0000-0000-0000000000a0',
+  '00000000-0000-0000-0000-0000000000ff'
+)
+ORDER BY a;
+
+SELECT * FROM t1
+WHERE a BETWEEN
+  '00000000-0000-0000-0000-000000000080'
+  AND
+  '00000000-0000-0000-0000-000000000081'
+ORDER BY a;
+
+SELECT CONCAT('ffff',SUBSTRING(a, 5,256)) FROM t1 WHERE a LIKE '%a_';
+UPDATE t1 SET a=CONCAT('ffff',SUBSTRING(a, 5,256)) WHERE a LIKE '%a_';
+
+SELECT * FROM t1 WHERE a LIKE 'ffff%' ORDER BY a;
+
+DROP TABLE t1;
+
+CREATE TABLE t1 (a UUID NOT NULL) ENGINE=CSV;
+INSERT INTO t1 VALUES ('123e4567-e89b-12d3-a456-426655440000');
+SELECT * FROM t1;
+DROP TABLE t1;
+
+
+--echo #
+--echo # End of 10.5 tests
+--echo #
diff --git a/plugin/type_uuid/mysql-test/type_uuid/type_uuid_engines.inc b/plugin/type_uuid/mysql-test/type_uuid/type_uuid_engines.inc
new file mode 100644
index 00000000000..91909d94228
--- /dev/null
+++ b/plugin/type_uuid/mysql-test/type_uuid/type_uuid_engines.inc
@@ -0,0 +1,68 @@
+--echo #
+--echo # Range optimizer
+--echo #
+
+CREATE TABLE t1 (a UUID, INDEX(a));
+SHOW CREATE TABLE t1;
+
+DELIMITER $$;
+FOR i IN 0..255
+DO
+  INSERT INTO t1 VALUES (CONCAT(REPEAT(0x00, 15),CHAR(i USING BINARY)));
+END FOR
+$$
+DELIMITER ;$$
+
+SELECT * FROM t1 WHERE a='00000000-0000-0000-0000-0000000000ff';
+EXPLAIN SELECT * FROM t1 WHERE a='00000000-0000-0000-0000-0000000000ff';
+SELECT * FROM t1 WHERE a='garbage';
+EXPLAIN SELECT * FROM t1 WHERE a='garbage';
+
+SELECT * FROM t1 WHERE a>='00000000-0000-0000-0000-0000000000fe';
+EXPLAIN SELECT * FROM t1 WHERE a>='00000000-0000-0000-0000-0000000000fe';
+SELECT * FROM t1 WHERE a>='garbage';
+EXPLAIN SELECT * FROM t1 WHERE a>='garbage';
+
+SELECT * FROM t1 WHERE a IN
+(
+ '00000000-0000-0000-0000-000000000080',
+ '00000000-0000-0000-0000-0000000000a0',
+ '00000000-0000-0000-0000-0000000000f0'
+);
+EXPLAIN SELECT * FROM t1 WHERE a IN
+(
+ '00000000-0000-0000-0000-000000000080',
+ '00000000-0000-0000-0000-0000000000a0',
+ '00000000-0000-0000-0000-0000000000f0'
+);
+SELECT * FROM t1 WHERE a IN
+(
+ '00000000-0000-0000-0000-000000000080',
+ '00000000-0000-0000-0000-0000000000a0',
+ 'garbage'
+);
+EXPLAIN SELECT * FROM t1 WHERE a IN
+(
+ '00000000-0000-0000-0000-000000000080',
+ '00000000-0000-0000-0000-0000000000a0',
+ 'garbage'
+);
+
+SELECT * FROM t1 WHERE a BETWEEN
+  '00000000-0000-0000-0000-000000000080' AND
+  '00000000-0000-0000-0000-000000000081';
+EXPLAIN SELECT * FROM t1 WHERE a BETWEEN
+  '00000000-0000-0000-0000-000000000080' AND
+  '00000000-0000-0000-0000-000000000081';
+
+SELECT * FROM t1 WHERE a BETWEEN
+  '00000000-0000-0000-0000-000000000080' AND
+  'garbage';
+EXPLAIN SELECT * FROM t1 WHERE a BETWEEN
+  '00000000-0000-0000-0000-000000000080' AND
+  'garbage';
+
+SELECT * FROM t1 WHERE a=CAST('00000000-0000-0000-0000-0000000000ff' AS UUID);
+EXPLAIN EXTENDED SELECT * FROM t1 WHERE a=CAST('00000000-0000-0000-0000-0000000000ff' AS UUID);
+
+DROP TABLE t1;
diff --git a/plugin/type_uuid/mysql-test/type_uuid/type_uuid_innodb.result b/plugin/type_uuid/mysql-test/type_uuid/type_uuid_innodb.result
new file mode 100644
index 00000000000..643a2e9f0cb
--- /dev/null
+++ b/plugin/type_uuid/mysql-test/type_uuid/type_uuid_innodb.result
@@ -0,0 +1,120 @@
+#
+# Start of 10.5 tests
+#
+#
+# MDEV-4958 Adding datatype UUID
+#
+SET default_storage_engine=InnoDB;
+#
+# Range optimizer
+#
+CREATE TABLE t1 (a UUID, INDEX(a));
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` uuid DEFAULT NULL,
+  KEY `a` (`a`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+FOR i IN 0..255
+DO
+INSERT INTO t1 VALUES (CONCAT(REPEAT(0x00, 15),CHAR(i USING BINARY)));
+END FOR
+$$
+SELECT * FROM t1 WHERE a='00000000-0000-0000-0000-0000000000ff';
+a
+00000000-0000-0000-0000-0000000000ff
+EXPLAIN SELECT * FROM t1 WHERE a='00000000-0000-0000-0000-0000000000ff';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ref	a	a	17	const	1	Using where; Using index
+SELECT * FROM t1 WHERE a='garbage';
+a
+Warnings:
+Warning	1292	Incorrect uuid value: 'garbage'
+EXPLAIN SELECT * FROM t1 WHERE a='garbage';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
+Warnings:
+Warning	1292	Incorrect uuid value: 'garbage'
+SELECT * FROM t1 WHERE a>='00000000-0000-0000-0000-0000000000fe';
+a
+00000000-0000-0000-0000-0000000000fe
+00000000-0000-0000-0000-0000000000ff
+EXPLAIN SELECT * FROM t1 WHERE a>='00000000-0000-0000-0000-0000000000fe';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	range	a	a	17	NULL	2	Using where; Using index
+SELECT * FROM t1 WHERE a>='garbage';
+a
+EXPLAIN SELECT * FROM t1 WHERE a>='garbage';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
+SELECT * FROM t1 WHERE a IN
+(
+'00000000-0000-0000-0000-000000000080',
+'00000000-0000-0000-0000-0000000000a0',
+'00000000-0000-0000-0000-0000000000f0'
+);
+a
+00000000-0000-0000-0000-000000000080
+00000000-0000-0000-0000-0000000000a0
+00000000-0000-0000-0000-0000000000f0
+EXPLAIN SELECT * FROM t1 WHERE a IN
+(
+'00000000-0000-0000-0000-000000000080',
+'00000000-0000-0000-0000-0000000000a0',
+'00000000-0000-0000-0000-0000000000f0'
+);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	range	a	a	17	NULL	3	Using where; Using index
+SELECT * FROM t1 WHERE a IN
+(
+'00000000-0000-0000-0000-000000000080',
+'00000000-0000-0000-0000-0000000000a0',
+'garbage'
+);
+a
+00000000-0000-0000-0000-000000000080
+00000000-0000-0000-0000-0000000000a0
+Warnings:
+Warning	1292	Incorrect uuid value: 'garbage'
+EXPLAIN SELECT * FROM t1 WHERE a IN
+(
+'00000000-0000-0000-0000-000000000080',
+'00000000-0000-0000-0000-0000000000a0',
+'garbage'
+);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	range	a	a	17	NULL	2	Using where; Using index
+Warnings:
+Warning	1292	Incorrect uuid value: 'garbage'
+SELECT * FROM t1 WHERE a BETWEEN
+'00000000-0000-0000-0000-000000000080' AND
+'00000000-0000-0000-0000-000000000081';
+a
+00000000-0000-0000-0000-000000000080
+00000000-0000-0000-0000-000000000081
+EXPLAIN SELECT * FROM t1 WHERE a BETWEEN
+'00000000-0000-0000-0000-000000000080' AND
+'00000000-0000-0000-0000-000000000081';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	range	a	a	17	NULL	2	Using where; Using index
+SELECT * FROM t1 WHERE a BETWEEN
+'00000000-0000-0000-0000-000000000080' AND
+'garbage';
+a
+EXPLAIN SELECT * FROM t1 WHERE a BETWEEN
+'00000000-0000-0000-0000-000000000080' AND
+'garbage';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
+SELECT * FROM t1 WHERE a=CAST('00000000-0000-0000-0000-0000000000ff' AS UUID);
+a
+00000000-0000-0000-0000-0000000000ff
+EXPLAIN EXTENDED SELECT * FROM t1 WHERE a=CAST('00000000-0000-0000-0000-0000000000ff' AS UUID);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	ref	a	a	17	const	1	100.00	Using where; Using index
+Warnings:
+Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = UUID'00000000-0000-0000-0000-0000000000ff'
+DROP TABLE t1;
+#
+# End of 10.5 tests
+#
diff --git a/plugin/type_uuid/mysql-test/type_uuid/type_uuid_innodb.test b/plugin/type_uuid/mysql-test/type_uuid/type_uuid_innodb.test
new file mode 100644
index 00000000000..062e25d366b
--- /dev/null
+++ b/plugin/type_uuid/mysql-test/type_uuid/type_uuid_innodb.test
@@ -0,0 +1,16 @@
+--source include/have_innodb.inc
+
+--echo #
+--echo # Start of 10.5 tests
+--echo #
+
+--echo #
+--echo # MDEV-4958 Adding datatype UUID
+--echo #
+
+SET default_storage_engine=InnoDB;
+--source type_uuid_engines.inc
+
+--echo #
+--echo # End of 10.5 tests
+--echo #
diff --git a/plugin/type_uuid/mysql-test/type_uuid/type_uuid_memory.result b/plugin/type_uuid/mysql-test/type_uuid/type_uuid_memory.result
new file mode 100644
index 00000000000..42bb74d4b01
--- /dev/null
+++ b/plugin/type_uuid/mysql-test/type_uuid/type_uuid_memory.result
@@ -0,0 +1,187 @@
+#
+# Start of 10.5 tests
+#
+#
+# MDEV-4958 Adding datatype UUID
+#
+SET default_storage_engine=MEMORY;
+#
+# Range optimizer
+#
+CREATE TABLE t1 (a UUID, INDEX(a));
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` uuid DEFAULT NULL,
+  KEY `a` (`a`)
+) ENGINE=MEMORY DEFAULT CHARSET=latin1
+FOR i IN 0..255
+DO
+INSERT INTO t1 VALUES (CONCAT(REPEAT(0x00, 15),CHAR(i USING BINARY)));
+END FOR
+$$
+SELECT * FROM t1 WHERE a='00000000-0000-0000-0000-0000000000ff';
+a
+00000000-0000-0000-0000-0000000000ff
+EXPLAIN SELECT * FROM t1 WHERE a='00000000-0000-0000-0000-0000000000ff';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ref	a	a	17	const	2	Using where
+SELECT * FROM t1 WHERE a='garbage';
+a
+Warnings:
+Warning	1292	Incorrect uuid value: 'garbage'
+EXPLAIN SELECT * FROM t1 WHERE a='garbage';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
+Warnings:
+Warning	1292	Incorrect uuid value: 'garbage'
+SELECT * FROM t1 WHERE a>='00000000-0000-0000-0000-0000000000fe';
+a
+00000000-0000-0000-0000-0000000000fe
+00000000-0000-0000-0000-0000000000ff
+EXPLAIN SELECT * FROM t1 WHERE a>='00000000-0000-0000-0000-0000000000fe';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	a	NULL	NULL	NULL	256	Using where
+SELECT * FROM t1 WHERE a>='garbage';
+a
+Warnings:
+Warning	1292	Incorrect uuid value: 'garbage'
+EXPLAIN SELECT * FROM t1 WHERE a>='garbage';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	a	NULL	NULL	NULL	256	Using where
+SELECT * FROM t1 WHERE a IN
+(
+'00000000-0000-0000-0000-000000000080',
+'00000000-0000-0000-0000-0000000000a0',
+'00000000-0000-0000-0000-0000000000f0'
+);
+a
+00000000-0000-0000-0000-000000000080
+00000000-0000-0000-0000-0000000000a0
+00000000-0000-0000-0000-0000000000f0
+EXPLAIN SELECT * FROM t1 WHERE a IN
+(
+'00000000-0000-0000-0000-000000000080',
+'00000000-0000-0000-0000-0000000000a0',
+'00000000-0000-0000-0000-0000000000f0'
+);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	range	a	a	17	NULL	6	Using where
+SELECT * FROM t1 WHERE a IN
+(
+'00000000-0000-0000-0000-000000000080',
+'00000000-0000-0000-0000-0000000000a0',
+'garbage'
+);
+a
+00000000-0000-0000-0000-000000000080
+00000000-0000-0000-0000-0000000000a0
+Warnings:
+Warning	1292	Incorrect uuid value: 'garbage'
+EXPLAIN SELECT * FROM t1 WHERE a IN
+(
+'00000000-0000-0000-0000-000000000080',
+'00000000-0000-0000-0000-0000000000a0',
+'garbage'
+);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	range	a	a	17	NULL	4	Using where
+Warnings:
+Warning	1292	Incorrect uuid value: 'garbage'
+SELECT * FROM t1 WHERE a BETWEEN
+'00000000-0000-0000-0000-000000000080' AND
+'00000000-0000-0000-0000-000000000081';
+a
+00000000-0000-0000-0000-000000000080
+00000000-0000-0000-0000-000000000081
+EXPLAIN SELECT * FROM t1 WHERE a BETWEEN
+'00000000-0000-0000-0000-000000000080' AND
+'00000000-0000-0000-0000-000000000081';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	a	NULL	NULL	NULL	256	Using where
+SELECT * FROM t1 WHERE a BETWEEN
+'00000000-0000-0000-0000-000000000080' AND
+'garbage';
+a
+Warnings:
+Warning	1292	Incorrect uuid value: 'garbage'
+Warning	1292	Incorrect uuid value: 'garbage'
+Warning	1292	Incorrect uuid value: 'garbage'
+Warning	1292	Incorrect uuid value: 'garbage'
+Warning	1292	Incorrect uuid value: 'garbage'
+Warning	1292	Incorrect uuid value: 'garbage'
+Warning	1292	Incorrect uuid value: 'garbage'
+Warning	1292	Incorrect uuid value: 'garbage'
+Warning	1292	Incorrect uuid value: 'garbage'
+Warning	1292	Incorrect uuid value: 'garbage'
+Warning	1292	Incorrect uuid value: 'garbage'
+Warning	1292	Incorrect uuid value: 'garbage'
+Warning	1292	Incorrect uuid value: 'garbage'
+Warning	1292	Incorrect uuid value: 'garbage'
+Warning	1292	Incorrect uuid value: 'garbage'
+Warning	1292	Incorrect uuid value: 'garbage'
+Warning	1292	Incorrect uuid value: 'garbage'
+Warning	1292	Incorrect uuid value: 'garbage'
+Warning	1292	Incorrect uuid value: 'garbage'
+Warning	1292	Incorrect uuid value: 'garbage'
+Warning	1292	Incorrect uuid value: 'garbage'
+Warning	1292	Incorrect uuid value: 'garbage'
+Warning	1292	Incorrect uuid value: 'garbage'
+Warning	1292	Incorrect uuid value: 'garbage'
+Warning	1292	Incorrect uuid value: 'garbage'
+Warning	1292	Incorrect uuid value: 'garbage'
+Warning	1292	Incorrect uuid value: 'garbage'
+Warning	1292	Incorrect uuid value: 'garbage'
+Warning	1292	Incorrect uuid value: 'garbage'
+Warning	1292	Incorrect uuid value: 'garbage'
+Warning	1292	Incorrect uuid value: 'garbage'
+Warning	1292	Incorrect uuid value: 'garbage'
+Warning	1292	Incorrect uuid value: 'garbage'
+Warning	1292	Incorrect uuid value: 'garbage'
+Warning	1292	Incorrect uuid value: 'garbage'
+Warning	1292	Incorrect uuid value: 'garbage'
+Warning	1292	Incorrect uuid value: 'garbage'
+Warning	1292	Incorrect uuid value: 'garbage'
+Warning	1292	Incorrect uuid value: 'garbage'
+Warning	1292	Incorrect uuid value: 'garbage'
+Warning	1292	Incorrect uuid value: 'garbage'
+Warning	1292	Incorrect uuid value: 'garbage'
+Warning	1292	Incorrect uuid value: 'garbage'
+Warning	1292	Incorrect uuid value: 'garbage'
+Warning	1292	Incorrect uuid value: 'garbage'
+Warning	1292	Incorrect uuid value: 'garbage'
+Warning	1292	Incorrect uuid value: 'garbage'
+Warning	1292	Incorrect uuid value: 'garbage'
+Warning	1292	Incorrect uuid value: 'garbage'
+Warning	1292	Incorrect uuid value: 'garbage'
+Warning	1292	Incorrect uuid value: 'garbage'
+Warning	1292	Incorrect uuid value: 'garbage'
+Warning	1292	Incorrect uuid value: 'garbage'
+Warning	1292	Incorrect uuid value: 'garbage'
+Warning	1292	Incorrect uuid value: 'garbage'
+Warning	1292	Incorrect uuid value: 'garbage'
+Warning	1292	Incorrect uuid value: 'garbage'
+Warning	1292	Incorrect uuid value: 'garbage'
+Warning	1292	Incorrect uuid value: 'garbage'
+Warning	1292	Incorrect uuid value: 'garbage'
+Warning	1292	Incorrect uuid value: 'garbage'
+Warning	1292	Incorrect uuid value: 'garbage'
+Warning	1292	Incorrect uuid value: 'garbage'
+Warning	1292	Incorrect uuid value: 'garbage'
+EXPLAIN SELECT * FROM t1 WHERE a BETWEEN
+'00000000-0000-0000-0000-000000000080' AND
+'garbage';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	a	NULL	NULL	NULL	256	Using where
+SELECT * FROM t1 WHERE a=CAST('00000000-0000-0000-0000-0000000000ff' AS UUID);
+a
+00000000-0000-0000-0000-0000000000ff
+EXPLAIN EXTENDED SELECT * FROM t1 WHERE a=CAST('00000000-0000-0000-0000-0000000000ff' AS UUID);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	ref	a	a	17	const	2	100.00	Using where
+Warnings:
+Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = UUID'00000000-0000-0000-0000-0000000000ff'
+DROP TABLE t1;
+#
+# End of 10.5 tests
+#
diff --git a/plugin/type_uuid/mysql-test/type_uuid/type_uuid_memory.test b/plugin/type_uuid/mysql-test/type_uuid/type_uuid_memory.test
new file mode 100644
index 00000000000..cdae2bc39c2
--- /dev/null
+++ b/plugin/type_uuid/mysql-test/type_uuid/type_uuid_memory.test
@@ -0,0 +1,15 @@
+--echo #
+--echo # Start of 10.5 tests
+--echo #
+
+--echo #
+--echo # MDEV-4958 Adding datatype UUID
+--echo #
+
+SET default_storage_engine=MEMORY;
+--source type_uuid_engines.inc
+
+
+--echo #
+--echo # End of 10.5 tests
+--echo #
diff --git a/plugin/type_uuid/mysql-test/type_uuid/type_uuid_myisam.result b/plugin/type_uuid/mysql-test/type_uuid/type_uuid_myisam.result
new file mode 100644
index 00000000000..fb788dd91ab
--- /dev/null
+++ b/plugin/type_uuid/mysql-test/type_uuid/type_uuid_myisam.result
@@ -0,0 +1,120 @@
+#
+# Start of 10.5 tests
+#
+#
+# MDEV-4958 Adding datatype UUID
+#
+SET default_storage_engine=MyISAM;
+#
+# Range optimizer
+#
+CREATE TABLE t1 (a UUID, INDEX(a));
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` uuid DEFAULT NULL,
+  KEY `a` (`a`)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+FOR i IN 0..255
+DO
+INSERT INTO t1 VALUES (CONCAT(REPEAT(0x00, 15),CHAR(i USING BINARY)));
+END FOR
+$$
+SELECT * FROM t1 WHERE a='00000000-0000-0000-0000-0000000000ff';
+a
+00000000-0000-0000-0000-0000000000ff
+EXPLAIN SELECT * FROM t1 WHERE a='00000000-0000-0000-0000-0000000000ff';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ref	a	a	17	const	1	Using where; Using index
+SELECT * FROM t1 WHERE a='garbage';
+a
+Warnings:
+Warning	1292	Incorrect uuid value: 'garbage'
+EXPLAIN SELECT * FROM t1 WHERE a='garbage';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
+Warnings:
+Warning	1292	Incorrect uuid value: 'garbage'
+SELECT * FROM t1 WHERE a>='00000000-0000-0000-0000-0000000000fe';
+a
+00000000-0000-0000-0000-0000000000fe
+00000000-0000-0000-0000-0000000000ff
+EXPLAIN SELECT * FROM t1 WHERE a>='00000000-0000-0000-0000-0000000000fe';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	range	a	a	17	NULL	2	Using where; Using index
+SELECT * FROM t1 WHERE a>='garbage';
+a
+EXPLAIN SELECT * FROM t1 WHERE a>='garbage';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
+SELECT * FROM t1 WHERE a IN
+(
+'00000000-0000-0000-0000-000000000080',
+'00000000-0000-0000-0000-0000000000a0',
+'00000000-0000-0000-0000-0000000000f0'
+);
+a
+00000000-0000-0000-0000-000000000080
+00000000-0000-0000-0000-0000000000a0
+00000000-0000-0000-0000-0000000000f0
+EXPLAIN SELECT * FROM t1 WHERE a IN
+(
+'00000000-0000-0000-0000-000000000080',
+'00000000-0000-0000-0000-0000000000a0',
+'00000000-0000-0000-0000-0000000000f0'
+);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	range	a	a	17	NULL	3	Using where; Using index
+SELECT * FROM t1 WHERE a IN
+(
+'00000000-0000-0000-0000-000000000080',
+'00000000-0000-0000-0000-0000000000a0',
+'garbage'
+);
+a
+00000000-0000-0000-0000-000000000080
+00000000-0000-0000-0000-0000000000a0
+Warnings:
+Warning	1292	Incorrect uuid value: 'garbage'
+EXPLAIN SELECT * FROM t1 WHERE a IN
+(
+'00000000-0000-0000-0000-000000000080',
+'00000000-0000-0000-0000-0000000000a0',
+'garbage'
+);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	range	a	a	17	NULL	2	Using where; Using index
+Warnings:
+Warning	1292	Incorrect uuid value: 'garbage'
+SELECT * FROM t1 WHERE a BETWEEN
+'00000000-0000-0000-0000-000000000080' AND
+'00000000-0000-0000-0000-000000000081';
+a
+00000000-0000-0000-0000-000000000080
+00000000-0000-0000-0000-000000000081
+EXPLAIN SELECT * FROM t1 WHERE a BETWEEN
+'00000000-0000-0000-0000-000000000080' AND
+'00000000-0000-0000-0000-000000000081';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	range	a	a	17	NULL	2	Using where; Using index
+SELECT * FROM t1 WHERE a BETWEEN
+'00000000-0000-0000-0000-000000000080' AND
+'garbage';
+a
+EXPLAIN SELECT * FROM t1 WHERE a BETWEEN
+'00000000-0000-0000-0000-000000000080' AND
+'garbage';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
+SELECT * FROM t1 WHERE a=CAST('00000000-0000-0000-0000-0000000000ff' AS UUID);
+a
+00000000-0000-0000-0000-0000000000ff
+EXPLAIN EXTENDED SELECT * FROM t1 WHERE a=CAST('00000000-0000-0000-0000-0000000000ff' AS UUID);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	ref	a	a	17	const	1	100.00	Using where; Using index
+Warnings:
+Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = UUID'00000000-0000-0000-0000-0000000000ff'
+DROP TABLE t1;
+#
+# End of 10.5 tests
+#
diff --git a/plugin/type_uuid/mysql-test/type_uuid/type_uuid_myisam.test b/plugin/type_uuid/mysql-test/type_uuid/type_uuid_myisam.test
new file mode 100644
index 00000000000..0f992779666
--- /dev/null
+++ b/plugin/type_uuid/mysql-test/type_uuid/type_uuid_myisam.test
@@ -0,0 +1,16 @@
+--echo #
+--echo # Start of 10.5 tests
+--echo #
+
+--echo #
+--echo # MDEV-4958 Adding datatype UUID
+--echo #
+
+
+SET default_storage_engine=MyISAM;
+--source type_uuid_engines.inc
+
+
+--echo #
+--echo # End of 10.5 tests
+--echo #
diff --git a/plugin/type_uuid/mysql-test/type_uuid/type_uuid_mysql.result b/plugin/type_uuid/mysql-test/type_uuid/type_uuid_mysql.result
new file mode 100644
index 00000000000..70d2c2d2378
--- /dev/null
+++ b/plugin/type_uuid/mysql-test/type_uuid/type_uuid_mysql.result
@@ -0,0 +1,15 @@
+CREATE TABLE t1 (a UUID);
+Field   1:  `a`
+Catalog:    `def`
+Database:   `test`
+Table:      `t1`
+Org_table:  `t1`
+Type:       STRING (type=uuid)
+Collation:  latin1_swedish_ci (8)
+Length:     36
+Max_length: 0
+Decimals:   0
+Flags:      UNSIGNED BINARY 
+
+
+DROP TABLE t1;
diff --git a/plugin/type_uuid/mysql-test/type_uuid/type_uuid_mysql.test b/plugin/type_uuid/mysql-test/type_uuid/type_uuid_mysql.test
new file mode 100644
index 00000000000..c9bfadca01b
--- /dev/null
+++ b/plugin/type_uuid/mysql-test/type_uuid/type_uuid_mysql.test
@@ -0,0 +1,6 @@
+-- source include/have_working_dns.inc
+-- source include/not_embedded.inc
+
+CREATE TABLE t1 (a UUID);
+--exec $MYSQL -t test --column-type-info -e "SELECT * FROM t1" 2>&1 
+DROP TABLE t1;
diff --git a/plugin/type_uuid/mysql-test/type_uuid/type_uuid_partition.result b/plugin/type_uuid/mysql-test/type_uuid/type_uuid_partition.result
new file mode 100644
index 00000000000..bd0879325e7
--- /dev/null
+++ b/plugin/type_uuid/mysql-test/type_uuid/type_uuid_partition.result
@@ -0,0 +1,29 @@
+#
+# MDEV-4958 Adding datatype UUID
+#
+SET NAMES utf8;
+CREATE TABLE t1 (a UUID)
+PARTITION BY LIST COLUMNS(a)
+(PARTITION p00 VALUES IN (10));
+ERROR HY000: Partition column values of incorrect type
+CREATE TABLE t1 (a UUID)
+PARTITION BY LIST COLUMNS(a)
+(PARTITION p00 VALUES IN (TIME'10:20:30'));
+ERROR HY000: Partition column values of incorrect type
+CREATE TABLE t1 (a UUID)
+PARTITION BY LIST COLUMNS(a)
+(PARTITION p00 VALUES IN ('€'));
+ERROR 22007: Incorrect uuid value: '€'
+CREATE TABLE t1 (a UUID)
+PARTITION BY LIST COLUMNS(a)
+(PARTITION p00 VALUES IN ('123e4567-e89b-12d3-a456-426655440000'),
+PARTITION pFF VALUES IN (0xffff000000000000000000000000ffff));
+INSERT INTO t1 VALUES ('123e4567-e89b-12d3-a456-426655440000');
+INSERT INTO t1 VALUES ('ffff0000-0000-0000-0000-00000000ffff');
+SELECT * FROM t1 PARTITION (p00);
+a
+123e4567-e89b-12d3-a456-426655440000
+SELECT * FROM t1 PARTITION (pFF);
+a
+ffff0000-0000-0000-0000-00000000ffff
+DROP TABLE t1;
diff --git a/plugin/type_uuid/mysql-test/type_uuid/type_uuid_partition.test b/plugin/type_uuid/mysql-test/type_uuid/type_uuid_partition.test
new file mode 100644
index 00000000000..6c4341079c9
--- /dev/null
+++ b/plugin/type_uuid/mysql-test/type_uuid/type_uuid_partition.test
@@ -0,0 +1,32 @@
+--source include/have_partition.inc
+
+--echo #
+--echo # MDEV-4958 Adding datatype UUID
+--echo #
+
+SET NAMES utf8;
+
+--error ER_WRONG_TYPE_COLUMN_VALUE_ERROR
+CREATE TABLE t1 (a UUID)
+  PARTITION BY LIST COLUMNS(a)
+    (PARTITION p00 VALUES IN (10));
+
+--error ER_WRONG_TYPE_COLUMN_VALUE_ERROR
+CREATE TABLE t1 (a UUID)
+  PARTITION BY LIST COLUMNS(a)
+    (PARTITION p00 VALUES IN (TIME'10:20:30'));
+
+--error ER_TRUNCATED_WRONG_VALUE
+CREATE TABLE t1 (a UUID)
+  PARTITION BY LIST COLUMNS(a)
+    (PARTITION p00 VALUES IN ('€'));
+
+CREATE TABLE t1 (a UUID)
+  PARTITION BY LIST COLUMNS(a)
+    (PARTITION p00 VALUES IN ('123e4567-e89b-12d3-a456-426655440000'),
+     PARTITION pFF VALUES IN (0xffff000000000000000000000000ffff));
+INSERT INTO t1 VALUES ('123e4567-e89b-12d3-a456-426655440000');
+INSERT INTO t1 VALUES ('ffff0000-0000-0000-0000-00000000ffff');
+SELECT * FROM t1 PARTITION (p00);
+SELECT * FROM t1 PARTITION (pFF);
+DROP TABLE t1;
diff --git a/plugin/type_uuid/mysql-test/type_uuid/type_uuid_plugin.result b/plugin/type_uuid/mysql-test/type_uuid/type_uuid_plugin.result
new file mode 100644
index 00000000000..9166bcffc3b
--- /dev/null
+++ b/plugin/type_uuid/mysql-test/type_uuid/type_uuid_plugin.result
@@ -0,0 +1,31 @@
+#
+# Start of 10.5 tests
+#
+#
+# MDEV-4958 Adding datatype UUID
+#
+SELECT
+PLUGIN_NAME,
+PLUGIN_VERSION,
+PLUGIN_STATUS,
+PLUGIN_TYPE,
+PLUGIN_AUTHOR,
+PLUGIN_DESCRIPTION,
+PLUGIN_LICENSE,
+PLUGIN_MATURITY,
+PLUGIN_AUTH_VERSION
+FROM INFORMATION_SCHEMA.PLUGINS
+WHERE PLUGIN_TYPE='DATA TYPE'
+    AND PLUGIN_NAME='uuid';
+PLUGIN_NAME	uuid
+PLUGIN_VERSION	1.0
+PLUGIN_STATUS	ACTIVE
+PLUGIN_TYPE	DATA TYPE
+PLUGIN_AUTHOR	MariaDB Corporation
+PLUGIN_DESCRIPTION	Data type UUID
+PLUGIN_LICENSE	GPL
+PLUGIN_MATURITY	Beta
+PLUGIN_AUTH_VERSION	1.0
+#
+# End of 10.5 tests
+#
diff --git a/plugin/type_uuid/mysql-test/type_uuid/type_uuid_plugin.test b/plugin/type_uuid/mysql-test/type_uuid/type_uuid_plugin.test
new file mode 100644
index 00000000000..112105a176b
--- /dev/null
+++ b/plugin/type_uuid/mysql-test/type_uuid/type_uuid_plugin.test
@@ -0,0 +1,27 @@
+--echo #
+--echo # Start of 10.5 tests
+--echo #
+
+--echo #
+--echo # MDEV-4958 Adding datatype UUID
+--echo #
+
+--vertical_results
+SELECT
+  PLUGIN_NAME,
+  PLUGIN_VERSION,
+  PLUGIN_STATUS,
+  PLUGIN_TYPE,
+  PLUGIN_AUTHOR,
+  PLUGIN_DESCRIPTION,
+  PLUGIN_LICENSE,
+  PLUGIN_MATURITY,
+  PLUGIN_AUTH_VERSION
+FROM INFORMATION_SCHEMA.PLUGINS
+  WHERE PLUGIN_TYPE='DATA TYPE'
+    AND PLUGIN_NAME='uuid';
+--horizontal_results
+
+--echo #
+--echo # End of 10.5 tests
+--echo #
diff --git a/plugin/type_uuid/mysql-test/type_uuid/type_uuid_stat_tables.result b/plugin/type_uuid/mysql-test/type_uuid/type_uuid_stat_tables.result
new file mode 100644
index 00000000000..09784df61ff
--- /dev/null
+++ b/plugin/type_uuid/mysql-test/type_uuid/type_uuid_stat_tables.result
@@ -0,0 +1,24 @@
+#
+# Start of 10.5 tests
+#
+#
+# MDEV-4958 Adding datatype UUID
+#
+CREATE TABLE t1 (a UUID);
+INSERT INTO t1 VALUES
+('123e4567-e89b-12d3-a456-426655440000'),
+('123e4567-e89b-12d3-a456-426655440001');
+ANALYZE TABLE t1 PERSISTENT FOR ALL;
+Table	Op	Msg_type	Msg_text
+test.t1	analyze	status	Engine-independent statistics collected
+test.t1	analyze	status	OK
+INSERT INTO t1 VALUES ('123e4567-e89b-12d3-a456-426655440002');
+SELECT * FROM t1 ORDER BY a;
+a
+123e4567-e89b-12d3-a456-426655440000
+123e4567-e89b-12d3-a456-426655440001
+123e4567-e89b-12d3-a456-426655440002
+DROP TABLE t1;
+#
+# End of 10.5 tests
+#
diff --git a/plugin/type_uuid/mysql-test/type_uuid/type_uuid_stat_tables.test b/plugin/type_uuid/mysql-test/type_uuid/type_uuid_stat_tables.test
new file mode 100644
index 00000000000..1a39f813cba
--- /dev/null
+++ b/plugin/type_uuid/mysql-test/type_uuid/type_uuid_stat_tables.test
@@ -0,0 +1,22 @@
+--source include/have_stat_tables.inc
+
+--echo #
+--echo # Start of 10.5 tests
+--echo #
+
+--echo #
+--echo # MDEV-4958 Adding datatype UUID
+--echo #
+
+CREATE TABLE t1 (a UUID);
+INSERT INTO t1 VALUES
+  ('123e4567-e89b-12d3-a456-426655440000'),
+  ('123e4567-e89b-12d3-a456-426655440001');
+ANALYZE TABLE t1 PERSISTENT FOR ALL;
+INSERT INTO t1 VALUES ('123e4567-e89b-12d3-a456-426655440002');
+SELECT * FROM t1 ORDER BY a;
+DROP TABLE t1;
+
+--echo #
+--echo # End of 10.5 tests
+--echo #
diff --git a/plugin/type_uuid/plugin.cc b/plugin/type_uuid/plugin.cc
new file mode 100644
index 00000000000..ae511b95456
--- /dev/null
+++ b/plugin/type_uuid/plugin.cc
@@ -0,0 +1,122 @@
+/* Copyright (c) 2019,2021, MariaDB Corporation
+
+   This program is free software; you can redistribute it and/or modify
+   it under the terms of the GNU General Public License as published by
+   the Free Software Foundation; version 2 of the License.
+
+   This program is distributed in the hope that it will be useful,
+   but WITHOUT ANY WARRANTY; without even the implied warranty of
+   MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
+   GNU General Public License for more details.
+
+   You should have received a copy of the GNU General Public License
+   along with this program; if not, write to the Free Software
+   Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1335  USA */
+
+#define MYSQL_SERVER
+#include "mariadb.h"
+#include "sql_class.h"
+#include "sql_type_uuid.h"
+#include "item_uuidfunc.h"
+#include <mysql/plugin_data_type.h>
+#include <mysql/plugin_function.h>
+
+static struct st_mariadb_data_type plugin_descriptor_type_uuid=
+{
+  MariaDB_DATA_TYPE_INTERFACE_VERSION,
+  UUIDBundle::type_handler_fbt()
+};
+
+/*************************************************************************/
+
+class Create_func_uuid : public Create_func_arg0
+{
+public:
+  Item *create_builder(THD *thd) override
+  {
+    DBUG_ENTER("Create_func_uuid::create");
+    thd->lex->set_stmt_unsafe(LEX::BINLOG_STMT_UNSAFE_SYSTEM_FUNCTION);
+    thd->lex->safe_to_cache_query= 0;
+    DBUG_RETURN(new (thd->mem_root) Item_func_uuid(thd, true));
+  }
+  static Create_func_uuid s_singleton;
+
+protected:
+  Create_func_uuid() {}
+  virtual ~Create_func_uuid() {}
+};
+
+
+class Create_func_sys_guid : public Create_func_arg0
+{
+public:
+  Item *create_builder(THD *thd) override
+  {
+    DBUG_ENTER("Create_func_sys_guid::create");
+    thd->lex->set_stmt_unsafe(LEX::BINLOG_STMT_UNSAFE_SYSTEM_FUNCTION);
+    thd->lex->safe_to_cache_query= 0;
+    DBUG_RETURN(new (thd->mem_root) Item_func_uuid(thd, false));
+  }
+  static Create_func_sys_guid s_singleton;
+
+protected:
+  Create_func_sys_guid() {}
+  virtual ~Create_func_sys_guid() {}
+};
+
+Create_func_uuid Create_func_uuid::s_singleton;
+Create_func_sys_guid Create_func_sys_guid::s_singleton;
+
+static Plugin_function
+  plugin_descriptor_function_uuid(&Create_func_uuid::s_singleton),
+  plugin_descriptor_function_sys_guid(&Create_func_sys_guid::s_singleton);
+
+/*************************************************************************/
+
+maria_declare_plugin(type_uuid)
+{
+  MariaDB_DATA_TYPE_PLUGIN,     // the plugin type (see include/mysql/plugin.h)
+  &plugin_descriptor_type_uuid, // pointer to type-specific plugin descriptor
+  "uuid",                       // plugin name
+  "MariaDB Corporation",        // plugin author
+  "Data type UUID",             // the plugin description
+  PLUGIN_LICENSE_GPL,           // the plugin license (see include/mysql/plugin.h)
+  0,                            // Pointer to plugin initialization function
+  0,                            // Pointer to plugin deinitialization function
+  0x0100,                       // Numeric version 0xAABB means AA.BB version
+  NULL,                         // Status variables
+  NULL,                         // System variables
+  "1.0",                        // String version representation
+  MariaDB_PLUGIN_MATURITY_BETA  // Maturity(see include/mysql/plugin.h)*/
+},
+{
+  MariaDB_FUNCTION_PLUGIN,      // the plugin type (see include/mysql/plugin.h)
+  &plugin_descriptor_function_uuid,// pointer to type-specific plugin descriptor
+  "uuid",                       // plugin name
+  "MariaDB Corporation",        // plugin author
+  "Function UUID()",            // the plugin description
+  PLUGIN_LICENSE_GPL,           // the plugin license (see include/mysql/plugin.h)
+  0,                            // Pointer to plugin initialization function
+  0,                            // Pointer to plugin deinitialization function
+  0x0100,                       // Numeric version 0xAABB means AA.BB version
+  NULL,                         // Status variables
+  NULL,                         // System variables
+  "1.0",                        // String version representation
+  MariaDB_PLUGIN_MATURITY_BETA  // Maturity(see include/mysql/plugin.h)*/
+},
+{
+  MariaDB_FUNCTION_PLUGIN,      // the plugin type (see include/mysql/plugin.h)
+  &plugin_descriptor_function_sys_guid,// pointer to type-specific plugin descriptor
+  "sys_guid",                   // plugin name
+  "MariaDB Corporation",        // plugin author
+  "Function SYS_GUID()",        // the plugin description
+  PLUGIN_LICENSE_GPL,           // the plugin license (see include/mysql/plugin.h)
+  0,                            // Pointer to plugin initialization function
+  0,                            // Pointer to plugin deinitialization function
+  0x0100,                       // Numeric version 0xAABB means AA.BB version
+  NULL,                         // Status variables
+  NULL,                         // System variables
+  "1.0",                        // String version representation
+  MariaDB_PLUGIN_MATURITY_BETA  // Maturity(see include/mysql/plugin.h)*/
+}
+maria_declare_plugin_end;
diff --git a/plugin/type_uuid/sql_type_uuid.cc b/plugin/type_uuid/sql_type_uuid.cc
new file mode 100644
index 00000000000..3f5549fe4dc
--- /dev/null
+++ b/plugin/type_uuid/sql_type_uuid.cc
@@ -0,0 +1,87 @@
+/* Copyright (c) 2019,2021 MariaDB Corporation
+
+   This program is free software; you can redistribute it and/or modify
+   it under the terms of the GNU General Public License as published by
+   the Free Software Foundation; version 2 of the License.
+
+   This program is distributed in the hope that it will be useful,
+   but WITHOUT ANY WARRANTY; without even the implied warranty of
+   MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
+   GNU General Public License for more details.
+
+   You should have received a copy of the GNU General Public License
+   along with this program; if not, write to the Free Software
+   Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1335  USA */
+
+#define MYSQL_SERVER
+#include "mariadb.h"
+#include "my_net.h"
+#include "sql_class.h" // THD, SORT_FIELD_ATTR
+#include "opt_range.h" // SEL_ARG
+#include "sql_type_uuid.h"
+
+
+static bool get_digit(char ch, uint *val)
+{
+  if (ch >= '0' && ch <= '9')
+  {
+    *val= (uint) ch - '0';
+    return false;
+  }
+  if (ch >= 'a' && ch <= 'f')
+  {
+    *val= (uint) ch - 'a' + 0x0a;
+    return false;
+  }
+  if (ch >= 'A' && ch <= 'F')
+  {
+    *val= (uint) ch - 'A' + 0x0a;
+    return false;
+  }
+  return true;
+}
+
+
+template<>
+bool UUIDBundle::Fbt::ascii_to_fbt(const char *str, size_t str_length)
+{
+  if (str_length < 32 || str_length > 3 * binary_length() - 1)
+    return true;
+
+  uint oidx= 0;
+  for (const char *s= str; s < str + str_length; )
+  {
+    if (oidx >= binary_length())
+      goto err;
+    if (*s == '-')
+    {
+      if (s == str)
+        goto err;
+      s++;
+      continue;
+    }
+    uint hi, lo;
+    if (get_digit(*s++, &hi) || get_digit(*s++, &lo))
+      goto err;
+    m_buffer[oidx++]= (char) ((hi << 4) + lo);
+  }
+  return false;
+err:
+  bzero(m_buffer, sizeof(m_buffer));
+  return true;
+}
+
+template<>
+size_t UUIDBundle::Fbt::to_string(char *dst, size_t dstsize) const
+{
+  my_uuid2str((const uchar *) m_buffer, dst, 1);
+  return MY_UUID_STRING_LENGTH;
+}
+
+
+template<>
+const Name &UUIDBundle::Type_handler_fbt::default_value() const
+{
+  static Name def(STRING_WITH_LEN("00000000-0000-0000-0000-000000000000"));
+  return def;
+}
diff --git a/plugin/type_uuid/sql_type_uuid.h b/plugin/type_uuid/sql_type_uuid.h
new file mode 100644
index 00000000000..2b85c62f21c
--- /dev/null
+++ b/plugin/type_uuid/sql_type_uuid.h
@@ -0,0 +1,22 @@
+#ifndef SQL_TYPE_UUID_INCLUDED
+#define SQL_TYPE_UUID_INCLUDED
+
+/* Copyright (c) 2019,2021 MariaDB Corporation
+
+   This program is free software; you can redistribute it and/or modify
+   it under the terms of the GNU General Public License as published by
+   the Free Software Foundation; version 2 of the License.
+
+   This program is distributed in the hope that it will be useful,
+   but WITHOUT ANY WARRANTY; without even the implied warranty of
+   MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
+   GNU General Public License for more details.
+
+   You should have received a copy of the GNU General Public License
+   along with this program; if not, write to the Free Software
+   Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1335  USA */
+
+#include "sql_type_fixedbin.h"
+typedef FixedBinTypeBundle<MY_UUID_SIZE, MY_UUID_STRING_LENGTH> UUIDBundle;
+
+#endif // SQL_TYPE_UUID_INCLUDED
diff --git a/sql/item_create.cc b/sql/item_create.cc
index e019ac6f8bb..6dd2cdd8d25 100644
--- a/sql/item_create.cc
+++ b/sql/item_create.cc
@@ -2261,30 +2261,6 @@ protected:
 };
 
 
-class Create_func_uuid : public Create_func_arg0
-{
-public:
-  virtual Item *create_builder(THD *thd);
-
-  static Create_func_uuid s_singleton;
-
-protected:
-  Create_func_uuid() {}
-  virtual ~Create_func_uuid() {}
-};
-
-class Create_func_sys_guid : public Create_func_arg0
-{
-public:
-  virtual Item *create_builder(THD *thd);
-
-  static Create_func_sys_guid s_singleton;
-
-protected:
-  Create_func_sys_guid() {}
-  virtual ~Create_func_sys_guid() {}
-};
-
 class Create_func_uuid_short : public Create_func_arg0
 {
 public:
@@ -5328,29 +5304,6 @@ Create_func_unix_timestamp::create_native(THD *thd, LEX_CSTRING *name,
 }
 
 
-Create_func_uuid Create_func_uuid::s_singleton;
-
-Item*
-Create_func_uuid::create_builder(THD *thd)
-{
-  DBUG_ENTER("Create_func_uuid::create");
-  thd->lex->set_stmt_unsafe(LEX::BINLOG_STMT_UNSAFE_SYSTEM_FUNCTION);
-  thd->lex->safe_to_cache_query= 0;
-  DBUG_RETURN(new (thd->mem_root) Item_func_uuid(thd, 0));
-}
-
-Create_func_sys_guid Create_func_sys_guid::s_singleton;
-
-Item*
-Create_func_sys_guid::create_builder(THD *thd)
-{
-  DBUG_ENTER("Create_func_sys_guid::create");
-  thd->lex->set_stmt_unsafe(LEX::BINLOG_STMT_UNSAFE_SYSTEM_FUNCTION);
-  thd->lex->safe_to_cache_query= 0;
-  DBUG_RETURN(new (thd->mem_root) Item_func_uuid(thd, 1));
-}
-
-
 Create_func_uuid_short Create_func_uuid_short::s_singleton;
 
 Item*
@@ -5692,7 +5645,6 @@ Native_func_registry func_array[] =
       BUILDER(Create_func_substr_oracle)},
   { { STRING_WITH_LEN("SUBSTRING_INDEX") }, BUILDER(Create_func_substr_index)},
   { { STRING_WITH_LEN("SUBTIME") }, BUILDER(Create_func_subtime)},
-  { { STRING_WITH_LEN("SYS_GUID") }, BUILDER(Create_func_sys_guid)},
   { { STRING_WITH_LEN("TAN") }, BUILDER(Create_func_tan)},
   { { STRING_WITH_LEN("TIMEDIFF") }, BUILDER(Create_func_timediff)},
   { { STRING_WITH_LEN("TIME_FORMAT") }, BUILDER(Create_func_time_format)},
@@ -5708,7 +5660,6 @@ Native_func_registry func_array[] =
   { { STRING_WITH_LEN("UNIX_TIMESTAMP") }, BUILDER(Create_func_unix_timestamp)},
   { { STRING_WITH_LEN("UPDATEXML") }, BUILDER(Create_func_xml_update)},
   { { STRING_WITH_LEN("UPPER") }, BUILDER(Create_func_ucase)},
-  { { STRING_WITH_LEN("UUID") }, BUILDER(Create_func_uuid)},
   { { STRING_WITH_LEN("UUID_SHORT") }, BUILDER(Create_func_uuid_short)},
   { { STRING_WITH_LEN("VERSION") }, BUILDER(Create_func_version)},
   { { STRING_WITH_LEN("WEEKDAY") }, BUILDER(Create_func_weekday)},
diff --git a/sql/item_strfunc.cc b/sql/item_strfunc.cc
index 10f464a06e1..dce94e7a481 100644
--- a/sql/item_strfunc.cc
+++ b/sql/item_strfunc.cc
@@ -4375,22 +4375,6 @@ err:
 #endif
 
 
-String *Item_func_uuid::val_str(String *str)
-{
-  DBUG_ASSERT(fixed());
-  uchar guid[MY_UUID_SIZE];
-  size_t length= without_separators ? MY_UUID_BARE_STRING_LENGTH
-                                    : MY_UUID_STRING_LENGTH;
-
-  str->alloc(length+1);
-  str->length(length);
-  str->set_charset(system_charset_info);
-  my_uuid(guid);
-  my_uuid2str(guid, (char *)str->ptr(), !without_separators);
-  return str;
-}
-
-
 Item_func_dyncol_create::Item_func_dyncol_create(THD *thd, List<Item> &args,
                                                  DYNCALL_CREATE_DEF *dfs):
   Item_str_func(thd, args), defs(dfs), vals(0), keys_num(NULL), keys_str(NULL),
diff --git a/sql/item_strfunc.h b/sql/item_strfunc.h
index 8e0e6b3a1c5..d4d3bcc3e07 100644
--- a/sql/item_strfunc.h
+++ b/sql/item_strfunc.h
@@ -1996,40 +1996,6 @@ public:
 };
 
 
-class Item_func_uuid: public Item_str_func
-{
-  /* Set if uuid should be returned without separators (Oracle sys_guid) */
-  bool without_separators;
-public:
-Item_func_uuid(THD *thd, bool without_separators_arg): Item_str_func(thd),
-    without_separators(without_separators_arg)
-  {}
-  bool fix_length_and_dec() override
-  {
-    collation.set(DTCollation_numeric());
-    fix_char_length(without_separators ? MY_UUID_BARE_STRING_LENGTH
-                                       : MY_UUID_STRING_LENGTH);
-    return FALSE;
-  }
-  bool const_item() const override { return false; }
-  table_map used_tables() const override { return RAND_TABLE_BIT; }
-  LEX_CSTRING func_name_cstring() const override
-  {
-    static LEX_CSTRING mariadb_name=  {STRING_WITH_LEN("uuid") };
-    static LEX_CSTRING oracle_name=   {STRING_WITH_LEN("sys_guid") };
-    return without_separators ? oracle_name : mariadb_name;
-  }
-  String *val_str(String *) override;
-  bool check_vcol_func_processor(void *arg) override
-  {
-    return mark_unsupported_function(func_name(), "()", arg,
-                                     VCOL_NON_DETERMINISTIC);
-  }
-  Item *get_copy(THD *thd) override
-  { return get_item_copy<Item_func_uuid>(thd, this); }
-};
-
-
 class Item_func_dyncol_create: public Item_str_func
 {
 protected: