mysqlsqlvitess

How to select data depending on data from the same table with custom field in SELECT?

I have a query to fetch closest airports to the airport:

SELECT *,
(
  6371 *
  acos(cos(radians(55.966324)) *
  cos(radians(latitude)) *
  cos(radians(longitude) -
  radians(37.416573)) +
  sin(radians(55.966324)) *
  sin(radians(latitude)))
) AS distance
FROM flautru.Airport
HAVING distance < 250
ORDER BY distance;

Where 55.966324 and 37.416573 are latitude and longitude of the airport I'm searching neighbour airports for. But in order to get those coordinates I would need to fetch that airport data first in a separate query which would slow things down. I would like to make this operation in a single query knowing only airport code, so I wrote the following SQL query:

SELECT neighbour.*,
(
   6371 *
   acos(cos(radians(main.latitude)) *
   cos(radians(latitude)) *
   cos(radians(longitude) -
   radians(main.longitude)) +
   sin(radians(main.latitude)) *
   sin(radians(latitude)))
) AS distance
FROM flautru.Airport neighbour
JOIN flautru.Airport main ON neighbour.code <> main.code
WHERE main.code = 'JFK'
HAVING distance < 250
ORDER BY distance;

But it doesn't work. My database only shows me the error:

Error Code: 1052. target: flautru.-.primary: vttablet: rpc error: code = InvalidArgument desc = (errno 1052) (sqlstate 23000) (CallerID: unsecure_grpc_client): Sql: "select neighbour.*, :vtg1 * acos(cos(radians(main.latitude)) * cos(radians(latitude)) * cos(radians(longitude) - radians(main.longitude)) + sin(radians(main.latitude)) * sin(radians(latitude))) as distance from Airport as neighbour join Airport as main on neighbour.`code` != main.`code` where main.`code` = :vtg2 having distance < :vtg3 order by distance asc", B

What am I doing wrong with my query? MySQL Workbench seems to be OK with syntax.

Solution

  • Error 1052 in mysql indicates that a reference is ambiguous (see https://dev.mysql.com/doc/mysql-errors/5.7/en/server-error-reference.html) Qualify all columns in your expression with the correct alias, like:

    acos(cos(radians(main.latitude)) *
   cos(radians(neighbour.latitude)) *
   cos(radians(neighbourlongitude) -
   radians(main.longitude)) +
   sin(radians(main.latitude)) *
   sin(radians(neighbour.latitude)))

    I