github.com/dolthub/go-mysql-server@v0.18.0/enginetest/queries/sysbench_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 SysbenchPlanTests = []QueryPlanTest{
    20  	{
    21  		Query: `select a.id, a.small_int_col from sbtest1 a, sbtest1 b where a.id = b.id limit 500`,
    22  		ExpectedPlan: "Limit(500)\n" +
    23  			" └─ Project\n" +
    24  			"     ├─ columns: [a.id:0!null, a.small_int_col:1!null]\n" +
    25  			"     └─ MergeJoin\n" +
    26  			"         ├─ cmp: Eq\n" +
    27  			"         │   ├─ a.id:0!null\n" +
    28  			"         │   └─ b.id:2!null\n" +
    29  			"         ├─ TableAlias(a)\n" +
    30  			"         │   └─ IndexedTableAccess(sbtest1)\n" +
    31  			"         │       ├─ index: [sbtest1.id]\n" +
    32  			"         │       ├─ static: [{[NULL, ∞)}]\n" +
    33  			"         │       ├─ colSet: (1-24)\n" +
    34  			"         │       ├─ tableId: 1\n" +
    35  			"         │       └─ Table\n" +
    36  			"         │           ├─ name: sbtest1\n" +
    37  			"         │           └─ columns: [id small_int_col]\n" +
    38  			"         └─ TableAlias(b)\n" +
    39  			"             └─ IndexedTableAccess(sbtest1)\n" +
    40  			"                 ├─ index: [sbtest1.id]\n" +
    41  			"                 ├─ static: [{[NULL, ∞)}]\n" +
    42  			"                 ├─ colSet: (25-48)\n" +
    43  			"                 ├─ tableId: 2\n" +
    44  			"                 └─ Table\n" +
    45  			"                     ├─ name: sbtest1\n" +
    46  			"                     └─ columns: [id]\n" +
    47  			"",
    48  		ExpectedEstimates: "Limit(500)\n" +
    49  			" └─ Project\n" +
    50  			"     ├─ columns: [a.id, a.small_int_col]\n" +
    51  			"     └─ MergeJoin\n" +
    52  			"         ├─ cmp: (a.id = b.id)\n" +
    53  			"         ├─ TableAlias(a)\n" +
    54  			"         │   └─ IndexedTableAccess(sbtest1)\n" +
    55  			"         │       ├─ index: [sbtest1.id]\n" +
    56  			"         │       ├─ filters: [{[NULL, ∞)}]\n" +
    57  			"         │       └─ columns: [id small_int_col]\n" +
    58  			"         └─ TableAlias(b)\n" +
    59  			"             └─ IndexedTableAccess(sbtest1)\n" +
    60  			"                 ├─ index: [sbtest1.id]\n" +
    61  			"                 ├─ filters: [{[NULL, ∞)}]\n" +
    62  			"                 └─ columns: [id]\n" +
    63  			"",
    64  		ExpectedAnalysis: "Limit(500)\n" +
    65  			" └─ Project\n" +
    66  			"     ├─ columns: [a.id, a.small_int_col]\n" +
    67  			"     └─ MergeJoin\n" +
    68  			"         ├─ cmp: (a.id = b.id)\n" +
    69  			"         ├─ TableAlias(a)\n" +
    70  			"         │   └─ IndexedTableAccess(sbtest1)\n" +
    71  			"         │       ├─ index: [sbtest1.id]\n" +
    72  			"         │       ├─ filters: [{[NULL, ∞)}]\n" +
    73  			"         │       └─ columns: [id small_int_col]\n" +
    74  			"         └─ TableAlias(b)\n" +
    75  			"             └─ IndexedTableAccess(sbtest1)\n" +
    76  			"                 ├─ index: [sbtest1.id]\n" +
    77  			"                 ├─ filters: [{[NULL, ∞)}]\n" +
    78  			"                 └─ columns: [id]\n" +
    79  			"",
    80  	},
    81  	{
    82  		Query: `select a.id, a.small_int_col, b.id, b.int_col from sbtest1 a, sbtest2 b where a.id = b.int_col limit 500`,
    83  		ExpectedPlan: "Limit(500)\n" +
    84  			" └─ Project\n" +
    85  			"     ├─ columns: [a.id:2!null, a.small_int_col:3!null, b.id:0!null, b.int_col:1!null]\n" +
    86  			"     └─ LookupJoin\n" +
    87  			"         ├─ TableAlias(b)\n" +
    88  			"         │   └─ ProcessTable\n" +
    89  			"         │       └─ Table\n" +
    90  			"         │           ├─ name: sbtest2\n" +
    91  			"         │           └─ columns: [id int_col]\n" +
    92  			"         └─ TableAlias(a)\n" +
    93  			"             └─ IndexedTableAccess(sbtest1)\n" +
    94  			"                 ├─ index: [sbtest1.id]\n" +
    95  			"                 ├─ keys: [b.int_col:1!null]\n" +
    96  			"                 ├─ colSet: (1-24)\n" +
    97  			"                 ├─ tableId: 1\n" +
    98  			"                 └─ Table\n" +
    99  			"                     ├─ name: sbtest1\n" +
   100  			"                     └─ columns: [id small_int_col]\n" +
   101  			"",
   102  		ExpectedEstimates: "Limit(500)\n" +
   103  			" └─ Project\n" +
   104  			"     ├─ columns: [a.id, a.small_int_col, b.id, b.int_col]\n" +
   105  			"     └─ LookupJoin\n" +
   106  			"         ├─ TableAlias(b)\n" +
   107  			"         │   └─ Table\n" +
   108  			"         │       ├─ name: sbtest2\n" +
   109  			"         │       └─ columns: [id int_col]\n" +
   110  			"         └─ TableAlias(a)\n" +
   111  			"             └─ IndexedTableAccess(sbtest1)\n" +
   112  			"                 ├─ index: [sbtest1.id]\n" +
   113  			"                 ├─ columns: [id small_int_col]\n" +
   114  			"                 └─ keys: b.int_col\n" +
   115  			"",
   116  		ExpectedAnalysis: "Limit(500)\n" +
   117  			" └─ Project\n" +
   118  			"     ├─ columns: [a.id, a.small_int_col, b.id, b.int_col]\n" +
   119  			"     └─ LookupJoin\n" +
   120  			"         ├─ TableAlias(b)\n" +
   121  			"         │   └─ Table\n" +
   122  			"         │       ├─ name: sbtest2\n" +
   123  			"         │       └─ columns: [id int_col]\n" +
   124  			"         └─ TableAlias(a)\n" +
   125  			"             └─ IndexedTableAccess(sbtest1)\n" +
   126  			"                 ├─ index: [sbtest1.id]\n" +
   127  			"                 ├─ columns: [id small_int_col]\n" +
   128  			"                 └─ keys: b.int_col\n" +
   129  			"",
   130  	},
   131  	{
   132  		Query: `SELECT year_col, count(year_col), max(big_int_col), avg(small_int_col) FROM sbtest1 WHERE big_int_col > 0 GROUP BY set_col ORDER BY year_col`,
   133  		ExpectedPlan: "Project\n" +
   134  			" ├─ columns: [sbtest1.year_col:3!null, count(sbtest1.year_col):1!null as count(year_col), max(sbtest1.big_int_col):2!null as max(big_int_col), avg(sbtest1.small_int_col):0 as avg(small_int_col)]\n" +
   135  			" └─ Sort(sbtest1.year_col:3!null ASC nullsFirst)\n" +
   136  			"     └─ GroupBy\n" +
   137  			"         ├─ select: AVG(sbtest1.small_int_col:0!null), COUNT(sbtest1.year_col:3!null), MAX(sbtest1.big_int_col:1!null), sbtest1.year_col:3!null\n" +
   138  			"         ├─ group: sbtest1.set_col:2!null\n" +
   139  			"         └─ IndexedTableAccess(sbtest1)\n" +
   140  			"             ├─ index: [sbtest1.big_int_col]\n" +
   141  			"             ├─ static: [{(0, ∞)}]\n" +
   142  			"             ├─ colSet: (1-24)\n" +
   143  			"             ├─ tableId: 1\n" +
   144  			"             └─ Table\n" +
   145  			"                 ├─ name: sbtest1\n" +
   146  			"                 └─ columns: [small_int_col big_int_col set_col year_col]\n" +
   147  			"",
   148  		ExpectedEstimates: "Project\n" +
   149  			" ├─ columns: [sbtest1.year_col, count(sbtest1.year_col) as count(year_col), max(sbtest1.big_int_col) as max(big_int_col), avg(sbtest1.small_int_col) as avg(small_int_col)]\n" +
   150  			" └─ Sort(sbtest1.year_col ASC)\n" +
   151  			"     └─ GroupBy\n" +
   152  			"         ├─ SelectedExprs(AVG(sbtest1.small_int_col), COUNT(sbtest1.year_col), MAX(sbtest1.big_int_col), sbtest1.year_col)\n" +
   153  			"         ├─ Grouping(sbtest1.set_col)\n" +
   154  			"         └─ IndexedTableAccess(sbtest1)\n" +
   155  			"             ├─ index: [sbtest1.big_int_col]\n" +
   156  			"             ├─ filters: [{(0, ∞)}]\n" +
   157  			"             └─ columns: [small_int_col big_int_col set_col year_col]\n" +
   158  			"",
   159  		ExpectedAnalysis: "Project\n" +
   160  			" ├─ columns: [sbtest1.year_col, count(sbtest1.year_col) as count(year_col), max(sbtest1.big_int_col) as max(big_int_col), avg(sbtest1.small_int_col) as avg(small_int_col)]\n" +
   161  			" └─ Sort(sbtest1.year_col ASC)\n" +
   162  			"     └─ GroupBy\n" +
   163  			"         ├─ SelectedExprs(AVG(sbtest1.small_int_col), COUNT(sbtest1.year_col), MAX(sbtest1.big_int_col), sbtest1.year_col)\n" +
   164  			"         ├─ Grouping(sbtest1.set_col)\n" +
   165  			"         └─ IndexedTableAccess(sbtest1)\n" +
   166  			"             ├─ index: [sbtest1.big_int_col]\n" +
   167  			"             ├─ filters: [{(0, ∞)}]\n" +
   168  			"             └─ columns: [small_int_col big_int_col set_col year_col]\n" +
   169  			"",
   170  	},
   171  	{
   172  		Query: `SELECT count(id) FROM sbtest1 WHERE big_int_col > 0`,
   173  		ExpectedPlan: "Project\n" +
   174  			" ├─ columns: [count(sbtest1.id):0!null as count(id)]\n" +
   175  			" └─ GroupBy\n" +
   176  			"     ├─ select: COUNT(sbtest1.id:0!null)\n" +
   177  			"     ├─ group: \n" +
   178  			"     └─ IndexedTableAccess(sbtest1)\n" +
   179  			"         ├─ index: [sbtest1.big_int_col]\n" +
   180  			"         ├─ static: [{(0, ∞)}]\n" +
   181  			"         ├─ colSet: (1-24)\n" +
   182  			"         ├─ tableId: 1\n" +
   183  			"         └─ Table\n" +
   184  			"             ├─ name: sbtest1\n" +
   185  			"             └─ columns: [id big_int_col]\n" +
   186  			"",
   187  		ExpectedEstimates: "Project\n" +
   188  			" ├─ columns: [count(sbtest1.id) as count(id)]\n" +
   189  			" └─ GroupBy\n" +
   190  			"     ├─ SelectedExprs(COUNT(sbtest1.id))\n" +
   191  			"     ├─ Grouping()\n" +
   192  			"     └─ IndexedTableAccess(sbtest1)\n" +
   193  			"         ├─ index: [sbtest1.big_int_col]\n" +
   194  			"         ├─ filters: [{(0, ∞)}]\n" +
   195  			"         └─ columns: [id big_int_col]\n" +
   196  			"",
   197  		ExpectedAnalysis: "Project\n" +
   198  			" ├─ columns: [count(sbtest1.id) as count(id)]\n" +
   199  			" └─ GroupBy\n" +
   200  			"     ├─ SelectedExprs(COUNT(sbtest1.id))\n" +
   201  			"     ├─ Grouping()\n" +
   202  			"     └─ IndexedTableAccess(sbtest1)\n" +
   203  			"         ├─ index: [sbtest1.big_int_col]\n" +
   204  			"         ├─ filters: [{(0, ∞)}]\n" +
   205  			"         └─ columns: [id big_int_col]\n" +
   206  			"",
   207  	},
   208  	{
   209  		Query: `SELECT * FROM sbtest1 WHERE big_int_col > 0`,
   210  		ExpectedPlan: "IndexedTableAccess(sbtest1)\n" +
   211  			" ├─ index: [sbtest1.big_int_col]\n" +
   212  			" ├─ static: [{(0, ∞)}]\n" +
   213  			" ├─ colSet: (1-24)\n" +
   214  			" ├─ tableId: 1\n" +
   215  			" └─ Table\n" +
   216  			"     ├─ name: sbtest1\n" +
   217  			"     └─ columns: [id tiny_int_col unsigned_tiny_int_col small_int_col unsigned_small_int_col medium_int_col unsigned_medium_int_col int_col unsigned_int_col big_int_col unsigned_big_int_col decimal_col float_col double_col bit_col char_col var_char_col enum_col set_col date_col time_col datetime_col timestamp_col year_col]\n" +
   218  			"",
   219  		ExpectedEstimates: "IndexedTableAccess(sbtest1)\n" +
   220  			" ├─ index: [sbtest1.big_int_col]\n" +
   221  			" ├─ filters: [{(0, ∞)}]\n" +
   222  			" └─ columns: [id tiny_int_col unsigned_tiny_int_col small_int_col unsigned_small_int_col medium_int_col unsigned_medium_int_col int_col unsigned_int_col big_int_col unsigned_big_int_col decimal_col float_col double_col bit_col char_col var_char_col enum_col set_col date_col time_col datetime_col timestamp_col year_col]\n" +
   223  			"",
   224  		ExpectedAnalysis: "IndexedTableAccess(sbtest1)\n" +
   225  			" ├─ index: [sbtest1.big_int_col]\n" +
   226  			" ├─ filters: [{(0, ∞)}]\n" +
   227  			" └─ columns: [id tiny_int_col unsigned_tiny_int_col small_int_col unsigned_small_int_col medium_int_col unsigned_medium_int_col int_col unsigned_int_col big_int_col unsigned_big_int_col decimal_col float_col double_col bit_col char_col var_char_col enum_col set_col date_col time_col datetime_col timestamp_col year_col]\n" +
   228  			"",
   229  	},
   230  }