mariadb/mysql-test/suite/compat/oracle/t/func_concat.test

#
# Testing CONCAT with null values
#

SET sql_mode=ORACLE;

EXPLAIN EXTENDED SELECT 'a'||'b'||'c';
EXPLAIN EXTENDED SELECT CONCAT('a'||'b'||'c');

SELECT ''   || '';
SELECT ''   || 'b';
SELECT ''   || NULL;
SELECT 'a'  || '';
SELECT 'a'  || 'b';
SELECT 'a'  || NULL;
SELECT NULL || '';
SELECT NULL || 'b';
SELECT NULL || NULL;

SELECT ''   || ''    || '';
SELECT ''   || ''    || 'c';
SELECT ''   || ''    || NULL;
SELECT ''   || 'b'   || '';
SELECT ''   || 'b'   || 'c';
SELECT ''   || 'b'   || NULL;
SELECT ''   || NULL  || '';
SELECT ''   || NULL  || 'c';
SELECT ''   || NULL  || NULL;

SELECT 'a'  || ''    || '';
SELECT 'a'  || ''    || 'c';
SELECT 'a'  || ''    || NULL;
SELECT 'a'  || 'b'   || '';
SELECT 'a'  || 'b'   || 'c';
SELECT 'a'  || 'b'   || NULL;
SELECT 'a'  || NULL  || '';
SELECT 'a'  || NULL  || 'c';
SELECT 'a'  || NULL  || NULL;

SELECT NULL || ''    || '';
SELECT NULL || ''    || 'c';
SELECT NULL || ''    || NULL;
SELECT NULL || 'b'   || '';
SELECT NULL || 'b'   || 'c';
SELECT NULL || 'b'   || NULL;
SELECT NULL || NULL  || '';
SELECT NULL || NULL  || 'c';
SELECT NULL || NULL  || NULL;

CREATE TABLE t1 (a VARCHAR(10), b VARCHAR(10), c VARCHAR(10));

INSERT INTO t1 VALUES ('',   '',   '');
INSERT INTO t1 VALUES ('',   '',   'c');
INSERT INTO t1 VALUES ('',   '',   NULL);
INSERT INTO t1 VALUES ('',   'b',  '');
INSERT INTO t1 VALUES ('',   'b',  'c');
INSERT INTO t1 VALUES ('',   'b',  NULL);
INSERT INTO t1 VALUES ('',   NULL, '');
INSERT INTO t1 VALUES ('',   NULL, 'c');
INSERT INTO t1 VALUES ('',   NULL, NULL);

INSERT INTO t1 VALUES ('a',  '',   '');
INSERT INTO t1 VALUES ('a',  '',   'c');
INSERT INTO t1 VALUES ('a',  '',   NULL);
INSERT INTO t1 VALUES ('a',  'b',  '');
INSERT INTO t1 VALUES ('a',  'b',  'c');
INSERT INTO t1 VALUES ('a',  'b',  NULL);
INSERT INTO t1 VALUES ('a',  NULL, '');
INSERT INTO t1 VALUES ('a',  NULL, 'c');
INSERT INTO t1 VALUES ('a',  NULL, NULL);

INSERT INTO t1 VALUES (NULL, '',   '');
INSERT INTO t1 VALUES (NULL, '',   'c');
INSERT INTO t1 VALUES (NULL, '',   NULL);
INSERT INTO t1 VALUES (NULL, 'b',  '');
INSERT INTO t1 VALUES (NULL, 'b',  'c');
INSERT INTO t1 VALUES (NULL, 'b',  NULL);
INSERT INTO t1 VALUES (NULL, NULL, '');
INSERT INTO t1 VALUES (NULL, NULL, 'c');
INSERT INTO t1 VALUES (NULL, NULL, NULL);

SELECT LENGTH(a||b||c), a||b||c FROM t1 ORDER BY a,b,c;
SELECT LENGTH(CONCAT(a||b||c)), CONCAT(a||b||c) FROM t1 ORDER BY a,b,c;

DROP TABLE t1;

--echo #
--echo # MDEV-12478 CONCAT function inside view casts values incorrectly with Oracle sql_mode
--echo #

SET sql_mode=ORACLE;
CREATE VIEW v1 AS SELECT 'foo'||NULL||'bar' AS test;
SHOW CREATE VIEW v1;
SELECT * FROM v1;
SET sql_mode=DEFAULT;
SHOW CREATE VIEW v1;
SELECT * FROM v1;
DROP VIEW v1;

SET sql_mode=DEFAULT;
CREATE VIEW v1 AS SELECT CONCAT('foo',NULL,'bar') AS test;
SHOW CREATE VIEW v1;
SELECT * FROM v1;
SET sql_mode=ORACLE;
SHOW CREATE VIEW v1;
SELECT * FROM v1;
DROP VIEW v1;

SET sql_mode=DEFAULT;
CREATE VIEW v1 AS SELECT '0'||'1' AS test;
SHOW CREATE VIEW v1;
SELECT * FROM v1;
SET sql_mode=ORACLE;
SHOW CREATE VIEW v1;
SELECT * FROM v1;
DROP VIEW v1;


--echo #
--echo # MDEV-16186 Concatenation operator || returns wrong results in sql_mode=ORACLE
--echo #

# Concatenation operator || has the same precedence with +
# (stronger than << and weaker than * ^)

SELECT -1<<1||1 AS a FROM DUAL;
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;

SELECT -1+1||1 AS a FROM DUAL;
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;

SELECT 1*1||-1 AS a FROM DUAL;
SELECT 1||1*-1 AS a FROM DUAL;

EXPLAIN EXTENDED SELECT 1*1||-1 AS a FROM DUAL;
EXPLAIN EXTENDED SELECT 1||1*-1 AS a FROM DUAL;

SELECT -1^1||1 AS a FROM DUAL;
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;


--echo #
--echo # Start of 10.6 tests
--echo #

--echo #
--echo # MDEV-31910 ASAN memcpy-param-overlap upon CONCAT in ORACLE mode
--echo #

SET SQL_MODE= ORACLE;
SELECT CONCAT(SUBSTR(123 FROM 2));

--echo #
--echo # End of 10.6 tests
--echo #