--! qt:dataset:src
set hive.fetch.task.conversion=more;

DESCRIBE FUNCTION substr;
DESCRIBE FUNCTION EXTENDED substr;

SELECT
  substr(null, 1), substr(null, 1, 1),
  substr('ABC', null), substr('ABC', null, 1),
  substr('ABC', 1, null)
FROM src tablesample (1 rows);

SELECT
  substr('ABC', 1, 0), substr('ABC', 1, -1), substr('ABC', 2, -100),
  substr('ABC', 4), substr('ABC', 4, 100),
  substr('ABC', -4), substr('ABC', -4, 100),
  substr('ABC', 100), substr('ABC', 100, 100),
  substr('ABC', -100), substr('ABC', -100, 100),
  substr('ABC', 2147483647), substr('ABC', 2147483647, 2147483647)
FROM src tablesample (1 rows);

SELECT
  substr('ABCDEFG', 3, 4), substr('ABCDEFG', -5, 4),
  substr('ABCDEFG', 3), substr('ABCDEFG', -5),
  substr('ABC', 0), substr('ABC', 1), substr('ABC', 2), substr('ABC', 3),
  substr('ABC', 1, 2147483647), substr('ABC', 2, 2147483647),
  substr('A', 0), substr('A', 1), substr('A', -1)
FROM src tablesample (1 rows);

SELECT
  substr('ABC', 0, 1), substr('ABC', 0, 2), substr('ABC', 0, 3), substr('ABC', 0, 4),
  substr('ABC', 1, 1), substr('ABC', 1, 2), substr('ABC', 1, 3), substr('ABC', 1, 4),
  substr('ABC', 2, 1), substr('ABC', 2, 2), substr('ABC', 2, 3), substr('ABC', 2, 4),
  substr('ABC', 3, 1), substr('ABC', 3, 2), substr('ABC', 3, 3), substr('ABC', 3, 4),
  substr('ABC', 4, 1)
FROM src tablesample (1 rows);

SELECT
  substr('ABC', -1, 1), substr('ABC', -1, 2), substr('ABC', -1, 3), substr('ABC', -1, 4),
  substr('ABC', -2, 1), substr('ABC', -2, 2), substr('ABC', -2, 3), substr('ABC', -2, 4),
  substr('ABC', -3, 1), substr('ABC', -3, 2), substr('ABC', -3, 3), substr('ABC', -3, 4),
  substr('ABC', -4, 1)
FROM src tablesample (1 rows);

-- substring() is a synonim of substr(), so just perform some basic tests
SELECT
  substring('ABCDEFG', 3, 4), substring('ABCDEFG', -5, 4),
  substring('ABCDEFG', 3), substring('ABCDEFG', -5),
  substring('ABC', 0), substring('ABC', 1), substring('ABC', 2), substring('ABC', 3),
  substring('ABC', 1, 2147483647), substring('ABC', 2, 2147483647),
  substring('A', 0), substring('A', 1), substring('A', -1)
FROM src tablesample (1 rows);

-- test for binary substr
SELECT
  substr(null, 1), substr(null, 1, 1),
  substr(ABC, null), substr(ABC, null, 1),
  substr(ABC, 1, null),
  substr(ABC, 0, 1), substr(ABC, 0, 2), substr(ABC, 0, 3), substr(ABC, 0, 4),
  substr(ABC, 1, 1), substr(ABC, 1, 2), substr(ABC, 1, 3), substr(ABC, 1, 4),
  substr(ABC, 2, 1), substr(ABC, 2, 2), substr(ABC, 2, 3), substr(ABC, 2, 4),
  substr(ABC, 3, 1), substr(ABC, 3, 2), substr(ABC, 3, 3), substr(ABC, 3, 4),
  substr(ABC, 4, 1),
  substr(ABC, -1, 1), substr(ABC, -1, 2), substr(ABC, -1, 3), substr(ABC, -1, 4),
  substr(ABC, -2, 1), substr(ABC, -2, 2), substr(ABC, -2, 3), substr(ABC, -2, 4),
  substr(ABC, -3, 1), substr(ABC, -3, 2), substr(ABC, -3, 3), substr(ABC, -3, 4),
  substr(ABC, -4, 1)
FROM (
   select CAST(concat(substr(value, 1, 0), 'ABC') as BINARY) as ABC from src tablesample (1 rows)
) X;

-- test UTF-8 substr
SELECT
  substr("玩", 1),
  substr("abc 玩", 5),
  substr("abc 玩玩玩 abc", 5),
  substr("abc 玩玩玩 abc", 5, 3)
FROM src tablesample (1 rows);

SELECT
  substr('ABC', cast(1 as bigint), cast(2 as bigint)),
  substr('ABC', cast(1 as bigint))
FROM src tablesample (1 rows);

SELECT
  substr('ABC', cast(1 as bigint), cast(2147483649 as bigint))
FROM src tablesample (1 rows);

SELECT
  substr('ABC', cast(2147483649 as bigint))
FROM src tablesample (1 rows);