diff options
Diffstat (limited to 'mysql-test/t')
25 files changed, 1109 insertions, 7 deletions
diff --git a/mysql-test/t/alter_table_online.test b/mysql-test/t/alter_table_online.test index d9c2a2c4d4f..9062ecba35a 100644 --- a/mysql-test/t/alter_table_online.test +++ b/mysql-test/t/alter_table_online.test @@ -121,3 +121,168 @@ drop table t1; create table t1 (a int) partition by hash(a) partitions 2; alter online table t1 modify a int comment 'test'; drop table t1; + +--echo # +--echo # MDEV-8948 ALTER ... INPLACE does work for BINARY, BLOB +--echo # +# +# ALTER to the same [VAR]BINARY type +# +CREATE TABLE t1 (a BINARY(10)); +ALTER TABLE t1 MODIFY a BINARY(10), ALGORITHM=INPLACE; +DROP TABLE t1; + +CREATE TABLE t1 (a VARBINARY(10)); +ALTER TABLE t1 MODIFY a VARBINARY(10), ALGORITHM=INPLACE; +DROP TABLE t1; + +# +# ALTER to the same BLOB variant +# +CREATE TABLE t1 (a TINYBLOB); +ALTER TABLE t1 MODIFY a TINYBLOB, ALGORITHM=INPLACE; +DROP TABLE t1; + +CREATE TABLE t1 (a MEDIUMBLOB); +ALTER TABLE t1 MODIFY a MEDIUMBLOB, ALGORITHM=INPLACE; +DROP TABLE t1; + +CREATE TABLE t1 (a BLOB); +ALTER TABLE t1 MODIFY a BLOB, ALGORITHM=INPLACE; +DROP TABLE t1; + +CREATE TABLE t1 (a LONGBLOB); +ALTER TABLE t1 MODIFY a LONGBLOB, ALGORITHM=INPLACE; +DROP TABLE t1; + +# +# ALTER to the same [VAR]CHAR type +# +CREATE TABLE t1 (a CHAR(10)); +ALTER TABLE t1 MODIFY a CHAR(10), ALGORITHM=INPLACE; +DROP TABLE t1; + +CREATE TABLE t1 (a VARCHAR(10)); +ALTER TABLE t1 MODIFY a VARCHAR(10), ALGORITHM=INPLACE; +DROP TABLE t1; + + +# +# ALTER to the same TEXT variant +# +CREATE TABLE t1 (a TINYTEXT); +ALTER TABLE t1 MODIFY a TINYTEXT, ALGORITHM=INPLACE; +DROP TABLE t1; + +CREATE TABLE t1 (a MEDIUMTEXT); +ALTER TABLE t1 MODIFY a MEDIUMTEXT, ALGORITHM=INPLACE; +DROP TABLE t1; + +CREATE TABLE t1 (a TEXT); +ALTER TABLE t1 MODIFY a TEXT, ALGORITHM=INPLACE; +DROP TABLE t1; + +CREATE TABLE t1 (a LONGTEXT); +ALTER TABLE t1 MODIFY a LONGTEXT, ALGORITHM=INPLACE; +DROP TABLE t1; + +# +# ALTER from a non-binary to a binary collation +# +CREATE TABLE t1 (a CHAR(10)); +--error ER_ALTER_OPERATION_NOT_SUPPORTED +ALTER TABLE t1 MODIFY a CHAR(10) COLLATE latin1_bin, ALGORITHM=INPLACE; +DROP TABLE t1; + +CREATE TABLE t1 (a VARCHAR(10)); +--error ER_ALTER_OPERATION_NOT_SUPPORTED +ALTER TABLE t1 MODIFY a VARCHAR(10) COLLATE latin1_bin, ALGORITHM=INPLACE; +DROP TABLE t1; + +CREATE TABLE t1 (a TINYTEXT); +--error ER_ALTER_OPERATION_NOT_SUPPORTED +ALTER TABLE t1 MODIFY a TINYTEXT COLLATE latin1_bin, ALGORITHM=INPLACE; +DROP TABLE t1; + +CREATE TABLE t1 (a MEDIUMTEXT); +--error ER_ALTER_OPERATION_NOT_SUPPORTED +ALTER TABLE t1 MODIFY a MEDIUMTEXT COLLATE latin1_bin, ALGORITHM=INPLACE; +DROP TABLE t1; + +CREATE TABLE t1 (a TEXT); +--error ER_ALTER_OPERATION_NOT_SUPPORTED +ALTER TABLE t1 MODIFY a TEXT COLLATE latin1_bin, ALGORITHM=INPLACE; +DROP TABLE t1; + +CREATE TABLE t1 (a LONGTEXT); +--error ER_ALTER_OPERATION_NOT_SUPPORTED +ALTER TABLE t1 MODIFY a LONGTEXT COLLATE latin1_bin, ALGORITHM=INPLACE; +DROP TABLE t1; + +# +# ALTER from a binary to a non-binary collation +# +CREATE TABLE t1 (a CHAR(10) COLLATE latin1_bin); +--error ER_ALTER_OPERATION_NOT_SUPPORTED +ALTER TABLE t1 MODIFY a CHAR(10) COLLATE latin1_swedish_ci, ALGORITHM=INPLACE; +DROP TABLE t1; + +CREATE TABLE t1 (a VARCHAR(10) COLLATE latin1_bin); +--error ER_ALTER_OPERATION_NOT_SUPPORTED +ALTER TABLE t1 MODIFY a VARCHAR(10) COLLATE latin1_swedish_ci, ALGORITHM=INPLACE; +DROP TABLE t1; + +CREATE TABLE t1 (a TINYTEXT COLLATE latin1_bin); +--error ER_ALTER_OPERATION_NOT_SUPPORTED +ALTER TABLE t1 MODIFY a TINYTEXT COLLATE latin1_swedish_ci, ALGORITHM=INPLACE; +DROP TABLE t1; + +CREATE TABLE t1 (a MEDIUMTEXT COLLATE latin1_bin); +--error ER_ALTER_OPERATION_NOT_SUPPORTED +ALTER TABLE t1 MODIFY a MEDIUMTEXT COLLATE latin1_swedish_ci, ALGORITHM=INPLACE; +DROP TABLE t1; + +CREATE TABLE t1 (a TEXT COLLATE latin1_bin); +--error ER_ALTER_OPERATION_NOT_SUPPORTED +ALTER TABLE t1 MODIFY a TEXT COLLATE latin1_swedish_ci, ALGORITHM=INPLACE; +DROP TABLE t1; + +CREATE TABLE t1 (a LONGTEXT COLLATE latin1_bin); +--error ER_ALTER_OPERATION_NOT_SUPPORTED +ALTER TABLE t1 MODIFY a LONGTEXT COLLATE latin1_swedish_ci, ALGORITHM=INPLACE; +DROP TABLE t1; + +# +# ALTER from a non-binary collation to another non-binary collation +# +CREATE TABLE t1 (a CHAR(10) COLLATE latin1_general_ci); +--error ER_ALTER_OPERATION_NOT_SUPPORTED +ALTER TABLE t1 MODIFY a CHAR(10) COLLATE latin1_swedish_ci, ALGORITHM=INPLACE; +DROP TABLE t1; + +CREATE TABLE t1 (a VARCHAR(10) COLLATE latin1_general_ci); +--error ER_ALTER_OPERATION_NOT_SUPPORTED +ALTER TABLE t1 MODIFY a VARCHAR(10) COLLATE latin1_swedish_ci, ALGORITHM=INPLACE; +DROP TABLE t1; + +CREATE TABLE t1 (a TINYTEXT COLLATE latin1_general_ci); +--error ER_ALTER_OPERATION_NOT_SUPPORTED +ALTER TABLE t1 MODIFY a TINYTEXT COLLATE latin1_swedish_ci, ALGORITHM=INPLACE; +DROP TABLE t1; + +CREATE TABLE t1 (a MEDIUMTEXT COLLATE latin1_general_ci); +--error ER_ALTER_OPERATION_NOT_SUPPORTED +ALTER TABLE t1 MODIFY a MEDIUMTEXT COLLATE latin1_swedish_ci, ALGORITHM=INPLACE; +DROP TABLE t1; + +CREATE TABLE t1 (a TEXT COLLATE latin1_general_ci); +--error ER_ALTER_OPERATION_NOT_SUPPORTED +ALTER TABLE t1 MODIFY a TEXT COLLATE latin1_swedish_ci, ALGORITHM=INPLACE; +DROP TABLE t1; + +CREATE TABLE t1 (a LONGTEXT COLLATE latin1_general_ci); +--error ER_ALTER_OPERATION_NOT_SUPPORTED +ALTER TABLE t1 MODIFY a LONGTEXT COLLATE latin1_swedish_ci, ALGORITHM=INPLACE; +DROP TABLE t1; + +# End of 10.0 tests diff --git a/mysql-test/t/binary_to_hex.test b/mysql-test/t/binary_to_hex.test new file mode 100644 index 00000000000..8312a246d0c --- /dev/null +++ b/mysql-test/t/binary_to_hex.test @@ -0,0 +1,76 @@ +# === Purpose === +# The purpose of this test case is to make +# sure that the binary data in tables is printed +# as hex when the option binary-as-hex is enabled. +# +# === Related bugs and/or worklogs === +# Bug #25340722 - PRINT BINARY DATA AS HEX IN THE MYSQL +# CLIENT (CONTRIBUTION) +# + +# Save the initial number of concurrent sessions +--source include/count_sessions.inc +--source include/not_embedded.inc + +USE test; +--disable_warnings +DROP TABLE IF EXISTS t1, t2; +--enable_warnings + +CREATE TABLE t1 (c1 TINYBLOB, + c2 BLOB, + c3 MEDIUMBLOB, + c4 LONGBLOB, + c5 TEXT, + c6 BIT(1), + c7 CHAR, + c8 VARCHAR(10), + c9 GEOMETRY) CHARACTER SET = binary; + +SHOW CREATE TABLE t1; + +INSERT INTO t1 VALUES ('tinyblob-text readable', 'blob-text readable', + 'mediumblob-text readable', 'longblob-text readable', + 'text readable', b'1', 'c', 'variable', + POINT(1, 1)); + +CREATE TABLE t2(id int, `col1` binary(10),`col2` blob); + +SHOW CREATE TABLE t2; + +INSERT INTO t2 VALUES (1, X'AB1234', X'123ABC'), (2, X'DE1234', X'123DEF'); + +--echo #Print the table contents when binary-as-hex option is off. +--replace_column 6 # 9 # +SELECT * FROM t1; + +--replace_column 2 # 3 # +SELECT * FROM t2; + +--echo #Print the table contents after turning on the binary-as-hex option +--echo +--echo #Print the table contents in tab format +--echo +--exec $MYSQL test --binary-as-hex -e "SELECT * FROM t1; SELECT * FROM t2;" +--echo +--echo #Print the table contents in table format +--echo +--exec $MYSQL test --binary-as-hex --table -e "SELECT * FROM t1; SELECT * FROM t2 WHERE col2=0x123ABC;" +--echo +--echo #Print the table contents vertically +--echo +--exec $MYSQL test --binary-as-hex --vertical -e "SELECT * FROM t1;" +--echo +--echo #Print the table contents in xml format +--echo +--exec $MYSQL test --binary-as-hex --xml -e "SELECT * FROM t1; SELECT * FROM t2;" +--echo +--echo #Print the table contents in html format +--echo +--exec $MYSQL test --binary-as-hex --html -e "SELECT * FROM t1; SELECT * FROM t2;" + +#Cleanup +DROP TABLE t1, t2; + +# Wait till all disconnects are completed + --source include/wait_until_count_sessions.inc diff --git a/mysql-test/t/bootstrap.test b/mysql-test/t/bootstrap.test index f92b7c5b148..5ab736cee15 100644 --- a/mysql-test/t/bootstrap.test +++ b/mysql-test/t/bootstrap.test @@ -59,6 +59,12 @@ drop table t1; SELECT 'bug' as '' FROM INFORMATION_SCHEMA.ENGINES WHERE engine='innodb' and SUPPORT='YES'; +# +# MDEV-13063 Server crashes in intern_plugin_lock or assertion `plugin_ptr->ref_count == 1' fails in plugin_init +# +--error 1 +--exec $MYSQLD_BOOTSTRAP_CMD --myisam_recover_options=NONE + --echo End of 5.5 tests --source include/not_windows_embedded.inc diff --git a/mysql-test/t/count_distinct.test b/mysql-test/t/count_distinct.test index 10b4ac6f0e7..a00574b6cba 100644 --- a/mysql-test/t/count_distinct.test +++ b/mysql-test/t/count_distinct.test @@ -107,3 +107,19 @@ create view v1 as select * from t1; select count(distinct i) from v1; drop table t1; drop view v1; + +# +# MDEV-12136 SELECT COUNT(DISTINCT) returns the wrong value when tmp_table_size is limited +# +create table t1 (user_id char(64) character set utf8); +insert t1 values(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15),(16),(17); +set @@tmp_table_size = 1024; +select count(distinct user_id) from t1; +alter table t1 modify user_id char(128) character set utf8; +select count(distinct user_id) from t1; +drop table t1; +set @@tmp_table_size = default; + +# +# End of 5.5 tests +# diff --git a/mysql-test/t/ctype_ucs.test b/mysql-test/t/ctype_ucs.test index 165d945944e..04156622ab2 100644 --- a/mysql-test/t/ctype_ucs.test +++ b/mysql-test/t/ctype_ucs.test @@ -924,5 +924,21 @@ SELECT 'a','aa'; --echo # +--echo # MDEV-10306 Wrong results with combination of CONCAT, SUBSTR and CONVERT in subquery +--echo # + +SET NAMES utf8, character_set_connection=ucs2; +SET @save_optimizer_switch=@@optimizer_switch; +SET optimizer_switch=_utf8'derived_merge=on'; +CREATE TABLE t1 (t VARCHAR(10) CHARSET latin1); +INSERT INTO t1 VALUES('abcdefghi'); +SET NAMES utf8, character_set_connection=ucs2; +SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT HEX(t) t2 FROM t1) sub; +SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT TO_BASE64(t) t2 FROM t1) sub; +DROP TABLE t1; +SET optimizer_switch=@save_optimizer_switch; + + +--echo # --echo # End of 10.0 tests --echo # diff --git a/mysql-test/t/engine_error_in_alter-8453.test b/mysql-test/t/engine_error_in_alter-8453.test new file mode 100644 index 00000000000..c4600ec07fe --- /dev/null +++ b/mysql-test/t/engine_error_in_alter-8453.test @@ -0,0 +1,11 @@ +# +# MDEV-8453 Alter table not returning engine errors +# +--source include/have_debug.inc + +create table t1 (a int, b int); +set debug_dbug='+d,external_lock_failure'; +--error ER_GET_ERRMSG +alter table t1 add column c int; +set debug_dbug=''; +drop table t1; diff --git a/mysql-test/t/func_concat.test b/mysql-test/t/func_concat.test index e56d1121808..be573f494a2 100644 --- a/mysql-test/t/func_concat.test +++ b/mysql-test/t/func_concat.test @@ -145,3 +145,94 @@ CALL p1(); DROP PROCEDURE p1; --echo # End of 5.1 tests + + +--echo # +--echo # Start of 10.0 tests +--echo # + +--echo # +--echo # MDEV-10306 Wrong results with combination of CONCAT, SUBSTR and CONVERT in subquery +--echo # + +SET @save_optimizer_switch=@@optimizer_switch; +SET optimizer_switch='derived_merge=on'; + +CREATE TABLE t1 (t VARCHAR(10) CHARSET latin1); +INSERT INTO t1 VALUES('1234567'); +SELECT CONCAT(SUBSTR(t2, 1, 3), SUBSTR(t2, 5)) c1, + CONCAT(SUBSTR(t2,1,3),'---',SUBSTR(t2,5)) c2 + FROM (SELECT CONVERT(t USING latin1) t2 FROM t1) sub; +SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT CONVERT(t USING latin1) t2 FROM t1) sub; +DROP TABLE t1; + +# Other functions affected by MDEV-10306 + +CREATE TABLE t1 (t VARCHAR(10) CHARSET latin1); +INSERT INTO t1 VALUES('1234567'); +SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT CONVERT(t USING latin1) t2 FROM t1) sub; +SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT REVERSE(t) t2 FROM t1) sub; +SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT SOUNDEX(t) t2 FROM t1) sub; +SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT TO_BASE64(t) t2 FROM t1) sub; +SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT WEIGHT_STRING(t) t2 FROM t1) sub; +SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT HEX(t) t2 FROM t1) sub; +SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT QUOTE(t) t2 FROM t1) sub; +DROP TABLE t1; + +CREATE TABLE t1 (t VARCHAR(32) CHARSET latin1); +INSERT INTO t1 VALUES(TO_BASE64('abcdefghi')); +SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT FROM_BASE64(t) t2 FROM t1) sub; +DROP TABLE t1; + +CREATE TABLE t1 (t VARCHAR(32) CHARSET latin1); +INSERT INTO t1 VALUES(HEX('abcdefghi')); +SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT UNHEX(t) t2 FROM t1) sub; +DROP TABLE t1; + +CREATE TABLE t1 (t VARCHAR(30) CHARSET latin1); +INSERT INTO t1 VALUES('test'); +SELECT LENGTH(CONCAT(t2)) c2 FROM (SELECT AES_ENCRYPT(t,'x') t2 FROM t1) sub; +SELECT LENGTH(CONCAT(t2,'-',t2)) c2 FROM (SELECT AES_ENCRYPT(t,'x') t2 FROM t1) sub; +SELECT LENGTH(CONCAT(t2,'--',t2)) c2 FROM (SELECT AES_ENCRYPT(t,'x') t2 FROM t1) sub; +SELECT LENGTH(CONCAT(t2)) c2 FROM (SELECT AES_DECRYPT(AES_ENCRYPT(t,'x'),'x') t2 FROM t1) sub; +SELECT LENGTH(CONCAT(t2,'-',t2)) c2 FROM (SELECT AES_DECRYPT(AES_ENCRYPT(t,'x'),'x') t2 FROM t1) sub; +SELECT LENGTH(CONCAT(t2,'--',t2)) c2 FROM (SELECT AES_DECRYPT(AES_ENCRYPT(t,'x'),'x') t2 FROM t1) sub; +DROP TABLE t1; + + +# Functions not affected by MDEV-10306 +# They only had an unused tmp_value, which was removed. + +CREATE TABLE t1 (t VARCHAR(64) CHARSET latin1); +INSERT INTO t1 VALUES('123456789'); +SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT MD5(t) t2 FROM t1) sub; +SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT FORMAT(t,2) t2 FROM t1) sub; +DROP TABLE t1; + +# Functions not affected by MDEV-10306 +# They already use tmp_value only for internal purposes and +# return the result in the String passed to val_str() + +CREATE TABLE t1 (t VARCHAR(32) CHARSET latin1); +INSERT INTO t1 VALUES('abcdefghi'); +SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT INSERT(t,3,4,'xxx') t2 FROM t1) sub; +DROP TABLE t1; + + +# Functions not affected by MDEV-10306 +# They use this code style: +# String *res= args[0]->val_str(str); +# tmp_value.set(*res, start, end); +# return &tmp_value; + +CREATE TABLE t1 (t VARCHAR(10) CHARSET latin1); +INSERT INTO t1 VALUES('abcdefghi'); +SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT LEFT(t,10) t2 FROM t1) sub; +SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT RIGHT(t,10) t2 FROM t1) sub; +SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT SUBSTR(t,1,10) t2 FROM t1) sub; +SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT LTRIM(t) t2 FROM t1) sub; +SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT RTRIM(t) t2 FROM t1) sub; +SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT TRIM(t) t2 FROM t1) sub; +DROP TABLE t1; + +SET optimizer_switch=@save_optimizer_switch; diff --git a/mysql-test/t/func_crypt.test b/mysql-test/t/func_crypt.test index ca6e712f45c..785cc63d3f1 100644 --- a/mysql-test/t/func_crypt.test +++ b/mysql-test/t/func_crypt.test @@ -70,3 +70,27 @@ SELECT OLD_PASSWORD(c1), PASSWORD(c1) FROM t1; DROP TABLE t1; --echo End of 5.0 tests + + +--echo # +--echo # Start of 10.0 tests +--echo # + +--echo # +--echo # MDEV-10306 Wrong results with combination of CONCAT, SUBSTR and CONVERT in subquery +--echo # + +SET @save_optimizer_switch=@@optimizer_switch; +SET optimizer_switch='derived_merge=on'; +# ENCRYPT() is not affected by MDEV-10306 +# It already uses tmp_value only for internal purposes and +# returns the result in the String passed to val_str() +CREATE TABLE t1 (t VARCHAR(32) CHARSET latin1); +INSERT INTO t1 VALUES('abcdefghi'); +SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT ENCRYPT(t,'aa') t2 FROM t1) sub; +DROP TABLE t1; +SET optimizer_switch=@save_optimizer_switch; + +--echo # +--echo # End of 10.0 tests +--echo # diff --git a/mysql-test/t/func_regexp_pcre.test b/mysql-test/t/func_regexp_pcre.test index 8e3adad0037..07de4b33271 100644 --- a/mysql-test/t/func_regexp_pcre.test +++ b/mysql-test/t/func_regexp_pcre.test @@ -430,3 +430,22 @@ SELECT CAST(0xE001 AS BINARY) REGEXP @regCheck; --echo # MDEV-12420: Testing recursion overflow --replace_regex /[0-9]+ exceeded/NUM exceeded/ SELECT 1 FROM dual WHERE ('Alpha,Bravo,Charlie,Delta,Echo,Foxtrot,StrataCentral,Golf,Hotel,India,Juliet,Kilo,Lima,Mike,StrataL3,November,Oscar,StrataL2,Sand,P3,P4SwitchTest,Arsys,Poppa,ExtensionMgr,Arp,Quebec,Romeo,StrataApiV2,PtReyes,Sierra,SandAcl,Arrow,Artools,BridgeTest,Tango,SandT,PAlaska,Namespace,Agent,Qos,PatchPanel,ProjectReport,Ark,Gimp,Agent,SliceAgent,Arnet,Bgp,Ale,Tommy,Central,AsicPktTestLib,Hsc,SandL3,Abuild,Pca9555,Standby,ControllerDut,CalSys,SandLib,Sb820,PointV2,BfnLib,Evpn,BfnSdk,Sflow,ManagementActive,AutoTest,GatedTest,Bgp,Sand,xinetd,BfnAgentLib,bf-utils,Hello,BfnState,Eos,Artest,Qos,Scd,ThermoMgr,Uniform,EosUtils,Eb,FanController,Central,BfnL3,BfnL2,tcp_wrappers,Victor,Environment,Route,Failover,Whiskey,Xray,Gimp,BfnFixed,Strata,SoCal,XApi,Msrp,XpProfile,tcpdump,PatchPanel,ArosTest,FhTest,Arbus,XpAcl,MacConc,XpApi,telnet,QosTest,Alpha2,BfnVlan,Stp,VxlanControllerTest,MplsAgent,Bravo2,Lanz,BfnMbb,Intf,XCtrl,Unicast,SandTunnel,L3Unicast,Ipsec,MplsTest,Rsvp,EthIntf,StageMgr,Sol,MplsUtils,Nat,Ira,P4NamespaceDut,Counters,Charlie2,Aqlc,Mlag,Power,OpenFlow,Lag,RestApi,BfdTest,strongs,Sfa,CEosUtils,Adt746,MaintenanceMode,MlagDut,EosImage,IpEth,MultiProtocol,Launcher,Max3179,Snmp,Acl,IpEthTest,PhyEee,bf-syslibs,tacc,XpL2,p4-ar-switch,p4-bf-switch,LdpTest,BfnPhy,Mirroring,Phy6,Ptp' REGEXP '^((?!\b(Strata|StrataApi|StrataApiV2)\b).)*$'); + +# +# MDEV-13173 An RLIKE that previously worked on 10.0 now returns "Got error 'pcre_exec: recursion limit of 100 exceeded' from regexp" +# +SELECT CONCAT(REPEAT('100,',400),'101') RLIKE '^(([1-9][0-9]*),)*[1-9][0-9]*$'; +--replace_regex /[0-9]+ exceeded/NUM exceeded/ +SELECT CONCAT(REPEAT('100,',600),'101') RLIKE '^(([1-9][0-9]*),)*[1-9][0-9]*$'; + +SELECT REGEXP_INSTR(CONCAT(REPEAT('100,',400),'101'), '^(([1-9][0-9]*),)*[1-9][0-9]*$'); +--replace_regex /[0-9]+ exceeded/NUM exceeded/ +SELECT REGEXP_INSTR(CONCAT(REPEAT('100,',600),'101'), '^(([1-9][0-9]*),)*[1-9][0-9]*$'); + +SELECT LENGTH(REGEXP_SUBSTR(CONCAT(REPEAT('100,',400/3),'101'), '^(([1-9][0-9]*),)*[1-9][0-9]*$')); +--replace_regex /[0-9]+ exceeded/NUM exceeded/ +SELECT LENGTH(REGEXP_SUBSTR(CONCAT(REPEAT('100,',600/3),'101'), '^(([1-9][0-9]*),)*[1-9][0-9]*$')); + +SELECT LENGTH(REGEXP_REPLACE(CONCAT(REPEAT('100,',400/3),'101'), '^(([1-9][0-9]*),)*[1-9][0-9]*$', '')); +--replace_regex /[0-9]+ exceeded/NUM exceeded/ +SELECT LENGTH(REGEXP_REPLACE(CONCAT(REPEAT('100,',600/3),'101'), '^(([1-9][0-9]*),)*[1-9][0-9]*$', '')); diff --git a/mysql-test/t/gis-alter_table_online.test b/mysql-test/t/gis-alter_table_online.test new file mode 100644 index 00000000000..be6d2137e77 --- /dev/null +++ b/mysql-test/t/gis-alter_table_online.test @@ -0,0 +1,82 @@ +--source include/have_innodb.inc +--source include/have_geometry.inc + +--echo # +--echo # Start of 10.1 tests +--echo # + +--echo # +--echo # MDEV-8948 ALTER ... INPLACE does work for BINARY, BLOB +--echo # + +# +# Blob variants to GEOMETRY +# +CREATE TABLE t1 (a TINYBLOB); +--error ER_ALTER_OPERATION_NOT_SUPPORTED +ALTER TABLE t1 MODIFY a GEOMETRY, ALGORITHM=INPLACE; +DROP TABLE t1; + +CREATE TABLE t1 (a MEDIUMBLOB); +--error ER_ALTER_OPERATION_NOT_SUPPORTED +ALTER TABLE t1 MODIFY a GEOMETRY, ALGORITHM=INPLACE; +DROP TABLE t1; + +CREATE TABLE t1 (a BLOB); +--error ER_ALTER_OPERATION_NOT_SUPPORTED +ALTER TABLE t1 MODIFY a GEOMETRY, ALGORITHM=INPLACE; +DROP TABLE t1; + +CREATE TABLE t1 (a LONGBLOB); +--error ER_ALTER_OPERATION_NOT_SUPPORTED +ALTER TABLE t1 MODIFY a GEOMETRY, ALGORITHM=INPLACE; +DROP TABLE t1; + +# +# GEOMETRY to BLOB variants +# + +CREATE TABLE t1 (a GEOMETRY); +--error ER_ALTER_OPERATION_NOT_SUPPORTED +ALTER TABLE t1 MODIFY a TINYBLOB, ALGORITHM=INPLACE; +DROP TABLE t1; + +CREATE TABLE t1 (a GEOMETRY); +--error ER_ALTER_OPERATION_NOT_SUPPORTED +ALTER TABLE t1 MODIFY a MEDIUMBLOB, ALGORITHM=INPLACE; +DROP TABLE t1; + +CREATE TABLE t1 (a GEOMETRY); +--error ER_ALTER_OPERATION_NOT_SUPPORTED +ALTER TABLE t1 MODIFY a BLOB, ALGORITHM=INPLACE; +DROP TABLE t1; + +CREATE TABLE t1 (a GEOMETRY); +--error ER_ALTER_OPERATION_NOT_SUPPORTED +ALTER TABLE t1 MODIFY a LONGBLOB, ALGORITHM=INPLACE; +DROP TABLE t1; + +# +# Different GEOMETRY types +# + +# Can't do INPLACE from a supertype to a subtype +CREATE TABLE t1 (a GEOMETRY); +--error ER_ALTER_OPERATION_NOT_SUPPORTED +ALTER TABLE t1 MODIFY a POLYGON, ALGORITHM=INPLACE; +DROP TABLE t1; + +# Ok to do INPLACE from a subtype to a supertype +CREATE TABLE t1 (a POLYGON); +ALTER TABLE t1 MODIFY a GEOMETRY, ALGORITHM=INPLACE; +DROP TABLE t1; + +# Ok to do INPLACE for two equal geometry subtypes +CREATE TABLE t1 (a POLYGON); +ALTER TABLE t1 MODIFY a POLYGON, ALGORITHM=INPLACE; +DROP TABLE t1; + + +--echo # +--echo # End of 10.1 tests +--echo # diff --git a/mysql-test/t/gis-rt-precise.test b/mysql-test/t/gis-rt-precise.test index 4cae10a9076..9c26aa05598 100644 --- a/mysql-test/t/gis-rt-precise.test +++ b/mysql-test/t/gis-rt-precise.test @@ -62,3 +62,25 @@ SELECT fid, AsText(g) FROM t1 WHERE ST_Within(g, DROP TABLE t1; --echo End of 5.5 tests. + +# +# MDEV-12078 Using spatial index changes type from point to geometry. +# +CREATE TABLE t1 ( + coordinate point NOT NULL, + SPATIAL KEY coordinate (coordinate) +) ENGINE=Aria DEFAULT CHARSET=ascii PAGE_CHECKSUM=1; + +SHOW COLUMNS FROM t1; + +INSERT INTO t1 (coordinate) VALUES(ST_PointFromText("POINT(0 0)")); +INSERT INTO t1 (coordinate) VALUES(ST_PointFromText("POINT(10 0)")); +INSERT INTO t1 (coordinate) VALUES(ST_PointFromText("POINT(10 10)")); +INSERT INTO t1 (coordinate) VALUES(ST_PointFromText("POINT(0 10)")); +INSERT INTO t1 (coordinate) VALUES(ST_PointFromText("POINT(5 5)")); + +SELECT astext(coordinate) FROM t1 WHERE ST_Intersects(ST_LineFromText("LINESTRING(0 0, 10 0, 10 10, 0 10)"), coordinate); + +SHOW COLUMNS FROM t1; + +DROP TABLE t1; diff --git a/mysql-test/t/gis.test b/mysql-test/t/gis.test index f689902533a..4192a3284b7 100644 --- a/mysql-test/t/gis.test +++ b/mysql-test/t/gis.test @@ -356,15 +356,15 @@ insert into t1 values ('85984',GeomFromText('MULTIPOLYGON(((-115.006363 36.248666,-115.263639 36.247466,-115.263839 36.252766,-115.261439 36.252666,-115.261439 36.247366,-115.247239 36.247066)))')); -# Expected result is 115.31877315203187, but IA64 returns 115.31877315203188 -# due to fused multiply-add instructions. ---replace_result 115.31877315203188 115.31877315203187 +# Expected results are 115.2970604672862 and 36.23335610879993, but IA64 returns +# slightly different values due to fused multiply-add instructions. +--replace_result 115.29706047613604 115.2970604672862 36.23335611157958 36.23335610879993 select object_id, geometrytype(geo), ISSIMPLE(GEO), ASTEXT(centroid(geo)) from t1 where object_id=85998; -# Expected result is 36.3310176346905, but IA64 returns 36.3310176346904 +# Expected result is 36.34725218253213, but IA64 returns 36.34725217627852 # due to fused multiply-add instructions. ---replace_result 36.3310176346904 36.3310176346905 -114.87787186923326 -114.87787186923313 36.33101763469053 36.33101763469059 36.33101763469043 36.33101763469059 +--replace_result 36.34725217627852 36.34725218253213 -114.86854470090232 -114.86854472054372 select object_id, geometrytype(geo), ISSIMPLE(GEO), ASTEXT(centroid(geo)) from t1 where object_id=85984; @@ -1503,5 +1503,20 @@ DROP VIEW v1; SELECT AsText(g) FROM (SELECT NULL AS g UNION SELECT Point(1,1)) AS t1; --echo # +--echo # MDEV-10306 Wrong results with combination of CONCAT, SUBSTR and CONVERT in subquery +--echo # + +SET @save_optimizer_switch=@@optimizer_switch; +SET optimizer_switch='derived_merge=on'; +CREATE TABLE t1 (x INT, y INT); +INSERT INTO t1 VALUES(0,0); +SELECT LENGTH(t2) c2 FROM (SELECT ST_BUFFER(POINT(x,y), 0) t2 FROM t1) sub; +SELECT LENGTH(CONCAT(t2,'-',t2)) c2 FROM (SELECT ST_BUFFER(POINT(x,y), 0) t2 FROM t1) sub; +SELECT LENGTH(CONCAT(t2,'--',t2)) c2 FROM (SELECT ST_BUFFER(POINT(x,y), 0) t2 FROM t1) sub; +DROP TABLE t1; +SET optimizer_switch=@save_optimizer_switch; + + +--echo # --echo # End 10.0 tests --echo # diff --git a/mysql-test/t/group_by.test b/mysql-test/t/group_by.test index 8ea2f923eb1..da8d3016942 100644 --- a/mysql-test/t/group_by.test +++ b/mysql-test/t/group_by.test @@ -1702,6 +1702,44 @@ select a as x from t1 group by x having x > 1; select a from t1 group by a having a > 1; drop table t1; set sql_mode= @save_sql_mode; + +# +# MDEV-7826 Server crashes in Item_subselect::enumerate_field_refs_processor +# +create table t1 (f1 int); +insert into t1 values (5),(9); +create table t2 (f2 int); +insert into t2 values (0),(6); +create table t3 (f3 int); +insert into t3 values (6),(3); +create table t4 (f4 int); +insert into t4 values (1),(0); +--error ER_ILLEGAL_REFERENCE +select +(select min(f1) from t1 where f1 in (select min(f4) from t2)) as field7, +(select count(*) from t3 where f3 in (select max(f4) from t2 group by field7)) +from t4; +drop table t1, t2, t3, t4; + +# +# MDEV-13180 Unused left join causes server crash +# +create table t1 (i1 int); +insert into t1 values (1); +create table t2 (i int); +insert into t2 values (2); +select 1 from t1 left join t2 b on b.i = (select max(b.i) from t2); +drop table t1, t2; + + +# +# MDEV-12489 The select stmt may fail due to "having clause is ambiguous" unexpected +# +create table t1 (c1 int, c2 int); +create table t2 (c1 int, c2 int); +select t1.c1 as c1, t2.c2 as c1 from t1, t2 where t1.c1 < 20 and t2.c2 > 5 group by t1.c1, t2.c2 having t1.c1 < 3; +drop table t1, t2; + # # End of MariaDB 5.5 tests # diff --git a/mysql-test/t/join_outer.test b/mysql-test/t/join_outer.test index 19c5f64de78..9645d8ad82a 100644 --- a/mysql-test/t/join_outer.test +++ b/mysql-test/t/join_outer.test @@ -1801,4 +1801,86 @@ SELECT * FROM t1 INNER JOIN t2 ON c = b LEFT JOIN t3 ON d = a DROP TABLE t1,t2,t3; +--echo # +--echo # MDEV-11958: LEFT JOIN with stored routine produces incorrect result +--echo # + +CREATE TABLE t (x INT); +INSERT INTO t VALUES(1),(NULL); +CREATE FUNCTION f (val INT, ret INT) RETURNS INT DETERMINISTIC RETURN IFNULL(val, ret); + +SELECT t1.x, t2.x, IFNULL(t2.x,0), f(t2.x,0) + FROM t t1 LEFT JOIN t t2 + ON t1.x = t2.x + WHERE IFNULL(t2.x,0)=0; +explain extended +SELECT t1.x, t2.x, IFNULL(t2.x,0), f(t2.x,0) + FROM t t1 LEFT JOIN t t2 + ON t1.x = t2.x + WHERE IFNULL(t2.x,0)=0; +SELECT t1.x, t2.x, IFNULL(t2.x,0), f(t2.x,0) + FROM t t1 LEFT JOIN t t2 + ON t1.x = t2.x + WHERE f(t2.x,0)=0; +explain extended +SELECT t1.x, t2.x, IFNULL(t2.x,0), f(t2.x,0) + FROM t t1 LEFT JOIN t t2 + ON t1.x = t2.x + WHERE f(t2.x,0)=0; + +drop function f; +drop table t; +CREATE TABLE t1 ( + col1 DECIMAL(33,5) NULL DEFAULT NULL, + col2 DECIMAL(33,5) NULL DEFAULT NULL +); + +CREATE TABLE t2 ( + col1 DECIMAL(33,5) NULL DEFAULT NULL, + col2 DECIMAL(33,5) NULL DEFAULT NULL, + col3 DECIMAL(33,5) NULL DEFAULT NULL +); + +INSERT INTO t1 VALUES (2, 1.1), (2, 2.1); +INSERT INTO t2 VALUES (3, 3.1, 4), (1, 1, NULL); + +DELIMITER |; + +CREATE FUNCTION f1 ( p_num DECIMAL(45,15), p_return DECIMAL(45,15)) +RETURNS decimal(33,5) +LANGUAGE SQL +DETERMINISTIC +CONTAINS SQL +SQL SECURITY INVOKER +BEGIN + IF p_num IS NULL THEN + RETURN p_return; + ELSE + RETURN p_num; + END IF; +END | + +DELIMITER ;| + +let $q1= +SELECT t1.col1, t2.col1, t2.col3 +FROM t1 LEFT OUTER JOIN t2 ON t1.col1 = t2.col2 +WHERE IFNULL(t2.col3,0) = 0; + +eval $q1; +eval EXPLAIN EXTENDED $q1; + +let $q2= +SELECT t1.col1, t2.col1, t2.col3 +FROM t1 LEFT OUTER JOIN t2 ON t1.col1 = t2.col2 +WHERE f1(t2.col3,0) = 0; +eval $q2; +eval EXPLAIN EXTENDED $q2; + +DROP FUNCTION f1; + +DROP TABLE t1,t2; + +--echo # end of 5.5 tests + SET optimizer_switch=@save_optimizer_switch; diff --git a/mysql-test/t/mysql.test b/mysql-test/t/mysql.test index 263e1103e8b..4403a9d668f 100644 --- a/mysql-test/t/mysql.test +++ b/mysql-test/t/mysql.test @@ -618,3 +618,33 @@ EOF --echo --echo End of tests + +# +# MDEV-13187 incorrect backslash parsing in clients +# +create table `a1\``b1` (a int); +show tables; +insert `a1\``b1` values (1),(2); +show create table `a1\``b1`; +--exec $MYSQL_DUMP --compact test +--exec $MYSQL_DUMP test > $MYSQLTEST_VARDIR/tmp/bug.sql +insert `a1\``b1` values (4),(5); +--exec $MYSQL test < $MYSQLTEST_VARDIR/tmp/bug.sql +show create table `a1\``b1`; +select * from `a1\``b1`; +drop table `a1\``b1`; + +# same with ansi_quotes +set sql_mode=ansi_quotes; +create table "a1\""b1" (a int); +show tables; +insert "a1\""b1" values (1),(2); +show create table "a1\""b1"; +--exec $MYSQL_DUMP --compact --compatible=postgres test +--exec $MYSQL_DUMP --compatible=postgres test > $MYSQLTEST_VARDIR/tmp/bug.sql +insert "a1\""b1" values (4),(5); +--exec $MYSQL test < $MYSQLTEST_VARDIR/tmp/bug.sql +show create table "a1\""b1"; +select * from "a1\""b1"; +drop table "a1\""b1"; +set sql_mode=default; diff --git a/mysql-test/t/mysql_upgrade.test b/mysql-test/t/mysql_upgrade.test index 2ef469ba7c5..f8026df8770 100644 --- a/mysql-test/t/mysql_upgrade.test +++ b/mysql-test/t/mysql_upgrade.test @@ -1,6 +1,7 @@ -- source include/mysql_upgrade_preparation.inc -- source include/have_working_dns.inc -- source include/have_innodb.inc +-- source include/have_partition.inc # # Basic test that we can run mysql_upgrde and that it finds the @@ -166,4 +167,26 @@ SELECT grantor FROM mysql.tables_priv WHERE db = 'mysql' AND table_name = 'user' DROP USER very_long_user_name_number_1, very_long_user_name_number_2, even_longer_user_name_number_3_to_test_the_grantor_and_definer_field_length@localhost; DROP PROCEDURE test.pr; ---echo End of tests +# +# MDEV-13274 mysql_upgrade fails if dbname+tablename+partioname > 64 chars +# +use test; +alter table mysql.innodb_table_stats modify last_update int not null; + +create table extralongname_extralongname_extralongname_extralongname_ext ( + id int(10) unsigned not null, + created_date date not null, + created timestamp not null, + primary key (created,id,created_date) +) engine=innodb stats_persistent=1 default charset=latin1 + partition by range (year(created_date)) + subpartition by hash (month(created_date)) + subpartitions 2 ( + partition p2007 values less than (2008), + partition p2008 values less than (2009) + ); +--exec $MYSQL_UPGRADE --skip-verbose --force 2>&1 +select length(table_name) from mysql.innodb_table_stats; +drop table extralongname_extralongname_extralongname_extralongname_ext; + +--echo End of 10.0 tests diff --git a/mysql-test/t/mysqltest.test b/mysql-test/t/mysqltest.test index e85d793b628..aea4ba6432d 100644 --- a/mysql-test/t/mysqltest.test +++ b/mysql-test/t/mysqltest.test @@ -2948,11 +2948,17 @@ disconnect $x; # Disconnect the selected connection disconnect $y; --echo $CURRENT_CONNECTION +connection default; +# +# MDEV-13187 incorrect backslash parsing in clients +# +set sql_mode=no_backslash_escapes; +select "foo\""bar"; +set sql_mode=default; --echo End of tests -connection default; # Wait till we reached the initial number of concurrent sessions --source include/wait_until_count_sessions.inc diff --git a/mysql-test/t/read_only.test b/mysql-test/t/read_only.test index 8ea0f2db9b7..7862580d3b0 100644 --- a/mysql-test/t/read_only.test +++ b/mysql-test/t/read_only.test @@ -80,6 +80,9 @@ insert into t3 values(1); insert into t4 select * from t3; +--error ER_OPTION_PREVENTS_STATEMENT +create table t3 (a int); + # a non-temp table updated: --error ER_OPTION_PREVENTS_STATEMENT update t1,t3 set t1.a=t3.a+1 where t1.a=t3.a; diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test index ab864803864..43373248834 100644 --- a/mysql-test/t/subselect.test +++ b/mysql-test/t/subselect.test @@ -6002,11 +6002,17 @@ INSERT INTO t2 VALUES (3); CREATE ALGORITHM=MERGE VIEW v2 AS SELECT * FROM t2; +--error ER_WRONG_GROUP_FIELD +SELECT ( SELECT MIN(t2.f2) FROM t1 ) AS sq FROM t2 GROUP BY sq; +--error ER_WRONG_GROUP_FIELD SELECT ( SELECT MIN(v2.f2) FROM t1 ) AS sq FROM v2 GROUP BY sq; SELECT * FROM v2 where ( SELECT MIN(v2.f2) FROM t1 ) > 0; SELECT count(*) FROM v2 group by ( SELECT MIN(v2.f2) FROM t1 ); delete from t1; +--error ER_WRONG_GROUP_FIELD +SELECT ( SELECT MIN(t2.f2) FROM t1 ) AS sq FROM t2 GROUP BY sq; +--error ER_WRONG_GROUP_FIELD SELECT ( SELECT MIN(v2.f2) FROM t1 ) AS sq FROM v2 GROUP BY sq; drop view v2; @@ -6021,3 +6027,45 @@ INSERT INTO t1 VALUES ('foo','bar'); SELECT * FROM t1 WHERE f2 >= SOME ( SELECT f1 FROM t1 ); SELECT * FROM t1 WHERE f2 <= SOME ( SELECT f1 FROM t1 ); DROP TABLE t1; + +--echo # +--echo # MDEV-10146: Wrong result (or questionable result and behavior) +--echo # with aggregate function in uncorrelated SELECT subquery +--echo # +CREATE TABLE t1 (f1 INT); +CREATE VIEW v1 AS SELECT * FROM t1; +INSERT INTO t1 VALUES (1),(2); + +CREATE TABLE t2 (f2 int); + +INSERT INTO t2 VALUES (3); +SELECT ( SELECT MAX(f1) FROM t2 ) FROM t1; + +SELECT ( SELECT MAX(f1) FROM t2 ) FROM v1; + +INSERT INTO t2 VALUES (4); + +--error ER_SUBQUERY_NO_1_ROW +SELECT ( SELECT MAX(f1) FROM t2 ) FROM v1; +--error ER_SUBQUERY_NO_1_ROW +SELECT ( SELECT MAX(f1) FROM t2 ) FROM t1; + +drop view v1; +drop table t1,t2; + +# +# MDEV-7828 Assertion `key_read == 0' failed in TABLE::enable_keyread with SELECT SQ and WHERE SQ +# +CREATE TABLE t1 (f1 INT, KEY(f1)) ENGINE=MyISAM; +INSERT t1 VALUES (4),(8); +CREATE TABLE t2 (f2 INT, KEY(f2)) ENGINE=MyISAM; +INSERT t2 VALUES (6); +SELECT (SELECT MAX(sq.f2) FROM t1) FROM (SELECT * FROM t2) AS sq WHERE f2 = 2; +--echo # +--echo # Disable this query till MDEV-13399 is resolved +--echo # +--echo # INSERT t2 VALUES (9); +--echo # --error ER_SUBQUERY_NO_1_ROW +--echo # SELECT (SELECT MAX(sq.f2) FROM t1) FROM (SELECT * FROM t2) AS sq WHERE f2 = 2; +--echo # +drop table t1, t2; diff --git a/mysql-test/t/subselect_mat_cost_bugs.test b/mysql-test/t/subselect_mat_cost_bugs.test index 9e3ac603ec6..35f2b9588fe 100644 --- a/mysql-test/t/subselect_mat_cost_bugs.test +++ b/mysql-test/t/subselect_mat_cost_bugs.test @@ -507,3 +507,19 @@ SELECT * FROM t1 ON (f3 = t2b.f2) ); DROP TABLE t1,t2,t3; + +--echo # +--echo # MDEV-12963: min/max optimization optimizing away all tables employed +--echo # for uncorrelated IN subquery used in a disjunct of WHERE +--echo # + +create table t1 (a int, index idx(a)) engine=myisam; +insert into t1 values (4),(7),(1),(3),(9); + +select * from t1 where a in (select max(a) from t1 where a < 4) or a > 5; +explain +select * from t1 where a in (select max(a) from t1 where a < 4) or a > 5; + +drop table t1; + + diff --git a/mysql-test/t/subselect_nulls.test b/mysql-test/t/subselect_nulls.test index 4b08e773b17..3e7b2189ed5 100644 --- a/mysql-test/t/subselect_nulls.test +++ b/mysql-test/t/subselect_nulls.test @@ -97,3 +97,9 @@ set optimizer_switch= @tmp_subselect_nulls; drop table x1; drop table x2; + +# +# MDEV-7339 Server crashes in Item_func_trig_cond::val_int +# +select (select 1, 2) in (select 3, 4); +select (select NULL, NULL) in (select 3, 4); diff --git a/mysql-test/t/subselect_sj2_mat.test b/mysql-test/t/subselect_sj2_mat.test index 0f2892ae2dc..cfb6c8c2819 100644 --- a/mysql-test/t/subselect_sj2_mat.test +++ b/mysql-test/t/subselect_sj2_mat.test @@ -283,3 +283,23 @@ select * from t1 where (rand() < 0) and i in (select i from t2); drop table t1,t2; set optimizer_switch=@save_optimizer_switch; + +--echo # +--echo # mdev-12855: materialization of a semi-join subquery + ORDER BY +--echo # + +CREATE TABLE t1 (f1 varchar(8), KEY(f1)) ENGINE=InnoDB; +INSERT INTO t1 VALUES ('qux'),('foo'); +CREATE TABLE t2 (f2 varchar(8)) ENGINE=InnoDB; +INSERT INTO t2 VALUES ('bar'),('foo'),('qux'); + +let $q= +SELECT f1 FROM t1 +WHERE f1 IN ( SELECT f2 FROM t2 WHERE f2 > 'bar' ) +HAVING f1 != 'foo' +ORDER BY f1; + +eval $q; +eval explain $q; + +DROP TABLE t1,t2; diff --git a/mysql-test/t/subselect_sj_mat.test b/mysql-test/t/subselect_sj_mat.test index 7b6c5818a04..c626d88e6ff 100644 --- a/mysql-test/t/subselect_sj_mat.test +++ b/mysql-test/t/subselect_sj_mat.test @@ -1950,6 +1950,213 @@ set optimizer_switch= @save_optimizer_switch; DROP TABLE t1,t2; +--echo # +--echo # mdev-12838: scan of materialized of semi-join subquery in join +--echo # + +set @save_optimizer_switch=@@optimizer_switch; + +CREATE TABLE t1 ( + dispatch_group varchar(32), + assignment_group varchar(32), + sys_id char(32), + PRIMARY KEY (sys_id), + KEY idx1 (dispatch_group), + KEY idx2 (assignment_group) +) ENGINE=MyISAM; + +CREATE TABLE t2 ( + ugroup varchar(32), + user varchar(32), + sys_id char(32), + PRIMARY KEY (sys_id), + KEY idx3 (ugroup), + KEY idx4 (user) +) ENGINE=MyISAM; + +CREATE TABLE t3 ( + type mediumtext, + sys_id char(32), + PRIMARY KEY (sys_id) +) ENGINE=MyISAM; + +--disable_query_log + +INSERT INTO t1 VALUES +('e5d9f63237232000158bbfc8bcbe5dbf','f304ae0037332000158bbfc8bcbe5d4f', +'5398c0e037003000158bbfc8bcbe5dbb'), +('69d9f63237232000158bbfc8bcbe5dcb','7172ea0037332000158bbfc8bcbe5db6', +'5c188ca037003000158bbfc8bcbe5dbc'), +('577ed708d773020058c92cf65e61037a','699708d4d773020058c92cf65e61037c', +'623a8cd4d773020058c92cf65e6103ea'), +('96fb652637232000158bbfc8bcbe5db4','df50316637232000158bbfc8bcbe5d23', +'6835bd6637232000158bbfc8bcbe5d21'), +('e1d9f63237232000158bbfc8bcbe5db8','96346e0037332000158bbfc8bcbe5daa', +'697880e037003000158bbfc8bcbe5dcd'), +('25d9f63237232000158bbfc8bcbe5dbe','f304ae0037332000158bbfc8bcbe5d4f', +'6a9804e037003000158bbfc8bcbe5d09'), +('96fb652637232000158bbfc8bcbe5db4','e08fad2637232000158bbfc8bcbe5d39', +'6d25f96637232000158bbfc8bcbe5d79'), +('e9d9f63237232000158bbfc8bcbe5dc6','7172ea0037332000158bbfc8bcbe5db6', +'702880e037003000158bbfc8bcbe5d94'), +('a5d9f63237232000158bbfc8bcbe5dca','f304ae0037332000158bbfc8bcbe5d4f', +'7188c0e037003000158bbfc8bcbe5d75'), +('65d9f63237232000158bbfc8bcbe5dc4','f304ae0037332000158bbfc8bcbe5d4f', +'778880e037003000158bbfc8bcbe5d9e'), +('a1d9f63237232000158bbfc8bcbe5dc3','7172ea0037332000158bbfc8bcbe5db6', +'7d0840e037003000158bbfc8bcbe5dde'), +('21d9f63237232000158bbfc8bcbe5db7','96346e0037332000158bbfc8bcbe5daa', +'7f6880e037003000158bbfc8bcbe5da7'), +('96fb652637232000158bbfc8bcbe5db4','ec70316637232000158bbfc8bcbe5d60', +'8025f96637232000158bbfc8bcbe5dd0'), +('3dd9f63237232000158bbfc8bcbe5dcc','7172ea0037332000158bbfc8bcbe5db6', +'823880e037003000158bbfc8bcbe5ded'), +('96fb652637232000158bbfc8bcbe5db4','7b10fd2637232000158bbfc8bcbe5d30', +'9a353d6637232000158bbfc8bcbe5dee'), +('75d9f63237232000158bbfc8bcbe5dd0','ebb4620037332000158bbfc8bcbe5d89', +'a558c0e037003000158bbfc8bcbe5d36'), +('6dd9f63237232000158bbfc8bcbe5db5','96346e0037332000158bbfc8bcbe5daa', +'bc78cca037003000158bbfc8bcbe5d74'), +('add9f63237232000158bbfc8bcbe5dc7','7172ea0037332000158bbfc8bcbe5db6', +'c53804a037003000158bbfc8bcbe5db8'), +('fdd9f63237232000158bbfc8bcbe5dcd','7864ae0037332000158bbfc8bcbe5db8', +'cfe740e037003000158bbfc8bcbe5de8'), +('96fb652637232000158bbfc8bcbe5db4','3120fd2637232000158bbfc8bcbe5d42', +'e2257d6637232000158bbfc8bcbe5ded'), +('3c3725e237232000158bbfc8bcbe5da1','96346e0037332000158bbfc8bcbe5daa', +'ee78c0e037003000158bbfc8bcbe5db5'), +('a9d9f63237232000158bbfc8bcbe5dc0','7172ea0037332000158bbfc8bcbe5db6', +'f00888a037003000158bbfc8bcbe5dd3'), +('29d9f63237232000158bbfc8bcbe5db9','7172ea0037332000158bbfc8bcbe5db6', +'fa0880e037003000158bbfc8bcbe5d70'), +('b1d9f63237232000158bbfc8bcbe5dcf','ebb4620037332000158bbfc8bcbe5d89', +'fa48c0e037003000158bbfc8bcbe5d28'); + +INSERT INTO t2 VALUES +('17801ac21b13200050fdfbcd2c0713e8','8e826bf03710200044e0bfc8bcbe5d86', +'14c19a061b13200050fdfbcd2c07134b'), +('577ed708d773020058c92cf65e61037a','931644d4d773020058c92cf65e61034c', +'339888d4d773020058c92cf65e6103aa'), +('df50316637232000158bbfc8bcbe5d23','92826bf03710200044e0bfc8bcbe5da9', +'3682f56637232000158bbfc8bcbe5d44'), +('b4f342b237232000158bbfc8bcbe5def','86826bf03710200044e0bfc8bcbe5d70', +'38e4c2b237232000158bbfc8bcbe5dea'), +('7b10fd2637232000158bbfc8bcbe5d30','8a826bf03710200044e0bfc8bcbe5d72', +'4442b56637232000158bbfc8bcbe5d43'), +('3120fd2637232000158bbfc8bcbe5d42','82826bf03710200044e0bfc8bcbe5d89', +'49d2396637232000158bbfc8bcbe5d12'), +('96fb652637232000158bbfc8bcbe5db4','86826bf03710200044e0bfc8bcbe5d79', +'4e3ca52637232000158bbfc8bcbe5d3e'), +('17801ac21b13200050fdfbcd2c0713e8','824fd523bf4320007a6d257b3f073963', +'58c19a061b13200050fdfbcd2c07134e'), +('699708d4d773020058c92cf65e61037c','901784d4d773020058c92cf65e6103da', +'5bc708d4d773020058c92cf65e6103d5'), +('75d9f63237232000158bbfc8bcbe5dd0','86826bf03710200044e0bfc8bcbe5d79', +'6b52cb7237232000158bbfc8bcbe5ded'), +('f253da061b13200050fdfbcd2c0713ab','8e826bf03710200044e0bfc8bcbe5d86', +'81045e061b13200050fdfbcd2c071373'), +('7b10fd2637232000158bbfc8bcbe5d30','8e826bf03710200044e0bfc8bcbe5d74', +'8c42b56637232000158bbfc8bcbe5d3f'), +('e5d9f63237232000158bbfc8bcbe5dbf','7a826bf03710200044e0bfc8bcbe5df5', +'a7acfe3237232000158bbfc8bcbe5d78'), +('8a5055c9c61122780043563ef53438e3','9ee1b13dc6112271007f9d0efdb69cd0', +'a9aff553c6112276015a8006174bee21'), +('8a4dde73c6112278017a6a4baf547aa7','9ee1b13dc6112271007f9d0efdb69cd0', +'a9b2f526c61122760003ae07349d294f'), +('aaccc971c0a8001500fe1ff4302de101','9ee1b13dc6112271007f9d0efdb69cd0', +'aacceed3c0a80015009069bba51c4e21'), +('65d9f63237232000158bbfc8bcbe5dc4','8d56406a0a0a0a6b004070b354aada28', +'ac1bfa3237232000158bbfc8bcbe5dc3'), +('b85d44954a3623120004689b2d5dd60a','97000fcc0a0a0a6e0104ca999f619e5b', +'b77bc032cbb00200d71cb9c0c24c9c45'), +('220f8e71c61122840197e57c33464f70','8d56406a0a0a0a6b004070b354aada28', +'b9b74f080a0a0b343ba75b95bdb27056'), +('e08fad2637232000158bbfc8bcbe5d39','82826bf03710200044e0bfc8bcbe5d80', +'be02756637232000158bbfc8bcbe5d8b'), +('ebb4620037332000158bbfc8bcbe5d89','7682abf03710200044e0bfc8bcbe5d25', +'c0122f4437732000158bbfc8bcbe5d7d'), +('96fb652637232000158bbfc8bcbe5db4','7a82abf03710200044e0bfc8bcbe5d27', +'c23ca52637232000158bbfc8bcbe5d3b'), +('22122b37c611228400f9ff91c857581d','9ee1b13dc6112271007f9d0efdb69cd0', +'d23bbf5dac14641866947512bde59dc5'), +('db53a9290a0a0a650091abebccf833c6','9ee1b13dc6112271007f9d0efdb69cd0', +'db54a0f60a0a0a65002c54dcb72b4f41'), +('e08fad2637232000158bbfc8bcbe5d39','8e826bf03710200044e0bfc8bcbe5d86', +'f602756637232000158bbfc8bcbe5d88'), +('699708d4d773020058c92cf65e61037c','8d59d601d7b3020058c92cf65e6103c2', +'f718a241d7b3020058c92cf65e610332'), +('df50316637232000158bbfc8bcbe5d23','9e826bf03710200044e0bfc8bcbe5da6', +'fe82f56637232000158bbfc8bcbe5d4e'), +('f972d6061b13200050fdfbcd2c0713e5','780395f0df031100a9e78b6c3df2631f', +'ff4395f0df031100a9e78b6c3df2637e'); + +INSERT INTO t3 VALUES +('87245e061b13200050fdfbcd2c0713cc','7172ea0037332000158bbfc8bcbe5db6'), +('74af88c6c611227d0066386e74dc853d','74ad1ff3c611227d01d25feac2af603f'), +('59e22fb137032000158bbfc8bcbe5d52','75d9f63237232000158bbfc8bcbe5dd0'), +('98906fb137032000158bbfc8bcbe5d65','781da52637232000158bbfc8bcbe5db8'), +('87245e061b13200050fdfbcd2c0713cc','7864ae0037332000158bbfc8bcbe5db8'), +('87245e061b13200050fdfbcd2c0713cc','7b10fd2637232000158bbfc8bcbe5d30'), +('59e22fb137032000158bbfc8bcbe5d52','81a880e037003000158bbfc8bcbe5df8'), +('74af88c6c611227d0066386e74dc853d','8a4cb6d4c61122780043b1642efcd52b'), +('1cb8ab9bff500200158bffffffffff62','8a4dde73c6112278017a6a4baf547aa7'), +('1cb8ab9bff500200158bffffffffff62','8a5055c9c61122780043563ef53438e3'), +('87245e061b13200050fdfbcd2c0713cc','96346e0037332000158bbfc8bcbe5daa'), +('59e22fb137032000158bbfc8bcbe5d52','96fb652637232000158bbfc8bcbe5db4'), +('59e22fb137032000158bbfc8bcbe5d52','a1d9f63237232000158bbfc8bcbe5dc3'), +('59e22fb137032000158bbfc8bcbe5d52','a5d9f63237232000158bbfc8bcbe5dca'), +('1cb8ab9bff500200158bffffffffff62','a715cd759f2002002920bde8132e7018'), +('59e22fb137032000158bbfc8bcbe5d52','a9d9f63237232000158bbfc8bcbe5dc0'), +('74af88c6c611227d0066386e74dc853d','aacb62e2c0a80015007f67f752c2b12c'), +('74af88c6c611227d0066386e74dc853d','aaccc971c0a8001500fe1ff4302de101'), +('59e22fb137032000158bbfc8bcbe5d52','add9f63237232000158bbfc8bcbe5dbb'), +('59e22fb137032000158bbfc8bcbe5d52','add9f63237232000158bbfc8bcbe5dc7'), +('59e22fb137032000158bbfc8bcbe5d52','b1d9f63237232000158bbfc8bcbe5dcf'), +('1cb8ab9bff500200158bffffffffff62','b85d44954a3623120004689b2d5dd60a'), +('1cb8ab9bff500200158bffffffffff62','b97e89b94a36231201676b73322a0311'), +('1cb8ab9bff500200158bffffffffff62','cfcbad03d711110050f5edcb9e61038f'), +('1cb8ab9bff500200158bffffffffff62','d625dccec0a8016700a222a0f7900d06'), +('1cb8ab9bff500200158bffffffffff62','db53580b0a0a0a6501aa37c294a2ba6b'), +('1cb8ab9bff500200158bffffffffff62','db53a9290a0a0a650091abebccf833c6'), +('1cb8ab9bff500200158bffffffffff62','dc0db135c332010016194ffe5bba8f23'), +('87245e061b13200050fdfbcd2c0713cc','df50316637232000158bbfc8bcbe5d23'), +('87245e061b13200050fdfbcd2c0713cc','e08fad2637232000158bbfc8bcbe5d39'), +('59e22fb137032000158bbfc8bcbe5d52','e1d9f63237232000158bbfc8bcbe5db8'), +('59e22fb137032000158bbfc8bcbe5d52','e5d9f63237232000158bbfc8bcbe5db4'), +('59e22fb137032000158bbfc8bcbe5d52','e5d9f63237232000158bbfc8bcbe5dbf'), +('59e22fb137032000158bbfc8bcbe5d52','e9d9f63237232000158bbfc8bcbe5dba'), +('59e22fb137032000158bbfc8bcbe5d52','e9d9f63237232000158bbfc8bcbe5dc6'), +('87245e061b13200050fdfbcd2c0713cc','ebb4620037332000158bbfc8bcbe5d89'), +('87245e061b13200050fdfbcd2c0713cc','ec70316637232000158bbfc8bcbe5d60'), +('87245e061b13200050fdfbcd2c0713cc','f253da061b13200050fdfbcd2c0713ab'), +('87245e061b13200050fdfbcd2c0713cc','f304ae0037332000158bbfc8bcbe5d4f'), +('98906fb137032000158bbfc8bcbe5d65','f972d6061b13200050fdfbcd2c0713e5'), +('59e22fb137032000158bbfc8bcbe5d52','fdd9f63237232000158bbfc8bcbe5dcd'); + +--enable_query_log + +let $q= +SELECT t1.assignment_group +FROM t1, t3 +WHERE t1.assignment_group = t3.sys_id AND + t1.dispatch_group IN + (SELECT t2.ugroup + FROM t2, t3 t3_i + WHERE t2.ugroup = t3_i.sys_id AND + t3_i.type LIKE '59e22fb137032000158bbfc8bcbe5d52' AND + t2.user = '86826bf03710200044e0bfc8bcbe5d79'); + +set optimizer_switch='materialization=off'; +eval explain $q; +eval $q; + +set optimizer_switch='materialization=on'; +eval explain $q; +eval $q; + +DROP TABLE t1,t2,t3; +set optimizer_switch=@save_optimizer_switch; + --echo # End of 5.5 tests --echo # --echo # MDEV-7220: Materialization strategy is not used for REPLACE ... SELECT diff --git a/mysql-test/t/union.test b/mysql-test/t/union.test index f4dc6a5d449..4a3c19b49ab 100644 --- a/mysql-test/t/union.test +++ b/mysql-test/t/union.test @@ -1370,3 +1370,18 @@ order by d; drop table t1; --echo End of 5.0 tests + +# +# Bug #24595639: INCORRECT BEHAVIOR IN QUERY WITH UNION AND GROUP BY +# +create table t1 (a int, b int); +insert into t1 values (1,1),(2,2),(3,3); +create table t2 (c varchar(30), d varchar(30)); +insert into t1 values ('1','1'),('2','2'),('4','4'); +create table t3 (e int, f int); +insert into t3 values (1,1),(2,2),(31,31),(32,32); +select e,f, (e , f) in (select e,b from t1 union select c,d from t2) as sub from t3; +select avg(f), (e , f) in (select e,b from t1 union select c,d from t2) as sub from t3 group by sub; +drop table t1,t2,t3; + +--echo End of 5.5 tests diff --git a/mysql-test/t/view.test b/mysql-test/t/view.test index 89a8e0c9ffc..89f9c744a5f 100644 --- a/mysql-test/t/view.test +++ b/mysql-test/t/view.test @@ -5565,6 +5565,22 @@ select * drop view v1; drop table t1,t2,t3; +--echo # +--echo # MDEV-11240: Server crashes in check_view_single_update or +--echo # Assertion `derived->table' failed in mysql_derived_merge_for_insert +--echo # + +CREATE TABLE t3 (a INT); +CREATE ALGORITHM = MERGE VIEW v1 AS SELECT t2.a FROM t3 AS t1, t3 AS t2; +CREATE ALGORITHM = MERGE VIEW v2 AS SELECT * FROM v1; +PREPARE stmt FROM 'REPLACE INTO v2 SELECT a FROM t3'; +--error ER_VIEW_NO_INSERT_FIELD_LIST +EXECUTE stmt; +--error ER_VIEW_NO_INSERT_FIELD_LIST +EXECUTE stmt; +drop view v1,v2; +drop table t3; + --echo # ----------------------------------------------------------------- --echo # -- End of 5.5 tests. --echo # ----------------------------------------------------------------- @@ -5814,6 +5830,55 @@ SELECT * FROM v1 where use_case_id = 10; drop view v1; drop table t1; +--echo # +--echo # MDEV-12666: CURRENT_ROLE() and DATABASE() does not work in a view +--echo # +--echo # DATABASE() fails only when the initial view creation features a NULL +--echo # default database. +--echo # +--echo # CREATE, USE and DROP database so that we have no "default" database. +--echo # +CREATE DATABASE temporary; +USE temporary; +DROP DATABASE temporary; +SELECT DATABASE(); + +CREATE VIEW test.v_no_db AS SELECT DATABASE() = 'temporary_two'; +SHOW CREATE VIEW test.v_no_db; +PREPARE prepared_no_database FROM "SELECT DATABASE() = 'temporary_two'"; + +--echo # +--echo # All statements should return NULL +--echo # +EXECUTE prepared_no_database; +SELECT DATABASE() = 'temporary_two'; +SELECT * FROM test.v_no_db; + +CREATE DATABASE temporary_two; +USE temporary_two; +CREATE VIEW test.v_with_db AS SELECT DATABASE() = 'temporary_two'; +PREPARE prepared_with_database FROM "SELECT DATABASE() = 'temporary_two'"; + +--echo # +--echo # All statements should return 1; +--echo # +SELECT DATABASE() = 'temporary_two'; +SELECT * FROM test.v_no_db; +SELECT * FROM test.v_with_db; +EXECUTE prepared_with_database; + +--echo # +--echo # Prepared statements maintain default database to be the same +--echo # during on creation so this should return NULL still. +--echo # See MySQL bug #25843 +--echo # +EXECUTE prepared_no_database; + +DROP DATABASE temporary_two; +DROP VIEW test.v_no_db; +DROP VIEW test.v_with_db; +USE test; + --echo # ----------------------------------------------------------------- --echo # -- End of 10.0 tests. --echo # ----------------------------------------------------------------- |