maria-developers team mailing list archive
-
maria-developers team
-
Mailing list archive
-
Message #11272
MDEV-16094 Crash when using AS OF with a stored function
Hi Sergei,
Please review a patch for MDEV-16094.
Thanks!
commit fa6cf08ad1c01e990cd6a497a0403c1bbfe0b27e
Author: Alexander Barkov <bar@xxxxxxxxxxx>
Date: Mon May 7 16:59:34 2018 +0400
MDEV-16094 Crash when using AS OF with a stored function
MDEV-16100 FOR SYSTEM_TIME erroneously resolves string user variables as transaction IDs
Problem:
Vers_history_point::resolve_unit() tested item->result_type() before
item->fix_fields() was called.
- Item_func_get_user_var::result_type() returned REAL_RESULT by default.
This caused MDEV-16100.
- Item_func_sp::result_type() crashed on assert.
This caused MDEV-16094
Changes:
1. Adding item->fix_fields() into Vers_history_point::resolve_unit()
before using data type specific properties of the history point
expression.
2. Adding a new virtual method Type_handler::Vers_history_point_resolve_unit()
3. Implementing type-specific
Type_handler_xxx::Type_handler::Vers_history_point_resolve_unit()
in the way to:
a. resolve temporal and general purpose string types to TIMESTAMP
b. resolve BIT and general purpose INT types to TRANSACTION
c. disallow use of non-relevant data type expressions in FOR SYSTEM_TIME
Note, DOUBLE and DECIMAL data types are disallowed intentionally.
- DOUBLE does not have enough precision to hold huge BIGINT UNSIGNED values
- DECIMAL rounds on conversion to INT
Both lack of precision and rounding might potentionally lead to
very unpredictable results when a wrong transaction ID would be chosen.
If one really wants dangerous use of DOUBLE and DECIMAL, explicit CAST
can be used:
FOR SYSTEM_TIME AS OF CAST(double_or_decimal AS UNSIGNED)
QQ: perhaps DECIMAL(N,0) could still be allowed.
4. Adding a new virtual method Item::type_handler_for_system_time(),
to make HEX hybrids and bit literals work as TRANSACTION rather
than TIMESTAMP.
5. sql_yacc.yy: replacing the rule temporal_literal to "TIMESTAMP TEXT_STRING".
Other temporal literals now resolve to TIMESTAMP through the new
Type_handler methods. No special grammar needed. This removed
a few shift/resolve conflicts.
(TIMESTAMP related conflicts in "history_point:" will be removed separately)
6. Removing the "timestamp_only" parameter from
vers_select_conds_t::resolve_units() and Vers_history_point::resolve_unit().
It was a hint telling that a table did not have any TRANSACTION-aware
system time columns, so it's OK to resolve to TIMESTAMP in case of uncertainty.
In the new reduction it works as follows:
- the decision between TIMESTAMP and TRANSACTION is first made
based only on the expression data type only
- then, in case if the expression resolved to TRANSACTION, the table
is checked if TRANSACTION-aware columns really exist.
This way is safer against possible ALTER TABLE statements changing
ROW START and ROW END columns from "BIGINT UNSIGNED" to "TIMESTAMP(x)"
or the other way around.
diff --git a/mysql-test/suite/versioning/r/select.result b/mysql-test/suite/versioning/r/select.result
index ff13b0e..c7fb4f9 100644
--- a/mysql-test/suite/versioning/r/select.result
+++ b/mysql-test/suite/versioning/r/select.result
@@ -346,6 +346,7 @@ x
## no specifier (auto-detection)
select x from t1 for system_time as of @ts;
x
+1
select x from t1 for system_time as of @trx_start;
x
1
@@ -390,7 +391,7 @@ create or replace table t1 (x int) with system versioning;
select * from t1 for system_time as of current_timestamp;
x
select * from t1 for system_time as of now;
-ERROR 42S22: Unknown column 'now' in 'on clause'
+ERROR 42S22: Unknown column 'now' in 'for system_time clause'
### Issue #405, NATURAL JOIN failure
create or replace table t1 (a int) with system versioning;
create or replace table t2 (b int);
diff --git a/mysql-test/suite/versioning/r/trx_id.result b/mysql-test/suite/versioning/r/trx_id.result
index c8c9b69..ab82ea2 100644
--- a/mysql-test/suite/versioning/r/trx_id.result
+++ b/mysql-test/suite/versioning/r/trx_id.result
@@ -155,3 +155,200 @@ x row_start < row_end
2 1
drop database test;
create database test;
+use test;
+#
+# MDEV-16100 FOR SYSTEM_TIME erroneously resolves string user variables as transaction IDs
+#
+CREATE TABLE t1 (
+x INT,
+sys_trx_start BIGINT UNSIGNED AS ROW START,
+sys_trx_end BIGINT UNSIGNED AS ROW END,
+PERIOD FOR SYSTEM_TIME (sys_trx_start, sys_trx_end)
+) WITH SYSTEM VERSIONING ENGINE=INNODB;
+INSERT INTO t1 (x) VALUES (1);
+SET @ts= DATE_ADD(NOW(), INTERVAL 1 YEAR);
+EXPLAIN EXTENDED SELECT x FROM t1 FOR SYSTEM_TIME AS OF TRANSACTION @ts;
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 1 100.00 Using where
+Warnings:
+Note 1003 select `test`.`t1`.`x` AS `x` from `test`.`t1` FOR SYSTEM_TIME ALL where vtq_trx_sees(`test`.`t1`.`sys_trx_end`,@`ts`) and vtq_trx_sees_eq(@`ts`,`test`.`t1`.`sys_trx_start`)
+EXPLAIN EXTENDED SELECT x FROM t1 FOR SYSTEM_TIME AS OF TIMESTAMP @ts;
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 1 100.00 Using where
+Warnings:
+Note 1003 select `test`.`t1`.`x` AS `x` from `test`.`t1` FOR SYSTEM_TIME ALL where vtq_trx_sees(`test`.`t1`.`sys_trx_end`,<cache>(vtq_trx_id(@`ts`))) and vtq_trx_sees_eq(<cache>(vtq_trx_id(@`ts`)),`test`.`t1`.`sys_trx_start`)
+EXPLAIN EXTENDED SELECT x FROM t1 FOR SYSTEM_TIME AS OF @ts;
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 1 100.00 Using where
+Warnings:
+Note 1003 select `test`.`t1`.`x` AS `x` from `test`.`t1` FOR SYSTEM_TIME ALL where vtq_trx_sees(`test`.`t1`.`sys_trx_end`,<cache>(vtq_trx_id(@`ts`))) and vtq_trx_sees_eq(<cache>(vtq_trx_id(@`ts`)),`test`.`t1`.`sys_trx_start`)
+DROP TABLE t1;
+#
+# Testing AS OF with expressions of various kinds and data types
+#
+CREATE TABLE t1
+(
+x INT,
+sys_trx_start BIGINT UNSIGNED AS ROW START INVISIBLE,
+sys_trx_end BIGINT UNSIGNED AS ROW END INVISIBLE,
+PERIOD FOR SYSTEM_TIME (sys_trx_start, sys_trx_end)
+) WITH SYSTEM VERSIONING;
+INSERT INTO t1 VALUES (1);
+CREATE TABLE t2
+(
+x INT,
+sys_trx_start TIMESTAMP(6) AS ROW START INVISIBLE,
+sys_trx_end TIMESTAMP(6) AS ROW END INVISIBLE,
+PERIOD FOR SYSTEM_TIME (sys_trx_start, sys_trx_end)
+) WITH SYSTEM VERSIONING;
+INSERT INTO t2 VALUES (1);
+#
+# ROW is not supported
+#
+SELECT * FROM t1 FOR SYSTEM_TIME AS OF (1,1);
+ERROR HY000: Illegal parameter data type row for operation 'FOR SYSTEM_TIME'
+SELECT * FROM t2 FOR SYSTEM_TIME AS OF (1,1);
+ERROR HY000: Illegal parameter data type row for operation 'FOR SYSTEM_TIME'
+#
+# DOUBLE is not supported, use explicit CAST
+#
+SELECT * FROM t1 FOR SYSTEM_TIME AS OF RAND();
+ERROR HY000: Illegal parameter data type double for operation 'FOR SYSTEM_TIME'
+SELECT * FROM t1 FOR SYSTEM_TIME AS OF (RAND());
+ERROR HY000: Illegal parameter data type double for operation 'FOR SYSTEM_TIME'
+SELECT * FROM t1 FOR SYSTEM_TIME AS OF COALESCE(RAND());
+ERROR HY000: Illegal parameter data type double for operation 'FOR SYSTEM_TIME'
+SELECT * FROM t2 FOR SYSTEM_TIME AS OF RAND();
+ERROR HY000: Illegal parameter data type double for operation 'FOR SYSTEM_TIME'
+SELECT * FROM t2 FOR SYSTEM_TIME AS OF (RAND());
+ERROR HY000: Illegal parameter data type double for operation 'FOR SYSTEM_TIME'
+SELECT * FROM t2 FOR SYSTEM_TIME AS OF COALESCE(RAND());
+ERROR HY000: Illegal parameter data type double for operation 'FOR SYSTEM_TIME'
+#
+# DECIMAL is not supported, use explicit CAST
+#
+SELECT * FROM t1 FOR SYSTEM_TIME AS OF 10.1;
+ERROR HY000: Illegal parameter data type decimal for operation 'FOR SYSTEM_TIME'
+SELECT * FROM t1 FOR SYSTEM_TIME AS OF COALESCE(10.1);
+ERROR HY000: Illegal parameter data type decimal for operation 'FOR SYSTEM_TIME'
+SELECT * FROM t2 FOR SYSTEM_TIME AS OF 10.1;
+ERROR HY000: Illegal parameter data type decimal for operation 'FOR SYSTEM_TIME'
+SELECT * FROM t2 FOR SYSTEM_TIME AS OF COALESCE(10.1);
+ERROR HY000: Illegal parameter data type decimal for operation 'FOR SYSTEM_TIME'
+#
+# YEAR is not supported, use explicit CAST
+#
+BEGIN NOT ATOMIC
+DECLARE var YEAR;
+SELECT * FROM t1 FOR SYSTEM_TIME AS OF var;
+END;
+$$
+ERROR HY000: Illegal parameter data type year for operation 'FOR SYSTEM_TIME'
+BEGIN NOT ATOMIC
+DECLARE var YEAR;
+SELECT * FROM t2 FOR SYSTEM_TIME AS OF var;
+END;
+$$
+ERROR HY000: Illegal parameter data type year for operation 'FOR SYSTEM_TIME'
+#
+# ENUM is not supported, use explicit CAST
+#
+BEGIN NOT ATOMIC
+DECLARE var ENUM('xxx') DEFAULT 'xxx';
+SELECT * FROM t1 FOR SYSTEM_TIME AS OF var;
+END;
+$$
+ERROR HY000: Illegal parameter data type enum for operation 'FOR SYSTEM_TIME'
+BEGIN NOT ATOMIC
+DECLARE var ENUM('xxx') DEFAULT 'xxx';
+SELECT * FROM t2 FOR SYSTEM_TIME AS OF var;
+END;
+$$
+ERROR HY000: Illegal parameter data type enum for operation 'FOR SYSTEM_TIME'
+#
+# SET is not supported, use explicit CAST
+#
+BEGIN NOT ATOMIC
+DECLARE var SET('xxx') DEFAULT 'xxx';
+SELECT * FROM t1 FOR SYSTEM_TIME AS OF var;
+END;
+$$
+ERROR HY000: Illegal parameter data type set for operation 'FOR SYSTEM_TIME'
+BEGIN NOT ATOMIC
+DECLARE var SET('xxx') DEFAULT 'xxx';
+SELECT * FROM t2 FOR SYSTEM_TIME AS OF var;
+END;
+$$
+ERROR HY000: Illegal parameter data type set for operation 'FOR SYSTEM_TIME'
+#
+# BIT is resolved to TRANSACTION
+#
+BEGIN NOT ATOMIC
+DECLARE var BIT(10);
+SELECT * FROM t1 FOR SYSTEM_TIME AS OF var;
+END;
+$$
+x
+1
+BEGIN NOT ATOMIC
+DECLARE var BIT(10);
+SELECT * FROM t2 FOR SYSTEM_TIME AS OF var;
+END;
+$$
+ERROR HY000: Transaction system versioning for `t2` is not supported
+#
+# String literals resolve to TIMESTAMP
+#
+SELECT * FROM t1 FOR SYSTEM_TIME AS OF '2038-12-30 00:00:00';
+x
+1
+SELECT * FROM t2 FOR SYSTEM_TIME AS OF '2038-12-30 00:00:00';
+x
+#
+# HEX hybrids resolve to TRANSACTION
+#
+SELECT * FROM t1 FOR SYSTEM_TIME AS OF (0x60);
+x
+1
+SELECT * FROM t2 FOR SYSTEM_TIME AS OF (0x60);
+ERROR HY000: Transaction system versioning for `t2` is not supported
+#
+# BIT literals resolve to TRANSACTION
+#
+SELECT * FROM t1 FOR SYSTEM_TIME AS OF (b'1100000');
+x
+1
+SELECT * FROM t2 FOR SYSTEM_TIME AS OF (b'1100000');
+ERROR HY000: Transaction system versioning for `t2` is not supported
+DROP TABLE t1, t2;
+#
+# MDEV-16094 Crash when using AS OF with a stored function
+#
+CREATE FUNCTION fts() RETURNS DATETIME RETURN '2001-01-01 10:20:30';
+CREATE FUNCTION ftx() RETURNS BIGINT UNSIGNED RETURN 1;
+CREATE TABLE ttx
+(
+x INT,
+start_timestamp BIGINT UNSIGNED GENERATED ALWAYS AS ROW START,
+end_timestamp BIGINT UNSIGNED GENERATED ALWAYS AS ROW END,
+PERIOD FOR SYSTEM_TIME(start_timestamp, end_timestamp)
+) ENGINE=InnoDB WITH SYSTEM VERSIONING;
+CREATE TABLE tts
+(
+x INT,
+start_timestamp TIMESTAMP(6) GENERATED ALWAYS AS ROW START,
+end_timestamp TIMESTAMP(6) GENERATED ALWAYS AS ROW END,
+PERIOD FOR SYSTEM_TIME(start_timestamp, end_timestamp)
+) ENGINE=InnoDB WITH SYSTEM VERSIONING;
+SELECT * FROM tts FOR SYSTEM_TIME AS OF fts();
+x start_timestamp end_timestamp
+SELECT * FROM tts FOR SYSTEM_TIME AS OF ftx();
+ERROR HY000: Transaction system versioning for `tts` is not supported
+SELECT * FROM ttx FOR SYSTEM_TIME AS OF fts();
+x start_timestamp end_timestamp
+SELECT * FROM ttx FOR SYSTEM_TIME AS OF ftx();
+x start_timestamp end_timestamp
+DROP TABLE tts;
+DROP TABLE ttx;
+DROP FUNCTION fts;
+DROP FUNCTION ftx;
diff --git a/mysql-test/suite/versioning/t/trx_id.test b/mysql-test/suite/versioning/t/trx_id.test
index 45d453d..a085cf3 100644
--- a/mysql-test/suite/versioning/t/trx_id.test
+++ b/mysql-test/suite/versioning/t/trx_id.test
@@ -143,3 +143,243 @@ select x, row_start < row_end from t1 for system_time all;
drop database test;
create database test;
+use test;
+
+
+--echo #
+--echo # MDEV-16100 FOR SYSTEM_TIME erroneously resolves string user variables as transaction IDs
+--echo #
+
+CREATE TABLE t1 (
+ x INT,
+ sys_trx_start BIGINT UNSIGNED AS ROW START,
+ sys_trx_end BIGINT UNSIGNED AS ROW END,
+ PERIOD FOR SYSTEM_TIME (sys_trx_start, sys_trx_end)
+) WITH SYSTEM VERSIONING ENGINE=INNODB;
+INSERT INTO t1 (x) VALUES (1);
+SET @ts= DATE_ADD(NOW(), INTERVAL 1 YEAR);
+EXPLAIN EXTENDED SELECT x FROM t1 FOR SYSTEM_TIME AS OF TRANSACTION @ts;
+EXPLAIN EXTENDED SELECT x FROM t1 FOR SYSTEM_TIME AS OF TIMESTAMP @ts;
+EXPLAIN EXTENDED SELECT x FROM t1 FOR SYSTEM_TIME AS OF @ts;
+DROP TABLE t1;
+
+
+--echo #
+--echo # Testing AS OF with expressions of various kinds and data types
+--echo #
+
+CREATE TABLE t1
+(
+ x INT,
+ sys_trx_start BIGINT UNSIGNED AS ROW START INVISIBLE,
+ sys_trx_end BIGINT UNSIGNED AS ROW END INVISIBLE,
+ PERIOD FOR SYSTEM_TIME (sys_trx_start, sys_trx_end)
+) WITH SYSTEM VERSIONING;
+INSERT INTO t1 VALUES (1);
+
+CREATE TABLE t2
+(
+ x INT,
+ sys_trx_start TIMESTAMP(6) AS ROW START INVISIBLE,
+ sys_trx_end TIMESTAMP(6) AS ROW END INVISIBLE,
+ PERIOD FOR SYSTEM_TIME (sys_trx_start, sys_trx_end)
+) WITH SYSTEM VERSIONING;
+INSERT INTO t2 VALUES (1);
+
+--echo #
+--echo # ROW is not supported
+--echo #
+
+--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
+SELECT * FROM t1 FOR SYSTEM_TIME AS OF (1,1);
+--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
+SELECT * FROM t2 FOR SYSTEM_TIME AS OF (1,1);
+
+
+--echo #
+--echo # DOUBLE is not supported, use explicit CAST
+--echo #
+
+--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
+SELECT * FROM t1 FOR SYSTEM_TIME AS OF RAND();
+--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
+SELECT * FROM t1 FOR SYSTEM_TIME AS OF (RAND());
+--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
+SELECT * FROM t1 FOR SYSTEM_TIME AS OF COALESCE(RAND());
+
+--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
+SELECT * FROM t2 FOR SYSTEM_TIME AS OF RAND();
+--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
+SELECT * FROM t2 FOR SYSTEM_TIME AS OF (RAND());
+--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
+SELECT * FROM t2 FOR SYSTEM_TIME AS OF COALESCE(RAND());
+
+
+--echo #
+--echo # DECIMAL is not supported, use explicit CAST
+--echo #
+
+--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
+SELECT * FROM t1 FOR SYSTEM_TIME AS OF 10.1;
+--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
+SELECT * FROM t1 FOR SYSTEM_TIME AS OF COALESCE(10.1);
+
+--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
+SELECT * FROM t2 FOR SYSTEM_TIME AS OF 10.1;
+--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
+SELECT * FROM t2 FOR SYSTEM_TIME AS OF COALESCE(10.1);
+
+
+--echo #
+--echo # YEAR is not supported, use explicit CAST
+--echo #
+
+DELIMITER $$;
+--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
+BEGIN NOT ATOMIC
+ DECLARE var YEAR;
+ SELECT * FROM t1 FOR SYSTEM_TIME AS OF var;
+END;
+$$
+DELIMITER ;$$
+
+DELIMITER $$;
+--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
+BEGIN NOT ATOMIC
+ DECLARE var YEAR;
+ SELECT * FROM t2 FOR SYSTEM_TIME AS OF var;
+END;
+$$
+DELIMITER ;$$
+
+
+--echo #
+--echo # ENUM is not supported, use explicit CAST
+--echo #
+
+DELIMITER $$;
+--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
+BEGIN NOT ATOMIC
+ DECLARE var ENUM('xxx') DEFAULT 'xxx';
+ SELECT * FROM t1 FOR SYSTEM_TIME AS OF var;
+END;
+$$
+DELIMITER ;$$
+
+
+DELIMITER $$;
+--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
+BEGIN NOT ATOMIC
+ DECLARE var ENUM('xxx') DEFAULT 'xxx';
+ SELECT * FROM t2 FOR SYSTEM_TIME AS OF var;
+END;
+$$
+DELIMITER ;$$
+
+
+--echo #
+--echo # SET is not supported, use explicit CAST
+--echo #
+
+DELIMITER $$;
+--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
+BEGIN NOT ATOMIC
+ DECLARE var SET('xxx') DEFAULT 'xxx';
+ SELECT * FROM t1 FOR SYSTEM_TIME AS OF var;
+END;
+$$
+DELIMITER ;$$
+
+DELIMITER $$;
+--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
+BEGIN NOT ATOMIC
+ DECLARE var SET('xxx') DEFAULT 'xxx';
+ SELECT * FROM t2 FOR SYSTEM_TIME AS OF var;
+END;
+$$
+DELIMITER ;$$
+
+
+--echo #
+--echo # BIT is resolved to TRANSACTION
+--echo #
+
+DELIMITER $$;
+BEGIN NOT ATOMIC
+ DECLARE var BIT(10);
+ SELECT * FROM t1 FOR SYSTEM_TIME AS OF var;
+END;
+$$
+DELIMITER ;$$
+
+DELIMITER $$;
+--error ER_VERS_ENGINE_UNSUPPORTED
+BEGIN NOT ATOMIC
+ DECLARE var BIT(10);
+ SELECT * FROM t2 FOR SYSTEM_TIME AS OF var;
+END;
+$$
+DELIMITER ;$$
+
+
+--echo #
+--echo # String literals resolve to TIMESTAMP
+--echo #
+
+SELECT * FROM t1 FOR SYSTEM_TIME AS OF '2038-12-30 00:00:00';
+SELECT * FROM t2 FOR SYSTEM_TIME AS OF '2038-12-30 00:00:00';
+
+
+--echo #
+--echo # HEX hybrids resolve to TRANSACTION
+--echo #
+
+SELECT * FROM t1 FOR SYSTEM_TIME AS OF (0x60);
+--error ER_VERS_ENGINE_UNSUPPORTED
+SELECT * FROM t2 FOR SYSTEM_TIME AS OF (0x60);
+
+
+--echo #
+--echo # BIT literals resolve to TRANSACTION
+--echo #
+
+SELECT * FROM t1 FOR SYSTEM_TIME AS OF (b'1100000');
+--error ER_VERS_ENGINE_UNSUPPORTED
+SELECT * FROM t2 FOR SYSTEM_TIME AS OF (b'1100000');
+
+DROP TABLE t1, t2;
+
+
+--echo #
+--echo # MDEV-16094 Crash when using AS OF with a stored function
+--echo #
+
+CREATE FUNCTION fts() RETURNS DATETIME RETURN '2001-01-01 10:20:30';
+CREATE FUNCTION ftx() RETURNS BIGINT UNSIGNED RETURN 1;
+
+CREATE TABLE ttx
+(
+ x INT,
+ start_timestamp BIGINT UNSIGNED GENERATED ALWAYS AS ROW START,
+ end_timestamp BIGINT UNSIGNED GENERATED ALWAYS AS ROW END,
+ PERIOD FOR SYSTEM_TIME(start_timestamp, end_timestamp)
+) ENGINE=InnoDB WITH SYSTEM VERSIONING;
+
+CREATE TABLE tts
+(
+ x INT,
+ start_timestamp TIMESTAMP(6) GENERATED ALWAYS AS ROW START,
+ end_timestamp TIMESTAMP(6) GENERATED ALWAYS AS ROW END,
+ PERIOD FOR SYSTEM_TIME(start_timestamp, end_timestamp)
+) ENGINE=InnoDB WITH SYSTEM VERSIONING;
+
+SELECT * FROM tts FOR SYSTEM_TIME AS OF fts();
+--error ER_VERS_ENGINE_UNSUPPORTED
+SELECT * FROM tts FOR SYSTEM_TIME AS OF ftx();
+SELECT * FROM ttx FOR SYSTEM_TIME AS OF fts();
+SELECT * FROM ttx FOR SYSTEM_TIME AS OF ftx();
+
+DROP TABLE tts;
+DROP TABLE ttx;
+DROP FUNCTION fts;
+DROP FUNCTION ftx;
diff --git a/sql/item.h b/sql/item.h
index e1a8906..3759693 100644
--- a/sql/item.h
+++ b/sql/item.h
@@ -844,6 +844,10 @@ class Item: public Value_source,
{
return type_handler();
}
+ virtual const Type_handler *type_handler_for_system_time() const
+ {
+ return real_type_handler();
+ }
/* result_type() of an item specifies how the value should be returned */
Item_result result_type() const
{
@@ -4212,6 +4216,10 @@ class Item_hex_hybrid: public Item_hex_constant
{
return &type_handler_longlong;
}
+ const Type_handler *type_handler_for_system_time() const
+ {
+ return &type_handler_longlong;
+ }
void print(String *str, enum_query_type query_type);
Item *get_copy(THD *thd)
{ return get_item_copy<Item_hex_hybrid>(thd, this); }
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 320e631..04abf54 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -839,11 +839,13 @@ int SELECT_LEX::vers_setup_conds(THD *thd, TABLE_LIST *tables)
if (vers_conditions)
{
+ thd->where= "for system_time clause";
/* TODO: do resolve fix_length_and_dec(), fix_fields(). This requires
storing vers_conditions as Item and make some magic related to
vers_system_time_t/VERS_TRX_ID at stage of fix_fields()
(this is large refactoring). */
- vers_conditions.resolve_units(timestamps_only);
+ if (vers_conditions.resolve_units(thd))
+ DBUG_RETURN(-1);
if (timestamps_only && (vers_conditions.start.unit == VERS_TRX_ID ||
vers_conditions.end.unit == VERS_TRX_ID))
{
diff --git a/sql/sql_type.cc b/sql/sql_type.cc
index fe5ce3f..22eebaf 100644
--- a/sql/sql_type.cc
+++ b/sql/sql_type.cc
@@ -5786,3 +5786,62 @@ void Type_handler_geometry::Item_param_set_param_func(Item_param *param,
#endif
/***************************************************************************/
+
+bool Type_handler::Vers_history_point_resolve_unit(THD *thd,
+ Vers_history_point *point)
+ const
+{
+ /*
+ Disallow using non-relevant data types in history points.
+ Even expressions with explicit TRANSACTION or TIMESTAMP units.
+ */
+ point->bad_expression_data_type_error(name().ptr());
+ return true;
+}
+
+
+bool Type_handler_typelib::
+ Vers_history_point_resolve_unit(THD *thd,
+ Vers_history_point *point) const
+{
+ /*
+ ENUM/SET have dual type properties (string and numeric).
+ Require explicit CAST to avoid ambiguity.
+ */
+ point->bad_expression_data_type_error(name().ptr());
+ return true;
+}
+
+
+bool Type_handler_general_purpose_int::
+ Vers_history_point_resolve_unit(THD *thd,
+ Vers_history_point *point) const
+{
+ return point->resolve_unit_trx_id(thd);
+}
+
+
+bool Type_handler_bit::
+ Vers_history_point_resolve_unit(THD *thd,
+ Vers_history_point *point) const
+{
+ return point->resolve_unit_trx_id(thd);
+}
+
+
+bool Type_handler_temporal_result::
+ Vers_history_point_resolve_unit(THD *thd,
+ Vers_history_point *point) const
+{
+ return point->resolve_unit_timestamp(thd);
+}
+
+
+bool Type_handler_general_purpose_string::
+ Vers_history_point_resolve_unit(THD *thd,
+ Vers_history_point *point) const
+{
+ return point->resolve_unit_timestamp(thd);
+}
+
+/***************************************************************************/
diff --git a/sql/sql_type.h b/sql/sql_type.h
index 16db421..1ddcef2 100644
--- a/sql/sql_type.h
+++ b/sql/sql_type.h
@@ -72,6 +72,7 @@ class handler;
struct Schema_specification_st;
struct TABLE;
struct SORT_FIELD_ATTR;
+class Vers_history_point;
/**
@@ -1409,6 +1410,9 @@ class Type_handler
Item_func_div_fix_length_and_dec(Item_func_div *func) const= 0;
virtual bool
Item_func_mod_fix_length_and_dec(Item_func_mod *func) const= 0;
+
+ virtual bool
+ Vers_history_point_resolve_unit(THD *thd, Vers_history_point *point) const;
};
@@ -2105,6 +2109,7 @@ class Type_handler_general_purpose_int: public Type_handler_int_result
virtual const Type_limits_int *
type_limits_int_by_unsigned_flag(bool unsigned_flag) const= 0;
uint32 max_display_length(const Item *item) const;
+ bool Vers_history_point_resolve_unit(THD *thd, Vers_history_point *p) const;
};
@@ -2174,6 +2179,7 @@ class Type_handler_temporal_result: public Type_handler
bool Item_func_mul_fix_length_and_dec(Item_func_mul *) const;
bool Item_func_div_fix_length_and_dec(Item_func_div *) const;
bool Item_func_mod_fix_length_and_dec(Item_func_mod *) const;
+ bool Vers_history_point_resolve_unit(THD *thd, Vers_history_point *p) const;
};
@@ -2298,6 +2304,7 @@ class Type_handler_general_purpose_string: public Type_handler_string_result
{
public:
bool is_general_purpose_string_type() const { return true; }
+ bool Vers_history_point_resolve_unit(THD *thd, Vers_history_point *p) const;
};
@@ -2570,6 +2577,7 @@ class Type_handler_bit: public Type_handler_int_result
const Record_addr &addr,
const Type_all_attributes &attr,
TABLE *table) const;
+ bool Vers_history_point_resolve_unit(THD *thd, Vers_history_point *p) const;
};
@@ -3384,6 +3392,7 @@ class Type_handler_typelib: public Type_handler_general_purpose_string
const;
void Item_param_set_param_func(Item_param *param,
uchar **pos, ulong len) const;
+ bool Vers_history_point_resolve_unit(THD *thd, Vers_history_point *p) const;
};
diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy
index 8789b21..7ad4fb9 100644
--- a/sql/sql_yacc.yy
+++ b/sql/sql_yacc.yy
@@ -895,10 +895,10 @@ bool my_yyoverflow(short **a, YYSTYPE **b, size_t *yystacksize);
%parse-param { THD *thd }
%lex-param { THD *thd }
/*
- Currently there are 139 shift/reduce conflicts.
+ Currently there are 127 shift/reduce conflicts.
We should not introduce new conflicts any more.
*/
-%expect 139
+%expect 127
/*
Comments for TOKENS.
@@ -9251,9 +9251,13 @@ opt_history_unit:
;
history_point:
- temporal_literal
+ TIMESTAMP TEXT_STRING
{
- $$= Vers_history_point(VERS_TIMESTAMP, $1);
+ Item *item;
+ if (!(item= create_temporal_literal(thd, $2.str, $2.length, YYCSCL,
+ MYSQL_TYPE_DATETIME, true)))
+ MYSQL_YYABORT;
+ $$= Vers_history_point(VERS_TIMESTAMP, item);
}
| function_call_keyword_timestamp
{
diff --git a/sql/table.cc b/sql/table.cc
index 8475d8b..5de0475 100644
--- a/sql/table.cc
+++ b/sql/table.cc
@@ -8858,28 +8858,33 @@ bool TR_table::check(bool error)
return false;
}
-void vers_select_conds_t::resolve_units(bool timestamps_only)
+bool vers_select_conds_t::resolve_units(THD *thd)
{
DBUG_ASSERT(type != SYSTEM_TIME_UNSPECIFIED);
DBUG_ASSERT(start.item);
- start.resolve_unit(timestamps_only);
- end.resolve_unit(timestamps_only);
+ return start.resolve_unit(thd) ||
+ end.resolve_unit(thd);
}
-void Vers_history_point::resolve_unit(bool timestamps_only)
+
+bool Vers_history_point::resolve_unit(THD *thd)
{
- if (item && unit == VERS_UNDEFINED)
- {
- if (item->type() == Item::FIELD_ITEM || timestamps_only)
- unit= VERS_TIMESTAMP;
- else if (item->result_type() == INT_RESULT ||
- item->result_type() == REAL_RESULT)
- unit= VERS_TRX_ID;
- else
- unit= VERS_TIMESTAMP;
- }
+ if (!item)
+ return false;
+ if (!item->fixed && item->fix_fields(thd, &item))
+ return true;
+ return item->this_item()->type_handler_for_system_time()->
+ Vers_history_point_resolve_unit(thd, this);
}
+
+void Vers_history_point::bad_expression_data_type_error(const char *type) const
+{
+ my_error(ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION, MYF(0),
+ type, "FOR SYSTEM_TIME");
+}
+
+
void Vers_history_point::fix_item()
{
if (item && item->decimals == 0 && item->type() == Item::FUNC_ITEM &&
diff --git a/sql/table.h b/sql/table.h
index 30588be..49d857c 100644
--- a/sql/table.h
+++ b/sql/table.h
@@ -1828,7 +1828,20 @@ class Vers_history_point : public vers_history_point_t
}
void empty() { unit= VERS_UNDEFINED; item= NULL; }
void print(String *str, enum_query_type, const char *prefix, size_t plen);
- void resolve_unit(bool timestamps_only);
+ bool resolve_unit(THD *thd);
+ bool resolve_unit_trx_id(THD *thd)
+ {
+ if (unit == VERS_UNDEFINED)
+ unit= VERS_TRX_ID;
+ return false;
+ }
+ bool resolve_unit_timestamp(THD *thd)
+ {
+ if (unit == VERS_UNDEFINED)
+ unit= VERS_TIMESTAMP;
+ return false;
+ }
+ void bad_expression_data_type_error(const char *type) const;
};
struct vers_select_conds_t
@@ -1873,7 +1886,7 @@ struct vers_select_conds_t
{
return type != SYSTEM_TIME_UNSPECIFIED;
}
- void resolve_units(bool timestamps_only);
+ bool resolve_units(THD *thd);
bool user_defined() const
{
return !from_query && type != SYSTEM_TIME_UNSPECIFIED;
Follow ups
