3 ms·

Sometimes MySQL is more standards-compliant than PostgreSQL

1 points by chanux 13y ago

bsg75 13y ago
Re "Character Sets And Collations", VARCHAR means variable length. It does not mean "automatically strip blanks" SQL-92 8.2(3) [1]: 3) The comparison of two character strings is determined as follows: a) If the length in characters of X is not equal to the length in characters of Y, then the shorter string is effectively replaced, for the purposes of comparison, with a copy of itself that has been extended to the length of the longer string by concatenation on the right of one or more pad char- acters, where the pad character is chosen based on CS. If CS has the NO PAD attribute, then the pad character is an implementation-dependent character different from any char- acter in the character set of X and Y that collates less than any string under CS. Otherwise, the pad character is a <space>. b) The result of the comparison of X and Y is given by the col- lating sequence CS. c) Depending on the collating sequence, two strings may com- pare as equal even if they are of different lengths or con- tain different sequences of characters. When the operations MAX, MIN, DISTINCT, references to a grouping column, and the UNION, EXCEPT, and INTERSECT operators refer to character strings, the specific value selected by these operations from a set of such equal values is implementation-dependent. Other implementations: MSSQL has a padding option for this [2] Redbrick docs state "A character string stored in a VARCHAR column has exactly the same length as the source string or the expression that generated the string (including trailing blanks). Character strings stored in a VARCHAR column can vary in length" [3] [1] http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt [2] http://support.microsoft.com/kb/316626 http://support.microsoft.com/kb/316626 [3] http://publib.boulder.ibm.com/infocenter/rbhelp/v6r3/index.jsp?topic=%2Fcom.ibm.redbrick.doc6.3%2Fsqlrg%2Fsqlrg17.htm http://publib.boulder.ibm.com/infocenter/rbhelp/v6r3/index.j...
bsg75 13y ago
Wow, what a bunch of MySQL apologist fluff, and half-hearted attempts to prove the authors own point. For example: "PostgreSQL rolls back the entire transaction when it encounters a syntax error. MySQL only cancels the statement. Now, PostgreSQL is within its rights -- the standard says that an implementor may do an "implicit rollback" for an error." may do an implicit rollback - the standards do not even apply to this argument. You could replace PostgreSQL in this post with any of MSSQL, Oracle, DB2, or similar, and find standards that are applied in one but not the other.

1 points by chanux 13y ago

bsg75 13y ago

Re "Character Sets And Collations", VARCHAR means variable length. It does not mean "automatically strip blanks" SQL-92 8.2(3) [1]: 3) The comparison of two character strings is determined as follows: a) If the length in characters of X is not equal to the length in characters of Y, then the shorter string is effectively replaced, for the purposes of comparison, with a copy of itself that has been extended to the length of the longer string by concatenation on the right of one or more pad char- acters, where the pad character is chosen based on CS. If CS has the NO PAD attribute, then the pad character is an implementation-dependent character different from any char- acter in the character set of X and Y that collates less than any string under CS. Otherwise, the pad character is a <space>. b) The result of the comparison of X and Y is given by the col- lating sequence CS. c) Depending on the collating sequence, two strings may com- pare as equal even if they are of different lengths or con- tain different sequences of characters. When the operations MAX, MIN, DISTINCT, references to a grouping column, and the UNION, EXCEPT, and INTERSECT operators refer to character strings, the specific value selected by these operations from a set of such equal values is implementation-dependent. Other implementations: MSSQL has a padding option for this [2] Redbrick docs state "A character string stored in a VARCHAR column has exactly the same length as the source string or the expression that generated the string (including trailing blanks). Character strings stored in a VARCHAR column can vary in length" [3] [1] http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt [2] http://support.microsoft.com/kb/316626 http://support.microsoft.com/kb/316626 [3] http://publib.boulder.ibm.com/infocenter/rbhelp/v6r3/index.jsp?topic=%2Fcom.ibm.redbrick.doc6.3%2Fsqlrg%2Fsqlrg17.htm http://publib.boulder.ibm.com/infocenter/rbhelp/v6r3/index.j...

bsg75 13y ago

Wow, what a bunch of MySQL apologist fluff, and half-hearted attempts to prove the authors own point. For example: "PostgreSQL rolls back the entire transaction when it encounters a syntax error. MySQL only cancels the statement. Now, PostgreSQL is within its rights -- the standard says that an implementor may do an "implicit rollback" for an error." may do an implicit rollback - the standards do not even apply to this argument. You could replace PostgreSQL in this post with any of MSSQL, Oracle, DB2, or similar, and find standards that are applied in one but not the other.