github.com/dolthub/go-mysql-server@v0.18.0/enginetest/queries/ordinal_ddl_queries.go (about) 1 // Copyright 2022 Dolthub, Inc. 2 // 3 // Licensed under the Apache License, Version 2.0 (the "License"); 4 // you may not use this file except in compliance with the License. 5 // You may obtain a copy of the License at 6 // 7 // http://www.apache.org/licenses/LICENSE-2.0 8 // 9 // Unless required by applicable law or agreed to in writing, software 10 // distributed under the License is distributed on an "AS IS" BASIS, 11 // WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. 12 // See the License for the specific language governing permissions and 13 // limitations under the License. 14 15 package queries 16 17 import ( 18 "github.com/dolthub/go-mysql-server/sql" 19 "github.com/dolthub/go-mysql-server/sql/types" 20 ) 21 22 var OrdinalDDLQueries = []QueryTest{ 23 { 24 Query: "show keys from short_ord_pk", 25 Expected: []sql.Row{ 26 {"short_ord_pk", 0, "PRIMARY", 1, "y", nil, 0, nil, nil, "", "BTREE", "", "", "YES", nil}, 27 {"short_ord_pk", 0, "PRIMARY", 2, "x", nil, 0, nil, nil, "", "BTREE", "", "", "YES", nil}, 28 }, 29 }, 30 { 31 Query: "SELECT column_name, ordinal_position FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'mydb' AND TABLE_NAME = 'short_ord_pk'", 32 Expected: []sql.Row{ 33 {"x", uint(1)}, 34 {"y", uint(2)}, 35 }, 36 }, 37 { 38 Query: "show keys from long_ord_pk1", 39 Expected: []sql.Row{ 40 {"long_ord_pk1", 0, "PRIMARY", 1, "y", nil, 0, nil, nil, "", "BTREE", "", "", "YES", nil}, 41 {"long_ord_pk1", 0, "PRIMARY", 2, "v", nil, 0, nil, nil, "", "BTREE", "", "", "YES", nil}, 42 }, 43 }, 44 { 45 Query: "SELECT column_name, ordinal_position FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'mydb' AND TABLE_NAME = 'long_ord_pk1' and column_key = 'PRI'", 46 Expected: []sql.Row{ 47 {"v", uint(2)}, 48 {"y", uint(5)}, 49 }, 50 }, 51 { 52 Query: "show keys from long_ord_pk2", 53 Expected: []sql.Row{ 54 {"long_ord_pk2", 0, "PRIMARY", 1, "y", nil, 0, nil, nil, "", "BTREE", "", "", "YES", nil}, 55 {"long_ord_pk2", 0, "PRIMARY", 2, "v", nil, 0, nil, nil, "", "BTREE", "", "", "YES", nil}, 56 {"long_ord_pk2", 0, "PRIMARY", 3, "x", nil, 0, nil, nil, "", "BTREE", "", "", "YES", nil}, 57 {"long_ord_pk2", 0, "PRIMARY", 4, "z", nil, 0, nil, nil, "", "BTREE", "", "", "YES", nil}, 58 {"long_ord_pk2", 0, "PRIMARY", 5, "u", nil, 0, nil, nil, "", "BTREE", "", "", "YES", nil}, 59 }, 60 }, 61 { 62 Query: "SELECT column_name, ordinal_position FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'mydb' AND TABLE_NAME = 'long_ord_pk2' and column_key = 'PRI'", 63 Expected: []sql.Row{ 64 {"u", uint(1)}, 65 {"v", uint(2)}, 66 {"x", uint(4)}, 67 {"y", uint(5)}, 68 {"z", uint(6)}, 69 }, 70 }, 71 { 72 Query: "show keys from long_ord_pk3", 73 Expected: []sql.Row{ 74 {"long_ord_pk3", 0, "PRIMARY", 1, "y", nil, 0, nil, nil, "", "BTREE", "", "", "YES", nil}, 75 {"long_ord_pk3", 0, "PRIMARY", 2, "v", nil, 0, nil, nil, "", "BTREE", "", "", "YES", nil}, 76 {"long_ord_pk3", 0, "PRIMARY", 3, "x", nil, 0, nil, nil, "", "BTREE", "", "", "YES", nil}, 77 {"long_ord_pk3", 0, "PRIMARY", 4, "z", nil, 0, nil, nil, "", "BTREE", "", "", "YES", nil}, 78 {"long_ord_pk3", 0, "PRIMARY", 5, "u", nil, 0, nil, nil, "", "BTREE", "", "", "YES", nil}, 79 }, 80 }, 81 { 82 Query: "SELECT column_name, ordinal_position FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'mydb' AND TABLE_NAME = 'long_ord_pk3' and column_key = 'PRI'", 83 Expected: []sql.Row{ 84 {"u", uint(1)}, 85 {"v", uint(2)}, 86 {"x", uint(5)}, 87 {"y", uint(6)}, 88 {"z", uint(7)}, 89 }, 90 }, 91 { 92 Query: "show keys from ord_kl", 93 Expected: []sql.Row{}, 94 }, 95 { 96 Query: "SELECT column_name, ordinal_position FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'mydb' AND TABLE_NAME = 'ord_kl' and column_key = 'PRI'", 97 Expected: []sql.Row{}, 98 }, 99 } 100 101 var OrdinalDDLWriteQueries = []WriteQueryTest{ 102 { 103 WriteQuery: "ALTER TABLE long_ord_pk1 ADD COLUMN ww int AFTER v", 104 ExpectedWriteResult: []sql.Row{ 105 {types.OkResult{RowsAffected: 0}}, 106 }, 107 SelectQuery: "SELECT column_name, ordinal_position FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'mydb' AND TABLE_NAME = 'long_ord_pk1' and column_key = 'PRI'", 108 ExpectedSelect: []sql.Row{ 109 {"v", uint(2)}, 110 {"y", uint(6)}, 111 }, 112 }, 113 { 114 WriteQuery: "ALTER TABLE long_ord_pk1 MODIFY COLUMN w int AFTER y", 115 ExpectedWriteResult: []sql.Row{ 116 {types.OkResult{RowsAffected: 0}}, 117 }, 118 SelectQuery: "SELECT column_name, ordinal_position FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'mydb' AND TABLE_NAME = 'long_ord_pk1' and column_key = 'PRI'", 119 ExpectedSelect: []sql.Row{ 120 {"v", uint(2)}, 121 {"y", uint(4)}, 122 }, 123 }, 124 { 125 WriteQuery: "ALTER TABLE long_ord_pk1 DROP PRIMARY KEY", 126 ExpectedWriteResult: []sql.Row{ 127 {types.OkResult{RowsAffected: 0}}, 128 }, 129 SelectQuery: "show keys from ord_kl", 130 ExpectedSelect: []sql.Row{}, 131 }, 132 { 133 WriteQuery: "ALTER TABLE ord_kl ADD PRIMARY KEY (y,v)", 134 ExpectedWriteResult: []sql.Row{ 135 {types.OkResult{RowsAffected: 0}}, 136 }, 137 SelectQuery: "show keys from ord_kl", 138 ExpectedSelect: []sql.Row{ 139 {"ord_kl", 0, "PRIMARY", 1, "y", nil, 0, nil, nil, "", "BTREE", "", "", "YES", nil}, 140 {"ord_kl", 0, "PRIMARY", 2, "v", nil, 0, nil, nil, "", "BTREE", "", "", "YES", nil}, 141 }, 142 }, 143 { 144 WriteQuery: "ALTER TABLE ord_kl ADD PRIMARY KEY (y,v)", 145 ExpectedWriteResult: []sql.Row{ 146 {types.OkResult{RowsAffected: 0}}, 147 }, 148 SelectQuery: "SELECT column_name, ordinal_position FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'mydb' AND TABLE_NAME = 'ord_kl' and column_key = 'PRI'", 149 ExpectedSelect: []sql.Row{ 150 {"v", uint(2)}, 151 {"y", uint(5)}, 152 }, 153 }, 154 { 155 WriteQuery: "ALTER TABLE long_ord_pk1 MODIFY COLUMN y int AFTER u", 156 ExpectedWriteResult: []sql.Row{ 157 {types.OkResult{RowsAffected: 0}}, 158 }, 159 SelectQuery: `SELECT column_name, ordinal_position FROM INFORMATION_SCHEMA.COLUMNS 160 WHERE TABLE_SCHEMA = 'mydb' AND TABLE_NAME = 'long_ord_pk1' and column_key = 'PRI' order by 2`, 161 ExpectedSelect: []sql.Row{ 162 {"y", uint(2)}, 163 {"v", uint(3)}, 164 }, 165 }, 166 { 167 WriteQuery: "ALTER TABLE long_ord_pk1 MODIFY COLUMN y int AFTER u", 168 ExpectedWriteResult: []sql.Row{ 169 {types.OkResult{RowsAffected: 0}}, 170 }, 171 SelectQuery: "show keys from long_ord_pk1", 172 ExpectedSelect: []sql.Row{ 173 {"long_ord_pk1", 0, "PRIMARY", 1, "y", nil, 0, nil, nil, "", "BTREE", "", "", "YES", nil}, 174 {"long_ord_pk1", 0, "PRIMARY", 2, "v", nil, 0, nil, nil, "", "BTREE", "", "", "YES", nil}, 175 }, 176 }, 177 { 178 WriteQuery: "ALTER TABLE long_ord_pk1 RENAME COLUMN y to yy", 179 ExpectedWriteResult: []sql.Row{ 180 {types.OkResult{RowsAffected: 0}}, 181 }, 182 SelectQuery: "SELECT column_name, ordinal_position FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'mydb' AND TABLE_NAME = 'long_ord_pk1' and column_key = 'PRI'", 183 ExpectedSelect: []sql.Row{ 184 {"v", uint(2)}, 185 {"yy", uint(5)}, 186 }, 187 }, 188 { 189 WriteQuery: "ALTER TABLE long_ord_pk1 RENAME COLUMN y to yy", 190 ExpectedWriteResult: []sql.Row{ 191 {types.OkResult{RowsAffected: 0}}, 192 }, 193 SelectQuery: "show keys from long_ord_pk1", 194 ExpectedSelect: []sql.Row{ 195 {"long_ord_pk1", 0, "PRIMARY", 1, "yy", nil, 0, nil, nil, "", "BTREE", "", "", "YES", nil}, 196 {"long_ord_pk1", 0, "PRIMARY", 2, "v", nil, 0, nil, nil, "", "BTREE", "", "", "YES", nil}, 197 }, 198 }, 199 { 200 WriteQuery: "ALTER TABLE long_ord_pk2 ADD COLUMN ww int AFTER w", 201 ExpectedWriteResult: []sql.Row{ 202 {types.OkResult{RowsAffected: 0}}, 203 }, 204 SelectQuery: "SELECT column_name, ordinal_position FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'mydb' AND TABLE_NAME = 'long_ord_pk2' and column_key = 'PRI'", 205 ExpectedSelect: []sql.Row{ 206 {"u", uint(1)}, 207 {"v", uint(2)}, 208 {"x", uint(5)}, 209 {"y", uint(6)}, 210 {"z", uint(7)}, 211 }, 212 }, 213 { 214 WriteQuery: "ALTER TABLE long_ord_pk2 ADD COLUMN ww int AFTER w", 215 ExpectedWriteResult: []sql.Row{ 216 {types.OkResult{RowsAffected: 0}}, 217 }, 218 SelectQuery: "show keys from long_ord_pk2", 219 ExpectedSelect: []sql.Row{ 220 {"long_ord_pk2", 0, "PRIMARY", 1, "y", nil, 0, nil, nil, "", "BTREE", "", "", "YES", nil}, 221 {"long_ord_pk2", 0, "PRIMARY", 2, "v", nil, 0, nil, nil, "", "BTREE", "", "", "YES", nil}, 222 {"long_ord_pk2", 0, "PRIMARY", 3, "x", nil, 0, nil, nil, "", "BTREE", "", "", "YES", nil}, 223 {"long_ord_pk2", 0, "PRIMARY", 4, "z", nil, 0, nil, nil, "", "BTREE", "", "", "YES", nil}, 224 {"long_ord_pk2", 0, "PRIMARY", 5, "u", nil, 0, nil, nil, "", "BTREE", "", "", "YES", nil}, 225 }, 226 }, 227 { 228 WriteQuery: "ALTER TABLE long_ord_pk3 DROP COLUMN ww", 229 ExpectedWriteResult: []sql.Row{ 230 {types.OkResult{RowsAffected: 0}}, 231 }, 232 SelectQuery: "SELECT column_name, ordinal_position FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'mydb' AND TABLE_NAME = 'long_ord_pk3' and column_key = 'PRI'", 233 ExpectedSelect: []sql.Row{ 234 {"u", uint(1)}, 235 {"v", uint(2)}, 236 {"x", uint(4)}, 237 {"y", uint(5)}, 238 {"z", uint(6)}, 239 }, 240 }, 241 { 242 WriteQuery: "ALTER TABLE long_ord_pk3 DROP COLUMN ww", 243 ExpectedWriteResult: []sql.Row{ 244 {types.OkResult{RowsAffected: 0}}, 245 }, 246 SelectQuery: "show keys from long_ord_pk3", 247 ExpectedSelect: []sql.Row{ 248 {"long_ord_pk3", 0, "PRIMARY", 1, "y", nil, 0, nil, nil, "", "BTREE", "", "", "YES", nil}, 249 {"long_ord_pk3", 0, "PRIMARY", 2, "v", nil, 0, nil, nil, "", "BTREE", "", "", "YES", nil}, 250 {"long_ord_pk3", 0, "PRIMARY", 3, "x", nil, 0, nil, nil, "", "BTREE", "", "", "YES", nil}, 251 {"long_ord_pk3", 0, "PRIMARY", 4, "z", nil, 0, nil, nil, "", "BTREE", "", "", "YES", nil}, 252 {"long_ord_pk3", 0, "PRIMARY", 5, "u", nil, 0, nil, nil, "", "BTREE", "", "", "YES", nil}, 253 }, 254 }, 255 { 256 WriteQuery: "ALTER TABLE long_ord_pk2 MODIFY COLUMN y int AFTER u", 257 ExpectedWriteResult: []sql.Row{ 258 {types.OkResult{RowsAffected: 0}}, 259 }, 260 SelectQuery: "SELECT column_name, ordinal_position FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'mydb' AND TABLE_NAME = 'long_ord_pk2' and column_key = 'PRI'", 261 ExpectedSelect: []sql.Row{ 262 {"u", uint(1)}, 263 {"y", uint(2)}, 264 {"v", uint(3)}, 265 {"x", uint(5)}, 266 {"z", uint(6)}, 267 }, 268 }, 269 { 270 WriteQuery: "ALTER TABLE long_ord_pk2 MODIFY COLUMN y int AFTER u", 271 ExpectedWriteResult: []sql.Row{ 272 {types.OkResult{RowsAffected: 0}}, 273 }, 274 SelectQuery: "show keys from long_ord_pk2", 275 ExpectedSelect: []sql.Row{ 276 {"long_ord_pk2", 0, "PRIMARY", 1, "y", nil, 0, nil, nil, "", "BTREE", "", "", "YES", nil}, 277 {"long_ord_pk2", 0, "PRIMARY", 2, "v", nil, 0, nil, nil, "", "BTREE", "", "", "YES", nil}, 278 {"long_ord_pk2", 0, "PRIMARY", 3, "x", nil, 0, nil, nil, "", "BTREE", "", "", "YES", nil}, 279 {"long_ord_pk2", 0, "PRIMARY", 4, "z", nil, 0, nil, nil, "", "BTREE", "", "", "YES", nil}, 280 {"long_ord_pk2", 0, "PRIMARY", 5, "u", nil, 0, nil, nil, "", "BTREE", "", "", "YES", nil}, 281 }, 282 }, 283 }