Materialize/test/sqllogictest/explain/optimized_plan_as_json.slt

# Copyright Materialize, Inc. and contributors. All rights reserved.
#
# Use of this software is governed by the Business Source License
# included in the LICENSE file at the root of this repository.
#
# As of the Change Date specified in that file, in accordance with
# the Business Source License, use of this software will be governed
# by the Apache License, Version 2.0.

# Test requires stable IDs
reset-server

statement ok
CREATE TABLE t (
  a int,
  b int
)

statement ok
CREATE TABLE u (
  c int,
  d int
)

statement ok
CREATE TABLE v (
  e int,
  f int
)

statement ok
CREATE INDEX t_a_idx ON T(a);

statement ok
CREATE VIEW ov AS SELECT * FROM t ORDER BY b asc, a desc LIMIT 5

statement ok
CREATE VIEW iv AS
SELECT * FROM t WHERE a IS NOT NULL

statement ok
CREATE DEFAULT INDEX ON iv

statement ok
CREATE MATERIALIZED VIEW mv AS
SELECT * FROM t WHERE a IS NOT NULL

mode cockroach

# Test constant error.
query T multiline
EXPLAIN OPTIMIZED PLAN WITH(raw) AS JSON FOR
SELECT 1 / 0
----
{
  "plans": [
    {
      "id": "Explained Query (fast path)",
      "plan": {
        "Constant": [
          {
            "Err": "DivisionByZero"
          },
          {
            "column_types": [
              {
                "scalar_type": "Int32",
                "nullable": false
              }
            ],
            "keys": []
          }
        ]
      }
    }
  ],
  "sources": []
}
EOF

# Test constant with two elements.
query T multiline
EXPLAIN OPTIMIZED PLAN WITH(raw) AS JSON FOR
(SELECT 1, 2) UNION ALL (SELECT 1, 2) UNION ALL (SELECT 3, 4)
----
{
  "plans": [
    {
      "id": "Explained Query (fast path)",
      "plan": {
        "Constant": [
          {
            "Ok": [
              [
                {
                  "data": [
                    45,
                    1,
                    45,
                    2
                  ]
                },
                2
              ],
              [
                {
                  "data": [
                    45,
                    3,
                    45,
                    4
                  ]
                },
                1
              ]
            ]
          },
          {
            "column_types": [
              {
                "scalar_type": "Int32",
                "nullable": false
              },
              {
                "scalar_type": "Int32",
                "nullable": false
              }
            ],
            "keys": []
          }
        ]
      }
    }
  ],
  "sources": []
}
EOF

# Test basic linear chains (fast path).
query T multiline
EXPLAIN OPTIMIZED PLAN WITH(raw) AS JSON FOR
SELECT 1, a + b as c FROM t WHERE a > 0 and b < 0 and a + b > 0
----
{
  "plans": [
    {
      "id": "Explained Query (fast path)",
      "plan": {
        "PeekExisting": [
          {
            "User": 1
          },
          {
            "User": 4
          },
          null,
          {
            "mfp": {
              "expressions": [
                {
                  "CallBinary": {
                    "func": "AddInt32",
                    "expr1": {
                      "Column": [
                        0,
                        "a"
                      ]
                    },
                    "expr2": {
                      "Column": [
                        1,
                        "b"
                      ]
                    }
                  }
                },
                {
                  "Literal": [
                    {
                      "Ok": {
                        "data": [
                          45,
                          1
                        ]
                      }
                    },
                    {
                      "scalar_type": "Int32",
                      "nullable": false
                    }
                  ]
                }
              ],
              "predicates": [
                [
                  1,
                  {
                    "CallBinary": {
                      "func": "Gt",
                      "expr1": {
                        "Column": [
                          0,
                          "a"
                        ]
                      },
                      "expr2": {
                        "Literal": [
                          {
                            "Ok": {
                              "data": [
                                44
                              ]
                            }
                          },
                          {
                            "scalar_type": "Int32",
                            "nullable": false
                          }
                        ]
                      }
                    }
                  }
                ],
                [
                  2,
                  {
                    "CallBinary": {
                      "func": "Lt",
                      "expr1": {
                        "Column": [
                          1,
                          "b"
                        ]
                      },
                      "expr2": {
                        "Literal": [
                          {
                            "Ok": {
                              "data": [
                                44
                              ]
                            }
                          },
                          {
                            "scalar_type": "Int32",
                            "nullable": false
                          }
                        ]
                      }
                    }
                  }
                ],
                [
                  3,
                  {
                    "CallBinary": {
                      "func": "Gt",
                      "expr1": {
                        "Column": [
                          2,
                          null
                        ]
                      },
                      "expr2": {
                        "Literal": [
                          {
                            "Ok": {
                              "data": [
                                44
                              ]
                            }
                          },
                          {
                            "scalar_type": "Int32",
                            "nullable": false
                          }
                        ]
                      }
                    }
                  }
                ]
              ],
              "projection": [
                3,
                2
              ],
              "input_arity": 2
            }
          }
        ]
      }
    }
  ],
  "sources": []
}
EOF

# Test basic linear chains (slow path).
query T multiline
EXPLAIN OPTIMIZED PLAN WITH(raw) AS JSON FOR
SELECT 1, a + b as c FROM mv WHERE a > 0 and b < 0 and a + b > 0
----
{
  "plans": [
    {
      "id": "Explained Query",
      "plan": {
        "Project": {
          "input": {
            "Filter": {
              "input": {
                "Map": {
                  "input": {
                    "Get": {
                      "id": {
                        "Global": {
                          "User": 8
                        }
                      },
                      "typ": {
                        "column_types": [
                          {
                            "scalar_type": "Int32",
                            "nullable": false
                          },
                          {
                            "scalar_type": "Int32",
                            "nullable": true
                          }
                        ],
                        "keys": []
                      },
                      "access_strategy": "Persist"
                    }
                  },
                  "scalars": [
                    {
                      "CallBinary": {
                        "func": "AddInt32",
                        "expr1": {
                          "Column": [
                            0,
                            "a"
                          ]
                        },
                        "expr2": {
                          "Column": [
                            1,
                            "b"
                          ]
                        }
                      }
                    },
                    {
                      "Literal": [
                        {
                          "Ok": {
                            "data": [
                              45,
                              1
                            ]
                          }
                        },
                        {
                          "scalar_type": "Int32",
                          "nullable": false
                        }
                      ]
                    }
                  ]
                }
              },
              "predicates": [
                {
                  "CallBinary": {
                    "func": "Lt",
                    "expr1": {
                      "Column": [
                        1,
                        "b"
                      ]
                    },
                    "expr2": {
                      "Literal": [
                        {
                          "Ok": {
                            "data": [
                              44
                            ]
                          }
                        },
                        {
                          "scalar_type": "Int32",
                          "nullable": false
                        }
                      ]
                    }
                  }
                },
                {
                  "CallBinary": {
                    "func": "Gt",
                    "expr1": {
                      "Column": [
                        0,
                        "a"
                      ]
                    },
                    "expr2": {
                      "Literal": [
                        {
                          "Ok": {
                            "data": [
                              44
                            ]
                          }
                        },
                        {
                          "scalar_type": "Int32",
                          "nullable": false
                        }
                      ]
                    }
                  }
                },
                {
                  "CallBinary": {
                    "func": "Gt",
                    "expr1": {
                      "Column": [
                        2,
                        null
                      ]
                    },
                    "expr2": {
                      "Literal": [
                        {
                          "Ok": {
                            "data": [
                              44
                            ]
                          }
                        },
                        {
                          "scalar_type": "Int32",
                          "nullable": false
                        }
                      ]
                    }
                  }
                }
              ]
            }
          },
          "outputs": [
            3,
            2
          ]
        }
      }
    }
  ],
  "sources": [
    {
      "id": {
        "User": 8
      },
      "op": {
        "expressions": [],
        "predicates": [
          [
            1,
            {
              "CallBinary": {
                "func": "Gt",
                "expr1": {
                  "Column": [
                    0,
                    "a"
                  ]
                },
                "expr2": {
                  "Literal": [
                    {
                      "Ok": {
                        "data": [
                          44
                        ]
                      }
                    },
                    {
                      "scalar_type": "Int32",
                      "nullable": false
                    }
                  ]
                }
              }
            }
          ],
          [
            2,
            {
              "CallBinary": {
                "func": "Lt",
                "expr1": {
                  "Column": [
                    1,
                    "b"
                  ]
                },
                "expr2": {
                  "Literal": [
                    {
                      "Ok": {
                        "data": [
                          44
                        ]
                      }
                    },
                    {
                      "scalar_type": "Int32",
                      "nullable": false
                    }
                  ]
                }
              }
            }
          ],
          [
            2,
            {
              "CallBinary": {
                "func": "Gt",
                "expr1": {
                  "CallBinary": {
                    "func": "AddInt32",
                    "expr1": {
                      "Column": [
                        0,
                        "a"
                      ]
                    },
                    "expr2": {
                      "Column": [
                        1,
                        "b"
                      ]
                    }
                  }
                },
                "expr2": {
                  "Literal": [
                    {
                      "Ok": {
                        "data": [
                          44
                        ]
                      }
                    },
                    {
                      "scalar_type": "Int32",
                      "nullable": false
                    }
                  ]
                }
              }
            }
          ]
        ],
        "projection": [
          0,
          1
        ],
        "input_arity": 2
      }
    }
  ]
}
EOF

# Test table functions in the select clause (FlatMap).
query T multiline
EXPLAIN OPTIMIZED PLAN WITH(raw) AS JSON FOR
SELECT generate_series(a, b) from t
----
{
  "plans": [
    {
      "id": "Explained Query",
      "plan": {
        "Project": {
          "input": {
            "FlatMap": {
              "input": {
                "Get": {
                  "id": {
                    "Global": {
                      "User": 1
                    }
                  },
                  "typ": {
                    "column_types": [
                      {
                        "scalar_type": "Int32",
                        "nullable": true
                      },
                      {
                        "scalar_type": "Int32",
                        "nullable": true
                      }
                    ],
                    "keys": []
                  },
                  "access_strategy": {
                    "Index": [
                      [
                        {
                          "User": 4
                        },
                        "FullScan"
                      ]
                    ]
                  }
                }
              },
              "func": "GenerateSeriesInt32",
              "exprs": [
                {
                  "Column": [
                    0,
                    "a"
                  ]
                },
                {
                  "Column": [
                    1,
                    "b"
                  ]
                },
                {
                  "Literal": [
                    {
                      "Ok": {
                        "data": [
                          45,
                          1
                        ]
                      }
                    },
                    {
                      "scalar_type": "Int32",
                      "nullable": false
                    }
                  ]
                }
              ]
            }
          },
          "outputs": [
            2
          ]
        }
      }
    }
  ],
  "sources": []
}
EOF

# Test Threshold, Union, Distinct, Negate.
query T multiline
EXPLAIN OPTIMIZED PLAN WITH(raw) AS JSON FOR
SELECT a FROM t EXCEPT SELECT b FROM mv
----
{
  "plans": [
    {
      "id": "Explained Query",
      "plan": {
        "Threshold": {
          "input": {
            "Union": {
              "base": {
                "Reduce": {
                  "input": {
                    "Project": {
                      "input": {
                        "Get": {
                          "id": {
                            "Global": {
                              "User": 1
                            }
                          },
                          "typ": {
                            "column_types": [
                              {
                                "scalar_type": "Int32",
                                "nullable": true
                              },
                              {
                                "scalar_type": "Int32",
                                "nullable": true
                              }
                            ],
                            "keys": []
                          },
                          "access_strategy": {
                            "Index": [
                              [
                                {
                                  "User": 4
                                },
                                "FullScan"
                              ]
                            ]
                          }
                        }
                      },
                      "outputs": [
                        0
                      ]
                    }
                  },
                  "group_key": [
                    {
                      "Column": [
                        0,
                        null
                      ]
                    }
                  ],
                  "aggregates": [],
                  "monotonic": false,
                  "expected_group_size": null
                }
              },
              "inputs": [
                {
                  "Negate": {
                    "input": {
                      "Reduce": {
                        "input": {
                          "Project": {
                            "input": {
                              "Get": {
                                "id": {
                                  "Global": {
                                    "User": 8
                                  }
                                },
                                "typ": {
                                  "column_types": [
                                    {
                                      "scalar_type": "Int32",
                                      "nullable": false
                                    },
                                    {
                                      "scalar_type": "Int32",
                                      "nullable": true
                                    }
                                  ],
                                  "keys": []
                                },
                                "access_strategy": "Persist"
                              }
                            },
                            "outputs": [
                              1
                            ]
                          }
                        },
                        "group_key": [
                          {
                            "Column": [
                              0,
                              null
                            ]
                          }
                        ],
                        "aggregates": [],
                        "monotonic": false,
                        "expected_group_size": null
                      }
                    }
                  }
                }
              ]
            }
          }
        }
      }
    }
  ],
  "sources": [
    {
      "id": {
        "User": 8
      },
      "op": null
    }
  ]
}
EOF

# Test Threshold, Union, Distinct, Negate.
query T multiline
EXPLAIN OPTIMIZED PLAN WITH(raw) AS JSON FOR
SELECT a FROM t EXCEPT ALL SELECT b FROM mv
----
{
  "plans": [
    {
      "id": "Explained Query",
      "plan": {
        "Threshold": {
          "input": {
            "Union": {
              "base": {
                "Project": {
                  "input": {
                    "Get": {
                      "id": {
                        "Global": {
                          "User": 1
                        }
                      },
                      "typ": {
                        "column_types": [
                          {
                            "scalar_type": "Int32",
                            "nullable": true
                          },
                          {
                            "scalar_type": "Int32",
                            "nullable": true
                          }
                        ],
                        "keys": []
                      },
                      "access_strategy": {
                        "Index": [
                          [
                            {
                              "User": 4
                            },
                            "FullScan"
                          ]
                        ]
                      }
                    }
                  },
                  "outputs": [
                    0
                  ]
                }
              },
              "inputs": [
                {
                  "Negate": {
                    "input": {
                      "Project": {
                        "input": {
                          "Get": {
                            "id": {
                              "Global": {
                                "User": 8
                              }
                            },
                            "typ": {
                              "column_types": [
                                {
                                  "scalar_type": "Int32",
                                  "nullable": false
                                },
                                {
                                  "scalar_type": "Int32",
                                  "nullable": true
                                }
                              ],
                              "keys": []
                            },
                            "access_strategy": "Persist"
                          }
                        },
                        "outputs": [
                          1
                        ]
                      }
                    }
                  }
                }
              ]
            }
          }
        }
      }
    }
  ],
  "sources": [
    {
      "id": {
        "User": 8
      },
      "op": null
    }
  ]
}
EOF

# Test TopK.
query T multiline
EXPLAIN OPTIMIZED PLAN WITH(raw) AS JSON FOR
SELECT * FROM ov
----
{
  "plans": [
    {
      "id": "Explained Query",
      "plan": {
        "TopK": {
          "input": {
            "Get": {
              "id": {
                "Global": {
                  "User": 1
                }
              },
              "typ": {
                "column_types": [
                  {
                    "scalar_type": "Int32",
                    "nullable": true
                  },
                  {
                    "scalar_type": "Int32",
                    "nullable": true
                  }
                ],
                "keys": []
              },
              "access_strategy": {
                "Index": [
                  [
                    {
                      "User": 4
                    },
                    "FullScan"
                  ]
                ]
              }
            }
          },
          "group_key": [],
          "order_key": [
            {
              "column": 1,
              "desc": false,
              "nulls_last": true
            },
            {
              "column": 0,
              "desc": true,
              "nulls_last": false
            }
          ],
          "limit": {
            "Literal": [
              {
                "Ok": {
                  "data": [
                    50,
                    5
                  ]
                }
              },
              {
                "scalar_type": "Int64",
                "nullable": false
              }
            ]
          },
          "offset": 0,
          "monotonic": false,
          "expected_group_size": null
        }
      }
    }
  ],
  "sources": []
}
EOF

# Test Finish.
query T multiline
EXPLAIN OPTIMIZED PLAN WITH(raw) AS JSON FOR
SELECT * FROM t ORDER BY b asc, a desc LIMIT 5
----
{
  "plans": [
    {
      "id": "Explained Query (fast path)",
      "plan": {
        "PeekExisting": [
          {
            "User": 1
          },
          {
            "User": 4
          },
          null,
          {
            "mfp": {
              "expressions": [],
              "predicates": [],
              "projection": [
                0,
                1
              ],
              "input_arity": 2
            }
          }
        ]
      }
    }
  ],
  "sources": []
}
EOF

# Test Reduce (global).
query T multiline
EXPLAIN OPTIMIZED PLAN WITH(raw) AS JSON FOR
SELECT abs(min(a) - max(a)) FROM t
----
{
  "plans": [
    {
      "id": "Explained Query",
      "plan": {
        "Let": {
          "id": 0,
          "value": {
            "Reduce": {
              "input": {
                "Project": {
                  "input": {
                    "Get": {
                      "id": {
                        "Global": {
                          "User": 1
                        }
                      },
                      "typ": {
                        "column_types": [
                          {
                            "scalar_type": "Int32",
                            "nullable": true
                          },
                          {
                            "scalar_type": "Int32",
                            "nullable": true
                          }
                        ],
                        "keys": []
                      },
                      "access_strategy": {
                        "Index": [
                          [
                            {
                              "User": 4
                            },
                            "FullScan"
                          ]
                        ]
                      }
                    }
                  },
                  "outputs": [
                    0
                  ]
                }
              },
              "group_key": [],
              "aggregates": [
                {
                  "func": "MinInt32",
                  "expr": {
                    "Column": [
                      0,
                      "a"
                    ]
                  },
                  "distinct": false
                },
                {
                  "func": "MaxInt32",
                  "expr": {
                    "Column": [
                      0,
                      "a"
                    ]
                  },
                  "distinct": false
                }
              ],
              "monotonic": false,
              "expected_group_size": null
            }
          },
          "body": {
            "Project": {
              "input": {
                "Map": {
                  "input": {
                    "Union": {
                      "base": {
                        "Get": {
                          "id": {
                            "Local": 0
                          },
                          "typ": {
                            "column_types": [
                              {
                                "scalar_type": "Int32",
                                "nullable": true
                              },
                              {
                                "scalar_type": "Int32",
                                "nullable": true
                              }
                            ],
                            "keys": [
                              []
                            ]
                          },
                          "access_strategy": "UnknownOrLocal"
                        }
                      },
                      "inputs": [
                        {
                          "Map": {
                            "input": {
                              "Union": {
                                "base": {
                                  "Negate": {
                                    "input": {
                                      "Project": {
                                        "input": {
                                          "Get": {
                                            "id": {
                                              "Local": 0
                                            },
                                            "typ": {
                                              "column_types": [
                                                {
                                                  "scalar_type": "Int32",
                                                  "nullable": true
                                                },
                                                {
                                                  "scalar_type": "Int32",
                                                  "nullable": true
                                                }
                                              ],
                                              "keys": [
                                                []
                                              ]
                                            },
                                            "access_strategy": "UnknownOrLocal"
                                          }
                                        },
                                        "outputs": []
                                      }
                                    }
                                  }
                                },
                                "inputs": [
                                  {
                                    "Constant": {
                                      "rows": {
                                        "Ok": [
                                          [
                                            {
                                              "data": []
                                            },
                                            1
                                          ]
                                        ]
                                      },
                                      "typ": {
                                        "column_types": [],
                                        "keys": []
                                      }
                                    }
                                  }
                                ]
                              }
                            },
                            "scalars": [
                              {
                                "Literal": [
                                  {
                                    "Ok": {
                                      "data": [
                                        0
                                      ]
                                    }
                                  },
                                  {
                                    "scalar_type": "Int32",
                                    "nullable": true
                                  }
                                ]
                              },
                              {
                                "Literal": [
                                  {
                                    "Ok": {
                                      "data": [
                                        0
                                      ]
                                    }
                                  },
                                  {
                                    "scalar_type": "Int32",
                                    "nullable": true
                                  }
                                ]
                              }
                            ]
                          }
                        }
                      ]
                    }
                  },
                  "scalars": [
                    {
                      "CallUnary": {
                        "func": {
                          "AbsInt32": null
                        },
                        "expr": {
                          "CallBinary": {
                            "func": "SubInt32",
                            "expr1": {
                              "Column": [
                                0,
                                "?column?"
                              ]
                            },
                            "expr2": {
                              "Column": [
                                1,
                                "?column?"
                              ]
                            }
                          }
                        }
                      }
                    }
                  ]
                }
              },
              "outputs": [
                2
              ]
            }
          }
        }
      }
    }
  ],
  "sources": []
}
EOF

# Test Reduce (local).
query T multiline
EXPLAIN OPTIMIZED PLAN WITH(raw) AS JSON FOR
SELECT abs(min(a) - max(a)) FROM t GROUP BY b
----
{
  "plans": [
    {
      "id": "Explained Query",
      "plan": {
        "Project": {
          "input": {
            "Map": {
              "input": {
                "Reduce": {
                  "input": {
                    "Get": {
                      "id": {
                        "Global": {
                          "User": 1
                        }
                      },
                      "typ": {
                        "column_types": [
                          {
                            "scalar_type": "Int32",
                            "nullable": true
                          },
                          {
                            "scalar_type": "Int32",
                            "nullable": true
                          }
                        ],
                        "keys": []
                      },
                      "access_strategy": {
                        "Index": [
                          [
                            {
                              "User": 4
                            },
                            "FullScan"
                          ]
                        ]
                      }
                    }
                  },
                  "group_key": [
                    {
                      "Column": [
                        1,
                        null
                      ]
                    }
                  ],
                  "aggregates": [
                    {
                      "func": "MinInt32",
                      "expr": {
                        "Column": [
                          0,
                          "a"
                        ]
                      },
                      "distinct": false
                    },
                    {
                      "func": "MaxInt32",
                      "expr": {
                        "Column": [
                          0,
                          "a"
                        ]
                      },
                      "distinct": false
                    }
                  ],
                  "monotonic": false,
                  "expected_group_size": null
                }
              },
              "scalars": [
                {
                  "CallUnary": {
                    "func": {
                      "AbsInt32": null
                    },
                    "expr": {
                      "CallBinary": {
                        "func": "SubInt32",
                        "expr1": {
                          "Column": [
                            1,
                            "?column?"
                          ]
                        },
                        "expr2": {
                          "Column": [
                            2,
                            "?column?"
                          ]
                        }
                      }
                    }
                  }
                }
              ]
            }
          },
          "outputs": [
            3
          ]
        }
      }
    }
  ],
  "sources": []
}
EOF

# Test EXISTS subqueries.
query T multiline
EXPLAIN OPTIMIZED PLAN WITH(raw) AS JSON FOR
SELECT * FROM t WHERE EXISTS(SELECT * FROM mv WHERE t.a < mv.a) AND EXISTS(SELECT * FROM mv WHERE t.b > mv.b)
----
{
  "plans": [
    {
      "id": "Explained Query",
      "plan": {
        "Let": {
          "id": 0,
          "value": {
            "Project": {
              "input": {
                "Join": {
                  "inputs": [
                    {
                      "ArrangeBy": {
                        "input": {
                          "Get": {
                            "id": {
                              "Global": {
                                "User": 1
                              }
                            },
                            "typ": {
                              "column_types": [
                                {
                                  "scalar_type": "Int32",
                                  "nullable": true
                                },
                                {
                                  "scalar_type": "Int32",
                                  "nullable": true
                                }
                              ],
                              "keys": []
                            },
                            "access_strategy": {
                              "Index": [
                                [
                                  {
                                    "User": 4
                                  },
                                  "DifferentialJoin"
                                ]
                              ]
                            }
                          }
                        },
                        "keys": [
                          [
                            {
                              "Column": [
                                0,
                                "a"
                              ]
                            }
                          ]
                        ]
                      }
                    },
                    {
                      "ArrangeBy": {
                        "input": {
                          "Reduce": {
                            "input": {
                              "Project": {
                                "input": {
                                  "Filter": {
                                    "input": {
                                      "Join": {
                                        "inputs": [
                                          {
                                            "ArrangeBy": {
                                              "input": {
                                                "Reduce": {
                                                  "input": {
                                                    "Project": {
                                                      "input": {
                                                        "Get": {
                                                          "id": {
                                                            "Global": {
                                                              "User": 1
                                                            }
                                                          },
                                                          "typ": {
                                                            "column_types": [
                                                              {
                                                                "scalar_type": "Int32",
                                                                "nullable": true
                                                              },
                                                              {
                                                                "scalar_type": "Int32",
                                                                "nullable": true
                                                              }
                                                            ],
                                                            "keys": []
                                                          },
                                                          "access_strategy": {
                                                            "Index": [
                                                              [
                                                                {
                                                                  "User": 4
                                                                },
                                                                "FullScan"
                                                              ]
                                                            ]
                                                          }
                                                        }
                                                      },
                                                      "outputs": [
                                                        0
                                                      ]
                                                    }
                                                  },
                                                  "group_key": [
                                                    {
                                                      "Column": [
                                                        0,
                                                        null
                                                      ]
                                                    }
                                                  ],
                                                  "aggregates": [],
                                                  "monotonic": false,
                                                  "expected_group_size": null
                                                }
                                              },
                                              "keys": [
                                                []
                                              ]
                                            }
                                          },
                                          {
                                            "ArrangeBy": {
                                              "input": {
                                                "Project": {
                                                  "input": {
                                                    "Get": {
                                                      "id": {
                                                        "Global": {
                                                          "User": 8
                                                        }
                                                      },
                                                      "typ": {
                                                        "column_types": [
                                                          {
                                                            "scalar_type": "Int32",
                                                            "nullable": false
                                                          },
                                                          {
                                                            "scalar_type": "Int32",
                                                            "nullable": true
                                                          }
                                                        ],
                                                        "keys": []
                                                      },
                                                      "access_strategy": "Persist"
                                                    }
                                                  },
                                                  "outputs": [
                                                    0
                                                  ]
                                                }
                                              },
                                              "keys": [
                                                []
                                              ]
                                            }
                                          }
                                        ],
                                        "equivalences": [],
                                        "implementation": {
                                          "Differential": [
                                            [
                                              0,
                                              [],
                                              {
                                                "V1": {
                                                  "unique_key": false,
                                                  "key_length": 0,
                                                  "arranged": false,
                                                  "cardinality": null,
                                                  "filters": {
                                                    "literal_equality": false,
                                                    "like": false,
                                                    "is_null": false,
                                                    "literal_inequality": 0,
                                                    "any_filter": false
                                                  },
                                                  "input": 0
                                                }
                                              }
                                            ],
                                            [
                                              [
                                                1,
                                                [],
                                                {
                                                  "V1": {
                                                    "unique_key": false,
                                                    "key_length": 0,
                                                    "arranged": false,
                                                    "cardinality": null,
                                                    "filters": {
                                                      "literal_equality": false,
                                                      "like": false,
                                                      "is_null": false,
                                                      "literal_inequality": 0,
                                                      "any_filter": false
                                                    },
                                                    "input": 1
                                                  }
                                                }
                                              ]
                                            ]
                                          ]
                                        }
                                      }
                                    },
                                    "predicates": [
                                      {
                                        "CallBinary": {
                                          "func": "Lt",
                                          "expr1": {
                                            "Column": [
                                              0,
                                              "a"
                                            ]
                                          },
                                          "expr2": {
                                            "Column": [
                                              1,
                                              "a"
                                            ]
                                          }
                                        }
                                      }
                                    ]
                                  }
                                },
                                "outputs": [
                                  0
                                ]
                              }
                            },
                            "group_key": [
                              {
                                "Column": [
                                  0,
                                  null
                                ]
                              }
                            ],
                            "aggregates": [],
                            "monotonic": false,
                            "expected_group_size": null
                          }
                        },
                        "keys": [
                          [
                            {
                              "Column": [
                                0,
                                null
                              ]
                            }
                          ]
                        ]
                      }
                    }
                  ],
                  "equivalences": [
                    [
                      {
                        "Column": [
                          0,
                          null
                        ]
                      },
                      {
                        "Column": [
                          2,
                          null
                        ]
                      }
                    ]
                  ],
                  "implementation": {
                    "Differential": [
                      [
                        1,
                        [
                          {
                            "Column": [
                              0,
                              null
                            ]
                          }
                        ],
                        {
                          "V1": {
                            "unique_key": true,
                            "key_length": 1,
                            "arranged": true,
                            "cardinality": null,
                            "filters": {
                              "literal_equality": false,
                              "like": false,
                              "is_null": false,
                              "literal_inequality": 0,
                              "any_filter": false
                            },
                            "input": 1
                          }
                        }
                      ],
                      [
                        [
                          0,
                          [
                            {
                              "Column": [
                                0,
                                "a"
                              ]
                            }
                          ],
                          {
                            "V1": {
                              "unique_key": false,
                              "key_length": 1,
                              "arranged": true,
                              "cardinality": null,
                              "filters": {
                                "literal_equality": false,
                                "like": false,
                                "is_null": false,
                                "literal_inequality": 0,
                                "any_filter": false
                              },
                              "input": 0
                            }
                          }
                        ]
                      ]
                    ]
                  }
                }
              },
              "outputs": [
                0,
                1
              ]
            }
          },
          "body": {
            "Project": {
              "input": {
                "Join": {
                  "inputs": [
                    {
                      "ArrangeBy": {
                        "input": {
                          "Get": {
                            "id": {
                              "Local": 0
                            },
                            "typ": {
                              "column_types": [
                                {
                                  "scalar_type": "Int32",
                                  "nullable": false
                                },
                                {
                                  "scalar_type": "Int32",
                                  "nullable": true
                                }
                              ],
                              "keys": []
                            },
                            "access_strategy": "UnknownOrLocal"
                          }
                        },
                        "keys": [
                          [
                            {
                              "Column": [
                                1,
                                null
                              ]
                            }
                          ]
                        ]
                      }
                    },
                    {
                      "ArrangeBy": {
                        "input": {
                          "Reduce": {
                            "input": {
                              "Project": {
                                "input": {
                                  "Filter": {
                                    "input": {
                                      "Join": {
                                        "inputs": [
                                          {
                                            "ArrangeBy": {
                                              "input": {
                                                "Reduce": {
                                                  "input": {
                                                    "Project": {
                                                      "input": {
                                                        "Get": {
                                                          "id": {
                                                            "Local": 0
                                                          },
                                                          "typ": {
                                                            "column_types": [
                                                              {
                                                                "scalar_type": "Int32",
                                                                "nullable": false
                                                              },
                                                              {
                                                                "scalar_type": "Int32",
                                                                "nullable": true
                                                              }
                                                            ],
                                                            "keys": []
                                                          },
                                                          "access_strategy": "UnknownOrLocal"
                                                        }
                                                      },
                                                      "outputs": [
                                                        1
                                                      ]
                                                    }
                                                  },
                                                  "group_key": [
                                                    {
                                                      "Column": [
                                                        0,
                                                        null
                                                      ]
                                                    }
                                                  ],
                                                  "aggregates": [],
                                                  "monotonic": false,
                                                  "expected_group_size": null
                                                }
                                              },
                                              "keys": [
                                                []
                                              ]
                                            }
                                          },
                                          {
                                            "ArrangeBy": {
                                              "input": {
                                                "Project": {
                                                  "input": {
                                                    "Get": {
                                                      "id": {
                                                        "Global": {
                                                          "User": 8
                                                        }
                                                      },
                                                      "typ": {
                                                        "column_types": [
                                                          {
                                                            "scalar_type": "Int32",
                                                            "nullable": false
                                                          },
                                                          {
                                                            "scalar_type": "Int32",
                                                            "nullable": true
                                                          }
                                                        ],
                                                        "keys": []
                                                      },
                                                      "access_strategy": "Persist"
                                                    }
                                                  },
                                                  "outputs": [
                                                    1
                                                  ]
                                                }
                                              },
                                              "keys": [
                                                []
                                              ]
                                            }
                                          }
                                        ],
                                        "equivalences": [],
                                        "implementation": {
                                          "Differential": [
                                            [
                                              0,
                                              [],
                                              {
                                                "V1": {
                                                  "unique_key": false,
                                                  "key_length": 0,
                                                  "arranged": false,
                                                  "cardinality": null,
                                                  "filters": {
                                                    "literal_equality": false,
                                                    "like": false,
                                                    "is_null": false,
                                                    "literal_inequality": 0,
                                                    "any_filter": false
                                                  },
                                                  "input": 0
                                                }
                                              }
                                            ],
                                            [
                                              [
                                                1,
                                                [],
                                                {
                                                  "V1": {
                                                    "unique_key": false,
                                                    "key_length": 0,
                                                    "arranged": false,
                                                    "cardinality": null,
                                                    "filters": {
                                                      "literal_equality": false,
                                                      "like": false,
                                                      "is_null": false,
                                                      "literal_inequality": 0,
                                                      "any_filter": false
                                                    },
                                                    "input": 1
                                                  }
                                                }
                                              ]
                                            ]
                                          ]
                                        }
                                      }
                                    },
                                    "predicates": [
                                      {
                                        "CallBinary": {
                                          "func": "Gt",
                                          "expr1": {
                                            "Column": [
                                              0,
                                              "b"
                                            ]
                                          },
                                          "expr2": {
                                            "Column": [
                                              1,
                                              "b"
                                            ]
                                          }
                                        }
                                      }
                                    ]
                                  }
                                },
                                "outputs": [
                                  0
                                ]
                              }
                            },
                            "group_key": [
                              {
                                "Column": [
                                  0,
                                  null
                                ]
                              }
                            ],
                            "aggregates": [],
                            "monotonic": false,
                            "expected_group_size": null
                          }
                        },
                        "keys": [
                          [
                            {
                              "Column": [
                                0,
                                null
                              ]
                            }
                          ]
                        ]
                      }
                    }
                  ],
                  "equivalences": [
                    [
                      {
                        "Column": [
                          1,
                          null
                        ]
                      },
                      {
                        "Column": [
                          2,
                          null
                        ]
                      }
                    ]
                  ],
                  "implementation": {
                    "Differential": [
                      [
                        1,
                        [
                          {
                            "Column": [
                              0,
                              null
                            ]
                          }
                        ],
                        {
                          "V1": {
                            "unique_key": true,
                            "key_length": 1,
                            "arranged": true,
                            "cardinality": null,
                            "filters": {
                              "literal_equality": false,
                              "like": false,
                              "is_null": false,
                              "literal_inequality": 0,
                              "any_filter": false
                            },
                            "input": 1
                          }
                        }
                      ],
                      [
                        [
                          0,
                          [
                            {
                              "Column": [
                                1,
                                null
                              ]
                            }
                          ],
                          {
                            "V1": {
                              "unique_key": false,
                              "key_length": 1,
                              "arranged": false,
                              "cardinality": null,
                              "filters": {
                                "literal_equality": false,
                                "like": false,
                                "is_null": false,
                                "literal_inequality": 0,
                                "any_filter": false
                              },
                              "input": 0
                            }
                          }
                        ]
                      ]
                    ]
                  }
                }
              },
              "outputs": [
                0,
                1
              ]
            }
          }
        }
      }
    }
  ],
  "sources": [
    {
      "id": {
        "User": 8
      },
      "op": null
    }
  ]
}
EOF

# Test SELECT subqueries.
query T multiline
EXPLAIN OPTIMIZED PLAN WITH(raw) AS JSON FOR
SELECT (SELECT iv.a FROM iv WHERE iv.b = t.b LIMIT 1), (SELECT mv.a FROM mv WHERE mv.b = t.b LIMIT 1) FROM t
----
{
  "plans": [
    {
      "id": "Explained Query",
      "plan": {
        "Let": {
          "id": 0,
          "value": {
            "Project": {
              "input": {
                "Get": {
                  "id": {
                    "Global": {
                      "User": 1
                    }
                  },
                  "typ": {
                    "column_types": [
                      {
                        "scalar_type": "Int32",
                        "nullable": true
                      },
                      {
                        "scalar_type": "Int32",
                        "nullable": true
                      }
                    ],
                    "keys": []
                  },
                  "access_strategy": {
                    "Index": [
                      [
                        {
                          "User": 4
                        },
                        "FullScan"
                      ]
                    ]
                  }
                }
              },
              "outputs": [
                1
              ]
            }
          },
          "body": {
            "Let": {
              "id": 1,
              "value": {
                "Reduce": {
                  "input": {
                    "Get": {
                      "id": {
                        "Local": 0
                      },
                      "typ": {
                        "column_types": [
                          {
                            "scalar_type": "Int32",
                            "nullable": true
                          }
                        ],
                        "keys": []
                      },
                      "access_strategy": "UnknownOrLocal"
                    }
                  },
                  "group_key": [
                    {
                      "Column": [
                        0,
                        null
                      ]
                    }
                  ],
                  "aggregates": [],
                  "monotonic": false,
                  "expected_group_size": null
                }
              },
              "body": {
                "Let": {
                  "id": 2,
                  "value": {
                    "ArrangeBy": {
                      "input": {
                        "Get": {
                          "id": {
                            "Local": 1
                          },
                          "typ": {
                            "column_types": [
                              {
                                "scalar_type": "Int32",
                                "nullable": true
                              }
                            ],
                            "keys": [
                              [
                                0
                              ]
                            ]
                          },
                          "access_strategy": "UnknownOrLocal"
                        }
                      },
                      "keys": [
                        [
                          {
                            "Column": [
                              0,
                              "b"
                            ]
                          }
                        ]
                      ]
                    }
                  },
                  "body": {
                    "Let": {
                      "id": 3,
                      "value": {
                        "TopK": {
                          "input": {
                            "Project": {
                              "input": {
                                "Join": {
                                  "inputs": [
                                    {
                                      "Get": {
                                        "id": {
                                          "Local": 2
                                        },
                                        "typ": {
                                          "column_types": [
                                            {
                                              "scalar_type": "Int32",
                                              "nullable": true
                                            }
                                          ],
                                          "keys": [
                                            [
                                              0
                                            ]
                                          ]
                                        },
                                        "access_strategy": "UnknownOrLocal"
                                      }
                                    },
                                    {
                                      "ArrangeBy": {
                                        "input": {
                                          "Filter": {
                                            "input": {
                                              "Get": {
                                                "id": {
                                                  "Global": {
                                                    "User": 6
                                                  }
                                                },
                                                "typ": {
                                                  "column_types": [
                                                    {
                                                      "scalar_type": "Int32",
                                                      "nullable": false
                                                    },
                                                    {
                                                      "scalar_type": "Int32",
                                                      "nullable": true
                                                    }
                                                  ],
                                                  "keys": []
                                                },
                                                "access_strategy": {
                                                  "Index": [
                                                    [
                                                      {
                                                        "User": 7
                                                      },
                                                      "FullScan"
                                                    ]
                                                  ]
                                                }
                                              }
                                            },
                                            "predicates": [
                                              {
                                                "CallUnary": {
                                                  "func": {
                                                    "Not": null
                                                  },
                                                  "expr": {
                                                    "CallUnary": {
                                                      "func": {
                                                        "IsNull": null
                                                      },
                                                      "expr": {
                                                        "Column": [
                                                          1,
                                                          "b"
                                                        ]
                                                      }
                                                    }
                                                  }
                                                }
                                              }
                                            ]
                                          }
                                        },
                                        "keys": [
                                          [
                                            {
                                              "Column": [
                                                1,
                                                "b"
                                              ]
                                            }
                                          ]
                                        ]
                                      }
                                    }
                                  ],
                                  "equivalences": [
                                    [
                                      {
                                        "Column": [
                                          0,
                                          "b"
                                        ]
                                      },
                                      {
                                        "Column": [
                                          2,
                                          "b"
                                        ]
                                      }
                                    ]
                                  ],
                                  "implementation": {
                                    "Differential": [
                                      [
                                        0,
                                        [
                                          {
                                            "Column": [
                                              0,
                                              "b"
                                            ]
                                          }
                                        ],
                                        {
                                          "V1": {
                                            "unique_key": true,
                                            "key_length": 1,
                                            "arranged": true,
                                            "cardinality": null,
                                            "filters": {
                                              "literal_equality": false,
                                              "like": false,
                                              "is_null": false,
                                              "literal_inequality": 0,
                                              "any_filter": false
                                            },
                                            "input": 0
                                          }
                                        }
                                      ],
                                      [
                                        [
                                          1,
                                          [
                                            {
                                              "Column": [
                                                1,
                                                "b"
                                              ]
                                            }
                                          ],
                                          {
                                            "V1": {
                                              "unique_key": false,
                                              "key_length": 1,
                                              "arranged": false,
                                              "cardinality": null,
                                              "filters": {
                                                "literal_equality": false,
                                                "like": false,
                                                "is_null": false,
                                                "literal_inequality": 0,
                                                "any_filter": false
                                              },
                                              "input": 1
                                            }
                                          }
                                        ]
                                      ]
                                    ]
                                  }
                                }
                              },
                              "outputs": [
                                0,
                                1
                              ]
                            }
                          },
                          "group_key": [
                            0
                          ],
                          "order_key": [],
                          "limit": {
                            "Literal": [
                              {
                                "Ok": {
                                  "data": [
                                    50,
                                    1
                                  ]
                                }
                              },
                              {
                                "scalar_type": "Int64",
                                "nullable": false
                              }
                            ]
                          },
                          "offset": 0,
                          "monotonic": false,
                          "expected_group_size": null
                        }
                      },
                      "body": {
                        "Let": {
                          "id": 4,
                          "value": {
                            "TopK": {
                              "input": {
                                "Project": {
                                  "input": {
                                    "Join": {
                                      "inputs": [
                                        {
                                          "Get": {
                                            "id": {
                                              "Local": 2
                                            },
                                            "typ": {
                                              "column_types": [
                                                {
                                                  "scalar_type": "Int32",
                                                  "nullable": true
                                                }
                                              ],
                                              "keys": [
                                                [
                                                  0
                                                ]
                                              ]
                                            },
                                            "access_strategy": "UnknownOrLocal"
                                          }
                                        },
                                        {
                                          "ArrangeBy": {
                                            "input": {
                                              "Filter": {
                                                "input": {
                                                  "Get": {
                                                    "id": {
                                                      "Global": {
                                                        "User": 8
                                                      }
                                                    },
                                                    "typ": {
                                                      "column_types": [
                                                        {
                                                          "scalar_type": "Int32",
                                                          "nullable": false
                                                        },
                                                        {
                                                          "scalar_type": "Int32",
                                                          "nullable": true
                                                        }
                                                      ],
                                                      "keys": []
                                                    },
                                                    "access_strategy": "Persist"
                                                  }
                                                },
                                                "predicates": [
                                                  {
                                                    "CallUnary": {
                                                      "func": {
                                                        "Not": null
                                                      },
                                                      "expr": {
                                                        "CallUnary": {
                                                          "func": {
                                                            "IsNull": null
                                                          },
                                                          "expr": {
                                                            "Column": [
                                                              1,
                                                              "b"
                                                            ]
                                                          }
                                                        }
                                                      }
                                                    }
                                                  }
                                                ]
                                              }
                                            },
                                            "keys": [
                                              [
                                                {
                                                  "Column": [
                                                    1,
                                                    "b"
                                                  ]
                                                }
                                              ]
                                            ]
                                          }
                                        }
                                      ],
                                      "equivalences": [
                                        [
                                          {
                                            "Column": [
                                              0,
                                              "b"
                                            ]
                                          },
                                          {
                                            "Column": [
                                              2,
                                              "b"
                                            ]
                                          }
                                        ]
                                      ],
                                      "implementation": {
                                        "Differential": [
                                          [
                                            0,
                                            [
                                              {
                                                "Column": [
                                                  0,
                                                  "b"
                                                ]
                                              }
                                            ],
                                            {
                                              "V1": {
                                                "unique_key": true,
                                                "key_length": 1,
                                                "arranged": true,
                                                "cardinality": null,
                                                "filters": {
                                                  "literal_equality": false,
                                                  "like": false,
                                                  "is_null": false,
                                                  "literal_inequality": 0,
                                                  "any_filter": false
                                                },
                                                "input": 0
                                              }
                                            }
                                          ],
                                          [
                                            [
                                              1,
                                              [
                                                {
                                                  "Column": [
                                                    1,
                                                    "b"
                                                  ]
                                                }
                                              ],
                                              {
                                                "V1": {
                                                  "unique_key": false,
                                                  "key_length": 1,
                                                  "arranged": false,
                                                  "cardinality": null,
                                                  "filters": {
                                                    "literal_equality": false,
                                                    "like": false,
                                                    "is_null": false,
                                                    "literal_inequality": 0,
                                                    "any_filter": false
                                                  },
                                                  "input": 1
                                                }
                                              }
                                            ]
                                          ]
                                        ]
                                      }
                                    }
                                  },
                                  "outputs": [
                                    0,
                                    1
                                  ]
                                }
                              },
                              "group_key": [
                                0
                              ],
                              "order_key": [],
                              "limit": {
                                "Literal": [
                                  {
                                    "Ok": {
                                      "data": [
                                        50,
                                        1
                                      ]
                                    }
                                  },
                                  {
                                    "scalar_type": "Int64",
                                    "nullable": false
                                  }
                                ]
                              },
                              "offset": 0,
                              "monotonic": false,
                              "expected_group_size": null
                            }
                          },
                          "body": {
                            "Project": {
                              "input": {
                                "Join": {
                                  "inputs": [
                                    {
                                      "ArrangeBy": {
                                        "input": {
                                          "Get": {
                                            "id": {
                                              "Local": 0
                                            },
                                            "typ": {
                                              "column_types": [
                                                {
                                                  "scalar_type": "Int32",
                                                  "nullable": true
                                                }
                                              ],
                                              "keys": []
                                            },
                                            "access_strategy": "UnknownOrLocal"
                                          }
                                        },
                                        "keys": [
                                          [
                                            {
                                              "Column": [
                                                0,
                                                null
                                              ]
                                            }
                                          ]
                                        ]
                                      }
                                    },
                                    {
                                      "ArrangeBy": {
                                        "input": {
                                          "Union": {
                                            "base": {
                                              "Get": {
                                                "id": {
                                                  "Local": 3
                                                },
                                                "typ": {
                                                  "column_types": [
                                                    {
                                                      "scalar_type": "Int32",
                                                      "nullable": false
                                                    },
                                                    {
                                                      "scalar_type": "Int32",
                                                      "nullable": false
                                                    }
                                                  ],
                                                  "keys": [
                                                    [
                                                      0
                                                    ]
                                                  ]
                                                },
                                                "access_strategy": "UnknownOrLocal"
                                              }
                                            },
                                            "inputs": [
                                              {
                                                "Map": {
                                                  "input": {
                                                    "Union": {
                                                      "base": {
                                                        "Negate": {
                                                          "input": {
                                                            "Project": {
                                                              "input": {
                                                                "Get": {
                                                                  "id": {
                                                                    "Local": 3
                                                                  },
                                                                  "typ": {
                                                                    "column_types": [
                                                                      {
                                                                        "scalar_type": "Int32",
                                                                        "nullable": false
                                                                      },
                                                                      {
                                                                        "scalar_type": "Int32",
                                                                        "nullable": false
                                                                      }
                                                                    ],
                                                                    "keys": [
                                                                      [
                                                                        0
                                                                      ]
                                                                    ]
                                                                  },
                                                                  "access_strategy": "UnknownOrLocal"
                                                                }
                                                              },
                                                              "outputs": [
                                                                0
                                                              ]
                                                            }
                                                          }
                                                        }
                                                      },
                                                      "inputs": [
                                                        {
                                                          "Get": {
                                                            "id": {
                                                              "Local": 1
                                                            },
                                                            "typ": {
                                                              "column_types": [
                                                                {
                                                                  "scalar_type": "Int32",
                                                                  "nullable": true
                                                                }
                                                              ],
                                                              "keys": [
                                                                [
                                                                  0
                                                                ]
                                                              ]
                                                            },
                                                            "access_strategy": "UnknownOrLocal"
                                                          }
                                                        }
                                                      ]
                                                    }
                                                  },
                                                  "scalars": [
                                                    {
                                                      "Literal": [
                                                        {
                                                          "Ok": {
                                                            "data": [
                                                              0
                                                            ]
                                                          }
                                                        },
                                                        {
                                                          "scalar_type": "Int32",
                                                          "nullable": true
                                                        }
                                                      ]
                                                    }
                                                  ]
                                                }
                                              }
                                            ]
                                          }
                                        },
                                        "keys": [
                                          [
                                            {
                                              "Column": [
                                                0,
                                                null
                                              ]
                                            }
                                          ]
                                        ]
                                      }
                                    },
                                    {
                                      "ArrangeBy": {
                                        "input": {
                                          "Union": {
                                            "base": {
                                              "Get": {
                                                "id": {
                                                  "Local": 4
                                                },
                                                "typ": {
                                                  "column_types": [
                                                    {
                                                      "scalar_type": "Int32",
                                                      "nullable": false
                                                    },
                                                    {
                                                      "scalar_type": "Int32",
                                                      "nullable": false
                                                    }
                                                  ],
                                                  "keys": [
                                                    [
                                                      0
                                                    ]
                                                  ]
                                                },
                                                "access_strategy": "UnknownOrLocal"
                                              }
                                            },
                                            "inputs": [
                                              {
                                                "Map": {
                                                  "input": {
                                                    "Union": {
                                                      "base": {
                                                        "Negate": {
                                                          "input": {
                                                            "Project": {
                                                              "input": {
                                                                "Get": {
                                                                  "id": {
                                                                    "Local": 4
                                                                  },
                                                                  "typ": {
                                                                    "column_types": [
                                                                      {
                                                                        "scalar_type": "Int32",
                                                                        "nullable": false
                                                                      },
                                                                      {
                                                                        "scalar_type": "Int32",
                                                                        "nullable": false
                                                                      }
                                                                    ],
                                                                    "keys": [
                                                                      [
                                                                        0
                                                                      ]
                                                                    ]
                                                                  },
                                                                  "access_strategy": "UnknownOrLocal"
                                                                }
                                                              },
                                                              "outputs": [
                                                                0
                                                              ]
                                                            }
                                                          }
                                                        }
                                                      },
                                                      "inputs": [
                                                        {
                                                          "Get": {
                                                            "id": {
                                                              "Local": 1
                                                            },
                                                            "typ": {
                                                              "column_types": [
                                                                {
                                                                  "scalar_type": "Int32",
                                                                  "nullable": true
                                                                }
                                                              ],
                                                              "keys": [
                                                                [
                                                                  0
                                                                ]
                                                              ]
                                                            },
                                                            "access_strategy": "UnknownOrLocal"
                                                          }
                                                        }
                                                      ]
                                                    }
                                                  },
                                                  "scalars": [
                                                    {
                                                      "Literal": [
                                                        {
                                                          "Ok": {
                                                            "data": [
                                                              0
                                                            ]
                                                          }
                                                        },
                                                        {
                                                          "scalar_type": "Int32",
                                                          "nullable": true
                                                        }
                                                      ]
                                                    }
                                                  ]
                                                }
                                              }
                                            ]
                                          }
                                        },
                                        "keys": [
                                          [
                                            {
                                              "Column": [
                                                0,
                                                null
                                              ]
                                            }
                                          ]
                                        ]
                                      }
                                    }
                                  ],
                                  "equivalences": [
                                    [
                                      {
                                        "Column": [
                                          0,
                                          null
                                        ]
                                      },
                                      {
                                        "Column": [
                                          1,
                                          null
                                        ]
                                      },
                                      {
                                        "Column": [
                                          3,
                                          null
                                        ]
                                      }
                                    ]
                                  ],
                                  "implementation": {
                                    "DeltaQuery": [
                                      [
                                        [
                                          1,
                                          [
                                            {
                                              "Column": [
                                                0,
                                                null
                                              ]
                                            }
                                          ],
                                          {
                                            "V1": {
                                              "unique_key": true,
                                              "key_length": 1,
                                              "arranged": false,
                                              "cardinality": null,
                                              "filters": {
                                                "literal_equality": false,
                                                "like": false,
                                                "is_null": false,
                                                "literal_inequality": 0,
                                                "any_filter": false
                                              },
                                              "input": 1
                                            }
                                          }
                                        ],
                                        [
                                          2,
                                          [
                                            {
                                              "Column": [
                                                0,
                                                null
                                              ]
                                            }
                                          ],
                                          {
                                            "V1": {
                                              "unique_key": true,
                                              "key_length": 1,
                                              "arranged": false,
                                              "cardinality": null,
                                              "filters": {
                                                "literal_equality": false,
                                                "like": false,
                                                "is_null": false,
                                                "literal_inequality": 0,
                                                "any_filter": false
                                              },
                                              "input": 2
                                            }
                                          }
                                        ]
                                      ],
                                      [
                                        [
                                          2,
                                          [
                                            {
                                              "Column": [
                                                0,
                                                null
                                              ]
                                            }
                                          ],
                                          {
                                            "V1": {
                                              "unique_key": true,
                                              "key_length": 1,
                                              "arranged": false,
                                              "cardinality": null,
                                              "filters": {
                                                "literal_equality": false,
                                                "like": false,
                                                "is_null": false,
                                                "literal_inequality": 0,
                                                "any_filter": false
                                              },
                                              "input": 2
                                            }
                                          }
                                        ],
                                        [
                                          0,
                                          [
                                            {
                                              "Column": [
                                                0,
                                                null
                                              ]
                                            }
                                          ],
                                          {
                                            "V1": {
                                              "unique_key": false,
                                              "key_length": 1,
                                              "arranged": false,
                                              "cardinality": null,
                                              "filters": {
                                                "literal_equality": false,
                                                "like": false,
                                                "is_null": false,
                                                "literal_inequality": 0,
                                                "any_filter": false
                                              },
                                              "input": 0
                                            }
                                          }
                                        ]
                                      ],
                                      [
                                        [
                                          1,
                                          [
                                            {
                                              "Column": [
                                                0,
                                                null
                                              ]
                                            }
                                          ],
                                          {
                                            "V1": {
                                              "unique_key": true,
                                              "key_length": 1,
                                              "arranged": false,
                                              "cardinality": null,
                                              "filters": {
                                                "literal_equality": false,
                                                "like": false,
                                                "is_null": false,
                                                "literal_inequality": 0,
                                                "any_filter": false
                                              },
                                              "input": 1
                                            }
                                          }
                                        ],
                                        [
                                          0,
                                          [
                                            {
                                              "Column": [
                                                0,
                                                null
                                              ]
                                            }
                                          ],
                                          {
                                            "V1": {
                                              "unique_key": false,
                                              "key_length": 1,
                                              "arranged": false,
                                              "cardinality": null,
                                              "filters": {
                                                "literal_equality": false,
                                                "like": false,
                                                "is_null": false,
                                                "literal_inequality": 0,
                                                "any_filter": false
                                              },
                                              "input": 0
                                            }
                                          }
                                        ]
                                      ]
                                    ]
                                  }
                                }
                              },
                              "outputs": [
                                2,
                                4
                              ]
                            }
                          }
                        }
                      }
                    }
                  }
                }
              }
            }
          }
        }
      }
    }
  ],
  "sources": [
    {
      "id": {
        "User": 8
      },
      "op": {
        "expressions": [],
        "predicates": [
          [
            2,
            {
              "CallUnary": {
                "func": {
                  "Not": null
                },
                "expr": {
                  "CallUnary": {
                    "func": {
                      "IsNull": null
                    },
                    "expr": {
                      "Column": [
                        1,
                        "b"
                      ]
                    }
                  }
                }
              }
            }
          ]
        ],
        "projection": [
          0,
          1
        ],
        "input_arity": 2
      }
    }
  ]
}
EOF

# Test outer joins (ON syntax).
query T multiline
EXPLAIN OPTIMIZED PLAN WITH(raw) AS JSON FOR
SELECT t1.a, t2.a
FROM t as t1
LEFT JOIN t as t2 ON t1.b = t2.b
RIGHT JOIN t as t3 ON t2.b = t3.b
----
{
  "plans": [
    {
      "id": "Explained Query",
      "plan": {
        "Let": {
          "id": 0,
          "value": {
            "Filter": {
              "input": {
                "Get": {
                  "id": {
                    "Global": {
                      "User": 1
                    }
                  },
                  "typ": {
                    "column_types": [
                      {
                        "scalar_type": "Int32",
                        "nullable": true
                      },
                      {
                        "scalar_type": "Int32",
                        "nullable": true
                      }
                    ],
                    "keys": []
                  },
                  "access_strategy": {
                    "Index": [
                      [
                        {
                          "User": 4
                        },
                        "FullScan"
                      ]
                    ]
                  }
                }
              },
              "predicates": [
                {
                  "CallUnary": {
                    "func": {
                      "Not": null
                    },
                    "expr": {
                      "CallUnary": {
                        "func": {
                          "IsNull": null
                        },
                        "expr": {
                          "Column": [
                            1,
                            "b"
                          ]
                        }
                      }
                    }
                  }
                }
              ]
            }
          },
          "body": {
            "Let": {
              "id": 1,
              "value": {
                "ArrangeBy": {
                  "input": {
                    "Get": {
                      "id": {
                        "Local": 0
                      },
                      "typ": {
                        "column_types": [
                          {
                            "scalar_type": "Int32",
                            "nullable": true
                          },
                          {
                            "scalar_type": "Int32",
                            "nullable": false
                          }
                        ],
                        "keys": []
                      },
                      "access_strategy": "UnknownOrLocal"
                    }
                  },
                  "keys": [
                    [
                      {
                        "Column": [
                          1,
                          "b"
                        ]
                      }
                    ]
                  ]
                }
              },
              "body": {
                "Let": {
                  "id": 2,
                  "value": {
                    "ArrangeBy": {
                      "input": {
                        "Project": {
                          "input": {
                            "Get": {
                              "id": {
                                "Local": 0
                              },
                              "typ": {
                                "column_types": [
                                  {
                                    "scalar_type": "Int32",
                                    "nullable": true
                                  },
                                  {
                                    "scalar_type": "Int32",
                                    "nullable": false
                                  }
                                ],
                                "keys": []
                              },
                              "access_strategy": "UnknownOrLocal"
                            }
                          },
                          "outputs": [
                            1
                          ]
                        }
                      },
                      "keys": [
                        [
                          {
                            "Column": [
                              0,
                              "b"
                            ]
                          }
                        ]
                      ]
                    }
                  },
                  "body": {
                    "Let": {
                      "id": 3,
                      "value": {
                        "Project": {
                          "input": {
                            "Join": {
                              "inputs": [
                                {
                                  "Get": {
                                    "id": {
                                      "Local": 1
                                    },
                                    "typ": {
                                      "column_types": [
                                        {
                                          "scalar_type": "Int32",
                                          "nullable": true
                                        },
                                        {
                                          "scalar_type": "Int32",
                                          "nullable": false
                                        }
                                      ],
                                      "keys": []
                                    },
                                    "access_strategy": "UnknownOrLocal"
                                  }
                                },
                                {
                                  "Get": {
                                    "id": {
                                      "Local": 1
                                    },
                                    "typ": {
                                      "column_types": [
                                        {
                                          "scalar_type": "Int32",
                                          "nullable": true
                                        },
                                        {
                                          "scalar_type": "Int32",
                                          "nullable": false
                                        }
                                      ],
                                      "keys": []
                                    },
                                    "access_strategy": "UnknownOrLocal"
                                  }
                                },
                                {
                                  "Get": {
                                    "id": {
                                      "Local": 2
                                    },
                                    "typ": {
                                      "column_types": [
                                        {
                                          "scalar_type": "Int32",
                                          "nullable": false
                                        }
                                      ],
                                      "keys": []
                                    },
                                    "access_strategy": "UnknownOrLocal"
                                  }
                                }
                              ],
                              "equivalences": [
                                [
                                  {
                                    "Column": [
                                      1,
                                      "b"
                                    ]
                                  },
                                  {
                                    "Column": [
                                      3,
                                      "b"
                                    ]
                                  },
                                  {
                                    "Column": [
                                      4,
                                      "b"
                                    ]
                                  }
                                ]
                              ],
                              "implementation": {
                                "DeltaQuery": [
                                  [
                                    [
                                      1,
                                      [
                                        {
                                          "Column": [
                                            1,
                                            "b"
                                          ]
                                        }
                                      ],
                                      {
                                        "V1": {
                                          "unique_key": false,
                                          "key_length": 1,
                                          "arranged": false,
                                          "cardinality": null,
                                          "filters": {
                                            "literal_equality": false,
                                            "like": false,
                                            "is_null": false,
                                            "literal_inequality": 0,
                                            "any_filter": false
                                          },
                                          "input": 1
                                        }
                                      }
                                    ],
                                    [
                                      2,
                                      [
                                        {
                                          "Column": [
                                            0,
                                            "b"
                                          ]
                                        }
                                      ],
                                      {
                                        "V1": {
                                          "unique_key": false,
                                          "key_length": 1,
                                          "arranged": false,
                                          "cardinality": null,
                                          "filters": {
                                            "literal_equality": false,
                                            "like": false,
                                            "is_null": false,
                                            "literal_inequality": 0,
                                            "any_filter": false
                                          },
                                          "input": 2
                                        }
                                      }
                                    ]
                                  ],
                                  [
                                    [
                                      0,
                                      [
                                        {
                                          "Column": [
                                            1,
                                            "b"
                                          ]
                                        }
                                      ],
                                      {
                                        "V1": {
                                          "unique_key": false,
                                          "key_length": 1,
                                          "arranged": false,
                                          "cardinality": null,
                                          "filters": {
                                            "literal_equality": false,
                                            "like": false,
                                            "is_null": false,
                                            "literal_inequality": 0,
                                            "any_filter": false
                                          },
                                          "input": 0
                                        }
                                      }
                                    ],
                                    [
                                      2,
                                      [
                                        {
                                          "Column": [
                                            0,
                                            "b"
                                          ]
                                        }
                                      ],
                                      {
                                        "V1": {
                                          "unique_key": false,
                                          "key_length": 1,
                                          "arranged": false,
                                          "cardinality": null,
                                          "filters": {
                                            "literal_equality": false,
                                            "like": false,
                                            "is_null": false,
                                            "literal_inequality": 0,
                                            "any_filter": false
                                          },
                                          "input": 2
                                        }
                                      }
                                    ]
                                  ],
                                  [
                                    [
                                      0,
                                      [
                                        {
                                          "Column": [
                                            1,
                                            "b"
                                          ]
                                        }
                                      ],
                                      {
                                        "V1": {
                                          "unique_key": false,
                                          "key_length": 1,
                                          "arranged": false,
                                          "cardinality": null,
                                          "filters": {
                                            "literal_equality": false,
                                            "like": false,
                                            "is_null": false,
                                            "literal_inequality": 0,
                                            "any_filter": false
                                          },
                                          "input": 0
                                        }
                                      }
                                    ],
                                    [
                                      1,
                                      [
                                        {
                                          "Column": [
                                            1,
                                            "b"
                                          ]
                                        }
                                      ],
                                      {
                                        "V1": {
                                          "unique_key": false,
                                          "key_length": 1,
                                          "arranged": false,
                                          "cardinality": null,
                                          "filters": {
                                            "literal_equality": false,
                                            "like": false,
                                            "is_null": false,
                                            "literal_inequality": 0,
                                            "any_filter": false
                                          },
                                          "input": 1
                                        }
                                      }
                                    ]
                                  ]
                                ]
                              }
                            }
                          },
                          "outputs": [
                            0,
                            1,
                            2
                          ]
                        }
                      },
                      "body": {
                        "Union": {
                          "base": {
                            "Map": {
                              "input": {
                                "Union": {
                                  "base": {
                                    "Negate": {
                                      "input": {
                                        "Project": {
                                          "input": {
                                            "Join": {
                                              "inputs": [
                                                {
                                                  "Get": {
                                                    "id": {
                                                      "Local": 2
                                                    },
                                                    "typ": {
                                                      "column_types": [
                                                        {
                                                          "scalar_type": "Int32",
                                                          "nullable": false
                                                        }
                                                      ],
                                                      "keys": []
                                                    },
                                                    "access_strategy": "UnknownOrLocal"
                                                  }
                                                },
                                                {
                                                  "ArrangeBy": {
                                                    "input": {
                                                      "Reduce": {
                                                        "input": {
                                                          "Project": {
                                                            "input": {
                                                              "Get": {
                                                                "id": {
                                                                  "Local": 3
                                                                },
                                                                "typ": {
                                                                  "column_types": [
                                                                    {
                                                                      "scalar_type": "Int32",
                                                                      "nullable": true
                                                                    },
                                                                    {
                                                                      "scalar_type": "Int32",
                                                                      "nullable": false
                                                                    },
                                                                    {
                                                                      "scalar_type": "Int32",
                                                                      "nullable": true
                                                                    }
                                                                  ],
                                                                  "keys": []
                                                                },
                                                                "access_strategy": "UnknownOrLocal"
                                                              }
                                                            },
                                                            "outputs": [
                                                              1
                                                            ]
                                                          }
                                                        },
                                                        "group_key": [
                                                          {
                                                            "Column": [
                                                              0,
                                                              null
                                                            ]
                                                          }
                                                        ],
                                                        "aggregates": [],
                                                        "monotonic": false,
                                                        "expected_group_size": null
                                                      }
                                                    },
                                                    "keys": [
                                                      [
                                                        {
                                                          "Column": [
                                                            0,
                                                            null
                                                          ]
                                                        }
                                                      ]
                                                    ]
                                                  }
                                                }
                                              ],
                                              "equivalences": [
                                                [
                                                  {
                                                    "Column": [
                                                      0,
                                                      "b"
                                                    ]
                                                  },
                                                  {
                                                    "Column": [
                                                      1,
                                                      null
                                                    ]
                                                  }
                                                ]
                                              ],
                                              "implementation": {
                                                "Differential": [
                                                  [
                                                    1,
                                                    [
                                                      {
                                                        "Column": [
                                                          0,
                                                          null
                                                        ]
                                                      }
                                                    ],
                                                    {
                                                      "V1": {
                                                        "unique_key": true,
                                                        "key_length": 1,
                                                        "arranged": true,
                                                        "cardinality": null,
                                                        "filters": {
                                                          "literal_equality": false,
                                                          "like": false,
                                                          "is_null": false,
                                                          "literal_inequality": 0,
                                                          "any_filter": false
                                                        },
                                                        "input": 1
                                                      }
                                                    }
                                                  ],
                                                  [
                                                    [
                                                      0,
                                                      [
                                                        {
                                                          "Column": [
                                                            0,
                                                            "b"
                                                          ]
                                                        }
                                                      ],
                                                      {
                                                        "V1": {
                                                          "unique_key": false,
                                                          "key_length": 1,
                                                          "arranged": false,
                                                          "cardinality": null,
                                                          "filters": {
                                                            "literal_equality": false,
                                                            "like": false,
                                                            "is_null": false,
                                                            "literal_inequality": 0,
                                                            "any_filter": false
                                                          },
                                                          "input": 0
                                                        }
                                                      }
                                                    ]
                                                  ]
                                                ]
                                              }
                                            }
                                          },
                                          "outputs": []
                                        }
                                      }
                                    }
                                  },
                                  "inputs": [
                                    {
                                      "Project": {
                                        "input": {
                                          "Get": {
                                            "id": {
                                              "Global": {
                                                "User": 1
                                              }
                                            },
                                            "typ": {
                                              "column_types": [
                                                {
                                                  "scalar_type": "Int32",
                                                  "nullable": true
                                                },
                                                {
                                                  "scalar_type": "Int32",
                                                  "nullable": true
                                                }
                                              ],
                                              "keys": []
                                            },
                                            "access_strategy": {
                                              "Index": [
                                                [
                                                  {
                                                    "User": 4
                                                  },
                                                  "FullScan"
                                                ]
                                              ]
                                            }
                                          }
                                        },
                                        "outputs": []
                                      }
                                    }
                                  ]
                                }
                              },
                              "scalars": [
                                {
                                  "Literal": [
                                    {
                                      "Ok": {
                                        "data": [
                                          0
                                        ]
                                      }
                                    },
                                    {
                                      "scalar_type": "Int32",
                                      "nullable": true
                                    }
                                  ]
                                },
                                {
                                  "Literal": [
                                    {
                                      "Ok": {
                                        "data": [
                                          0
                                        ]
                                      }
                                    },
                                    {
                                      "scalar_type": "Int32",
                                      "nullable": true
                                    }
                                  ]
                                }
                              ]
                            }
                          },
                          "inputs": [
                            {
                              "Project": {
                                "input": {
                                  "Get": {
                                    "id": {
                                      "Local": 3
                                    },
                                    "typ": {
                                      "column_types": [
                                        {
                                          "scalar_type": "Int32",
                                          "nullable": true
                                        },
                                        {
                                          "scalar_type": "Int32",
                                          "nullable": false
                                        },
                                        {
                                          "scalar_type": "Int32",
                                          "nullable": true
                                        }
                                      ],
                                      "keys": []
                                    },
                                    "access_strategy": "UnknownOrLocal"
                                  }
                                },
                                "outputs": [
                                  0,
                                  2
                                ]
                              }
                            }
                          ]
                        }
                      }
                    }
                  }
                }
              }
            }
          }
        }
      }
    }
  ],
  "sources": []
}
EOF

# Test multiple CTEs: a case where we cannot pull the let statement up through
# the join because the local l0 is correlated against the lhs of the enclosing join.
query T multiline
EXPLAIN OPTIMIZED PLAN WITH(raw) AS JSON FOR
SELECT
  *
FROM
  (
    SELECT * FROM t
  ) as r1
  CROSS JOIN LATERAL (
    WITH r2 as (
      SELECT MAX(r1.a * t.a) AS m FROM t
    )
    SELECT * FROM r2 WHERE r2.m != r1.a
  ) as r3
  CROSS JOIN LATERAL (
    WITH r4 as (
      SELECT MAX(r1.a * t.a) AS m FROM t
    )
    SELECT * FROM r4 WHERE r4.m != r1.a OR (r4.m IS NOT NULL AND r1.a IS NULL)
  ) as r5;
----
{
  "plans": [
    {
      "id": "Explained Query",
      "plan": {
        "Let": {
          "id": 0,
          "value": {
            "Project": {
              "input": {
                "Get": {
                  "id": {
                    "Global": {
                      "User": 1
                    }
                  },
                  "typ": {
                    "column_types": [
                      {
                        "scalar_type": "Int32",
                        "nullable": true
                      },
                      {
                        "scalar_type": "Int32",
                        "nullable": true
                      }
                    ],
                    "keys": []
                  },
                  "access_strategy": {
                    "Index": [
                      [
                        {
                          "User": 4
                        },
                        "FullScan"
                      ]
                    ]
                  }
                }
              },
              "outputs": [
                0
              ]
            }
          },
          "body": {
            "Let": {
              "id": 1,
              "value": {
                "ArrangeBy": {
                  "input": {
                    "Get": {
                      "id": {
                        "Local": 0
                      },
                      "typ": {
                        "column_types": [
                          {
                            "scalar_type": "Int32",
                            "nullable": true
                          }
                        ],
                        "keys": []
                      },
                      "access_strategy": "UnknownOrLocal"
                    }
                  },
                  "keys": [
                    []
                  ]
                }
              },
              "body": {
                "Let": {
                  "id": 2,
                  "value": {
                    "Project": {
                      "input": {
                        "Filter": {
                          "input": {
                            "Join": {
                              "inputs": [
                                {
                                  "ArrangeBy": {
                                    "input": {
                                      "Get": {
                                        "id": {
                                          "Global": {
                                            "User": 1
                                          }
                                        },
                                        "typ": {
                                          "column_types": [
                                            {
                                              "scalar_type": "Int32",
                                              "nullable": true
                                            },
                                            {
                                              "scalar_type": "Int32",
                                              "nullable": true
                                            }
                                          ],
                                          "keys": []
                                        },
                                        "access_strategy": {
                                          "Index": [
                                            [
                                              {
                                                "User": 4
                                              },
                                              "DifferentialJoin"
                                            ]
                                          ]
                                        }
                                      }
                                    },
                                    "keys": [
                                      [
                                        {
                                          "Column": [
                                            0,
                                            "a"
                                          ]
                                        }
                                      ]
                                    ]
                                  }
                                },
                                {
                                  "ArrangeBy": {
                                    "input": {
                                      "Reduce": {
                                        "input": {
                                          "Join": {
                                            "inputs": [
                                              {
                                                "ArrangeBy": {
                                                  "input": {
                                                    "Reduce": {
                                                      "input": {
                                                        "Get": {
                                                          "id": {
                                                            "Local": 0
                                                          },
                                                          "typ": {
                                                            "column_types": [
                                                              {
                                                                "scalar_type": "Int32",
                                                                "nullable": true
                                                              }
                                                            ],
                                                            "keys": []
                                                          },
                                                          "access_strategy": "UnknownOrLocal"
                                                        }
                                                      },
                                                      "group_key": [
                                                        {
                                                          "Column": [
                                                            0,
                                                            null
                                                          ]
                                                        }
                                                      ],
                                                      "aggregates": [],
                                                      "monotonic": false,
                                                      "expected_group_size": null
                                                    }
                                                  },
                                                  "keys": [
                                                    []
                                                  ]
                                                }
                                              },
                                              {
                                                "Get": {
                                                  "id": {
                                                    "Local": 1
                                                  },
                                                  "typ": {
                                                    "column_types": [
                                                      {
                                                        "scalar_type": "Int32",
                                                        "nullable": true
                                                      }
                                                    ],
                                                    "keys": []
                                                  },
                                                  "access_strategy": "UnknownOrLocal"
                                                }
                                              }
                                            ],
                                            "equivalences": [],
                                            "implementation": {
                                              "Differential": [
                                                [
                                                  0,
                                                  [],
                                                  {
                                                    "V1": {
                                                      "unique_key": false,
                                                      "key_length": 0,
                                                      "arranged": false,
                                                      "cardinality": null,
                                                      "filters": {
                                                        "literal_equality": false,
                                                        "like": false,
                                                        "is_null": false,
                                                        "literal_inequality": 0,
                                                        "any_filter": false
                                                      },
                                                      "input": 0
                                                    }
                                                  }
                                                ],
                                                [
                                                  [
                                                    1,
                                                    [],
                                                    {
                                                      "V1": {
                                                        "unique_key": false,
                                                        "key_length": 0,
                                                        "arranged": false,
                                                        "cardinality": null,
                                                        "filters": {
                                                          "literal_equality": false,
                                                          "like": false,
                                                          "is_null": false,
                                                          "literal_inequality": 0,
                                                          "any_filter": false
                                                        },
                                                        "input": 1
                                                      }
                                                    }
                                                  ]
                                                ]
                                              ]
                                            }
                                          }
                                        },
                                        "group_key": [
                                          {
                                            "Column": [
                                              0,
                                              null
                                            ]
                                          }
                                        ],
                                        "aggregates": [
                                          {
                                            "func": "MaxInt32",
                                            "expr": {
                                              "CallBinary": {
                                                "func": "MulInt32",
                                                "expr1": {
                                                  "Column": [
                                                    0,
                                                    "a"
                                                  ]
                                                },
                                                "expr2": {
                                                  "Column": [
                                                    1,
                                                    "a"
                                                  ]
                                                }
                                              }
                                            },
                                            "distinct": false
                                          }
                                        ],
                                        "monotonic": false,
                                        "expected_group_size": null
                                      }
                                    },
                                    "keys": [
                                      [
                                        {
                                          "Column": [
                                            0,
                                            null
                                          ]
                                        }
                                      ]
                                    ]
                                  }
                                }
                              ],
                              "equivalences": [
                                [
                                  {
                                    "Column": [
                                      0,
                                      null
                                    ]
                                  },
                                  {
                                    "Column": [
                                      2,
                                      null
                                    ]
                                  }
                                ]
                              ],
                              "implementation": {
                                "Differential": [
                                  [
                                    1,
                                    [
                                      {
                                        "Column": [
                                          0,
                                          null
                                        ]
                                      }
                                    ],
                                    {
                                      "V1": {
                                        "unique_key": true,
                                        "key_length": 1,
                                        "arranged": true,
                                        "cardinality": null,
                                        "filters": {
                                          "literal_equality": false,
                                          "like": false,
                                          "is_null": false,
                                          "literal_inequality": 0,
                                          "any_filter": true
                                        },
                                        "input": 1
                                      }
                                    }
                                  ],
                                  [
                                    [
                                      0,
                                      [
                                        {
                                          "Column": [
                                            0,
                                            "a"
                                          ]
                                        }
                                      ],
                                      {
                                        "V1": {
                                          "unique_key": false,
                                          "key_length": 1,
                                          "arranged": true,
                                          "cardinality": null,
                                          "filters": {
                                            "literal_equality": false,
                                            "like": false,
                                            "is_null": false,
                                            "literal_inequality": 0,
                                            "any_filter": true
                                          },
                                          "input": 0
                                        }
                                      }
                                    ]
                                  ]
                                ]
                              }
                            }
                          },
                          "predicates": [
                            {
                              "CallBinary": {
                                "func": "NotEq",
                                "expr1": {
                                  "Column": [
                                    0,
                                    null
                                  ]
                                },
                                "expr2": {
                                  "Column": [
                                    3,
                                    "m"
                                  ]
                                }
                              }
                            }
                          ]
                        }
                      },
                      "outputs": [
                        0,
                        1,
                        3
                      ]
                    }
                  },
                  "body": {
                    "Project": {
                      "input": {
                        "Filter": {
                          "input": {
                            "Join": {
                              "inputs": [
                                {
                                  "ArrangeBy": {
                                    "input": {
                                      "Get": {
                                        "id": {
                                          "Local": 2
                                        },
                                        "typ": {
                                          "column_types": [
                                            {
                                              "scalar_type": "Int32",
                                              "nullable": false
                                            },
                                            {
                                              "scalar_type": "Int32",
                                              "nullable": true
                                            },
                                            {
                                              "scalar_type": "Int32",
                                              "nullable": false
                                            }
                                          ],
                                          "keys": []
                                        },
                                        "access_strategy": "UnknownOrLocal"
                                      }
                                    },
                                    "keys": [
                                      [
                                        {
                                          "Column": [
                                            0,
                                            null
                                          ]
                                        }
                                      ]
                                    ]
                                  }
                                },
                                {
                                  "ArrangeBy": {
                                    "input": {
                                      "Reduce": {
                                        "input": {
                                          "Join": {
                                            "inputs": [
                                              {
                                                "ArrangeBy": {
                                                  "input": {
                                                    "Reduce": {
                                                      "input": {
                                                        "Project": {
                                                          "input": {
                                                            "Get": {
                                                              "id": {
                                                                "Local": 2
                                                              },
                                                              "typ": {
                                                                "column_types": [
                                                                  {
                                                                    "scalar_type": "Int32",
                                                                    "nullable": false
                                                                  },
                                                                  {
                                                                    "scalar_type": "Int32",
                                                                    "nullable": true
                                                                  },
                                                                  {
                                                                    "scalar_type": "Int32",
                                                                    "nullable": false
                                                                  }
                                                                ],
                                                                "keys": []
                                                              },
                                                              "access_strategy": "UnknownOrLocal"
                                                            }
                                                          },
                                                          "outputs": [
                                                            0
                                                          ]
                                                        }
                                                      },
                                                      "group_key": [
                                                        {
                                                          "Column": [
                                                            0,
                                                            null
                                                          ]
                                                        }
                                                      ],
                                                      "aggregates": [],
                                                      "monotonic": false,
                                                      "expected_group_size": null
                                                    }
                                                  },
                                                  "keys": [
                                                    []
                                                  ]
                                                }
                                              },
                                              {
                                                "Get": {
                                                  "id": {
                                                    "Local": 1
                                                  },
                                                  "typ": {
                                                    "column_types": [
                                                      {
                                                        "scalar_type": "Int32",
                                                        "nullable": true
                                                      }
                                                    ],
                                                    "keys": []
                                                  },
                                                  "access_strategy": "UnknownOrLocal"
                                                }
                                              }
                                            ],
                                            "equivalences": [],
                                            "implementation": {
                                              "Differential": [
                                                [
                                                  0,
                                                  [],
                                                  {
                                                    "V1": {
                                                      "unique_key": false,
                                                      "key_length": 0,
                                                      "arranged": false,
                                                      "cardinality": null,
                                                      "filters": {
                                                        "literal_equality": false,
                                                        "like": false,
                                                        "is_null": false,
                                                        "literal_inequality": 0,
                                                        "any_filter": false
                                                      },
                                                      "input": 0
                                                    }
                                                  }
                                                ],
                                                [
                                                  [
                                                    1,
                                                    [],
                                                    {
                                                      "V1": {
                                                        "unique_key": false,
                                                        "key_length": 0,
                                                        "arranged": false,
                                                        "cardinality": null,
                                                        "filters": {
                                                          "literal_equality": false,
                                                          "like": false,
                                                          "is_null": false,
                                                          "literal_inequality": 0,
                                                          "any_filter": false
                                                        },
                                                        "input": 1
                                                      }
                                                    }
                                                  ]
                                                ]
                                              ]
                                            }
                                          }
                                        },
                                        "group_key": [
                                          {
                                            "Column": [
                                              0,
                                              null
                                            ]
                                          }
                                        ],
                                        "aggregates": [
                                          {
                                            "func": "MaxInt32",
                                            "expr": {
                                              "CallBinary": {
                                                "func": "MulInt32",
                                                "expr1": {
                                                  "Column": [
                                                    0,
                                                    "a"
                                                  ]
                                                },
                                                "expr2": {
                                                  "Column": [
                                                    1,
                                                    "a"
                                                  ]
                                                }
                                              }
                                            },
                                            "distinct": false
                                          }
                                        ],
                                        "monotonic": false,
                                        "expected_group_size": null
                                      }
                                    },
                                    "keys": [
                                      [
                                        {
                                          "Column": [
                                            0,
                                            null
                                          ]
                                        }
                                      ]
                                    ]
                                  }
                                }
                              ],
                              "equivalences": [
                                [
                                  {
                                    "Column": [
                                      0,
                                      null
                                    ]
                                  },
                                  {
                                    "Column": [
                                      3,
                                      null
                                    ]
                                  }
                                ]
                              ],
                              "implementation": {
                                "Differential": [
                                  [
                                    1,
                                    [
                                      {
                                        "Column": [
                                          0,
                                          null
                                        ]
                                      }
                                    ],
                                    {
                                      "V1": {
                                        "unique_key": true,
                                        "key_length": 1,
                                        "arranged": true,
                                        "cardinality": null,
                                        "filters": {
                                          "literal_equality": false,
                                          "like": false,
                                          "is_null": false,
                                          "literal_inequality": 0,
                                          "any_filter": true
                                        },
                                        "input": 1
                                      }
                                    }
                                  ],
                                  [
                                    [
                                      0,
                                      [
                                        {
                                          "Column": [
                                            0,
                                            null
                                          ]
                                        }
                                      ],
                                      {
                                        "V1": {
                                          "unique_key": false,
                                          "key_length": 1,
                                          "arranged": false,
                                          "cardinality": null,
                                          "filters": {
                                            "literal_equality": false,
                                            "like": false,
                                            "is_null": false,
                                            "literal_inequality": 0,
                                            "any_filter": true
                                          },
                                          "input": 0
                                        }
                                      }
                                    ]
                                  ]
                                ]
                              }
                            }
                          },
                          "predicates": [
                            {
                              "CallBinary": {
                                "func": "NotEq",
                                "expr1": {
                                  "Column": [
                                    0,
                                    null
                                  ]
                                },
                                "expr2": {
                                  "Column": [
                                    4,
                                    "m"
                                  ]
                                }
                              }
                            }
                          ]
                        }
                      },
                      "outputs": [
                        0,
                        1,
                        2,
                        4
                      ]
                    }
                  }
                }
              }
            }
          }
        }
      }
    }
  ],
  "sources": []
}
EOF

# Test cross join.
query T multiline
EXPLAIN OPTIMIZED PLAN WITH(raw) AS JSON FOR
SELECT t1.a, t2.a FROM t as t1, t as t2
----
{
  "plans": [
    {
      "id": "Explained Query",
      "plan": {
        "Let": {
          "id": 0,
          "value": {
            "ArrangeBy": {
              "input": {
                "Project": {
                  "input": {
                    "Get": {
                      "id": {
                        "Global": {
                          "User": 1
                        }
                      },
                      "typ": {
                        "column_types": [
                          {
                            "scalar_type": "Int32",
                            "nullable": true
                          },
                          {
                            "scalar_type": "Int32",
                            "nullable": true
                          }
                        ],
                        "keys": []
                      },
                      "access_strategy": {
                        "Index": [
                          [
                            {
                              "User": 4
                            },
                            "FullScan"
                          ]
                        ]
                      }
                    }
                  },
                  "outputs": [
                    0
                  ]
                }
              },
              "keys": [
                []
              ]
            }
          },
          "body": {
            "Join": {
              "inputs": [
                {
                  "Get": {
                    "id": {
                      "Local": 0
                    },
                    "typ": {
                      "column_types": [
                        {
                          "scalar_type": "Int32",
                          "nullable": true
                        }
                      ],
                      "keys": []
                    },
                    "access_strategy": "UnknownOrLocal"
                  }
                },
                {
                  "Get": {
                    "id": {
                      "Local": 0
                    },
                    "typ": {
                      "column_types": [
                        {
                          "scalar_type": "Int32",
                          "nullable": true
                        }
                      ],
                      "keys": []
                    },
                    "access_strategy": "UnknownOrLocal"
                  }
                }
              ],
              "equivalences": [],
              "implementation": {
                "Differential": [
                  [
                    0,
                    [],
                    {
                      "V1": {
                        "unique_key": false,
                        "key_length": 0,
                        "arranged": false,
                        "cardinality": null,
                        "filters": {
                          "literal_equality": false,
                          "like": false,
                          "is_null": false,
                          "literal_inequality": 0,
                          "any_filter": false
                        },
                        "input": 0
                      }
                    }
                  ],
                  [
                    [
                      1,
                      [],
                      {
                        "V1": {
                          "unique_key": false,
                          "key_length": 0,
                          "arranged": false,
                          "cardinality": null,
                          "filters": {
                            "literal_equality": false,
                            "like": false,
                            "is_null": false,
                            "literal_inequality": 0,
                            "any_filter": false
                          },
                          "input": 1
                        }
                      }
                    ]
                  ]
                ]
              }
            }
          }
        }
      }
    }
  ],
  "sources": []
}
EOF

# Test cyclic join.
query T multiline
EXPLAIN OPTIMIZED PLAN WITH(raw) AS JSON FOR
SELECT t1.a, t2.a
FROM
  t as t1,
  t as t2,
  t as t3
WHERE t1.b = t2.b AND t2.b = t3.b
----
{
  "plans": [
    {
      "id": "Explained Query",
      "plan": {
        "Let": {
          "id": 0,
          "value": {
            "Filter": {
              "input": {
                "Get": {
                  "id": {
                    "Global": {
                      "User": 1
                    }
                  },
                  "typ": {
                    "column_types": [
                      {
                        "scalar_type": "Int32",
                        "nullable": true
                      },
                      {
                        "scalar_type": "Int32",
                        "nullable": true
                      }
                    ],
                    "keys": []
                  },
                  "access_strategy": {
                    "Index": [
                      [
                        {
                          "User": 4
                        },
                        "FullScan"
                      ]
                    ]
                  }
                }
              },
              "predicates": [
                {
                  "CallUnary": {
                    "func": {
                      "Not": null
                    },
                    "expr": {
                      "CallUnary": {
                        "func": {
                          "IsNull": null
                        },
                        "expr": {
                          "Column": [
                            1,
                            "b"
                          ]
                        }
                      }
                    }
                  }
                }
              ]
            }
          },
          "body": {
            "Let": {
              "id": 1,
              "value": {
                "ArrangeBy": {
                  "input": {
                    "Get": {
                      "id": {
                        "Local": 0
                      },
                      "typ": {
                        "column_types": [
                          {
                            "scalar_type": "Int32",
                            "nullable": true
                          },
                          {
                            "scalar_type": "Int32",
                            "nullable": false
                          }
                        ],
                        "keys": []
                      },
                      "access_strategy": "UnknownOrLocal"
                    }
                  },
                  "keys": [
                    [
                      {
                        "Column": [
                          1,
                          "b"
                        ]
                      }
                    ]
                  ]
                }
              },
              "body": {
                "Project": {
                  "input": {
                    "Join": {
                      "inputs": [
                        {
                          "Get": {
                            "id": {
                              "Local": 1
                            },
                            "typ": {
                              "column_types": [
                                {
                                  "scalar_type": "Int32",
                                  "nullable": true
                                },
                                {
                                  "scalar_type": "Int32",
                                  "nullable": false
                                }
                              ],
                              "keys": []
                            },
                            "access_strategy": "UnknownOrLocal"
                          }
                        },
                        {
                          "Get": {
                            "id": {
                              "Local": 1
                            },
                            "typ": {
                              "column_types": [
                                {
                                  "scalar_type": "Int32",
                                  "nullable": true
                                },
                                {
                                  "scalar_type": "Int32",
                                  "nullable": false
                                }
                              ],
                              "keys": []
                            },
                            "access_strategy": "UnknownOrLocal"
                          }
                        },
                        {
                          "ArrangeBy": {
                            "input": {
                              "Project": {
                                "input": {
                                  "Get": {
                                    "id": {
                                      "Local": 0
                                    },
                                    "typ": {
                                      "column_types": [
                                        {
                                          "scalar_type": "Int32",
                                          "nullable": true
                                        },
                                        {
                                          "scalar_type": "Int32",
                                          "nullable": false
                                        }
                                      ],
                                      "keys": []
                                    },
                                    "access_strategy": "UnknownOrLocal"
                                  }
                                },
                                "outputs": [
                                  1
                                ]
                              }
                            },
                            "keys": [
                              [
                                {
                                  "Column": [
                                    0,
                                    "b"
                                  ]
                                }
                              ]
                            ]
                          }
                        }
                      ],
                      "equivalences": [
                        [
                          {
                            "Column": [
                              1,
                              "b"
                            ]
                          },
                          {
                            "Column": [
                              3,
                              "b"
                            ]
                          },
                          {
                            "Column": [
                              4,
                              "b"
                            ]
                          }
                        ]
                      ],
                      "implementation": {
                        "DeltaQuery": [
                          [
                            [
                              1,
                              [
                                {
                                  "Column": [
                                    1,
                                    "b"
                                  ]
                                }
                              ],
                              {
                                "V1": {
                                  "unique_key": false,
                                  "key_length": 1,
                                  "arranged": false,
                                  "cardinality": null,
                                  "filters": {
                                    "literal_equality": false,
                                    "like": false,
                                    "is_null": false,
                                    "literal_inequality": 0,
                                    "any_filter": false
                                  },
                                  "input": 1
                                }
                              }
                            ],
                            [
                              2,
                              [
                                {
                                  "Column": [
                                    0,
                                    "b"
                                  ]
                                }
                              ],
                              {
                                "V1": {
                                  "unique_key": false,
                                  "key_length": 1,
                                  "arranged": false,
                                  "cardinality": null,
                                  "filters": {
                                    "literal_equality": false,
                                    "like": false,
                                    "is_null": false,
                                    "literal_inequality": 0,
                                    "any_filter": false
                                  },
                                  "input": 2
                                }
                              }
                            ]
                          ],
                          [
                            [
                              0,
                              [
                                {
                                  "Column": [
                                    1,
                                    "b"
                                  ]
                                }
                              ],
                              {
                                "V1": {
                                  "unique_key": false,
                                  "key_length": 1,
                                  "arranged": false,
                                  "cardinality": null,
                                  "filters": {
                                    "literal_equality": false,
                                    "like": false,
                                    "is_null": false,
                                    "literal_inequality": 0,
                                    "any_filter": false
                                  },
                                  "input": 0
                                }
                              }
                            ],
                            [
                              2,
                              [
                                {
                                  "Column": [
                                    0,
                                    "b"
                                  ]
                                }
                              ],
                              {
                                "V1": {
                                  "unique_key": false,
                                  "key_length": 1,
                                  "arranged": false,
                                  "cardinality": null,
                                  "filters": {
                                    "literal_equality": false,
                                    "like": false,
                                    "is_null": false,
                                    "literal_inequality": 0,
                                    "any_filter": false
                                  },
                                  "input": 2
                                }
                              }
                            ]
                          ],
                          [
                            [
                              0,
                              [
                                {
                                  "Column": [
                                    1,
                                    "b"
                                  ]
                                }
                              ],
                              {
                                "V1": {
                                  "unique_key": false,
                                  "key_length": 1,
                                  "arranged": false,
                                  "cardinality": null,
                                  "filters": {
                                    "literal_equality": false,
                                    "like": false,
                                    "is_null": false,
                                    "literal_inequality": 0,
                                    "any_filter": false
                                  },
                                  "input": 0
                                }
                              }
                            ],
                            [
                              1,
                              [
                                {
                                  "Column": [
                                    1,
                                    "b"
                                  ]
                                }
                              ],
                              {
                                "V1": {
                                  "unique_key": false,
                                  "key_length": 1,
                                  "arranged": false,
                                  "cardinality": null,
                                  "filters": {
                                    "literal_equality": false,
                                    "like": false,
                                    "is_null": false,
                                    "literal_inequality": 0,
                                    "any_filter": false
                                  },
                                  "input": 1
                                }
                              }
                            ]
                          ]
                        ]
                      }
                    }
                  },
                  "outputs": [
                    0,
                    2
                  ]
                }
              }
            }
          }
        }
      }
    }
  ],
  "sources": []
}
EOF

# Create indexes required for differential join tests

statement ok
CREATE INDEX u_c_idx ON U(c);

statement ok
CREATE INDEX u_d_idx ON U(d);

statement ok
CREATE INDEX v_e_idx ON V(e);

# Test a differential join.
query T multiline
EXPLAIN OPTIMIZED PLAN WITH(raw) AS JSON FOR
SELECT a, b, c, d, e, f
FROM t, u, v
WHERE a = c and d = e and b = f
----
{
  "plans": [
    {
      "id": "Explained Query",
      "plan": {
        "Project": {
          "input": {
            "Join": {
              "inputs": [
                {
                  "ArrangeBy": {
                    "input": {
                      "Filter": {
                        "input": {
                          "Get": {
                            "id": {
                              "Global": {
                                "User": 1
                              }
                            },
                            "typ": {
                              "column_types": [
                                {
                                  "scalar_type": "Int32",
                                  "nullable": true
                                },
                                {
                                  "scalar_type": "Int32",
                                  "nullable": true
                                }
                              ],
                              "keys": []
                            },
                            "access_strategy": {
                              "Index": [
                                [
                                  {
                                    "User": 4
                                  },
                                  "FullScan"
                                ]
                              ]
                            }
                          }
                        },
                        "predicates": [
                          {
                            "CallUnary": {
                              "func": {
                                "Not": null
                              },
                              "expr": {
                                "CallUnary": {
                                  "func": {
                                    "IsNull": null
                                  },
                                  "expr": {
                                    "Column": [
                                      0,
                                      "a"
                                    ]
                                  }
                                }
                              }
                            }
                          },
                          {
                            "CallUnary": {
                              "func": {
                                "Not": null
                              },
                              "expr": {
                                "CallUnary": {
                                  "func": {
                                    "IsNull": null
                                  },
                                  "expr": {
                                    "Column": [
                                      1,
                                      "b"
                                    ]
                                  }
                                }
                              }
                            }
                          }
                        ]
                      }
                    },
                    "keys": [
                      [
                        {
                          "Column": [
                            0,
                            "a"
                          ]
                        }
                      ],
                      [
                        {
                          "Column": [
                            0,
                            "a"
                          ]
                        },
                        {
                          "Column": [
                            1,
                            "b"
                          ]
                        }
                      ]
                    ]
                  }
                },
                {
                  "ArrangeBy": {
                    "input": {
                      "Get": {
                        "id": {
                          "Global": {
                            "User": 2
                          }
                        },
                        "typ": {
                          "column_types": [
                            {
                              "scalar_type": "Int32",
                              "nullable": true
                            },
                            {
                              "scalar_type": "Int32",
                              "nullable": true
                            }
                          ],
                          "keys": []
                        },
                        "access_strategy": {
                          "Index": [
                            [
                              {
                                "User": 9
                              },
                              {
                                "DeltaJoin": "Lookup"
                              }
                            ],
                            [
                              {
                                "User": 10
                              },
                              {
                                "DeltaJoin": "Lookup"
                              }
                            ]
                          ]
                        }
                      }
                    },
                    "keys": [
                      [
                        {
                          "Column": [
                            0,
                            "c"
                          ]
                        }
                      ],
                      [
                        {
                          "Column": [
                            1,
                            "d"
                          ]
                        }
                      ]
                    ]
                  }
                },
                {
                  "ArrangeBy": {
                    "input": {
                      "Filter": {
                        "input": {
                          "Get": {
                            "id": {
                              "Global": {
                                "User": 3
                              }
                            },
                            "typ": {
                              "column_types": [
                                {
                                  "scalar_type": "Int32",
                                  "nullable": true
                                },
                                {
                                  "scalar_type": "Int32",
                                  "nullable": true
                                }
                              ],
                              "keys": []
                            },
                            "access_strategy": {
                              "Index": [
                                [
                                  {
                                    "User": 11
                                  },
                                  "FullScan"
                                ]
                              ]
                            }
                          }
                        },
                        "predicates": [
                          {
                            "CallUnary": {
                              "func": {
                                "Not": null
                              },
                              "expr": {
                                "CallUnary": {
                                  "func": {
                                    "IsNull": null
                                  },
                                  "expr": {
                                    "Column": [
                                      0,
                                      "e"
                                    ]
                                  }
                                }
                              }
                            }
                          },
                          {
                            "CallUnary": {
                              "func": {
                                "Not": null
                              },
                              "expr": {
                                "CallUnary": {
                                  "func": {
                                    "IsNull": null
                                  },
                                  "expr": {
                                    "Column": [
                                      1,
                                      "f"
                                    ]
                                  }
                                }
                              }
                            }
                          }
                        ]
                      }
                    },
                    "keys": [
                      [
                        {
                          "Column": [
                            0,
                            "e"
                          ]
                        },
                        {
                          "Column": [
                            1,
                            "f"
                          ]
                        }
                      ]
                    ]
                  }
                }
              ],
              "equivalences": [
                [
                  {
                    "Column": [
                      0,
                      "a"
                    ]
                  },
                  {
                    "Column": [
                      2,
                      "c"
                    ]
                  }
                ],
                [
                  {
                    "Column": [
                      1,
                      "b"
                    ]
                  },
                  {
                    "Column": [
                      5,
                      "f"
                    ]
                  }
                ],
                [
                  {
                    "Column": [
                      3,
                      "d"
                    ]
                  },
                  {
                    "Column": [
                      4,
                      "e"
                    ]
                  }
                ]
              ],
              "implementation": {
                "DeltaQuery": [
                  [
                    [
                      1,
                      [
                        {
                          "Column": [
                            0,
                            "c"
                          ]
                        }
                      ],
                      {
                        "V1": {
                          "unique_key": false,
                          "key_length": 1,
                          "arranged": true,
                          "cardinality": null,
                          "filters": {
                            "literal_equality": false,
                            "like": false,
                            "is_null": false,
                            "literal_inequality": 0,
                            "any_filter": false
                          },
                          "input": 1
                        }
                      }
                    ],
                    [
                      2,
                      [
                        {
                          "Column": [
                            0,
                            "e"
                          ]
                        },
                        {
                          "Column": [
                            1,
                            "f"
                          ]
                        }
                      ],
                      {
                        "V1": {
                          "unique_key": false,
                          "key_length": 2,
                          "arranged": false,
                          "cardinality": null,
                          "filters": {
                            "literal_equality": false,
                            "like": false,
                            "is_null": false,
                            "literal_inequality": 0,
                            "any_filter": false
                          },
                          "input": 2
                        }
                      }
                    ]
                  ],
                  [
                    [
                      0,
                      [
                        {
                          "Column": [
                            0,
                            "a"
                          ]
                        }
                      ],
                      {
                        "V1": {
                          "unique_key": false,
                          "key_length": 1,
                          "arranged": true,
                          "cardinality": null,
                          "filters": {
                            "literal_equality": false,
                            "like": false,
                            "is_null": false,
                            "literal_inequality": 0,
                            "any_filter": false
                          },
                          "input": 0
                        }
                      }
                    ],
                    [
                      2,
                      [
                        {
                          "Column": [
                            0,
                            "e"
                          ]
                        },
                        {
                          "Column": [
                            1,
                            "f"
                          ]
                        }
                      ],
                      {
                        "V1": {
                          "unique_key": false,
                          "key_length": 2,
                          "arranged": false,
                          "cardinality": null,
                          "filters": {
                            "literal_equality": false,
                            "like": false,
                            "is_null": false,
                            "literal_inequality": 0,
                            "any_filter": false
                          },
                          "input": 2
                        }
                      }
                    ]
                  ],
                  [
                    [
                      1,
                      [
                        {
                          "Column": [
                            1,
                            "d"
                          ]
                        }
                      ],
                      {
                        "V1": {
                          "unique_key": false,
                          "key_length": 1,
                          "arranged": true,
                          "cardinality": null,
                          "filters": {
                            "literal_equality": false,
                            "like": false,
                            "is_null": false,
                            "literal_inequality": 0,
                            "any_filter": false
                          },
                          "input": 1
                        }
                      }
                    ],
                    [
                      0,
                      [
                        {
                          "Column": [
                            0,
                            "a"
                          ]
                        },
                        {
                          "Column": [
                            1,
                            "b"
                          ]
                        }
                      ],
                      {
                        "V1": {
                          "unique_key": false,
                          "key_length": 2,
                          "arranged": false,
                          "cardinality": null,
                          "filters": {
                            "literal_equality": false,
                            "like": false,
                            "is_null": false,
                            "literal_inequality": 0,
                            "any_filter": false
                          },
                          "input": 0
                        }
                      }
                    ]
                  ]
                ]
              }
            }
          },
          "outputs": [
            0,
            1,
            0,
            3,
            3,
            1
          ]
        }
      }
    }
  ],
  "sources": []
}
EOF

# Create indexes required for delta join tests

statement ok
CREATE INDEX t_b_idx ON T(b);

# Test a delta join WITH.
query T multiline
EXPLAIN OPTIMIZED PLAN WITH(raw) AS JSON FOR
SELECT a, b, c, d, e, f
FROM t, u, v
WHERE b = c and d = e
----
{
  "plans": [
    {
      "id": "Explained Query",
      "plan": {
        "Project": {
          "input": {
            "Filter": {
              "input": {
                "Join": {
                  "inputs": [
                    {
                      "ArrangeBy": {
                        "input": {
                          "Get": {
                            "id": {
                              "Global": {
                                "User": 1
                              }
                            },
                            "typ": {
                              "column_types": [
                                {
                                  "scalar_type": "Int32",
                                  "nullable": true
                                },
                                {
                                  "scalar_type": "Int32",
                                  "nullable": true
                                }
                              ],
                              "keys": []
                            },
                            "access_strategy": {
                              "Index": [
                                [
                                  {
                                    "User": 12
                                  },
                                  {
                                    "DeltaJoin": "FirstInputFullScan"
                                  }
                                ]
                              ]
                            }
                          }
                        },
                        "keys": [
                          [
                            {
                              "Column": [
                                1,
                                "b"
                              ]
                            }
                          ]
                        ]
                      }
                    },
                    {
                      "ArrangeBy": {
                        "input": {
                          "Get": {
                            "id": {
                              "Global": {
                                "User": 2
                              }
                            },
                            "typ": {
                              "column_types": [
                                {
                                  "scalar_type": "Int32",
                                  "nullable": true
                                },
                                {
                                  "scalar_type": "Int32",
                                  "nullable": true
                                }
                              ],
                              "keys": []
                            },
                            "access_strategy": {
                              "Index": [
                                [
                                  {
                                    "User": 9
                                  },
                                  {
                                    "DeltaJoin": "Lookup"
                                  }
                                ],
                                [
                                  {
                                    "User": 10
                                  },
                                  {
                                    "DeltaJoin": "Lookup"
                                  }
                                ]
                              ]
                            }
                          }
                        },
                        "keys": [
                          [
                            {
                              "Column": [
                                0,
                                "c"
                              ]
                            }
                          ],
                          [
                            {
                              "Column": [
                                1,
                                "d"
                              ]
                            }
                          ]
                        ]
                      }
                    },
                    {
                      "ArrangeBy": {
                        "input": {
                          "Get": {
                            "id": {
                              "Global": {
                                "User": 3
                              }
                            },
                            "typ": {
                              "column_types": [
                                {
                                  "scalar_type": "Int32",
                                  "nullable": true
                                },
                                {
                                  "scalar_type": "Int32",
                                  "nullable": true
                                }
                              ],
                              "keys": []
                            },
                            "access_strategy": {
                              "Index": [
                                [
                                  {
                                    "User": 11
                                  },
                                  {
                                    "DeltaJoin": "Lookup"
                                  }
                                ]
                              ]
                            }
                          }
                        },
                        "keys": [
                          [
                            {
                              "Column": [
                                0,
                                "e"
                              ]
                            }
                          ]
                        ]
                      }
                    }
                  ],
                  "equivalences": [
                    [
                      {
                        "Column": [
                          1,
                          "b"
                        ]
                      },
                      {
                        "Column": [
                          2,
                          "c"
                        ]
                      }
                    ],
                    [
                      {
                        "Column": [
                          3,
                          "d"
                        ]
                      },
                      {
                        "Column": [
                          4,
                          "e"
                        ]
                      }
                    ]
                  ],
                  "implementation": {
                    "DeltaQuery": [
                      [
                        [
                          1,
                          [
                            {
                              "Column": [
                                0,
                                "c"
                              ]
                            }
                          ],
                          {
                            "V1": {
                              "unique_key": false,
                              "key_length": 1,
                              "arranged": true,
                              "cardinality": null,
                              "filters": {
                                "literal_equality": false,
                                "like": false,
                                "is_null": false,
                                "literal_inequality": 0,
                                "any_filter": false
                              },
                              "input": 1
                            }
                          }
                        ],
                        [
                          2,
                          [
                            {
                              "Column": [
                                0,
                                "e"
                              ]
                            }
                          ],
                          {
                            "V1": {
                              "unique_key": false,
                              "key_length": 1,
                              "arranged": true,
                              "cardinality": null,
                              "filters": {
                                "literal_equality": false,
                                "like": false,
                                "is_null": false,
                                "literal_inequality": 0,
                                "any_filter": false
                              },
                              "input": 2
                            }
                          }
                        ]
                      ],
                      [
                        [
                          0,
                          [
                            {
                              "Column": [
                                1,
                                "b"
                              ]
                            }
                          ],
                          {
                            "V1": {
                              "unique_key": false,
                              "key_length": 1,
                              "arranged": true,
                              "cardinality": null,
                              "filters": {
                                "literal_equality": false,
                                "like": false,
                                "is_null": false,
                                "literal_inequality": 0,
                                "any_filter": false
                              },
                              "input": 0
                            }
                          }
                        ],
                        [
                          2,
                          [
                            {
                              "Column": [
                                0,
                                "e"
                              ]
                            }
                          ],
                          {
                            "V1": {
                              "unique_key": false,
                              "key_length": 1,
                              "arranged": true,
                              "cardinality": null,
                              "filters": {
                                "literal_equality": false,
                                "like": false,
                                "is_null": false,
                                "literal_inequality": 0,
                                "any_filter": false
                              },
                              "input": 2
                            }
                          }
                        ]
                      ],
                      [
                        [
                          1,
                          [
                            {
                              "Column": [
                                1,
                                "d"
                              ]
                            }
                          ],
                          {
                            "V1": {
                              "unique_key": false,
                              "key_length": 1,
                              "arranged": true,
                              "cardinality": null,
                              "filters": {
                                "literal_equality": false,
                                "like": false,
                                "is_null": false,
                                "literal_inequality": 0,
                                "any_filter": false
                              },
                              "input": 1
                            }
                          }
                        ],
                        [
                          0,
                          [
                            {
                              "Column": [
                                1,
                                "b"
                              ]
                            }
                          ],
                          {
                            "V1": {
                              "unique_key": false,
                              "key_length": 1,
                              "arranged": true,
                              "cardinality": null,
                              "filters": {
                                "literal_equality": false,
                                "like": false,
                                "is_null": false,
                                "literal_inequality": 0,
                                "any_filter": false
                              },
                              "input": 0
                            }
                          }
                        ]
                      ]
                    ]
                  }
                }
              },
              "predicates": [
                {
                  "CallUnary": {
                    "func": {
                      "Not": null
                    },
                    "expr": {
                      "CallUnary": {
                        "func": {
                          "IsNull": null
                        },
                        "expr": {
                          "Column": [
                            1,
                            "b"
                          ]
                        }
                      }
                    }
                  }
                },
                {
                  "CallUnary": {
                    "func": {
                      "Not": null
                    },
                    "expr": {
                      "CallUnary": {
                        "func": {
                          "IsNull": null
                        },
                        "expr": {
                          "Column": [
                            3,
                            "d"
                          ]
                        }
                      }
                    }
                  }
                }
              ]
            }
          },
          "outputs": [
            0,
            1,
            1,
            3,
            3,
            5
          ]
        }
      }
    }
  ],
  "sources": []
}
EOF

statement ok
CREATE TABLE t2(s string, like_pat string, regex_pat string);

statement ok
INSERT INTO t2 VALUES ('abc', 'a%', 'a.*'), ('ABC', 'a%', 'a.*'), ('ccc', 'a%', 'a.*');

# Our Regex wrapper struct's serde is currently not serializing the Regex itself, but all info should be visible in
# the other fields.

query T multiline
EXPLAIN OPTIMIZED PLAN AS JSON FOR
SELECT s FROM t2 WHERE s ~ 'a.*';
----
{
  "plans": [
    {
      "id": "Explained Query",
      "plan": {
        "Project": {
          "input": {
            "Filter": {
              "input": {
                "Get": {
                  "id": {
                    "Global": {
                      "User": 13
                    }
                  },
                  "typ": {
                    "column_types": [
                      {
                        "scalar_type": "String",
                        "nullable": true
                      },
                      {
                        "scalar_type": "String",
                        "nullable": true
                      },
                      {
                        "scalar_type": "String",
                        "nullable": true
                      }
                    ],
                    "keys": []
                  },
                  "access_strategy": "Persist"
                }
              },
              "predicates": [
                {
                  "CallUnary": {
                    "func": {
                      "IsRegexpMatch": {
                        "pattern": "a.*",
                        "case_insensitive": false,
                        "dot_matches_new_line": true
                      }
                    },
                    "expr": {
                      "Column": [
                        0,
                        "s"
                      ]
                    }
                  }
                }
              ]
            }
          },
          "outputs": [
            0
          ]
        }
      }
    }
  ],
  "sources": [
    {
      "id": {
        "User": 13
      },
      "op": {
        "expressions": [],
        "predicates": [
          [
            1,
            {
              "CallUnary": {
                "func": {
                  "IsRegexpMatch": {
                    "pattern": "a.*",
                    "case_insensitive": false,
                    "dot_matches_new_line": true
                  }
                },
                "expr": {
                  "Column": [
                    0,
                    "s"
                  ]
                }
              }
            }
          ]
        ],
        "projection": [
          0,
          1,
          2
        ],
        "input_arity": 3
      }
    }
  ]
}
EOF