[MDEV-12401] JOIN on two same columns make server search indefintely Created: 2017-03-29  Updated: 2017-03-31  Resolved: 2017-03-30

Status: Closed
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 10.0.29-galera
Fix Version/s: N/A

Type: Bug Priority: Minor
Reporter: David Côté-Tremblay Assignee: Sergei Golubchik
Resolution: Not a Bug Votes: 0
Labels: None
Environment:

Ubuntu Server 16.04


 Description   

It was caused directly from a programming mistake, but I think that it can also be considered as a bug.

I do a mistake like this in a JOIN :

SELECT categories.*
 
FROM categories
 
JOIN products ON products.category_id = products.category_id

I don't know if it's a bug, but I think that the server should throw an error when a query try to join comparing two table/columns pairs that are the same causing the server to indefinitely run.

Thank you very much and have a great day !

 Comments   
Comment by Sergei Golubchik [ 2017-03-30 ]

This is not a bug. The server does not return an error, because it's a completely valid SQL query.

And it doesn't execute indefinitely. You're doing a Cartesian product of two big tables, it takes a while, but it'll finish eventually.

If you want to prevent mistakes like this in the future, you can set

SET max_statement_time=100

to tell the server to abort any query that takes longer than 100 seconds. See https://mariadb.com/kb/en/mariadb/aborting-statements/

Comment by David Côté-Tremblay [ 2017-03-31 ]

I am not sure by what you mean a "cartesian product". I think that does seems interesting, but I don't understand the purpose of joining same columns together. Do you have an example to show me ?

Thank you very much !

Comment by Daniel Black [ 2017-03-31 ]

https://en.wikipedia.org/wiki/Join_(SQL)#Cross_join

Comment by David Côté-Tremblay [ 2017-03-31 ]

Wow very interesting I didn't know that this exists. It does really seems useful.

I would get an example of this kind of usage :

JOIN products ON products.category_id = products.category_id

Thank you !

Comment by Daniel Black [ 2017-03-31 ]

There is no need for an almost always true join criteria to get a cross join. Explicit "CROSS JOIN" is useful to show where this is intended rather than the accidental case you discovered. Please use irc or mariadb-discuss mail lists to assist with SQL usage. JIRA really is a bugs/feature requests only medium.

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