Merge pull request #462 from halfspawn/bb-10.2-ext

MDEV-14012 - sql_mode=Oracle: substr(): treat position 0 as position 1

2 files changed, 78 insertions, 0 deletions

diff --git a/mysql-test/suite/compat/oracle/r/func_substr.result b/mysql-test/suite/compat/oracle/r/func_substr.result
new file mode 100644
index 00000000000..eca5f480191
--- /dev/null
+++ b/mysql-test/suite/compat/oracle/r/func_substr.result
@@ -0,0 +1,50 @@
+SET sql_mode=ORACLE;
+SELECT SUBSTR('abc',2,1),SUBSTR('abc',1,1), SUBSTR('abc',0,1) FROM dual;
+SUBSTR('abc',2,1)	SUBSTR('abc',1,1)	SUBSTR('abc',0,1)
+b	a	a
+SELECT SUBSTR('abc',2),SUBSTR('abc',1), SUBSTR('abc',0) FROM dual;
+SUBSTR('abc',2)	SUBSTR('abc',1)	SUBSTR('abc',0)
+bc	abc	abc
+SELECT SUBSTR(null,2,1),SUBSTR(null,1), SUBSTR(null,0) FROM dual;
+SUBSTR(null,2,1)	SUBSTR(null,1)	SUBSTR(null,0)
+NULL	NULL	NULL
+SELECT SUBSTR('abc',-2),SUBSTR('abc',-1), SUBSTR('abc',-0) FROM dual;
+SUBSTR('abc',-2)	SUBSTR('abc',-1)	SUBSTR('abc',-0)
+bc	c	abc
+SELECT SUBSTR('abc',-2,1),SUBSTR('abc',-1,1), SUBSTR('abc',-0,1) FROM dual;
+SUBSTR('abc',-2,1)	SUBSTR('abc',-1,1)	SUBSTR('abc',-0,1)
+b	c	a
+SELECT SUBSTR('abc',null) FROM dual;
+SUBSTR('abc',null)
+NULL
+SELECT SUBSTR('abc',2,null),SUBSTR('abc',1,null), SUBSTR('abc',0,null) FROM dual;
+SUBSTR('abc',2,null)	SUBSTR('abc',1,null)	SUBSTR('abc',0,null)
+NULL	NULL	NULL
+SELECT SUBSTR('abc',2,0),SUBSTR('abc',1,0), SUBSTR('abc',0,0) FROM dual;
+SUBSTR('abc',2,0)	SUBSTR('abc',1,0)	SUBSTR('abc',0,0)
+		
+create table t1 (c1 varchar(10),start integer, length integer);
+INSERT INTO t1 VALUES ('abc', 1, 1);
+INSERT INTO t1 VALUES ('abc', 0, 1);
+INSERT INTO t1 VALUES (null, 1, 1);
+INSERT INTO t1 VALUES (null, 0, 1);
+select substr(c1,start,length) from t1;
+substr(c1,start,length)
+a
+a
+NULL
+NULL
+drop table t1;
+EXPLAIN EXTENDED SELECT SUBSTR('abc',2,1) ;
+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 substr_oracle('abc',2,1) AS "SUBSTR('abc',2,1)"
+CREATE VIEW v1 AS SELECT SUBSTR('abc',2,1) ;
+SHOW CREATE VIEW v1;
+View	Create View	character_set_client	collation_connection
+v1	CREATE VIEW "v1" AS select substr_oracle('abc',2,1) AS "SUBSTR('abc',2,1)"	latin1	latin1_swedish_ci
+SELECT * FROM v1;
+SUBSTR('abc',2,1)
+b
+DROP VIEW v1;
diff --git a/mysql-test/suite/compat/oracle/t/func_substr.test b/mysql-test/suite/compat/oracle/t/func_substr.test
new file mode 100644
index 00000000000..5d5ec78abdd
--- /dev/null
+++ b/mysql-test/suite/compat/oracle/t/func_substr.test
@@ -0,0 +1,28 @@
+#
+# MDEV-14012 - sql_mode=Oracle: substr(): treat position 0 as position 1
+#
+
+SET sql_mode=ORACLE;
+SELECT SUBSTR('abc',2,1),SUBSTR('abc',1,1), SUBSTR('abc',0,1) FROM dual;
+SELECT SUBSTR('abc',2),SUBSTR('abc',1), SUBSTR('abc',0) FROM dual;
+SELECT SUBSTR(null,2,1),SUBSTR(null,1), SUBSTR(null,0) FROM dual;
+SELECT SUBSTR('abc',-2),SUBSTR('abc',-1), SUBSTR('abc',-0) FROM dual;
+SELECT SUBSTR('abc',-2,1),SUBSTR('abc',-1,1), SUBSTR('abc',-0,1) FROM dual;
+SELECT SUBSTR('abc',null) FROM dual;
+SELECT SUBSTR('abc',2,null),SUBSTR('abc',1,null), SUBSTR('abc',0,null) FROM dual;
+SELECT SUBSTR('abc',2,0),SUBSTR('abc',1,0), SUBSTR('abc',0,0) FROM dual;
+
+create table t1 (c1 varchar(10),start integer, length integer);
+INSERT INTO t1 VALUES ('abc', 1, 1);
+INSERT INTO t1 VALUES ('abc', 0, 1);
+INSERT INTO t1 VALUES (null, 1, 1);
+INSERT INTO t1 VALUES (null, 0, 1);
+select substr(c1,start,length) from t1;
+drop table t1;
+
+EXPLAIN EXTENDED SELECT SUBSTR('abc',2,1) ;
+
+CREATE VIEW v1 AS SELECT SUBSTR('abc',2,1) ;
+SHOW CREATE VIEW v1;
+SELECT * FROM v1;
+DROP VIEW v1;