[MDEV-19990] JOIN USING not behaving like 'equals' - Jira

Nuno created issue - 2019-07-08 23:35

Nuno made changes - 2019-07-08 23:36

Field	Original Value	New Value
Description	(I have no idea what the reason can be, and which Component this belongs to.) However, since 10.3 (I believe), the following query returns EMPTY set: SELECT k.TABLE_NAME, k.COLUMN_NAME, k.CONSTRAINT_NAME, k.REFERENCED_TABLE_NAME, k.REFERENCED_COLUMN_NAME, r.DELETE_RULE, r.UPDATE_RULE FROM information_schema.REFERENTIAL_CONSTRAINTS r JOIN information_schema.KEY_COLUMN_USAGE k USING (CONSTRAINT_CATALOG, CONSTRAINT_SCHEMA, CONSTRAINT_NAME) But if I expand the USING into 'equals', it returns proper results: SELECT k.TABLE_NAME, k.COLUMN_NAME, k.CONSTRAINT_NAME, k.REFERENCED_TABLE_NAME, k.REFERENCED_COLUMN_NAME, r.DELETE_RULE, r.UPDATE_RULE FROM information_schema.REFERENTIAL_CONSTRAINTS r JOIN information_schema.KEY_COLUMN_USAGE k on r.CONSTRAINT_CATALOG = k.CONSTRAINT_CATALOG and r.CONSTRAINT_SCHEMA = k.CONSTRAINT_SCHEMA and r.CONSTRAINT_NAME = k.CONSTRAINT_NAME Tried with "root" and local user. Before, it was always returning results with USING. I would expect that USING works exactly the same with 'equals' ?	(I have no idea what the reason can be, and which Component this belongs to.) However, since 10.3 (I believe), the following query returns EMPTY set: {{SELECT k.TABLE_NAME, k.COLUMN_NAME, k.CONSTRAINT_NAME, k.REFERENCED_TABLE_NAME, k.REFERENCED_COLUMN_NAME, r.DELETE_RULE, r.UPDATE_RULE FROM information_schema.REFERENTIAL_CONSTRAINTS r JOIN information_schema.KEY_COLUMN_USAGE k USING (CONSTRAINT_CATALOG, CONSTRAINT_SCHEMA, CONSTRAINT_NAME)}} But if I expand the USING into 'equals', it returns proper results: {{SELECT k.TABLE_NAME, k.COLUMN_NAME, k.CONSTRAINT_NAME, k.REFERENCED_TABLE_NAME, k.REFERENCED_COLUMN_NAME, r.DELETE_RULE, r.UPDATE_RULE FROM information_schema.REFERENTIAL_CONSTRAINTS r JOIN information_schema.KEY_COLUMN_USAGE k on r.CONSTRAINT_CATALOG = k.CONSTRAINT_CATALOG and r.CONSTRAINT_SCHEMA = k.CONSTRAINT_SCHEMA and r.CONSTRAINT_NAME = k.CONSTRAINT_NAME}} Tried with "root" and local user. Before, it was always returning results with USING. I would expect that USING works exactly the same with 'equals' ?

Nuno made changes - 2019-07-08 23:36

Description

(I have no idea what the reason can be, and which Component this belongs to.)

However, since 10.3 (I believe), the following query returns EMPTY set:

{{SELECT k.TABLE_NAME, k.COLUMN_NAME, k.CONSTRAINT_NAME, k.REFERENCED_TABLE_NAME, k.REFERENCED_COLUMN_NAME, r.DELETE_RULE, r.UPDATE_RULE FROM information_schema.REFERENTIAL_CONSTRAINTS r JOIN information_schema.KEY_COLUMN_USAGE k USING (CONSTRAINT_CATALOG, CONSTRAINT_SCHEMA, CONSTRAINT_NAME)}}

But if I expand the USING into 'equals', it returns proper results:

{{SELECT k.TABLE_NAME, k.COLUMN_NAME, k.CONSTRAINT_NAME, k.REFERENCED_TABLE_NAME, k.REFERENCED_COLUMN_NAME, r.DELETE_RULE, r.UPDATE_RULE FROM information_schema.REFERENTIAL_CONSTRAINTS r JOIN information_schema.KEY_COLUMN_USAGE k
on r.CONSTRAINT_CATALOG = k.CONSTRAINT_CATALOG
and r.CONSTRAINT_SCHEMA = k.CONSTRAINT_SCHEMA
and r.CONSTRAINT_NAME = k.CONSTRAINT_NAME}}

Tried with "root" and local user.
Before, it was always returning results with USING.

I would expect that USING works exactly the same with 'equals' ?

(I have no idea what the reason can be, and which Component this belongs to.)

However, since 10.3 (I believe), the following query returns EMPTY set:

{{SELECT k.TABLE_NAME, k.COLUMN_NAME, k.CONSTRAINT_NAME, k.REFERENCED_TABLE_NAME, k.REFERENCED_COLUMN_NAME, r.DELETE_RULE, r.UPDATE_RULE FROM information_schema.REFERENTIAL_CONSTRAINTS r JOIN information_schema.KEY_COLUMN_USAGE k USING (CONSTRAINT_CATALOG, CONSTRAINT_SCHEMA, CONSTRAINT_NAME)}}

But if I expand the USING into 'equals', it returns proper results:

{{SELECT k.TABLE_NAME, k.COLUMN_NAME, k.CONSTRAINT_NAME, k.REFERENCED_TABLE_NAME, k.REFERENCED_COLUMN_NAME, r.DELETE_RULE, r.UPDATE_RULE FROM information_schema.REFERENTIAL_CONSTRAINTS r JOIN information_schema.KEY_COLUMN_USAGE k on r.CONSTRAINT_CATALOG = k.CONSTRAINT_CATALOG and r.CONSTRAINT_SCHEMA = k.CONSTRAINT_SCHEMA and r.CONSTRAINT_NAME = k.CONSTRAINT_NAME}}

Tried with "root" and local user.
Before, it was always returning results with USING.

I would expect that USING works exactly the same with 'equals' ?

Sergei Golubchik made changes - 2019-07-09 07:58

Issue Type

Task [ 3 ]

Bug [ 1 ]

Sergei Golubchik made changes - 2019-07-09 07:58

Affects Version/s

10.3 [ 22126 ]

Sergei Golubchik made changes - 2019-07-09 07:58

Labels

regression

Sergei Golubchik made changes - 2019-07-09 07:59

Description

(I have no idea what the reason can be, and which Component this belongs to.)

However, since 10.3 (I believe), the following query returns EMPTY set:

{{SELECT k.TABLE_NAME, k.COLUMN_NAME, k.CONSTRAINT_NAME, k.REFERENCED_TABLE_NAME, k.REFERENCED_COLUMN_NAME, r.DELETE_RULE, r.UPDATE_RULE FROM information_schema.REFERENTIAL_CONSTRAINTS r JOIN information_schema.KEY_COLUMN_USAGE k USING (CONSTRAINT_CATALOG, CONSTRAINT_SCHEMA, CONSTRAINT_NAME)}}

But if I expand the USING into 'equals', it returns proper results:

{{SELECT k.TABLE_NAME, k.COLUMN_NAME, k.CONSTRAINT_NAME, k.REFERENCED_TABLE_NAME, k.REFERENCED_COLUMN_NAME, r.DELETE_RULE, r.UPDATE_RULE FROM information_schema.REFERENTIAL_CONSTRAINTS r JOIN information_schema.KEY_COLUMN_USAGE k on r.CONSTRAINT_CATALOG = k.CONSTRAINT_CATALOG and r.CONSTRAINT_SCHEMA = k.CONSTRAINT_SCHEMA and r.CONSTRAINT_NAME = k.CONSTRAINT_NAME}}

Tried with "root" and local user.
Before, it was always returning results with USING.

I would expect that USING works exactly the same with 'equals' ?

(I have no idea what the reason can be, and which Component this belongs to.)

However, since 10.3 (I believe), the following query returns EMPTY set:

{code:sql}
SELECT k.TABLE_NAME, k.COLUMN_NAME, k.CONSTRAINT_NAME, k.REFERENCED_TABLE_NAME, k.REFERENCED_COLUMN_NAME, r.DELETE_RULE, r.UPDATE_RULE FROM information_schema.REFERENTIAL_CONSTRAINTS r JOIN information_schema.KEY_COLUMN_USAGE k USING (CONSTRAINT_CATALOG, CONSTRAINT_SCHEMA, CONSTRAINT_NAME)
{code}

But if I expand the USING into 'equals', it returns proper results:

{code:sql}
SELECT k.TABLE_NAME, k.COLUMN_NAME, k.CONSTRAINT_NAME, k.REFERENCED_TABLE_NAME, k.REFERENCED_COLUMN_NAME, r.DELETE_RULE, r.UPDATE_RULE FROM information_schema.REFERENTIAL_CONSTRAINTS r JOIN information_schema.KEY_COLUMN_USAGE k on r.CONSTRAINT_CATALOG = k.CONSTRAINT_CATALOG and r.CONSTRAINT_SCHEMA = k.CONSTRAINT_SCHEMA and r.CONSTRAINT_NAME = k.CONSTRAINT_NAME
{code}

Tried with "root" and local user.
Before, it was always returning results with USING.

I would expect that USING works exactly the same with 'equals' ?

Sergei Golubchik made changes - 2019-07-09 08:00

Component/s

Optimizer [ 10200 ]

Nuno made changes - 2019-07-09 08:25

Description

(I have no idea what the reason can be, and which Component this belongs to.)

However, since 10.3 (I believe), the following query returns EMPTY set:

{code:sql}
SELECT k.TABLE_NAME, k.COLUMN_NAME, k.CONSTRAINT_NAME, k.REFERENCED_TABLE_NAME, k.REFERENCED_COLUMN_NAME, r.DELETE_RULE, r.UPDATE_RULE FROM information_schema.REFERENTIAL_CONSTRAINTS r JOIN information_schema.KEY_COLUMN_USAGE k USING (CONSTRAINT_CATALOG, CONSTRAINT_SCHEMA, CONSTRAINT_NAME)
{code}

But if I expand the USING into 'equals', it returns proper results:

{code:sql}
SELECT k.TABLE_NAME, k.COLUMN_NAME, k.CONSTRAINT_NAME, k.REFERENCED_TABLE_NAME, k.REFERENCED_COLUMN_NAME, r.DELETE_RULE, r.UPDATE_RULE FROM information_schema.REFERENTIAL_CONSTRAINTS r JOIN information_schema.KEY_COLUMN_USAGE k on r.CONSTRAINT_CATALOG = k.CONSTRAINT_CATALOG and r.CONSTRAINT_SCHEMA = k.CONSTRAINT_SCHEMA and r.CONSTRAINT_NAME = k.CONSTRAINT_NAME
{code}

Tried with "root" and local user.
Before, it was always returning results with USING.

I would expect that USING works exactly the same with 'equals' ?

However, since 10.3 (I believe), the following query returns EMPTY set:

{code:sql}
SELECT k.TABLE_NAME, k.COLUMN_NAME, k.CONSTRAINT_NAME, k.REFERENCED_TABLE_NAME, k.REFERENCED_COLUMN_NAME, r.DELETE_RULE, r.UPDATE_RULE FROM information_schema.REFERENTIAL_CONSTRAINTS r JOIN information_schema.KEY_COLUMN_USAGE k USING (CONSTRAINT_CATALOG, CONSTRAINT_SCHEMA, CONSTRAINT_NAME)
{code}

But if I expand the USING into 'equals', it returns proper results:

{code:sql}
SELECT k.TABLE_NAME, k.COLUMN_NAME, k.CONSTRAINT_NAME, k.REFERENCED_TABLE_NAME, k.REFERENCED_COLUMN_NAME, r.DELETE_RULE, r.UPDATE_RULE FROM information_schema.REFERENTIAL_CONSTRAINTS r JOIN information_schema.KEY_COLUMN_USAGE k on r.CONSTRAINT_CATALOG = k.CONSTRAINT_CATALOG and r.CONSTRAINT_SCHEMA = k.CONSTRAINT_SCHEMA and r.CONSTRAINT_NAME = k.CONSTRAINT_NAME
{code}

Tried with "root" and local user.
Before, it was always returning results with USING.

I would expect that USING works exactly the same with 'equals' ?

Nuno added a comment - 2019-07-09 08:26

Please let me know if you think this can affect other queries outside the "information_schema".
I haven't detected any, myself.

Nuno added a comment - 2019-07-09 08:26 Please let me know if you think this can affect other queries outside the "information_schema". I haven't detected any, myself.

Alice Sherepa made changes - 2019-07-09 10:57

Affects Version/s

10.4 [ 22408 ]

Alice Sherepa made changes - 2019-07-09 10:57

Fix Version/s		10.3 [ 22126 ]
Fix Version/s		10.4 [ 22408 ]

Alice Sherepa made changes - 2019-07-09 10:57

Status

Open [ 1 ]

Confirmed [ 10101 ]

Alice Sherepa added a comment - 2019-07-09 11:01 - edited

Thanks a lot! Reproducible on 10.3, 10.4:

CREATE TABLE author (

	id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,

	name VARCHAR(100) NOT NULL

) ENGINE = InnoDB;

CREATE TABLE book (

	id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,

	title VARCHAR(200) NOT NULL,

	author_id SMALLINT UNSIGNED NOT NULL,

	CONSTRAINT `fk_b` FOREIGN KEY (author_id) REFERENCES author (id) ON DELETE CASCADE ON UPDATE RESTRICT

) ENGINE = InnoDB;

SELECT k.TABLE_NAME,r.DELETE_RULE

FROM information_schema.REFERENTIAL_CONSTRAINTS r JOIN information_schema.KEY_COLUMN_USAGE k

USING (CONSTRAINT_CATALOG, CONSTRAINT_SCHEMA, CONSTRAINT_NAME);

SELECT k.TABLE_NAME,r.DELETE_RULE

FROM information_schema.REFERENTIAL_CONSTRAINTS r JOIN information_schema.KEY_COLUMN_USAGE k

ON r.CONSTRAINT_CATALOG = k.CONSTRAINT_CATALOG AND r.CONSTRAINT_SCHEMA = k.CONSTRAINT_SCHEMA AND r.CONSTRAINT_NAME = k.CONSTRAINT_NAME;

MariaDB [test]> SELECT k.TABLE_NAME,r.DELETE_RULE

    -> FROM information_schema.REFERENTIAL_CONSTRAINTS r JOIN information_schema.KEY_COLUMN_USAGE k

    -> USING (CONSTRAINT_CATALOG, CONSTRAINT_SCHEMA, CONSTRAINT_NAME);

Empty set (0.037 sec)

MariaDB [test]> SELECT k.TABLE_NAME,r.DELETE_RULE

    -> FROM information_schema.REFERENTIAL_CONSTRAINTS r JOIN information_schema.KEY_COLUMN_USAGE k

    -> ON r.CONSTRAINT_CATALOG = k.CONSTRAINT_CATALOG AND r.CONSTRAINT_SCHEMA = k.CONSTRAINT_SCHEMA AND r.CONSTRAINT_NAME = k.CONSTRAINT_NAME;

+------------+-------------+

| TABLE_NAME | DELETE_RULE |

+------------+-------------+

| book       | CASCADE     |

+------------+-------------+

1 row in set (0.033 sec)

MariaDB [test]> analyze format=json SELECT k.TABLE_NAME,r.DELETE_RULE FROM information_schema.REFERENTIAL_CONSTRAINTS r JOIN information_schema.KEY_COLUMN_USAGE k  USING (CONSTRAINT_CATALOG, CONSTRAINT_SCHEMA, CONSTRAINT_NAME)\G

*************************** 1. row ***************************

ANALYZE: {

  "query_block": {

    "select_id": 1,

    "r_loops": 1,

    "r_total_time_ms": 35.09,

    "table": {

      "table_name": "r",

      "access_type": "ALL",

      "r_loops": 1,

      "r_rows": 1,

      "r_total_time_ms": 0.0521,

      "r_filtered": 100,

      "open_full_table": true,

      "scanned_databases": "all"

},

    "block-nl-join": {

      "table": {

        "table_name": "k",

        "access_type": "ALL",

        "r_loops": 1,

        "r_rows": 71,

        "r_total_time_ms": 0.0383,

        "r_filtered": 100,

        "open_full_table": true,

        "scanned_databases": "all"

},

      "buffer_type": "flat",

      "buffer_size": "2Kb",

      "join_type": "BNL",

      "attached_condition": "k.`CONSTRAINT_CATALOG` = r.`CONSTRAINT_CATALOG` and k.`CONSTRAINT_SCHEMA` = r.`CONSTRAINT_SCHEMA` and k.`CONSTRAINT_NAME` = r.`CONSTRAINT_NAME`",

      "r_filtered": 0

1 row in set (0.037 sec)

MariaDB [test]> analyze format=json  SELECT k.TABLE_NAME,r.DELETE_RULE FROM information_schema.REFERENTIAL_CONSTRAINTS r JOIN information_schema.KEY_COLUMN_USAGE k  ON r.CONSTRAINT_CATALOG = k.CONSTRAINT_CATALOG AND r.CONSTRAINT_SCHEMA = k.CONSTRAINT_SCHEMA AND r.CONSTRAINT_NAME = k.CONSTRAINT_NAME\G

*************************** 1. row ***************************

ANALYZE: {

  "query_block": {

    "select_id": 1,

    "r_loops": 1,

    "r_total_time_ms": 36.172,

    "table": {

      "table_name": "r",

      "access_type": "ALL",

      "r_loops": 1,

      "r_rows": 1,

      "r_total_time_ms": 0.0514,

      "r_filtered": 100,

      "open_full_table": true,

      "scanned_databases": "all"

},

    "block-nl-join": {

      "table": {

        "table_name": "k",

        "access_type": "ALL",

        "r_loops": 1,

        "r_rows": 71,

        "r_total_time_ms": 0.0762,

        "r_filtered": 100,

        "open_full_table": true,

        "scanned_databases": "all"

},

      "buffer_type": "flat",

      "buffer_size": "22Kb",

      "join_type": "BNL",

      "attached_condition": "k.`CONSTRAINT_CATALOG` = r.`CONSTRAINT_CATALOG` and k.`CONSTRAINT_SCHEMA` = r.`CONSTRAINT_SCHEMA` and k.`CONSTRAINT_NAME` = r.`CONSTRAINT_NAME`",

      "r_filtered": 1.4085

1 row in set (0.038 sec)

Alice Sherepa added a comment - 2019-07-09 11:01 - edited Thanks a lot! Reproducible on 10.3, 10.4: CREATE TABLE author ( id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY , name VARCHAR (100) NOT NULL ) ENGINE = InnoDB; CREATE TABLE book ( id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY , title VARCHAR (200) NOT NULL , author_id SMALLINT UNSIGNED NOT NULL , CONSTRAINT `fk_b` FOREIGN KEY (author_id) REFERENCES author (id) ON DELETE CASCADE ON UPDATE RESTRICT ) ENGINE = InnoDB; SELECT k.TABLE_NAME,r.DELETE_RULE FROM information_schema.REFERENTIAL_CONSTRAINTS r JOIN information_schema.KEY_COLUMN_USAGE k USING (CONSTRAINT_CATALOG, CONSTRAINT_SCHEMA, CONSTRAINT_NAME); SELECT k.TABLE_NAME,r.DELETE_RULE FROM information_schema.REFERENTIAL_CONSTRAINTS r JOIN information_schema.KEY_COLUMN_USAGE k ON r.CONSTRAINT_CATALOG = k.CONSTRAINT_CATALOG AND r.CONSTRAINT_SCHEMA = k.CONSTRAINT_SCHEMA AND r.CONSTRAINT_NAME = k.CONSTRAINT_NAME; MariaDB [test]> SELECT k.TABLE_NAME,r.DELETE_RULE -> FROM information_schema.REFERENTIAL_CONSTRAINTS r JOIN information_schema.KEY_COLUMN_USAGE k -> USING (CONSTRAINT_CATALOG, CONSTRAINT_SCHEMA, CONSTRAINT_NAME); Empty set (0.037 sec) MariaDB [test]> SELECT k.TABLE_NAME,r.DELETE_RULE -> FROM information_schema.REFERENTIAL_CONSTRAINTS r JOIN information_schema.KEY_COLUMN_USAGE k -> ON r.CONSTRAINT_CATALOG = k.CONSTRAINT_CATALOG AND r.CONSTRAINT_SCHEMA = k.CONSTRAINT_SCHEMA AND r.CONSTRAINT_NAME = k.CONSTRAINT_NAME; +------------+-------------+ | TABLE_NAME | DELETE_RULE | +------------+-------------+ | book | CASCADE | +------------+-------------+ 1 row in set (0.033 sec) MariaDB [test]> analyze format=json SELECT k.TABLE_NAME,r.DELETE_RULE FROM information_schema.REFERENTIAL_CONSTRAINTS r JOIN information_schema.KEY_COLUMN_USAGE k USING (CONSTRAINT_CATALOG, CONSTRAINT_SCHEMA, CONSTRAINT_NAME)\G *************************** 1. row *************************** ANALYZE: { "query_block": { "select_id": 1, "r_loops": 1, "r_total_time_ms": 35.09, "table": { "table_name": "r", "access_type": "ALL", "r_loops": 1, "r_rows": 1, "r_total_time_ms": 0.0521, "r_filtered": 100, "open_full_table": true, "scanned_databases": "all" }, "block-nl-join": { "table": { "table_name": "k", "access_type": "ALL", "r_loops": 1, "r_rows": 71, "r_total_time_ms": 0.0383, "r_filtered": 100, "open_full_table": true, "scanned_databases": "all" }, "buffer_type": "flat", "buffer_size": "2Kb", "join_type": "BNL", "attached_condition": "k.`CONSTRAINT_CATALOG` = r.`CONSTRAINT_CATALOG` and k.`CONSTRAINT_SCHEMA` = r.`CONSTRAINT_SCHEMA` and k.`CONSTRAINT_NAME` = r.`CONSTRAINT_NAME`", "r_filtered": 0 } } } 1 row in set (0.037 sec) MariaDB [test]> analyze format=json SELECT k.TABLE_NAME,r.DELETE_RULE FROM information_schema.REFERENTIAL_CONSTRAINTS r JOIN information_schema.KEY_COLUMN_USAGE k ON r.CONSTRAINT_CATALOG = k.CONSTRAINT_CATALOG AND r.CONSTRAINT_SCHEMA = k.CONSTRAINT_SCHEMA AND r.CONSTRAINT_NAME = k.CONSTRAINT_NAME\G *************************** 1. row *************************** ANALYZE: { "query_block": { "select_id": 1, "r_loops": 1, "r_total_time_ms": 36.172, "table": { "table_name": "r", "access_type": "ALL", "r_loops": 1, "r_rows": 1, "r_total_time_ms": 0.0514, "r_filtered": 100, "open_full_table": true, "scanned_databases": "all" }, "block-nl-join": { "table": { "table_name": "k", "access_type": "ALL", "r_loops": 1, "r_rows": 71, "r_total_time_ms": 0.0762, "r_filtered": 100, "open_full_table": true, "scanned_databases": "all" }, "buffer_type": "flat", "buffer_size": "22Kb", "join_type": "BNL", "attached_condition": "k.`CONSTRAINT_CATALOG` = r.`CONSTRAINT_CATALOG` and k.`CONSTRAINT_SCHEMA` = r.`CONSTRAINT_SCHEMA` and k.`CONSTRAINT_NAME` = r.`CONSTRAINT_NAME`", "r_filtered": 1.4085 } } } 1 row in set (0.038 sec)

Alice Sherepa made changes - 2019-07-09 11:02

Assignee

Alice Sherepa [ alice ]

Nuno made changes - 2019-07-09 11:27

Description

However, since 10.3 (I believe), the following query returns EMPTY set:

{code:sql}
SELECT k.TABLE_NAME, k.COLUMN_NAME, k.CONSTRAINT_NAME, k.REFERENCED_TABLE_NAME, k.REFERENCED_COLUMN_NAME, r.DELETE_RULE, r.UPDATE_RULE FROM information_schema.REFERENTIAL_CONSTRAINTS r JOIN information_schema.KEY_COLUMN_USAGE k USING (CONSTRAINT_CATALOG, CONSTRAINT_SCHEMA, CONSTRAINT_NAME)
{code}

But if I expand the USING into 'equals', it returns proper results:

{code:sql}
SELECT k.TABLE_NAME, k.COLUMN_NAME, k.CONSTRAINT_NAME, k.REFERENCED_TABLE_NAME, k.REFERENCED_COLUMN_NAME, r.DELETE_RULE, r.UPDATE_RULE FROM information_schema.REFERENTIAL_CONSTRAINTS r JOIN information_schema.KEY_COLUMN_USAGE k on r.CONSTRAINT_CATALOG = k.CONSTRAINT_CATALOG and r.CONSTRAINT_SCHEMA = k.CONSTRAINT_SCHEMA and r.CONSTRAINT_NAME = k.CONSTRAINT_NAME
{code}

Tried with "root" and local user.
Before, it was always returning results with USING.

I would expect that USING works exactly the same with 'equals' ?

Since 10.3 (I believe), the following query returns EMPTY set:

{code:sql}
SELECT k.TABLE_NAME, k.COLUMN_NAME, k.CONSTRAINT_NAME, k.REFERENCED_TABLE_NAME, k.REFERENCED_COLUMN_NAME, r.DELETE_RULE, r.UPDATE_RULE FROM information_schema.REFERENTIAL_CONSTRAINTS r JOIN information_schema.KEY_COLUMN_USAGE k USING (CONSTRAINT_CATALOG, CONSTRAINT_SCHEMA, CONSTRAINT_NAME)
{code}

But if I expand the USING into 'equals', it returns proper results:

{code:sql}
SELECT k.TABLE_NAME, k.COLUMN_NAME, k.CONSTRAINT_NAME, k.REFERENCED_TABLE_NAME, k.REFERENCED_COLUMN_NAME, r.DELETE_RULE, r.UPDATE_RULE FROM information_schema.REFERENTIAL_CONSTRAINTS r JOIN information_schema.KEY_COLUMN_USAGE k on r.CONSTRAINT_CATALOG = k.CONSTRAINT_CATALOG and r.CONSTRAINT_SCHEMA = k.CONSTRAINT_SCHEMA and r.CONSTRAINT_NAME = k.CONSTRAINT_NAME
{code}

Tried with "root" and local user.
Before, it was always returning results with USING.

I would expect that USING works exactly the same with 'equals' ?

Alice Sherepa made changes - 2019-07-09 11:30

Assignee

Alice Sherepa [ alice ]

Varun Gupta [ varun ]

Varun Gupta (Inactive) added a comment - 2020-12-15 08:14

Fixed by ~~MDEV-21201~~

Varun Gupta (Inactive) added a comment - 2020-12-15 08:14 Fixed by MDEV-21201

Varun Gupta (Inactive) made changes - 2020-12-15 08:14

Fix Version/s		N/A [ 14700 ]
Fix Version/s	10.3 [ 22126 ]
Fix Version/s	10.4 [ 22408 ]
Resolution		Duplicate [ 3 ]
Status	Confirmed [ 10101 ]	Closed [ 6 ]

Varun Gupta (Inactive) made changes - 2020-12-15 08:15

Link

This issue is duplicated by ~~MDEV-21201~~ [ ~~MDEV-21201~~ ]

Sergei Golubchik made changes - 2021-12-06 21:49

Workflow

MariaDB v3 [ 98083 ]

MariaDB v4 [ 156453 ]

MariaDB Server

JOIN USING not behaving like 'equals'

Details

Description

Attachments

Issue Links

Activity

People

Dates

Git Integration