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

[SOLVED] "select field1,field2,fieldN" always return zero, but not "select *"

Details

    Description

      [SOLVED] after replace "USING(...)" with explicit "JOIN ... ON(...)" all good.

      after migrating to mariadb yesterday, i just noticed that some of my storedprocedures suddenly stop working. its related to "information_schema", for retreiving a table primary/foreign key column(s).

      when using "select" syntax, if i listing column(s) name, the result is always zero.
      but when using "*", it working properly.

      #########################

      SELECT * /* WORKING */
      SELECT `column_name`,`constraint_type` /* NOT WORKING */

      FROM `information_schema`.`table_constraints`
      JOIN `information_schema`.`key_column_usage`
      USING( `table_schema`,`table_name`,`constraint_name` )

      WHERE `table_schema` = database() AND `table_name` = 'something'
      AND (`constraint_type` = 'PRIMARY KEY' OR `constraint_type` = 'FOREIGN KEY')

      ORDER BY `constraint_type` DESC, `column_name` ASC;

      #########################

      i have trying to run that sql from CLI, still not working (only "*" that work).

      if running that sql on phpmyadmin/pma, it will always result nothing (using "*" or not).
      But, when you change "database()" to explicit dbname, "*" will work.

      there is something strange with "database()" too.

      (sorry for my english)

      Attachments

        Activity

          Hi anovsiradj,
          can you please provide proper example with sql queries as a test, to demonstrate this behavior ?

          anel Anel Husakovic added a comment - Hi anovsiradj , can you please provide proper example with sql queries as a test, to demonstrate this behavior ?
          anovsiradj anovsiradj added a comment -

          yes.

          #########################

          working sql:

          SELECT *
          FROM `information_schema`.`table_constraints`
          JOIN `information_schema`.`key_column_usage`
          USING( `table_schema`,`table_name`,`constraint_name` )
          WHERE `table_schema` = database() AND `table_name` = 'your_table_name'
          AND (`constraint_type` = 'PRIMARY KEY' OR `constraint_type` = 'FOREIGN KEY')

          ORDER BY `constraint_type` DESC, `column_name` ASC;

          #########################

          not working sql:

          SELECT `column_name`,`constraint_type`

          FROM `information_schema`.`table_constraints`
          JOIN `information_schema`.`key_column_usage`
          USING( `table_schema`,`table_name`,`constraint_name` )

          WHERE `table_schema` = database() AND `table_name` = 'your_table_name'
          AND (`constraint_type` = 'PRIMARY KEY' OR `constraint_type` = 'FOREIGN KEY')

          ORDER BY `constraint_type` DESC, `column_name` ASC;

          anovsiradj anovsiradj added a comment - yes. ######################### working sql: SELECT * FROM `information_schema`.`table_constraints` JOIN `information_schema`.`key_column_usage` USING( `table_schema`,`table_name`,`constraint_name` ) WHERE `table_schema` = database() AND `table_name` = 'your_table_name' AND (`constraint_type` = 'PRIMARY KEY' OR `constraint_type` = 'FOREIGN KEY') ORDER BY `constraint_type` DESC, `column_name` ASC; ######################### not working sql: SELECT `column_name`,`constraint_type` FROM `information_schema`.`table_constraints` JOIN `information_schema`.`key_column_usage` USING( `table_schema`,`table_name`,`constraint_name` ) WHERE `table_schema` = database() AND `table_name` = 'your_table_name' AND (`constraint_type` = 'PRIMARY KEY' OR `constraint_type` = 'FOREIGN KEY') ORDER BY `constraint_type` DESC, `column_name` ASC;
          anel Anel Husakovic added a comment - - edited

          Sorry, you updated your question with [SOLVED] does it mean we can close this MDEV ?
          If not, please give us example with your data, write some test case. It is easier to take a look into real example than into a generic one.

          And yes you should specify which database you are using with

          using(<db_name>)

          clause.

          anel Anel Husakovic added a comment - - edited Sorry, you updated your question with [SOLVED] does it mean we can close this MDEV ? If not, please give us example with your data, write some test case. It is easier to take a look into real example than into a generic one. And yes you should specify which database you are using with using(<db_name>) clause.
          anovsiradj anovsiradj added a comment -

          yes, you can close this mdev. for reference, i have upload a screencast.
          thankyou.

          anovsiradj anovsiradj added a comment - yes, you can close this mdev. for reference, i have upload a screencast. thankyou.

          People

            anel Anel Husakovic
            anovsiradj anovsiradj
            Votes:
            0 Vote for this issue
            Watchers:
            2 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.