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

No records produced in information_schema query, depending on projection

Details

    Description

      Create a table with a check constraint:

      create table t (i int, constraint a check (i > 0));

      Now consider this query, which should produce the above check constraint:

      select 
        tc.TABLE_SCHEMA, 
        tc.TABLE_NAME, 
        cc.CONSTRAINT_NAME, 
        cc.CHECK_CLAUSE
      		 
      from information_schema.TABLE_CONSTRAINTS tc
      		 
        join information_schema.CHECK_CONSTRAINTS cc
      		 
          using (CONSTRAINT_CATALOG, CONSTRAINT_SCHEMA, TABLE_NAME, CONSTRAINT_NAME)
      		 
      ;

      It does not produce any results.

      Add two columns to the projection and ta-dah, we get the desired output:

      select 
        tc.TABLE_SCHEMA, 
        tc.TABLE_NAME, 
        cc.CONSTRAINT_NAME, 
        cc.CHECK_CLAUSE,
      		 
        tc.CONSTRAINT_CATALOG, 
        tc.CONSTRAINT_SCHEMA
      		 
      from information_schema.TABLE_CONSTRAINTS tc
      		 
        join information_schema.CHECK_CONSTRAINTS cc
      		 
          using (CONSTRAINT_CATALOG, CONSTRAINT_SCHEMA, TABLE_NAME, CONSTRAINT_NAME)
      		 
      ;

      Attachments

        Issue Links

          duplicates

          Bug - A problem which impairs or prevents the functions of the product. MDEV-19990 JOIN USING not behaving like 'equals'

          • Major - Major loss of function.
          • Closed
          is duplicated by

          Bug - A problem which impairs or prevents the functions of the product. MDEV-24466 logic bug when joining INFROMATION_SCHEMA tables using USING

          • Major - Major loss of function.
          • Closed

          Activity

            Ascending order - Click to sort in descending order
            View 6 older comments
            sanja Oleksandr Byelkin added a comment -

            Check natural join, probably we have probem with it

            sanja Oleksandr Byelkin added a comment - Check natural join, probably we have probem with it
            sanja Oleksandr Byelkin added a comment - - edited

            probably we have to have to allow matching with other table fields names in case of natural join

            sanja Oleksandr Byelkin added a comment - - edited probably we have to have to allow matching with other table fields names in case of natural join
            sanja Oleksandr Byelkin added a comment -

            gin/bb-10.3-MDEV-21201)
            Author: Oleksandr Byelkin <sanja@mariadb.com>
            Date: Wed Jul 8 16:26:34 2020 +0200

            MDEV-21201 No records produced in information_schema query, depending on projection

            In case of USING check also join field list to mark fields as used.

            In case of NATURAL JOIN mark all field (one table can not be opened
            in any case so optimisation does not worth it).

            IMHO table should be checked for used fields and filled after prepare,
            when we will fave whole info about used fields but it is too big change
            for a bugfix.

            sanja Oleksandr Byelkin added a comment - gin/bb-10.3- MDEV-21201 ) Author: Oleksandr Byelkin <sanja@mariadb.com> Date: Wed Jul 8 16:26:34 2020 +0200 MDEV-21201 No records produced in information_schema query, depending on projection In case of USING check also join field list to mark fields as used. In case of NATURAL JOIN mark all field (one table can not be opened in any case so optimisation does not worth it). IMHO table should be checked for used fields and filled after prepare, when we will fave whole info about used fields but it is too big change for a bugfix.
            serg Sergei Golubchik added a comment -

            for now, please, just add something like

              if (natural join or union)
            		 
                birtmap_set_all(&bitmap);

            that is, disable the MDEV-14275 optimization in this case.
            I already have a fix that does it correctly in all applicable cases, I hope it can be pushed soon.

            serg Sergei Golubchik added a comment - for now, please, just add something like if (natural join or union ) birtmap_set_all(&bitmap); that is, disable the MDEV-14275 optimization in this case. I already have a fix that does it correctly in all applicable cases, I hope it can be pushed soon.
            sanja Oleksandr Byelkin added a comment -

            Temporary patch (not so efficient) pushed to 10.3. Proper fix will go later (next release).

            sanja Oleksandr Byelkin added a comment - Temporary patch (not so efficient) pushed to 10.3. Proper fix will go later (next release).

            People

              sanja Oleksandr Byelkin
              lukas.eder Lukas Eder
              Votes:
              1 Vote for this issue
              Watchers:
              7 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.