QGIS mbtiles vs xyz tiles export inconsistency

Question

I want to use mbtiles in a project so I am generating mbtiles for a selected area of osm.

Extent (xmin, xmax, ymin, ymax):

463297.84985694464,538040.0735187809,6769150.388750425,6834675.317430764 [EPSG:3857]

I was a bit surprised to find out that I wasn't able to find a record for these values

z = 14
y = 5412
x = 8390

Query I am running:

select tile_data from tiles where zoom_level=14 and tile_column=8390 and tile_row=5412;

has no results.

However

select tile_data from tiles where zoom_level=14 and tile_column=8390;

has 28 results, where tile_row ranges from 10960 through 10987.

So I thought I would take xyz tile exports for the same area and settings to see if it has a tile for the given xyz and IT DOES!

I don't understand how this is possible! Is there something I am missing while exporting mbtiles? OR is this a bug in QGIS?

Answer 1

The default behaviour of QGIS mbtiles export is to use TMS scheme instead of xyz scheme. Hence the discrepancy. I read this nice post about xyz and tms which helped me in doing the conversion. Simply converting the y value to pow(2, z) - y - 1 does the job. Here's the QGIS issue for anyone interested in following through.