Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.lang.php > #15157 > unrolled thread

Problem creating a series of tables

Back to article view | Back to comp.lang.php

Contents

  Problem creating a series of tables richard <noreply@example.com> - 2015-03-31 12:14 -0400
    Re: Problem creating a series of tables Paul Herber <paul@pherber.com> - 2015-03-31 19:09 +0100
      Re: Problem creating a series of tables richard <noreply@example.com> - 2015-03-31 14:30 -0400
    Re: Problem creating a series of tables Denis McMahon <denismfmcmahon@gmail.com> - 2015-03-31 19:40 +0000
      Re: Problem creating a series of tables Jerry Stuckle <jstucklex@attglobal.net> - 2015-03-31 16:19 -0400
        Re: Problem creating a series of tables Richard Yates <richard@yatesguitar.com> - 2015-03-31 18:07 -0700
          Re: Problem creating a series of tables Jerry Stuckle <jstucklex@attglobal.net> - 2015-03-31 21:23 -0400
        Re: Problem creating a series of tables Erwin Moller <erwinmollerusenet@xs4all.nl> - 2015-04-01 12:19 +0200
          Re: Problem creating a series of tables Thomas 'PointedEars' Lahn <PointedEars@web.de> - 2015-04-02 15:23 +0200
            Re: Problem creating a series of tables Jerry Stuckle <jstucklex@attglobal.net> - 2015-04-02 10:17 -0400
      Re: Problem creating a series of tables richard <noreply@example.com> - 2015-03-31 20:14 -0400
        Re: Problem creating a series of tables Doug Miller <doug_at_milmac_dot_com@example.com> - 2015-04-01 02:44 +0000
          Re: Problem creating a series of tables Jerry Stuckle <jstucklex@attglobal.net> - 2015-03-31 23:06 -0400
            Re: Problem creating a series of tables richard <noreply@example.com> - 2015-04-01 00:09 -0400
              Re: Problem creating a series of tables Doug Miller <doug_at_milmac_dot_com@example.com> - 2015-04-01 20:37 +0000
                Re: Problem creating a series of tables Jerry Stuckle <jstucklex@attglobal.net> - 2015-04-01 17:08 -0400
      Re: Problem creating a series of tables Thomas 'PointedEars' Lahn <PointedEars@web.de> - 2015-04-02 14:50 +0200
        Re: Problem creating a series of tables Jerry Stuckle <jstucklex@attglobal.net> - 2015-04-02 10:20 -0400
          Re: Problem creating a series of tables Thomas 'PointedEars' Lahn <PointedEars@web.de> - 2015-04-02 22:32 +0200
            Re: Problem creating a series of tables Thomas 'PointedEars' Lahn <PointedEars@web.de> - 2015-04-03 00:44 +0200
              Re: Problem creating a series of tables Jerry Stuckle <jstucklex@attglobal.net> - 2015-04-02 19:22 -0400
              Re: Problem creating a series of tables Jerry Stuckle <jstucklex@attglobal.net> - 2015-04-02 19:24 -0400
              Re: Problem creating a series of tables "Christoph M. Becker" <cmbecker69@arcor.de> - 2015-04-03 01:46 +0200
                Re: Problem creating a series of tables Thomas 'PointedEars' Lahn <PointedEars@web.de> - 2015-04-03 20:45 +0200
            Re: Problem creating a series of tables Richard Yates <richard@yatesguitar.com> - 2015-04-02 15:59 -0700
              Re: Problem creating a series of tables Thomas 'PointedEars' Lahn <PointedEars@web.de> - 2015-04-03 01:10 +0200
                Re: Problem creating a series of tables Richard Yates <richard@yatesguitar.com> - 2015-04-02 18:44 -0700

Page 2 of 2 — ← Prev page 1 [2]

#15195

On 4/2/2015 6:44 PM, the pedantic troll Thomas 'Pointed Head' Lahn wrote:
> Thomas 'PointedEars' Lahn wrote:
> 
>> ,----
>> | mysql> create temporary table tmp (id int(10) primary key not null);
>> | Query OK, 0 rows affected (0.01 sec)
>> | 
>> | mysql> explain tmp;
>> | +-------+---------+------+-----+---------+-------+
>> | | Field | Type    | Null | Key | Default | Extra |
>> | +-------+---------+------+-----+---------+-------+
>> | | id    | int(10) | NO   | PRI | NULL    |       |
>> | +-------+---------+------+-----+---------+-------+
>> | 1 row in set (0.01 sec)
>> | 
>> | mysql> insert into tmp values (1);
>> | Query OK, 1 row affected (0.00 sec)
>> | 
>> | mysql> insert into tmp values (0);
>> | Query OK, 1 row affected (0.01 sec)
>> | 
>> | mysql> select * from tmp;
>> | +----+
>> | | id |
>> | +----+
>> | |  0 |
>> | |  1 |
>> | +----+
>> | 2 rows in set (0.00 sec)
>> `----
> 
> I realize that this is not a good test.  The order could still be arbitrary; 
> one run is insufficient to show the opposite.  But by the law of large 
> numbers, if the order of result records would be arbitrary, the record “1” 
> should occur at first position about 50 % of the time, and there should be 
> at least one occurrence of “1” at first position.  But:
> 
> $ mysql -u root -p$pass <<<'create database tmp; use tmp; create table tmp 
> (id int(10) primary key not null); insert into tmp values (1); insert into 
> tmp values (0);'
> 
> $ for i in $(seq 1 100); do mysql -u root -p$pass <<<'use tmp; select id 
> from tmp limit 1\G'; done | grep '^id: 0' | wc -l
> 100
> 
> $ for i in $(seq 1 1000); do mysql -u root -p$pass <<<'use tmp; select id 
> from tmp limit 1\G'; done | grep '^id: 1' | wc -l
> 0
> 
> $ for i in $(seq 1 10000); do mysql -u root -p$pass <<<'use tmp; select id 
> from tmp limit 1\G'; done | grep '^id: 1' | wc -l
> 0
> 
> $ for i in $(seq 1 100000); do mysql -u root -p$pass <<<'use tmp; select 
> id from tmp limit 1\G'; done | grep '^id: 1' | wc -l
> 0
> 
> You can repeat this with ever larger numbers; the results are consistent: 
> Never once is “1” at first position.  Because “0” always is, although that 
> record had been inserted *after* “1”.  Conclusion: The result records are 
> sorted by primary key.  Q.E.D.
> 

And what happens if you delete rows then insert more?

Your example works ONLY because you are inserting new rows into an empty
table - so obviously they are in order in the table.

But once you've DELETEd or UPDATEd rows, they do NOT have to be in any
specific order.

Your comments only serve to prove your ignorance.  You can copy and
paste code.  But you have no idea how it works.

-- 
==================
Remove the "x" from my email address
Jerry Stuckle
jstucklex@attglobal.net
==================

[toc] | [prev] | [next] | [standalone]

#15196

On 4/2/2015 6:44 PM, the pedantic troll Thomas 'Pointed Head' Lahn wrote:
> Thomas 'PointedEars' Lahn wrote:
> 
>> ,----
>> | mysql> create temporary table tmp (id int(10) primary key not null);
>> | Query OK, 0 rows affected (0.01 sec)
>> | 
>> | mysql> explain tmp;
>> | +-------+---------+------+-----+---------+-------+
>> | | Field | Type    | Null | Key | Default | Extra |
>> | +-------+---------+------+-----+---------+-------+
>> | | id    | int(10) | NO   | PRI | NULL    |       |
>> | +-------+---------+------+-----+---------+-------+
>> | 1 row in set (0.01 sec)
>> | 
>> | mysql> insert into tmp values (1);
>> | Query OK, 1 row affected (0.00 sec)
>> | 
>> | mysql> insert into tmp values (0);
>> | Query OK, 1 row affected (0.01 sec)
>> | 
>> | mysql> select * from tmp;
>> | +----+
>> | | id |
>> | +----+
>> | |  0 |
>> | |  1 |
>> | +----+
>> | 2 rows in set (0.00 sec)
>> `----
> 
> I realize that this is not a good test.  The order could still be arbitrary; 
> one run is insufficient to show the opposite.  But by the law of large 
> numbers, if the order of result records would be arbitrary, the record “1” 
> should occur at first position about 50 % of the time, and there should be 
> at least one occurrence of “1” at first position.  But:
> 
> $ mysql -u root -p$pass <<<'create database tmp; use tmp; create table tmp 
> (id int(10) primary key not null); insert into tmp values (1); insert into 
> tmp values (0);'
> 
> $ for i in $(seq 1 100); do mysql -u root -p$pass <<<'use tmp; select id 
> from tmp limit 1\G'; done | grep '^id: 0' | wc -l
> 100
> 
> $ for i in $(seq 1 1000); do mysql -u root -p$pass <<<'use tmp; select id 
> from tmp limit 1\G'; done | grep '^id: 1' | wc -l
> 0
> 
> $ for i in $(seq 1 10000); do mysql -u root -p$pass <<<'use tmp; select id 
> from tmp limit 1\G'; done | grep '^id: 1' | wc -l
> 0
> 
> $ for i in $(seq 1 100000); do mysql -u root -p$pass <<<'use tmp; select 
> id from tmp limit 1\G'; done | grep '^id: 1' | wc -l
> 0
> 
> You can repeat this with ever larger numbers; the results are consistent: 
> Never once is “1” at first position.  Because “0” always is, although that 
> record had been inserted *after* “1”.  Conclusion: The result records are 
> sorted by primary key.  Q.E.D.
> 

P.S. See http://dev.mysql.com/doc/refman/5.7/en/sorting-rows.html for
more details.


-- 
==================
Remove the "x" from my email address
Jerry Stuckle
jstucklex@attglobal.net
==================

[toc] | [prev] | [next] | [standalone]

#15197

Thomas 'PointedEars' Lahn wrote:

> I realize that this is not a good test.
> [...]
> Conclusion: The result records are sorted by primary key.  Q.E.D.

Even a perfect test would always give implementation specific results,
which might be subject to change.  FWIW, the SQL 92 specification[1]
states in section 20.2 <direct select statement: multiple rows>:

| If an <order by clause> is not specified, then the ordering of
| the rows of Q is implementation-dependent.

[1] <http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt>

-- 
Christoph M. Becker

[toc] | [prev] | [next] | [standalone]

#15199

Christoph M. Becker wrote:

> Thomas 'PointedEars' Lahn wrote:
>> I realize that this is not a good test.
>> [...]
>> Conclusion: The result records are sorted by primary key.  Q.E.D.
> 
> Even a perfect test would always give implementation specific results,
> which might be subject to change. 

Yes, of course, which is why I have given the details of my test 
environment.  My posting should not be misconstrued to mean that
you can always omit ORDER BY if you defined an index.

> FWIW, the SQL 92 specification[1] states in section 20.2 <direct select 
> statement: multiple rows>:
> 
> | If an <order by clause> is not specified, then the ordering of
> | the rows of Q is implementation-dependent.
> 
> [1] <http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt>

Thanks.

-- 
PointedEars
Zend Certified PHP Engineer
Twitter: @PointedEars2
Please do not cc me. / Bitte keine Kopien per E-Mail.

[toc] | [prev] | [next] | [standalone]

#15193

On Thu, 02 Apr 2015 22:32:33 +0200, Thomas 'PointedEars' Lahn
<PointedEars@web.de> wrote:

>Jerry Stuckle wrote:
>
>> On 4/2/2015 8:50 AM, the pedantic troll Thomas 'Pointed Head' Lahn wrote:
>>> Denis McMahon wrote:
>>>> Remember that if you don't specify a sort order in the mysql query, the
>>>> ordering of the records it returns is random.
>>> 
>>> Incorrect.  The ordering is undefined if the table has no keys that
>>> define
>>> sort order.  For example, for a InnoDb table named `test` that has
>>> 
>>> | Field   Type    Null    Key     Default Extra
>>> | id      int(10) unsigned        NO      PRI     NULL    auto_increment
>>> 
>>> the result of the MySQL query
>>> 
>>> | SELECT * FROM `test` LIMIT 1
>>> 
>>> will be the record with the smallest `id` field value.
>>> 
>>> $ php -v
>>> PHP 5.6.7-1 (cli) (built: Mar 24 2015 12:30:15)
>>> Copyright (c) 1997-2015 The PHP Group
>>> Zend Engine v2.6.0, Copyright (c) 1998-2015 Zend Technologies
>>>     with Zend OPcache v7.0.4-dev, Copyright (c) 1999-2015, by Zend
>>> Technologies
>>>     with Xdebug v2.2.6, Copyright (c) 2002-2014, by Derick Rethans
>>> 
>>> $ mysql -V
>>> mysql  Ver 14.14 Distrib 5.5.42, for debian-linux-gnu (x86_64) using
>>> readline 6.3
>> 
>> Incorrect (again).  It will return the first row in the table - which
>> may or may not be the one with the smallest 'id' value.
>
>Si tacuisses, philosophus mansisses.
>
>,----
>| mysql> create temporary table tmp (id int(10) primary key not null);
>| Query OK, 0 rows affected (0.01 sec)
>| 
>| mysql> explain tmp;
>| +-------+---------+------+-----+---------+-------+
>| | Field | Type    | Null | Key | Default | Extra |
>| +-------+---------+------+-----+---------+-------+
>| | id    | int(10) | NO   | PRI | NULL    |       |
>| +-------+---------+------+-----+---------+-------+
>| 1 row in set (0.01 sec)
>| 
>| mysql> insert into tmp values (1);
>| Query OK, 1 row affected (0.00 sec)
>| 
>| mysql> insert into tmp values (0);
>| Query OK, 1 row affected (0.01 sec)
>| 
>| mysql> select * from tmp;
>| +----+
>| | id |
>| +----+
>| |  0 |
>| |  1 |
>| +----+
>| 2 rows in set (0.00 sec)
>`----
>
>> By definition, order is undefined in a SQL database unless the ORDER BY
>> clause is specified.
>
>So much for theory – for which you want to cite evidence.

Interesting. InnoDb pulls them out in order of the primary index.
MyISAM pulls them in the order that they went in.

[toc] | [prev] | [next] | [standalone]

#15194

Richard Yates wrote:

> Interesting. InnoDb pulls them out in order of the primary index.
> MyISAM pulls them in the order that they went in.

I cannot reproduce that.  The same table, using MyISAM as database engine 
instead, yields the same results as in 
<news:2535888.CQyUAPWPjF@PointedEars.de>: Never once is the “1” at first 
position.

-- 
PointedEars
Zend Certified PHP Engineer
Twitter: @PointedEars2
Please do not cc me. / Bitte keine Kopien per E-Mail.

[toc] | [prev] | [next] | [standalone]

#15198

On Fri, 03 Apr 2015 01:10:59 +0200, Thomas 'PointedEars' Lahn
<PointedEars@web.de> wrote:

>Richard Yates wrote:
>
>> Interesting. InnoDb pulls them out in order of the primary index.
>> MyISAM pulls them in the order that they went in.
>
>I cannot reproduce that.  The same table, using MyISAM as database engine 
>instead, yields the same results as in 
><news:2535888.CQyUAPWPjF@PointedEars.de>: Never once is the “1” at first 
>position.

I did it with php and a permanent table and reset the storage engine
between runs:

<?php require_once('../../Connections/Salem_Harvest.php'); 
echo '<pre>';
$describeq="describe temp";
$rsDescribe=mysqli_query($Salem_Harvest, $describeq);
while($describerow=mysqli_fetch_assoc($rsDescribe)) {
print_r($describerow); }

$insertq="insert into temp (id, text) values (7, 'erwer')";
$rsInsertq=mysqli_query($Salem_Harvest,$insertq);
$insertq="insert into temp (id, text) values (1, 'sdfsdf')";
$rsInsertq=mysqli_query($Salem_Harvest,$insertq);
$insertq="insert into temp (id, text) values (8, 'sdfkljksdf')";  
$rsInsertq=mysqli_query($Salem_Harvest,$insertq);

$selectq="select * from temp";
$rsSelect=mysqli_query($Salem_Harvest,$selectq);
while($row=mysqli_fetch_assoc($rsSelect)) { print_r($row); }
exit;
?>

Array
(
    [Field] => id
    [Type] => int(4)
    [Null] => NO
    [Key] => PRI
    [Default] => 
    [Extra] => auto_increment
)
Array
(
    [Field] => text
    [Type] => varchar(50)
    [Null] => YES
    [Key] => 
    [Default] => 
    [Extra] => 
)
Array
(
    [id] => 7
    [text] => erwer
)
Array
(
    [id] => 1
    [text] => sdfsdf
)
Array
(
    [id] => 8
    [text] => sdfkljksdf
)

[toc] | [prev] | [standalone]

Page 2 of 2 — ← Prev page 1 [2]

Back to top | Article view | comp.lang.php

csiph-web