[MDEV-20687] [SOLVED] "select field1,field2,fieldN" always return zero, but not "select *" Created: 2019-09-27  Updated: 2019-09-27  Resolved: 2019-09-27

Status: Closed
Project: MariaDB Server
Component/s: Information Schema, N/A
Affects Version/s: 10.4.8
Fix Version/s: 10.4.8

Type: Bug Priority: Major
Reporter: anovsiradj Assignee: Anel Husakovic
Resolution: Not a Bug Votes: 0
Labels: Compatibility, mysql
Environment:

ubuntu = 1604 xenial /
php = 7.0.33 /
php-extension = mysqlnd 5.0.12-dev - 20150407 /

https://downloads.mariadb.org/mariadb/repositories/#distro=Ubuntu&distro_release=xenial--ubuntu_xenial&version=10.4

Attachments: File mariadb-20190927163209.webm    

 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)

 Comments   
Comment by Anel Husakovic [ 2019-09-27 ]

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

Comment by anovsiradj [ 2019-09-27 ]

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;

Comment by Anel Husakovic [ 2019-09-27 ]

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.

Comment by anovsiradj [ 2019-09-27 ]

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

Generated at Thu Feb 08 09:01:24 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.