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

          Activity

            People

            Assignee:
            sanja Oleksandr Byelkin
            Reporter:
            lukas.eder Lukas Eder
            Votes:
            1 Vote for this issue
            Watchers:
            7 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved: