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

     1  # This file contains tests for handling of duplicate impure projections.  See
     2  # #44865.
     3  
     4  query I
     5  WITH cte (a, b) AS (SELECT random(), random())
     6  SELECT count(*) FROM cte WHERE a = b
     7  ----
     8  0
     9  
    10  query I
    11  WITH cte (x, a, b) AS (SELECT x, random(), random() FROM (VALUES (1), (2), (3)) AS v(x))
    12  SELECT count(*) FROM cte WHERE a = b
    13  ----
    14  0
    15  
    16  statement ok
    17  CREATE TABLE kab (k INT PRIMARY KEY, a UUID, b UUID)
    18  
    19  statement ok
    20  INSERT INTO kab VALUES (1, gen_random_uuid(), gen_random_uuid())
    21  
    22  statement ok
    23  INSERT INTO kab VALUES (2, gen_random_uuid(), gen_random_uuid())
    24  
    25  statement ok
    26  INSERT INTO kab VALUES (3, gen_random_uuid(), gen_random_uuid()),
    27                         (4, gen_random_uuid(), gen_random_uuid()),
    28                         (5, gen_random_uuid(), gen_random_uuid()),
    29                         (6, gen_random_uuid(), gen_random_uuid())
    30  
    31  query I
    32  SELECT count(*) FROM kab WHERE a=b
    33  ----
    34  0
    35  
    36  statement ok
    37  CREATE TABLE kabcd (
    38    k INT PRIMARY KEY,
    39    a UUID,
    40    b UUID,
    41    c UUID DEFAULT gen_random_uuid(),
    42    d UUID DEFAULT gen_random_uuid()
    43  )
    44  
    45  statement ok
    46  INSERT INTO kabcd VALUES (1, gen_random_uuid(), gen_random_uuid())
    47  
    48  statement ok
    49  INSERT INTO kabcd VALUES (2, gen_random_uuid(), gen_random_uuid())
    50  
    51  statement ok
    52  INSERT INTO kabcd VALUES (3, gen_random_uuid(), gen_random_uuid()),
    53                           (4, gen_random_uuid(), gen_random_uuid()),
    54                           (5, gen_random_uuid(), gen_random_uuid()),
    55                           (6, gen_random_uuid(), gen_random_uuid())
    56  
    57  query I
    58  SELECT count(*) FROM kabcd WHERE a=b OR a=c OR a=d OR b=c OR b=d OR c=d
    59  ----
    60  0