Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-19990

JOIN USING not behaving like 'equals'

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Duplicate
    • 10.3(EOL), 10.4(EOL)
    • N/A
    • Optimizer

    Description

      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' ?

      Attachments

        Issue Links

          Activity

            nunop Nuno added a comment -

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

            nunop Nuno added a comment - Please let me know if you think this can affect other queries outside the "information_schema". I haven't detected any, myself.
            alice Alice Sherepa added a comment - - 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 Alice Sherepa added a comment - - 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)
            varun Varun Gupta (Inactive) added a comment - Fixed by MDEV-21201

            People

              varun Varun Gupta (Inactive)
              nunop Nuno
              Votes:
              1 Vote for this issue
              Watchers:
              6 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.