Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Columns from view in MySQL 8 sorted alphabetically on Data tab #617

Closed
tattybaws opened this issue Apr 17, 2019 · 14 comments

Comments

Projects
None yet
2 participants
@tattybaws
Copy link

commented Apr 17, 2019

Steps to reproduce this issue

  1. Step 1; The SQL for the view is :
    Query Results
    View Results

select m.SERVER_NAME AS SERVER_NAME,'Monitored' AS Status,date_format(m.CREATION_TIME,'%Y-%m-%d') AS DataDate,(case when ((l.SERVER_NAME = m.SERVER_NAME) and (m.OLD_NEW = 'N')) then 'YES' when ((l.SERVER_NAME <> m.SERVER_NAME) and (m.OLD_NEW = 'N')) then 'NO' else 'NA' end) AS GO_LIVE,(case when (m.OLD_NEW = 'O') then 'Old_Patrol' when (m.OLD_NEW = 'N') then 'New_Patrol' else 'God Knows' end) AS OldNew,upper(b.OS) AS OS,(case when (s.SERVER_NAME = m.SERVER_NAME) then 'YES' else 'NO' end) AS STANDBY,(case when isnull(c.SERVER_NAME) then 'NO' else 'YES' end) AS BCR from ((((PatrolMonitoredServers m left join PatrolGoLiveServers l on((upper(m.SERVER_NAME) = upper(l.SERVER_NAME)))) left join BCR.SERVERS b on((upper(m.SERVER_NAME) = upper(b.SERVER_NAME)))) left join PatrolMonitoredStandbyServers s on((upper(m.SERVER_NAME) = upper(s.SERVER_NAME)))) left join BCR.SERVERS c on((upper(m.SERVER_NAME) = upper(c.SERVER_NAME)))) union select d.SERVER_NAME AS SERVER_NAME,'Decommissioned' AS Status,date_format(d.DECOMM_ON,'%Y-%m-%d') AS DataDate,'NOT_LIVE' AS NOT_LIVE,'NA' AS OldNew,upper(b.OS) AS OS,'NA' AS DONT CARE,'NA' AS BCR from (PatrolDecommServers d left join BCR.SERVERS b on((upper(d.SERVER_NAME) = upper(b.SERVER_NAME))))
2. Step 2;
Output from running query is correct
3. Step 3;
Looking at data tab for the view has the wrong column headings.
4. Then I get...

Current behavior

Looking at data tab for the view has the wrong column headings.

Expected behavior

Output from running query should be consistent with the output from running the SQL

Possible solution

Environment

  • HeidiSQL version:
    10.1.0.5537
  • Database system and version:
    Server version: 8.0.15 MySQL Community Server - GPL
  • Operating system:
    Linux centos 3.10.0-957.10.1.el7.x86_64
@ansgarbecker

This comment has been minimized.

Copy link
Collaborator

commented Apr 17, 2019

It's highly unlikely that HeidiSQL mixes up the order of column names from a view. More likely that the view code has this wrong order already. Please check if that's the case. If you still think HeidiSQL is to blame here, provide a usable reproduction recipe, with queries for creating all dependencies of your view.

@tattybaws

This comment has been minimized.

Copy link
Author

commented Apr 18, 2019

I have shown the output of the view and the output of the view SQL when running it a query and they have different column names for the data. I have also shown the SQL. What more evidence do you need ? Happy to provide. Also when I look at the view data in PHPMyadmin I get the correct column names

@ansgarbecker

This comment has been minimized.

Copy link
Collaborator

commented Apr 18, 2019

Please post queries needed to create all dependent tables and the view itself.

@tattybaws

This comment has been minimized.

Copy link
Author

commented Apr 18, 2019

/*
||
|| ViewMonitoredAndDecommOverview
||
*/
SELECT `m`.`SERVER_NAME` AS `SERVER_NAME`
	,'Monitored' AS `Status`
	,date_format(`m`.`CREATION_TIME`, '%Y-%m-%d') AS `DataDate`
	,(
		CASE 
			WHEN (
					(`l`.`SERVER_NAME` = `m`.`SERVER_NAME`)
					AND (`m`.`OLD_NEW` = 'N')
					)
				THEN 'YES'
			WHEN (
					(`l`.`SERVER_NAME` <> `m`.`SERVER_NAME`)
					AND (`m`.`OLD_NEW` = 'N')
					)
				THEN 'NO'
			ELSE 'NA'
			END
		) AS `GO_LIVE`
	,(
		CASE 
			WHEN (`m`.`OLD_NEW` = 'O')
				THEN 'Old_Patrol'
			WHEN (`m`.`OLD_NEW` = 'N')
				THEN 'New_Patrol'
			ELSE 'God Knows'
			END
		) AS `OldNew`
	,upper(`b`.`OS`) AS `OS`
	,(
		CASE 
			WHEN (`s`.`SERVER_NAME` = `m`.`SERVER_NAME`)
				THEN 'YES'
			ELSE 'NO'
			END
		) AS `STANDBY`
	,(
		CASE 
			WHEN isnull(`c`.`SERVER_NAME`)
				THEN 'NO'
			ELSE 'YES'
			END
		) AS `BCR`
FROM (
	(
		(
			(
				`PatrolMonitoredServers` `m` LEFT JOIN `PatrolGoLiveServers` `l` ON ((upper(`m`.`SERVER_NAME`) = upper(`l`.`SERVER_NAME`)))
				) LEFT JOIN `BCR`.`SERVERS` `b` ON ((upper(`m`.`SERVER_NAME`) = upper(`b`.`SERVER_NAME`)))
			) LEFT JOIN `PatrolMonitoredStandbyServers` `s` ON ((upper(`m`.`SERVER_NAME`) = upper(`s`.`SERVER_NAME`)))
		) LEFT JOIN `BCR`.`SERVERS` `c` ON ((upper(`m`.`SERVER_NAME`) = upper(`c`.`SERVER_NAME`)))
	)

UNION

SELECT `d`.`SERVER_NAME` AS `SERVER_NAME`
	,'Decommissioned' AS `Status`
	,date_format(`d`.`DECOMM_ON`, '%Y-%m-%d') AS `DataDate`
	,'NOT_LIVE' AS `NOT_LIVE`
	,'NA' AS `OldNew`
	,upper(`b`.`OS`) AS `OS`
	,'NA' AS `DONT CARE`
	,'NA' AS `BCR`
FROM (
	`PatrolDecommServers` `d` LEFT JOIN `BCR`.`SERVERS` `b` ON ((upper(`d`.`SERVER_NAME`) = upper(`b`.`SERVER_NAME`)))
	)


/*
||
|| patrol.PatrolMonitoredServers
||
*/
CREATE TABLE `PatrolMonitoredServers` (
	`SERVER_NAME` VARCHAR(50) NULL DEFAULT NULL,
	`OLD_NEW` VARCHAR(3) NULL DEFAULT NULL,
	`PATROL_VERSION` CHAR(3) NULL DEFAULT NULL,
	`DBATEAM_SERVER_ID` INT(10) UNSIGNED NULL DEFAULT NULL,
	`OS_VERSION` VARCHAR(50) NULL DEFAULT NULL,
	`NOTES` VARCHAR(200) NULL DEFAULT NULL,
	`OS` VARCHAR(10) NULL DEFAULT NULL,
	`IP_ADDRESS` VARCHAR(15) NULL DEFAULT NULL,
	`CREATION_TIME` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
	UNIQUE INDEX `Index 1` (`SERVER_NAME`, `OLD_NEW`)
)
COMMENT='File that is used directly to populate this table  :/media/sf_VM_Share/Patrol/MonitoredServers/Monitored_Servers.YYYY-MM-DD.txtOriginal file are located in directory /media/sf_VM_Share/Patrol/Enrichment.1) /Patrol3/sp_patrol3_Toplevel.csv ...'
COLLATE='utf8_general_ci'
ENGINE=InnoDB
ROW_FORMAT=COMPACT
;


/*
||
|| patrol.PatrolGoLiveServers
||
*/
CREATE TABLE `PatrolGoLiveServers` (
	`SERVER_NAME` VARCHAR(100) NOT NULL,
	`COMMENT` VARCHAR(200) NULL DEFAULT NULL,
	`CREATION_TIME` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
)
COMMENT='File that is used directly to populate this table  :/media/sf_VM_Share/Patrol/MonitoredServers/Monitored_Servers.GoLive.YYYY-MM-DD.txtThis only has data for New Patrol'
COLLATE='utf8_general_ci'
ENGINE=InnoDB
ROW_FORMAT=COMPACT
;


/*
||
|| BCR.SERVERS
||
*/
CREATE TABLE `SERVERS` (
	`SERVER_NAME` VARCHAR(50) NOT NULL COLLATE 'utf8_general_ci',
	`OS` VARCHAR(50) NOT NULL COLLATE 'utf8_general_ci',
	`ID` INT(11) NOT NULL DEFAULT '0',
	`BCR` VARCHAR(200) NULL DEFAULT NULL,
	`SUPPORTED` VARCHAR(20) NULL DEFAULT NULL COLLATE 'utf8_general_ci',
	`STANDBY` VARCHAR(20) NULL DEFAULT NULL COLLATE 'utf8_general_ci',
	`COMMISSIONED_ON` DATE NULL DEFAULT NULL,
	`DECOMMED_ON` DATE NULL DEFAULT NULL,
	`CREATION_TIME` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
	INDEX `Index 1` (`SERVER_NAME`),
	INDEX `Index 2` (`ID`)
)
COMMENT='Populated by data in spreadsheet "Documents/VM_Share/IBM_Baseline/CurrentServers"Pentaho job : Baseline/BaselineAvailable from Pentaho Jobs web page/mnt/sf_VM_Share/IBM_Baseline/CurrentServers.xls'
COLLATE='utf8_general_ci'
ENGINE=InnoDB
ROW_FORMAT=COMPACT
;


/*
||
|| patrol.PatrolMonitoredStandbyServers
||
*/
CREATE TABLE `PatrolMonitoredStandbyServers` (
	`SERVER_NAME` VARCHAR(50) NULL DEFAULT NULL,
	`CREATION_TIME` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
	UNIQUE INDEX `Index 1` (`SERVER_NAME`)
)
COMMENT='File that is used directly to populate this table  :/media/sf_VM_Share/Patrol/MonitoredServers/Monitored_Standby_Servers.YYYY-MM-DD.txt'
COLLATE='utf8_general_ci'
ENGINE=InnoDB
ROW_FORMAT=COMPACT
;

@ansgarbecker

This comment has been minimized.

Copy link
Collaborator

commented Apr 18, 2019

Ok, if I remove whitespaces in the index names (eg in "Index 1"), I can create the tables. But the view code is not fireable:

SQL Error (1146): Table 'bcr.servers' doesn't exist
@tattybaws

This comment has been minimized.

Copy link
Author

commented Apr 19, 2019

The create code for the BCR.SERVERS table is in the SQL I have posted. I missed out the SQL for the table below :
/*
||
|| patrol.PatrolDecommServers
|| test1
*/
CREATE TABLE PatrolDecommServers (
SERVER_NAME VARCHAR(50) NULL DEFAULT NULL,
DECOMM_ON DATETIME NULL DEFAULT NULL,
CREATION_TIME TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
)
COMMENT='File that is used directly to populate this table :/media/sf_VM_Share/Patrol/DecommServers/DecommServers.YYYY-MM-DD.txtParse the files below looking for the tag {Dd]ecommFiles are located in directory /media/sf_VM_Share/Patrol/Enrichment.'
COLLATE='utf8_general_ci'
ENGINE=InnoDB
ROW_FORMAT=COMPACT
;

@tattybaws

This comment has been minimized.

Copy link
Author

commented Apr 19, 2019

The tables and views involved are

patrol.ViewMonitoredAndDecommOverview
patrol.PatrolMonitoredServers
patrol.PatrolGoLiveServers
BCR.SERVERS
patrol.PatrolMonitoredStandbyServers
patrol.PatrolDecommServers

@ansgarbecker

This comment has been minimized.

Copy link
Collaborator

commented Apr 19, 2019

Ok, that BCR database was not mentioned so I created it in the same test database as the other tables.

I finally created the view from your very first post on my MariaDB 10.3 server, and get these columns in the data tab:
grafik

Selecting it via the query you provided gives me the same columns in the same order:

grafik

So I thought this may behave differently when doing that on a MySQL 8 server, as you did. And tata, the same view on MySQL 8 gives me a mixed up result header:

grafik

However, this is not a bug in HeidiSQL. You should search for similar issues on MySQL 8. Probably that reordering is intentionally done, for some reason. Here's the documentation for view creation on MySQL 8.

@tattybaws

This comment has been minimized.

Copy link
Author

commented Apr 19, 2019

Thanks for the feedback but I dont see this problem when I look at the view on phpMyadmin

@tattybaws

This comment has been minimized.

Copy link
Author

commented Apr 19, 2019

I also do not understand why the data is presented correctly when runin the SQL for the view but when you look at the data tab it is wrong. Looks to me like the problem only exists in the HeidiSQL data tab for a view.

@ansgarbecker

This comment has been minimized.

Copy link
Collaborator

commented Apr 19, 2019

Welcome in the world of incompatible changes between versions.

Could you also please try a different client, like MySQL Workbench, or at least some other Windows based client?

The code in HeidiSQL for retrieving a result from a table or view is pretty straight forward:

      NumFields := mysql_num_fields(LastResult);
      FColumnNames.Clear;
      for i:=0 to NumFields-1 do begin
        Field := mysql_fetch_field_direct(LastResult, i);
        FColumnNames.Add(Connection.DecodeAPIString(Field.name));
        ...
      end;
@ansgarbecker

This comment has been minimized.

Copy link
Collaborator

commented Apr 19, 2019

Oh, I just see that even when selecting from the view in a query tab shows the correct column order, while the data tab still reorders them. And the order is - maybe you haven't noticed it - alphabetically. Possibly there is some sort of sorting for column names in the data grids.

@ansgarbecker ansgarbecker reopened this Apr 19, 2019

@ansgarbecker

This comment has been minimized.

Copy link
Collaborator

commented Apr 19, 2019

Found it: selecting columns from IS.COLUMNS does no longer sort them by their ordinal position:

SELECT * FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME='yourview' AND TABLE_SCHEMA='yourdatabase'

Adding a sorting fixes it:

SELECT * FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME='yourview' AND TABLE_SCHEMA='yourdatabase'
ORDER BY ORDINAL_POSITION

@ansgarbecker ansgarbecker changed the title Column names in wrong order Columns from view in MySQL 8 sorted alphabetically on Data tab Apr 19, 2019

@ansgarbecker ansgarbecker added this to the v10.2 milestone Apr 19, 2019

@ansgarbecker

This comment has been minimized.

Copy link
Collaborator

commented Apr 19, 2019

Please update to the next build and test again.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.