1--TEST--
2Playing with SELECT FORMAT(...) AS _format - see also bugs.php.net/42378
3--SKIPIF--
4<?php
5require_once('skipif.inc');
6require_once('skipifconnectfailure.inc');
7?>
8--INI--
9memory_limit=83886080
10--FILE--
11<?php
12    require_once("connect.inc");
13
14    function create_table($link, $column, $min, $max, $engine, $offset) {
15
16        if (!mysqli_query($link, 'DROP TABLE IF EXISTS test')) {
17            printf("[%03d] Cannot drop table test, [%d] %s\n",
18                $offset,
19                mysqli_errno($link), mysqli_error($link));
20            return array();
21        }
22
23        $sql = sprintf("CREATE TABLE test(id INT AUTO_INCREMENT PRIMARY KEY, col1 %s) ENGINE=%s",
24            $column, $engine);
25        if (!mysqli_query($link, $sql)) {
26            printf("[%03d] Cannot create table test, [%d] %s\n",
27                $offset + 1,
28                mysqli_errno($link), mysqli_error($link));
29            return array();
30        }
31
32        $values = array();
33        for ($i = 1; $i <= 100; $i++) {
34            $col1 = mt_rand($min, $max);
35            $values[$i] = $col1;
36            $sql = sprintf("INSERT INTO test(id, col1) VALUES (%d, %f)",
37                $i, $col1);
38            if (!mysqli_query($link, $sql)) {
39                printf("[%03d] Cannot insert data, [%d] %s\n",
40                    $offset + 2,
41                    mysqli_errno($link), mysqli_error($link));
42                return array();
43            }
44        }
45
46        return $values;
47    }
48
49    function test_format($link, $format, $from, $order_by, $expected, $offset) {
50
51        if (!$stmt = mysqli_stmt_init($link)) {
52            printf("[%03d] Cannot create PS, [%d] %s\n",
53                $offset,
54                mysqli_errno($link), mysqli_error($link));
55            return false;
56        }
57
58        if ($order_by)
59            $sql = sprintf('SELECT %s AS _format FROM %s ORDER BY %s', $format, $from, $order_by);
60        else
61            $sql = sprintf('SELECT %s AS _format FROM %s', $format, $from);
62
63        if (!mysqli_stmt_prepare($stmt, $sql)) {
64            printf("[%03d] Cannot prepare PS, [%d] %s\n",
65                $offset + 1,
66                mysqli_stmt_errno($stmt), mysqli_stmt_error($stmt));
67            return false;
68        }
69
70        if (!mysqli_stmt_execute($stmt)) {
71            printf("[%03d] Cannot execute PS, [%d] %s\n",
72                $offset + 2,
73                mysqli_stmt_errno($stmt), mysqli_stmt_error($stmt));
74            return false;
75        }
76
77        if (!mysqli_stmt_store_result($stmt)) {
78            printf("[%03d] Cannot store result set, [%d] %s\n",
79                $offset + 3,
80                mysqli_stmt_errno($stmt), mysqli_stmt_error($stmt));
81            return false;
82        }
83
84        if (!is_array($expected)) {
85
86            $result = null;
87            if (!mysqli_stmt_bind_result($stmt, $result)) {
88                printf("[%03d] Cannot bind result, [%d] %s\n",
89                    $offset + 4,
90                    mysqli_stmt_errno($stmt), mysqli_stmt_error($stmt));
91                return false;
92            }
93
94            if (!mysqli_stmt_fetch($stmt)) {
95                printf("[%03d] Cannot fetch result,, [%d] %s\n",
96                    $offset + 5,
97                    mysqli_stmt_errno($stmt), mysqli_stmt_error($stmt));
98                return false;
99            }
100
101            if ($result !== $expected) {
102                printf("[%03d] Expecting %s/%s got %s/%s with %s - %s.\n",
103                    $offset + 6,
104                    gettype($expected), $expected,
105                    gettype($result), $result,
106                    $format, $sql);
107            }
108
109        } else {
110
111            $order_by_col = $result = null;
112            if (!mysqli_stmt_bind_result($stmt, $order_by_col, $result)) {
113                printf("[%03d] Cannot bind result, [%d] %s\n",
114                    $offset + 7,
115                    mysqli_stmt_errno($stmt), mysqli_stmt_error($stmt));
116                return false;
117            }
118
119            foreach ($expected as $k => $v) {
120                if (!mysqli_stmt_fetch($stmt)) {
121                    break;
122                }
123                if ($result !== $v) {
124                    printf("[%03d] Row %d - expecting %s/%s got %s/%s [%s] with %s - %s.\n",
125                        $offset + 8,
126                        $k,
127                        gettype($v), $v,
128                        gettype($result), $result,
129                        $order_by_col,
130                        $format, $sql);
131                }
132            }
133
134        }
135
136        mysqli_stmt_free_result($stmt);
137        mysqli_stmt_close($stmt);
138
139        return true;
140    }
141
142    if (!$link = my_mysqli_connect($host, $user, $passwd, $db, $port, $socket))
143        printf("[001] Cannot connect - [%d] %s\n",
144            mysqli_connect_errno(),
145            mysqli_connect_error());
146
147    /* select from dual - pseudo table */
148    test_format($link, 'FORMAT(1.01, 0)', 'DUAL', null, '1', 10);
149    test_format($link, 'FORMAT(1.23, 1)', 'DUAL', null, '1.2', 20);
150    test_format($link, 'FORMAT(1.23, 2)', 'DUAL', null, '1.23', 30);
151    test_format($link, 'FORMAT(1234.567, 3)', 'DUAL', null, '1,234.567', 40);
152    /* no typo! */
153    test_format($link, 'FORMAT(1234.567, 4)', 'DUAL', null, '1,234.5670', 50);
154
155    mysqli_close($link);
156    require_once('table.inc');
157
158    /* select from existing table */
159    test_format($link, 'FORMAT(id, 0)', 'test', null, '1', 60);
160    test_format($link, 'FORMAT(id + 0.1, 1)', 'test', null, '1.1', 70);
161    test_format($link, 'FORMAT(id + 0.01, 2)', 'test', null, '1.01', 80);
162
163    /* create new table and select from it */
164    $expected = create_table($link, 'FLOAT', -10000, 10000, $engine, 90);
165    foreach ($expected as $k => $v)
166        $expected[$k] = number_format(round($v), 0, '.', ',');
167    test_format($link, 'id AS order_by_col, FORMAT(col1, 0)', 'test', 'id', $expected, 100);
168
169    $expected = create_table($link, 'FLOAT UNSIGNED', 0, 10000, $engine, 110);
170    foreach ($expected as $k => $v)
171        $expected[$k] = number_format(round($v), 0, '.', ',');
172    test_format($link, 'id AS order_by_col, FORMAT(col1, 0)', 'test', 'id', $expected, 120);
173
174    $expected = create_table($link, 'TINYINT', -128, 127, $engine, 130);
175    foreach ($expected as $k => $v)
176        $expected[$k] = number_format(round($v), 0, '.', ',');
177    test_format($link, 'id AS order_by_col, FORMAT(col1, 0)', 'test', 'id', $expected, 140);
178
179    $expected = create_table($link, 'SMALLINT UNSIGNED', 0, 65535, $engine, 150);
180    foreach ($expected as $k => $v)
181        $expected[$k] = number_format(round($v), 0, '.', ',');
182    test_format($link, 'id AS order_by_col, FORMAT(col1, 0)', 'test', 'id', $expected, 160);
183
184    $expected = create_table($link, 'MEDIUMINT', 0, 8388607, $engine, 170);
185    foreach ($expected as $k => $v)
186        $expected[$k] = number_format(round($v), 0, '.', ',');
187    test_format($link, 'id AS order_by_col, FORMAT(col1, 0)', 'test', 'id', $expected, 180);
188
189    $expected = create_table($link, 'INT UNSIGNED', 0, 1000, $engine, 190);
190    foreach ($expected as $k => $v)
191        $expected[$k] = number_format(round($v), 0, '.', ',');
192    test_format($link, 'id AS order_by_col, FORMAT(col1, 0)', 'test', 'id', $expected, 200);
193
194    $expected = create_table($link, 'BIGINT', -1000, 1000, $engine, 210);
195    foreach ($expected as $k => $v)
196        $expected[$k] = number_format(round($v), 0, '.', ',');
197    test_format($link, 'id AS order_by_col, FORMAT(col1, 0)', 'test', 'id', $expected, 220);
198
199    $expected = create_table($link, 'DECIMAL(5,0)', -1000, 1000, $engine, 230);
200    foreach ($expected as $k => $v)
201        $expected[$k] = number_format(round($v), 0, '.', ',');
202    test_format($link, 'id AS order_by_col, FORMAT(col1, 0)', 'test', 'id', $expected, 240);
203
204    // http://bugs.php.net/bug.php?id=42378
205    if (!mysqli_query($link, "DROP TABLE IF EXISTS test")) {
206        printf("[300] [%d] %s\n", mysqli_errno($link), mysqli_error($link));
207    }
208
209    if (mysqli_query($link, "CREATE TABLE `test` (
210  `targetport` int(11) NOT NULL default '0',
211  `sources` double(17,4) default NULL,
212  `current_sources` double(17,4) default NULL,
213  `reports` double(17,4) default NULL,
214  `current_reports` double(17,4) default NULL,
215  `targets` double(17,4) default NULL,
216  `current_targets` double(17,4) default NULL,
217  `maxsources` int(11) default NULL,
218  `maxtargets` int(11) default NULL,
219  `maxreports` int(11) default NULL,
220  `trend` float default NULL,
221  PRIMARY KEY  (`targetport`)
222) ENGINE=InnoDB DEFAULT CHARSET=latin1")) {
223
224        do {
225            $values = array();
226            for ($i = 0; $i < 200; $i++) {
227                $current_targets = mt_rand(-100000, 100000) / 10;
228                do {
229                    $trend = (mt_rand(0, 3) > 1) ? (mt_rand(-10000, 10000) / 100) : 'NULL';
230                } while (isset($values[$trend]));
231
232                $sql = sprintf('INSERT INTO test(targetport, current_targets, maxreports, trend) VALUES (%d, %f, %s, %s)',
233                    $i,
234                    $current_targets,
235                    (mt_rand(0, 1) > 0) ? mt_rand(0, 1000) : 'NULL',
236                    $trend);
237                if (!mysqli_query($link, $sql)) {
238                    printf("[301] [%d] %s\n", mysqli_errno($link), mysqli_error($link));
239                    break 2;
240                }
241                if ($current_targets > 0 && $trend !== 'NULL')
242                    $values[$trend] = $i;
243            }
244            krsort($values);
245
246            if (!$stmt = mysqli_stmt_init($link)) {
247                printf("[302] [%d] %s\n", mysqli_errno($link), mysqli_error($link));
248                break;
249            }
250
251            if (!mysqli_stmt_prepare($stmt, 'SELECT trend, targetport, FORMAT(trend, 2) FROM test WHERE current_targets > 0 AND trend IS NOT NULL ORDER BY trend DESC LIMIT 100')) {
252                printf("[303] [%d] %s\n", mysqli_stmt_errno($link), mysqli_stmt_error($link));
253                break;
254            }
255
256            if (!mysqli_stmt_execute($stmt)) {
257                printf("[304] [%d] %s\n", mysqli_stmt_errno($link), mysqli_stmt_error($link));
258                break;
259            }
260
261            if (!mysqli_stmt_store_result($stmt)) {
262                printf("[305] [%d] %s\n", mysqli_stmt_errno($link), mysqli_stmt_error($link));
263                break;
264            }
265
266            $trend = $targetport = $format = null;
267            if (!mysqli_stmt_bind_result($stmt, $trend, $targetport, $format)) {
268
269                printf("[305] [%d] %s\n", mysqli_stmt_errno($link), mysqli_stmt_error($link));
270                break;
271            }
272
273            foreach ($values as $exp_trend => $exp_targetport) {
274                if (!mysqli_stmt_fetch($stmt)) {
275                    break;
276                }
277                if ($targetport != $exp_targetport) {
278                    printf("[306] Values fetched from MySQL seem to be wrong, check manually\n");
279                    printf("%s/%s - %s/%s - '%s'\n", $trend, $exp_trend, $targetport, $exp_targetport, $format);
280                }
281            }
282            mysqli_stmt_free_result($stmt);
283            mysqli_stmt_close($stmt);
284
285            // same but OO interface
286            if (!$stmt = mysqli_stmt_init($link)) {
287                printf("[307] [%d] %s\n", mysqli_errno($link), mysqli_error($link));
288                break;
289            }
290
291            if (!$stmt->prepare('SELECT trend, targetport, FORMAT(trend, 2) FROM test WHERE current_targets > 0 AND trend IS NOT NULL ORDER BY trend DESC LIMIT 100')) {
292                printf("[308] [%d] %s\n", mysqli_stmt_errno($link), mysqli_stmt_error($link));
293                break;
294            }
295
296            if (!$stmt->execute()) {
297                printf("[309] [%d] %s\n", mysqli_stmt_errno($link), mysqli_stmt_error($link));
298                break;
299            }
300
301            if (!$stmt->store_result()) {
302                printf("[310] [%d] %s\n", mysqli_stmt_errno($link), mysqli_stmt_error($link));
303                break;
304            }
305
306            $trend = $targetport = $format = null;
307            if (!$stmt->bind_result($trend, $targetport, $format)) {
308
309                printf("[311] [%d] %s\n", mysqli_stmt_errno($link), mysqli_stmt_error($link));
310                break;
311            }
312
313            foreach ($values as $exp_trend => $exp_targetport) {
314                if (!$stmt->fetch()) {
315                    break;
316                }
317                if ($targetport != $exp_targetport) {
318                    printf("[312] Values fetched from MySQL seem to be wrong, check manually\n");
319                    printf("%s/%s - %s/%s - '%s'\n", $trend, $exp_trend, $targetport, $exp_targetport, $format);
320                }
321            }
322            $stmt->free_result();
323            $stmt->close();
324
325        } while (false);
326
327    } else {
328        var_dump(mysqli_error($link));
329    }
330
331
332    mysqli_close($link);
333    print "done!";
334?>
335--CLEAN--
336<?php
337	require_once("clean_table.inc");
338?>
339--EXPECT--
340done!
341