SQL query joining on taxonomy tables not returning what I would expect

As @Mark Kaplun mentioned, the WP API can reveal the the queries performed

Below is a simple query using the API in a template to see what’s going on:

<?php
/*
 * Template Name: Troubleshooting
 */

$args = array(
    'post_type' => 'post',
    'tax_query' => array(
        'relation' => 'AND',
        array(
            'taxonomy' => 'category',
            'field'    => 'slug',
            'terms'    => array( 'uncategorized' ),
        ),
        array(
            'taxonomy' => 'post_tag',
            'field'    => 'slug',
            'terms'    => array( 'red' ),
        ),
    ),
);
$query = new WP_Query( $args ); 
echo $query->request;