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

No records produced in information_schema query, depending on projection

    XMLWordPrintable

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

        1. screenshot-1.png
          29 kB
          Lukas Eder
        2. screenshot-2.png
          45 kB
          Lukas Eder

        Issue Links

          Activity

            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.