0

I always thought, and searching on Google/SO seems to confirm, that these do the same:

SELECT ... FROM a
INNER JOIN b ON (a.foo = b.foo AND a.bar = b.bar)

should be equivalent to

SELECT ... FROM a
INNER JOIN b USING (foo, bar)

i.e. USING is just syntactic sugar for the simple/general case where both tables have the same column names.

Now I'm observing different behavior with MariaDB on Debian 9 and 10, where 9 does what I expect and 10 doesn't.

Debian 9: mysqld Ver 10.1.26-MariaDB-0+deb9u1 for debian-linux-gnu on x86_64 (Debian 9.1)

Debian 10: mysqld Ver 10.3.17-MariaDB-0+deb10u1 for debian-linux-gnu on x86_64 (Debian 10)

Now I have a query that's supposed to list all constraints in all databases with their according update and delete rules.

This works on both:

SELECT a.CONSTRAINT_NAME, b.UPDATE_RULE, b.DELETE_RULE
FROM information_schema.KEY_COLUMN_USAGE a
INNER JOIN information_schema.REFERENTIAL_CONSTRAINTS b ON (
      a.CONSTRAINT_CATALOG = b.CONSTRAINT_CATALOG
  AND a.CONSTRAINT_SCHEMA = b.CONSTRAINT_SCHEMA
  AND a.CONSTRAINT_NAME = b.CONSTRAINT_NAME
)

This returns an empty set on Debian 10 but works on 9:

SELECT a.CONSTRAINT_NAME, b.UPDATE_RULE, b.DELETE_RULE
FROM information_schema.KEY_COLUMN_USAGE a
INNER JOIN information_schema.REFERENTIAL_CONSTRAINTS b
  USING (CONSTRAINT_CATALOG, CONSTRAINT_SCHEMA, CONSTRAINT_NAME)

EXPLAIN also looks identical for both queries:

+------+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------------------------------------------+
| id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                                                                                   |
+------+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------------------------------------------+
|    1 | SIMPLE      | a     | ALL  | NULL          | NULL | NULL    | NULL | NULL | Open_full_table; Scanned all databases                                                  |
|    1 | SIMPLE      | b     | ALL  | NULL          | NULL | NULL    | NULL | NULL | Using where; Open_full_table; Scanned all databases; Using join buffer (flat, BNL join) |
+------+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------------------------------------------+

What is going on here?

Simon
  • 178
  • 1
  • 10
  • 3
    You may have found a bug, in which case you might want to report it. – Tim Biegeleisen Oct 31 '19 at 15:30
  • Please in code questions give a [mre]--cut & paste & runnable code; example input (as initialization code) with desired & actual output (including verbatim error messages); tags & versions; clear specification & explanation. That includes the least code you can give that is code that you show is OK extended by code that you show is not OK. (Debugging fundamental.) For SQL that includes DBMS/product & DDL, which includes constraints & indexes & tabular formatted initialization. PS Although here--as with a bug report--the specification isn't needed. – philipxy Oct 31 '19 at 20:06
  • Your initial expectation as stated is wrong, because the 2nd FROM returns one less column. But the 2 DBMS+OS versions should return the same result given the same query. PS Referencing a USING column via a (possibly implicit) alias is a non-standared MySQL/MariaDB extension. – philipxy Nov 01 '19 at 02:31
  • Thanks. It was the end of a long work day so I was almost sure I got something wrong. – Simon Nov 04 '19 at 13:11
  • `USING` may give you fewer columns for `SELECT *`. For example, with `USING(id)` there is not need to show `id` twice. – Rick James Nov 05 '19 at 03:41

0 Answers0