github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/opt/xform/testdata/rules/computed (about) 1 # -------------------------------------------------- 2 # GenerateConstrainedScans + Computed Cols 3 # -------------------------------------------------- 4 5 exec-ddl 6 CREATE TABLE t_int ( 7 k_int INT, 8 c_int INT AS (k_int % 4) STORED, 9 c_int_2 INT AS (k_int % 4) STORED, 10 INDEX c_int_index (c_int, k_int) 11 ) 12 ---- 13 14 exec-ddl 15 CREATE TABLE t_float ( 16 k_float FLOAT, 17 c_float FLOAT AS (k_float + 1) STORED, 18 INDEX c_float_index (c_float, k_float) 19 ) 20 ---- 21 22 exec-ddl 23 CREATE TABLE t_now ( 24 k_interval INTERVAL, 25 c_ts TIMESTAMP AS (now() + k_interval) STORED, 26 INDEX c_ts_index (c_ts, k_interval) 27 ) 28 ---- 29 30 exec-ddl 31 CREATE TABLE t_mult ( 32 k_int INT, 33 k_int_2 INT, 34 c_int INT AS (k_int % 4) STORED, 35 c_mult INT AS (c_mult_2 * c_int * k_int * k_int_2) STORED, 36 c_mult_2 INT AS (k_int + 1) STORED, 37 INDEX c_mult_index (c_mult, c_mult_2, c_int, k_int, k_int_2) 38 ) 39 ---- 40 41 exec-ddl 42 CREATE TABLE hashed ( 43 k STRING, 44 hash INT AS (fnv32(k) % 4) STORED CHECK (hash IN (0, 1, 2, 3)), 45 INDEX (hash, k) 46 ) 47 ---- 48 49 # Constrain the index using computed column. Ensure that another computed column 50 # depending on the same base column isn't included as a filter (c_int_2). 51 opt 52 SELECT k_int FROM t_int WHERE k_int = 5 53 ---- 54 scan t_int@c_int_index 55 ├── columns: k_int:1!null 56 ├── constraint: /2/1/4: [/1/5 - /1/5] 57 └── fd: ()-->(1) 58 59 # Use index with multiple computed columns, based on multiple input columns in 60 # acyclic graph. 61 opt 62 SELECT k_int, k_int_2, c_mult, c_mult_2, c_int FROM t_mult WHERE k_int = 5 AND k_int_2 = 10 63 ---- 64 scan t_mult@c_mult_index 65 ├── columns: k_int:1!null k_int_2:2!null c_mult:4 c_mult_2:5 c_int:3 66 ├── constraint: /4/5/3/1/2/6: [/300/6/1/5/10 - /300/6/1/5/10] 67 └── fd: ()-->(1,2) 68 69 # Test computed + check columns in same table. 70 opt 71 SELECT * FROM hashed WHERE k = 'andy' 72 ---- 73 scan hashed@secondary 74 ├── columns: k:1!null hash:2 75 ├── constraint: /2/1/3: [/1/'andy' - /1/'andy'] 76 └── fd: ()-->(1) 77 78 # Don't constrain when filter has multiple columns. 79 opt 80 SELECT k_int FROM t_mult WHERE (k_int, k_int_2) > (1, 2) 81 ---- 82 project 83 ├── columns: k_int:1!null 84 └── select 85 ├── columns: k_int:1!null k_int_2:2 86 ├── scan t_mult 87 │ ├── columns: k_int:1 k_int_2:2 88 │ └── computed column expressions 89 │ ├── c_int:3 90 │ │ └── k_int:1 % 4 91 │ ├── c_mult:4 92 │ │ └── k_int_2:2 * (k_int:1 * (c_mult_2:5 * c_int:3)) 93 │ └── c_mult_2:5 94 │ └── k_int:1 + 1 95 └── filters 96 └── (k_int:1, k_int_2:2) > (1, 2) [outer=(1,2), constraints=(/1/2: [/1/3 - ]; tight)] 97 98 # Don't constrain when filter has multiple spans. 99 opt 100 SELECT k_int FROM t_mult WHERE k_int = 2 OR k_int = 3 101 ---- 102 select 103 ├── columns: k_int:1!null 104 ├── scan t_mult 105 │ ├── columns: k_int:1 106 │ └── computed column expressions 107 │ ├── c_int:3 108 │ │ └── k_int:1 % 4 109 │ ├── c_mult:4 110 │ │ └── k_int_2:2 * (k_int:1 * (c_mult_2:5 * c_int:3)) 111 │ └── c_mult_2:5 112 │ └── k_int:1 + 1 113 └── filters 114 └── (k_int:1 = 2) OR (k_int:1 = 3) [outer=(1), constraints=(/1: [/2 - /2] [/3 - /3]; tight)] 115 116 # Don't constrain the index for a NULL value. 117 opt 118 SELECT k_int FROM t_int WHERE k_int IS NULL 119 ---- 120 select 121 ├── columns: k_int:1 122 ├── fd: ()-->(1) 123 ├── scan t_int@c_int_index 124 │ └── columns: k_int:1 125 └── filters 126 └── k_int:1 IS NULL [outer=(1), constraints=(/1: [/NULL - /NULL]; tight), fd=()-->(1)] 127 128 # Don't constrain the index for a FLOAT column, since the FLOAT data type uses 129 # a composite key encoding. 130 opt 131 SELECT k_float FROM t_float WHERE k_float = 5.0 132 ---- 133 select 134 ├── columns: k_float:1!null 135 ├── fd: ()-->(1) 136 ├── scan t_float 137 │ ├── columns: k_float:1 138 │ └── computed column expressions 139 │ └── c_float:2 140 │ └── k_float:1 + 1.0 141 └── filters 142 └── k_float:1 = 5.0 [outer=(1), constraints=(/1: [/5.0 - /5.0]; tight), fd=()-->(1)] 143 144 # Don't constrain the index when the computed column has a non-pure function. 145 opt 146 SELECT k_interval FROM t_now WHERE k_interval = '3 hours' 147 ---- 148 select 149 ├── columns: k_interval:1!null 150 ├── fd: ()-->(1) 151 ├── scan t_now 152 │ ├── columns: k_interval:1 153 │ └── computed column expressions 154 │ └── c_ts:2 155 │ └── k_interval:1 + now() 156 └── filters 157 └── k_interval:1 = '03:00:00' [outer=(1), constraints=(/1: [/'03:00:00' - /'03:00:00']; tight), fd=()-->(1)] 158 159 # Verify that a stored NULL value is handled correctly (#44132). 160 exec-ddl 161 CREATE TABLE null_col ( 162 a INT, 163 b INT AS (NULL) STORED, 164 INDEX ab (a, b) 165 ) 166 ---- 167 168 opt 169 SELECT a, b FROM null_col WHERE a = 1 170 ---- 171 scan null_col@ab 172 ├── columns: a:1!null b:2 173 ├── constraint: /1/2/3: [/1/NULL - /1/NULL] 174 └── fd: ()-->(1)