diff options
author | halfspawn <j.brauge@qualiac.com> | 2018-10-09 10:20:49 +0200 |
---|---|---|
committer | Alexander Barkov <bar@mariadb.com> | 2018-10-18 14:13:00 +0400 |
commit | 67f06cadc3e340b707cae3925d59e2e209ca87a8 (patch) | |
tree | 5c33b1b76635b03e8e2a009051946232455a128e /mysql-test/main | |
parent | 981a81090f1e57ad6b852cc69b353d1718f14a1e (diff) | |
download | mariadb-git-67f06cadc3e340b707cae3925d59e2e209ca87a8.tar.gz |
MDEV-17359 Concatenation operator || in like expression
Diffstat (limited to 'mysql-test/main')
-rw-r--r-- | mysql-test/main/func_like.result | 117 | ||||
-rw-r--r-- | mysql-test/main/func_like.test | 50 |
2 files changed, 167 insertions, 0 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 FROM t1 ORDER BY c2; +c1 c2 c2<<c1 2 LIKE c2<<c1 +1 1 2 1 +1 2 4 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 1 1 +SELECT c1, c2, c1+c2, 2 LIKE c1+c2 FROM t1 ORDER BY c2; +c1 c2 c1+c2 2 LIKE c1+c2 +1 1 2 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 0 0 +1 2 -1 1 +SELECT c1, c2, c1*c2, 2 LIKE c1*c2 FROM t1 ORDER BY c2; +c1 c2 c1*c2 2 LIKE c1*c2 +1 1 1 0 +1 2 2 1 +SELECT c1, c2, c1/c2, 0.5000 LIKE c1/c2 FROM t1 ORDER BY c2; +c1 c2 c1/c2 0.5000 LIKE c1/c2 +1 1 1.0000 0 +1 2 0.5000 1 +SELECT c1, c2, c1 DIV c2, 0 LIKE c1 DIV c2 FROM t1 ORDER BY c2; +c1 c2 c1 DIV c2 0 LIKE c1 DIV c2 +1 1 1 0 +1 2 0 1 +SELECT c1, c2, c1 MOD c2, 0 LIKE c1 MOD c2 FROM t1 ORDER BY c2; +c1 c2 c1 MOD c2 0 LIKE c1 MOD c2 +1 1 0 1 +1 2 1 0 +CREATE VIEW v1 AS +SELECT 1 LIKE c1|c2, 1 LIKE c1&c2, 1 LIKE c2>>c1, 2 LIKE c2<<c1, +1 LIKE c1||c2, 2 LIKE c1+c2, -1 LIKE c1-c2, 2 LIKE c1*c2, +0.5000 LIKE c1/c2, 0 LIKE c1 DIV c2, 0 LIKE c1 MOD c2 +FROM t1 ORDER BY c2; +SELECT * FROM v1; +1 LIKE c1|c2 1 LIKE c1&c2 1 LIKE c2>>c1 2 LIKE c2<<c1 1 LIKE c1||c2 2 LIKE c1+c2 -1 LIKE c1-c2 2 LIKE c1*c2 0.5000 LIKE c1/c2 0 LIKE c1 DIV c2 0 LIKE c1 MOD c2 +1 1 0 1 1 1 0 0 0 0 1 +0 0 1 0 1 0 1 1 1 1 0 +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 2 100.00 Using filesort +Warnings: +Note 1003 select 1 like `test`.`t1`.`c1` | `test`.`t1`.`c2` AS `1 LIKE c1|c2`,1 like `test`.`t1`.`c1` & `test`.`t1`.`c2` AS `1 LIKE c1&c2`,1 like `test`.`t1`.`c2` >> `test`.`t1`.`c1` AS `1 LIKE c2>>c1`,2 like `test`.`t1`.`c2` << `test`.`t1`.`c1` AS `2 LIKE c2<<c1`,1 like `test`.`t1`.`c1` or `test`.`t1`.`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<<c1 FROM t1 ORDER BY c2; +SELECT c1, c2, c1||c2, 1 LIKE c1||c2 FROM t1 ORDER BY c2; +SELECT c1, c2, c1+c2, 2 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, c1*c2, 2 LIKE c1*c2 FROM t1 ORDER BY c2; +SELECT c1, c2, c1/c2, 0.5000 LIKE c1/c2 FROM t1 ORDER BY c2; +SELECT c1, c2, c1 DIV c2, 0 LIKE c1 DIV c2 FROM t1 ORDER BY c2; +SELECT c1, c2, c1 MOD c2, 0 LIKE c1 MOD c2 FROM t1 ORDER BY c2; + +CREATE VIEW v1 AS +SELECT 1 LIKE c1|c2, 1 LIKE c1&c2, 1 LIKE c2>>c1, 2 LIKE c2<<c1, + 1 LIKE c1||c2, 2 LIKE c1+c2, -1 LIKE c1-c2, 2 LIKE c1*c2, + 0.5000 LIKE c1/c2, 0 LIKE c1 DIV c2, 0 LIKE c1 MOD c2 + FROM t1 ORDER BY c2; + +SELECT * FROM v1; +EXPLAIN EXTENDED SELECT * FROM v1; +DROP VIEW v1; +DROP TABLE t1; |