[Maria-developers] ha_innobase::info_low() n_rows hack
In ha_innobase::info_low() there is following dirty hack: /* The MySQL optimizer seems to assume in a left join that n_rows is an accurate estimate if it is zero. Of course, it is not, since we do not have any locks on the rows yet at this phase. Since SHOW TABLE STATUS seems to call this function with the HA_STATUS_TIME flag set, while the left join optimizer does not set that flag, we add one to a zero value if the flag is not set. That way SHOW TABLE STATUS will show the best estimate, while the optimizer never sees the table empty. */ if (n_rows == 0 && !(flag & HA_STATUS_TIME)) { n_rows++; } It is very old (from 5.0 or earlier) and bug-prone. Because in ha_innobase::open(): info(HA_STATUS_NO_LOCK | HA_STATUS_VARIABLE | HA_STATUS_CONST); every opened empty table will be non-empty! I don't know what is the problem with join optimizer, but having storage engine to handle it seems not the right thing to do. Moreover, relying on HA_STATUS_TIME in this is definitely wrong. We can make join optimizer to ignore "0 rows case" for all storage engines. Is it big win from "1 row case" anyway? Or we can make new flag HA_JOIN_STAT and use it in make_join_statistics().
Hi, Aleksey! On Sep 11, Aleksey Midenkov wrote:
In ha_innobase::info_low() there is following dirty hack:
if (n_rows == 0 && !(flag & HA_STATUS_TIME)) { n_rows++; }
It is very old (from 5.0 or earlier) and bug-prone. Because in ha_innobase::open():
info(HA_STATUS_NO_LOCK | HA_STATUS_VARIABLE | HA_STATUS_CONST);
every opened empty table will be non-empty!
Is that a problem?
I don't know what is the problem with join optimizer,
if it notices an empty table it can return an empty result set right away.
having storage engine to handle it seems not the right thing to do.
Yes, storage engine's job in this case is to return an approximate (or exact) number of rows in a table. The API is documented in the handler.h: /* The number of records in the table. 0 - means the table has exactly 0 rows other - if (table_flags() & HA_STATS_RECORDS_IS_EXACT) the value is the exact number of records in the table else it is an estimate */ So, InnoDB returns "1 row" as an estimate for a "seemingly empty" table. Nothing's wrong with that.
Moreover, relying on HA_STATUS_TIME in this is definitely wrong. We can make join optimizer to ignore "0 rows case" for all storage engines. Is it big win from "1 row case" anyway? Or we can make new flag HA_JOIN_STAT and use it in make_join_statistics().
Yes, relying on HA_STATUS_TIME is a hack. A safer solution would be to return "1 row" for an "seemingly empty" table, unless it was proven to be empty, under a lock. Regards, Sergei Chief Architect MariaDB and security@mariadb.org
Hello! On Mon, Sep 11, 2017 at 4:37 PM, Sergei Golubchik <serg@mariadb.org> wrote:
Hi, Aleksey!
On Sep 11, Aleksey Midenkov wrote:
In ha_innobase::info_low() there is following dirty hack:
if (n_rows == 0 && !(flag & HA_STATUS_TIME)) { n_rows++; }
It is very old (from 5.0 or earlier) and bug-prone. Because in ha_innobase::open():
info(HA_STATUS_NO_LOCK | HA_STATUS_VARIABLE | HA_STATUS_CONST);
every opened empty table will be non-empty!
Is that a problem?
Don't you find it at least misleading? ha_innobase::open() is exclusive (process-wide) operation and it lies about non-empty table.
Hi, Aleksey! On Sep 11, Aleksey Midenkov wrote:
On Mon, Sep 11, 2017 at 4:37 PM, Sergei Golubchik <serg@mariadb.org> wrote:
Hi, Aleksey!
On Sep 11, Aleksey Midenkov wrote:
In ha_innobase::info_low() there is following dirty hack:
if (n_rows == 0 && !(flag & HA_STATUS_TIME)) { n_rows++; }
It is very old (from 5.0 or earlier) and bug-prone. Because in ha_innobase::open():
info(HA_STATUS_NO_LOCK | HA_STATUS_VARIABLE | HA_STATUS_CONST);
every opened empty table will be non-empty!
Is that a problem?
Don't you find it at least misleading? ha_innobase::open() is exclusive (process-wide) operation and it lies about non-empty table.
ha_innobase::open() is not the only place where the ::info() method is called. I'd say that it'd be correct to return n_rows == 0 if the table is locked. Regards, Sergei Chief Architect MariaDB and security@mariadb.org
participants (2)
-
Aleksey Midenkov -
Sergei Golubchik