NULL in select is escaped (mysqli_sql_exception)

[fefo-p]

When trying to get a field that does not exist in a table, I get an Unknown column 'NULL' in 'field list' error.
As you can see below, it seems that BaseBuilder is not taking into account that NULL is not a field (then backticks are not needed).

My function is:

  public function joinExpedientes() {
        return $this->select('NULL as fecha_salida')
                    ->join('expedientes', 'movimientos_entradas.id_expediente = expedientes.id_expediente');
    }

And what BaseBuilder gets is:

SELECT `NULL` as `fecha_salida`
FROM `movimientos_entradas`
JOIN `expedientes` ON `movimientos_entradas`.`id_expediente` = `expedientes`.`id_expediente`

Obviously, when you try this select (without the backticks) in MySQL, it works fine.
Maybe there is a way to escape NULL in active record select, but if that's the case, I do not know how.

[fefo-p]

Not sure I solved it, but I found a work around.
This is the diff on system/Database/BaseBuilder.php

262,268d261
< 	/*
< 	 *	When doing 'SELECT NULL as field_alias FROM table'
< 	 *	 null gets taken as a field, and therefore escaped
< 	 *	 with backticks.
< 	 *	This prevents NULL being escaped
< 	 */
< 	if ( strtoupper(mb_substr(trim($val), 0, 4)) == 'NULL') $escape = FALSE;

[lonnieezell]

Random question - why would you want to do this query anyway? What does it do? As far as I can tell it doesn't do anything - select NULL. So it wouldn't join the tables in any meaningful way, right? Seems better to pull directly from the joined table in this case.

[fefo-p]

That was just a segment of the sql to show the "problem".
The complete sql is a union between two tables which do not have the same amount of columns.
In order for union to work, both should have the same number of columns as far as I know.
Could it be bad db design from my side? Most probably :)

The actual query:

                select id_movimiento_entrada as id,
                       expedientes.codigo,
                       CONVERT(expedientes.numero, CHAR) as numero,
                       expedientes.anyo,
                       expedientes.caratula,
                       movimientos_entradas.id_expediente,
                       fecha_entrada,
                       id_organismo,
                       id_dependencia,
                       id_departamento,
                       id_sector,
                       id_user,
                       id_tarea
                 from movimientos_entradas 
                 left join expedientes on movimientos_entradas.id_expediente = expedientes.id_expediente
                where CONCAT(YEAR(fecha_entrada),"-",MONTH(fecha_entrada),"-",DAY(fecha_entrada)) = CURDATE()

                UNION

                select id_movimiento_historial as id,
                       expedientes.codigo,
                       CONVERT(expedientes.numero, CHAR) as numero,
                       expedientes.anyo,
                       expedientes.caratula,
                       movimientos_historial.id_expediente,
                       fecha_entrada,
                       null as id_organismo,
                       origen_id_dependencia as id_dependencia,
                       null as id_departamento,
                       origen_id_sector as id_sector,
                       id_user,
                       id_tarea
                 from movimientos_historial
                 left join expedientes on movimientos_historial.id_expediente = expedientes.id_expediente
                where CONCAT(YEAR(fecha_entrada),"-",MONTH(fecha_entrada),"-",DAY(fecha_entrada)) = CURDATE()

[lonnieezell]

Oh! That makes more sense, then. I don't often use unions, so I didn't even think of that.

I'll get that patch applied. Thanks.