[MDEV-5535] cannot reopen temporary table Created: 2014-01-16  Updated: 2019-05-14  Resolved: 2016-06-10

Status: Closed
Project: MariaDB Server
Component/s: Data Definition - Temporary
Fix Version/s: 10.2.1

Type: Task Priority: Major
Reporter: tem (Inactive) Assignee: Nirbhay Choubey (Inactive)
Resolution: Fixed Votes: 7
Labels: gsoc14, gsoc15

Issue Links:
Blocks
blocks CONJ-296 Support COM_MULTI Closed
Problem/Incident
causes MDEV-15867 Slave SQL: Error 'Table 't' is specif... Closed
Relates
relates to MDEV-6115 window functions as in the SQL standard Closed
relates to MDEV-8789 Implement non-recursive common table ... Closed
relates to MDEV-10216 Assertion `strcmp(share->unique_file_... Closed
relates to MDEV-11173 If a materialized VIEW is used multip... Open
Sprint: 10.2.0-1, 10.2.0-3, 10.2.0-5, 10.2.0-6, 10.2.0-7, 10.2.0-8, 10.2.0-9, 10.2.0-10, 10.2.0-11, 10.2.1-1, 10.2.1-3, 10.2.1-4, 5.5.50

 Description   

It is a well-known and very old MySQL/MariaDB limitation that temporary tables can only be used once in any query; for example, one cannot join a temporary table to itself. This task is about removing this limitation.

original bug report:

if I create temporary table a (id int); I cant select a1.* from a a1, a a2;

I understand, that http://bugs.mysql.com/bug.php?id=10327 is in "feature" state for 8(!!!) years.
But may be at least at mariadb someone to fix it.
It is not so extraordinary type of select.
I had bumped at almost all kind of problems about this, which are described about this bug. And any kind of rewrite selects/logic to avoid this error is a huge performance and logic issuse at hiload projects.
Can anyone tell me if it would be fixed? when? where?
Because I cant find any mention about it in mariadb.
Sorry if I missed something.

 Comments   
Comment by tem (Inactive) [ 2014-01-20 ]

any chance to be fixed in 5.x?

Comment by Sergei Golubchik [ 2014-01-27 ]

Sorry, no. Not even in 10.0 — what you're talking about is a fundamental limitation of the current MySQL and MariaDB codebase, it would require serious changes to lift it. We cannot do that in a GA version. Not even in RC or Beta, it still would be too risky.

Comment by Michael McClennen [ 2014-11-26 ]

Can somebody please update the Mariadb documentation to mention this limitation? At least the MySQL documentation <a href="https://dev.mysql.com/doc/refman/5.7/en/temporary-table-problems.html">is explicit about it</a>.

I have just added a vote for this issue: it is by far the MOST IMPORTANT issue for me in all of Mariadb. This has been a problem for far too long.

Comment by kapil chhajer [ 2015-03-11 ]

Hi,
I am Kapil Chhajer student from IIIT Hyderabad pursuing M.tech. CSE. I am comfortable with c, c++ and java. I want to work on this issue.

Please suggest me some related material that helps me to know more about it

Comment by Sergei Golubchik [ 2015-03-11 ]

Normal tables are opened like this:

  1. table definition is read, parsed into a TABLE_SHARE object
  2. TABLE_SHARE object is stored in the table definition cache
  3. a TABLE object is created from the TABLE_SHARE, and a table is opened in the storage engine

When the same table is opened again

  1. the TABLE_SHARE object is taken from the table definition cache
  2. a TABLE object is created from the TABLE_SHARE, and a table is opened in the storage engine

That is, there is always one TABLE_SHARE per table, but there can be many TABLE objects, if the table is opened many times, say, is joined to itself.

Temporary tables aren't cached in the table definition cache, and their TABLE_SHARE objects are, in a sense, part of the TABLE objects. That is, for temporary tables there can be only one TABLE per TABLE_SHARE. That's why temporary tables cannot be reopened.

The fix is to decouple TABLE_SHARE from TABLE for temporary tables and store them separately in two different lists in THD.

Comment by kapil chhajer [ 2015-03-12 ]

Hi,
Thanks Sergei for replying. I got a copy of source code with the help of
this link https://code.launchpad.net/maria. I want to look
the source code So I will get whole idea.

please suggest me where to start.

On Wed, Mar 11, 2015 at 8:17 PM, Sergei Golubchik (JIRA) <

Comment by Sergei Golubchik [ 2015-03-13 ]

start from https://github.com/mariadb/server
then see my previous comment, it has enough pointers for you to start exploring the source code

Comment by kapil chhajer [ 2015-03-13 ]

Thanks sergei

Comment by Sergei Petrunia [ 2015-11-26 ]

It looks like there are multiple other tasks that could benefit from this task. These are:

  • MDEV-6115: Window functions
  • MDEV-8789: Common Table Expressions
  • [No MDEV yet] Materialized view processing. Currently, if a materialized VIEW is used multiple times in the query, several temporary tables will be created and populated.
Comment by Sergei Golubchik [ 2015-11-26 ]

nirbhay_c, note the comment above. It means that one should be able to reopen internal temporary tables too.

Comment by Nirbhay Choubey (Inactive) [ 2016-03-08 ]

https://github.com/mariadb/server/tree/bb-10.2-mdev-5535

Comment by Nirbhay Choubey (Inactive) [ 2016-05-17 ]

Post-review patch :
http://lists.askmonty.org/pipermail/commits/2016-May/009365.html

Comment by Nirbhay Choubey (Inactive) [ 2016-05-27 ]

http://lists.askmonty.org/pipermail/commits/2016-May/009382.html
http://lists.askmonty.org/pipermail/commits/2016-May/009383.html

Comment by Nirbhay Choubey (Inactive) [ 2016-06-10 ]

Branch: 10.2
Commits : 78d4276..b2ae32a

Generated at Thu Feb 08 07:05:07 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.