SELECT SQL_CALC_FOUND_ROWS DISTINCT `C`.`services`,`C`.`category_id`,`CD`.`category`,`COM`.`company`,`CUR`.`price` AS `currency_rate`,IF(`HP`.`created_at`, MAX(`HP`.`created_at`), `P`.`timestamp`) AS `price_update_time`,`P`.`amount`,`P`.`city_id`,`P`.`brand_id`,`P`.`company_id`,`P`.`city_place`,`P`.`conditions`,`P`.`guarantee_id`,`P`.`type_of_sale`,`P`.`product_code`,`P`.`product_id`,`P`.`status`,`P`.`year`,`P`.`uniqueSale`,`P`.`is_confiscated`,`P`.`updated_timestamp`,`PC`.`category_id` AS `main_category`,`PD`.`full_description`,`PD`.`short_description`,`PD`.`leasingCount`,`PD`.`leasingPrice`,`PD`.`product`,`PP`.`currency`,`PP`.`discount_percent`,`PP`.`price`,`PP`.`recycling_price_rub`,((PP.price * COALESCE(CUR.price, 1)) * (1 - PP.discount_percent / 100) + PP.recycling_price_rub) AS total_price,CASE WHEN `GC`.`compare_id` IS NOT NULL THEN "Y" ELSE "N" END AS `compare`,CASE WHEN `GW`.`wishlist_id` IS NOT NULL THEN "Y" ELSE "N" END AS `wish`,IF(`CITY`.`city_id` IS NULL, 0, 1) as city_priority,CITY.address,(select count(*) from `cscart_product_stocks` where product_id = P.product_id) as stockCount,COALESCE(FILE.path, "") AS image,COALESCE(IMG.alt, "") AS image_alt FROM `cscart_products` AS `P` LEFT JOIN `cscart_guest__wishlist` AS `GW` ON `P`.`product_id` = `GW`.`product_id` AND `GW`.`guest_id` = 0 LEFT JOIN `cscart_guest__compare` AS `GC` ON `P`.`product_id` = `GC`.`product_id` AND `GC`.`guest_id` = 0 LEFT JOIN `cscart_product_stocks` AS `PST` ON `P`.`product_id` = `PST`.`product_id` LEFT JOIN `cscart_company_stocks` AS `CST` ON `PST`.`stock_id` = `CST`.`stock_id` LEFT JOIN `cscart_custom_city` AS `CITY` ON `CITY`.`fias_id` = `CST`.`fias_id` LEFT JOIN `cscart_product_prices_history` AS `HP` ON `HP`.`product_id` = `P`.`product_id` LEFT JOIN `cscart_products__images` AS `IMG` ON `P`.`product_id` = `IMG`.`product_id` LEFT JOIN `files` AS `FILE` ON `FILE`.`id` = `IMG`.`watermark_small_file_id` LEFT JOIN `cscart_companies` AS `COM` ON `COM`.`company_id` = `P`.`company_id` INNER JOIN `cscart_products_categories` AS `PC` ON `PC`.`product_id` = `P`.`product_id` AND `PC`.`link_type` = 'M' INNER JOIN `cscart_categories` AS `C` ON `C`.`category_id` = `PC`.`category_id` INNER JOIN `cscart_category_descriptions` AS `CD` ON `C`.`category_id` = `CD`.`category_id` LEFT JOIN `cscart_product_prices` AS `PP` ON `PP`.`product_id` = `P`.`product_id` LEFT JOIN `cscart_product_descriptions` AS `PD` ON `PD`.`product_id` = `P`.`product_id` AND `PD`.`lang_code` = 'ru' LEFT JOIN `cscart_currencies` AS `CUR` ON `PP`.`currency` = `CUR`.`currency_code` WHERE 1 AND `P`.`product_id` IN (14693, 27392, 27394, 32429, 32436) AND `COM`.`status` = 'A' GROUP BY `P`.`product_id` ORDER BY city_priority DESC,updated_timestamp DESC 

Query is invalid

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "15010.16"
    },
    "ordering_operation": {
      "using_filesort": true,
      "grouping_operation": {
        "using_temporary_table": true,
        "using_filesort": false,
        "nested_loop": [
          {
            "table": {
              "table_name": "PC",
              "access_type": "range",
              "possible_keys": [
                "PRIMARY",
                "link_type",
                "pt",
                "idx_pc_product_id_type"
              ],
              "key": "pt",
              "used_key_parts": [
                "product_id",
                "link_type"
              ],
              "key_length": "6",
              "rows_examined_per_scan": 5,
              "rows_produced_per_join": 5,
              "filtered": "100.00",
              "using_index": true,
              "cost_info": {
                "read_cost": "0.79",
                "eval_cost": "0.50",
                "prefix_cost": "1.29",
                "data_read_per_join": "80"
              },
              "used_columns": [
                "product_id",
                "category_id",
                "link_type"
              ],
              "attached_condition": "((`dev2`.`PC`.`product_id` in (14693,27392,27394,32429,32436)) and (`dev2`.`PC`.`link_type` = 'M'))"
            }
          },
          {
            "table": {
              "table_name": "P",
              "access_type": "eq_ref",
              "possible_keys": [
                "PRIMARY",
                "age_verification",
                "status",
                "idx_parent_product_id",
                "cscart_products_brand_id_index",
                "cscart_products_conditions_index",
                "cscart_products_real_company_id_index",
                "cscart_products_product_id_index",
                "idx_status"
              ],
              "key": "PRIMARY",
              "used_key_parts": [
                "product_id"
              ],
              "key_length": "3",
              "ref": [
                "dev2.PC.product_id"
              ],
              "rows_examined_per_scan": 1,
              "rows_produced_per_join": 5,
              "filtered": "100.00",
              "cost_info": {
                "read_cost": "1.25",
                "eval_cost": "0.50",
                "prefix_cost": "3.04",
                "data_read_per_join": "37K"
              },
              "used_columns": [
                "product_id",
                "product_code",
                "status",
                "company_id",
                "amount",
                "timestamp",
                "updated_timestamp",
                "guarantee_id",
                "conditions",
                "year",
                "city_place",
                "city_id",
                "brand_id",
                "uniqueSale",
                "is_confiscated",
                "type_of_sale"
              ]
            }
          },
          {
            "table": {
              "table_name": "CD",
              "access_type": "ref",
              "possible_keys": [
                "PRIMARY"
              ],
              "key": "PRIMARY",
              "used_key_parts": [
                "category_id"
              ],
              "key_length": "3",
              "ref": [
                "dev2.PC.category_id"
              ],
              "rows_examined_per_scan": 1,
              "rows_produced_per_join": 5,
              "filtered": "100.00",
              "cost_info": {
                "read_cost": "1.25",
                "eval_cost": "0.50",
                "prefix_cost": "4.79",
                "data_read_per_join": "21K"
              },
              "used_columns": [
                "category_id",
                "lang_code",
                "category"
              ]
            }
          },
          {
            "table": {
              "table_name": "COM",
              "access_type": "eq_ref",
              "possible_keys": [
                "PRIMARY"
              ],
              "key": "PRIMARY",
              "used_key_parts": [
                "company_id"
              ],
              "key_length": "4",
              "ref": [
                "dev2.P.company_id"
              ],
              "rows_examined_per_scan": 1,
              "rows_produced_per_join": 0,
              "filtered": "10.00",
              "cost_info": {
                "read_cost": "1.25",
                "eval_cost": "0.05",
                "prefix_cost": "6.54",
                "data_read_per_join": "3K"
              },
              "used_columns": [
                "company_id",
                "status",
                "company"
              ],
              "attached_condition": "(`dev2`.`COM`.`status` = 'A')"
            }
          },
          {
            "table": {
              "table_name": "C",
              "access_type": "eq_ref",
              "possible_keys": [
                "PRIMARY",
                "p_category_id"
              ],
              "key": "PRIMARY",
              "used_key_parts": [
                "category_id"
              ],
              "key_length": "3",
              "ref": [
                "dev2.PC.category_id"
              ],
              "rows_examined_per_scan": 1,
              "rows_produced_per_join": 0,
              "filtered": "100.00",
              "cost_info": {
                "read_cost": "0.31",
                "eval_cost": "0.05",
                "prefix_cost": "6.90",
                "data_read_per_join": "5K"
              },
              "used_columns": [
                "category_id",
                "services"
              ]
            }
          },
          {
            "table": {
              "table_name": "PP",
              "access_type": "ref",
              "possible_keys": [
                "usergroup",
                "product_id"
              ],
              "key": "usergroup",
              "used_key_parts": [
                "product_id"
              ],
              "key_length": "3",
              "ref": [
                "dev2.PC.product_id"
              ],
              "rows_examined_per_scan": 1,
              "rows_produced_per_join": 0,
              "filtered": "100.00",
              "cost_info": {
                "read_cost": "0.14",
                "eval_cost": "0.05",
                "prefix_cost": "7.09",
                "data_read_per_join": "124"
              },
              "used_columns": [
                "product_id",
                "price",
                "currency",
                "recycling_price_rub",
                "discount_percent",
                "lower_limit",
                "usergroup_id"
              ]
            }
          },
          {
            "table": {
              "table_name": "PD",
              "access_type": "eq_ref",
              "possible_keys": [
                "PRIMARY",
                "product_id"
              ],
              "key": "PRIMARY",
              "used_key_parts": [
                "product_id",
                "lang_code"
              ],
              "key_length": "9",
              "ref": [
                "dev2.PC.product_id",
                "const"
              ],
              "rows_examined_per_scan": 1,
              "rows_produced_per_join": 0,
              "filtered": "100.00",
              "cost_info": {
                "read_cost": "0.42",
                "eval_cost": "0.05",
                "prefix_cost": "7.56",
                "data_read_per_join": "2K"
              },
              "used_columns": [
                "product_id",
                "lang_code",
                "product",
                "short_description",
                "full_description",
                "leasingCount",
                "leasingPrice"
              ]
            }
          },
          {
            "table": {
              "table_name": "CUR",
              "access_type": "eq_ref",
              "possible_keys": [
                "currency_code"
              ],
              "key": "currency_code",
              "used_key_parts": [
                "currency_code"
              ],
              "key_length": "12",
              "ref": [
                "dev2.PP.currency"
              ],
              "rows_examined_per_scan": 1,
              "rows_produced_per_join": 0,
              "filtered": "100.00",
              "cost_info": {
                "read_cost": "0.13",
                "eval_cost": "0.05",
                "prefix_cost": "7.73",
                "data_read_per_join": "48"
              },
              "used_columns": [
                "currency_id",
                "currency_code",
                "price"
              ]
            }
          },
          {
            "table": {
              "table_name": "GW",
              "access_type": "ALL",
              "rows_examined_per_scan": 48,
              "rows_produced_per_join": 24,
              "filtered": "100.00",
              "using_join_buffer": "hash join",
              "cost_info": {
                "read_cost": "0.25",
                "eval_cost": "2.40",
                "prefix_cost": "10.38",
                "data_read_per_join": "384"
              },
              "used_columns": [
                "wishlist_id",
                "product_id",
                "guest_id"
              ],
              "attached_condition": "<if>(is_not_null_compl(GW), ((`dev2`.`GW`.`guest_id` = 0) and (`dev2`.`PC`.`product_id` = `dev2`.`GW`.`product_id`)), true)"
            }
          },
          {
            "table": {
              "table_name": "GC",
              "access_type": "ALL",
              "rows_examined_per_scan": 88,
              "rows_produced_per_join": 2112,
              "filtered": "100.00",
              "using_join_buffer": "hash join",
              "cost_info": {
                "read_cost": "0.31",
                "eval_cost": "211.20",
                "prefix_cost": "221.89",
                "data_read_per_join": "33K"
              },
              "used_columns": [
                "compare_id",
                "guest_id",
                "product_id"
              ],
              "attached_condition": "<if>(is_not_null_compl(GC), ((`dev2`.`GC`.`guest_id` = 0) and (`dev2`.`PC`.`product_id` = `dev2`.`GC`.`product_id`)), true)"
            }
          },
          {
            "table": {
              "table_name": "PST",
              "access_type": "ref",
              "possible_keys": [
                "cscart_product_stocks_pk"
              ],
              "key": "cscart_product_stocks_pk",
              "used_key_parts": [
                "product_id"
              ],
              "key_length": "4",
              "ref": [
                "dev2.PC.product_id"
              ],
              "rows_examined_per_scan": 1,
              "rows_produced_per_join": 2172,
              "filtered": "100.00",
              "using_index": true,
              "cost_info": {
                "read_cost": "957.05",
                "eval_cost": "217.27",
                "prefix_cost": "1396.21",
                "data_read_per_join": "50K"
              },
              "used_columns": [
                "product_id",
                "stock_id"
              ],
              "attached_condition": "<if>(is_not_null_compl(PST), (`dev2`.`PC`.`product_id` = `dev2`.`PST`.`product_id`), true)"
            }
          },
          {
            "table": {
              "table_name": "CST",
              "access_type": "eq_ref",
              "possible_keys": [
                "PRIMARY"
              ],
              "key": "PRIMARY",
              "used_key_parts": [
                "stock_id"
              ],
              "key_length": "4",
              "ref": [
                "dev2.PST.stock_id"
              ],
              "rows_examined_per_scan": 1,
              "rows_produced_per_join": 2172,
              "filtered": "100.00",
              "cost_info": {
                "read_cost": "0.13",
                "eval_cost": "217.27",
                "prefix_cost": "1613.61",
                "data_read_per_join": "848K"
              },
              "used_columns": [
                "stock_id",
                "fias_id"
              ]
            }
          },
          {
            "table": {
              "table_name": "CITY",
              "access_type": "ref",
              "possible_keys": [
                "idx_custom_city_fias_id"
              ],
              "key": "idx_custom_city_fias_id",
              "used_key_parts": [
                "fias_id"
              ],
              "key_length": "386",
              "ref": [
                "dev2.CST.fias_id"
              ],
              "rows_examined_per_scan": 1,
              "rows_produced_per_join": 2172,
              "filtered": "100.00",
              "cost_info": {
                "read_cost": "543.17",
                "eval_cost": "217.27",
                "prefix_cost": "2374.05",
                "data_read_per_join": "11M"
              },
              "used_columns": [
                "city_id",
                "address",
                "fias_id"
              ]
            }
          },
          {
            "table": {
              "table_name": "HP",
              "access_type": "ref",
              "possible_keys": [
                "idx_hp_product_id"
              ],
              "key": "idx_hp_product_id",
              "used_key_parts": [
                "product_id"
              ],
              "key_length": "3",
              "ref": [
                "dev2.PC.product_id"
              ],
              "rows_examined_per_scan": 2,
              "rows_produced_per_join": 5379,
              "filtered": "100.00",
              "cost_info": {
                "read_cost": "2582.34",
                "eval_cost": "537.99",
                "prefix_cost": "5494.37",
                "data_read_per_join": "1M"
              },
              "used_columns": [
                "id",
                "product_id",
                "created_at"
              ]
            }
          },
          {
            "table": {
              "table_name": "IMG",
              "access_type": "ref",
              "possible_keys": [
                "product_id"
              ],
              "key": "product_id",
              "used_key_parts": [
                "product_id"
              ],
              "key_length": "3",
              "ref": [
                "dev2.PC.product_id"
              ],
              "rows_examined_per_scan": 3,
              "rows_produced_per_join": 21143,
              "filtered": "100.00",
              "cost_info": {
                "read_cost": "5285.87",
                "eval_cost": "2114.35",
                "prefix_cost": "12894.59",
                "data_read_per_join": "660K"
              },
              "used_columns": [
                "product_id",
                "watermark_small_file_id",
                "alt"
              ]
            }
          },
          {
            "table": {
              "table_name": "FILE",
              "access_type": "eq_ref",
              "possible_keys": [
                "PRIMARY"
              ],
              "key": "PRIMARY",
              "used_key_parts": [
                "id"
              ],
              "key_length": "4",
              "ref": [
                "dev2.IMG.watermark_small_file_id"
              ],
              "rows_examined_per_scan": 1,
              "rows_produced_per_join": 21143,
              "filtered": "100.00",
              "cost_info": {
                "read_cost": "1.23",
                "eval_cost": "2114.35",
                "prefix_cost": "15010.16",
                "data_read_per_join": "76M"
              },
              "used_columns": [
                "id",
                "path"
              ]
            }
          }
        ],
        "select_list_subqueries": [
          {
            "dependent": true,
            "cacheable": false,
            "query_block": {
              "select_id": 2,
              "cost_info": {
                "query_cost": "0.56"
              },
              "table": {
                "table_name": "cscart_product_stocks",
                "access_type": "ref",
                "possible_keys": [
                  "cscart_product_stocks_pk"
                ],
                "key": "cscart_product_stocks_pk",
                "used_key_parts": [
                  "product_id"
                ],
                "key_length": "4",
                "ref": [
                  "func"
                ],
                "rows_examined_per_scan": 1,
                "rows_produced_per_join": 1,
                "filtered": "100.00",
                "using_index": true,
                "cost_info": {
                  "read_cost": "0.45",
                  "eval_cost": "0.10",
                  "prefix_cost": "0.56",
                  "data_read_per_join": "24"
                },
                "used_columns": [
                  "product_id"
                ],
                "attached_condition": "(`dev2`.`cscart_product_stocks`.`product_id` = `dev2`.`P`.`product_id`)"
              }
            }
          }
        ]
      }
    }
  }
}