vitess.io/vitess@v0.16.2/go/vt/vtgate/planbuilder/testdata/hash_joins.txt (about)

     1  # Test cases in this file are currently turned off
     2  # Multi-route unique vindex constraint (with hash join)
     3  "select /*vt+ ALLOW_HASH_JOIN */ user_extra.id from user join user_extra on user.col = user_extra.col where user.id = 5"
     4  {
     5    "QueryType": "SELECT",
     6    "Original": "select /*vt+ ALLOW_HASH_JOIN */ user_extra.id from user join user_extra on user.col = user_extra.col where user.id = 5",
     7    "Instructions": {
     8      "OperatorType": "Join",
     9      "Variant": "Join",
    10      "JoinColumnIndexes": "1",
    11      "JoinVars": {
    12        "user_col": 0
    13      },
    14      "TableName": "`user`_user_extra",
    15      "Inputs": [
    16        {
    17          "OperatorType": "Route",
    18          "Variant": "EqualUnique",
    19          "Keyspace": {
    20            "Name": "user",
    21            "Sharded": true
    22          },
    23          "FieldQuery": "select `user`.col from `user` where 1 != 1",
    24          "Query": "select /*vt+ ALLOW_HASH_JOIN */ `user`.col from `user` where `user`.id = 5",
    25          "Table": "`user`",
    26          "Values": [
    27            "INT64(5)"
    28          ],
    29          "Vindex": "user_index"
    30        },
    31        {
    32          "OperatorType": "Route",
    33          "Variant": "Scatter",
    34          "Keyspace": {
    35            "Name": "user",
    36            "Sharded": true
    37          },
    38          "FieldQuery": "select user_extra.id from user_extra where 1 != 1",
    39          "Query": "select /*vt+ ALLOW_HASH_JOIN */ user_extra.id from user_extra where user_extra.col = :user_col",
    40          "Table": "user_extra"
    41        }
    42      ]
    43    }
    44  }
    45  {
    46    "QueryType": "SELECT",
    47    "Original": "select /*vt+ ALLOW_HASH_JOIN */ user_extra.id from user join user_extra on user.col = user_extra.col where user.id = 5",
    48    "Instructions": {
    49      "OperatorType": "Join",
    50      "Variant": "HashJoin",
    51      "ComparisonType": "INT16",
    52      "JoinColumnIndexes": "2",
    53      "Predicate": "`user`.col = user_extra.col",
    54      "TableName": "`user`_user_extra",
    55      "Inputs": [
    56        {
    57          "OperatorType": "Route",
    58          "Variant": "EqualUnique",
    59          "Keyspace": {
    60            "Name": "user",
    61            "Sharded": true
    62          },
    63          "FieldQuery": "select `user`.col from `user` where 1 != 1",
    64          "Query": "select /*vt+ ALLOW_HASH_JOIN */ `user`.col from `user` where `user`.id = 5",
    65          "Table": "`user`",
    66          "Values": [
    67            "INT64(5)"
    68          ],
    69          "Vindex": "user_index"
    70        },
    71        {
    72          "OperatorType": "Route",
    73          "Variant": "Scatter",
    74          "Keyspace": {
    75            "Name": "user",
    76            "Sharded": true
    77          },
    78          "FieldQuery": "select user_extra.col, user_extra.id from user_extra where 1 != 1",
    79          "Query": "select /*vt+ ALLOW_HASH_JOIN */ user_extra.col, user_extra.id from user_extra",
    80          "Table": "user_extra"
    81        }
    82      ]
    83    }
    84  }
    85  
    86  
    87  # Multi-route with non-route constraint, should use first route.
    88  "select /*vt+ ALLOW_HASH_JOIN */ user_extra.id from user join user_extra on user.col = user_extra.col where 1 = 1"
    89  {
    90    "QueryType": "SELECT",
    91    "Original": "select /*vt+ ALLOW_HASH_JOIN */ user_extra.id from user join user_extra on user.col = user_extra.col where 1 = 1",
    92    "Instructions": {
    93      "OperatorType": "Join",
    94      "Variant": "Join",
    95      "JoinColumnIndexes": "1",
    96      "JoinVars": {
    97        "user_col": 0
    98      },
    99      "TableName": "`user`_user_extra",
   100      "Inputs": [
   101        {
   102          "OperatorType": "Route",
   103          "Variant": "Scatter",
   104          "Keyspace": {
   105            "Name": "user",
   106            "Sharded": true
   107          },
   108          "FieldQuery": "select `user`.col from `user` where 1 != 1",
   109          "Query": "select /*vt+ ALLOW_HASH_JOIN */ `user`.col from `user` where 1 = 1",
   110          "Table": "`user`"
   111        },
   112        {
   113          "OperatorType": "Route",
   114          "Variant": "Scatter",
   115          "Keyspace": {
   116            "Name": "user",
   117            "Sharded": true
   118          },
   119          "FieldQuery": "select user_extra.id from user_extra where 1 != 1",
   120          "Query": "select /*vt+ ALLOW_HASH_JOIN */ user_extra.id from user_extra where user_extra.col = :user_col",
   121          "Table": "user_extra"
   122        }
   123      ]
   124    }
   125  }
   126  {
   127    "QueryType": "SELECT",
   128    "Original": "select /*vt+ ALLOW_HASH_JOIN */ user_extra.id from user join user_extra on user.col = user_extra.col where 1 = 1",
   129    "Instructions": {
   130      "OperatorType": "Join",
   131      "Variant": "HashJoin",
   132      "ComparisonType": "INT16",
   133      "JoinColumnIndexes": "2",
   134      "Predicate": "`user`.col = user_extra.col",
   135      "TableName": "`user`_user_extra",
   136      "Inputs": [
   137        {
   138          "OperatorType": "Route",
   139          "Variant": "Scatter",
   140          "Keyspace": {
   141            "Name": "user",
   142            "Sharded": true
   143          },
   144          "FieldQuery": "select `user`.col from `user` where 1 != 1",
   145          "Query": "select /*vt+ ALLOW_HASH_JOIN */ `user`.col from `user` where 1 = 1",
   146          "Table": "`user`"
   147        },
   148        {
   149          "OperatorType": "Route",
   150          "Variant": "Scatter",
   151          "Keyspace": {
   152            "Name": "user",
   153            "Sharded": true
   154          },
   155          "FieldQuery": "select user_extra.col, user_extra.id from user_extra where 1 != 1",
   156          "Query": "select /*vt+ ALLOW_HASH_JOIN */ user_extra.col, user_extra.id from user_extra where 1 = 1",
   157          "Table": "user_extra"
   158        }
   159      ]
   160    }
   161  }
   162  
   163  # wire-up on within cross-shard derived table (hash-join version)
   164  "select /*vt+ ALLOW_HASH_JOIN */ t.id from (select user.id, user.col1 from user join user_extra on user_extra.col = user.col) as t"
   165  {
   166    "QueryType": "SELECT",
   167    "Original": "select /*vt+ ALLOW_HASH_JOIN */ t.id from (select user.id, user.col1 from user join user_extra on user_extra.col = user.col) as t",
   168    "Instructions": {
   169      "OperatorType": "SimpleProjection",
   170      "Columns": [
   171        0
   172      ],
   173      "Inputs": [
   174        {
   175          "OperatorType": "Join",
   176          "Variant": "Join",
   177          "JoinColumnIndexes": "-1,-2",
   178          "JoinVars": {
   179            "user_col": 2
   180          },
   181          "TableName": "`user`_user_extra",
   182          "Inputs": [
   183            {
   184              "OperatorType": "Route",
   185              "Variant": "Scatter",
   186              "Keyspace": {
   187                "Name": "user",
   188                "Sharded": true
   189              },
   190              "FieldQuery": "select `user`.id, `user`.col1, `user`.col from `user` where 1 != 1",
   191              "Query": "select /*vt+ ALLOW_HASH_JOIN */ `user`.id, `user`.col1, `user`.col from `user`",
   192              "Table": "`user`"
   193            },
   194            {
   195              "OperatorType": "Route",
   196              "Variant": "Scatter",
   197              "Keyspace": {
   198                "Name": "user",
   199                "Sharded": true
   200              },
   201              "FieldQuery": "select 1 from user_extra where 1 != 1",
   202              "Query": "select /*vt+ ALLOW_HASH_JOIN */ 1 from user_extra where user_extra.col = :user_col",
   203              "Table": "user_extra"
   204            }
   205          ]
   206        }
   207      ]
   208    }
   209  }
   210  {
   211    "QueryType": "SELECT",
   212    "Original": "select /*vt+ ALLOW_HASH_JOIN */ t.id from (select user.id, user.col1 from user join user_extra on user_extra.col = user.col) as t",
   213    "Instructions": {
   214      "OperatorType": "SimpleProjection",
   215      "Columns": [
   216        0
   217      ],
   218      "Inputs": [
   219        {
   220          "OperatorType": "Join",
   221          "Variant": "HashJoin",
   222          "ComparisonType": "INT16",
   223          "JoinColumnIndexes": "-2,-3",
   224          "Predicate": "user_extra.col = `user`.col",
   225          "TableName": "`user`_user_extra",
   226          "Inputs": [
   227            {
   228              "OperatorType": "Route",
   229              "Variant": "Scatter",
   230              "Keyspace": {
   231                "Name": "user",
   232                "Sharded": true
   233              },
   234              "FieldQuery": "select `user`.col, `user`.id, `user`.col1 from `user` where 1 != 1",
   235              "Query": "select /*vt+ ALLOW_HASH_JOIN */ `user`.col, `user`.id, `user`.col1 from `user`",
   236              "Table": "`user`"
   237            },
   238            {
   239              "OperatorType": "Route",
   240              "Variant": "Scatter",
   241              "Keyspace": {
   242                "Name": "user",
   243                "Sharded": true
   244              },
   245              "FieldQuery": "select user_extra.col from user_extra where 1 != 1",
   246              "Query": "select /*vt+ ALLOW_HASH_JOIN */ user_extra.col from user_extra",
   247              "Table": "user_extra"
   248            }
   249          ]
   250        }
   251      ]
   252    }
   253  }
   254  
   255  # hash join on int columns
   256  "select /*vt+ ALLOW_HASH_JOIN */ u.id from user as u join user as uu on u.intcol = uu.intcol"
   257  {
   258    "QueryType": "SELECT",
   259    "Original": "select /*vt+ ALLOW_HASH_JOIN */ u.id from user as u join user as uu on u.intcol = uu.intcol",
   260    "Instructions": {
   261      "OperatorType": "Join",
   262      "Variant": "Join",
   263      "JoinColumnIndexes": "-1",
   264      "JoinVars": {
   265        "u_intcol": 1
   266      },
   267      "TableName": "`user`_`user`",
   268      "Inputs": [
   269        {
   270          "OperatorType": "Route",
   271          "Variant": "Scatter",
   272          "Keyspace": {
   273            "Name": "user",
   274            "Sharded": true
   275          },
   276          "FieldQuery": "select u.id, u.intcol from `user` as u where 1 != 1",
   277          "Query": "select /*vt+ ALLOW_HASH_JOIN */ u.id, u.intcol from `user` as u",
   278          "Table": "`user`"
   279        },
   280        {
   281          "OperatorType": "Route",
   282          "Variant": "Scatter",
   283          "Keyspace": {
   284            "Name": "user",
   285            "Sharded": true
   286          },
   287          "FieldQuery": "select 1 from `user` as uu where 1 != 1",
   288          "Query": "select /*vt+ ALLOW_HASH_JOIN */ 1 from `user` as uu where uu.intcol = :u_intcol",
   289          "Table": "`user`"
   290        }
   291      ]
   292    }
   293  }
   294  {
   295    "QueryType": "SELECT",
   296    "Original": "select /*vt+ ALLOW_HASH_JOIN */ u.id from user as u join user as uu on u.intcol = uu.intcol",
   297    "Instructions": {
   298      "OperatorType": "Join",
   299      "Variant": "HashJoin",
   300      "ComparisonType": "INT16",
   301      "JoinColumnIndexes": "-2",
   302      "Predicate": "u.intcol = uu.intcol",
   303      "TableName": "`user`_`user`",
   304      "Inputs": [
   305        {
   306          "OperatorType": "Route",
   307          "Variant": "Scatter",
   308          "Keyspace": {
   309            "Name": "user",
   310            "Sharded": true
   311          },
   312          "FieldQuery": "select u.intcol, u.id from `user` as u where 1 != 1",
   313          "Query": "select /*vt+ ALLOW_HASH_JOIN */ u.intcol, u.id from `user` as u",
   314          "Table": "`user`"
   315        },
   316        {
   317          "OperatorType": "Route",
   318          "Variant": "Scatter",
   319          "Keyspace": {
   320            "Name": "user",
   321            "Sharded": true
   322          },
   323          "FieldQuery": "select uu.intcol from `user` as uu where 1 != 1",
   324          "Query": "select /*vt+ ALLOW_HASH_JOIN */ uu.intcol from `user` as uu",
   325          "Table": "`user`"
   326        }
   327      ]
   328    }
   329  }
   330  
   331  # Author5.joins(books: [{orders: :customer}, :supplier]) (with hash join)
   332  "select /*vt+ ALLOW_HASH_JOIN */ author5s.* from author5s join book6s on book6s.author5_id = author5s.id join book6s_order2s on book6s_order2s.book6_id = book6s.id join order2s on order2s.id = book6s_order2s.order2_id join customer2s on customer2s.id = order2s.customer2_id join supplier5s on supplier5s.id = book6s.supplier5_id"
   333  {
   334    "QueryType": "SELECT",
   335    "Original": "select /*vt+ ALLOW_HASH_JOIN */ author5s.* from author5s join book6s on book6s.author5_id = author5s.id join book6s_order2s on book6s_order2s.book6_id = book6s.id join order2s on order2s.id = book6s_order2s.order2_id join customer2s on customer2s.id = order2s.customer2_id join supplier5s on supplier5s.id = book6s.supplier5_id",
   336    "Instructions": {
   337      "OperatorType": "Join",
   338      "Variant": "Join",
   339      "JoinColumnIndexes": "-1,-2,-3,-4",
   340      "JoinVars": {
   341        "book6s_supplier5_id": 4
   342      },
   343      "TableName": "author5s, book6s_book6s_order2s_order2s_customer2s_supplier5s",
   344      "Inputs": [
   345        {
   346          "OperatorType": "Join",
   347          "Variant": "Join",
   348          "JoinColumnIndexes": "-1,-2,-3,-4,-5",
   349          "JoinVars": {
   350            "order2s_customer2_id": 5
   351          },
   352          "TableName": "author5s, book6s_book6s_order2s_order2s_customer2s",
   353          "Inputs": [
   354            {
   355              "OperatorType": "Join",
   356              "Variant": "Join",
   357              "JoinColumnIndexes": "-1,-2,-3,-4,-5,1",
   358              "JoinVars": {
   359                "book6s_order2s_order2_id": 5
   360              },
   361              "TableName": "author5s, book6s_book6s_order2s_order2s",
   362              "Inputs": [
   363                {
   364                  "OperatorType": "Join",
   365                  "Variant": "Join",
   366                  "JoinColumnIndexes": "-1,-2,-3,-4,-5,1",
   367                  "JoinVars": {
   368                    "book6s_id": 5
   369                  },
   370                  "TableName": "author5s, book6s_book6s_order2s",
   371                  "Inputs": [
   372                    {
   373                      "OperatorType": "Route",
   374                      "Variant": "Scatter",
   375                      "Keyspace": {
   376                        "Name": "user",
   377                        "Sharded": true
   378                      },
   379                      "FieldQuery": "select author5s.id, author5s.`name`, author5s.created_at, author5s.updated_at, book6s.supplier5_id, book6s.id from author5s join book6s on book6s.author5_id = author5s.id where 1 != 1",
   380                      "Query": "select /*vt+ ALLOW_HASH_JOIN */ author5s.id, author5s.`name`, author5s.created_at, author5s.updated_at, book6s.supplier5_id, book6s.id from author5s join book6s on book6s.author5_id = author5s.id",
   381                      "Table": "author5s, book6s"
   382                    },
   383                    {
   384                      "OperatorType": "Route",
   385                      "Variant": "EqualUnique",
   386                      "Keyspace": {
   387                        "Name": "user",
   388                        "Sharded": true
   389                      },
   390                      "FieldQuery": "select book6s_order2s.order2_id from book6s_order2s where 1 != 1",
   391                      "Query": "select /*vt+ ALLOW_HASH_JOIN */ book6s_order2s.order2_id from book6s_order2s where book6s_order2s.book6_id = :book6s_id",
   392                      "Table": "book6s_order2s",
   393                      "Values": [
   394                        ":book6s_id"
   395                      ],
   396                      "Vindex": "binary_md5"
   397                    }
   398                  ]
   399                },
   400                {
   401                  "OperatorType": "Route",
   402                  "Variant": "Scatter",
   403                  "Keyspace": {
   404                    "Name": "user",
   405                    "Sharded": true
   406                  },
   407                  "FieldQuery": "select order2s.customer2_id from order2s where 1 != 1",
   408                  "Query": "select /*vt+ ALLOW_HASH_JOIN */ order2s.customer2_id from order2s where order2s.id = :book6s_order2s_order2_id",
   409                  "Table": "order2s"
   410                }
   411              ]
   412            },
   413            {
   414              "OperatorType": "Route",
   415              "Variant": "EqualUnique",
   416              "Keyspace": {
   417                "Name": "user",
   418                "Sharded": true
   419              },
   420              "FieldQuery": "select 1 from customer2s where 1 != 1",
   421              "Query": "select /*vt+ ALLOW_HASH_JOIN */ 1 from customer2s where customer2s.id = :order2s_customer2_id",
   422              "Table": "customer2s",
   423              "Values": [
   424                ":order2s_customer2_id"
   425              ],
   426              "Vindex": "binary_md5"
   427            }
   428          ]
   429        },
   430        {
   431          "OperatorType": "Route",
   432          "Variant": "EqualUnique",
   433          "Keyspace": {
   434            "Name": "user",
   435            "Sharded": true
   436          },
   437          "FieldQuery": "select 1 from supplier5s where 1 != 1",
   438          "Query": "select /*vt+ ALLOW_HASH_JOIN */ 1 from supplier5s where supplier5s.id = :book6s_supplier5_id",
   439          "Table": "supplier5s",
   440          "Values": [
   441            ":book6s_supplier5_id"
   442          ],
   443          "Vindex": "binary_md5"
   444        }
   445      ]
   446    }
   447  }
   448  {
   449    "QueryType": "SELECT",
   450    "Original": "select /*vt+ ALLOW_HASH_JOIN */ author5s.* from author5s join book6s on book6s.author5_id = author5s.id join book6s_order2s on book6s_order2s.book6_id = book6s.id join order2s on order2s.id = book6s_order2s.order2_id join customer2s on customer2s.id = order2s.customer2_id join supplier5s on supplier5s.id = book6s.supplier5_id",
   451    "Instructions": {
   452      "OperatorType": "Join",
   453      "Variant": "HashJoin",
   454      "ComparisonType": "INT64",
   455      "JoinColumnIndexes": "2,3,4,5",
   456      "Predicate": "order2s.id = book6s_order2s.order2_id",
   457      "TableName": "customer2s, order2s_author5s, book6s_book6s_order2s_supplier5s",
   458      "Inputs": [
   459        {
   460          "OperatorType": "Route",
   461          "Variant": "Scatter",
   462          "Keyspace": {
   463            "Name": "user",
   464            "Sharded": true
   465          },
   466          "FieldQuery": "select order2s.id from order2s, customer2s where 1 != 1",
   467          "Query": "select /*vt+ ALLOW_HASH_JOIN */ order2s.id from order2s, customer2s where customer2s.id = order2s.customer2_id",
   468          "Table": "customer2s, order2s"
   469        },
   470        {
   471          "OperatorType": "Join",
   472          "Variant": "HashJoin",
   473          "ComparisonType": "INT64",
   474          "JoinColumnIndexes": "-1,-2,-3,-4,-5",
   475          "Predicate": "supplier5s.id = book6s.supplier5_id",
   476          "TableName": "author5s, book6s_book6s_order2s_supplier5s",
   477          "Inputs": [
   478            {
   479              "OperatorType": "Join",
   480              "Variant": "Join",
   481              "JoinColumnIndexes": "1,-3,-4,-5,-6",
   482              "JoinVars": {
   483                "book6s_id": 0
   484              },
   485              "Predicate": "book6s_order2s.book6_id = book6s.id",
   486              "TableName": "author5s, book6s_book6s_order2s",
   487              "Inputs": [
   488                {
   489                  "OperatorType": "Route",
   490                  "Variant": "Scatter",
   491                  "Keyspace": {
   492                    "Name": "user",
   493                    "Sharded": true
   494                  },
   495                  "FieldQuery": "select book6s.id, book6s.supplier5_id, author5s.id as id, author5s.`name` as `name`, author5s.created_at as created_at, author5s.updated_at as updated_at from author5s, book6s where 1 != 1",
   496                  "Query": "select /*vt+ ALLOW_HASH_JOIN */ book6s.id, book6s.supplier5_id, author5s.id as id, author5s.`name` as `name`, author5s.created_at as created_at, author5s.updated_at as updated_at from author5s, book6s where book6s.author5_id = author5s.id",
   497                  "Table": "author5s, book6s"
   498                },
   499                {
   500                  "OperatorType": "Route",
   501                  "Variant": "EqualUnique",
   502                  "Keyspace": {
   503                    "Name": "user",
   504                    "Sharded": true
   505                  },
   506                  "FieldQuery": "select book6s_order2s.order2_id from book6s_order2s where 1 != 1",
   507                  "Query": "select /*vt+ ALLOW_HASH_JOIN */ book6s_order2s.order2_id from book6s_order2s where book6s_order2s.book6_id = :book6s_id",
   508                  "Table": "book6s_order2s",
   509                  "Values": [
   510                    ":book6s_id"
   511                  ],
   512                  "Vindex": "binary_md5"
   513                }
   514              ]
   515            },
   516            {
   517              "OperatorType": "Route",
   518              "Variant": "Scatter",
   519              "Keyspace": {
   520                "Name": "user",
   521                "Sharded": true
   522              },
   523              "FieldQuery": "select supplier5s.id from supplier5s where 1 != 1",
   524              "Query": "select /*vt+ ALLOW_HASH_JOIN */ supplier5s.id from supplier5s",
   525              "Table": "supplier5s"
   526            }
   527          ]
   528        }
   529      ]
   530    }
   531  }