Apache OpenOffice (AOO) Bugzilla – Issue 107224
after copying a table with data, can not open due to "unknown column in order clause"
Last modified: 2013-02-02 02:54:51 UTC
I have a table pupil I do a copy and paste to create pupil2 (appending data) Then I double click the new table to open it. I get an error 'unknown column' 'pupil.file_nbr' in 'order.clause' file_nbr is the one field in the primary key. It has "ascending sort order". I suspect that when copying the table, the reference to pupil.file_nbr is not renamed to pupil2.file_nbr the "more information" dialog box tells me The SQL command leading to this error is: SELECT * FROM `bellevue_kinder`.`pupil2` ORDER BY `pupil`.`file_nbr`
workaround in to execute create table pupil2 select * from pupil
@ timrichardson What database you use? If odb with built-in HSQLDB, than you must use double-quote for database fields as "pupil"."file_nbr". Can you add your database without sensitive data to this issue? It needs to investigate it.
Database is MySql 5.1.41
mysql 5.1.41 with a jdbc connection
-- MySQL dump 10.13 Distrib 5.1.41, for debian-linux-gnu (i486) -- -- Host: localhost Database: bellevue_kinder -- ------------------------------------------------------ -- Server version 5.1.41-2 /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; /*!40101 SET NAMES utf8 */; /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; /*!40103 SET TIME_ZONE='+00:00' */; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; -- -- Table structure for table `pupil` -- DROP TABLE IF EXISTS `pupil`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `pupil` ( `kinder_group` varchar(32) DEFAULT NULL, `alloc_nbr_pref` varchar(255) DEFAULT NULL, `kinder_name` varchar(128) DEFAULT NULL, `file_nbr` varchar(16) NOT NULL, `given_name` varchar(32) DEFAULT NULL, `surname` varchar(64) DEFAULT NULL, `DOB` date DEFAULT NULL, `year_attending_kinder3` int(4) DEFAULT NULL, `year_attending_kinder4` int(4) DEFAULT NULL, `is_special_needs` tinyint(1) DEFAULT NULL, `address_cor_1` varchar(64) DEFAULT NULL, `address_cor_2` varchar(64) DEFAULT NULL, `address_cor_suburb` varchar(32) DEFAULT NULL, `address_cor_state` varchar(16) DEFAULT NULL, `address_cor_pcode` varchar(8) DEFAULT NULL, `address_Child_1` varchar(64) DEFAULT NULL, `address_child_2` varchar(64) DEFAULT NULL, `address_child_suburb` varchar(32) DEFAULT NULL, `address_child_state` varchar(16) DEFAULT NULL, `address_child_pcode` varchar(8) DEFAULT NULL, `mothers_first_name` varchar(32) DEFAULT NULL, `mothers_last_name` varchar(32) DEFAULT NULL, `fathers_first_name` varchar(32) DEFAULT NULL, `fathers_last_name` varchar(32) DEFAULT NULL, `phone_home` varchar(32) DEFAULT NULL, `phone_mobile` varchar(32) DEFAULT NULL, `email` varchar(128) DEFAULT NULL, `is_health_care_card_holder` tinyint(1) DEFAULT NULL, `application_fee` varchar(4) DEFAULT NULL, `is_offer_made` tinyint(1) DEFAULT NULL, `is_offer_rejected` tinyint(1) DEFAULT NULL, `date_accepted` date DEFAULT NULL, `sex_of_child` varchar(1) DEFAULT NULL, `is_boroondara_resident` tinyint(1) DEFAULT NULL, PRIMARY KEY (`file_nbr`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `pupil` -- /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */; /*!40101 SET SQL_MODE=@OLD_SQL_MODE */; /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */; /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */; /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */; /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */; /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */; -- Dump completed on 2009-11-26 23:36:26
@ timrichardson I forgot to ask: Under what OS, this happens? If linux, than you use OOo from Debian source or downloaded from OOo site? JDBC version?
Debian Sid. Using the packaged OOo so whatever is the latest jdbc
Changing MySQL database structures in OOo, not supported well, instead it use MySQL own tools for this type of work. Use OOo as data input and output only. Debian builds own OOo version, submit your bug in their bugtracker. Install OOo from openoffice.org site and check the issue.
I tested it in a DEV300m64 and it works. I remember that we fixed that in OOO320 also. Could some test it in that version as well? @timrichardson: You may correct me if I understand something wrong. - Create new table - Insert some data - Create a sort order (just by pressing the Z->A button in data view) - Close data view - D&D table -> copy wizard appears - Press finish - Open newly created table => The sort order should still be working
I don't recall creating a sort order. I think the problem is the sort order associated with the primary key, which keeps the name of the original table. The consequence is that the table can not be opened (in data edit mode)
This Issue requires more information ('needmoreinfo'), but has not been updated within the last year. Please provide feedback as requested and re-test with the the latest version of OpenOffice - the problem(s) may already be addressed. You can download Apache OpenOffice 3.4.1 from http://www.openoffice.org/download Please report back the outcome of your testing, so this Issue may be closed or progressed as necessary - otherwise the issue may be Resolved as Invalid in the future.