https://bugs.freedesktop.org/show_bug.cgi?id=42400
Bug #: 42400
Summary: Query designer: numeric literal in condition on
(VAR)CHAR column destructively transformed into string
Classification: Unclassified
Product: LibreOffice
Version: LibO 3.4.3 release
Platform: All
OS/Version: All
Status: UNCONFIRMED
Severity: minor
Priority: low
Component: Database
AssignedTo: [email protected]
ReportedBy: [email protected]
Reproduced on 3.4.3 and master (3.5)
Open the following SQL query in the query designer:
SELECT col FROM tbl WHERE col = 8
where "col" is a VARCHAR column of table tbl. It is specific to the db system
whether this query is valid... E.g. in PostgreSQL it is not (error), but in
MySQL it is perfectly valid: a maximal prefix of col is converted to a number
and compared to 8.
Switch to design view.
The "Condition" row of the "col" column shows "8" (without the double quotes).
Switch back to SQL view. The query looks like:
SELECT col FROM tbl WHERE col = '8'
Switch back to design view, the "condition" row now reads "'8'" (without the
double quotes, with the single quotes).
The query was changed, and does not give the same result anymore... In MySQL
instead of returning col values "'8'", "'8 children'", ... it returns only col
value "'8'".
Slightly worse, now consider:
SELECT col FROM tbl WHERE col = 8.5
If one is in a locale where the decimal separator is "," (comma), the query
designer changes that to
SELECT col FROM tbl WHERE col = '8,5'
The dot becomes a comma, leading to a (very minor) data loss: without a copy of
the original query, it is not obvious whether the original query contained 8.5
or 8,5.
Just for complete clarity: I think it is good that if the user types "8" into
the query designer, it gets automatically converted to "'8'"; that's most
probably what he/she meant. This is about not changing the meaning of an
existing (working) query.
--
Configure bugmail: https://bugs.freedesktop.org/userprefs.cgi?tab=email
------- You are receiving this mail because: -------
You are the assignee for the bug.
_______________________________________________
Libreoffice-bugs mailing list
[email protected]
http://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs
