github.com/dolthub/go-mysql-server@v0.18.0/enginetest/queries/generated_column_plans.go (about)

     1  // Code generated by plangen.
     2  
     3  // Copyright 2024 Dolthub, Inc.
     4  //
     5  // Licensed under the Apache License, Version 2.0 (the "License");
     6  // you may not use this file except in compliance with the License.
     7  // You may obtain a copy of the License at
     8  //
     9  //     http://www.apache.org/licenses/LICENSE-2.0
    10  //
    11  // Unless required by applicable law or agreed to in writing, software
    12  // distributed under the License is distributed on an "AS IS" BASIS,
    13  // WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
    14  // See the License for the specific language governing permissions and
    15  // limitations under the License.
    16  
    17  package queries
    18  
    19  var GeneratedColumnPlanTests = []QueryPlanTest{
    20  	{
    21  		Query: `select * from generated_stored_1 where b = 2 order by a`,
    22  		ExpectedPlan: "Sort(generated_stored_1.a:0!null ASC nullsFirst)\n" +
    23  			" └─ IndexedTableAccess(generated_stored_1)\n" +
    24  			"     ├─ index: [generated_stored_1.b]\n" +
    25  			"     ├─ static: [{[2, 2]}]\n" +
    26  			"     ├─ colSet: (1,2)\n" +
    27  			"     ├─ tableId: 1\n" +
    28  			"     └─ Table\n" +
    29  			"         ├─ name: generated_stored_1\n" +
    30  			"         └─ columns: [a b]\n" +
    31  			"",
    32  		ExpectedEstimates: "Sort(generated_stored_1.a ASC)\n" +
    33  			" └─ IndexedTableAccess(generated_stored_1)\n" +
    34  			"     ├─ index: [generated_stored_1.b]\n" +
    35  			"     ├─ filters: [{[2, 2]}]\n" +
    36  			"     └─ columns: [a b]\n" +
    37  			"",
    38  		ExpectedAnalysis: "Sort(generated_stored_1.a ASC)\n" +
    39  			" └─ IndexedTableAccess(generated_stored_1)\n" +
    40  			"     ├─ index: [generated_stored_1.b]\n" +
    41  			"     ├─ filters: [{[2, 2]}]\n" +
    42  			"     └─ columns: [a b]\n" +
    43  			"",
    44  	},
    45  	{
    46  		Query: `select * from generated_stored_2 where b = 2 and c = 3 order by a`,
    47  		ExpectedPlan: "Sort(generated_stored_2.a:0!null ASC nullsFirst)\n" +
    48  			" └─ IndexedTableAccess(generated_stored_2)\n" +
    49  			"     ├─ index: [generated_stored_2.b,generated_stored_2.c]\n" +
    50  			"     ├─ static: [{[2, 2], [3, 3]}]\n" +
    51  			"     ├─ colSet: (1-3)\n" +
    52  			"     ├─ tableId: 1\n" +
    53  			"     └─ Table\n" +
    54  			"         ├─ name: generated_stored_2\n" +
    55  			"         └─ columns: [a b c]\n" +
    56  			"",
    57  		ExpectedEstimates: "Sort(generated_stored_2.a ASC)\n" +
    58  			" └─ IndexedTableAccess(generated_stored_2)\n" +
    59  			"     ├─ index: [generated_stored_2.b,generated_stored_2.c]\n" +
    60  			"     ├─ filters: [{[2, 2], [3, 3]}]\n" +
    61  			"     └─ columns: [a b c]\n" +
    62  			"",
    63  		ExpectedAnalysis: "Sort(generated_stored_2.a ASC)\n" +
    64  			" └─ IndexedTableAccess(generated_stored_2)\n" +
    65  			"     ├─ index: [generated_stored_2.b,generated_stored_2.c]\n" +
    66  			"     ├─ filters: [{[2, 2], [3, 3]}]\n" +
    67  			"     └─ columns: [a b c]\n" +
    68  			"",
    69  	},
    70  	{
    71  		Query: `delete from generated_stored_2 where b = 3 and c = 4`,
    72  		ExpectedPlan: "RowUpdateAccumulator\n" +
    73  			" └─ Delete\n" +
    74  			"     └─ IndexedTableAccess(generated_stored_2)\n" +
    75  			"         ├─ index: [generated_stored_2.b,generated_stored_2.c]\n" +
    76  			"         ├─ static: [{[3, 3], [4, 4]}]\n" +
    77  			"         ├─ colSet: (1-3)\n" +
    78  			"         ├─ tableId: 1\n" +
    79  			"         └─ Table\n" +
    80  			"             ├─ name: generated_stored_2\n" +
    81  			"             └─ columns: [a b c]\n" +
    82  			"",
    83  	},
    84  	{
    85  		Query: `update generated_stored_2 set a = 5, c = 10 where b = 2 and c = 3`,
    86  		ExpectedPlan: "RowUpdateAccumulator\n" +
    87  			" └─ Update\n" +
    88  			"     └─ UpdateSource(SET generated_stored_2.a:0!null = 5 (tinyint),SET generated_stored_2.c:2 = 10 (tinyint),SET generated_stored_2.b:1 = parenthesized((generated_stored_2.a:0!null + 1 (tinyint))))\n" +
    89  			"         └─ IndexedTableAccess(generated_stored_2)\n" +
    90  			"             ├─ index: [generated_stored_2.b,generated_stored_2.c]\n" +
    91  			"             ├─ static: [{[2, 2], [3, 3]}]\n" +
    92  			"             ├─ colSet: (1-3)\n" +
    93  			"             ├─ tableId: 1\n" +
    94  			"             └─ Table\n" +
    95  			"                 ├─ name: generated_stored_2\n" +
    96  			"                 └─ columns: [a b c]\n" +
    97  			"",
    98  	},
    99  	{
   100  		Query: `select * from generated_virtual_1 where c = 7`,
   101  		ExpectedPlan: "IndexedTableAccess(generated_virtual_1)\n" +
   102  			" ├─ index: [generated_virtual_1.c]\n" +
   103  			" ├─ static: [{[7, 7]}]\n" +
   104  			" ├─ colSet: (4-6)\n" +
   105  			" ├─ tableId: 2\n" +
   106  			" └─ VirtualColumnTable\n" +
   107  			"     ├─ name: generated_virtual_1\n" +
   108  			"     ├─ columns: [generated_virtual_1.a:0!null, generated_virtual_1.b:1, parenthesized((generated_virtual_1.a:0!null + generated_virtual_1.b:1))]\n" +
   109  			"     └─ Table\n" +
   110  			"         ├─ name: generated_virtual_1\n" +
   111  			"         └─ columns: [a b c]\n" +
   112  			"",
   113  		ExpectedEstimates: "IndexedTableAccess(generated_virtual_1)\n" +
   114  			" ├─ index: [generated_virtual_1.c]\n" +
   115  			" └─ filters: [{[7, 7]}]\n" +
   116  			"",
   117  		ExpectedAnalysis: "IndexedTableAccess(generated_virtual_1)\n" +
   118  			" ├─ index: [generated_virtual_1.c]\n" +
   119  			" └─ filters: [{[7, 7]}]\n" +
   120  			"",
   121  	},
   122  	{
   123  		Query: `update generated_virtual_1 set b = 5 where c = 3`,
   124  		ExpectedPlan: "RowUpdateAccumulator\n" +
   125  			" └─ Update\n" +
   126  			"     └─ UpdateSource(SET generated_virtual_1.b:1 = 5 (tinyint),SET generated_virtual_1.c:2 = parenthesized((generated_virtual_1.a:0!null + generated_virtual_1.b:1)))\n" +
   127  			"         └─ IndexedTableAccess(generated_virtual_1)\n" +
   128  			"             ├─ index: [generated_virtual_1.c]\n" +
   129  			"             ├─ static: [{[3, 3]}]\n" +
   130  			"             ├─ colSet: (4-6)\n" +
   131  			"             ├─ tableId: 2\n" +
   132  			"             └─ VirtualColumnTable\n" +
   133  			"                 ├─ name: generated_virtual_1\n" +
   134  			"                 ├─ columns: [generated_virtual_1.a:0!null, generated_virtual_1.b:1, parenthesized((generated_virtual_1.a:0!null + generated_virtual_1.b:1))]\n" +
   135  			"                 └─ Table\n" +
   136  			"                     ├─ name: generated_virtual_1\n" +
   137  			"                     └─ columns: [a b c]\n" +
   138  			"",
   139  	},
   140  	{
   141  		Query: `delete from generated_virtual_1 where c = 6`,
   142  		ExpectedPlan: "RowUpdateAccumulator\n" +
   143  			" └─ Delete\n" +
   144  			"     └─ IndexedTableAccess(generated_virtual_1)\n" +
   145  			"         ├─ index: [generated_virtual_1.c]\n" +
   146  			"         ├─ static: [{[6, 6]}]\n" +
   147  			"         ├─ colSet: (4-6)\n" +
   148  			"         ├─ tableId: 2\n" +
   149  			"         └─ VirtualColumnTable\n" +
   150  			"             ├─ name: generated_virtual_1\n" +
   151  			"             ├─ columns: [generated_virtual_1.a:0!null, generated_virtual_1.b:1, parenthesized((generated_virtual_1.a:0!null + generated_virtual_1.b:1))]\n" +
   152  			"             └─ Table\n" +
   153  			"                 ├─ name: generated_virtual_1\n" +
   154  			"                 └─ columns: [a b c]\n" +
   155  			"",
   156  	},
   157  	{
   158  		Query: `select * from generated_virtual_keyless where v = 2`,
   159  		ExpectedPlan: "IndexedTableAccess(generated_virtual_keyless)\n" +
   160  			" ├─ index: [generated_virtual_keyless.v]\n" +
   161  			" ├─ static: [{[2, 2]}]\n" +
   162  			" ├─ colSet: (3,4)\n" +
   163  			" ├─ tableId: 2\n" +
   164  			" └─ VirtualColumnTable\n" +
   165  			"     ├─ name: generated_virtual_keyless\n" +
   166  			"     ├─ columns: [generated_virtual_keyless.j:0, parenthesized(json_unquote(json_extract(generated_virtual_keyless.j, '$.a')))]\n" +
   167  			"     └─ Table\n" +
   168  			"         ├─ name: generated_virtual_keyless\n" +
   169  			"         └─ columns: [j v]\n" +
   170  			"",
   171  		ExpectedEstimates: "IndexedTableAccess(generated_virtual_keyless)\n" +
   172  			" ├─ index: [generated_virtual_keyless.v]\n" +
   173  			" └─ filters: [{[2, 2]}]\n" +
   174  			"",
   175  		ExpectedAnalysis: "IndexedTableAccess(generated_virtual_keyless)\n" +
   176  			" ├─ index: [generated_virtual_keyless.v]\n" +
   177  			" └─ filters: [{[2, 2]}]\n" +
   178  			"",
   179  	},
   180  	{
   181  		Query: `update generated_virtual_keyless set j = '{"a": 5}' where v = 2`,
   182  		ExpectedPlan: "RowUpdateAccumulator\n" +
   183  			" └─ Update\n" +
   184  			"     └─ UpdateSource(SET generated_virtual_keyless.j:0 = {\"a\": 5} (longtext),SET generated_virtual_keyless.v:1 = parenthesized(json_unquote(json_extract(generated_virtual_keyless.j, '$.a'))))\n" +
   185  			"         └─ IndexedTableAccess(generated_virtual_keyless)\n" +
   186  			"             ├─ index: [generated_virtual_keyless.v]\n" +
   187  			"             ├─ static: [{[2, 2]}]\n" +
   188  			"             ├─ colSet: (3,4)\n" +
   189  			"             ├─ tableId: 2\n" +
   190  			"             └─ VirtualColumnTable\n" +
   191  			"                 ├─ name: generated_virtual_keyless\n" +
   192  			"                 ├─ columns: [generated_virtual_keyless.j:0, parenthesized(json_unquote(json_extract(generated_virtual_keyless.j, '$.a')))]\n" +
   193  			"                 └─ Table\n" +
   194  			"                     ├─ name: generated_virtual_keyless\n" +
   195  			"                     └─ columns: [j v]\n" +
   196  			"",
   197  	},
   198  	{
   199  		Query: `delete from generated_virtual_keyless where v = 5`,
   200  		ExpectedPlan: "RowUpdateAccumulator\n" +
   201  			" └─ Delete\n" +
   202  			"     └─ IndexedTableAccess(generated_virtual_keyless)\n" +
   203  			"         ├─ index: [generated_virtual_keyless.v]\n" +
   204  			"         ├─ static: [{[5, 5]}]\n" +
   205  			"         ├─ colSet: (3,4)\n" +
   206  			"         ├─ tableId: 2\n" +
   207  			"         └─ VirtualColumnTable\n" +
   208  			"             ├─ name: generated_virtual_keyless\n" +
   209  			"             ├─ columns: [generated_virtual_keyless.j:0, parenthesized(json_unquote(json_extract(generated_virtual_keyless.j, '$.a')))]\n" +
   210  			"             └─ Table\n" +
   211  			"                 ├─ name: generated_virtual_keyless\n" +
   212  			"                 └─ columns: [j v]\n" +
   213  			"",
   214  	},
   215  }