github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/logictest/testdata/logic_test/default (about)

     1  statement error expected DEFAULT expression to have type int, but 'false' has type bool
     2  CREATE TABLE t (a INT PRIMARY KEY DEFAULT false)
     3  
     4  statement error variable sub-expressions are not allowed in DEFAULT
     5  CREATE TABLE t (a INT PRIMARY KEY DEFAULT (SELECT 1))
     6  
     7  statement error variable sub-expressions are not allowed in DEFAULT
     8  CREATE TABLE t (a INT PRIMARY KEY DEFAULT b)
     9  
    10  # Issue #14308: support tables with DEFAULT NULL columns.
    11  statement ok
    12  CREATE TABLE null_default (ts TIMESTAMP PRIMARY KEY NULL DEFAULT NULL)
    13  
    14  # Aggregate function calls in CHECK are not ok.
    15  statement error aggregate functions are not allowed in DEFAULT
    16  CREATE TABLE bad (a INT DEFAULT count(1))
    17  
    18  # Window function calls in CHECK are not ok.
    19  statement error window functions are not allowed in DEFAULT
    20  CREATE TABLE bad (a INT DEFAULT count(1) OVER ())
    21  
    22  statement ok
    23  CREATE TABLE t (
    24    a INT PRIMARY KEY DEFAULT 42,
    25    b TIMESTAMP DEFAULT now(),
    26    c FLOAT DEFAULT random(),
    27    d DATE DEFAULT now()
    28  )
    29  
    30  query TTBTTTB colnames
    31  SHOW COLUMNS FROM t
    32  ----
    33  column_name  data_type  is_nullable  column_default     generation_expression  indices    is_hidden
    34  a            INT8       false        42:::INT8          ·                      {primary}  false
    35  b            TIMESTAMP  true         now():::TIMESTAMP  ·                      {}         false
    36  c            FLOAT8     true         random()           ·                      {}         false
    37  d            DATE       true         now():::DATE       ·                      {}         false
    38  
    39  statement ok
    40  COMMENT ON COLUMN t.a IS 'a'
    41  
    42  query TTBTTTBT colnames
    43  SHOW COLUMNS FROM t WITH COMMENT
    44  ----
    45  column_name  data_type  is_nullable  column_default     generation_expression  indices    is_hidden  comment
    46  a            INT8       false        42:::INT8          ·                      {primary}  false      a
    47  b            TIMESTAMP  true         now():::TIMESTAMP  ·                      {}         false      NULL
    48  c            FLOAT8     true         random()           ·                      {}         false      NULL
    49  d            DATE       true         now():::DATE       ·                      {}         false      NULL
    50  
    51  statement ok
    52  INSERT INTO t VALUES (DEFAULT, DEFAULT, DEFAULT, DEFAULT)
    53  
    54  query IBBB
    55  SELECT a, b <= now(), c >= 0.0, d <= now() FROM t
    56  ----
    57  42 true true true
    58  
    59  statement ok
    60  TRUNCATE TABLE t
    61  
    62  statement ok
    63  INSERT INTO t DEFAULT VALUES
    64  
    65  query IBBB
    66  SELECT a, b <= now(), c >= 0.0, d <= now() FROM t
    67  ----
    68  42 true true true
    69  
    70  statement ok
    71  INSERT INTO t (a) VALUES (1)
    72  
    73  query IBBB
    74  SELECT a, b <= now(), c >= 0.0, d <= now() FROM t WHERE a = 1
    75  ----
    76  1 true true true
    77  
    78  statement ok
    79  INSERT INTO t VALUES (2)
    80  
    81  query IBBB
    82  SELECT a, b <= now(), c >= 0.0, d <= now() FROM t WHERE a = 2
    83  ----
    84  2 true true true
    85  
    86  statement ok
    87  UPDATE t SET (b, c) = ('2015-09-18 00:00:00', -1.0)
    88  
    89  statement ok
    90  UPDATE t SET b = DEFAULT WHERE a = 1
    91  
    92  query IBBB
    93  SELECT a, b <= now(), c = -1.0, d <= now() FROM t WHERE a = 1
    94  ----
    95  1 true true true
    96  
    97  statement ok
    98  UPDATE t SET (b, c) = (DEFAULT, DEFAULT) WHERE a = 2
    99  
   100  query IBBB
   101  SELECT a, b <= now(), c >= 0.0, d <= now() FROM t WHERE a = 2
   102  ----
   103  2 true true true
   104  
   105  statement ok
   106  UPDATE t SET b = DEFAULT, c = DEFAULT, d = DEFAULT
   107  
   108  statement ok
   109  UPDATE t SET (b) = (DEFAULT), (c) = (DEFAULT), (d) = (DEFAULT)
   110  
   111  # Test a table without a default and with a null default
   112  statement ok
   113  CREATE TABLE v (
   114    a INT PRIMARY KEY,
   115    b TIMESTAMP NULL DEFAULT NULL,
   116    c INT
   117  )
   118  
   119  statement ok
   120  UPDATE v SET a = DEFAULT
   121  
   122  statement ok
   123  UPDATE v SET (a, c) = (DEFAULT, DEFAULT)
   124  
   125  query TTBTTTB colnames
   126  SHOW COLUMNS FROM v
   127  ----
   128  column_name  data_type  is_nullable  column_default  generation_expression  indices    is_hidden
   129  a            INT8       false        NULL            ·                      {primary}  false
   130  b            TIMESTAMP  true         NULL            ·                      {}         false
   131  c            INT8       true         NULL            ·                      {}         false
   132  
   133  # Regression test for #34901: verify that builtins can be used in default value
   134  # expressions without a "memory budget exceeded" error while backfilling
   135  statement ok
   136  CREATE TABLE t34901 (x STRING)
   137  
   138  statement ok
   139  INSERT INTO t34901 VALUES ('a')
   140  
   141  statement ok
   142  ALTER TABLE t34901 ADD COLUMN y STRING DEFAULT (concat('b', 'c'))
   143  
   144  query TT
   145  SELECT * FROM t34901
   146  ----
   147  a  bc