github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/opt/norm/testdata/rules/numeric (about) 1 exec-ddl 2 CREATE TABLE a (k INT PRIMARY KEY, i INT, f FLOAT, d DECIMAL, t TIME) 3 ---- 4 5 # -------------------------------------------------- 6 # FoldPlusZero, FoldZeroPlus 7 # -------------------------------------------------- 8 9 # Add columns to prevent NormalizeVar from swapping left and right. 10 norm expect=(FoldPlusZero,FoldZeroPlus) 11 SELECT 12 (a.i + a.i) + 0 AS r, 0 + (a.i + a.i) AS s, 13 (a.f + a.f) + 0 AS t, 0 + (a.f + a.f) AS u, 14 (a.d + a.d) + 0 AS v, 0 + (a.d + a.d) AS w 15 FROM a 16 ---- 17 project 18 ├── columns: r:6 s:7 t:8 u:9 v:10 w:11 19 ├── scan a 20 │ └── columns: i:2 f:3 d:4 21 └── projections 22 ├── i:2 + i:2 [as=r:6, outer=(2)] 23 ├── i:2 + i:2 [as=s:7, outer=(2)] 24 ├── f:3 + f:3 [as=t:8, outer=(3)] 25 ├── f:3 + f:3 [as=u:9, outer=(3)] 26 ├── d:4 + d:4 [as=v:10, outer=(4)] 27 └── d:4 + d:4 [as=w:11, outer=(4)] 28 29 30 # Regression test for #35113. 31 norm expect=FoldPlusZero 32 SELECT i + 0::decimal FROM a 33 ---- 34 project 35 ├── columns: "?column?":6 36 ├── scan a 37 │ └── columns: i:2 38 └── projections 39 └── i:2::DECIMAL [as="?column?":6, outer=(2)] 40 41 norm expect=FoldZeroPlus 42 SELECT 0::decimal + i FROM a 43 ---- 44 project 45 ├── columns: "?column?":6 46 ├── scan a 47 │ └── columns: i:2 48 └── projections 49 └── i:2::DECIMAL [as="?column?":6, outer=(2)] 50 51 # -------------------------------------------------- 52 # FoldMinusZero 53 # -------------------------------------------------- 54 55 # Add columns to prevent NormalizeVar from swapping left and right. 56 norm expect=FoldMinusZero 57 SELECT 58 (a.i + a.i) - 0 AS r, 59 (a.f + a.f) - 0 AS s, 60 (a.d + a.d) - 0 AS t 61 FROM a 62 ---- 63 project 64 ├── columns: r:6 s:7 t:8 65 ├── scan a 66 │ └── columns: i:2 f:3 d:4 67 └── projections 68 ├── i:2 + i:2 [as=r:6, outer=(2)] 69 ├── f:3 + f:3 [as=s:7, outer=(3)] 70 └── d:4 + d:4 [as=t:8, outer=(4)] 71 72 # Regression test for #35113. 73 norm expect=FoldMinusZero 74 SELECT i - 0::decimal FROM a 75 ---- 76 project 77 ├── columns: "?column?":6 78 ├── scan a 79 │ └── columns: i:2 80 └── projections 81 └── i:2::DECIMAL [as="?column?":6, outer=(2)] 82 83 # Regression test for #35612. 84 norm expect-not=FoldMinusZero 85 SELECT '[123]'::jsonb - 0 86 ---- 87 values 88 ├── columns: "?column?":1!null 89 ├── cardinality: [1 - 1] 90 ├── key: () 91 ├── fd: ()-->(1) 92 └── ('[]',) 93 94 # -------------------------------------------------- 95 # FoldMultOne, FoldOneMult 96 # -------------------------------------------------- 97 98 # Add columns to prevent NormalizeVar from swapping left and right. 99 norm expect=(FoldMultOne,FoldOneMult) 100 SELECT 101 (a.i + a.i) * 1 AS r, 1 * (a.i + a.i) AS s, 102 (a.f + a.f) * 1 AS t, 1 * (a.f + a.f) AS u, 103 (a.d + a.d) * 1 AS v, 1 * (a.d + a.d) AS w 104 FROM a 105 ---- 106 project 107 ├── columns: r:6 s:7 t:8 u:9 v:10 w:11 108 ├── scan a 109 │ └── columns: i:2 f:3 d:4 110 └── projections 111 ├── i:2 + i:2 [as=r:6, outer=(2)] 112 ├── i:2 + i:2 [as=s:7, outer=(2)] 113 ├── f:3 + f:3 [as=t:8, outer=(3)] 114 ├── f:3 + f:3 [as=u:9, outer=(3)] 115 ├── d:4 + d:4 [as=v:10, outer=(4)] 116 └── d:4 + d:4 [as=w:11, outer=(4)] 117 118 # Regression test for #35113. 119 norm expect=FoldMultOne 120 SELECT i * 1::decimal FROM a 121 ---- 122 project 123 ├── columns: "?column?":6 124 ├── scan a 125 │ └── columns: i:2 126 └── projections 127 └── i:2::DECIMAL [as="?column?":6, outer=(2)] 128 129 norm expect=FoldOneMult 130 SELECT 1::decimal * i FROM a 131 ---- 132 project 133 ├── columns: "?column?":6 134 ├── scan a 135 │ └── columns: i:2 136 └── projections 137 └── i:2::DECIMAL [as="?column?":6, outer=(2)] 138 139 # -------------------------------------------------- 140 # FoldDivOne 141 # -------------------------------------------------- 142 143 norm expect=FoldDivOne 144 SELECT 145 a.i / 1 AS r, 146 a.f / 1 AS s, 147 a.d / 1 AS t 148 FROM a 149 ---- 150 project 151 ├── columns: r:6 s:7 t:8 152 ├── scan a 153 │ └── columns: i:2 f:3 d:4 154 └── projections 155 ├── i:2::DECIMAL [as=r:6, outer=(2)] 156 ├── f:3 [as=s:7, outer=(3)] 157 └── d:4 [as=t:8, outer=(4)] 158 159 # Regression test for #35113. 160 norm expect=FoldDivOne 161 SELECT i / 1::decimal FROM a 162 ---- 163 project 164 ├── columns: "?column?":6 165 ├── scan a 166 │ └── columns: i:2 167 └── projections 168 └── i:2::DECIMAL [as="?column?":6, outer=(2)] 169 170 norm expect=FoldDivOne 171 SELECT i / 1::int8 FROM a 172 ---- 173 project 174 ├── columns: "?column?":6 175 ├── scan a 176 │ └── columns: i:2 177 └── projections 178 └── i:2::DECIMAL [as="?column?":6, outer=(2)] 179 180 # -------------------------------------------------- 181 # InvertMinus 182 # -------------------------------------------------- 183 norm expect=InvertMinus 184 SELECT 185 -(a.f - a.f) AS r, 186 -(a.d - a.i) AS s, 187 -(a.t - a.t) AS t 188 FROM a 189 ---- 190 project 191 ├── columns: r:6 s:7 t:8 192 ├── scan a 193 │ └── columns: i:2 f:3 d:4 a.t:5 194 └── projections 195 ├── f:3 - f:3 [as=r:6, outer=(3)] 196 ├── i:2 - d:4 [as=s:7, outer=(2,4)] 197 └── a.t:5 - a.t:5 [as=t:8, outer=(5)] 198 199 # -------------------------------------------------- 200 # EliminateUnaryMinus 201 # -------------------------------------------------- 202 norm expect=EliminateUnaryMinus 203 SELECT -(-a.i::int) AS r FROM a 204 ---- 205 project 206 ├── columns: r:6 207 ├── scan a 208 │ └── columns: i:2 209 └── projections 210 └── i:2 [as=r:6, outer=(2)]