migration batchInsert can't handle NULL in .dat files

[marcelloh]

if we use a .dat file with null values, like this:
1,1,superadmin,"First name Last name",email@test.com,,NULL,NULL
the NULL's are imported as strings and become "NULL"

this is wrong.

Expected Behavior

Have one record in the database with 2 columns with a NULL value

Workaround

We have a self written batchInsert that is almost the same, except for the value part:

$values = array_map(
	function ($value) {
		return 'null' === strtolower($value) ? null : $value;
	},
	$line
);

output

INSERT INTO "users" ("id", "login_type_id", "username", "name", "email_addr", "password", "phone_num", "token_id") VALUES (?, ?, ?, ?, ?, null, ?, ?)ERROR: SQLSTATE[22P02]: Invalid text representation: 7 ERROR:  invalid input syntax for integer: "NULL"

Details

Phalcon DevTools (3.0.5)

Environment::
  OS: Linux 273379e6b838 4.9.12-moby #1 SMP Tue Feb 28 12:11:36 UTC 2017 x86_64
  PHP Version: 7.0.16-4+deb.sury.org~xenial+1
  PHP SAPI: cli
  PHP Bin: /usr/bin/php7.0
  PHP Extension Dir: /usr/lib/php/20151012
  PHP Bin Dir: /usr/bin
  Loaded PHP config: /etc/php/7.0/cli/php.ini
Versions::
  Phalcon DevTools Version: 3.0.5
  Phalcon Version: 3.0.4
  AdminLTE Version: 2.3.6
Database : Postgress

[sergeysviridenko]

@marcelloh could you provide information about table and steps to reproduce?

[marcelloh]

nope, this was an issue along time ago and we solved it by the workaround mentioned.

I think the insert statement reflects the structure of the table, and I did write enough to reproduce.
What data I used is also mentioned. so no, you have to figure it out yourselves. (If you wanted this, you should have reacted way sooner.)

[sergeysviridenko]

@marcelloh sorry for the long reaction. We try to help to all users as soon as possible.
I won't be able to reproduce this issue without SQL for inserting table and data

[marcelloh]

ehm, when I see this:
INSERT INTO "users" ("id", "login_type_id", "username", "name", "email_addr", "password", "phone_num", "token_id") VALUES (?, ?, ?, ?, ?, null, ?, ?)
You can create a table out of it, that's not hard.
and the data was mentioned also:
if we use a .dat file with null values, like this:
1,1,superadmin,"First name Last name",email@test.com,,NULL,NULL
the NULL's are imported as strings and become "NULL"

[sergeysviridenko]

@marcelloh I'd like to see your SQL with parameters for inserting table in DB. It's very important for accurate reproducing.

[marcelloh]

I hope it's this one:

CREATE TABLE public.users (
	"id" SERIAL,
	"login_type_id" INTEGER NOT NULL,
	"username" VARCHAR(255) NOT NULL,
	"name" VARCHAR(255) NOT NULL,
	"email_addr" VARCHAR(255) NOT NULL,
	"password" VARCHAR(255) NOT NULL,
	"phone_num" BIGINT NULL,
	"token_id" BIGINT NULL,
	CONSTRAINT users_pkey PRIMARY KEY ("id"),
	CONSTRAINT users_username_unique UNIQUE ("username")
); 

[sergeysviridenko]

I suppose this issue can be closed