Below is the list of changes that have just been committed into a local
mysqldoc repository of pgulutzan. When pgulutzan does a push these
changes will
be propagated to the main repository and, within 24 hours after the
push, to the public repository.
For information on how to access the public repository
see http://www.mysql.com/doc/I/n/Installing_source_tree.html
ChangeSet
1.3134 05/07/26 19:31:38 peterg@stripped +1 -0
Updated data-type sections, cleaned elsewhere.
internals/internals.xml
1.14 05/07/26 19:31:37 peterg@stripped +147 -275
Updated data-type sections, cleaned elsewhere.
# This is a BitKeeper patch. What follows are the unified diffs for the
# set of deltas contained in the patch. The rest of the patch, the part
# that BitKeeper cares about, is below these diffs.
# User: peterg
# Host: d-142-59-78-116.abhsia.telus.net
# Root: /home/pgulutzan/mysqldoc
--- 1.13/internals/internals.xml 2005-07-25 18:37:27 -06:00
+++ 1.14/internals/internals.xml 2005-07-26 19:31:37 -06:00
@@ -419,7 +419,6 @@
Coding Guidelines
The Optimizer
Important Algorithms and Structures
-The different column types in MySQL
How MySQL Performs Different Selects
How MySQL Transforms Subqueries
MySQL Client/Server Protocol
@@ -5603,69 +5602,12 @@
<!-- This section written by Ingo Struewing -->
<para>
- A quotation from the MySQL Reference Manual regarding numeric
- types:
+ The MySQL Reference Manual has a discussion of floating-point
+ numbers in Section 11.2 Numeric Types, including details about
+ the storage. Let us now take up the story from where the
+ MySQL Reference Manual leaves off.
</para>
-
- <para>
- "12.2 Numeric Types ... For floating-point column types, MySQL
- uses four bytes for single-precision values and eight bytes for
- double-precision values.
- </para>
-
- <para>
- The <literal>FLOAT</literal> type is used to represent
- approximate numeric data types. The SQL standard allows an
- optional specification of the precision (but not the range of
- the exponent) in bits following the keyword
- <literal>FLOAT</literal> in parentheses. The MySQL
- implementation also supports this optional precision
- specification, but the precision value is used only to
determine
- storage size. A precision from 0 to 23 results in four-byte
- single-precision <literal>FLOAT</literal> column. A precision
- from 24 to 53 results in eight-byte double-precision
- <literal>DOUBLE</literal> column.
- </para>
-
- <para>
- When the keyword <literal>FLOAT</literal> is used for a column
- type without a precision specification, MySQL uses four bytes
to
- store the values. MySQL also supports variant syntax with two
- numbers given in parentheses following the
- <literal>FLOAT</literal> keyword. The first number represents
- the display width and the second number specifies the number of
- digits to be stored and displayed following the decimal point
- (as with DECIMAL and NUMERIC). When MySQL is asked to store a
- number for such a column with more decimal digits following the
- decimal point than specified for the column, the value is
- rounded to eliminate the extra digits when the value is stored.
- </para>
-
- <para>
- In standard SQL, the <literal>REAL</literal> and
<literal>DOUBLE
- PRECISION</literal> types do not accept precision
- specifications. MySQL supports a variant syntax with two
numbers
- given in parentheses following the type name. The first number
- represents the display width and the second number specifies
the
- number of digits to be stored and displayed following the
- decimal point. As an extension to the SQL standard, MySQL
- recognizes <literal>DOUBLE</literal> as a synonym for the
- <literal>DOUBLE PRECISION</literal> type. In contrast with the
- standard's requirement that the precision for
- <literal>REAL</literal> be smaller than that used for
- <literal>DOUBLE PRECISION</literal>, MySQL implements both as
- eight-byte double-precision floating-point values (unless the
- server SQL mode includes the <literal>REAL_AS_FLOAT</literal>
- option).
- </para>
-
- <para>
- For maximum portability, code requiring storage of approximate
- numeric data values should use <literal>FLOAT</literal> or
- <literal>DOUBLE PRECISION</literal> with no specification of
- precision or number of decimal points."
- </para>
-
+
<para>
The following discussion concentrates on the case where no
display width and decimals are given. This means that
@@ -5703,12 +5645,12 @@
</programlisting>
<para>
- If the <literal>libc</literal> conversion brings too much
+ If the <literal>libc</literal> conversion produces too many
characters, the precision is reduced by the space required for
the scientific notation (1.234e+05). Thus the
<literal>printf()</literal> conversion is forced to switch to
the scientific notation, since the value would not fit
- otherwise. Or, if it was scientific already, the precision was
+ otherwise. Or, if it was scientific already, the precision is
reduced and also uses less space. I left out some important
stuff around limit checking just to show the idea. This simple
algorithm should work quite well in most cases, but has been
@@ -5721,15 +5663,15 @@
converted into small fields. Remeber that I talk only of
conversions where field length and precision are not given. In
this case a sufficient field length is selected at several
- places, except of a bug where it was selected wrongly. If a
+ places, except for a bug where it was selected wrongly. If a
field length is given, a different conversion is used anyway.
- But since the code is quite complex, I don't claim to have the
- full insigth and as such may be in error. Hence, let us look
+ But since the code is quite complex, I don't claim to grasp
+ it in full, and therefore may be in error. So let us look
further:
</para>
<para>
- The second attempt in the bugfix was like this:
+ The second attempt to fix the bug looked like this:
</para>
<programlisting>
@@ -5769,8 +5711,8 @@
completely absurd: 0.699999988079071044921875. To understand
more about the <literal>%g</literal> conversion, I quote from a
comment introduced in the source at the beginning of bugfixing
- #4937 (this has been removed later, since it mainly describes,
- how the <literal>printf()</literal> conversion works. I think
+ #4937 (this comment was removed because it mainly describes,
+ how the <literal>printf()</literal> conversion works, but I
think
it's valuable enough to include it here):
</para>
@@ -5790,12 +5732,12 @@
specific C define for maximum number of decimal digits of a floating
point number, such that rounding to hardware floating point
representation and back to decimal will not lead to loss of
- precision. I.e if DBL_DIG is 15, number 123456789111315 can be
+ precision. That is: if DBL_DIG is 15, number 123456789111315 can be
represented as double without precision loss. As one can judge from
- this description, chosing DBL_DIG here is questionable, especially
+ this description, choosing DBL_DIG here is questionable, especially
because it introduces a system dependency.
'g' means that conversion will use [-]ddd.ddd (conventional) style,
- and fall back to [-]d.ddde[+|i]ddd (scientific) style if there is no
+ and fall back to [-]d.ddde[+|i]ddd (scientific) style if there is not
enough space for all digits.
Maximum length of result string (not counting spaces) is (I guess)
DBL_DIG + 8, where 8 is 1 for sign, 1 for decimal point, 1 for
@@ -5840,7 +5782,7 @@
numbers as 1 bit for sign, 8 bits for biased exponent and 23
bits for fraction and for double precision numbers as 1-bit
sign, 11-bit biased exponent and 52-bit fraction. However, we
- can not rely in the fact that every system uses this
+ can not rely on the fact that every system uses this
representation. Luckily, the ISO C standard requires the
standard C library to have a header
<filename>float.h</filename>
that describes some details of the floating point
representation
@@ -5859,8 +5801,8 @@
took <literal>DBL_DIG</literal> into account, if however only
for the second call to <literal>sprintf()</literal>. But
<literal>FLT_DIG</literal> has never been accounted for. At the
- place of the conversions it was not even known, if the value
has
- come from a <literal>float</literal> or
+ conversion section of the code, it was not even known whether
the value
+ came from a <literal>float</literal> or
<literal>double</literal> field.
</para>
@@ -5872,21 +5814,21 @@
<literal>float</literal> versus <literal>double</literal> to
this function wherever it is called. This solution managed to
keep the test suite happy while solving the new problem of Bug
- #4457. Luckily, the first was not a big problem, as the test
+ #4457. Luckily the first problem was not big, as the test
cases have been very carefully selected, so that they succeed
as
- long as the machine uses IEEE 754 ;).
+ long as the machine uses IEEE 754.
</para>
<para>
Nevertheless, the function is still not perfect. It is not
- possible to guess how many sigificant digits a number has, and
- as such it is not simple to tell how long the resulting string
+ possible to guess how many sigificant digits a number has.
Given
+ that, it is not simple to tell how long the resulting string
would be. This applies to numbers with an absolute value
smaller
then 1. There are probably ways to figure this out, but I doubt
that we would win in terms of performance over the simple
- solution of the first attempt, let alone the chance for new
+ solution of the first attempt, and besides we might cause new
bugs. The compromise taken here is to accept that the resulting
- string may exceed the destined field length by five characters
+ string may exceed the destination field length by five
characters
in the worst case.
</para>
@@ -5962,7 +5904,7 @@
limiting numbers arrays only once into static space. It copes
with negative numbers and tries to decide even over small
numbers. The latter has only small implications, as the prefix
- 0.000 is exactly the same size as the postfix e-100. But to
know
+ 0.000 is exactly the same size as the postfix e-100. But
knowing
if scientific notation will be selected by
<literal>sprintf()</literal> allows for saving one digit when
the exponent is larger than -100.
@@ -5976,16 +5918,16 @@
combinations which exploit the gap, but these won't emerge
anyway as I found no situation where this function is called
with small field lengths. Remember again that it is not called
- with user supplied field lengths.
+ with user-supplied field lengths.
</para>
<para>
However in the current stable releases (including gamma) we
have
some places where the field length is too small by one
- character. Thus, the precision is sometimes by one digit
smaller
+ character. Thus, the precision is sometimes one digit smaller
than <literal>DBL_DIG</literal> would allow for. Consequently,
we cannot use the simple algorithm in the stable releases.
There
- is a chance for doing it in a development release, though.
+ is a chance of doing it in a development release, though.
</para>
<para>
@@ -5996,7 +5938,7 @@
There turned out to be a new solution to the "big number array"
problem. We have a statically initialized array
<literal>log_10</literal>, which holds the necessary values.
But
- I did not check, if these values are safe. Even if computed by
+ I did not check whether these values are safe. Even if computed
by
the compiler, they could carry values slightly above the
decimal
powers, which would be bad. In this case we needed to
initialize
by 9.99999999e+xxx, where the number of nines is equal to
@@ -6012,7 +5954,7 @@
But all of this is probably completely unneccessary, since we
are only speaking of ceses where no user-supplied field length
is given. So MySQL selects the field length on its own. So it
is
- totally possible, yes highly desired that MySQL selects a field
+ totally possible, indeed highly desirable, that MySQL selects a
field
length, which allows for a maximum of precision for all
possible
values. And these are <literal>DBL_DIG+7</literal> or
<literal>FLT_DIG+6</literal> respectively as far as IEEE 754 is
@@ -6027,7 +5969,7 @@
for a simple <literal>sprintf(buff, "%.*g", DBL_DIG,
nr)</literal> or <literal>sprintf(buff, "%.*g", FLT_DIG,
nr)</literal>, respectively. To be safe, we should not use the
- machine dependend constants everywhere, but instead concentrate
+ machine dependent constants everywhere, but instead concentrate
them into definitions like these:
</para>
@@ -6053,15 +5995,15 @@
These definitions should be used wherever an item or field of
type <literal>float</literal> or <literal>double</literal>
without an explicit field length specification is encountered.
- We have to propagate these lengths though all deviated items
and
- fields and to select the maximum of all field lengths wherever
- in an expression or function two or more of them are used.
+ We have to propagate these lengths though all derived items and
+ fields and we have to select the maximum of all field lengths
wherever
+ in two or more of them are used in an expression or a function.
</para>
<para>
We need to treat the precision
(<literal>DBL_DIG</literal>/<literal>FLT_DIG</literal>)
- similarly, but have to select the smallest in expressions or
+ similarly, but have to select the minimum in expressions or
functions.
</para>
@@ -6653,179 +6595,6 @@
</chapter>
- <chapter id="mysql-column-types">
-
- <title id="title-mysql-column-types">The different column types in
MySQL</title>
-
- <para>
- This needs to be extended...
- </para>
-
- <section id="varchar">
-
- <title id="title-varchar">VARCHAR type</title>
-
- <itemizedlist>
-
- <listitem>
- <para>
- In old tables (from MySQL 4.1 and earlier),
- <literal>VARCHAR</literal> columns have type
- <literal>MYSQL_TYPE_VAR_STRING</literal>, which works
- exactly like a <literal>CHAR</literal> with the exception
- that if you do an <literal>ALTER TABLE</literal>, it's
- converted to a true <literal>VARCHAR</literal>
- (<literal>MYSQL_TYPE_VARCHAR</literal>). (This means that
- old tables will work as before for users)
- </para>
- </listitem>
-
- <listitem>
- <para>
- Apart from the above case, there is not anymore any
- automatic changes from <literal>CHAR</literal> to
- <literal>VARCHAR</literal> or from
- <literal>VARCHAR</literal> to <literal>CHAR</literal>.
MySQL
- will remember the used type and stick to it.
- </para>
- </listitem>
-
- <listitem>
- <para>
- <literal>VARCHAR</literal> is implemented in
- <filename>field.h</filename> and
- <filename>field.cc</filename> through the new class
- <literal>Field_varstring</literal>.
- </para>
- </listitem>
-
- <listitem>
- <para>
- <literal>MyISAM</literal> implements
- <literal>VARCHAR</literal> both for dynamic-length and
- fixed-length rows (as signaled with the
- <literal>ROW_FORMAT</literal> flag)
- </para>
- </listitem>
-
- <listitem>
- <para>
- The main difference between <literal>VARCHAR</literal> and
- <literal>CHAR</literal> is that <literal>VARCHAR</literal>
- stores end space. (If end space doesn't fit into the
column,
- this is an error in strict mode). Note that end space are
- not significant in comparisons or for unique. In this case,
- <literal>VARCHAR</literal> works just like
- <literal>CHAR</literal>.
- </para>
- </listitem>
-
- <listitem>
- <para>
- In <literal>table->record</literal>, there is reserved a
- fixed amount of space for the varchar lengths (1 or 2
bytes)
- + data.
- </para>
- </listitem>
-
- <listitem>
- <para>
- If <literal>VARCHAR</literal> storage length <= 255,
then
- we use 1-byte length, else a 2-byte length. This gives us
- 65535 as a maximum size for <literal>VARCHAR</literal> (for
- multi-byte character sets, the maximum size is of course
- smaller).
- </para>
- </listitem>
-
- <listitem>
- <para>
- The number of bytes used to store the length is in the
field
- <literal>Field_varchar->length_bytes</literal>. Note
that
- internally this can be 2 even if
- <literal>Field_varchar->field_length</literal> < 256
- (for example, for a shortened key to a
- <literal>varchar(256)</literal>).
- </para>
- </listitem>
-
- <listitem>
- <para>
- There is a new macro,
- <literal>HA_VARCHAR_PACKLENGTH(field_length)</literal>,
that
- can be used on <literal>field->length</literal> in
- write_row / read_row to check how many length bytes are
- used. (In this context we can't have a field_length <
256
- with a 2-byte pack length)
- </para>
- </listitem>
-
- <listitem>
- <para>
- When creating a key for the handler,
- <literal>HA_KEYTYPE_VARTEXT1</literal> and
- <literal>HA_KEYTYPE_BINARY1</literal> are used for a key on
- a column that has a 1-byte length prefix and
- <literal>HA_KEYTYPE_VARTEXT2</literal> and
- <literal>HA_KEYTYPE_BINARY2</literal> for a column that has
- a 2-byte length prefix. (In the future we will probably
- delete <literal>HA_KEYTYPE_BINARY#</literal>, as this can
be
- instead be done by just using the <literal>binary</literal>
- character set with <literal>HA_KEYTYPE_VARTEXT#</literal>)
- </para>
- </listitem>
-
- <listitem>
- <para>
- When sending a key to the handler for
- <literal>index_read()</literal> or records_in_range, we
- always use a 2-byte length for the
- <literal>VARCHAR</literal> to make things simpler. (The
- intention is in 5.1 change <literal>CHAR</literal>'s to
also
- use a 2-byte length for these functions, as this will speed
- up and simplify the key handling code on the handler side)
- </para>
- </listitem>
-
- <listitem>
- <para>
- The test case file
- <filename>mysql-test/include/varchar.inc</filename> should
- be included in the code that tests the handler. See
- <filename>t/myisam.test</filename> for how to use this. You
- should verify the result against the one in
- <filename>mysql-test/t/myisam.result</filename> to ensure
- that you get the correct results.
- </para>
- </listitem>
-
- <listitem>
- <para>
- A client sees both the old and new
- <literal>VARCHAR</literal> type as
- <literal>MYSQL_TYPE_VAR_STRING</literal>. It will never (at
- least for 5.0) see <literal>MYSQL_TYPE_VARCHAR</literal>.
- This is to ensure that old clients will work as before.
- </para>
- </listitem>
-
- <listitem>
- <para>
- If you run MySQL 5.0 with the <literal>--new</literal>
- option, MySQL will show old <literal>VARCHAR</literal>
- columns as <literal>'CHAR'</literal> in <literal>SHOW
CREATE
- TABLE</literal>. (This is useful when testing whether a
- table is using the new <literal>VARCHAR</literal> type or
- not).
- </para>
- </listitem>
-
- </itemizedlist>
-
- </section>
-
- </chapter>
-
<chapter id="selects">
<title id="title-selects">How MySQL Performs Different
Selects</title>
@@ -12117,15 +11886,14 @@
<section id="replication-plans">
- <title id="title-replication-plans">Plans for MySQL 5.0</title>
+ <title id="title-replication-plans">Plans</title>
<para>
- We plan (Guilhem is presently working on it) to add more
- information in the events. For example, the number of rows the
- query modified. We also plan to have a more dynamic binlog
- format, i.e. if we need to add 1 more byte in the header,
- replication between an old and a new server would still be
- possible.
+ We have already made extensive changes to the above in MySQL
5.0.
+ For an upcoming version we plan a new format where data is
replicated by row
+ instead of by statement. This makes it possible to replicate
data
+ from one MySQL Cluster to another. We also plan new
functionality
+ to handle replication connections from multiple masters into
one slave.
</para>
</section>
@@ -12487,6 +12255,89 @@
</para>
</listitem>
+ <listitem>
+ <para>
+ In MySQL 4.1 the length is always 1 byte. In MySQL
+ 5.0 the length may be either 1 byte (for up to 255)
+ or 2 bytes (for 256 to 65535).
+ Some further random notes about the new format:
+ In old tables (from MySQL 4.1 and earlier),
+ <literal>VARCHAR</literal> columns have type
+ <literal>MYSQL_TYPE_VAR_STRING</literal>, which works
+ exactly like a <literal>CHAR</literal> with the
exception
+ that if you do an <literal>ALTER TABLE</literal>,
it's
+ converted to a true <literal>VARCHAR</literal>
+ (<literal>MYSQL_TYPE_VARCHAR</literal>). (This means
that
+ old tables will work as before for users.) ...
+ Apart from the above case, there are no longer any
+ automatic changes from <literal>CHAR</literal> to
+ <literal>VARCHAR</literal> or from
+ <literal>VARCHAR</literal> to
<literal>CHAR</literal>. MySQL
+ will remember the declared type and stick to it ...
+ <literal>VARCHAR</literal> is implemented in
+ <filename>field.h</filename> and
+ <filename>field.cc</filename> through the new class
+ <literal>Field_varstring</literal> ...
+ <literal>MyISAM</literal> implements
+ <literal>VARCHAR</literal> both for dynamic-length
and
+ fixed-length rows (as signaled with the
+ <literal>ROW_FORMAT</literal> flag) ...
+ <literal>VARCHAR</literal> now stores trailing
spaces.
+ (If they don't fit, that's an error in strict mode.)
+ Trailing spaces are not significant in
comparisons ...
+ In <literal>table->record</literal>, the space is
+ reserved for length (1 or 2 bytes) plus data ...
+ The number of bytes used to store the length is in
the field
+ <literal>Field_varchar->length_bytes</literal>.
Note that
+ internally this can be 2 even if
+ <literal>Field_varchar->field_length</literal>
< 256
+ (for example, for a shortened key to a
+ <literal>varchar(256)</literal>) ...
+ There is a new macro,
+
<literal>HA_VARCHAR_PACKLENGTH(field_length)</literal>, that
+ can be used on <literal>field->length</literal> in
+ write_row / read_row to check how many length bytes
are
+ used. (In this context we can't have a field_length
< 256
+ with a 2-byte pack length) ...
+ When creating a key for the handler,
+ <literal>HA_KEYTYPE_VARTEXT1</literal> and
+ <literal>HA_KEYTYPE_BINARY1</literal> are used for a
key on
+ a column that has a 1-byte length prefix and
+ <literal>HA_KEYTYPE_VARTEXT2</literal> and
+ <literal>HA_KEYTYPE_BINARY2</literal> for a column
that has
+ a 2-byte length prefix. (In the future we will
probably
+ delete <literal>HA_KEYTYPE_BINARY#</literal>, as this
can be
+ instead be done by just using the
<literal>binary</literal>
+ character set with
<literal>HA_KEYTYPE_VARTEXT#</literal>.) ...
+ When sending a key to the handler for
+ <literal>index_read()</literal> or records_in_range,
we
+ always use a 2-byte length for the
+ <literal>VARCHAR</literal> to make things simpler.
(For
+ version 5.1 we intend to change
<literal>CHAR</literal>s to also
+ use a 2-byte length for these functions, as this will
speed
+ up and simplify the key handling code on the handler
side.) ...
+ The test case file
+ <filename>mysql-test/include/varchar.inc</filename>
should
+ be included in the code that tests the handler. See
+ <filename>t/myisam.test</filename> for how to use
this. You
+ should verify the result against the one in
+ <filename>mysql-test/t/myisam.result</filename> to
ensure
+ that you get the correct results ...
+ A client sees both the old and new
+ <literal>VARCHAR</literal> type as
+ <literal>MYSQL_TYPE_VAR_STRING</literal>. It will
never (at
+ least for 5.0) see
<literal>MYSQL_TYPE_VARCHAR</literal>.
+ This ensures that old clients will work as before ...
+ If you run MySQL 5.0 with the
<literal>--new</literal>
+ option, MySQL will show old
<literal>VARCHAR</literal>
+ columns as <literal>'CHAR'</literal> in <literal>SHOW
CREATE
+ TABLE</literal>. (This is useful when testing whether
a
+ table is using the new <literal>VARCHAR</literal>
type or
+ not.)
+ </para>
+ </listitem>
+
+
</itemizedlist>
</para>
</listitem>
@@ -12687,7 +12538,7 @@
<listitem>
<para>
- Storage: fixed-length string, with a leading byte for
+ MySQL 4.1 Storage: fixed-length string, with a
leading byte for
the sign, if any.
</para>
</listitem>
@@ -12716,6 +12567,27 @@
column containing <literal>65</literal> looks like:
<literal>hexadecimal 36 35 2E 30 30</literal> --
(length = 5, value = <literal>'65.00'</literal>)
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ MySQL 5.0 Storage: high byte first, four-byte chunks.
+ We call the four-byte chunks "*decimal*
+ digits". Since 2**32 = 4294967296, one *decimal*
+ digit can hold values up to 10**9 (999999999), and
+ two *decimal* digits can hold values up to 10**18,
+ and so on. There is an implied decimal point.
+ Details are in /strings/decimal.c.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ Example: a MySQL 5.0 <literal>DECIMAL(21,9)</literal>
+ column conaining
<literal>111222333444.555666777</literal> looks like:
+ <literal>hexadecimal 80 6f 0d 40 8a 04 21 1e cd
59</literal> --
+ (flag + '111', '222333444', '555666777').
</para>
</listitem>
| Thread |
|---|
| • bk commit - mysqldoc@docsrva tree (peterg:1.3134) | Peter Gulutzan | 27 Jul |
