xref: /php-src/ext/mysqli/tests/bug76386.phpt (revision a21edc52) 
1--TEST--
2Prepared Statement formatter truncates fractional seconds from date/time column (bug #76386)
3--EXTENSIONS--
4mysqli
5--SKIPIF--
6<?php
7require_once 'connect.inc';
8
9if (!$link = @my_mysqli_connect($host, $user, $passwd, $db, $port, $socket)) {
10    die(sprintf("skip Can't connect to MySQL Server - [%d] %s", mysqli_connect_errno(), mysqli_connect_error()));
11}
12
13/* Fractional seconds are supported with servers >= 5.6.4. */
14if (mysqli_get_server_version($link) < 50604) {
15    die(sprintf("skip Server doesn't support fractional seconds in timestamp (%s)", mysqli_get_server_version($link)));
16}
17mysqli_close($link);
18?>
19--FILE--
20<?php
21require_once 'connect.inc';
22// test part 1 - TIMESTAMP(n)
23$link = new mysqli($host, $user, $passwd, $db, $port, $socket);
24$link->query('DROP TABLE IF EXISTS ts_test;');
25$link->query(
26    'CREATE TABLE ts_test (id bigint unsigned auto_increment primary key, ' .
27    'ts timestamp default current_timestamp(), ts2 timestamp(2) default ' .
28    'current_timestamp(2), ts2b timestamp(2) default "2018-01-01 03:04:05.06", ' .
29    'ts4 timestamp(4) default current_timestamp(4), ts4b timestamp(4) default ' .
30    '"2018-01-01 03:04:05.0070", ts6 timestamp(6) default current_timestamp(6), ts6b ' .
31    'timestamp(6) default "2018-01-01 03:04:05.008000") character set utf8 collate ' .
32    'utf8_general_ci;'
33);
34$link->query(
35    'INSERT INTO ts_test (ts, ts2, ts4, ts6) VALUES ("2018-01-01 11:22:33", ' .
36    '"2018-02-02 11:22:33.77", "2018-03-03 11:22:33.8888", ' .
37    '"2018-04-04 11:22:33.999999");'
38);
39$stmt = $link->prepare('SELECT * FROM ts_test;'); // must be statement
40if ($stmt) {
41    $stmt->execute();
42    $tsid = $ts = $ts2 = $ts2b = $ts4 = $ts4b = $ts6 = $ts6b = null;
43    $stmt->bind_result($tsid, $ts, $ts2, $ts2b, $ts4, $ts4b, $ts6, $ts6b);
44    $stmt->fetch();
45    var_dump($ts, $ts2, $ts2b, $ts4, $ts4b, $ts6, $ts6b);
46    $stmt->free_result();
47} else {
48    echo '[FIRST][FAIL] mysqli::prepare returned false: ' . $link->error . PHP_EOL;
49}
50$link->close();
51
52// test part 2 - TIME(n)
53$link = new mysqli($host, $user, $passwd, $db, $port, $socket);
54$link->query('DROP TABLE IF EXISTS t_test;');
55$link->query(
56    'CREATE TABLE t_test (id bigint unsigned auto_increment primary key, ' .
57    't time default "11:00:00", t2 time(2) default "11:00:00.22", t4 ' .
58    'time(4) default "11:00:00.4444", t6 time(6) default "11:00:00.006054") ' .
59    'character set utf8 collate utf8_general_ci;'
60);
61$link->query(
62    'INSERT INTO t_test (t, t2, t4, t6) VALUES ("21:22:33", "21:22:33.44", ' .
63    '"21:22:33.5555", "21:22:33.676767");'
64);
65$link->query('INSERT INTO t_test VALUES ();');
66
67$stmt = $link->prepare('SELECT * FROM t_test;');
68if ($stmt) {
69    $stmt->execute();
70    $tid = $t = $t2 = $t3 = $t4 = null;
71    $stmt->bind_result($tid, $t, $t2, $t4, $t6);
72    while ($stmt->fetch()) {
73        var_dump($t, $t2, $t4, $t6);
74    }
75    $stmt->free_result();
76} else {
77    echo '[SECOND][FAIL] mysqli::prepare returned false: ' . $link->error . PHP_EOL;
78}
79$link->close();
80?>
81--EXPECT--
82string(19) "2018-01-01 11:22:33"
83string(22) "2018-02-02 11:22:33.77"
84string(22) "2018-01-01 03:04:05.06"
85string(24) "2018-03-03 11:22:33.8888"
86string(24) "2018-01-01 03:04:05.0070"
87string(26) "2018-04-04 11:22:33.999999"
88string(26) "2018-01-01 03:04:05.008000"
89string(8) "21:22:33"
90string(11) "21:22:33.44"
91string(13) "21:22:33.5555"
92string(15) "21:22:33.676767"
93string(8) "11:00:00"
94string(11) "11:00:00.22"
95string(13) "11:00:00.4444"
96string(15) "11:00:00.006054"
97--CLEAN--
98<?php
99require_once 'connect.inc';
100$link = new mysqli($host, $user, $passwd, $db, $port, $socket);
101$link->query('DROP TABLE ts_test;');
102$link->query('DROP TABLE t_test;');
103$link->close();
104?>
105