| Bug #21263 | mysql client XML output does not distinguish between NULL and string 'NULL' | ||
|---|---|---|---|
| Submitted: | 25 Jul 2006 3:02 | Modified: | 8 Oct 2006 6:47 |
| Reporter: | joe barillari | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: XML functions | Severity: | S3 (Non-critical) |
| Version: | 5.0.22 | OS: | Linux (Debian Linux) |
| Assigned to: | Alexander Barkov | CPU Architecture: | Any |
| Tags: | client, null, XML | ||
[25 Jul 2006 6:27]
Valeriy Kravchuk
Thank you for a bug report and patch. Verified just as described. The problem is obvious in results of simple SELECT * FROM foo. NULL and 'NULL' are "visually" the same, and --xml option just presents them in the same way. While it can be OK for usual presentation of results, it is a problem for XML.
[9 Aug 2006 10:23]
Roland Bouman
The SQL Standard actually has a solution for this. There are two different methods to cope, dependant upon a global setting. 1) Omit NULL elements. 2) Mark them with a xsi:nil="true" attribute. This may be checked in: ISO 9075-14:2003, 4.8.4 "Mapping values of SQL data types to XML", p16: "As for null values, there is generally a choice of whether to represent nulls using absence or xsi:nil="true"." and, in 9.16, p 123 the full account is given of the mapping to XML values. It is not clear if the choice for representation is a choice in implementation or a choice that the user can influence runtime.
[13 Sep 2006 5:35]
Alexander Barkov
After discussion with the team, decided to dump null values as: <field name="name" xsi:nil="true" />
[13 Sep 2006 5:45]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/11814 ChangeSet@1.2236, 2006-09-13 10:35:27+05:00, bar@mysql.com +2 -0 Bug#21263 mysql client XML output does not distinguish between NULL and string 'NULL' Fix: "mysql --xml" now print NULL values the same way that "mysqldump --xml" does: <field name="name" xsi:nil="true" /> to distinguish from empty strings: <field name="name"></field> and from string "NULL": <field name="name">NULL</field>
[29 Sep 2006 11:30]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/12833 ChangeSet@1.2284, 2006-09-29 16:29:39+05:00, bar@mysql.com +2 -0 Bug#21263 mysql client XML output does not distinguish between NULL and string 'NULL' Fix: "mysql --xml" now print NULL values the same way that "mysqldump --xml" does: <field name="name" xsi:nil="true" /> to distinguish from empty strings: <field name="name"></field> and from string "NULL": <field name="name">NULL</field>
[3 Oct 2006 20:02]
Chad MILLER
Available in 5.0.26.
[3 Oct 2006 20:15]
Chad MILLER
Available in 5.1.12-beta.
[8 Oct 2006 6:47]
Jon Stephens
Thank you for your bug report. This issue has been committed to our source repository of that product and will be incorporated into the next release.
If necessary, you can access the source repository and build the latest available version, including the bug fix. More information about accessing the source trees is available at
http://dev.mysql.com/doc/en/installing-source.html
Noted previously undocumented --xml behaviour for mysql and mysqldump in 5.0/5.1 Client and Utility Programs chapter of Manual.
Documented bugfix in 5.0.26 & 5.1.12 changelogs.
Description: The --xml option to the MySQL command line client produces identical output for a field containing the string 'NULL' and a NULL-valued field. This makes it impossible to reconstruct a table from the XML dump. How to repeat: mysql> drop table foo; Query OK, 0 rows affected (0.00 sec) mysql> create table foo (bar varchar(32), baz int); Query OK, 0 rows affected (0.00 sec) mysql> insert into foo (bar,baz) values ('NULL', 1); Query OK, 1 row affected (0.00 sec) mysql> insert into foo (bar,baz) values (NULL, 2); Query OK, 1 row affected (0.00 sec) mysql> select * from foo where bar LIKE '%U%'; +------+------+ | bar | baz | +------+------+ | NULL | 1 | +------+------+ 1 row in set (0.00 sec) Note that there are two rows in the table, but only one shows up here (because bar is the string 'NULL'. In the other row, bar is NULL-valued. Here's how it manifests itself in the client: $ echo "select * from foo;" | mysql jdb --xml <?xml version="1.0"?> <resultset statement="select * from foo"> <row> <field name="bar">NULL</field> <field name="baz">1</field> </row> <row> <field name="bar">NULL</field> <field name="baz">2</field> </row> </resultset> Note that there is no way to tell that in row 1, bar is the string 'NULL', and in row two, bar is NULL-valued. Suggested fix: A small patch to mysql.cc fixes the problem. When a field is NULL, the client now adds the attribute 'isnull="yes"' to the field tag. Properly-designed parsers expecting the old format should not break, as they won't be looking for this attribute. New parsers can use it to distinguish between 'NULL' and the value NULL. Here's the unified diff of the patch. --- mysql.cc.orig 2006-05-25 04:56:46.000000000 -0400 +++ mysql.cc 2006-07-24 20:38:24.000000000 -0400 @@ -2448,6 +2448,9 @@ { tee_fprintf(PAGER, "\t<field name=\""); xmlencode_print(fields[i].name, (uint) strlen(fields[i].name)); + if (!cur[i]) { + tee_fprintf(PAGER, "\" isnull=\"yes"); + } tee_fprintf(PAGER, "\">"); xmlencode_print(cur[i], lengths[i]); tee_fprintf(PAGER, "</field>\n"); With the fix installed, the xml from the box above looks like this: $ echo "select * from foo;" | mysql jdb --xml <?xml version="1.0"?> <resultset statement="select * from foo"> <row> <field name="bar">NULL</field> <field name="baz">1</field> </row> <row> <field name="bar" isnull="yes">NULL</field> <field name="baz">2</field> </row> </resultset> Note the "isnull" indicating that the 'bar' column in the second row is null-valued, not the string 'NULL'.