| Bug #2657 | Function geomFromWKB() inserts NULL for some values | ||
|---|---|---|---|
| Submitted: | 5 Feb 2004 16:00 | Modified: | 19 Feb 2004 23:15 |
| Reporter: | ryan proulx | Email Updates: | |
| Status: | Can't repeat | Impact on me: | |
| Category: | MySQL Server | Severity: | S1 (Critical) |
| Version: | 4.1.1 Alpha | OS: | Windows (WinXP) |
| Assigned to: | Alexey Botchkov | CPU Architecture: | Any |
[6 Feb 2004 7:25]
Alexander Keremidarski
I got slighlty different result than reported:
mysql> insert into canada_cities values('Calgary','N','Alberta','2000',geomfromtext('POINT(-114.050003100000000 51.029998800000001)'));
Query OK, 1 row affected (0.00 sec)
mysql> insert into canada_cities values('Vancouver','N','British Columbia','2000',geomfromwkb('POINT(-123.0699997 49.1599998)'));
ERROR 1048 (23000): Column 'geom' cannot be null
mysql> insert into canada_cities values('Calgary','N','Alberta','2000',geomfromwkb('0101000000B6CC334033835CC0AA752C00D7834940'));
ERROR 1048 (23000): Column 'geom' cannot be null
[6 Feb 2004 7:58]
ryan proulx
Read the bug more carefully and you will see that I reported that result as well when using the mysql command line. The place where I have issues is when using the C API to call mysql_real_query which overwrides the NOT NULL property of the column and writes the NULL anyway. Whether it overwrites NULL or not the issues is it does not give me the point I desire. The value should not be NULL.
[6 Feb 2004 8:03]
ryan proulx
I suppose I should mention that when using the C API I am not actually inserting a hex encoded string but rather an ascii encoded binary string containing the WKB values. but how would I show you the values if I did not write them in hex? From the command line I did write the values using hex as per the example given.
[6 Feb 2004 8:05]
ryan proulx
I appologize, I see the issue. The second insert statement you ran should be 'geomfromtext' adn not 'geomfromwkb'. My mistake.
[18 Feb 2004 1:13]
Alexey Botchkov
Sorry, but the bug system is not the appropriate forum for asking
support questions. Your problem is not the result of a bug.
For a list of more appropriate places to ask for help using MySQL
products, please visit http://www.mysql.com/support/
Thank you for your interest in MySQL.
Additional info:
geomfromwkb parameter just wasn't specified right.
Should be:
insert into canada_cities values('Calgary','N','Alberta','2000',
geomfromwkb(x'0101000000B6CC334033835CC0AA752C00D7834940'));
or
insert into canada_cities values('Calgary','N','Alberta','2000',
-> geomfromwkb(0x0101000000B6CC334033835CC0AA752C00D7834940));
[18 Feb 2004 12:03]
ryan proulx
I suppose there has been a misunderstanding here. I am NOT trying to use geomfromwkb() with a hex string as the argument. I am trying to write the ascii string, but in order to give you the value of the string that fails I have converted it to hex for your replication of the result I received. You will have to CONVERT THE HEX STRING BACK TO BINARY before calling the function. I appologize for not being so explicit in the 'steps to reproduce' section. Then hopefully you can see teh error I receive.
[19 Feb 2004 4:35]
Lenz Grimmer
So I re-open this report, as it still seems to be unresolved
[19 Feb 2004 23:15]
Alexey Botchkov
Not enough information was provided for us to be able to handle this bug. Please re-read the instructions at http://bugs.mysql.com/how-to-report.php If you can provide more information, feel free to add it to this bug and change the status back to 'Open'. Thank you for your interest in MySQL. Additional info: Could you give an example of your program then? Now i only can suspect you didn't 'escape' your string properly. Here's my one, it works well: unsigned char obj_2657[]= { 0x01, 0x01, 0x00, 0x00, 0x00, 0xB6, 0xCC, 0x33, 0x40, 0x33, 0x83, 0x5C, 0xC0, 0xAA, 0x75, 0x2C, 0x00, 0xD7, 0x83, 0x49, 0x40}; void test_2657(MYSQL *mysql) { char query[1000]; char *qn= query; char estr[100]; unsigned int estr_len; strcpy(query, "INSERT INTO canada_cities VALUES 'Calgary', 'N', 'Alberta', '2000', geomfromwkb('"); qn+= strlen(query); estr_len= mysql_real_escape_string(mysql, estr, obj_2657, sizeof(obj_2657)); memcpy(qn, estr, estr_len); qn+= estr_len; strcpy(qn, "'))"); qn+= 3; if (mysql_real_query(mysql, query, qn-query)) printf("%s\n", mysql_error(mysql)); } Regards HF
Description: GeomFromWKB() inserts NULL values instead of inserting the correct geometry for some point (and likely other) geometries. GeomFromWKT() does work. For Example - POINT(-114.050003100000000 51.029998800000001) in little endian should equal - 0101000000B6CC334033835CC0AA752C00D7834940 However, inserting GeomFromWKB('0101000000B6CC334033835CC0AA752C00D7834940') into a geometry column results in a NULL value, despite the column being marked NOT NULL. If I manually (mysql command line) instead of programmatically try to insert the value, I get the error: 'ERROR 1048 (23000): Column 'geom' cannot be null'. In total 4 points of 25 do not work: POINT(-114.0500031 51.0299988) POINT(-113.2799988 53.3300018) POINT(-114.0746994 62.5131302) POINT(-52.6848717 47.5695686) 0101000000B6CC334033835CC0AA752C00D7834940 0101000000D53A1680EB515CC0814FBD7F3DAA4A40 0101000000F901FEDFC7845CC083CB1940AE414F40 01010000005CBA38E0A9574AC00CEAB69FE7C84740 How to repeat: From SHOW CREATE TABLE: CREATE TABLE `canada_cities` ( `name` varchar(59) default NULL, `capital` varchar(9) default NULL, `prov_name` varchar(33) default NULL, `population` double default NULL, `geom` geometry NOT NULL default '', KEY `geom` (`geom`(12)), KEY `canada_cities_name1076022572933` (`name`) ) TYPE=MyISAM DEFAULT CHARSET=latin1 INSERT WKT statement that works: insert into canada_cities values('Calgary','N','Alberta','2000',geom fromtext('POINT(-114.050003100000000 51.029998800000001)')); INSERT WKB statement that works: insert into canada_cities values('Vancouver','N','British Columbia','2000',geom fromwkb('POINT(-123.0699997 49.1599998)')); INSERT WKB statement that does NOT work: insert into canada_cities values('Calgary','N','Alberta','2000',geom fromwkb('0101000000B6CC334033835CC0AA752C00D7834940')); Suggested fix: Seems like a parsing issue and not a memory issue since the same fields consistently succeed or fail under repetition. Should look at WKB parsing in GeomFromWKB() function and perhaps the OGC Simple Features Spec again. For now use GeomFromWKT() as a work around even though it is slower.