diff options
Diffstat (limited to 'mysql-test/r')
24 files changed, 761 insertions, 29 deletions
diff --git a/mysql-test/r/create.result b/mysql-test/r/create.result index 088cd9e531d..372e2baa02b 100644 --- a/mysql-test/r/create.result +++ b/mysql-test/r/create.result @@ -2662,5 +2662,21 @@ Warnings: Note 1291 Column 'a' has duplicated value '' in ENUM drop table t1; set @@session.collation_server=default; +# +# MDEV-7765: Crash (Assertion `!table || (!table->write_set || +# bitmap_is_set(table->write_set, field_index) || +# bitmap_is_set(table->vcol_set, field_index))' fails) +# on using function over not created table +# +CREATE function f1() returns int +BEGIN +declare n int; +set n:= (select count(*) from t1); +return n; +end| +create table t1 as select f1(); +ERROR 42S02: Table 'test.t1' doesn't exist +drop function f1; +End of 5.5 tests create table t1; ERROR 42000: A table must have at least 1 column diff --git a/mysql-test/r/ctype_utf8.result b/mysql-test/r/ctype_utf8.result index 23278ea43ca..532c54dd79e 100644 --- a/mysql-test/r/ctype_utf8.result +++ b/mysql-test/r/ctype_utf8.result @@ -116,7 +116,7 @@ hex(a) STRCMP(a,'a') STRCMP(a,'a ') DROP TABLE t1; select insert('txs',2,1,'hi'),insert('is ',4,0,'a'),insert('txxxxt',2,4,'es'); insert('txs',2,1,'hi') insert('is ',4,0,'a') insert('txxxxt',2,4,'es') -this is a test +this is test select insert("aa",100,1,"b"),insert("aa",1,3,"b"); insert("aa",100,1,"b") insert("aa",1,3,"b") aa b @@ -5370,9 +5370,10 @@ SET sql_mode=default; SET NAMES utf8; CREATE TABLE t1 (a INT); INSERT INTO t1 VALUES (0), (0), (1), (0), (0); -SELECT COUNT(*) FROM t1, t1 t2 +SELECT COUNT(*) FROM t1, t1 t2 GROUP BY INSERT('', t2.a, t1.a, (@@global.max_binlog_size)); -ERROR 23000: Duplicate entry '107374182410737418241' for key 'group_key' +COUNT(*) +25 DROP TABLE t1; # # Bug#11764503 (Bug#57341) Query in EXPLAIN EXTENDED shows wrong characters diff --git a/mysql-test/r/ctype_utf8mb4.result b/mysql-test/r/ctype_utf8mb4.result index 8772eb941b4..d8e97371294 100644 --- a/mysql-test/r/ctype_utf8mb4.result +++ b/mysql-test/r/ctype_utf8mb4.result @@ -116,7 +116,7 @@ hex(a) STRCMP(a,'a') STRCMP(a,'a ') DROP TABLE t1; select insert('txs',2,1,'hi'),insert('is ',4,0,'a'),insert('txxxxt',2,4,'es'); insert('txs',2,1,'hi') insert('is ',4,0,'a') insert('txxxxt',2,4,'es') -this is a test +this is test select insert("aa",100,1,"b"),insert("aa",1,3,"b"); insert("aa",100,1,"b") insert("aa",1,3,"b") aa b diff --git a/mysql-test/r/ctype_utf8mb4_heap.result b/mysql-test/r/ctype_utf8mb4_heap.result index 57d29a24fd0..3f543ce73e1 100644 --- a/mysql-test/r/ctype_utf8mb4_heap.result +++ b/mysql-test/r/ctype_utf8mb4_heap.result @@ -116,7 +116,7 @@ hex(a) STRCMP(a,'a') STRCMP(a,'a ') DROP TABLE t1; select insert('txs',2,1,'hi'),insert('is ',4,0,'a'),insert('txxxxt',2,4,'es'); insert('txs',2,1,'hi') insert('is ',4,0,'a') insert('txxxxt',2,4,'es') -this is a test +this is test select insert("aa",100,1,"b"),insert("aa",1,3,"b"); insert("aa",100,1,"b") insert("aa",1,3,"b") aa b diff --git a/mysql-test/r/ctype_utf8mb4_innodb.result b/mysql-test/r/ctype_utf8mb4_innodb.result index ba03a3f66e6..cc0ded6728d 100644 --- a/mysql-test/r/ctype_utf8mb4_innodb.result +++ b/mysql-test/r/ctype_utf8mb4_innodb.result @@ -116,7 +116,7 @@ hex(a) STRCMP(a,'a') STRCMP(a,'a ') DROP TABLE t1; select insert('txs',2,1,'hi'),insert('is ',4,0,'a'),insert('txxxxt',2,4,'es'); insert('txs',2,1,'hi') insert('is ',4,0,'a') insert('txxxxt',2,4,'es') -this is a test +this is test select insert("aa",100,1,"b"),insert("aa",1,3,"b"); insert("aa",100,1,"b") insert("aa",1,3,"b") aa b diff --git a/mysql-test/r/ctype_utf8mb4_myisam.result b/mysql-test/r/ctype_utf8mb4_myisam.result index c4ff8e0a882..03e32836cb5 100644 --- a/mysql-test/r/ctype_utf8mb4_myisam.result +++ b/mysql-test/r/ctype_utf8mb4_myisam.result @@ -116,7 +116,7 @@ hex(a) STRCMP(a,'a') STRCMP(a,'a ') DROP TABLE t1; select insert('txs',2,1,'hi'),insert('is ',4,0,'a'),insert('txxxxt',2,4,'es'); insert('txs',2,1,'hi') insert('is ',4,0,'a') insert('txxxxt',2,4,'es') -this is a test +this is test select insert("aa",100,1,"b"),insert("aa",1,3,"b"); insert("aa",100,1,"b") insert("aa",1,3,"b") aa b diff --git a/mysql-test/r/derived.result b/mysql-test/r/derived.result index 2b0b1041936..97ba35ed9cd 100644 --- a/mysql-test/r/derived.result +++ b/mysql-test/r/derived.result @@ -604,4 +604,349 @@ select x.id, message from (select id from t1) x left join where coalesce(message,0) <> 0; id message drop table t1,t2; +# +# MDEV-7827: Assertion `!table || (!table->read_set || +# bitmap_is_set(table->read_set, field_index))' failed +# in Field_long::val_str on EXPLAIN EXTENDED +# +CREATE TABLE t1 (f1 INT, f2 INT, KEY(f2)) ENGINE=MyISAM; +INSERT INTO t1 VALUES (6,9); +CREATE TABLE t2 (f3 INT) ENGINE=MyISAM; +INSERT INTO t2 VALUES (2),(0); +EXPLAIN EXTENDED +SELECT f1 FROM ( SELECT * FROM t1 ) AS sq +WHERE f1 IN ( +SELECT f3 FROM t2 WHERE f2 IN ( +SELECT f3 FROM t2 HAVING f3 >= 8 +) +); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY <derived2> system NULL NULL NULL NULL 1 100.00 +1 PRIMARY <subquery4> eq_ref distinct_key distinct_key 4 sq.f2 1 100.00 +1 PRIMARY t2 ALL NULL NULL NULL NULL 2 100.00 Using where; FirstMatch(<subquery4>); Using join buffer (flat, BNL join) +4 MATERIALIZED t2 ALL NULL NULL NULL NULL 2 100.00 +2 DERIVED t1 system NULL NULL NULL NULL 1 100.00 +Warnings: +Note 1276 Field or reference 'sq.f2' of SELECT #3 was resolved in SELECT #1 +Note 1003 select 6 AS `f1` from <materialize> (select `test`.`t2`.`f3` from `test`.`t2` having (`test`.`t2`.`f3` >= 8)) semi join (`test`.`t2`) where ((`test`.`t2`.`f3` = 6) and (9 = `<subquery4>`.`f3`)) +DROP TABLE t2,t1; +# +# MDEV-9462: Out of memory using explain on 2 empty tables +# +CREATE TABLE `t1` ( +`REC_GROUP` char(2) DEFAULT NULL, +`CLIENT_INFO` text CHARACTER SET utf8, +`NAME` text, +`PHONE_NUMBER` text, +`ATTENTION_NAME` text, +`PAYMENT_TERM` text CHARACTER SET utf8, +`CREDIT_LIMIT` decimal(12,2) DEFAULT NULL, +`LAST_PAY_DATE` text CHARACTER SET utf8, +`TOTAL` double DEFAULT NULL, +`TOTAL_MCL` double DEFAULT NULL, +`TOTAL_MFS` double DEFAULT NULL, +`TOTAL_MIS` double DEFAULT NULL, +`BEFORE_DUE_7_MCL` double DEFAULT NULL, +`BEFORE_DUE_7_MFS` double DEFAULT NULL, +`BEFORE_DUE_7_MIS` double DEFAULT NULL, +`PER1_MCL` double DEFAULT NULL, +`PER1_MFS` double DEFAULT NULL, +`PER1_MIS` double DEFAULT NULL, +`PER2_MCL` double DEFAULT NULL, +`PER2_MFS` double DEFAULT NULL, +`PER2_MIS` double DEFAULT NULL, +`PER3_MCL` double DEFAULT NULL, +`PER3_MFS` double DEFAULT NULL, +`PER3_MIS` double DEFAULT NULL, +`PER4_MCL` double DEFAULT NULL, +`PER4_MFS` double DEFAULT NULL, +`PER4_MIS` double DEFAULT NULL, +`PER5_MCL` double DEFAULT NULL, +`PER5_MFS` double DEFAULT NULL, +`PER5_MIS` double DEFAULT NULL, +`PER6_MCL` double DEFAULT NULL, +`PER6_MFS` double DEFAULT NULL, +`PER6_MIS` double DEFAULT NULL, +`PER7_MCL` double DEFAULT NULL, +`PER7_MFS` double DEFAULT NULL, +`PER7_MIS` double DEFAULT NULL, +`BEFORE_DUE_7` double DEFAULT NULL, +`PER1` double DEFAULT NULL, +`PER2` double DEFAULT NULL, +`PER3` double DEFAULT NULL, +`PER4` double DEFAULT NULL, +`PER5` double DEFAULT NULL, +`PER6` double DEFAULT NULL, +`PER7` double DEFAULT NULL, +`REF` varchar(30) DEFAULT NULL, +`TYPE` varchar(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL +); +CREATE TABLE `t2` ( +`RECEIVABLE_GROUP` char(2) DEFAULT NULL, +`CLIENT_NUMBER` varchar(35) DEFAULT NULL, +`CLIENT_NAME` varchar(73) DEFAULT NULL, +`PHONE_NUMBER` char(12) DEFAULT NULL, +`ATTENTION_NAME` char(26) DEFAULT NULL, +`PAYMENT_TERM` varchar(26) CHARACTER SET utf8 DEFAULT NULL, +`CREDIT_LIMIT` decimal(12,2) DEFAULT NULL, +`LAST_PAY_DATE` varchar(42) CHARACTER SET utf8 DEFAULT NULL, +`TOTAL` decimal(12,2) DEFAULT NULL, +`BEFORE_DUE_7` decimal(12,2) DEFAULT NULL, +`PER1` decimal(12,2) DEFAULT NULL, +`PER2` decimal(12,2) DEFAULT NULL, +`PER3` decimal(12,2) DEFAULT NULL, +`PER4` decimal(12,2) DEFAULT NULL, +`PER5` decimal(12,2) DEFAULT NULL, +`PER6` decimal(12,2) DEFAULT NULL, +`PER7` decimal(12,2) DEFAULT NULL, +`DIVISION` varchar(3) CHARACTER SET utf8 NOT NULL, +`CLIENT_INFO` varchar(294) CHARACTER SET utf8 DEFAULT NULL, +`EXCHANGE_RATE` double NOT NULL, +`REF` varchar(30) DEFAULT NULL +); +explain +SELECT A.RECEIVABLE_GROUP,A.CLIENT_INFO,A.CLIENT_NAME,A.PHONE_NUMBER,A.ATTENTION_NAME,A.PAYMENT_TERM,A.CREDIT_LIMIT,A.LAST_PAY_DATE,A.TOTAL, +COALESCE(B.TOTAL_MCL,0) AS TOTAL_MCL, +COALESCE(C.TOTAL_MFS,0) AS TOTAL_MFS, +COALESCE(D.TOTAL_MIS,0) AS TOTAL_MIS, +COALESCE(F.BEFORE_DUE_7_MCL,0) AS BEFORE_DUE_7_MCL, +COALESCE(G.BEFORE_DUE_7_MFS,0) AS BEFORE_DUE_7_MFS, +COALESCE(H.BEFORE_DUE_7_MIS,0) AS BEFORE_DUE_7_MIS, +COALESCE(I.PER1_MCL,0) AS PER1_MCL, +COALESCE(J.PER1_MFS,0) AS PER1_MFS, +COALESCE(K.PER1_MIS,0) AS PER1_MIS, +COALESCE(L.PER2_MCL,0) AS PER2_MCL, +COALESCE(M.PER2_MFS,0) AS PER2_MFS, +COALESCE(N.PER2_MIS,0) AS PER2_MIS, +COALESCE(O.PER3_MCL,0) AS PER3_MCL, +COALESCE(P.PER3_MFS,0) AS PER3_MFS, +COALESCE(R.PER3_MIS,0) AS PER3_MIS, +COALESCE(S.PER4_MCL,0) AS PER4_MCL, +COALESCE(T.PER4_MFS,0) AS PER4_MFS, +COALESCE(U.PER4_MIS,0) AS PER4_MIS, +COALESCE(V.PER5_MCL,0) AS PER5_MCL, +COALESCE(X.PER5_MFS,0) AS PER5_MFS, +COALESCE(Z.PER5_MIS,0) AS PER5_MIS, +COALESCE(Q.PER6_MCL,0) AS PER6_MCL, +COALESCE(Y.PER6_MFS,0) AS PER6_MFS, +COALESCE(W.PER6_MIS,0) AS PER6_MIS, +COALESCE(A1.PER7_MCL,0) AS PER7_MCL, +COALESCE(B1.PER7_MFS,0) AS PER7_MFS, +COALESCE(C1.PER7_MIS,0) AS PER7_MIS, +A.BEFORE_DUE_7,A.PER1,A.PER2,A.PER3,A.PER4,A.PER5,A.PER6,A.PER7, +CONCAT(A.DIVISION,'-',A.CLIENT_NUMBER) AS REF,"2" AS TYPE FROM +(SELECT RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER, +GROUP_CONCAT(DISTINCT CLIENT_INFO SEPARATOR '<br>') AS CLIENT_INFO, +GROUP_CONCAT(DISTINCT CLIENT_NAME SEPARATOR '<br>') AS CLIENT_NAME, +GROUP_CONCAT( DISTINCT `PHONE_NUMBER` SEPARATOR '<br>' ) AS PHONE_NUMBER , +GROUP_CONCAT( DISTINCT `ATTENTION_NAME` SEPARATOR '<br>' ) AS ATTENTION_NAME, +GROUP_CONCAT( DISTINCT `PAYMENT_TERM` SEPARATOR '<br>' ) AS PAYMENT_TERM, +CREDIT_LIMIT , +GROUP_CONCAT( `LAST_PAY_DATE` SEPARATOR '<br>' ) AS LAST_PAY_DATE, +SUM( `TOTAL`*EXCHANGE_RATE ) AS TOTAL, +SUM( `BEFORE_DUE_7`*EXCHANGE_RATE ) AS BEFORE_DUE_7, +SUM( `PER1`*EXCHANGE_RATE ) AS PER1, +SUM( `PER2`*EXCHANGE_RATE ) AS PER2, +SUM( `PER3`*EXCHANGE_RATE ) AS PER3, +SUM( `PER4`*EXCHANGE_RATE ) AS PER4, +SUM( `PER5`*EXCHANGE_RATE ) AS PER5, +SUM( `PER6`*EXCHANGE_RATE ) AS PER6, +SUM( `PER7`*EXCHANGE_RATE ) AS PER7 +FROM `t2` +WHERE REF IS NULL GROUP BY RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT) AS A +LEFT JOIN +(SELECT RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT,SUM( `TOTAL`*EXCHANGE_RATE ) AS TOTAL_MCL +FROM `t2` +WHERE REF IS NULL AND DIVISION="MCL" GROUP BY RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT) AS B ON A.CLIENT_NUMBER=B.CLIENT_NUMBER AND +A.DIVISION=B.DIVISION AND A.RECEIVABLE_GROUP=B.RECEIVABLE_GROUP AND A.CREDIT_LIMIT=B.CREDIT_LIMIT +LEFT JOIN +(SELECT RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT,SUM( `TOTAL`*EXCHANGE_RATE ) AS TOTAL_MFS +FROM `t2` +WHERE REF IS NULL AND DIVISION="MFS" GROUP BY RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT) AS C ON A.CLIENT_NUMBER=C.CLIENT_NUMBER +AND +A.DIVISION=C.DIVISION AND A.RECEIVABLE_GROUP=C.RECEIVABLE_GROUP AND A.CREDIT_LIMIT=C.CREDIT_LIMIT +LEFT JOIN +(SELECT RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT,SUM( `TOTAL`*EXCHANGE_RATE ) AS TOTAL_MIS +FROM `t2` +WHERE REF IS NULL AND DIVISION="MIS" GROUP BY RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT) AS D ON A.CLIENT_NUMBER=D.CLIENT_NUMBER AND +A.DIVISION=D.DIVISION AND A.RECEIVABLE_GROUP=D.RECEIVABLE_GROUP AND A.CREDIT_LIMIT=D.CREDIT_LIMIT +LEFT JOIN +(SELECT RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT,SUM( BEFORE_DUE_7*EXCHANGE_RATE ) AS BEFORE_DUE_7_MCL +FROM `t2` +WHERE REF IS NULL AND DIVISION="MCL" GROUP BY RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT) AS F ON A.CLIENT_NUMBER=F.CLIENT_NUMBER AND +A.DIVISION=F.DIVISION AND A.RECEIVABLE_GROUP=F.RECEIVABLE_GROUP AND A.CREDIT_LIMIT=F.CREDIT_LIMIT +LEFT JOIN +(SELECT RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT,SUM( BEFORE_DUE_7*EXCHANGE_RATE ) AS BEFORE_DUE_7_MFS +FROM `t2` +WHERE REF IS NULL AND DIVISION="MFS" GROUP BY RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT) AS G ON A.CLIENT_NUMBER=G.CLIENT_NUMBER AND +A.DIVISION=G.DIVISION AND A.RECEIVABLE_GROUP=G.RECEIVABLE_GROUP AND A.CREDIT_LIMIT=G.CREDIT_LIMIT +LEFT JOIN +(SELECT RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT,SUM( BEFORE_DUE_7*EXCHANGE_RATE ) AS BEFORE_DUE_7_MIS +FROM `t2` +WHERE REF IS NULL AND DIVISION="MIS" GROUP BY RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT) AS H ON A.CLIENT_NUMBER=H.CLIENT_NUMBER AND +A.DIVISION=H.DIVISION AND A.RECEIVABLE_GROUP=H.RECEIVABLE_GROUP AND A.CREDIT_LIMIT=H.CREDIT_LIMIT +LEFT JOIN +(SELECT RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT,SUM( PER1*EXCHANGE_RATE ) AS PER1_MCL +FROM `t2` +WHERE REF IS NULL AND DIVISION="MCL" GROUP BY RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT) AS I ON A.CLIENT_NUMBER=I.CLIENT_NUMBER AND +A.DIVISION=I.DIVISION AND A.RECEIVABLE_GROUP=I.RECEIVABLE_GROUP AND A.CREDIT_LIMIT=I.CREDIT_LIMIT +LEFT JOIN +(SELECT RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT,SUM( PER1*EXCHANGE_RATE ) AS PER1_MFS +FROM `t2` +WHERE REF IS NULL AND DIVISION="MFS" GROUP BY RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT) AS J ON A.CLIENT_NUMBER=J.CLIENT_NUMBER AND +A.DIVISION=J.DIVISION AND A.RECEIVABLE_GROUP=J.RECEIVABLE_GROUP AND A.CREDIT_LIMIT=J.CREDIT_LIMIT +LEFT JOIN +(SELECT RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT,SUM( PER1*EXCHANGE_RATE ) AS PER1_MIS +FROM `t2` +WHERE REF IS NULL AND DIVISION="MIS" GROUP BY RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT) AS K ON A.CLIENT_NUMBER=K.CLIENT_NUMBER AND +A.DIVISION=K.DIVISION AND A.RECEIVABLE_GROUP=K.RECEIVABLE_GROUP AND A.CREDIT_LIMIT=K.CREDIT_LIMIT +LEFT JOIN +(SELECT RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT,SUM( PER2*EXCHANGE_RATE ) AS PER2_MCL +FROM `t2` +WHERE REF IS NULL AND DIVISION="MCL" GROUP BY RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT) AS L ON A.CLIENT_NUMBER=L.CLIENT_NUMBER AND +A.DIVISION=L.DIVISION AND A.RECEIVABLE_GROUP=L.RECEIVABLE_GROUP AND A.CREDIT_LIMIT=L.CREDIT_LIMIT +LEFT JOIN +(SELECT RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT,SUM( PER2*EXCHANGE_RATE ) AS PER2_MFS +FROM `t2` +WHERE REF IS NULL AND DIVISION="MFS" GROUP BY RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT) AS M ON A.CLIENT_NUMBER=M.CLIENT_NUMBER AND +A.DIVISION=M.DIVISION AND A.RECEIVABLE_GROUP=M.RECEIVABLE_GROUP AND A.CREDIT_LIMIT=M.CREDIT_LIMIT +LEFT JOIN +(SELECT RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT,SUM( PER2*EXCHANGE_RATE ) AS PER2_MIS +FROM `t2` +WHERE REF IS NULL AND DIVISION="MIS" GROUP BY RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT) AS N ON A.CLIENT_NUMBER=N.CLIENT_NUMBER AND +A.DIVISION=N.DIVISION AND A.RECEIVABLE_GROUP=N.RECEIVABLE_GROUP AND A.CREDIT_LIMIT=N.CREDIT_LIMIT +LEFT JOIN +(SELECT RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT,SUM( PER3*EXCHANGE_RATE ) AS PER3_MCL +FROM `t2` +WHERE REF IS NULL AND DIVISION="MCL" GROUP BY RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT) AS O ON A.CLIENT_NUMBER=O.CLIENT_NUMBER AND +A.DIVISION=O.DIVISION AND A.RECEIVABLE_GROUP=O.RECEIVABLE_GROUP AND A.CREDIT_LIMIT=O.CREDIT_LIMIT +LEFT JOIN +(SELECT RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT,SUM( PER3*EXCHANGE_RATE ) AS PER3_MFS +FROM `t2` +WHERE REF IS NULL AND DIVISION="MFS" GROUP BY RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT) AS P ON A.CLIENT_NUMBER=P.CLIENT_NUMBER AND +A.DIVISION=P.DIVISION AND A.RECEIVABLE_GROUP=P.RECEIVABLE_GROUP AND A.CREDIT_LIMIT=P.CREDIT_LIMIT +LEFT JOIN +(SELECT RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT,SUM( PER3*EXCHANGE_RATE ) AS PER3_MIS +FROM `t2` +WHERE REF IS NULL AND DIVISION="MIS" GROUP BY RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT) AS R ON A.CLIENT_NUMBER=R.CLIENT_NUMBER AND +A.DIVISION=R.DIVISION AND A.RECEIVABLE_GROUP=R.RECEIVABLE_GROUP AND A.CREDIT_LIMIT=R.CREDIT_LIMIT +LEFT JOIN +(SELECT RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT,SUM( PER4*EXCHANGE_RATE ) AS PER4_MCL +FROM `t2` +WHERE REF IS NULL AND DIVISION="MCL" GROUP BY RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT) AS S ON A.CLIENT_NUMBER=S.CLIENT_NUMBER AND +A.DIVISION=S.DIVISION AND A.RECEIVABLE_GROUP=S.RECEIVABLE_GROUP AND A.CREDIT_LIMIT=S.CREDIT_LIMIT +LEFT JOIN +(SELECT RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT,SUM( PER4*EXCHANGE_RATE ) AS PER4_MFS +FROM `t2` +WHERE REF IS NULL AND DIVISION="MFS" GROUP BY RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT) AS T ON A.CLIENT_NUMBER=T.CLIENT_NUMBER AND +A.DIVISION=T.DIVISION AND A.RECEIVABLE_GROUP=T.RECEIVABLE_GROUP AND A.CREDIT_LIMIT=T.CREDIT_LIMIT +LEFT JOIN +(SELECT RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT,SUM( PER4*EXCHANGE_RATE ) AS PER4_MIS +FROM `t2` +WHERE REF IS NULL AND DIVISION="MIS" GROUP BY RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT) AS U ON A.CLIENT_NUMBER=U.CLIENT_NUMBER AND +A.DIVISION=U.DIVISION AND A.RECEIVABLE_GROUP=U.RECEIVABLE_GROUP AND A.CREDIT_LIMIT=U.CREDIT_LIMIT +LEFT JOIN +(SELECT RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT,SUM( PER5*EXCHANGE_RATE ) AS PER5_MCL +FROM `t2` +WHERE REF IS NULL AND DIVISION="MCL" GROUP BY RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT) AS V ON A.CLIENT_NUMBER=V.CLIENT_NUMBER AND +A.DIVISION=V.DIVISION AND A.RECEIVABLE_GROUP=V.RECEIVABLE_GROUP AND A.CREDIT_LIMIT=V.CREDIT_LIMIT +LEFT JOIN +(SELECT RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT,SUM( PER5*EXCHANGE_RATE ) AS PER5_MFS +FROM `t2` +WHERE REF IS NULL AND DIVISION="MFS" GROUP BY RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT) AS X ON A.CLIENT_NUMBER=X.CLIENT_NUMBER AND +A.DIVISION=X.DIVISION AND A.RECEIVABLE_GROUP=X.RECEIVABLE_GROUP AND A.CREDIT_LIMIT=X.CREDIT_LIMIT +LEFT JOIN +(SELECT RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT,SUM( PER5*EXCHANGE_RATE ) AS PER5_MIS +FROM `t2` +WHERE REF IS NULL AND DIVISION="MIS" GROUP BY RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT) AS Z ON A.CLIENT_NUMBER=Z.CLIENT_NUMBER AND +A.DIVISION=Z.DIVISION AND A.RECEIVABLE_GROUP=Z.RECEIVABLE_GROUP AND A.CREDIT_LIMIT=Z.CREDIT_LIMIT +LEFT JOIN +(SELECT RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT,SUM( PER6*EXCHANGE_RATE ) AS PER6_MCL +FROM `t2` +WHERE REF IS NULL AND DIVISION="MCL" GROUP BY RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT) AS Q ON A.CLIENT_NUMBER=Q.CLIENT_NUMBER AND +A.DIVISION=Q.DIVISION AND A.RECEIVABLE_GROUP=Q.RECEIVABLE_GROUP AND A.CREDIT_LIMIT=Q.CREDIT_LIMIT +LEFT JOIN +(SELECT RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT,SUM( PER6*EXCHANGE_RATE ) AS PER6_MFS +FROM `t2` +WHERE REF IS NULL AND DIVISION="MFS" GROUP BY RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT) AS Y ON A.CLIENT_NUMBER=Y.CLIENT_NUMBER AND +A.DIVISION=Y.DIVISION AND A.RECEIVABLE_GROUP=Y.RECEIVABLE_GROUP AND A.CREDIT_LIMIT=Y.CREDIT_LIMIT +LEFT JOIN +(SELECT RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT,SUM( PER6*EXCHANGE_RATE ) AS PER6_MIS +FROM `t2` +WHERE REF IS NULL AND DIVISION="MIS" GROUP BY RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT) AS W ON A.CLIENT_NUMBER=W.CLIENT_NUMBER AND +A.DIVISION=W.DIVISION AND A.RECEIVABLE_GROUP=W.RECEIVABLE_GROUP AND A.CREDIT_LIMIT=W.CREDIT_LIMIT +LEFT JOIN +(SELECT RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT,SUM( PER7*EXCHANGE_RATE ) AS PER7_MCL +FROM `t2` +WHERE REF IS NULL AND DIVISION="MCL" GROUP BY RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT) AS A1 ON A.CLIENT_NUMBER=A1.CLIENT_NUMBER AND +A.DIVISION=A1.DIVISION AND A.RECEIVABLE_GROUP=A1.RECEIVABLE_GROUP AND A.CREDIT_LIMIT=A1.CREDIT_LIMIT +LEFT JOIN +(SELECT RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT,SUM( PER7*EXCHANGE_RATE ) AS PER7_MFS +FROM `t2` +WHERE REF IS NULL AND DIVISION="MFS" GROUP BY RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT) AS B1 ON A.CLIENT_NUMBER=B1.CLIENT_NUMBER AND +A.DIVISION=B1.DIVISION AND A.RECEIVABLE_GROUP=B1.RECEIVABLE_GROUP AND A.CREDIT_LIMIT=B1.CREDIT_LIMIT +LEFT JOIN +(SELECT RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT,SUM( PER7*EXCHANGE_RATE ) AS PER7_MIS +FROM `t2` +WHERE REF IS NULL AND DIVISION="MIS" GROUP BY RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT) AS C1 ON A.CLIENT_NUMBER=C1.CLIENT_NUMBER AND +A.DIVISION=C1.DIVISION AND A.RECEIVABLE_GROUP=C1.RECEIVABLE_GROUP AND A.CREDIT_LIMIT=C1.CREDIT_LIMIT +ORDER BY TOTAL DESC; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY <derived2> system NULL NULL NULL NULL 0 const row not found +1 PRIMARY <derived3> system NULL NULL NULL NULL 0 const row not found +1 PRIMARY <derived4> system NULL NULL NULL NULL 0 const row not found +1 PRIMARY <derived5> system NULL NULL NULL NULL 0 const row not found +1 PRIMARY <derived6> system NULL NULL NULL NULL 0 const row not found +1 PRIMARY <derived7> system NULL NULL NULL NULL 0 const row not found +1 PRIMARY <derived8> system NULL NULL NULL NULL 0 const row not found +1 PRIMARY <derived9> system NULL NULL NULL NULL 0 const row not found +1 PRIMARY <derived10> system NULL NULL NULL NULL 0 const row not found +1 PRIMARY <derived11> system NULL NULL NULL NULL 0 const row not found +1 PRIMARY <derived12> system NULL NULL NULL NULL 0 const row not found +1 PRIMARY <derived13> system NULL NULL NULL NULL 0 const row not found +1 PRIMARY <derived14> system NULL NULL NULL NULL 0 const row not found +1 PRIMARY <derived15> system NULL NULL NULL NULL 0 const row not found +1 PRIMARY <derived16> system NULL NULL NULL NULL 0 const row not found +1 PRIMARY <derived17> system NULL NULL NULL NULL 0 const row not found +1 PRIMARY <derived18> system NULL NULL NULL NULL 0 const row not found +1 PRIMARY <derived19> system NULL NULL NULL NULL 0 const row not found +1 PRIMARY <derived20> system NULL NULL NULL NULL 0 const row not found +1 PRIMARY <derived21> system NULL NULL NULL NULL 0 const row not found +1 PRIMARY <derived22> system NULL NULL NULL NULL 0 const row not found +1 PRIMARY <derived23> system NULL NULL NULL NULL 0 const row not found +1 PRIMARY <derived24> system NULL NULL NULL NULL 0 const row not found +1 PRIMARY <derived25> system NULL NULL NULL NULL 0 const row not found +1 PRIMARY <derived26> system NULL NULL NULL NULL 0 const row not found +1 PRIMARY <derived27> system NULL NULL NULL NULL 0 const row not found +1 PRIMARY <derived28> system NULL NULL NULL NULL 0 const row not found +1 PRIMARY <derived29> system NULL NULL NULL NULL 0 const row not found +29 DERIVED NULL NULL NULL NULL NULL NULL NULL no matching row in const table +28 DERIVED NULL NULL NULL NULL NULL NULL NULL no matching row in const table +27 DERIVED NULL NULL NULL NULL NULL NULL NULL no matching row in const table +26 DERIVED NULL NULL NULL NULL NULL NULL NULL no matching row in const table +25 DERIVED NULL NULL NULL NULL NULL NULL NULL no matching row in const table +24 DERIVED NULL NULL NULL NULL NULL NULL NULL no matching row in const table +23 DERIVED NULL NULL NULL NULL NULL NULL NULL no matching row in const table +22 DERIVED NULL NULL NULL NULL NULL NULL NULL no matching row in const table +21 DERIVED NULL NULL NULL NULL NULL NULL NULL no matching row in const table +20 DERIVED NULL NULL NULL NULL NULL NULL NULL no matching row in const table +19 DERIVED NULL NULL NULL NULL NULL NULL NULL no matching row in const table +18 DERIVED NULL NULL NULL NULL NULL NULL NULL no matching row in const table +17 DERIVED NULL NULL NULL NULL NULL NULL NULL no matching row in const table +16 DERIVED NULL NULL NULL NULL NULL NULL NULL no matching row in const table +15 DERIVED NULL NULL NULL NULL NULL NULL NULL no matching row in const table +14 DERIVED NULL NULL NULL NULL NULL NULL NULL no matching row in const table +13 DERIVED NULL NULL NULL NULL NULL NULL NULL no matching row in const table +12 DERIVED NULL NULL NULL NULL NULL NULL NULL no matching row in const table +11 DERIVED NULL NULL NULL NULL NULL NULL NULL no matching row in const table +10 DERIVED NULL NULL NULL NULL NULL NULL NULL no matching row in const table +9 DERIVED NULL NULL NULL NULL NULL NULL NULL no matching row in const table +8 DERIVED NULL NULL NULL NULL NULL NULL NULL no matching row in const table +7 DERIVED NULL NULL NULL NULL NULL NULL NULL no matching row in const table +6 DERIVED NULL NULL NULL NULL NULL NULL NULL no matching row in const table +5 DERIVED NULL NULL NULL NULL NULL NULL NULL no matching row in const table +4 DERIVED NULL NULL NULL NULL NULL NULL NULL no matching row in const table +3 DERIVED NULL NULL NULL NULL NULL NULL NULL no matching row in const table +2 DERIVED NULL NULL NULL NULL NULL NULL NULL no matching row in const table +DROP TABLES t1,t2; set optimizer_switch=@save_derived_optimizer_switch; diff --git a/mysql-test/r/func_str.result b/mysql-test/r/func_str.result index bc5f6951184..8f08d7acd25 100644 --- a/mysql-test/r/func_str.result +++ b/mysql-test/r/func_str.result @@ -203,7 +203,7 @@ CONCAT('"',CONCAT_WS('";"',repeat('a',60),repeat('b',60),repeat('c',60),repeat(' "aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa";"bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb";"cccccccccccccccccccccccccccccccccccccccccccccccccccccccccccc";"dddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddd" select insert('txs',2,1,'hi'),insert('is ',4,0,'a'),insert('txxxxt',2,4,'es'); insert('txs',2,1,'hi') insert('is ',4,0,'a') insert('txxxxt',2,4,'es') -this is a test +this is test select replace('aaaa','a','b'),replace('aaaa','aa','b'),replace('aaaa','a','bb'),replace('aaaa','','b'),replace('bbbb','a','c'); replace('aaaa','a','b') replace('aaaa','aa','b') replace('aaaa','a','bb') replace('aaaa','','b') replace('bbbb','a','c') bbbb bb bbbbbbbb aaaa bbbb @@ -2335,7 +2335,7 @@ INSERT('abc', 3, 3, '1234') ab1234 SELECT INSERT('abc', 4, 3, '1234'); INSERT('abc', 4, 3, '1234') -abc1234 +abc SELECT INSERT('abc', 5, 3, '1234'); INSERT('abc', 5, 3, '1234') abc @@ -2625,7 +2625,7 @@ CREATE TABLE t1 ( a TEXT ); SELECT 'aaaaaaaaaaaaaa' INTO OUTFILE 'MYSQLTEST_VARDIR/tmp/bug58165.txt';; SELECT insert( substring_index( 'a', 'a', 'b' ), 1, 0, 'x' ); insert( substring_index( 'a', 'a', 'b' ), 1, 0, 'x' ) -x + Warnings: Warning 1292 Truncated incorrect INTEGER value: 'b' LOAD DATA INFILE 'MYSQLTEST_VARDIR/tmp/bug58165.txt' INTO TABLE t1;; diff --git a/mysql-test/r/func_time.result b/mysql-test/r/func_time.result index b660df15fed..85ba5d73f36 100644 --- a/mysql-test/r/func_time.result +++ b/mysql-test/r/func_time.result @@ -1722,6 +1722,43 @@ select 1 from t1 where 1 < some (select cast(a as datetime) from t1); 1 1 drop table t1; +# +# Bug #21564557: INCONSISTENT OUTPUT FROM 5.5 AND 5.6 +# UNIX_TIMESTAMP(STR_TO_DATE('201506', "%Y%M" +# +SELECT UNIX_TIMESTAMP(STR_TO_DATE('201506', "%Y%m")); +UNIX_TIMESTAMP(STR_TO_DATE('201506', "%Y%m")) +NULL +Warnings: +Warning 1411 Incorrect datetime value: '201506' for function str_to_date +SELECT UNIX_TIMESTAMP('2015-06-00'); +UNIX_TIMESTAMP('2015-06-00') +NULL +Warnings: +Warning 1292 Incorrect datetime value: '2015-06-00' +SELECT UNIX_TIMESTAMP(STR_TO_DATE('0000-00-00 10:30:30', '%Y-%m-%d %h:%i:%s')); +UNIX_TIMESTAMP(STR_TO_DATE('0000-00-00 10:30:30', '%Y-%m-%d %h:%i:%s')) +NULL +set sql_mode= 'TRADITIONAL'; +SELECT @@sql_mode; +@@sql_mode +STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION +SELECT UNIX_TIMESTAMP(STR_TO_DATE('201506', "%Y%m")); +UNIX_TIMESTAMP(STR_TO_DATE('201506', "%Y%m")) +NULL +Warnings: +Warning 1411 Incorrect datetime value: '201506' for function str_to_date +SELECT UNIX_TIMESTAMP('2015-06-00'); +UNIX_TIMESTAMP('2015-06-00') +NULL +Warnings: +Warning 1292 Incorrect datetime value: '2015-06-00' +SELECT UNIX_TIMESTAMP(STR_TO_DATE('0000-00-00 10:30:30', '%Y-%m-%d %h:%i:%s')); +UNIX_TIMESTAMP(STR_TO_DATE('0000-00-00 10:30:30', '%Y-%m-%d %h:%i:%s')) +NULL +Warnings: +Warning 1411 Incorrect datetime value: '0000-00-00 10:30:30' for function str_to_date +set sql_mode= default; select time('10:10:10') > 10; time('10:10:10') > 10 1 diff --git a/mysql-test/r/merge_innodb.result b/mysql-test/r/merge_innodb.result index f6057d279b1..5aa344a9391 100644 --- a/mysql-test/r/merge_innodb.result +++ b/mysql-test/r/merge_innodb.result @@ -35,3 +35,36 @@ c1 Ann Alice DROP TABLE t1, t2, t3, t4, t5; +create table t1 (c1 varchar(100)); +create table t2 (c1 varchar(100)); +create view t3 as select * from t1; +insert into t1 values ('ann'), ('alice'); +insert into t2 values ('bob'), ('brian'); +create temporary table t4 (c1 varchar(100)) engine=MERGE union=(t2, t1); +create temporary table t5 (c1 varchar(100)) engine=MERGE union=(t3, t1); +select * from t5; +ERROR HY000: Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist +lock tables t1 read, t2 read, t3 read, t4 read; +select * from t5; +ERROR HY000: Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist +select * from t4; +c1 +bob +brian +ann +alice +unlock tables; +drop table t2; +create view t2 as select * from t1; +select * from t4; +ERROR HY000: Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist +lock tables t1 read, t2 read, t3 read; +select * from t4; +ERROR HY000: Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist +select * from t4; +ERROR HY000: Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist +select * from t4; +ERROR HY000: Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist +unlock tables; +drop view t2, t3; +drop table t1; diff --git a/mysql-test/r/mysql_upgrade_view.result b/mysql-test/r/mysql_upgrade_view.result index 63f86af3591..7966941cb1f 100644 --- a/mysql-test/r/mysql_upgrade_view.result +++ b/mysql-test/r/mysql_upgrade_view.result @@ -318,4 +318,64 @@ master-bin.000001 # Gtid # # GTID #-#-# master-bin.000001 # Query # # use `test`; REPAIR VIEW `v4` FROM MYSQL drop table if exists kv; drop view v1,v2,v3,v4; +rename table mysql.event to mysql.ev_bk; +flush tables; +The --upgrade-system-tables option was used, user tables won't be touched. +MySQL upgrade detected +Phase 1/6: Checking and upgrading mysql database +Processing databases +mysql +mysql.column_stats OK +mysql.columns_priv OK +mysql.db OK +mysql.ev_bk OK +mysql.event OK +mysql.func OK +mysql.gtid_slave_pos OK +mysql.help_category OK +mysql.help_keyword OK +mysql.help_relation OK +mysql.help_topic OK +mysql.host OK +mysql.index_stats OK +mysql.innodb_index_stats +Error : Unknown storage engine 'InnoDB' +error : Corrupt +mysql.innodb_table_stats +Error : Unknown storage engine 'InnoDB' +error : Corrupt +mysql.plugin OK +mysql.proc OK +mysql.procs_priv OK +mysql.proxies_priv OK +mysql.roles_mapping OK +mysql.servers OK +mysql.table_stats OK +mysql.tables_priv OK +mysql.time_zone OK +mysql.time_zone_leap_second OK +mysql.time_zone_name OK +mysql.time_zone_transition OK +mysql.time_zone_transition_type OK +mysql.user OK + +Repairing tables +mysql.innodb_index_stats +Error : Unknown storage engine 'InnoDB' +error : Corrupt +mysql.innodb_table_stats +Error : Unknown storage engine 'InnoDB' +error : Corrupt +Phase 2/6: Fixing views from mysql +test.v1 OK +test.v2 OK +test.v3 OK +Phase 3/6: Running 'mysql_fix_privilege_tables' +Phase 4/6: Fixing table and database names ... Skipped +Phase 5/6: Checking and upgrading tables... Skipped +Phase 6/6: Running 'FLUSH PRIVILEGES' +OK +drop table mysql.event; +rename table mysql.ev_bk to mysql.event; +drop view v1,v2,v3; drop table t1; diff --git a/mysql-test/r/second_frac-9175.result b/mysql-test/r/second_frac-9175.result new file mode 100644 index 00000000000..dbf268b5c3b --- /dev/null +++ b/mysql-test/r/second_frac-9175.result @@ -0,0 +1,13 @@ +select timestampdiff(microsecond,'2000-01-01 00:00:00','2001-01-01 00:00:00.123456'); +timestampdiff(microsecond,'2000-01-01 00:00:00','2001-01-01 00:00:00.123456') +31622400123456 +explain extended select timestampdiff(microsecond,'2000-01-01 00:00:00','2001-01-01 00:00:00.123456'); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used +Warnings: +Note 1003 select timestampdiff(MICROSECOND,'2000-01-01 00:00:00','2001-01-01 00:00:00.123456') AS `timestampdiff(microsecond,'2000-01-01 00:00:00','2001-01-01 00:00:00.123456')` +create view v1 as select timestampdiff(microsecond,'2000-01-01 00:00:00','2001-01-01 00:00:00.123456'); +select * from v1; +Name_exp_1 +31622400123456 +drop view v1; diff --git a/mysql-test/r/sp.result b/mysql-test/r/sp.result index cf50bf49004..471576e563f 100644 --- a/mysql-test/r/sp.result +++ b/mysql-test/r/sp.result @@ -4323,57 +4323,57 @@ test.t1 repair status OK test.t2 repair status OK test.t3 repair status OK test.v1 repair Error 'test.v1' is not BASE TABLE -test.v1 repair error Corrupt +test.v1 repair status Operation failed Table Op Msg_type Msg_text test.t1 optimize status OK test.t2 optimize status OK test.t3 optimize status OK test.v1 optimize Error 'test.v1' is not BASE TABLE -test.v1 optimize error Corrupt +test.v1 optimize status Operation failed Table Op Msg_type Msg_text test.t1 analyze status Table is already up to date test.t2 analyze status Table is already up to date test.t3 analyze status Table is already up to date test.v1 analyze Error 'test.v1' is not BASE TABLE -test.v1 analyze error Corrupt +test.v1 analyze status Operation failed call bug13012()| Table Op Msg_type Msg_text test.t1 repair status OK test.t2 repair status OK test.t3 repair status OK test.v1 repair Error 'test.v1' is not BASE TABLE -test.v1 repair error Corrupt +test.v1 repair status Operation failed Table Op Msg_type Msg_text test.t1 optimize status OK test.t2 optimize status OK test.t3 optimize status OK test.v1 optimize Error 'test.v1' is not BASE TABLE -test.v1 optimize error Corrupt +test.v1 optimize status Operation failed Table Op Msg_type Msg_text test.t1 analyze status Table is already up to date test.t2 analyze status Table is already up to date test.t3 analyze status Table is already up to date test.v1 analyze Error 'test.v1' is not BASE TABLE -test.v1 analyze error Corrupt +test.v1 analyze status Operation failed call bug13012()| Table Op Msg_type Msg_text test.t1 repair status OK test.t2 repair status OK test.t3 repair status OK test.v1 repair Error 'test.v1' is not BASE TABLE -test.v1 repair error Corrupt +test.v1 repair status Operation failed Table Op Msg_type Msg_text test.t1 optimize status OK test.t2 optimize status OK test.t3 optimize status OK test.v1 optimize Error 'test.v1' is not BASE TABLE -test.v1 optimize error Corrupt +test.v1 optimize status Operation failed Table Op Msg_type Msg_text test.t1 analyze status Table is already up to date test.t2 analyze status Table is already up to date test.t3 analyze status Table is already up to date test.v1 analyze Error 'test.v1' is not BASE TABLE -test.v1 analyze error Corrupt +test.v1 analyze status Operation failed drop procedure bug13012| drop view v1| select * from t1 order by data| diff --git a/mysql-test/r/ssl_cert_verify.result b/mysql-test/r/ssl_cert_verify.result new file mode 100644 index 00000000000..1da77329509 --- /dev/null +++ b/mysql-test/r/ssl_cert_verify.result @@ -0,0 +1,5 @@ +#T1: Host name (/CN=localhost/) as OU name in the server certificate, server certificate verification should fail. +#T2: Host name (localhost) as common name in the server certificate, server certificate verification should pass. +Variable_name Value +Ssl_version TLS_VERSION +# restart server using restart diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result index f49a9bc4aa0..fa6a0624dcb 100644 --- a/mysql-test/r/subselect.result +++ b/mysql-test/r/subselect.result @@ -7086,6 +7086,20 @@ NULL deallocate prepare stmt; drop table t1,t2,t3,t4; # +# MDEV-7122 +# Assertion `0' failed in subselect_hash_sj_engine::init +# +SET SESSION big_tables=1; +CREATE TABLE t1(a char(255) DEFAULT '', KEY(a(10))) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; +INSERT INTO t1 VALUES(0),(0),(0); +SELECT * FROM t1 WHERE a IN(SELECT MIN(a) FROM t1); +a +0 +0 +0 +DROP TABLE t1; +SET SESSION big_tables=0; +# # MDEV-7930: Assertion `table_share->tmp_table != NO_TMP_TABLE || # m_lock_type != 2' failed in handler::ha_index_read_map # diff --git a/mysql-test/r/subselect_extra_no_semijoin.result b/mysql-test/r/subselect_extra_no_semijoin.result index e5b36055c91..79bca388181 100644 --- a/mysql-test/r/subselect_extra_no_semijoin.result +++ b/mysql-test/r/subselect_extra_no_semijoin.result @@ -349,9 +349,9 @@ WHERE t.a IN (SELECT b FROM t1); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 system NULL NULL NULL NULL 1 100.00 1 PRIMARY t2 ALL NULL NULL NULL NULL 2 100.00 Using where -3 MATERIALIZED t1 system NULL NULL NULL NULL 1 100.00 +3 DEPENDENT SUBQUERY t1 system NULL NULL NULL NULL 1 100.00 Warnings: -Note 1003 select `test`.`t2`.`a` AS `a`,0 AS `a`,0 AS `b` from `test`.`t2` where <expr_cache><0>(<in_optimizer>(0,0 in ( <materialize> (select 0 from dual ), <primary_index_lookup>(0 in <temporary table> on distinct_key where ((0 = `<subquery3>`.`b`)))))) +Note 1003 select `test`.`t2`.`a` AS `a`,0 AS `a`,0 AS `b` from `test`.`t2` where <expr_cache><0>(<in_optimizer>(0,<exists>(select 0 from dual where (<cache>(0) = 0)))) SELECT * FROM t2 RIGHT JOIN v1 AS t ON t.a != 0 WHERE t.a IN (SELECT b FROM t1); a a b @@ -362,9 +362,9 @@ WHERE t.a IN (SELECT b FROM t1); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 system NULL NULL NULL NULL 1 100.00 1 PRIMARY t2 ALL NULL NULL NULL NULL 2 100.00 Using where -2 MATERIALIZED t1 system NULL NULL NULL NULL 1 100.00 +2 DEPENDENT SUBQUERY t1 system NULL NULL NULL NULL 1 100.00 Warnings: -Note 1003 select `test`.`t2`.`a` AS `a`,0 AS `a`,0 AS `b` from `test`.`t2` where <expr_cache><0>(<in_optimizer>(0,0 in ( <materialize> (select 0 from dual ), <primary_index_lookup>(0 in <temporary table> on distinct_key where ((0 = `<subquery2>`.`b`)))))) +Note 1003 select `test`.`t2`.`a` AS `a`,0 AS `a`,0 AS `b` from `test`.`t2` where <expr_cache><0>(<in_optimizer>(0,<exists>(select 0 from dual where (<cache>(0) = 0)))) DROP VIEW v1; DROP TABLE t1,t2; # diff --git a/mysql-test/r/subselect_no_exists_to_in.result b/mysql-test/r/subselect_no_exists_to_in.result index f6434c8fe4c..012d7a80610 100644 --- a/mysql-test/r/subselect_no_exists_to_in.result +++ b/mysql-test/r/subselect_no_exists_to_in.result @@ -7086,6 +7086,20 @@ NULL deallocate prepare stmt; drop table t1,t2,t3,t4; # +# MDEV-7122 +# Assertion `0' failed in subselect_hash_sj_engine::init +# +SET SESSION big_tables=1; +CREATE TABLE t1(a char(255) DEFAULT '', KEY(a(10))) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; +INSERT INTO t1 VALUES(0),(0),(0); +SELECT * FROM t1 WHERE a IN(SELECT MIN(a) FROM t1); +a +0 +0 +0 +DROP TABLE t1; +SET SESSION big_tables=0; +# # MDEV-7930: Assertion `table_share->tmp_table != NO_TMP_TABLE || # m_lock_type != 2' failed in handler::ha_index_read_map # diff --git a/mysql-test/r/subselect_no_mat.result b/mysql-test/r/subselect_no_mat.result index 49ad0ed4037..e83697fcb4f 100644 --- a/mysql-test/r/subselect_no_mat.result +++ b/mysql-test/r/subselect_no_mat.result @@ -7079,6 +7079,20 @@ NULL deallocate prepare stmt; drop table t1,t2,t3,t4; # +# MDEV-7122 +# Assertion `0' failed in subselect_hash_sj_engine::init +# +SET SESSION big_tables=1; +CREATE TABLE t1(a char(255) DEFAULT '', KEY(a(10))) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; +INSERT INTO t1 VALUES(0),(0),(0); +SELECT * FROM t1 WHERE a IN(SELECT MIN(a) FROM t1); +a +0 +0 +0 +DROP TABLE t1; +SET SESSION big_tables=0; +# # MDEV-7930: Assertion `table_share->tmp_table != NO_TMP_TABLE || # m_lock_type != 2' failed in handler::ha_index_read_map # diff --git a/mysql-test/r/subselect_no_opts.result b/mysql-test/r/subselect_no_opts.result index 2122747d262..c620f788cf7 100644 --- a/mysql-test/r/subselect_no_opts.result +++ b/mysql-test/r/subselect_no_opts.result @@ -7077,6 +7077,20 @@ NULL deallocate prepare stmt; drop table t1,t2,t3,t4; # +# MDEV-7122 +# Assertion `0' failed in subselect_hash_sj_engine::init +# +SET SESSION big_tables=1; +CREATE TABLE t1(a char(255) DEFAULT '', KEY(a(10))) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; +INSERT INTO t1 VALUES(0),(0),(0); +SELECT * FROM t1 WHERE a IN(SELECT MIN(a) FROM t1); +a +0 +0 +0 +DROP TABLE t1; +SET SESSION big_tables=0; +# # MDEV-7930: Assertion `table_share->tmp_table != NO_TMP_TABLE || # m_lock_type != 2' failed in handler::ha_index_read_map # diff --git a/mysql-test/r/subselect_no_scache.result b/mysql-test/r/subselect_no_scache.result index a1c468ba334..b2b2518bdd3 100644 --- a/mysql-test/r/subselect_no_scache.result +++ b/mysql-test/r/subselect_no_scache.result @@ -7092,6 +7092,20 @@ NULL deallocate prepare stmt; drop table t1,t2,t3,t4; # +# MDEV-7122 +# Assertion `0' failed in subselect_hash_sj_engine::init +# +SET SESSION big_tables=1; +CREATE TABLE t1(a char(255) DEFAULT '', KEY(a(10))) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; +INSERT INTO t1 VALUES(0),(0),(0); +SELECT * FROM t1 WHERE a IN(SELECT MIN(a) FROM t1); +a +0 +0 +0 +DROP TABLE t1; +SET SESSION big_tables=0; +# # MDEV-7930: Assertion `table_share->tmp_table != NO_TMP_TABLE || # m_lock_type != 2' failed in handler::ha_index_read_map # diff --git a/mysql-test/r/subselect_no_semijoin.result b/mysql-test/r/subselect_no_semijoin.result index 168908aa356..2bd82dffd36 100644 --- a/mysql-test/r/subselect_no_semijoin.result +++ b/mysql-test/r/subselect_no_semijoin.result @@ -7077,6 +7077,20 @@ NULL deallocate prepare stmt; drop table t1,t2,t3,t4; # +# MDEV-7122 +# Assertion `0' failed in subselect_hash_sj_engine::init +# +SET SESSION big_tables=1; +CREATE TABLE t1(a char(255) DEFAULT '', KEY(a(10))) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; +INSERT INTO t1 VALUES(0),(0),(0); +SELECT * FROM t1 WHERE a IN(SELECT MIN(a) FROM t1); +a +0 +0 +0 +DROP TABLE t1; +SET SESSION big_tables=0; +# # MDEV-7930: Assertion `table_share->tmp_table != NO_TMP_TABLE || # m_lock_type != 2' failed in handler::ha_index_read_map # diff --git a/mysql-test/r/subselect_sj.result b/mysql-test/r/subselect_sj.result index 46c799c4f6e..3ca3f0d35fb 100644 --- a/mysql-test/r/subselect_sj.result +++ b/mysql-test/r/subselect_sj.result @@ -2997,4 +2997,69 @@ explain select 1 from t1 where _cp932 "1" in (select '1' from t1); ERROR HY000: Illegal mix of collations (cp932_japanese_ci,COERCIBLE) and (latin1_swedish_ci,COERCIBLE) for operation '=' drop table t1; +# +# MDEV-7823: Server crashes in next_depth_first_tab on nested IN clauses with SQ inside +# +set @tmp_mdev7823=@@optimizer_switch; +set optimizer_switch=default; +CREATE TABLE t1 (f1 INT); +INSERT INTO t1 VALUES (1); +CREATE TABLE t2 (f2 INT, KEY(f2)); +INSERT INTO t2 VALUES (8),(0); +CREATE TABLE t3 (f3 INT); +INSERT INTO t3 VALUES (1),(2); +CREATE TABLE t4 (f4 INT); +INSERT INTO t4 VALUES (0),(5); +explain +SELECT * FROM t1, t2, t3 WHERE f2 IN ( f1 IN ( SELECT f4 FROM t4 ) ); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 system NULL NULL NULL NULL 1 +1 PRIMARY t2 ref f2 f2 5 const 0 Using where; Using index +1 PRIMARY t3 ALL NULL NULL NULL NULL 2 Using join buffer (flat, BNL join) +2 DEPENDENT SUBQUERY t4 ALL NULL NULL NULL NULL 2 Using where +SELECT * FROM t1, t2, t3 WHERE f2 IN ( f1 IN ( SELECT f4 FROM t4 ) ); +f1 f2 f3 +1 0 1 +1 0 2 +drop table t1,t2,t3,t4; +set optimizer_switch= @tmp_mdev7823; +# +# MDEV-6859: scalar subqueries in a comparison produced unexpected result +# +set @tmp_mdev6859=@@optimizer_switch; +set optimizer_switch=default; +CREATE TABLE t1 ( +project_number varchar(50) NOT NULL, +PRIMARY KEY (project_number) +) ENGINE=MyISAM; +INSERT INTO t1 (project_number) VALUES ('aaa'),('bbb'); +CREATE TABLE t2 ( +id int(10) unsigned NOT NULL AUTO_INCREMENT, +project_number varchar(50) NOT NULL, +history_date date NOT NULL, +country varchar(50) NOT NULL, +PRIMARY KEY (id) +) ENGINE=MyISAM; +INSERT INTO t2 (id, project_number, history_date, country) VALUES +(1, 'aaa', '2014-08-09', 'france'),(2, 'aaa', '2014-09-09', 'singapore'); +CREATE TABLE t3 ( +region varchar(50) NOT NULL, +country varchar(50) NOT NULL +) ENGINE=MyISAM; +INSERT INTO t3 (region, country) VALUES ('apac', 'singapore'),('eame', 'france'); +SELECT SQL_NO_CACHE a.project_number +FROM t1 a +WHERE ( SELECT z.country +FROM t2 z +WHERE z.project_number = a.project_number AND z.history_date <= '2014-09-01' +ORDER BY z.id DESC LIMIT 1 +) IN ( +SELECT r.country +FROM t3 r +WHERE r.region = 'eame' + ); +project_number +aaa +drop table t1, t2, t3; +set optimizer_switch= @tmp_mdev6859; set optimizer_switch=@subselect_sj_tmp; diff --git a/mysql-test/r/subselect_sj_jcl6.result b/mysql-test/r/subselect_sj_jcl6.result index ba582d7e008..ed6bf8bc11b 100644 --- a/mysql-test/r/subselect_sj_jcl6.result +++ b/mysql-test/r/subselect_sj_jcl6.result @@ -3011,6 +3011,71 @@ explain select 1 from t1 where _cp932 "1" in (select '1' from t1); ERROR HY000: Illegal mix of collations (cp932_japanese_ci,COERCIBLE) and (latin1_swedish_ci,COERCIBLE) for operation '=' drop table t1; +# +# MDEV-7823: Server crashes in next_depth_first_tab on nested IN clauses with SQ inside +# +set @tmp_mdev7823=@@optimizer_switch; +set optimizer_switch=default; +CREATE TABLE t1 (f1 INT); +INSERT INTO t1 VALUES (1); +CREATE TABLE t2 (f2 INT, KEY(f2)); +INSERT INTO t2 VALUES (8),(0); +CREATE TABLE t3 (f3 INT); +INSERT INTO t3 VALUES (1),(2); +CREATE TABLE t4 (f4 INT); +INSERT INTO t4 VALUES (0),(5); +explain +SELECT * FROM t1, t2, t3 WHERE f2 IN ( f1 IN ( SELECT f4 FROM t4 ) ); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 system NULL NULL NULL NULL 1 +1 PRIMARY t2 ref f2 f2 5 const 0 Using where; Using index +1 PRIMARY t3 ALL NULL NULL NULL NULL 2 Using join buffer (flat, BNL join) +2 DEPENDENT SUBQUERY t4 ALL NULL NULL NULL NULL 2 Using where +SELECT * FROM t1, t2, t3 WHERE f2 IN ( f1 IN ( SELECT f4 FROM t4 ) ); +f1 f2 f3 +1 0 1 +1 0 2 +drop table t1,t2,t3,t4; +set optimizer_switch= @tmp_mdev7823; +# +# MDEV-6859: scalar subqueries in a comparison produced unexpected result +# +set @tmp_mdev6859=@@optimizer_switch; +set optimizer_switch=default; +CREATE TABLE t1 ( +project_number varchar(50) NOT NULL, +PRIMARY KEY (project_number) +) ENGINE=MyISAM; +INSERT INTO t1 (project_number) VALUES ('aaa'),('bbb'); +CREATE TABLE t2 ( +id int(10) unsigned NOT NULL AUTO_INCREMENT, +project_number varchar(50) NOT NULL, +history_date date NOT NULL, +country varchar(50) NOT NULL, +PRIMARY KEY (id) +) ENGINE=MyISAM; +INSERT INTO t2 (id, project_number, history_date, country) VALUES +(1, 'aaa', '2014-08-09', 'france'),(2, 'aaa', '2014-09-09', 'singapore'); +CREATE TABLE t3 ( +region varchar(50) NOT NULL, +country varchar(50) NOT NULL +) ENGINE=MyISAM; +INSERT INTO t3 (region, country) VALUES ('apac', 'singapore'),('eame', 'france'); +SELECT SQL_NO_CACHE a.project_number +FROM t1 a +WHERE ( SELECT z.country +FROM t2 z +WHERE z.project_number = a.project_number AND z.history_date <= '2014-09-01' +ORDER BY z.id DESC LIMIT 1 +) IN ( +SELECT r.country +FROM t3 r +WHERE r.region = 'eame' + ); +project_number +aaa +drop table t1, t2, t3; +set optimizer_switch= @tmp_mdev6859; set optimizer_switch=@subselect_sj_tmp; # # BUG#49129: Wrong result with IN-subquery with join_cache_level=6 and firstmatch=off diff --git a/mysql-test/r/view.result b/mysql-test/r/view.result index aa67013757f..96c35d9dcb3 100644 --- a/mysql-test/r/view.result +++ b/mysql-test/r/view.result @@ -2422,28 +2422,28 @@ CREATE VIEW v1 AS SELECT id FROM t1; OPTIMIZE TABLE v1; Table Op Msg_type Msg_text test.v1 optimize Error 'test.v1' is not BASE TABLE -test.v1 optimize error Corrupt +test.v1 optimize status Operation failed ANALYZE TABLE v1; Table Op Msg_type Msg_text test.v1 analyze Error 'test.v1' is not BASE TABLE -test.v1 analyze error Corrupt +test.v1 analyze status Operation failed REPAIR TABLE v1; Table Op Msg_type Msg_text test.v1 repair Error 'test.v1' is not BASE TABLE -test.v1 repair error Corrupt +test.v1 repair status Operation failed DROP TABLE t1; OPTIMIZE TABLE v1; Table Op Msg_type Msg_text test.v1 optimize Error 'test.v1' is not BASE TABLE -test.v1 optimize error Corrupt +test.v1 optimize status Operation failed ANALYZE TABLE v1; Table Op Msg_type Msg_text test.v1 analyze Error 'test.v1' is not BASE TABLE -test.v1 analyze error Corrupt +test.v1 analyze status Operation failed REPAIR TABLE v1; Table Op Msg_type Msg_text test.v1 repair Error 'test.v1' is not BASE TABLE -test.v1 repair error Corrupt +test.v1 repair status Operation failed DROP VIEW v1; create definer = current_user() sql security invoker view v1 as select 1; show create view v1; @@ -5510,6 +5510,14 @@ execute stmt; deallocate prepare stmt; drop view v1,v2; drop table `t1`; +create table t1 (a int, b int); +create view v1 as select a+b from t1; +alter table v1 check partition p1; +Table Op Msg_type Msg_text +test.v1 check Error 'test.v1' is not BASE TABLE +test.v1 check status Operation failed +drop view v1; +drop table t1; # ----------------------------------------------------------------- # -- End of 5.5 tests. # ----------------------------------------------------------------- |