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