--
-- ALTER TABLE ADD COLUMN DEFAULT test
--

CREATE SCHEMA fast_default;

CREATE TABLE has_volatile(id int);
insert into has_volatile SELECT * FROM generate_series(1,10) id;

ALTER TABLE has_volatile ADD col1 int;
ALTER TABLE has_volatile ADD col2 int DEFAULT 1;
ALTER TABLE has_volatile ADD col3 timestamptz DEFAULT current_timestamp;
ALTER TABLE has_volatile ADD col4 int DEFAULT (random() * 10000)::int;


-- Test a large sample of different datatypes
drop table if not exists T;
CREATE TABLE T(pk INT NOT NULL PRIMARY KEY, c_int INT DEFAULT 1);

INSERT INTO T VALUES (1), (2);

ALTER TABLE T ADD COLUMN c_bpchar BPCHAR(5) DEFAULT 'hello',
              ALTER COLUMN c_int SET DEFAULT 2;

INSERT INTO T VALUES (3), (4);


ALTER TABLE T ADD COLUMN c_text TEXT,
              ALTER COLUMN c_bpchar SET DEFAULT 'dog';

INSERT INTO T VALUES (5), (6);

ALTER TABLE T ADD COLUMN c_date DATE DEFAULT '2016-06-02';
--             ALTER COLUMN c_text SET DEFAULT 'cat';

INSERT INTO T VALUES (7), (8);

ALTER TABLE T ADD COLUMN c_timestamp TIMESTAMP DEFAULT '2016-09-01 12:00:00',
              ADD COLUMN c_timestamp_null TIMESTAMP,
              ALTER COLUMN c_date SET DEFAULT '2010-01-01';

INSERT INTO T VALUES (9), (10);

ALTER TABLE T ALTER COLUMN c_timestamp SET DEFAULT '1970-12-31 11:12:13',
              ALTER COLUMN c_timestamp_null SET DEFAULT '2016-09-29 12:00:00';

INSERT INTO T VALUES (11), (12);

ALTER TABLE T ADD COLUMN c_small SMALLINT DEFAULT -5,
              ADD COLUMN c_small_null SMALLINT;

INSERT INTO T VALUES (13), (14);

ALTER TABLE T ADD COLUMN c_big BIGINT DEFAULT 180000000000018,
              ALTER COLUMN c_small SET DEFAULT 9,
              ALTER COLUMN c_small_null SET DEFAULT 13;

INSERT INTO T VALUES (15), (16);

ALTER TABLE T ADD COLUMN c_num NUMERIC(10,10) DEFAULT 1.00000000001,
              ALTER COLUMN c_big SET DEFAULT -9999999999999999;

INSERT INTO T VALUES (17), (18);

ALTER TABLE T ADD COLUMN c_time TIME DEFAULT '12:00:00',
              ALTER COLUMN c_num SET DEFAULT 2.000000000000002;

INSERT INTO T VALUES (19), (20);

ALTER TABLE T ALTER COLUMN c_time SET DEFAULT '23:59:59';

INSERT INTO T VALUES (21), (22);

INSERT INTO T VALUES (23), (24);

INSERT INTO T VALUES (25), (26);

ALTER TABLE T ALTER COLUMN c_bpchar    DROP DEFAULT,
              ALTER COLUMN c_date      DROP DEFAULT,
              ALTER COLUMN c_text      DROP DEFAULT,
              ALTER COLUMN c_timestamp DROP DEFAULT,
              ALTER COLUMN c_small     DROP DEFAULT,
              ALTER COLUMN c_big       DROP DEFAULT,
              ALTER COLUMN c_num       DROP DEFAULT,
              ALTER COLUMN c_time      DROP DEFAULT;

INSERT INTO T VALUES (27), (28);

SELECT pk, c_int, c_bpchar, c_text, c_date, c_timestamp,
       c_timestamp_null, c_small, c_small_null,
       c_big, c_num, c_time
FROM T ORDER BY pk;

DROP TABLE T;

-- Test expressions in the defaults
CREATE OR REPLACE FUNCTION foo(a INT) RETURNS TEXT AS $$
DECLARE res TEXT = '';
        i INT;
BEGIN
  i = 0;
  WHILE (i < a) LOOP
    res = res || chr(ascii('a') + i);
    i = i + 1;
  END LOOP;
  RETURN res;
END; $$ LANGUAGE PLPGSQL STABLE;

CREATE TABLE T(pk INT NOT NULL PRIMARY KEY, c_int INT DEFAULT LENGTH(foo(6)));

INSERT INTO T VALUES (1), (2);

ALTER TABLE T ADD COLUMN c_bpchar BPCHAR(5) DEFAULT foo(4),
              ALTER COLUMN c_int SET DEFAULT LENGTH(foo(8));

INSERT INTO T VALUES (3), (4);

ALTER TABLE T ADD COLUMN c_text TEXT  DEFAULT foo(6),
              ALTER COLUMN c_bpchar SET DEFAULT foo(3);

INSERT INTO T VALUES (5), (6);

ALTER TABLE T ADD COLUMN c_date DATE
                  DEFAULT '2016-06-02'::DATE  + LENGTH(foo(10)),
              ALTER COLUMN c_text SET DEFAULT foo(12);

INSERT INTO T VALUES (7), (8);

ALTER TABLE T ADD COLUMN c_timestamp TIMESTAMP
                  DEFAULT '2016-09-01'::DATE + LENGTH(foo(10)),
              ALTER COLUMN c_date
                  SET DEFAULT '2010-01-01'::DATE - LENGTH(foo(4));

INSERT INTO T VALUES (9), (10);

ALTER TABLE T ALTER COLUMN c_timestamp
                  SET DEFAULT '1970-12-31'::DATE + LENGTH(foo(30));

INSERT INTO T VALUES (11), (12);

ALTER TABLE T ALTER COLUMN c_int DROP DEFAULT;

INSERT INTO T VALUES (13), (14);

ALTER TABLE T ALTER COLUMN c_bpchar    DROP DEFAULT,
              ALTER COLUMN c_date      DROP DEFAULT,
              ALTER COLUMN c_text      DROP DEFAULT,
              ALTER COLUMN c_timestamp DROP DEFAULT;

INSERT INTO T VALUES (15), (16);

SELECT * FROM T;

DROP TABLE T;

DROP FUNCTION foo(INT);

-- Fall back to full rewrite for volatile expressions
CREATE TABLE T(pk INT NOT NULL PRIMARY KEY);

INSERT INTO T VALUES (1);

-- now() is stable, because it returns the transaction timestamp
ALTER TABLE T ADD COLUMN c1 TIMESTAMP DEFAULT now();

-- clock_timestamp() is volatile
ALTER TABLE T ADD COLUMN c2 TIMESTAMP DEFAULT clock_timestamp();

DROP TABLE T;

-- Simple querie
CREATE TABLE T (pk INT NOT NULL PRIMARY KEY);

INSERT INTO T SELECT * FROM generate_series(1, 10) a;

ALTER TABLE T ADD COLUMN c_bigint BIGINT NOT NULL DEFAULT -1;

INSERT INTO T SELECT b, b - 10 FROM generate_series(11, 20) a(b);

ALTER TABLE T ADD COLUMN c_text TEXT DEFAULT 'hello';

INSERT INTO T SELECT b, b - 10, (b + 10)::text FROM generate_series(21, 30) a(b);

-- WHERE clause
SELECT c_bigint, c_text FROM T WHERE c_bigint = -1 LIMIT 1;

EXPLAIN (VERBOSE TRUE, COSTS FALSE)
SELECT c_bigint, c_text FROM T WHERE c_bigint = -1 LIMIT 1;

SELECT c_bigint, c_text FROM T WHERE c_text = 'hello' LIMIT 1;

EXPLAIN (VERBOSE TRUE, COSTS FALSE) SELECT c_bigint, c_text FROM T WHERE c_text = 'hello' LIMIT 1;


-- COALESCE
SELECT COALESCE(c_bigint, pk), COALESCE(c_text, pk::text)
FROM T
ORDER BY pk LIMIT 10;

-- Aggregate function
SELECT SUM(c_bigint), MAX(c_text COLLATE "C" ), MIN(c_text COLLATE "C") FROM T;

-- ORDER BY
SELECT * FROM T ORDER BY c_bigint, c_text, pk LIMIT 10;

EXPLAIN (VERBOSE TRUE, COSTS FALSE)
SELECT * FROM T ORDER BY c_bigint, c_text, pk LIMIT 10;

-- LIMIT
SELECT * FROM T WHERE c_bigint > -1 ORDER BY c_bigint, c_text, pk LIMIT 10;

EXPLAIN (VERBOSE TRUE, COSTS FALSE)
SELECT * FROM T WHERE c_bigint > -1 ORDER BY c_bigint, c_text, pk LIMIT 10;

--  DELETE with RETURNING
DELETE FROM T WHERE pk BETWEEN 10 AND 20 RETURNING *;
-- will crash: EXPLAIN (VERBOSE TRUE, COSTS FALSE)
-- DELETE FROM T WHERE pk BETWEEN 10 AND 20 RETURNING *;

-- UPDATE
UPDATE T SET c_text = '"' || c_text || '"'  WHERE pk < 10;
SELECT * FROM T WHERE c_text LIKE '"%"' ORDER BY PK;

DROP TABLE T;


-- Combine with other DDL
CREATE TABLE T(pk INT NOT NULL PRIMARY KEY);

INSERT INTO T VALUES (1), (2);

ALTER TABLE T ADD COLUMN c_int INT NOT NULL DEFAULT -1;

INSERT INTO T VALUES (3), (4);

ALTER TABLE T ADD COLUMN c_text TEXT DEFAULT 'Hello';

INSERT INTO T VALUES (5), (6);

ALTER TABLE T ALTER COLUMN c_text SET DEFAULT 'world',
              ALTER COLUMN c_int  SET DEFAULT 1;

INSERT INTO T VALUES (7), (8);

SELECT * FROM T ORDER BY pk;

-- Add an index
CREATE INDEX i ON T(c_int, c_text);

SELECT c_text FROM T WHERE c_int = -1;

DROP TABLE T;

-- 2 new columns, both have defaults
CREATE TABLE t (id serial PRIMARY KEY, a int, b int, c int);
INSERT INTO t (a,b,c) VALUES (1,2,3);
ALTER TABLE t ADD COLUMN x int NOT NULL DEFAULT 4;
ALTER TABLE t ADD COLUMN y int NOT NULL DEFAULT 5;
SELECT * FROM t;
UPDATE t SET y = 2;
SELECT * FROM t;
DROP TABLE t;

-- 2 new columns, first has default
CREATE TABLE t (id serial PRIMARY KEY, a int, b int, c int);
INSERT INTO t (a,b,c) VALUES (1,2,3);
ALTER TABLE t ADD COLUMN x int NOT NULL DEFAULT 4;
ALTER TABLE t ADD COLUMN y int;
SELECT * FROM t;
UPDATE t SET y = 2;
SELECT * FROM t;
DROP TABLE t;

-- 2 new columns, second has default
CREATE TABLE t (id serial PRIMARY KEY, a int, b int, c int);
INSERT INTO t (a,b,c) VALUES (1,2,3);
ALTER TABLE t ADD COLUMN x int;
ALTER TABLE t ADD COLUMN y int NOT NULL DEFAULT 5;
SELECT * FROM t;
UPDATE t SET y = 2;
SELECT * FROM t;
DROP TABLE t;

-- 2 new columns, neither has default
CREATE TABLE t (id serial PRIMARY KEY, a int, b int, c int);
INSERT INTO t (a,b,c) VALUES (1,2,3);
ALTER TABLE t ADD COLUMN x int;
ALTER TABLE t ADD COLUMN y int;
SELECT * FROM t;
UPDATE t SET y = 2;
SELECT * FROM t;
DROP TABLE t;

-- same as last 4 tests but here the last original column has a NULL value
-- 2 new columns, both have defaults
CREATE TABLE t (id serial PRIMARY KEY, a int, b int, c int);
INSERT INTO t (a,b,c) VALUES (1,2,NULL);
ALTER TABLE t ADD COLUMN x int NOT NULL DEFAULT 4;
ALTER TABLE t ADD COLUMN y int NOT NULL DEFAULT 5;
SELECT * FROM t;
UPDATE t SET y = 2;
SELECT * FROM t;
DROP TABLE t;

-- 2 new columns, first has default
CREATE TABLE t (id serial PRIMARY KEY, a int, b int, c int);
INSERT INTO t (a,b,c) VALUES (1,2,NULL);
ALTER TABLE t ADD COLUMN x int NOT NULL DEFAULT 4;
ALTER TABLE t ADD COLUMN y int;
SELECT * FROM t;
UPDATE t SET y = 2;
SELECT * FROM t;
DROP TABLE t;

-- 2 new columns, second has default
CREATE TABLE t (id serial PRIMARY KEY, a int, b int, c int);
INSERT INTO t (a,b,c) VALUES (1,2,NULL);
ALTER TABLE t ADD COLUMN x int;
ALTER TABLE t ADD COLUMN y int NOT NULL DEFAULT 5;
SELECT * FROM t;
UPDATE t SET y = 2;
SELECT * FROM t;
DROP TABLE t;

-- 2 new columns, neither has default
CREATE TABLE t (id serial PRIMARY KEY, a int, b int, c int);
INSERT INTO t (a,b,c) VALUES (1,2,NULL);
ALTER TABLE t ADD COLUMN x int;
ALTER TABLE t ADD COLUMN y int;
SELECT * FROM t;
UPDATE t SET y = 2;
SELECT * FROM t;
DROP TABLE t;

CREATE TABLE leader (a int PRIMARY KEY, b int);
CREATE TABLE follower (a int, b int);
INSERT INTO leader VALUES (1, 1), (2, 2);
ALTER TABLE leader ADD c int;
ALTER TABLE leader DROP c;
DELETE FROM leader;

-- check that ALTER TABLE ... ALTER TYPE does the right thing

CREATE TABLE vtype( a integer);
INSERT INTO vtype VALUES (1);
ALTER TABLE vtype ADD COLUMN b DOUBLE PRECISION DEFAULT 0.2;
ALTER TABLE vtype ADD COLUMN c BOOLEAN DEFAULT true;
SELECT * FROM vtype;
ALTER TABLE vtype
      ALTER b TYPE text USING b::text,
      ALTER c TYPE text USING c::text;
SELECT * FROM vtype;

-- also check the case that doesn't rewrite the table

CREATE TABLE vtype2 (a int);
INSERT INTO vtype2 VALUES (1);
ALTER TABLE vtype2 ADD COLUMN b varchar(10) DEFAULT 'xxx';
ALTER TABLE vtype2 ALTER COLUMN b SET DEFAULT 'yyy';
INSERT INTO vtype2 VALUES (2);

ALTER TABLE vtype2 ALTER COLUMN b TYPE varchar(20) USING b::varchar(20);
SELECT * FROM vtype2;

-- cleanup
DROP TABLE vtype;
DROP TABLE vtype2;
DROP TABLE follower;
DROP TABLE leader;
DROP TABLE t1;
DROP TABLE has_volatile;
DROP FUNCTION log_rewrite;
DROP SCHEMA fast_default;

-- Leave a table with an active fast default in place, for pg_upgrade testing
set search_path = public;
create table has_fast_default(f1 int);
insert into has_fast_default values(1);
alter table has_fast_default add column f2 int default 42;
table has_fast_default;
drop table has_fast_default;
