From 67f06cadc3e340b707cae3925d59e2e209ca87a8 Mon Sep 17 00:00:00 2001 From: halfspawn Date: Tue, 9 Oct 2018 10:20:49 +0200 Subject: MDEV-17359 Concatenation operator || in like expression --- mysql-test/main/func_like.result | 117 +++++++++++++++++++++ mysql-test/main/func_like.test | 50 +++++++++ .../suite/compat/oracle/r/func_concat.result | 69 ++++++++++++ mysql-test/suite/compat/oracle/t/func_concat.test | 36 +++++++ sql/sql_yacc.yy | 4 +- sql/sql_yacc_ora.yy | 4 +- 6 files changed, 276 insertions(+), 4 deletions(-) diff --git a/mysql-test/main/func_like.result b/mysql-test/main/func_like.result index 0ba8e41f164..06a549e94b2 100644 --- a/mysql-test/main/func_like.result +++ b/mysql-test/main/func_like.result @@ -289,3 +289,120 @@ a b c d 3 f_ 1 0 1 3 f\_ 0 1 0 drop table t1; +# +# MDEV-17359 - Extend expression supported by like (| & << >> || + - * / DIV MOD ^ ) +# +SELECT 1 LIKE +1; +1 LIKE +1 +1 +SELECT -1 LIKE -1; +-1 LIKE -1 +1 +SELECT 1 LIKE (1); +1 LIKE (1) +1 +SELECT 1 LIKE 1|2, 3 LIKE 1|2; +1 LIKE 1|2 3 LIKE 1|2 +0 1 +SELECT 1 LIKE 3&2, 2 LIKE 3&2; +1 LIKE 3&2 2 LIKE 3&2 +0 1 +SELECT 1 LIKE 1>>0, 1 LIKE 1>>1 , 64 LIKE 256>>2; +1 LIKE 1>>0 1 LIKE 1>>1 64 LIKE 256>>2 +1 0 1 +SELECT 1 LIKE 1<<0, 1 LIKE 0<<2, 32 LIKE 1<<5; +1 LIKE 1<<0 1 LIKE 0<<2 32 LIKE 1<<5 +1 0 1 +SELECT 1 LIKE 1||2, 1 LIKE 0||2; +1 LIKE 1||2 1 LIKE 0||2 +1 1 +SELECT 2 LIKE 1+1, 2.0 LIKE 1+1.0, 2 LIKE 1+1.0, 1+1 LIKE 2, 1+1 LIKE 0+2; +2 LIKE 1+1 2.0 LIKE 1+1.0 2 LIKE 1+1.0 1+1 LIKE 2 1+1 LIKE 0+2 +1 1 0 1 1 +SELECT 0 LIKE 1-1, 2.0 LIKE 3-1.0, 2 LIKE 3-1.0, 2-1 LIKE 1, 3-1 LIKE 4-1; +0 LIKE 1-1 2.0 LIKE 3-1.0 2 LIKE 3-1.0 2-1 LIKE 1 3-1 LIKE 4-1 +1 1 0 1 0 +SELECT 1 LIKE 1*1, 2.0 LIKE 2*1.0, 2 LIKE 2*1.0, 2*1 LIKE 2, 2*3 LIKE 6*1; +1 LIKE 1*1 2.0 LIKE 2*1.0 2 LIKE 2*1.0 2*1 LIKE 2 2*3 LIKE 6*1 +1 1 0 1 1 +SELECT 1 LIKE 1/1, 1.0000 LIKE 1/1, 1.0000 LIKE 1/1.000000, 1.000000 LIKE 1.0/1.000000, 1/1 like 1/1; +1 LIKE 1/1 1.0000 LIKE 1/1 1.0000 LIKE 1/1.000000 1.000000 LIKE 1.0/1.000000 1/1 like 1/1 +0 1 1 0 1 +SELECT 1 LIKE 1 DIV 1, 1 LIKE 1.0 DIV 1.0 ; +1 LIKE 1 DIV 1 1 LIKE 1.0 DIV 1.0 +1 1 +SELECT 2 LIKE 10 MOD 8, 1.9 LIKE 10 MOD 8.1, 1.9 LIKE 10 MOD 8.10 ; +2 LIKE 10 MOD 8 1.9 LIKE 10 MOD 8.1 1.9 LIKE 10 MOD 8.10 +1 1 0 +SELECT 1 LIKE CAST(1 AS CHAR(10)); +1 LIKE CAST(1 AS CHAR(10)) +1 +SELECT 1 LIKE CASE WHEN 1=1 THEN '1' ELSE '0' END; +1 LIKE CASE WHEN 1=1 THEN '1' ELSE '0' END +1 +SELECT 1 LIKE COALESCE(1+0, 1); +1 LIKE COALESCE(1+0, 1) +1 +CREATE TABLE t1(c1 INTEGER, c2 INTEGER); +INSERT INTO t1 VALUES(1,1); +INSERT INTO t1 VALUES(1,2); +SELECT c1, c2, c1|c2, 1 LIKE c1|c2 FROM t1 ORDER BY c2; +c1 c2 c1|c2 1 LIKE c1|c2 +1 1 1 1 +1 2 3 0 +SELECT c1, c2, c1&c2, 1 LIKE c1&c2 FROM t1 ORDER BY c2; +c1 c2 c1&c2 1 LIKE c1&c2 +1 1 1 1 +1 2 0 0 +SELECT c1, c2, c2>>c1, 1 LIKE c2>>c1 FROM t1 ORDER BY c2; +c1 c2 c2>>c1 1 LIKE c2>>c1 +1 1 0 0 +1 2 1 1 +SELECT c1, c2, c2<>c1, 2 LIKE c2<>c1 2 LIKE c2<> `test`.`t1`.`c1` AS `1 LIKE c2>>c1`,2 like `test`.`t1`.`c2` << `test`.`t1`.`c1` AS `2 LIKE c2< 0 AS `1 LIKE c1||c2`,2 like `test`.`t1`.`c1` + `test`.`t1`.`c2` AS `2 LIKE c1+c2`,-1 like `test`.`t1`.`c1` - `test`.`t1`.`c2` AS `-1 LIKE c1-c2`,2 like `test`.`t1`.`c1` * `test`.`t1`.`c2` AS `2 LIKE c1*c2`,0.5000 like `test`.`t1`.`c1` / `test`.`t1`.`c2` AS `0.5000 LIKE c1/c2`,0 like `test`.`t1`.`c1` DIV `test`.`t1`.`c2` AS `0 LIKE c1 DIV c2`,0 like `test`.`t1`.`c1` MOD `test`.`t1`.`c2` AS `0 LIKE c1 MOD c2` from `test`.`t1` order by `test`.`t1`.`c2` +DROP VIEW v1; +DROP TABLE t1; diff --git a/mysql-test/main/func_like.test b/mysql-test/main/func_like.test index 5026bb76aa3..cb50fb91879 100644 --- a/mysql-test/main/func_like.test +++ b/mysql-test/main/func_like.test @@ -207,3 +207,53 @@ insert t1 (a) values ('3 f_'), ('3 f\_'); set sql_mode=default; select * from t1; drop table t1; + +--echo # +--echo # MDEV-17359 - Extend expression supported by like (| & << >> || + - * / DIV MOD ^ ) +--echo # + +SELECT 1 LIKE +1; +SELECT -1 LIKE -1; +SELECT 1 LIKE (1); +SELECT 1 LIKE 1|2, 3 LIKE 1|2; +SELECT 1 LIKE 3&2, 2 LIKE 3&2; +SELECT 1 LIKE 1>>0, 1 LIKE 1>>1 , 64 LIKE 256>>2; +SELECT 1 LIKE 1<<0, 1 LIKE 0<<2, 32 LIKE 1<<5; +SELECT 1 LIKE 1||2, 1 LIKE 0||2; +SELECT 2 LIKE 1+1, 2.0 LIKE 1+1.0, 2 LIKE 1+1.0, 1+1 LIKE 2, 1+1 LIKE 0+2; +SELECT 0 LIKE 1-1, 2.0 LIKE 3-1.0, 2 LIKE 3-1.0, 2-1 LIKE 1, 3-1 LIKE 4-1; +SELECT 1 LIKE 1*1, 2.0 LIKE 2*1.0, 2 LIKE 2*1.0, 2*1 LIKE 2, 2*3 LIKE 6*1; +SELECT 1 LIKE 1/1, 1.0000 LIKE 1/1, 1.0000 LIKE 1/1.000000, 1.000000 LIKE 1.0/1.000000, 1/1 like 1/1; +SELECT 1 LIKE 1 DIV 1, 1 LIKE 1.0 DIV 1.0 ; +SELECT 2 LIKE 10 MOD 8, 1.9 LIKE 10 MOD 8.1, 1.9 LIKE 10 MOD 8.10 ; + +SELECT 1 LIKE CAST(1 AS CHAR(10)); +SELECT 1 LIKE CASE WHEN 1=1 THEN '1' ELSE '0' END; +SELECT 1 LIKE COALESCE(1+0, 1); + +CREATE TABLE t1(c1 INTEGER, c2 INTEGER); +INSERT INTO t1 VALUES(1,1); +INSERT INTO t1 VALUES(1,2); + +SELECT c1, c2, c1|c2, 1 LIKE c1|c2 FROM t1 ORDER BY c2; +SELECT c1, c2, c1&c2, 1 LIKE c1&c2 FROM t1 ORDER BY c2; +SELECT c1, c2, c2>>c1, 1 LIKE c2>>c1 FROM t1 ORDER BY c2; +SELECT c1, c2, c2<>c1, 2 LIKE c2<(concat_operator_oracle('%','b')) order by "test"."t1"."ord" +SELECT c1 FROM t1 WHERE c1 LIKE c2||'%'||'c' ORDER BY ord; +c1 +abc +EXPLAIN EXTENDED SELECT c1 FROM t1 WHERE c1 LIKE c2||'%'||'c' ORDER BY ord; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using where; Using filesort +Warnings: +Note 1003 select "test"."t1"."c1" AS "c1" from "test"."t1" where "test"."t1"."c1" like concat_operator_oracle(concat_operator_oracle("test"."t1"."c2",'%'),'c') order by "test"."t1"."ord" +SELECT 'x' FROM t1 WHERE c1||c2 LIKE 'aa%'; +x +x +EXPLAIN EXTENDED SELECT 'x' FROM t1 WHERE c1||c2 LIKE 'aa%'; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using where +Warnings: +Note 1003 select 'x' AS "x" from "test"."t1" where concat_operator_oracle("test"."t1"."c1","test"."t1"."c2") like 'aa%' +SELECT 'x' FROM t1 WHERE c1||c2 LIKE c2||c1; +x +x +EXPLAIN EXTENDED SELECT 'x' FROM t1 WHERE c1||c2 LIKE c2||c1; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using where +Warnings: +Note 1003 select 'x' AS "x" from "test"."t1" where concat_operator_oracle("test"."t1"."c1","test"."t1"."c2") like concat_operator_oracle("test"."t1"."c2","test"."t1"."c1") +CREATE VIEW v1 AS SELECT c1, c2, c1 LIKE c2||'_' FROM t1 ORDER BY ord; +SELECT * FROM v1; +c1 c2 c1 LIKE c2||'_' +a ab 0 +ab ab 0 +abc ab 1 +EXPLAIN EXTENDED SELECT * FROM v1; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using filesort +Warnings: +Note 1003 select "test"."t1"."c1" AS "c1","test"."t1"."c2" AS "c2","test"."t1"."c1" like concat_operator_oracle("test"."t1"."c2",'_') AS "c1 LIKE c2||'_'" from "test"."t1" order by "test"."t1"."ord" +DROP VIEW v1; +DROP TABLE t1; diff --git a/mysql-test/suite/compat/oracle/t/func_concat.test b/mysql-test/suite/compat/oracle/t/func_concat.test index 7f9fec4f2fe..5a613242e87 100644 --- a/mysql-test/suite/compat/oracle/t/func_concat.test +++ b/mysql-test/suite/compat/oracle/t/func_concat.test @@ -146,3 +146,39 @@ SELECT -1||0^1 AS a FROM DUAL; EXPLAIN EXTENDED SELECT -1^1||1 AS a FROM DUAL; EXPLAIN EXTENDED SELECT -1||0^1 AS a FROM DUAL; + + +--echo # +--echo # MDEV-17359 Concatenation operator || in like expression failed in sql_mode=ORACLE +--echo # + +SELECT 'abc' LIKE 'a'||'%'; +EXPLAIN EXTENDED SELECT 'abc' LIKE 'a'||'%'; + +SELECT 'x' FROM DUAL WHERE 11 LIKE 1||1; +SELECT 'x' FROM DUAL WHERE 1||1 LIKE 11; +SELECT 'x' FROM DUAL WHERE 1||1 LIKE 1||1; + +CREATE TABLE t1 (c1 VARCHAR(10),c2 VARCHAR(10), ord INTEGER); +INSERT INTO t1 VALUES ('a', 'ab' ,1); +INSERT INTO t1 VALUES ('ab', 'ab', 2); +INSERT INTO t1 VALUES ('abc', 'ab', 3); + +SELECT c1 FROM t1 WHERE c1 LIKE '%'||'b' ORDER BY ord; +EXPLAIN EXTENDED SELECT c1 FROM t1 WHERE c1 LIKE '%'||'b' ORDER BY ord; + +SELECT c1 FROM t1 WHERE c1 LIKE c2||'%'||'c' ORDER BY ord; +EXPLAIN EXTENDED SELECT c1 FROM t1 WHERE c1 LIKE c2||'%'||'c' ORDER BY ord; + +SELECT 'x' FROM t1 WHERE c1||c2 LIKE 'aa%'; +EXPLAIN EXTENDED SELECT 'x' FROM t1 WHERE c1||c2 LIKE 'aa%'; + +SELECT 'x' FROM t1 WHERE c1||c2 LIKE c2||c1; +EXPLAIN EXTENDED SELECT 'x' FROM t1 WHERE c1||c2 LIKE c2||c1; + +CREATE VIEW v1 AS SELECT c1, c2, c1 LIKE c2||'_' FROM t1 ORDER BY ord; +SELECT * FROM v1; +EXPLAIN EXTENDED SELECT * FROM v1; + +DROP VIEW v1; +DROP TABLE t1; diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy index 2669538761b..caa070090b6 100644 --- a/sql/sql_yacc.yy +++ b/sql/sql_yacc.yy @@ -9712,14 +9712,14 @@ predicate: if (unlikely($$ == NULL)) MYSQL_YYABORT; } - | bit_expr LIKE mysql_concatenation_expr opt_escape + | bit_expr LIKE bit_expr opt_escape { $$= new (thd->mem_root) Item_func_like(thd, $1, $3, $4, Lex->escape_used); if (unlikely($$ == NULL)) MYSQL_YYABORT; } - | bit_expr not LIKE mysql_concatenation_expr opt_escape + | bit_expr not LIKE bit_expr opt_escape { Item *item= new (thd->mem_root) Item_func_like(thd, $1, $4, $5, Lex->escape_used); diff --git a/sql/sql_yacc_ora.yy b/sql/sql_yacc_ora.yy index 97245259fec..aaac4ba3e54 100644 --- a/sql/sql_yacc_ora.yy +++ b/sql/sql_yacc_ora.yy @@ -9784,14 +9784,14 @@ predicate: if (unlikely($$ == NULL)) MYSQL_YYABORT; } - | bit_expr LIKE mysql_concatenation_expr opt_escape + | bit_expr LIKE bit_expr opt_escape { $$= new (thd->mem_root) Item_func_like(thd, $1, $3, $4, Lex->escape_used); if (unlikely($$ == NULL)) MYSQL_YYABORT; } - | bit_expr not LIKE mysql_concatenation_expr opt_escape + | bit_expr not LIKE bit_expr opt_escape { Item *item= new (thd->mem_root) Item_func_like(thd, $1, $4, $5, Lex->escape_used); -- cgit v1.2.1 From abbf169f52e3ec598c37848e11e0d6351cbfb52c Mon Sep 17 00:00:00 2001 From: =?UTF-8?q?Marko=20M=C3=A4kel=C3=A4?= Date: Fri, 19 Oct 2018 09:28:21 +0300 Subject: Remove unused TIMETPF --- storage/innobase/include/univ.i | 3 --- 1 file changed, 3 deletions(-) diff --git a/storage/innobase/include/univ.i b/storage/innobase/include/univ.i index 1d2e0f37dbe..453f15b8784 100644 --- a/storage/innobase/include/univ.i +++ b/storage/innobase/include/univ.i @@ -453,7 +453,6 @@ typedef ssize_t lint; # define INT64PF "%lld" # define UINT64scan "llu" # define UINT64PFx "%016llx" -# define TIMETPF "%ld" #elif defined __APPLE__ /* Apple prefers to call the 64-bit types 'long long' in both 32-bit and 64-bit environments. */ @@ -461,14 +460,12 @@ in both 32-bit and 64-bit environments. */ # define INT64PF "%lld" # define UINT64scan "llu" # define UINT64PFx "%016llx" -# define TIMETPF "%" PRIdFAST32 #else /* Use the integer types and formatting strings defined in the C99 standard. */ # define UINT32PF "%" PRIu32 # define INT64PF "%" PRId64 # define UINT64scan PRIu64 # define UINT64PFx "%016" PRIx64 -# define TIMETPF "%" PRIdFAST32 #endif #ifdef UNIV_INNOCHECKSUM -- cgit v1.2.1 From ab1ce2204e959bea596817494e932754ab5cbe88 Mon Sep 17 00:00:00 2001 From: =?UTF-8?q?Marko=20M=C3=A4kel=C3=A4?= Date: Fri, 19 Oct 2018 09:29:28 +0300 Subject: MDEV-17466: Remove the debug assertion This reverts commit 2d4075e1d9dba3dd8503d65fdb145a6dcc056c51 where the debug assertion was added. There seems to be a potential problem in the purge of indexes that depend on virtual columns. Ultimately, we should change the InnoDB undo log format so that all actual secondary index keys are stored there, also for virtual or spatial indexes. In that way, purge and rollback would be more straightforward. --- storage/innobase/row/row0row.cc | 2 -- 1 file changed, 2 deletions(-) diff --git a/storage/innobase/row/row0row.cc b/storage/innobase/row/row0row.cc index 3f4308d4bed..ad4991fb4e9 100644 --- a/storage/innobase/row/row0row.cc +++ b/storage/innobase/row/row0row.cc @@ -127,8 +127,6 @@ row_build_index_entry_low( ut_ad(dfield_is_null(dfield2) || dfield_get_len(dfield2) == 0 || dfield2->data); - ut_ad(dfield2->type.mtype != DATA_MISSING - || !index->is_committed()); } else { dfield2 = dtuple_get_nth_field(row, col_no); ut_ad(dfield_get_type(dfield2)->mtype == DATA_MISSING -- cgit v1.2.1